SQLite basics.

Why SQLite

Embedded, single-file, zero-config. Ships with most languages. Great for: local apps, edge, tests, small/medium prod.

CLI

sqlite3 mydb.sqlite
.help
.tables
.schema users
.mode column
.headers on
.quit

sqlite3 mydb "SELECT * FROM users"
sqlite3 mydb < script.sql

Create table

CREATE TABLE users (
    id INTEGER PRIMARY KEY,
    email TEXT NOT NULL UNIQUE,
    name TEXT NOT NULL,
    created_at TEXT DEFAULT CURRENT_TIMESTAMP
);

CREATE INDEX idx_users_email ON users(email);

INTEGER PRIMARY KEY

Alias for ROWID; auto-incremented. Faster than separate AUTOINCREMENT.

Types (dynamic)

SQLite has no enforced types by default. Types:

  • INTEGER
  • REAL
  • TEXT
  • BLOB
  • NUMERIC

Use:

  • INTEGER for ints.
  • TEXT for strings, dates, JSON.
  • REAL for floats.
  • BLOB for binary.

STRICT tables (3.37+)

CREATE TABLE users (
    id INTEGER PRIMARY KEY,
    email TEXT NOT NULL
) STRICT;

Enforces types.

Insert / select / update / delete

INSERT INTO users (email, name) VALUES ('[email protected]', 'Alice');
INSERT INTO users (email, name) VALUES ('[email protected]', 'A') ON CONFLICT (email) DO UPDATE SET name = excluded.name;

SELECT * FROM users WHERE name LIKE 'A%';
SELECT count(*) FROM users;

UPDATE users SET name = 'B' WHERE id = 1;
DELETE FROM users WHERE id = 1;

RETURNING (3.35+)

INSERT INTO users (email, name) VALUES ('a@b', 'A') RETURNING id;
UPDATE users SET name = 'B' WHERE id = 1 RETURNING *;

JSON

INSERT INTO posts (data) VALUES (json('{"title":"x","tags":["a","b"]}'));

SELECT json_extract(data, '$.title') FROM posts;
SELECT data->>'$.title' FROM posts;        -- arrow operator

SELECT * FROM posts WHERE json_extract(data, '$.tags[0]') = 'a';

Date / time

SELECT date('now');
SELECT datetime('now');
SELECT strftime('%Y-%m-%d', 'now');
SELECT date('now', '-7 days');
SELECT datetime('now', 'localtime');

Stored as TEXT in ISO 8601.

Transactions

BEGIN;
INSERT ...;
COMMIT;

-- Or savepoint
SAVEPOINT s1;
ROLLBACK TO s1;

Foreign keys (off by default!)

PRAGMA foreign_keys = ON;

CREATE TABLE posts (
    id INTEGER PRIMARY KEY,
    user_id INTEGER REFERENCES users(id) ON DELETE CASCADE,
    title TEXT
);

Must enable per connection.

Useful PRAGMAs

PRAGMA journal_mode = WAL;         -- highly recommended
PRAGMA synchronous = NORMAL;       -- with WAL
PRAGMA cache_size = -64000;        -- ~64MB cache
PRAGMA foreign_keys = ON;
PRAGMA busy_timeout = 5000;

Backup

sqlite3 mydb.sqlite ".backup backup.sqlite"

Online; safe to run while DB in use.

Common mistakes

  • Foreign keys off by default.
  • Reading without WAL → reader blocks writer.
  • Multiple writers (one writer at a time).
  • AUTOINCREMENT when not needed (slower than ROWID alias).
  • Strict types not used.

Read this next

If you want my SQLite setup, it’s at rajpoot.dev .

Building something AI-, backend-, or data-heavy and want a second pair of eyes? I do consulting and freelance work — see my projects and ways to reach me at rajpoot.dev .