Migrations npm

Great! You're now ready for database migrations, which are version-controlled SQL files that can be executed in order, only once, even across environments. Here's how you can set that up:

---

✅ Option: Use node-pg-migrate

This tool is built specifically for PostgreSQL, supports checksums/versioning, and integrates well with Neon.

---

Step-by-step Setup

1. Install node-pg-migrate

npm install node-pg-migrate --save-dev

2. Add scripts in package.json

"scripts": {
  "migrate": "node-pg-migrate up",
  "migrate-down": "node-pg-migrate down"
}

3. Create a config file migrate-config.js

import dotenv from 'dotenv';
dotenv.config();

export default {
    databaseUrl: process.env.DATABASE_URL,
    migrationsTable: 'pgmigrations',
    dir: 'migrations',
    direction: 'up',
    ssl: {
        rejectUnauthorized: false
    }
};

4. Generate a migration file

npx node-pg-migrate create init-schema --migrations-dir migrations

This creates a file like:
migrations/1689000000000-init-schema.js

5. Paste your SQL schema inside the file

Here’s a sample structure:

exports.shorthands = undefined;

exports.up = (pgm) => {
  pgm.sql(`
    -- Paste your full schema from canvas here
  `);
};

exports.down = (pgm) => {
  pgm.sql(`
    DROP TABLE IF EXISTS cards;
    DROP TABLE IF EXISTS deck_permissions;
    DROP TABLE IF EXISTS decks;
    DROP TABLE IF EXISTS users;
  `);
};

The down method rolls back the migration.

6. Run your migration

npm run migrate

This:

• Runs your migration exactly once.

• Creates a table called pgmigrations to track which migrations ran (with timestamp/checksum).

• Prevents re-running the same file again.

---

Let me know if you want me to convert your current schema into a working migration file.