SQLite Manual

A quick reference to the SQLite embedded database engine and the sqlite3 command-line shell.  |  Official docs: sqlite.org/docs.html

Overview map

SQLite                        serverless, zero-config, single-file SQL engine
├── Engine model            embedded library; one file = one database
├── sqlite3 CLI             interactive shell + dot-commands (.tables .schema .mode …)
├── Data types              storage classes + type affinity (dynamic typing)
├── DDL                     CREATE TABLE, constraints, PRIMARY KEY / rowid
├── DML                     INSERT / UPDATE / DELETE
├── SELECT                  WHERE, ORDER BY, GROUP BY, HAVING, LIMIT, JOIN
├── Indexes                 CREATE INDEX — speed lookups & sorts
├── Transactions            BEGIN / COMMIT / ROLLBACK — atomic units of work
├── PRAGMA                  journal_mode (DELETE vs WAL), foreign_keys …
└── UPSERT                  INSERT … ON CONFLICT DO UPDATE / DO NOTHING

1. Serverless single-file model

Serverless / embedded — no separate database process

SQLite is not a client-server engine: it is a C library linked directly into your program, and it reads and writes ordinary disk files with no separate server process to install, configure, or keep running. This makes it the natural choice for application files, local caches, and prototyping.

# There is nothing to start or stop — you just open a file:
sqlite3 demo.db

demo.db — one file is the entire database

A complete SQLite database — tables, indexes, and data — lives in a single cross-platform file on disk. Copying, emailing, or version-controlling that one file moves the whole database; deleting it deletes the database.

# Opening a non-existent path creates a new, empty database.
# (The file is written lazily, once the first table is created.)
sqlite3 /path/to/app.db

:memory: — a transient in-RAM database

Passing the special name :memory: (or no filename at all) creates a private database that lives only in RAM and disappears when the connection closes. It is handy for tests and throwaway experiments.

sqlite3 :memory:

2. The sqlite3 CLI & dot-commands

The sqlite3 shell mixes two languages: ordinary SQL statements (which must end in a semicolon) and dot-commands — shell directives that begin with a . and control the shell itself rather than the database. Dot-commands are typed on their own line with no trailing semicolon.

.open — close the current database and open another

Closes whatever database is currently attached and opens FILE instead, creating it if it does not exist. It lets you switch databases without leaving the shell.

.open demo.db

.tables — list table names

Lists the names of all tables and views in the attached databases, optionally filtered by a LIKE pattern. It is the quickest way to see what a database contains.

.tables
.tables user%

.schema — show the CREATE statements

Prints the CREATE TABLE and CREATE INDEX statements that define the schema, optionally for objects matching a pattern. This reveals the exact column types and constraints as SQLite recorded them.

.schema
.schema users

.mode — choose the output format

Sets how query results are rendered. Common modes include list (the default), column (aligned columns), box and table (Unicode grids), csv, and json. Pair it with .headers on to print column names.

.mode box
.headers on
SELECT * FROM users;

.import — load delimited data into a table

Reads a delimited file and inserts its rows into a table (.import FILE TABLE). With --csv it parses CSV per RFC 4180, and --skip N skips leading header lines; if the table does not yet exist it is created from the first line.

.import --csv --skip 1 people.csv users

.dump — render the database as SQL text

Emits the entire database (or matching objects) as a plain-text SQL script of CREATE and INSERT statements. Redirecting it to a file produces a portable, text-based backup that any SQLite build can replay.

.output backup.sql
.dump
.output stdout

.backup — make an online binary copy

Copies a live database to another file (.backup ?DB? FILE, where DB defaults to main) using SQLite's Online Backup API, so it is safe to run even while the database is in use. The result is a byte-for-byte usable database file.

.backup demo.db.bak

3. Type affinity & storage classes

SQLite uses dynamic typing: a value's type travels with the value, not with the column. Every stored value belongs to one of five storage classes, while each column has a type affinity that only influences — never rigidly enforces — how incoming values are stored.

Storage classes — the five value types

A value is stored as exactly one of: NULL, INTEGER (a 1–8 byte signed integer), REAL (an 8-byte IEEE float), TEXT (a UTF-8/UTF-16 string), or BLOB (raw bytes stored exactly as given). Use typeof() to see which class a value actually has.

SELECT typeof(1), typeof(1.0), typeof('x'), typeof(NULL), typeof(x'00');
-- integer|real|text|null|blob

Type affinity — how a declared type maps

Each column is assigned one of five affinities — TEXT, NUMERIC, INTEGER, REAL, or BLOB — from its declared type string. The rules, checked in order: a type containing INT → INTEGER; containing CHAR, CLOB, or TEXT → TEXT; containing BLOB or having no type at all → BLOB; containing REAL, FLOA, or DOUB → REAL; otherwise → NUMERIC.

CREATE TABLE t(a INTEGER, b VARCHAR(20), c REAL, d);
-- affinities:      INTEGER   TEXT        REAL    BLOB

STRICT — opt into rigid typing

Because affinity is only a hint, a column declared INTEGER will still accept the text 'hello' in an ordinary table. Declaring a table STRICT tells SQLite to reject values whose type does not match the column's declared type.

CREATE TABLE t(id INTEGER, name TEXT) STRICT;

4. DDL — CREATE TABLE & constraints

CREATE TABLE — define a table

Declares a table from a list of columns and optional constraints; add IF NOT EXISTS to avoid an error when it already exists, or TEMP to create it in the temporary database. Column type names are optional and only set affinity (§3).

CREATE TABLE IF NOT EXISTS users (
  id     INTEGER PRIMARY KEY,
  name   TEXT NOT NULL,
  email  TEXT UNIQUE,
  age    INTEGER CHECK (age >= 0),
  joined TEXT DEFAULT CURRENT_TIMESTAMP
);

Column constraints — NOT NULL, UNIQUE, CHECK, DEFAULT

Constraints guard data integrity: NOT NULL forbids NULLs, UNIQUE forbids duplicate values (NULLs are treated as distinct from each other), CHECK validates an expression on every insert and update, and DEFAULT supplies a value when none is given. A table may hold several UNIQUE constraints but at most one PRIMARY KEY.

CREATE TABLE product (
  sku   TEXT UNIQUE NOT NULL,
  price REAL CHECK (price > 0),
  stock INTEGER DEFAULT 0
);

INTEGER PRIMARY KEY — an alias for the rowid

Every ordinary table row has a hidden 64-bit rowid. When a single-column primary key is declared with the exact type INTEGER, that column becomes an alias for the rowid, giving the fastest possible lookups and inserts. (Declaring it BIGINT or INT instead does not create the alias.)

CREATE TABLE note (id INTEGER PRIMARY KEY, body TEXT);
INSERT INTO note(body) VALUES('hi');   -- id auto-fills to 1

AUTOINCREMENT — never reuse a rowid

A plain INTEGER PRIMARY KEY may reuse the ids of deleted rows; adding AUTOINCREMENT guarantees each new id is larger than any that has ever existed in the table, tracked through the internal sqlite_sequence table. The docs warn it "imposes extra CPU, memory, disk space, and disk I/O overhead and should be avoided if not strictly needed."

CREATE TABLE event (id INTEGER PRIMARY KEY AUTOINCREMENT, msg TEXT);

FOREIGN KEY — reference another table

A foreign key ties a column to a key in another table. Note that SQLite does not enforce foreign keys by default — you must turn enforcement on per connection with PRAGMA foreign_keys = ON (§10).

CREATE TABLE post (
  id      INTEGER PRIMARY KEY,
  user_id INTEGER REFERENCES users(id)
);

5. DML — INSERT / UPDATE / DELETE

INSERT — add rows

Adds one or more rows to a table; list the target columns explicitly (recommended) and supply several value tuples in a single statement. Omitted columns take their DEFAULT or NULL.

INSERT INTO users(name, email, age) VALUES
  ('Ada', 'ada@x.io', 36),
  ('Grace', 'grace@x.io', 45);

UPDATE — modify existing rows

Changes column values in rows that match the WHERE clause. Omitting WHERE updates every row, so always double-check the filter first.

UPDATE users SET age = age + 1 WHERE name = 'Ada';

DELETE — remove rows

Deletes the rows matching WHERE; as with UPDATE, an absent WHERE clause empties the whole table. Wrapping edits in a transaction (§9) lets you roll a mistake back.

DELETE FROM users WHERE age < 18;

6. Querying with SELECT

SELECT … WHERE — read and filter

Retrieves columns from one or more tables, with WHERE restricting which rows come back. It is the workhorse statement of any database.

SELECT name, age FROM users WHERE age >= 18;

ORDER BY … LIMIT — sort and paginate

ORDER BY sorts the result (ASC by default, DESC for descending), and LIMIT caps the row count — optionally with OFFSET to page through results.

SELECT * FROM users ORDER BY age DESC LIMIT 10 OFFSET 20;

GROUP BY … HAVING — aggregate in buckets

GROUP BY collapses rows that share a value into groups so aggregate functions (§11) run per group, and HAVING filters those groups — whereas WHERE filters individual rows before grouping.

SELECT age, COUNT(*) AS n
FROM users
GROUP BY age
HAVING n > 1
ORDER BY n DESC;

7. JOINs

INNER JOIN — keep only matching pairs

Combines rows from two tables where the ON condition matches, dropping any row that has no partner on either side. It is the default join and the one you reach for most often.

SELECT p.title, u.name
FROM post AS p
JOIN users AS u ON u.id = p.user_id;

LEFT JOIN — keep every left row

Returns all rows from the left table plus matching right-table columns, filling in NULL where no match exists. Use it to find "records with no related rows" — for example, users who have never posted.

SELECT u.name, p.title
FROM users AS u
LEFT JOIN post AS p ON p.user_id = u.id
WHERE p.id IS NULL;   -- users with no posts

8. Indexes

CREATE INDEX — speed up lookups

An index is a sorted auxiliary structure that lets SQLite find matching rows without scanning the whole table, dramatically speeding up WHERE filters, JOIN keys, and ORDER BY on the indexed columns. Add UNIQUE to also enforce uniqueness.

CREATE INDEX idx_post_user ON post(user_id);
CREATE UNIQUE INDEX idx_users_email ON users(email);

EXPLAIN QUERY PLAN — confirm the index is used

Prefixing a query with EXPLAIN QUERY PLAN shows whether SQLite performs a full table SCAN or a fast index SEARCH, so you can verify an index actually helps a given query before relying on it.

EXPLAIN QUERY PLAN
SELECT * FROM post WHERE user_id = 42;

💡 Rule of thumb: index the columns you filter or join on, but not every column — each index consumes space and slows down writes, since it must be updated on every INSERT, UPDATE, and DELETE.

9. Transactions

BEGIN … COMMIT — one atomic unit of work

A transaction groups several statements so they either all take effect (COMMIT, whose synonym is END) or none do. This atomicity guarantees the database is never left half-updated, even after a crash or power loss in the middle of a write.

BEGIN;
UPDATE account SET bal = bal - 100 WHERE id = 1;
UPDATE account SET bal = bal + 100 WHERE id = 2;
COMMIT;

ROLLBACK — undo everything since BEGIN

Discards all changes made since the matching BEGIN, restoring the database to its prior state. It is the safety net for "on second thought, don't apply any of that."

BEGIN;
DELETE FROM users;      -- oops, no WHERE
ROLLBACK;               -- nothing was actually deleted

Autocommit & transaction types

Outside an explicit BEGIN, each statement runs in its own automatic transaction (autocommit mode). An explicit BEGIN is DEFERRED by default — the write lock is not taken until the first write — while IMMEDIATE takes the write lock at once, which helps avoid SQLITE_BUSY races under concurrency.

BEGIN IMMEDIATE;
-- ... writes that must not race another writer ...
COMMIT;

10. PRAGMAs & journal modes

A PRAGMA queries or changes internal engine settings. Run one with no value to read the current setting, or with = value to change it.

PRAGMA journal_mode — DELETE vs WAL

The journal mode governs how SQLite guarantees atomic commits. The default is DELETE (a rollback journal file is written before changes and deleted at commit); other values are TRUNCATE, PERSIST, MEMORY, WAL, and OFF.

PRAGMA journal_mode;          -- read current mode (usually 'delete')

PRAGMA journal_mode=WAL — Write-Ahead Logging

WAL inverts the default scheme: "the original content is preserved in the database file and the changes are appended into a separate WAL file," which lets readers and a writer work concurrently and is often significantly faster. WAL creates companion -wal and -shm files, is persistent across connections, and periodically checkpoints changes back into the main file.

PRAGMA journal_mode=WAL;      -- returns 'wal' on success

PRAGMA foreign_keys=ON — enforce foreign keys

Foreign-key enforcement is off by default and must be enabled per database connection; without it, REFERENCES clauses are recorded but never checked. Set it right after opening the connection.

PRAGMA foreign_keys = ON;

PRAGMA table_info — inspect a table's columns

Returns one row per column with its name, declared type, NOT-NULL flag, default value, and primary-key position — the programmatic counterpart to .schema.

PRAGMA table_info(users);

11. Built-in functions

coalesce() / ifnull() — substitute for NULL

coalesce(X,Y,…) returns its first non-NULL argument (or NULL if all are NULL); ifnull(X,Y) is the two-argument shorthand. They are the idiomatic way to supply a fallback for a missing value.

SELECT name, ifnull(email, '(none)') FROM users;

length(), upper(), substr(), replace() — string helpers

Common text functions: length(X) counts Unicode code points, upper(X)/lower(X) change case, substr(X,Y,Z) extracts a slice starting at position Y, and replace(X,Y,Z) swaps every occurrence of Y with Z.

SELECT upper(name), substr(email, 1, 3) FROM users;

round(), abs(), typeof() — numeric & inspection

round(X,Y) rounds to Y decimals, abs(X) gives the magnitude, and typeof(X) reports the storage class of any expression — invaluable when debugging affinity surprises (§3).

SELECT round(3.14159, 2), abs(-5), typeof(1.0);

count(), sum(), avg(), group_concat() — aggregates

Aggregate functions reduce a group of rows to one value: count(), sum(), avg(), min(), max(), and group_concat(), which joins values into a delimited string. They are typically paired with GROUP BY (§6).

SELECT count(*), avg(age), group_concat(name, ', ') FROM users;

12. UPSERT (ON CONFLICT)

ON CONFLICT DO UPDATE — insert or update

UPSERT (added in SQLite 3.24.0) lets an INSERT that would violate a uniqueness constraint fall back to updating the existing row instead of failing. The conflict target must name a column covered by a PRIMARY KEY, UNIQUE constraint, or unique index, and the special excluded table refers to the row that would have been inserted.

INSERT INTO users(email, name) VALUES('ada@x.io', 'Ada L.')
ON CONFLICT(email) DO UPDATE SET name = excluded.name;

ON CONFLICT DO NOTHING — skip on conflict

The DO NOTHING variant silently skips a row that would violate a uniqueness constraint, leaving the existing row untouched and raising no error. It is the clean way to say "insert only if not already present."

INSERT INTO users(email, name) VALUES('ada@x.io', 'dup')
ON CONFLICT(email) DO NOTHING;

Reference links

• 📖 SQLite Documentation index
• ⚙️ Command Line Shell (sqlite3)
• ⚙️ SQL As Understood By SQLite (lang.html)
• ⚙️ Datatypes & type affinity
• ⚙️ Transactions (BEGIN/COMMIT/ROLLBACK)
• ⚙️ UPSERT (ON CONFLICT)
• ⚙️ Write-Ahead Logging (WAL)
• ⚙️ PRAGMA statements

Based on SQLite 3.x (current stable 3.53.3) | Docs collected 2026-07-03