SQLite Examples

Three reproducible scenarios — Beginner to Advanced — you can run in the sqlite3 shell against a fresh demo.db.  |  ← Back to the reference manual

Target versionSQLite 3.x (current stable 3.53.3)
What this file addsHands-on, copy-paste scenarios that put the manual's concepts to work.
Reference docSQLite_Manual.html — each scenario links back to it
PrerequisiteThe sqlite3 CLI installed; every scenario starts from an empty folder.
● Beginner Getting Started Create a database file, define a table, insert rows, and read them back.
G-1 Beginner Create your first database
You have never used SQLite before. From an empty folder you want to create a database file called demo.db, make a users table, add a couple of people, and list them back — all inside the sqlite3 shell.
Elements used
sqlite3 demo.db CREATE TABLE INSERT SELECT .mode
Steps
  1. Open (and create) the database

    Run this in a terminal from an empty folder. Because demo.db does not exist yet, SQLite creates it and drops you into the interactive shell.

    sqlite3 demo.db
  2. Create a table

    At the sqlite> prompt, define a users table. The INTEGER PRIMARY KEY column becomes an alias for the rowid, so ids fill in automatically.

    CREATE TABLE users (
  id    INTEGER PRIMARY KEY,
  name  TEXT NOT NULL,
  email TEXT UNIQUE,
  age   INTEGER
);
  3. Insert a few rows

    Add two people in one statement. We omit id, letting SQLite assign 1 and 2.

    INSERT INTO users(name, email, age) VALUES
  ('Ada',   'ada@x.io',   36),
  ('Grace', 'grace@x.io', 45);
  4. Read them back in a readable grid

    Turn on a boxed layout with headers, then select everyone. Type .quit when you are done.

    .mode box
.headers on
SELECT * FROM users;
.quit
Nothing was written to disk until you ran the first statement — SQLite created demo.db in the current folder, and every statement outside an explicit BEGIN committed on its own (autocommit). After .quit the file remains on disk with your two rows in it.
Re-running sqlite3 demo.db reopens the same file with your data intact. If you want a clean slate, delete demo.db (and any demo.db-wal / demo.db-shm companions) and start again.
● Intermediate Daily Workflow Model a second table, join it, add an index, and run an aggregate query.
D-1 Intermediate Join two tables with an index
Building on the users table from scenario G-1 (same demo.db), you now want to record blog posts, list each post next to its author, speed up the author lookup with an index, and count how many posts each person wrote.
Elements used
FOREIGN KEY INNER JOIN CREATE INDEX GROUP BY count()
Steps
  1. Add a related table and some rows

    Reopen demo.db and create a post table whose user_id references users(id), then insert three posts.

    sqlite3 demo.db

CREATE TABLE post (
  id      INTEGER PRIMARY KEY,
  title   TEXT NOT NULL,
  user_id INTEGER REFERENCES users(id)
);

INSERT INTO post(title, user_id) VALUES
  ('Hello world',    1),
  ('On algorithms',  1),
  ('Compiler notes', 2);
  2. Join posts to their authors

    An INNER JOIN pairs each post with the matching user row, returning only posts that have an author.

    .mode box
.headers on
SELECT p.title, u.name
FROM post AS p
JOIN users AS u ON u.id = p.user_id;
  3. Index the join / filter column

    Create an index on post.user_id so lookups by author use a fast index SEARCH instead of scanning the table. EXPLAIN QUERY PLAN confirms it.

    CREATE INDEX idx_post_user ON post(user_id);

EXPLAIN QUERY PLAN
SELECT * FROM post WHERE user_id = 1;
  4. Aggregate: posts per author

    Group the join by author and count. LEFT JOIN would also include authors with zero posts; here we use an inner join for authors who have posted.

    SELECT u.name, count(p.id) AS posts
FROM users AS u
JOIN post AS p ON p.user_id = u.id
GROUP BY u.id
ORDER BY posts DESC;
The index is a sorted copy of post.user_id pointing back at each row, so SQLite jumps straight to matching posts rather than reading the whole table. GROUP BY u.id then buckets the joined rows per author before count() tallies each bucket.
SQLite records the REFERENCES clause but does not enforce it unless you run PRAGMA foreign_keys = ON; on the connection first. Enable it if you want inserts with an unknown user_id to be rejected.
● Advanced Power User Run an atomic transaction, switch the database to WAL mode, and take a safe backup.
P-1 Advanced Transaction, WAL, and backup
Still working in the same demo.db, you need to move a value between two rows so it can never be left half-done, switch the database to WAL mode for better read/write concurrency, and capture a safe on-disk backup you can restore from.
Elements used
BEGIN / COMMIT ROLLBACK journal_mode=WAL .backup
Steps
  1. Set up an accounts table

    Reopen demo.db and create a small account table with two funded rows to transfer between.

    sqlite3 demo.db

CREATE TABLE account (
  id  INTEGER PRIMARY KEY,
  bal INTEGER NOT NULL
);
INSERT INTO account(id, bal) VALUES (1, 500), (2, 0);
  2. Move money in one atomic transaction

    Wrap both updates in a transaction. Either both take effect at COMMIT, or — if anything fails — neither does, so the total is never wrong mid-transfer.

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

SELECT id, bal FROM account;   -- 1|400 and 2|100
  3. Switch the database to WAL mode

    Enable Write-Ahead Logging. It returns wal on success, is persistent across future connections, and creates demo.db-wal and demo.db-shm companion files.

    PRAGMA journal_mode=WAL;
PRAGMA journal_mode;           -- confirms: wal
  4. Take an online backup

    The .backup dot-command writes a consistent copy of the live database using the Online Backup API — safe to run even while the database is in use. Then leave the shell.

    .backup demo.db.bak
.quit
During the transaction the two UPDATEs are staged together; COMMIT makes them durable in a single atomic step, so a crash between the debit and credit could never leave money missing. In WAL mode those changes were appended to demo.db-wal and later checkpointed back into the main file, while .backup produced demo.db.bak — a complete, standalone database you can open with sqlite3 demo.db.bak.
To prove atomicity, retry step 2 but end with ROLLBACK; instead of COMMIT; — a following SELECT shows the balances unchanged, because nothing between BEGIN and ROLLBACK was applied.
See also: the reference sections on Transactions, PRAGMAs & journal modes, and the sqlite3 CLI.

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