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
| Option | What it does |
|---|---|
-list | values separated by a delimiter (default |) — the default mode |
-csv | CSV with quotes where needed |
-column, -table, -box, -markdown | aligned table: no border / ASCII border / box-drawing border / Markdown |
-json, -line, -tabs, -html, -quote | JSON array, "column = value" per line, TSV, HTML, SQL literals |
-header / -noheader | print / don't print column names |
-separator SEP | column separator for -list |
-nullvalue TEXT | how to show NULL (default — an empty string) |
Behavior
| Option | What it does |
|---|---|
-cmd '.command' | run a command before the main queries — handy for setting the mode |
-bail | stop at the first error |
-readonly | open the database read-only |
-echo | print each query before running it |
-init file | run a file at startup instead of ~/.sqliterc |
-batch | non-interactive mode, even if input is a terminal |
-version | SQLite version |
Dot commands
Database structure
| Command | What 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 |
.databases | attached databases and their files (main, databases from ATTACH) |
.show | current client settings: mode, headers, separator |
Output
| Command | What it does |
|---|---|
.mode MODE | list, csv, column, table, box, markdown, json, line, tabs, html, quote, insert TABLE |
.headers on|off | column headers |
.separator COL [ROW] | column (and row) separator |
.nullvalue TEXT | how NULL is displayed |
.width N1 N2 … | column widths for column/table/box |
.output file / .output | all further output to a file / back to the screen |
.once file | to a file — only the result of the next query |
.print text | print a line |
Data and files
| Command | What it does |
|---|---|
.import [options] file table | load a file into a table; without --csv the separator comes from the current .mode |
.import --csv | parse as CSV (quotes, commas inside fields) |
.import --skip N | skip the first N lines — usually the header line |
.read file.sql | run SQL from a file |
.dump [table] | export the database or a table as SQL (CREATE + INSERT) |
.backup file / .restore file | copy the database to a file / restore from a copy |
.open file | close the current database and open another |
.shell command | run 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
| Query | What 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.v | upsert: update the existing row |
ATTACH 'other.db' AS o | attach a second database, tables as o.table |
BEGIN; … COMMIT; / ROLLBACK; | transaction |
PRAGMA foreign_keys = ON | enable 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 emptyap.db. If the tables are "missing", check the path first. Andsqlite3 "SELECT 1"without a database name silently creates a file namedSELECT 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 nobobcolumn: double quotes mean a column name, and SQLite turns them into a string only for legacy compatibility. Write'bob'. .importinto a new table makes all columnsTEXT. 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..importinto an existing table without--skip 1loads the header line as an ordinary data row..importwithout--csvsplits lines by the current mode's separator (|inlist) and doesn't understand quotes. For CSV always pass--csv.- Types in SQLite are loose. An
INTEGERcolumn accepts the string'abc'without an error. Check withtypeof(). - Foreign keys aren't checked by default.
REFERENCESin the schema guarantees nothing withoutPRAGMA foreign_keys = ON(in every connection). - In the shell, put the whole SQL in double quotes, and string literals inside in single quotes. Otherwise
basheats the quotes before the query reachessqlite3. - 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 filelasts until cancelled. Forgetting.outputwithout an argument sends everything after it to the file too. For a single query.onceis more convenient.
See also
psql · redirection · cut · datamash