SQLite DDL and Schema Design Cheatsheet

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

# Define a rowid-backed table with primary key and timestamps.

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'))
);

# Validate allowed values at the database layer.

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
);

# Reference a parent table from a child table.

CREATE TABLE locale_strings (
  locale TEXT NOT NULL,
  key TEXT NOT NULL,
  value TEXT NOT NULL,
  PRIMARY KEY (locale, key)
) WITHOUT ROWID;

# Optimize some composite-key tables.

CREATE TABLE events (
  id INTEGER PRIMARY KEY,
  payload TEXT NOT NULL,
  event_type TEXT GENERATED ALWAYS AS (json_extract(payload, '$.type')) STORED
);

# Materialize or compute a derived value from another column.

ALTER TABLE users ADD COLUMN timezone TEXT;

# Append a new nullable or defaulted column.

ALTER TABLE users RENAME TO app_users;

# Change the table name without recreating data.

ALTER TABLE app_users RENAME COLUMN display_name TO full_name;

# Adjust a column name in-place.

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;

# Canonical pattern for dropping constraints or changing types.