Running sqlite3

The Command-Line Program (CLP) in Command-Line Mode

You can use the CLP from the command line for tasks such as importing and exporting data, returning result sets, and performing general batch processing. It is ideal for use in shell scripts for automated database administration. To see what the CLP offers in command-line mode, invoke it from the shell (Windows or Unix) with the --help switch, as shown here:

The CLP in command-line mode takes the following arguments:

• A list of options (optional)
• A database filename (optional)
• A SQL command to execute (optional)

Most of the options control output formatting except for the init switch, which specifies a batch file of SQL commands to process. The database filename is required. The SQL command is optional with a few caveats.

Database Administration

Creating a Database

Let's start by creating a database that we will call test.db. From the command line, open the CLP in shell mode by typing the following:

sqlite3 test.db

Even though we have provided a database name, SQLite does not actually create the database (yet) if it doesn't already exist. SQLite will defer creating the database until you actually create something inside it, such as a table or view. The reason for this is so that you have the opportunity to set various permanent database settings (such as page size) before the database structure is committed to disk.

Some settings such as page size and character encoding (UTF-8, UTF-16, etc.) cannot be changed easily once the database is created, so this is where you have a chance to specify them. We will go with the default settings here, so to actually create the database on disk, we need only to create a table. Issue the following statement from the shell:

sqlite> create table test (id integer primary key, value text);

Now you have a database file on disk called test.db, which contains one table called test. This table, as you can see, has two columns:

• A primary key column called id, which has the ability to automatically generate values by default. Wherever you define a column of type integer primary key, SQLite will apply an function for the column to create and apply monotonically increasing values. That is, if no value is provided for the column in an INSERT statement, SQLite will automatically generate one by finding the next integer value specific to that column.
• A simple text field called value.

Let's add a few rows to the table:

sqlite> insert into test (id, value) values(1, 'eenie');
sqlite> insert into test (id, value) values(2, 'meenie');
sqlite> insert into test (value) values('miny');
sqlite> insert into test (value) values('mo');

Now fetch them back:

sqlite> .mode column
sqlite> .headers on
sqlite> select * from test;

The two commands preceding the select statement (.headers and .mode) are used to improve the formatting a little. We can see that our explicit ID values for the first two rows were used. We can also see that SQLite provided sequential integer values for the id column for rows 3 and 4, which we did not provide in the insert statements.

Getting Database Schema Information

There are several shell commands for obtaining information about the contents of a database. You can retrieve a list of tables (and views) using .tables [pattern], where the optional [pattern] can be any pattern that the SQL like operator understands. All tables and views matching the given pattern will be returned. If no pattern is supplied, all tables and views are returned:

sqlite> .tables

Similarly, indexes for a given table can be printed using .indices [table_name].

The SQL definition or data definition language (DDL) for a table or view can be obtained using .schema [table_name]. If no table name is provided, the SQL definitions of all database objects (tables, indexes, views, and triggers) are returned.

More detailed schema information can be had from SQLite's principal system view, sqlite_master. This view is a simple system catalog of sorts. Its schema is described in the table below.

This is how to query the sqlite_master for our current database (don't forget to use the .mode column and .headers on commands first to manually set the column format and headers):

sqlite> .mode column
sqlite> .headers on
sqlite> select type, name, tbl_name, sql from sqlite_master order by type;

We get a complete inventory of test.db objects...

Exporting Data

You can export database objects to SQL format using the .dump command. Without any arguments, .dump will export the entire database as a series of DDL (data definition language) and data manipulation language (DML) commands, suitable for re-creating the database objects and the data contained therein. If you provide arguments, the shell interprets them as table names or views. Any tables or views matching the given arguments will be exported. Those that don't are simply ignored. In shell mode, the output from the .dump command is directed to the screen by default. If you want to redirect output to a file, use the .output [FILENAME] command. This command redirects all output to the file FILENAME. To restore output back to the screen, simply issue .output stdout. So, to export the current database to a file file.sql, you would do the following:

sqlite> .output file.sql
sqlite> .dump
sqlite> .output stdout

This will create the file file.sql in your current working directory if it does not already exist. If a file by that name does exist, it will be overwritten.

By combining redirection with SQL and the various shell formatting options (covered elsewhere), you have a great deal of control over exporting data. You can export specific subsets of tables and views in various formats using the delimiter of your choice, which can later be imported using the .import command described next.

Importing Data

There are two ways to import data, depending on the format of the data in the file to import. If the file is composed of SQL, you can use the .read command to execute the commands contained in the file. If the file contains comma-separated values (CSV) or other delimited data, you can use the .import [FILE][TABLE] command. This command will parse the specified file and attempt to insert it into the specified table. It does this by parsing each line in the file using the pipe character (|) as the delimiter and inserting the parsed columns into the table. Naturally, the number of parsed fields in the file should match up with the number of columns in the table. You can specify a different delimiter using the .separator command. To see the current value set for the separator, use the .show command. This will show all user-defined settings for the shell, among them the current default separator:

sqlite> .show

Which might yield something like:

The .read command is the way to import files created by the .dump command. Using file.sql created earlier as a backup, we can drop the existing database objects (the test table and schema view) and re-import it as follows:

sqlite> drop table test;
sqlite> drop view schema;
sqlite> .read file.sql

Formatting

The shell offers a number of formatting options to help you and make your results and output neat and tidy. The simplest are .echo, which echoes the last run command after issuing a command, and .headers, which includes column names for queries when set to on. The text representation of NULL can be set with .nullvalue. For instance, if you want NULLs to appear as the text string NULL, simply issue the command .nullvalue NULL. By default, this presentation value is an empty string.

The shell prompt can be changed using .prompt [value]:

sqlite> .prompt 'sqlite3> '
sqlite3>

Result data can be formatted several ways using the .mode command. The current options are csv, column, html, insert, line, list, tabs, and tcl, each of which is helpful in different ways. The default is .list. For instance, list mode displays results with the columns separated by the default separator. Thus, if you wanted to dump a table in a CSV format, you could do the following:

sqlite3> .output file.csv
sqlite3> .separator ,
sqlite3> select * from test;
sqlite3> .output stdout

Actually, since there is a CSV mode already defined in the shell, it is just as easy to use it in this particular example instead:

sqlite3> .output file.csv
sqlite3> .mode csv
sqlite3> select * from test;
sqlite3> .output stdout

The results obtained are the same. The difference is that CSV mode will wrap field values with double quotes, whereas list mode (the default) does not.

Exporting Delimited Data

Combining the previous three sections on exporting, importing, and formatting data, we now have an easy way to export and import data in delimited form. For example, to export only the rows of the test table whose value fields start with the letter m to a file called test.csv in comma-separated values, do the following:

sqlite> .output text.csv
sqlite> .separator ,
sqlite> select * from test where value like 'm%';
sqlite> .output stdout

If you want to then import this CSV data into a similar table with the same structure as the test table (call it test2), do the following:

sqlite> create table test2(id integer primary key, value text);
sqlite> .import text.csv test2

Performing Unattended Maintenance

So far, you've seen the CLP used interactively to perform tasks such as creating a database and exporting data. However, you don't always want to be tied to your seat, executing CLP commands one at a time. Instead, you can use the command mode to run CLP commands in batches. You can then use your operating system's built-in scheduler to schedule those batches to run whenever you need them.

There are actually two ways to invoke the CLP in command-line mode. The first is to provide a SQL command, or a SQLite shell command as well, such as .dump and .schema. Any valid SQL or SQLite shell command will do. SQLite will execute the specified command, print the result to standard output, and exit. For example, to dump the test.db database from the command line, issue the following command:

sqlite3 test.db .dump

To make it useful, we should redirect the output to a file:

sqlite3 test.db .dump > test.sql

The file test.sql now contains the complete human-readable set of DDL and DML statements for the database test.db. Similarly, to select all records for the test table, issue this:

sqlite3 test.db "select * from test"

The second way to invoke the CLP in command-line mode is to redirect a file as an input stream. For instance, to create a new database test2.db from our database dump test.sql, do the following:

sqlite3 test2.db < test.sql

The CLP will read the file as standard input and then process and apply all SQL commands within it to the test2.db database file.

Another way to create a database from the test.sql file is to use the init option and provide the test.sql as an argument:

sqlite3 -init test.sql test3.db

The CLP will process test.sql, create the test3.db database, and then go into shell mode. Why? The invocation included no SQL command or input stream. To get around this, you need to provide a SQL command or SQLite shell command. For example:

sqlite3 -init test.sql test3.db .exit

The .exit command prompts the CLP to run in command-line mode and does as little as possible. All things considered, redirection is perhaps the easiest method for processing files from the command line.

Backing Up a Database

Backing up a database can be done in two ways, depending on the type of backup you desire. A SQL dump is perhaps the most portable form for keeping backups. The standard way to generate one is using the CLP .dump command, as shown in the previous section. From the command line, this is done as follows:

sqlite3 test.db .dump > test.sql

Within the shell, you can redirect output to an external file, issue the command, and restore output to the screen as follows:

sqlite> .output file.sql
sqlite> .dump
sqlite> .output stdout
sqlite> .exit

Likewise, importing a database is most easily done by providing the SQL dump as an input stream to the CLP:

sqlite3 test.db < test.sql

This assumes that test.db does not already exist. If it does, then things may still work if the contents of test.sql are different from those of test.db. You will of course get errors if test.sql contains objects that already reside within test.db or contains data that violates primary key or foreign key constraints.

Making a binary backup of a database is little more than a file copy. One small operation you may want to perform beforehand is a database vacuum, which will free up any unused space created from deleted objects. This will provide you with a smaller resulting file from the binary copy:

sqlite3 test.db vacuum
cp test.db test.backup

As a general rule, binary backups are not as portable as SQL backups. On the whole, SQLite does have good backward compatibility and is binary compatible across all platforms for a given database format. However, for long-term backups, it is always a good idea to use SQL form. If size is an issue, SQL format (raw text) usually yields a good compression ratio.

Finally, if you've worked with other databases, dropping a database in SQLite, like binary backups, is a simple file operation: you simply delete the database file you want to drop.

Getting Database File Information

The primary means by which to obtain logical database information, such as table names, DDL statements, and so on, is using the sqlite_master view, which provides detailed information about all objects contained in a given database.

If you want information on the physical database structure, you can use a tool called SQLite Analyzer, which can be downloaded in binary form from the SQLite website. SQLite Analyzer provides detailed technical information about the on-disk structure of a SQLite database. This information includes a detailed breakdown of database, table, and index statistics for individual objects and in aggregate. It provides everything from database properties such as page size, number of tables, indexes, file size, and average page density (utilization) to detailed descriptions of individual database objects. Following the report is a detailed list of definitions explaining all terms used within the report.

You would run sqlite_analyzer like this:

fuzzy@linux:/tmp$ sqlite3_analyzer test.db

Indexes

SQLite uses B-tree for organizing indexes. Note that B stands for balanced, B-tree is a balanced tree, not a binary tree.

A SQLite index contains data from the columns you specify in the index and the corresponding rowid value. This helps SQLite quickly locate the row based on the values of the indexed columns.

To create an index, you use the CREATE INDEX statement with the following syntax:

CREATE [UNIQUE] INDEX index_name
  ON table_name(column_list);

To create an index, you specify the following information:

• The index name after the CREATE INDEX keywords.
• The name of the table to which the index belongs after the ON keyword.
• A list of columns of the index inside parentheses () after the table name.

If you want to ensure the values in one or more columns are unique like email and phone, you can use the UNIQUE option in the CREATE INDEX statement to create a new unique index.

SQLite multicolumn indexes

SQLite sorts the data on the multicolumn index by the first column specified in the CREATE INDEX statement. Then, it sorts the duplicate values by the second column, and so on. Therefore, the column order is crucial when creating a multicolumn index.

To fully utilize a multicolumn index, the query must include conditions that match the column order defined in the index.

The following statement creates a multicolumn index on the first_name and last_name columns of the contacts table:

CREATE INDEX idx_contacts_name
ON contacts (first_name, last_name);

If you query the contacts table with one of the following conditions in the WHERE clause, SQLite will utilize the multicolumn index to search for data.

1. Filter data by the first_name column.

   WHERE first_name = 'John';

2. Filter data by both first_name and last_name columns:

   WHERE first_name = 'John' AND last_name = 'Doe';

However, SQLite will not use the multicolumn index if you use one of the following conditions.

1. Filter by the last_name column only.

   WHERE last_name = 'Doe';

2. Filter by first_name OR last_name columns.

   WHERE last_name = 'Doe' OR first_name = 'John';

Show Indexes

To find all indexes associated with a table, you use the following command:

PRAGMA index_list('table_name');

For example, this statement shows all the indexes of the contacts table:

PRAGMA index_list('contacts');

To get the information about the columns in an index, you use the following command:

PRAGMA index_info('index_name');

Another way to get all indexes from a database is to query from the sqlite_master table:

SELECT
   type,
   name,
   tbl_name,
   sql
FROM
   sqlite_master
WHERE
   type= 'index';

SQLite DROP INDEX statement

To remove an index from a database, you use the DROP INDEX statement:

DROP INDEX [IF EXISTS] index_name;

In this syntax, specify the index name you want to remove after the DROP INDEX keywords. The IF EXISTS option deletes the index only if it exists.