← Full reference

sqlite3

SQLite command-line client — a database in a single file, SQL queries and dot commands

sqlite3 opens an SQLite database — an ordinary file on disk — and runs SQL. No server is needed: a query can be passed as an argument, via stdin or typed in the interactive console. Besides SQL the client understands utility commands that start with a dot.

Syntax

sqlite3 [options] [file.db] ["SQL or .command" …]

Without queries after the file name an interactive console opens (exit — .quit or Ctrl+D). Each following argument is run in turn, as a separate input line. Without a file name the database is created in memory and disappears on exit.

Command-line options

Output format

OptionWhat it does
-listvalues separated by a delimiter (default |) — the default mode
-csvCSV with quotes where needed
-column, -table, -box, -markdownaligned table: no border / ASCII border / box-drawing border / Markdown
-json, -line, -tabs, -html, -quoteJSON array, "column = value" per line, TSV, HTML, SQL literals
-header / -noheaderprint / don't print column names
-separator SEPcolumn separator for -list
-nullvalue TEXThow to show NULL (default — an empty string)

Behavior

OptionWhat it does
-cmd '.command'run a command before the main queries — handy for setting the mode
-bailstop at the first error
-readonlyopen the database read-only
-echoprint each query before running it
-init filerun a file at startup instead of ~/.sqliterc
-batchnon-interactive mode, even if input is a terminal
-versionSQLite version

Dot commands

Database structure

CommandWhat it does
.tables [pattern]list of tables and views (the pattern is as in LIKE: user%)
.schema [pattern]CREATE statements for tables, indexes, triggers
.indexes [table]index names
.databasesattached databases and their files (main, databases from ATTACH)
.showcurrent client settings: mode, headers, separator

Output

CommandWhat it does
.mode MODElist, csv, column, table, box, markdown, json, line, tabs, html, quote, insert TABLE
.headers on|offcolumn headers
.separator COL [ROW]column (and row) separator
.nullvalue TEXThow NULL is displayed
.width N1 N2 …column widths for column/table/box
.output file / .outputall further output to a file / back to the screen
.once fileto a file — only the result of the next query
.print textprint a line

Data and files

CommandWhat it does
.import [options] file tableload a file into a table; without --csv the separator comes from the current .mode
.import --csvparse as CSV (quotes, commas inside fields)
.import --skip Nskip the first N lines — usually the header line
.read file.sqlrun SQL from a file
.dump [table]export the database or a table as SQL (CREATE + INSERT)
.backup file / .restore filecopy the database to a file / restore from a copy
.open fileclose the current database and open another
.shell commandrun a shell command
.help [command]help; .help import — details on a single command
.quit / .exit [code]quit (with an exit code)

A dot command takes up a whole line and is written without a trailing ;.

Useful SQL in SQLite

QueryWhat it does
PRAGMA table_info(t)table columns: name, type, NOT NULL, default value, PK
SELECT name FROM sqlite_master WHERE type='table'tables via SQL
typeof(x)the actual type of a value: integer, real, text, blob, null
CREATE TABLE t2 AS SELECT …a table from a query result
INSERT OR REPLACE INTO …insert or replace a row on a key conflict
INSERT … ON CONFLICT(k) DO UPDATE SET v = excluded.vupsert: update the existing row
ATTACH 'other.db' AS oattach a second database, tables as o.table
BEGIN; … COMMIT; / ROLLBACK;transaction
PRAGMA foreign_keys = ONenable foreign key checks (off by default)

Examples

# create a table and insert rows (the database file appears by itself)
sqlite3 app.db "CREATE TABLE users(id INTEGER PRIMARY KEY, name TEXT);
INSERT INTO users(name) VALUES ('alice'), ('bob');"

# which tables exist and how they are structured
sqlite3 app.db ".tables"
sqlite3 app.db ".schema users"
sqlite3 app.db "PRAGMA table_info(users)"

# the result with headers as a table
sqlite3 -header -column app.db "SELECT * FROM users"

# export the result to CSV
sqlite3 -csv -header app.db "SELECT id, name FROM users" > users.csv

# the mode is set via -cmd, and the query comes after
sqlite3 -cmd ".mode box" app.db "SELECT count(*) AS n FROM users"

# a single value without decoration — handy to assign to a variable
n=$(sqlite3 app.db "SELECT count(*) FROM users")

# CSV into a new table: the first line becomes the column names
sqlite3 app.db ".import --csv data.csv data"

# CSV into an existing table: skip the header line
sqlite3 app.db ".import --csv --skip 1 data.csv users"

# a script from a file; -bail stops it at the first error
sqlite3 -bail app.db < queries.sql

# full SQL dump and restore into another database
sqlite3 app.db .dump > dump.sql
sqlite3 copy.db < dump.sql

# read-only — protects against an accidental UPDATE
sqlite3 -readonly app.db "SELECT * FROM users"

Several commands in a row are easy to pass via a heredoc:

sqlite3 app.db <<'EOF'
.headers on
.mode csv
.output report.csv
SELECT * FROM users;
.output
EOF

Pitfalls

  • A typo in the file name creates a new empty database. sqlite3 ap.db ".tables" shows nothing and leaves an empty ap.db. If the tables are "missing", check the path first. And sqlite3 "SELECT 1" without a database name silently creates a file named SELECT 1: the first argument is always taken as the file.
  • Strings go in single quotes. WHERE name = "bob" works only as long as there's no bob column: double quotes mean a column name, and SQLite turns them into a string only for legacy compatibility. Write 'bob'.
  • .import into a new table makes all columns TEXT. Comparisons and sorting of numbers become string-based ('10' < '9'). Create the table with the right types beforehand and import into it with --skip 1.
  • .import into an existing table without --skip 1 loads the header line as an ordinary data row.
  • .import without --csv splits lines by the current mode's separator (| in list) and doesn't understand quotes. For CSV always pass --csv.
  • Types in SQLite are loose. An INTEGER column accepts the string 'abc' without an error. Check with typeof().
  • Foreign keys aren't checked by default. REFERENCES in the schema guarantees nothing without PRAGMA foreign_keys = ON (in every connection).
  • In the shell, put the whole SQL in double quotes, and string literals inside in single quotes. Otherwise bash eats the quotes before the query reaches sqlite3.
  • A script from stdin keeps going after an error: the following lines run, and only the exit code is 1. To stop at the first error, add -bail.
  • .output file lasts until cancelled. Forgetting .output without an argument sends everything after it to the file too. For a single query .once is more convenient.

See also

psql · redirection · cut · datamash