Common tasks in SQLite

Get help with the command-line tool

To get a list of possible options for the command line tool sqlite3, use the option -help. At the time of writing, this prints

$ sqlite3 -help
Usage: sqlite3 [OPTIONS] FILENAME [SQL]
FILENAME is the name of an SQLite database. A new database is created
if the file does not previously exist.
OPTIONS include:
   -help                show this message
   -init filename       read/process named file
   -echo                print commands before execution
   -[no]header          turn headers on or off
   -bail                stop after hitting an error
   -interactive         force interactive I/O
   -batch               force batch I/O
   -column              set output mode to 'column'
   -csv                 set output mode to 'csv'
   -html                set output mode to HTML
   -line                set output mode to 'line'
   -list                set output mode to 'list'
   -separator 'x'       set output field separator (|)
   -nullvalue 'text'    set text string for NULL values
   -version             show SQLite version

After starting the command-line tool, you can get help on the options available by typing .help. See also Command Line Shell For SQLite. The manual page which comes with the Linux distribution of sqlite3 does not mention all of the above options.

Create a database from a schema file

To create a new database in the file my.db from a schema file called schema.sql,

sqlite3 -batch my.db < schema.sql