Fixing Neon Schema Drift in CI by Running the Real Migration Script

TL;DR:

I added a dedicated migration script (apps/api/src/scripts/migrate-neon.ts) that runs the same migrate() function used in production against the Neon test database before the test suite. This guarantees the schema in CI matches the source‑of‑truth and eliminates flaky “column does not exist” errors.

---

The Problem

During the third phase of multi‑tenancy for the PlayaMXCRM SaaS, our GitHub Actions pipeline started failing with errors like:

ERROR: relation "users" does not exist
ERROR: column "created_at" of relation "orders" does not exist

The failures appeared only in the Neon‑hosted PostgreSQL instance used for end‑to‑end (E2E) tests. Locally everything passed because I was running npm run prisma migrate dev, which only applied migrations to my local SQLite DB. The CI environment, however, spun up a fresh Neon database for each run, and the migration step was missing. The result was a classic schema drift: the code expected columns that simply weren’t there.

---

What I Tried First

My first instinct was to copy‑paste the Prisma CLI command that works locally:

npx prisma migrate dev --preview-feature

I added that line to the ci.yml before the npm test step. It seemed to run, but it created a new migration history in Neon that diverged from the production history. Prisma complained about a mismatched migration checksum, and the CI job aborted:

⚠️  Migration history diverges from the source of truth.

Running prisma migrate reset was also a dead end – it wipes the database, which is fine for tests, but the reset also drops the prisma_migrations table that our runtime code relies on to detect the current schema version.

Bottom line: using the Prisma CLI directly in CI created a second source of truth, which is exactly what we wanted to avoid.

---

The Implementation

1. Centralising the migration logic

In the API codebase we already have a runtime migration runner used by the production server to bring the DB up to date on startup:

// apps/api/src/migrations/index.ts
import { Connection } from 'typeorm';
import { migrateV1ToV2 } from './v1_to_v2';
import { migrateV2ToV3 } from './v2_to_v3';

export async function migrate(connection: Connection) {
  // The order is important – each step is idempotent
  await migrateV1ToV2(connection);
  await migrateV2ToV3(connection);
  // …future migrations
}

This function is the single source of truth for the schema. The production server calls it in src/main.ts:

await migrate(connection);

2. Creating a CLI entry point for CI

I extracted that logic into a tiny executable script that can be invoked from the CI runner:

// apps/api/src/scripts/migrate-neon.ts
#!/usr/bin/env ts-node

import { createConnection } from 'typeorm';
import { migrate } from '../../migrations';

async function main() {
  // Neon connection string is provided by the CI secret
  const connection = await createConnection({
    type: 'postgres',
    url: process.env.NEON_DATABASE_URL,
    ssl: { rejectUnauthorized: false },
  });

  try {
    await migrate(connection);
    console.log('✅ Neon schema migrated successfully');
  } catch (err) {
    console.error('❌ Migration failed:', err);
    process.exit(1);
  } finally {
    await connection.close();
  }
}

main();

Key decisions:

• ts-node shebang – No compilation step; the script runs directly in the CI container that already has ts-node installed.
• Environment‑driven URL – Keeps the script agnostic; it works for local dev (NEON_DATABASE_URL=postgres://...) and CI (${{ secrets.NEON_DATABASE_URL }}).
• Idempotent migrations – Each migration step checks for the existence of tables/columns before creating or altering them, so re‑running the script is safe.

3. Hooking the script into the CI workflow

The GitHub Actions file (.github/workflows/ci.yml) now includes a step that runs the script before any tests:

jobs:
  test:
    runs-on: ubuntu-latest
    steps:
      - uses: actions/checkout@v3

      - name: Set up Node
        uses: actions/setup-node@v3
        with:
          node-version: '20'

      - name: Install deps
        run: npm ci

      - name: Start Neon test DB
        env:
          NEON_DATABASE_URL: ${{ secrets.NEON_DATABASE_URL }}
        run: |
          # The database is provisioned automatically by Neon
          echo "Neon DB ready"

      - name: Migrate Neon schema
        env:
          NEON_DATABASE_URL: ${{ secrets.NEON_DATABASE_URL }}
        run: |
          chmod +x apps/api/src/scripts/migrate-neon.ts
          ./apps/api/src/scripts/migrate-neon.ts

      - name: Run tests
        env:
          NEON_DATABASE_URL: ${{ secrets.NEON_DATABASE_URL }}
        run: npm test

Notice the chmod +x – the script is stored in the repo as a plain .ts file, so we need to make it executable for the shebang to work.

4. Verifying the fix

After the migration step, the CI logs now show:

✅ Neon schema migrated successfully

Followed by a clean test run:

PASS  apps/api/src/__tests__/order.service.spec.ts
PASS  apps/api/src/__tests__/user.service.spec.ts
Test Suites: 2 passed, 2 total

No more “column does not exist” errors.

---

Key Takeaway

Never let your CI environment drift from the migration code that runs in production. By exposing the same migrate() function as a CLI script and invoking it in the pipeline, you guarantee that the test database schema is always an exact replica of the production schema, eliminating flaky failures caused by hidden drift.

---

What's Next

• Add a pre‑commit hook (husky + lint-staged) that runs the migration script against a local Neon sandbox, catching drift early.
• Version the migration script using semantic versioning and store the hash in the CI artifact, so we can audit which migration version was applied to each test run.
• Automate rollback testing by adding a second step that runs the inverse migrations (if any)

---

Part of my Build in Public series — sharing the real process of building SaaS projects from Playa del Carmen, México.

Repo: zaerohell/content-automation · 2026-09-08

#playadev #buildinpublic