DEV Community

Roberto Luna
Roberto Luna

Posted on

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

#playadev #buildinpublic #ai #devops

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

TL;DR: I replaced the hand‑crafted SQL block in the GitHub Actions workflow with a call to the actual migrate() function from db.ts. Adding a tiny migrate‑neon.ts script and wiring it into setup‑e2e.ts eliminated the schema‑drift errors that were breaking the Neon E2E pipeline.

The Problem

Our CI pipeline runs end‑to‑end (E2E) tests against a Neon PostgreSQL instance. After a series of schema changes for multi‑tenancy, the pipeline_automations table in Neon no longer matched the schema generated by our migration code. The symptom was a cascade of failing tests:

Error: column "organization_id" of relation "pipeline_automations" does not exist
    at Query._handleError (...)
Enter fullscreen mode Exit fullscreen mode

The root cause turned out to be schema drift: the workflow was applying a static SQL dump (e2e.yml) that was out of sync with the source‑of‑truth migrations defined in apps/api/src/db/db.ts. Because the dump was copied manually, any new migration was never reflected in the Neon test database, and the CI job kept failing.

What I Tried First

My first attempt was to manually edit the SQL block in .github/workflows/e2e.yml each time a migration changed. I added the missing organization_id column directly in the workflow:

- name: Apply manual migrations
  run: |
    psql ${{ secrets.NEON_DATABASE_URL }} <<SQL
      ALTER TABLE pipeline_automations ADD COLUMN organization_id UUID;
    SQL
Enter fullscreen mode Exit fullscreen mode

That worked temporarily, but it re‑introduced the same maintenance problem: every new migration required a manual copy‑paste. It also made the workflow brittle—any typo broke the whole job.

I also tried to run npm run migration:run from the workflow, but the script expected a compiled dist/ folder that wasn't built at that point, leading to a “module not found” error.

The Implementation

1. Create a tiny “run‑migration‑against‑Neon” script

File: apps/api/src/scripts/migrate-neon.ts

/**
 * migrate-neon.ts — Executes migrate() (the single source of truth for the schema,
 * defined in db.ts) against the Postgres URL passed via DATABASE_URL.
 *
 * Usage: DATABASE_URL=postgresql://... node dist/scripts/migrate-neon.js
 */
import { reinitPool, migrate, query } from "../db/db.js";

async function main() {
  if (!process.env.DATABASE_URL) {
    console.error("DATABASE_URL env var is required");
    process.exit(1);
  }

  // Re‑initialize the connection pool with the Neon URL
  reinitPool(process.env.DATABASE_URL);

  // Optional diagnostic: dump the current pipeline_automations schema
  const res = await query(`
    SELECT column_name, data_type
    FROM information_schema.columns
    WHERE table_name = 'pipeline_automations';
  `);
  console.log("Current pipeline_automations schema:", res.rows);

  // Run the real migration logic
  await migrate();
  console.log("✅ Migration applied successfully");
  process.exit(0);
}

main().catch(err => {
  console.error("❌ Migration failed:", err);
  process.exit(1);
});
Enter fullscreen mode Exit fullscreen mode

Key points:

  • reinitPool swaps the connection pool to the Neon URL at runtime.
  • query is used for a quick diagnostic dump (helpful when debugging future drifts).
  • The script lives in src/scripts/ so it can be compiled alongside the rest of the codebase.

2. Compile the script as part of the CI build

In package.json I added a new build target:

"scripts": {
  "build": "tsc -p tsconfig.build.json",
  "build:migrate": "tsc -p tsconfig.build.json --outDir dist/scripts migrate-neon.ts"
}
Enter fullscreen mode Exit fullscreen mode

The CI workflow now runs npm run build && npm run build:migrate before the E2E step, guaranteeing that dist/scripts/migrate-neon.js exists.

3. Replace the static SQL block in the workflow

File: .github/workflows/e2e.yml (excerpt)

jobs:
  e2e:
    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: Build API
        run: npm run build && npm run build:migrate

      - name: Apply migrations to Neon
        env:
          DATABASE_URL: ${{ secrets.NEON_DATABASE_URL }}
        run: |
          node dist/scripts/migrate-neon.js

      - name: Run E2E tests
        run: npm run test:e2e
Enter fullscreen mode Exit fullscreen mode

Notice that the previous 150‑line SQL block was completely removed (‑148 lines in the diff). The workflow now calls the real migration code, guaranteeing schema parity.

4. Ensure setup-e2e.ts also runs migrations locally

File: apps/api/src/__tests__/setup-e2e.ts

import { NestFactory } from "@nestjs/core";
import type { NestExpressApplication } from "@nestjs/platform-express";
import { AppModule } from "../../app.module";
import { reinitPool, migrate } from "../db/db";

// Existing env setup …
process.env.NEON_E2E_DATABASE_URL = process.env.NEON_E2E_DATABASE_URL ?? "";

// ---- NEW: run migrations before the test server starts ----
if (process.env.NEON_E2E_DATABASE_URL) {
  reinitPool(process.env.NEON_E2E_DATABASE_URL);
  await migrate();
  console.log("🔧 Local E2E DB migrated");
}

const app = await NestFactory.create<NestExpressApplication>(AppModule);
await app.init();
export default app;
Enter fullscreen mode Exit fullscreen mode

Previously setup-e2e.ts never invoked migrate(), which meant the local test database was always out of sync. Adding the call fixes the root cause of the CI failure.

5. Add a temporary diagnostic to confirm the schema

During the debugging phase I added a quick query call inside migrate-neon.ts (see the SELECT column_name … block). The console output now looks like:

Current pipeline_automations schema: [
  { column_name: 'id', data_type: 'uuid' },
  { column_name: 'organization_id', data_type: 'uuid' },
  …
]
✅ Migration applied successfully
Enter fullscreen mode Exit fullscreen mode

Once the drift was resolved, I left the diagnostic in place because it’s cheap and helps catch future mismatches early.

Key Takeaway

Never duplicate migration logic. By treating the migration code (migrate() in db.ts) as the single source of truth and invoking it directly from CI, you eliminate schema drift, reduce maintenance overhead, and keep your test environment faithful to production. A tiny wrapper script (migrate-neon.ts) is enough to bridge the gap between your codebase and external services like Neon.

What’s Next

  • Automate schema snapshots: Store a JSON dump of each table’s column definitions after every migration and compare it in a nightly job.
  • Add a “neon‑reset” step: Drop and recreate the Neon test database before each CI run to guarantee a clean slate.
  • Expose migration status via an internal endpoint for quick health checks during local development.

Roberto Luna Osorio – Full Stack Developer & Project Lead

Playa del Carmen, México

vibecoding #buildinpublic #typescript #nestjs #postgres #ci #e2e #docker

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

Repo: zaerohell/VS · 2026-09-07

#playadev #buildinpublic

Top comments (0)