Deploying a SQL Schema Without a Migration Tool — Directory Convention Instead of Flyway or Liquibase

Published Jul 6, 2026 · Updated Jul 7, 2026
by

The schema is done — now what? The reflex answer is Flyway or Liquibase. But for a small, database-centric project, a migration tool is often more machinery than the task calls for: its own version table, a runtime dependency, a file format you commit to. There is another way. Deploying a Postgres schema without a migration tool comes down to a few conventions, idempotent scripts and a small Bash runner — versioned in the repo, with no lock-in.

This article walks through a running example: the database layer of an open Claude Code starter kit whose db/ folder is built exactly this way.

The essentials up front:

  • The directory plus the file numbering is the source of truth — there is no central deploy.sql.
  • Idempotent, convergent scripts (CREATE … IF NOT EXISTS, ALTER TABLE … ADD COLUMN IF NOT EXISTS, CREATE OR REPLACE) replace up migrations — including later changes to existing tables.
  • Run-once transitions in predeploy/postdeploy handle data-dependent steps such as backfills — exactly once per database, guarded by a checksum.
  • Two small log tables (schema_apply_log, schema_change_log) replace a migration tool’s version table.
  • The CI proves the idempotency by deploying twice — and where a tool is still the better choice.

Prerequisites: Postgres and psql; the example uses Postgres 17, and the approach works from Postgres 14 onward. The building blocks shown here come from the open DI² starter kit on GitHub.

Contents

The Problem With “Just Use a Migration Tool”

Flyway, Liquibase and Alembic solve a real problem: they keep track of which change was applied to which environment in which order. For that they bring a version table, a fixed order via numbered or dated migration files, and checksums that prevent an already-applied file from being modified after the fact.

The price is a permanent dependency: another tool in the stack, a file format your code is bound to, and — in the case of the JVM tools — a runtime that has to tag along. For a large application with many environments and a whole team, that is the right trade-off. For a solo project or a small, database-centric repo it is overhead — and lock-in for a benefit you can replace with very little DIY.

The appeal of a migration tool boils down to three ingredients: order, idempotency and a history. You can get all three with convention instead of tooling — plus the checksum guard, exactly where it really counts. To set the scope: this approach deliberately replaces only the part of a migration tool that a small project actually needs — what tools deliver beyond that is laid out honestly in the last section.

The Convention: Directory and Numbering as the Source of Truth

Instead of a growing list of migration files, the db/ tree describes the desired state of the schema — one object per file, sorted by object type:

db/schemas/app/
├── predeploy/
├── tables/        001.customer.sql
│                  002.order.sql
│                  003.schema_apply_log.sql
│                  004.schema_change_log.sql
├── policies/
├── functions/     000.fn_is_null_or_empty.sql
├── procedures/    001.sp_ins_customer.sql
├── trigger/       000.tf_set_modified.sql
│                  001.tr_u_customer.sql
├── views/         001.vw_customer_overview.sql
├── data/          001.seed.sql
└── postdeploy/    202607050900.backfill-customer-notes.sql

Two rules turn this into a reliable source of truth:

  • The numbering is a table-group indicator, not a global sequence. All objects belonging to one table — the table itself, its procedures, its trigger — share the same NNN. Related things stay together, and a new table does not break the numbering of existing ones. In predeploy and postdeploy a timestamp prefix (YYYYMMDDHHMM) applies instead — what matters there is chronology, not the table group.
  • The load order is fixed: predeploy → tables → policies → functions → procedures → trigger → views → data → postdeploy. Views come after the functions they use; the seed data comes almost last. Strictly speaking, the order only has to resolve references that are checked at CREATE time — a view on a function, say. PL/pgSQL bodies are resolved at runtime; a procedure may therefore freely reference a view that comes later in the order.

The tree thus holds two kinds of files: object files in the sections in between — idempotent, describing the desired state — and transition scripts in the two special slots at the start and the end, which run exactly once per database. The next two sections take them on in turn.

The runner (further down) walks exactly these sections in this order, sorted by prefix within each section. That makes the order — the first ingredient of the migration tool — determined by the directory structure alone.

Idempotency Instead of Up Migrations

A migration tool applies each file exactly once. The convention-based approach inverts that: every script may run any number of times and always leads to the same result. That removes the which-file-already-ran bookkeeping for the entire object inventory — you simply always apply everything.

Four SQL constructs right in the object files make that possible: CREATE TABLE IF NOT EXISTS, CREATE OR REPLACE for functions and procedures, DROP … IF EXISTS followed by ADD for constraints that cannot be changed in place — and ALTER TABLE … ADD COLUMN IF NOT EXISTS for columns added after the initial CREATE.

  1: CREATE TABLE IF NOT EXISTS app.customer
  2: (
  3:     id            bigint         NOT NULL GENERATED ALWAYS AS IDENTITY
  4:    ,name          varchar(200)   NOT NULL
  5:    ,is_active     boolean        NOT NULL DEFAULT true
  6:
  7:    ,created_on    timestamptz    NOT NULL DEFAULT now()
  8:    ,created_by    varchar(100)   NOT NULL
  9:    ,modified_on   timestamptz        NULL
 10:    ,modified_by   varchar(100)       NULL
 11:
 12:    ,CONSTRAINT pk_customer       PRIMARY KEY (id)
 13:    ,CONSTRAINT chk_customer_name CHECK (length(trim(name)) > 0)
 14: );
 15:
 16: ALTER TABLE app.customer DROP CONSTRAINT IF EXISTS uq_customer_name;
 17: ALTER TABLE app.customer ADD  CONSTRAINT uq_customer_name UNIQUE (name);
 18:
 19: -- Convergent evolution: columns added after the initial CREATE
 20: ALTER TABLE app.customer ADD COLUMN IF NOT EXISTS notes varchar(500) NULL;

Line 1 only creates the table if it is missing. Lines 16 and 17 reset and re-create the unique constraint — idempotent, because a second run first removes the constraint and then re-creates it identically. This is not free, though: the repeated ADD CONSTRAINT rebuilds the unique index on every deploy and locks the table while doing so. For most tables that carries no weight; on very large tables or in high-availability systems it deserves a deliberate decision. Two more details that keep the scripts disciplined: the audit columns created_on/created_by/modified_on/modified_by (lines 7–10) are identical on every table, and the primary key uses GENERATED ALWAYS AS IDENTITY (line 3) instead of the older serial notation.

Line 20 is the most important building block, because it answers the question where convention-based approaches usually end: how does a change get into a table that already exists? CREATE TABLE IF NOT EXISTS no longer touches an existing table — a column simply written into the CREATE block later would never arrive on an environment that already has the table; the IF NOT EXISTS skips the table entirely. That is why later columns do not go into the CREATE block but to the end of the file, as an idempotent ALTER TABLE … ADD COLUMN IF NOT EXISTS (lines 19 and 20). The object file thus describes the desired state and converges every environment toward it: a fresh deploy creates the table completely, an existing environment only receives the missing column — both end up with the identical shape, and no data is lost. Columns are just the most common case: every object kind needs its own convergent pattern — constraints use the drop-then-add shown above, functions and procedures use CREATE OR REPLACE. What decides is always the same property: every statement in the file must be safely repeatable on every environment.

What the object file cannot express is data-dependent work: filling the new column for existing rows, setting data aside before a destructive rebuild, a SET NOT NULL that may only apply after the backfill. Such steps must not run on every deploy — they need the opposite of idempotency: exactly once per database. That is what run-once transitions are for.

Run-Once Transitions: Backfills and Other Data-Dependent Steps

The load order starts and ends with two special slots: predeploy runs before all object files, postdeploy after them. This is where transition scripts live — named with a timestamp instead of a table-group number and sorted chronologically. A predeploy script sets data aside before a rebuild touches it, for example; a postdeploy script fills a column that only exists as of this deploy. Here is the backfill for the notes column from the previous section:

  1: -- postdeploy/202607050900.backfill-customer-notes.sql
  2: UPDATE app.customer
  3: SET
  4:    notes = 'backfilled: pre-existing row'
  5: WHERE
  6:    notes IS NULL;

The WHERE guard (line 6) has two jobs: it never overwrites a value a user has set in the meantime — and it makes the script greenfield-safe. A freshly created database runs through the same transitions as well, once, in chronological order; on an empty schema a script like this simply has to pass as a no-op.

Unlike the object files, transitions run exactly once per database. The bookkeeping for that lives in a second small tracker table:

  1: CREATE TABLE IF NOT EXISTS app.schema_change_log
  2: (
  3:     id            bigint        NOT NULL GENERATED ALWAYS AS IDENTITY
  4:    ,filename      varchar(200)  NOT NULL
  5:    ,checksum      varchar(64)   NOT NULL
  6:    ,git_sha       varchar(64)   NOT NULL
  7:    ,applied_on    timestamptz   NOT NULL DEFAULT now()
  8:    ,applied_by    varchar(100)  NOT NULL DEFAULT current_user
  9:
 10:    ,CONSTRAINT pk_schema_change_log  PRIMARY KEY (id)
 11: );
 12:
 13: ALTER TABLE app.schema_change_log DROP CONSTRAINT IF EXISTS uq_schema_change_log_filename;
 14: ALTER TABLE app.schema_change_log ADD  CONSTRAINT uq_schema_change_log_filename UNIQUE (filename);

The filename is the run-once key (unique constraint, lines 13 and 14); the SHA-256 checksum taken at apply time (line 5) is the immutability guard. Before each transition, the deploy distinguishes three cases:

  • Not applied yet → execute, then record filename, checksum and Git SHA.
  • Applied, checksum unchanged → skip.
  • Applied, checksum changed → abort. An applied transition file is immutable; a correction is a new file.

With that, the approach reclaims a migration tool’s checksum guard exactly where it counts: for scripts that must never run twice. The applied files stay in the repo — the tracking makes sure they never run again. How this tracking carries as a pattern of its own — run-once scripts, checksums and the immutability rule in detail — is described in Tracking Schema Changes Without a Framework.

The most important use case is the NOT NULL column on a populated table, in three steps (expand/contract):

  1. Object file: add the column as nullable via ADD COLUMN IF NOT EXISTS.
  2. Postdeploy script: the backfill first, then the SET NOT NULL at the end of the same script — if the SET NOT NULL sat in the object file, it would run before the backfill on an existing environment and fail.
  3. Contract: once the transition has run on every environment, move the SET NOT NULL into the object file as an idempotent ALTER — the file then fully describes the desired state again, and a fresh deploy does not depend on the effect of an old transition script.

This expand/contract pattern has a deep dive of its own: Adding a NOT NULL Column to a Populated Table plays the three steps through in detail on a populated table.

The schema_apply_log — a Version Table in Miniature

The third ingredient of the migration tool — the history — is handled by a sibling of the schema_change_log: the schema_apply_log. It is append-only as well, but records one row per deploy run rather than one per transition file — the most recent row documents the environment’s last successful deploy.

  1: CREATE TABLE IF NOT EXISTS app.schema_apply_log
  2: (
  3:     id            bigint        NOT NULL GENERATED ALWAYS AS IDENTITY
  4:    ,db_version    varchar(50)   NOT NULL
  5:    ,git_sha       varchar(64)   NOT NULL
  6:    ,environment   varchar(10)   NOT NULL
  7:    ,note          varchar(500)      NULL
  8:    ,applied_on    timestamptz   NOT NULL DEFAULT now()
  9:    ,applied_by    varchar(100)  NOT NULL DEFAULT current_user
 10:
 11:    ,CONSTRAINT pk_schema_apply_log PRIMARY KEY (id)
 12: );

The git_sha (line 5) pins every run to a concrete commit — the question “which schema is on environment X?” is answered by a SELECT on the most recent row, and a drift check compares that SHA with what the code reports. This holds as long as deploys go exclusively through the runner — manual hotfixes applied past it naturally never show up here. applied_by (line 9) falls back to current_user because the deploy actor is the connection role, not an application user.

The starter kit wraps the insert in a small procedure that passes version, Git SHA and environment through as parameters — at its core, though, it is just one INSERT per run:

  1: INSERT INTO app.schema_apply_log
  2:     (
  3:         db_version
  4:        ,git_sha
  5:        ,environment
  6:        ,note
  7:     )
  8: VALUES
  9:     (
 10:         '1.0.0'
 11:        ,'a1b2c3d'
 12:        ,'local'
 13:        ,'initial deploy'
 14:     );

What the schema_apply_log deliberately does not do: it enforces nothing — it is an audit log, not a gate. The approach’s only hard gate sits in the schema_change_log, and it applies to transition files only. For the object files the principle stands: the directory convention holds because the team follows it, not because a tool enforces it.

The Apply Runner

The runner is a Bash script with two execution paths. It collects the object sections in fixed order and hands them to one psql call with ON_ERROR_STOP=1 — they are idempotent, so everything simply always runs, and the first error aborts the run. The transitions in predeploy and postdeploy run file by file instead, each with the run-once check from the previous section.

  1: set -e
  2:
  3: SCHEMA_DIR="db/schemas/app"
  4: SECTIONS=(tables policies functions procedures trigger views data)
  5: PSQL=(psql -v ON_ERROR_STOP=1)
  6:
  7: # Run-once transition: new -> apply + record; unchanged -> skip;
  8: # changed -> abort (applied files are immutable).
  9: run_once() {
 10:    local file="$1" name checksum applied
 11:    name="app/$(basename "$(dirname "$file")")/$(basename "$file")"
 12:    checksum="$(sha256sum "$file" | cut -d' ' -f1)"
 13:
 14:    applied="$("${PSQL[@]}" -tA -v "fname=$name" -f - <<'SQL'
 15: SELECT checksum FROM app.schema_change_log WHERE filename = :'fname';
 16: SQL
 17: )"
 18:
 19:    if [ -z "$applied" ]; then
 20:       echo "applying $name"
 21:       "${PSQL[@]}" -f "$file"
 22:       "${PSQL[@]}" -v "fname=$name" -v "sum=$checksum" -f - <<'SQL'
 23: INSERT INTO app.schema_change_log (filename, checksum, git_sha)
 24: VALUES (:'fname', :'sum', 'unknown');
 25: SQL
 26:    elif [ "$applied" = "$checksum" ]; then
 27:       echo "$name skipped (already applied)"
 28:    else
 29:       echo "Error: $name was modified after being applied." >&2
 30:       exit 1
 31:    fi
 32: }
 33:
 34: # 1. Ensure the tracker first: predeploy runs before tables -- on a fresh
 35: #    database the schema_change_log would not exist yet.
 36: "${PSQL[@]}" -f "$SCHEMA_DIR/tables/004.schema_change_log.sql"
 37:
 38: # 2. predeploy: file by file, run-once.
 39: for f in "$SCHEMA_DIR"/predeploy/*.sql; do
 40:    [ -e "$f" ] || continue
 41:    run_once "$f"
 42: done
 43:
 44: # 3. Object sections: batched into one psql call.
 45: files=()
 46: for section in "${SECTIONS[@]}"; do
 47:    dir="$SCHEMA_DIR/$section"
 48:    [ -d "$dir" ] || continue
 49:    for f in "$dir"/*.sql; do
 50:       [ -e "$f" ] || continue
 51:       files+=(-f "$f")
 52:    done
 53: done
 54: "${PSQL[@]}" "${files[@]}"
 55:
 56: # 4. postdeploy: file by file, run-once.
 57: for f in "$SCHEMA_DIR"/postdeploy/*.sql; do
 58:    [ -e "$f" ] || continue
 59:    run_once "$f"
 60: done

One detail deserves a second look: the runner ensures the tracker table before anything else (step 1) — predeploy runs before tables, and on a fresh database the schema_change_log would not exist yet. A chicken-and-egg problem better solved in the runner than in every transition file. The checksum values enter the statements as psql variables (:'fname'), not via string concatenation.

What matters is the separation of two runs that are often confused:

  • The schema deploy (above) connects as the schema owner and is idempotent — repeatable at will.
  • The one-off cluster bootstrap (creating the database, extensions, roles, schema, grants) connects as a superuser and is drop-and-recreate, i.e. not idempotent. It runs once when an environment is set up, not on every deploy.

This separation is why the schema deploy stays so relaxed about repetition: it never touches the database or the roles, only the objects inside the schema.

Proving It in CI

The best evidence that the convention holds and is not a makeshift workaround: a CI that spins up a real database and applies the schema on every pull request — and then deploys a second time to prove the idempotency.

  1: services:
  2:   postgres:
  3:     image: postgres:17
  4:     env:
  5:       POSTGRES_PASSWORD: pw
  6: steps:
  7:   - run: bash db/scripts/deploy.sh app local     # 1. deploy
  8:   - run: bash db/tests/run.sh   app local        # 2. object tests
  9:   - run: bash db/scripts/deploy.sh app local     # 3. re-deploy = idempotency and skip check

The Postgres instance only lives for the duration of the CI run: it starts as a service in the runner and disappears afterwards, with a committed throwaway password instead of a secret — which is why the CI also runs from fork pull requests. Step 3 is the actual trick: if the deploy runs through twice without errors, the idempotency is not claimed but demonstrated. And the double deploy checks both — the idempotency of the object files and the run-once path of the transitions: the second run must report every applied transition as skipped (already applied). A DDL script that stumbles on the second run is caught here — not later, on an environment that already had the table.

That second run is easy to reproduce: apply the schema twice in a row in a throwaway postgres:16-alpine container — the second run reports relation "customer" already exists, skipping for the objects, skipped (already applied) for the transitions, and exits with code 0.

The complete CI setup — workflow file, service container and the throwaway database as a quality gate before every deploy — is shown in GitHub Actions for Postgres Deploys.

Letting the Rules Grow With the Project

The convention does not live in the runner but in the rules files that describe it — in the starter kit these are the rules for the directory layout, SQL formatting and the apply procedure, which Claude Code loads for every database task. These files are not a static rulebook; they are a living document.

The feedback loop is simple: whenever something about a generated result is off — the formatting is wrong, an approach should apply always from now on, or only in one specific case — don’t just fix the one file; tell the agent to update the rule along with it: “put that into the rule.” Every correction thus moves from the single case into the convention, and the same point never has to be raised twice.

That is exactly how the rules in this article came about: convergent evolution is its own convention in the kit’s tables rule — including the expand/contract sequence — and the immutability of applied transition files is written down in the deployment rule so it applies to every future schema change. Both started as discussion points about a concrete result before they became rules. What belongs in rules and what in skills in general is covered in Skills vs. Rules in Claude Code.

When a Real Migration Tool Is the Better Choice

The honest part. The reflex answer “as soon as real data is involved, you need a migration tool” is not true across the board: convergent object files carry additive changes, run-once transitions cover backfills, data moves and follow-up constraints — together they also carry environments holding data you must not throw away. What remains are cases where a tool is the better choice:

  • Rollback and down migrations. The convention-based approach only knows forward — a failed change is corrected by the next change, not by a backward step. If you need orderly down paths, say for release rollbacks that undo schema changes, Flyway or Liquibase deliver them in a structured way.
  • Orchestrated large-scale rebuilds. Renaming or dropping a column while preserving data, splitting a table, a type change with a long backfill, zero-downtime rebuilds with several intermediate states — all of it can be expressed as a chain of transitions, but beyond a certain complexity, tool features such as dry runs, change-set orchestration and reports are worth their price.
  • Large teams and governance. The checksum guard here covers transition files only; the object files remain a matter of convention. Where many people work on many environments, several active branches hit the same database and the convention cannot be relied on, a tool enforces the discipline completely.
  • The stack already ships its own tool. If you work with Prisma, Django or Alembic anyway, your stack comes with a migration model — follow it instead of maintaining a parallel DIY approach.

The point is not to avoid migration tools but to choose them deliberately. Starting small and database-centric, the convention buys you time and independence — and you switch when the project demands it, not reflexively on day one.

FAQ

How do I deploy a Postgres schema without Flyway or Liquibase?

Describe the desired state as one object per file, sorted by object type, and apply the files in a fixed order with a short runner script. Every script is idempotent (CREATE … IF NOT EXISTS, CREATE OR REPLACE), so you simply always apply all files instead of keeping track of individual migrations. Data-dependent one-off steps such as backfills are handled by run-once transition scripts; two small log tables keep the history.

What replaces the migration tool’s version table?

Two small append-only tables. schema_apply_log records every deploy run (version, Git SHA, environment, timestamp) — the most recent row documents the last successful deploy. schema_change_log keeps track of every applied transition file: the filename is the run-once key, and the checksum blocks later changes to files that have already been applied.

How do I make SQL scripts idempotent?

With CREATE TABLE IF NOT EXISTS, CREATE OR REPLACE for functions and procedures, DROP CONSTRAINT IF EXISTS followed by ADD CONSTRAINT for constraints — and ALTER TABLE … ADD COLUMN IF NOT EXISTS for columns added after the initial CREATE. To test it, run the same script twice in a row — the second run must pass without errors.

How do I handle a new column on a table that holds data?

The column goes into the object file as an idempotent ALTER TABLE … ADD COLUMN IF NOT EXISTS — CREATE TABLE IF NOT EXISTS alone would skip the existing table. If the column needs a backfill or a SET NOT NULL, a run-once script in postdeploy takes over: the backfill first, then the SET NOT NULL at the end of the same script; later the SET NOT NULL moves into the object file as an idempotent ALTER.

How do I prevent concurrent deploys?

Unlike Flyway, the approach ships no built-in deploy locking. In practice the CI serializes: deploys only go through the pipeline, and the pipeline allows exactly one run per environment at a time (in GitHub Actions via a concurrency group). For an additional database-side safeguard, hold an advisory lock (pg_advisory_lock) for the duration of the run — though at that point you have reached the stage where a tool with built-in locking is the simpler answer.

Does this approach work with SQL Server too?

The principle — directory as the source of truth, idempotent scripts, run-once transitions, the log tables — carries over, but the syntax does not map one to one: SQL Server has no CREATE TABLE IF NOT EXISTS; you check with IF OBJECT_ID(…) IS NULL or IF NOT EXISTS (SELECT … FROM sys.objects) instead, and IF COL_LENGTH(…) IS NULL takes the role of ADD COLUMN IF NOT EXISTS for columns. Load order and run-once mechanics stay the same.