SQLite Commands and Tips

SQLite is a lightweight, portable database which can be very useful in development environments. Here are some tips on some common commands.

Open/Create a Database

$ sqlite3 database_file.db

If the file does not exist, then sqlite will create it, otherwise, it will open it.

Execute a query against a database

$ sqlite3 database_file.db "select * from table"

If you want to make the response easier to read then, add the '-csv' '-header' options. For example:

$ sqlite3 -csv -header database_file.db "select * from table"

To create a csv file, use the above example and redirect the output to a file.

Formatting Query Output inside SQLite

You can specify the format of results inside the SQLite environment.

sqlite> .mode columns
sqlite> .headers on

All commands (but not queries) inside SQLite start with a '.' (period).

Creating tables

sqlite> create table foo 
(
    id integer primary key, 
    bar text
);

Listing Databases and Tables

To view all Databases

sqlite> .databases

To view all Tables

sqlite> .tables

Dump/Export Database information

sqlite> .output /path/foo.sql

or

sqlite> .dump

Import information

sqlite> .read /path/foo.sql

Get Help

sqlite> .help

This will list all commands with an explanation of what they do.