CREATE TABLE, constraints, generated columns, foreign keys, defaults, and schema design patterns for production SQLite databases.
Build durable schemas with keys, checks, and sensible defaults.
Define a rowid-backed table with primary key and timestamps.
CREATE TABLE users (
id INTEGER PRIMARY KEY,
email TEXT NOT NULL UNIQUE,
display_name TEXT,
created_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP
);`INTEGER PRIMARY KEY` aliases the rowid and is a common pattern for app tables.
CREATE TABLE subscriptions (
id INTEGER PRIMARY KEY,
plan TEXT NOT NULL CHECK (plan IN ('free','premium','team')),
status TEXT NOT NULL CHECK (status IN ('active','canceled','past_due'))
);CHECK constraints help protect data quality even when multiple apps write to the same file.
CREATE TABLE posts (
id INTEGER PRIMARY KEY,
user_id INTEGER NOT NULL,
title TEXT NOT NULL,
body TEXT NOT NULL,
FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
);Remember to enable foreign key enforcement per connection with `PRAGMA foreign_keys = ON;`.
Optimize some composite-key tables.
CREATE TABLE locale_strings (
locale TEXT NOT NULL,
key TEXT NOT NULL,
value TEXT NOT NULL,
PRIMARY KEY (locale, key)
) WITHOUT ROWID;Useful when the natural primary key is composite and rowid storage adds little value.
Materialize or compute a derived value from another column.
CREATE TABLE events (
id INTEGER PRIMARY KEY,
payload TEXT NOT NULL,
event_type TEXT GENERATED ALWAYS AS (json_extract(payload, '$.type')) STORED
);Generated columns are handy with JSON payloads and can be indexed when stored.
Rename objects, add columns, and rebuild tables when needed.
ALTER TABLE users ADD COLUMN timezone TEXT;Adding a column is easy, but more complex schema changes often require a table rebuild.
ALTER TABLE users RENAME TO app_users;Useful during refactors or when aligning names with a new domain model.
ALTER TABLE app_users RENAME COLUMN display_name TO full_name;Modern SQLite supports renaming columns directly, which simplifies many migrations.
Canonical pattern for dropping constraints or changing types.
BEGIN;
CREATE TABLE users_new (
id INTEGER PRIMARY KEY,
email TEXT NOT NULL UNIQUE,
full_name TEXT,
timezone TEXT
);
INSERT INTO users_new (id, email, full_name, timezone)
SELECT id, email, display_name, timezone FROM users;
DROP TABLE users;
ALTER TABLE users_new RENAME TO users;
COMMIT;SQLite supports many schema changes, but rebuilds are still the practical pattern for more invasive migrations.