Connect to sqlite3 databases and make queries

Problem
You have a binary sqlite3 database file and you want to make some queries on it: find out what tables it has, look at the content of the tables, etc.

Solution
There is a command called “sqlite3” which is a client for sqlite3 databases. Let’s say our database is stored in a file called “database.sqlite“. (Here “.sqlite” is the file extension.)

# open database.sqlite with the client:
sqlite3 database.sqlite
# let's get the list of tables in this database:
.tables
# Say it has a table called "images". Let's see its content:
select * from images;

To learn more about the commands, just type “.help” in the client. You can dump a database in SQL text format, you can get the schema of a table, etc.

Tip
Although .sqlite database files are binary files, you can open them with a text editor too. At the top you can see the schemas of the tables in text format. Just be careful not to modify it.

Create a database from a schema (update 20111120)

sqlite3 database.sqlite < schema.sql