Introduction to SQLite by Adam Shand

I recently had to learn how to use SQLite, here's my cheat sheet on how to do the basics.

Overview

SQLite is a very simple database which supports most of the SQL92 standard. It does not require a daemon process to run (similar to Berkeley DB) so can be a great alternative for lightweight database applications.

It does not have any support for users and instead relies on file system based permissions for controlling access to the database.

Administer the Database

There are two modes for administrating a SQLite database. From the command line (shown below by the # prompt) and from inside the SQLite console (shown by the sqlite> prompt).

In the console mode, all administration command begin with a leading . (eg. .help and .quit).

Create a new database and enter the console

# sqlite /tmp/new.sqlite

Open a console for an existing database

# sqlite /tmp/exists.sqlite

Import a SQL schema into the database

sqlite> .read /tmp/schema.sql

Print out a databases schema to standard out

# sqlite /tmp/exists.sqlite .dump

Dump out a database to a text file

# sqlite /tmp/exists.sqlite .dump > /tmp/backup.sql

Import a backup into a new database

# sqlite /tmp/new.sqlite
sqlite> .read /tmp/backup.sql

Quit the console

sqlite> .quit

Ask for help

sqlite> .help

Use the Database

List all of the open databases

sqlite> .databases
seq  name             file                                                      
---  ---------------  ----------------------------------------------------------
0    main             /tmp/new.sqlite                                           
1    temp             /var/tmp/sqlite_4aPDXkbVYL95nqt

List all the tables in the open database

sqlite> .tables
addressbook         addressbook_cat     addressbook_catmap