SQL = SSOT

What's the truth for your DB schema? ORM models? live DB? migration files? Choosing "declarative SQL files" keeps things simple and reproducible.

1. Three options

Approach	Truth	Feel
ORM-first	Entity classes	Rails/Django. Auto-sync with synchronize:true
Migration-first	Sequential files (Flyway/Liquibase)	Great history
Declarative SQL-first	CREATE TABLE IF NOT EXISTS files	Reproducible, manual ALTER

2. warragon picks Declarative SQL-first

CREATE TABLE IF NOT EXISTS public.posts (
  id BIGSERIAL PRIMARY KEY,
  slug TEXT NOT NULL,
  category_slug TEXT NULL,
  language CHAR(2) NOT NULL DEFAULT 'ko',
  content_kind VARCHAR(10) NOT NULL DEFAULT 'note'
    CHECK (content_kind IN ('note', 'blog', 'edu')),
  title TEXT NOT NULL,
  content_md TEXT NOT NULL DEFAULT '',
  published BOOLEAN NOT NULL DEFAULT FALSE,
  created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  updated_at TIMESTAMPTZ NOT NULL DEFAULT now()
);
CREATE INDEX IF NOT EXISTS idx_posts_published
  ON posts (language, content_kind, published);

3. Rules

• CREATE TABLE IF NOT EXISTS only
• CREATE INDEX IF NOT EXISTS only
• No DROP TABLE
• No persistent ALTER.sql files

4. Adding a column to a live table

1. ALTER the live DB:

docker exec prod-postgres psql -U ... \
  -c "ALTER TABLE posts ADD COLUMN IF NOT EXISTS subtitle TEXT;"

2. Add the column inside the CREATE TABLE in the SSOT file.
3. Do NOT keep an ALTER TABLE line in the file.

5. Exception — forward-ref FK

CREATE TABLE A (..., b_id BIGINT);
CREATE TABLE B (id BIGSERIAL PRIMARY KEY);
DO $
BEGIN
  IF NOT EXISTS (SELECT 1 FROM pg_constraint WHERE conname = 'a_b_fk') THEN
    ALTER TABLE A ADD CONSTRAINT a_b_fk FOREIGN KEY (b_id) REFERENCES B(id);
  END IF;
END $;

Only case DO $ block is allowed.

6. Why SQL-first

• Reproducible
• Reviewable in git diffs
• ORM-independent (Node, Java, Python can share)
• Manual psql checks match expectations

7. Why not ORM-first

ORM → SQL is a black-box translation. Across languages, it's hard to reconcile. Manual \d table results may diverge from entities.

8. Why not Flyway-style migration-first

Great in many teams. But live schema = cumulative migrations. Checking a column means reading many files. Fresh installs replay all migrations.

warragon prefers "see current schema at a glance + fresh install fast".

9. Update four places together

1. admin/sql/<domain>/NN.sql     (SSOT)
2. seed.ts
3. public site types
4. admin types

Check in PR. Freeze a count test in CI.

10. Seeds

Seed from code with idempotent INSERTs.

for (const c of CATEGORIES_SEED) {
  await query(`INSERT INTO categories (...) VALUES (...) ON CONFLICT ... DO NOTHING`, [...]);
}

11. Gotchas

• Permanent ALTER files
• CREATE vs live DB drift
• New UNIQUE → existing duplicate conflicts
• FK order errors → use forward-ref exception

Closing

SSOT is a promise "only this one place is the truth". Declarative SQL files express it in the simplest file.

Next

• 05-progressive-refactor