Schema Design and Migrations

Schema design is deciding what tables to create, what columns they have, and what rules apply to the data. A well-designed schema prevents bad data and makes queries efficient.

CREATE TABLE

CREATE TABLE users (
  id          INTEGER PRIMARY KEY AUTOINCREMENT,
  username    TEXT    NOT NULL UNIQUE,
  password_hash TEXT  NOT NULL,
  created_at  TEXT    NOT NULL DEFAULT (datetime('now'))
);

Anatomy of this statement:

• INTEGER PRIMARY KEY AUTOINCREMENT — auto-incrementing unique ID
• TEXT NOT NULL — required string field
• UNIQUE — no two rows can have the same value
• DEFAULT (datetime('now')) — automatically set to current time if not provided

SQLite data types

SQLite uses flexible typing. The practical types:

Use case	SQLite type
Integer numbers	INTEGER
Decimal numbers	REAL
Text strings	TEXT
Binary data	BLOB
Booleans	INTEGER (0 or 1)
Dates/times	TEXT (ISO 8601: '2024-01-15T10:30:00Z')

The Bulletin schema

-- users
CREATE TABLE IF NOT EXISTS users (
  id            INTEGER PRIMARY KEY AUTOINCREMENT,
  username      TEXT    NOT NULL UNIQUE,
  password_hash TEXT    NOT NULL,
  created_at    TEXT    NOT NULL DEFAULT (datetime('now'))
);

-- posts
CREATE TABLE IF NOT EXISTS posts (
  id          INTEGER PRIMARY KEY AUTOINCREMENT,
  title       TEXT    NOT NULL,
  body        TEXT    NOT NULL,
  user_id     INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  upvotes     INTEGER NOT NULL DEFAULT 0,
  created_at  TEXT    NOT NULL DEFAULT (datetime('now'))
);

-- comments
CREATE TABLE IF NOT EXISTS comments (
  id          INTEGER PRIMARY KEY AUTOINCREMENT,
  body        TEXT    NOT NULL,
  user_id     INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  post_id     INTEGER NOT NULL REFERENCES posts(id) ON DELETE CASCADE,
  created_at  TEXT    NOT NULL DEFAULT (datetime('now'))
);

-- upvote tracking (prevents duplicate upvotes)
CREATE TABLE IF NOT EXISTS post_upvotes (
  user_id     INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  post_id     INTEGER NOT NULL REFERENCES posts(id) ON DELETE CASCADE,
  PRIMARY KEY (user_id, post_id)  -- composite PK prevents duplicates
);

Key design decisions:

• REFERENCES users(id) ON DELETE CASCADE — when a user is deleted, their posts/comments are too
• post_upvotes has a composite primary key (user_id, post_id) — prevents the same user upvoting the same post twice
• IF NOT EXISTS — safe to run the schema file multiple times

A simple migration system

A migration applies schema changes once and records that it ran:

src/db/migrate.ts

import { db } from './index.js'
import { readFileSync } from 'fs'
import { join } from 'path'

// Create migrations tracking table
db.exec(`
  CREATE TABLE IF NOT EXISTS migrations (
    id   INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT    NOT NULL UNIQUE,
    ran_at TEXT  NOT NULL DEFAULT (datetime('now'))
  )
`)

function migrate(name: string, sql: string) {
  const already = db.prepare('SELECT id FROM migrations WHERE name = ?').get(name)
  if (already) return  // already ran

  db.exec(sql)
  db.prepare('INSERT INTO migrations (name) VALUES (?)').run(name)
  console.log(`Migration applied: ${name}`)
}

// Apply migrations
migrate('001_initial_schema',
  readFileSync(join(process.cwd(), 'src/db/schema.sql'), 'utf8')
)

Run this at startup in src/index.ts:

import './db/migrate.js' // runs before app.listen()

Exercise

1. Create src/db/schema.sql with the Bulletin schema above.
2. Create src/db/migrate.ts with the migration system.
3. Import the migration in src/index.ts (before app.listen).
4. Run npm run dev and verify the tables are created. Check the .db file in DB Browser.

Recap

• CREATE TABLE IF NOT EXISTS defines a table’s structure — safe to re-run.
• NOT NULL enforces required fields; UNIQUE prevents duplicates.
• REFERENCES table(id) ON DELETE CASCADE creates a foreign key with cascade deletion.
• A composite primary key PRIMARY KEY (a, b) prevents duplicate (a, b) pairs.
• Run CREATE TABLE statements at startup — a simple migration system tracks what’s been applied.