PostgreSQL + Neon DB: Schema Consistency

When I integrated Neon DB into the GeriCare DNB Portal, I expected database work to be the straightforward part. It wasn't. The first week in production, I had a deployment that worked perfectly in development break with a cryptic column-not-found error the moment it hit the live database. The schema had drifted.

Here's what I learned about managing PostgreSQL schema consistency when your database lives in the cloud and you're working across development, staging, and production environments.

Why Neon DB?

Neon is serverless PostgreSQL — you get a full Postgres-compatible database that scales to zero when idle. For the GeriCare DNB Portal, the advantages were clear:

• Branching: you can create an instant copy of your production database for testing
• No server to manage — just a connection string
• Works with every Postgres-compatible client (pg, Prisma, Drizzle, etc.)
• Generous free tier for internal tools

The schema drift problem

Early in the project, schema changes were made directly in the Neon console. ALTER TABLE here, a new column there. The development database accumulated these changes slowly, but the production database was only updated during deployments — if we remembered. After two weeks, the schemas were out of sync in ways that weren't obvious until runtime.

The fix: migration files

I stopped modifying schemas directly and started writing numbered SQL migration files:

-- migrations/001_initial_schema.sql
CREATE TABLE users (
  id         SERIAL PRIMARY KEY,
  email      VARCHAR(255) UNIQUE NOT NULL,
  role       VARCHAR(50) NOT NULL,
  centre_id  INTEGER REFERENCES centres(id),
  created_at TIMESTAMPTZ DEFAULT NOW()
);

-- migrations/002_add_logbook_table.sql
CREATE TABLE logbook_entries (
  id          SERIAL PRIMARY KEY,
  trainee_id  INTEGER REFERENCES users(id),
  case_type   VARCHAR(100),
  notes       TEXT,
  created_at  TIMESTAMPTZ DEFAULT NOW()
);

-- migrations/003_add_supervisor_notes.sql
ALTER TABLE logbook_entries
  ADD COLUMN supervisor_note TEXT,
  ADD COLUMN reviewed_at TIMESTAMPTZ;

A simple migrations tracker table records what's been applied:

CREATE TABLE IF NOT EXISTS _migrations (
  id         SERIAL PRIMARY KEY,
  filename   VARCHAR(255) UNIQUE NOT NULL,
  applied_at TIMESTAMPTZ DEFAULT NOW()
);

Running migrations on startup

I wrote a small Node.js script that runs before the Express server starts:

const fs   = require('fs');
const path = require('path');
const { pool } = require('./db');

async function runMigrations() {
  await pool.query(`
    CREATE TABLE IF NOT EXISTS _migrations (
      id SERIAL PRIMARY KEY,
      filename VARCHAR(255) UNIQUE NOT NULL,
      applied_at TIMESTAMPTZ DEFAULT NOW()
    )
  `);

  const files = fs.readdirSync('./migrations')
    .filter(f => f.endsWith('.sql'))
    .sort();

  for (const file of files) {
    const { rows } = await pool.query(
      'SELECT 1 FROM _migrations WHERE filename = $1', [file]
    );
    if (rows.length) continue; // already applied

    const sql = fs.readFileSync(path.join('./migrations', file), 'utf8');
    await pool.query(sql);
    await pool.query(
      'INSERT INTO _migrations (filename) VALUES ($1)', [file]
    );
    console.log(`✓ Applied: ${file}`);
  }
}

runMigrations().then(() => startServer());

Using Neon branching for safe changes

Neon's branching feature lets you create an instant copy of a database — including all data — in seconds. I used this before every schema change that touched existing data:

1. Create a branch from production in the Neon console
2. Test the migration script against the branch
3. Verify the app works correctly with the branch connection string
4. Run the same migration on production

The branch costs nothing while idle and is deleted after the change lands safely. It's the closest thing to a production dry-run I've found for a solo developer.

Schema drift is a silent killer in small projects. Setting up migration files takes an hour. Debugging a production outage from schema mismatch takes much longer — and it usually happens at the worst possible time.