sqlite Cheat sheet

Access sqlite db

You can access any sqlite database file, as long there is no other client locking it, using the command line client to perform SQL commands:

Copy
sqlite3 <database file>

List Schema

To list the schema of a sqlite database run

Copy
SELECT name FROM sqlite_master;

It will dump all schema elements. One field of the result table contains the SQL used to create each schema element. If you only want a list of tables use the client command ".tables"

Copy
.tables

Exporting

Dump db as SQL

To dump the entire database content in SQL for example for a backup run:

Copy
sqlite3 <database file> .dump >output.sql

See the next sections how to dump a single table or in SQL.

Dump table as SQL

To dump the SQL to create a table and its values run the command line client using the ".dump" command an redirect the output:

Copy
sqlite3 <database file> ".dump <table name>" >output.sql

Dump table as CSV

To dump the SQL to create a table and its values run the command line client using the ".mode" command to enable CSV output and then to perform a "SELECT" on the table you want to dump. By echoing this to the CLI you can easily redirect the output:

Copy
echo ".mode csv
    select * from <table name>;" | sqlite3 >output.sql

Cleanup with Vacuum

To run a one time cleanup just run the following command on an sqlite database file. Ensure there is no program accessing the database file. If there is it will fail and do nothing:

Copy
sqlite3 my.db "VACUUM;"

Configure Auto-Vacuum

If you want sqlite to perform vacuum on-demand you can set the "auto_vacuum" pragma to either "INCREMENTAL" or "FULL":

Copy
PRAGMA auto_vacuum = INCREMENTAL;

Copy
PRAGMA auto_vacuum = FULL;

To query the current "auto_vacuum setting run

Copy
PRAGMA auto_vacuum