Table of Contents for

Using SQLite

Once you have some form of SQLite installed, the first step is normally to run sqlite3 and play around. The sqlite3 tool accepts SQL commands from an interactive prompt and passes those commands to the SQLite core for processing.

Even if you have no intention of distributing a copy of sqlite3 with your application, it is extremely useful to have a copy around for testing and debugging queries. If your application uses a customized build of the SQLite core, you will likely want to build a copy of sqlite3 using the same build parameters.

To get started, just run the SQLite command. If you provide a filename (such as test.db), sqlite3 will open (or create) that file. If no filename is given, sqlite3 will automatically open an unnamed temporary database:

$ sqlite3 test.db
SQLite version 3.6.23.1
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite>

The sqlite> prompt means sqlite3 is ready to accept commands. We can start with some basic expressions:

sqlite> SELECT 3 * 5, 10;
15|10
sqlite>

SQL commands can also be entered across multiple lines. If no terminating semicolon is found, the statement is assumed to continue. In that case, the prompt will change to ...> to indicate sqlite3 is waiting for more input:

sqlite> SELECT 1 + 2,
    ...> 6 + 3;
3|9
sqlite>

If you ever find yourself at the ...> prompt unexpectedly, make sure you finished up the previous line with a semicolon.

In addition to processing SQL statements, there is a series of shell-specific commands. These are sometimes referred to as “dot-commands” because they start with a period. Dot-commands control the shell’s output formatting, and also provide a number of utility features. For example, the .read command can be used to execute a file full of SQL commands.

Dot-commands must be given at the sqlite> prompt. They must be given on one line, and should not end in a semicolon. You cannot mix SQL statements and dot-commands.

Two of the more useful dot-commands (besides .help) are .headers and .mode. Both of these control some aspect of the database output. Turning headers on and setting the output mode to column will produce a table that most people find easier to read:

sqlite> SELECT 'abc' AS start, 'xyz' AS end;
abc|xyz
sqlite> .headers on
sqlite> .mode column
sqlite> SELECT 'abc' AS start, 'xyz' AS end;
start       end
----------  ----------
abc         xyz
sqlite>

Also helpful is the .schema command. This will list all of the DDL commands (CREATE TABLE, CREATE INDEX, etc.) used to define the database. For a more complete list of all the sqlite3 command-line options and dot-commands, see Appendix A.