Creating the database: schema, migrations and seed data

From design to a real database

The schema is the full list of tables, columns and rules (NOT NULL, UNIQUE, foreign keys) in a database. In module 2 you wrote it down. To make it real, you run the CREATE TABLE statements against a database.

You could type them once into a database tool and forget them. That breaks quickly: your teammate's laptop, the test server and the production server would each need the same typing, and nobody would know which changes each one already has.

-- migrations/001_create_users_notes.sql
CREATE TABLE users (
  id INTEGER PRIMARY KEY,
  name TEXT NOT NULL,
  email TEXT UNIQUE NOT NULL,
  password_hash TEXT NOT NULL,
  created_at TEXT
);

CREATE TABLE notes (
  id INTEGER PRIMARY KEY,
  user_id INTEGER NOT NULL REFERENCES users(id),
  title TEXT NOT NULL,
  body TEXT,
  pinned INTEGER DEFAULT 0,
  created_at TEXT,
  updated_at TEXT
);

What is a migration?

A migration is a small file that changes the schema by one step: create a table, add a column, add an index. Files are numbered (001, 002, 003...) or start with a date, so everyone runs them in the same order. They live in the git repository next to the code.

1. A migration runner (a small program) reads the migrations folder.
2. It looks at a tracking table in the database, often called schema_migrations, which lists the files already applied.
3. It runs only the missing files, oldest first, usually each inside a transaction (all of it happens, or none of it).
4. After each file succeeds, it writes the file name into the tracking table, so it never runs twice.

Up and down

The "up" part of a migration makes the change. Some tools also let you write a "down" part that undoes it, useful on your laptop when you want to try again. On a live server, teams usually fix a mistake with a new "up" migration instead.

-- migrations/002_add_archived.sql
-- up
ALTER TABLE notes ADD COLUMN archived INTEGER NOT NULL DEFAULT 0;

-- down (undo)
ALTER TABLE notes DROP COLUMN archived;

ALTER TABLE changes an existing table. When you add a NOT NULL column to a table that already has rows, give it a DEFAULT, or the database will refuse because the old rows would have no value.

Seed data

Seed data is a set of sample rows (a few users and notes) loaded into a development or test database so the app has something to show. Keep seeds in a separate file or script, not inside migrations, because migrations also run on production and production must not get fake users.

-- seeds/dev.sql (development only)
INSERT INTO users (id, name, email, password_hash, created_at)
VALUES (1, 'Asha', 'asha@example.com', 'hash-a', '2026-01-05 10:00');

INSERT INTO notes (user_id, title, body, pinned, created_at, updated_at)
VALUES (1, 'Physics formulas', 'Ohm law: V = IR', 1, '2026-02-01 08:00', '2026-02-01 08:00');