42

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.

6
  • 1
    What output or view type do you want? There are also GUI database explorers which would be easier to handle, I think.
    – Byte Commander
    Jul 31, 2016 at 11:54
  • @ByteCommander: Depends what types there are, could you give me some examples? Also, I don't really mind if it is in CLI or GUI.
    – user364819
    Jul 31, 2016 at 12:15
  • 2
    BTW are you sure you want sqlite and not sqlite3? Jul 31, 2016 at 14:52
  • @AndreaLazzarotto: I don't know... All I know is I have .sqlite files created by Firefox that I want to view.
    – user364819
    Aug 1, 2016 at 9:40
  • sqlite is the old version. I strongly suggest you use sqlite3 but you probably already did that if you installed the wonderful sqlitebrowser as suggested in the A. :) Aug 1, 2016 at 16:47

1 Answer 1

79

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.

4
  • 1
    I would suggest the .schema command to see how tables and fields were created. Jul 31, 2016 at 14:53
  • Bad experience with sqlitebrowser. It hung when I ran a query or when the window was moved to background. Switched to command line tool after that.
    – ka3ak
    Dec 12, 2017 at 9:03
  • 3
    Thank you @Byte Commander for such a beautiful and thorough answer.
    – Foreever
    Sep 14, 2018 at 23:29
  • ctrl+d is a "clean exit" in sqlite3 leaving no running process.
    – Timo
    Apr 29, 2021 at 18:43

You must log in to answer this question.