Ubuntu – How to properly view a .sqlite file using sqlite

file formatsqlitesqlite3

I have installed the program sqlite in order to view file types with that extension, however even after reading its help documentation and its manpage, I am still confused about how I get it to show me what I want. So how exactly do you use this program? I just want to properly view a file of that extension.

I am running Ubuntu GNOME 16.04 with GNOME 3.20.

Best Answer

Let's start with the more user-friendly way and use the GUI tool sqlitebrowser. It offers you to easily explore a database without having to know SQL commands.

You can install it with the command

sudo apt install sqlitebrowser

and run it from the launcher/dash/application menu or using the command

sqlitebrowser

In the main window you can click Open database to open your *.sqlite file. It will then display something like this (here displaying the database of a Firefox add-on for applying user styles):

enter image description here

I already switched to the Browse Data tab on the left panel, where you can view the database table contents now. You select which table to display in the "Table:" combobox.

Of course you can also do this from the command-line, using e.g. sqlite3. This method requires you to know at least a basic set of SQL commands and is better suited for advanced users or if you need to parse the output in a script.

You install sqlite3 using the command

sudo apt install sqlite3

and then run it with the following command (-column and -header option for better readability of the output, see man sqlite3 for more info):

sqlite3 -column -header

Then you get an interactive SQLite3 command prompt, like this:

$ sqlite3 -column -header
SQLite version 3.11.0 2016-02-15 17:29:24
Enter ".help" for usage hints.
sqlite>

The first thing you must do now is to open the database file. Therefore you can use the .open SQLite command. Enter this to the sqlite> prompt (again using the same Firefox add-on database as above, you will type a different path of course):

.open "/home/bytecommander/.mozilla/firefox/gtltfeay.default/stylish.sqlite"

Note that Tab completion works here and will help you to enter the path to your database file.

You can now see the list of loaded databases using the .databases command (file name is truncated because it's so long):

sqlite> .databases
seq  name             file                                                      
---  ---------------  ----------------------------------------------------------
0    main             /home/bytecommander/.mozilla/firefox/gtltfeay.default/styl

You see the database we just opened is now called main.

In the next step we list all tables of the main (default) database:

sqlite> .tables
style_meta  styles

We see the two tables style_meta and styles listed.

Let's display the style_meta table completely with all columns. Therefore we need the SQL command SELECT * FROM style_meta; (don't forget the semicolon!):

sqlite> SELECT * FROM style_meta;
id          style_id    name        value      
----------  ----------  ----------  -----------
46          1           domain      lichess.org
47          1           type        site       
48          3           domain      lichess.org
49          3           type        site       
50          2           domain      lichess.org
51          2           type        site       
53          4           type        global

To get help with commands specific to SQLite3's interactive shell (commands starting with a period), type .help at the sqlite> prompt or read its manpage man sqlite3. All other commands are common SQL, you should search for a basic SQL tutorial to learn them.

You can exit the interactive sqlite3 shell again using the .exit command or Ctrl+D.

Related Question