@moneypot/pg-upgrade-schema NPM

pg-upgrade-schema

A simple library for managing PostgreSQL database schema migrations.

Overview

pg-upgrade-schema helps you manage database schema changes in a controlled, version-tracked manner. It:

Tracks schema versions in a dedicated database table
Applies migrations in sequential order
Supports both SQL and JavaScript/TypeScript migrations
Uses PostgreSQL advisory locks to prevent concurrent migrations
Handles transactions and rollbacks automatically

Installation

npm install pg-upgrade-schema

Quick Start

Create a directory for your migration scripts:

mkdir -p migrations

Create migration scripts with version numbers as prefixes:

echo "CREATE TABLE users (id SERIAL PRIMARY KEY, name TEXT);" > migrations/1-create-users.sql
echo "ALTER TABLE users ADD COLUMN email TEXT;" > migrations/2-add-user-email.sql

Run the migrations in your application:

You usually want to do this on startup, only continuing if the migrations succeed.

import { Client } from "pg";
import { upgradeSchema } from "pg-upgrade-schema";

async function automigrate() {
  const client = new Client({
    connectionString: process.env.DATABASE_URL,
  });

  await client.connect();

  try {
    await upgradeSchema({
      pgClient: client,
      dirname: "./migrations",
    });
    console.log("Database schema is up to date!");
  } finally {
    await client.end();
  }
}

automigrate()
  .then(() => {
    server.listen(3000, () => {
      console.log("Server is running on port 3000");
    });
  })
  .catch((err) => {
    console.error("Failed to initialize database:", err);
    process.exit(1);
  });

How It Works

When you run upgradeSchema(), the library:

Creates a version tracking table if it doesn't exist
Acquires a PostgreSQL advisory lock to prevent concurrent migrations
Determines the current schema version from the tracking table
Loads all migration scripts from the specified directory
Applies each migration in order, starting from the current version + 1
Records each successful migration in the tracking table
Releases the advisory lock

Migration Scripts

Migration scripts can be:

SQL Files (*.sql)

Simple SQL statements that will be executed directly:

-- 1-create-users.sql
CREATE TABLE users (
  id SERIAL PRIMARY KEY,
  username TEXT NOT NULL UNIQUE,
  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

JavaScript/TypeScript Files (.js/.ts)

JavaScript or TypeScript modules that export a string or a function:

// 2-add-user-email.ts

// Option 1: Export a SQL string
export default "ALTER TABLE users ADD COLUMN email TEXT;";

// Option 2: Export a function that returns a SQL string
export default function () {
  return "ALTER TABLE users ADD COLUMN email TEXT;";
}

// Option 3: Export a function that executes custom logic
export default async function (pgClient) {
  // You can run multiple queries or custom logic
  await pgClient.query("ALTER TABLE users ADD COLUMN email TEXT");
  await pgClient.query("CREATE INDEX idx_users_email ON users(email)");

  // No need to return anything
}

Configuration Options

The upgradeSchema function accepts the following configuration options:

interface UpgradeConfig {
  // Required: PostgreSQL client connection
  pgClient: Client;

  // Required: Directory containing migration scripts
  dirname: string;

  // Optional: Name of the version tracking table
  // Default: "pg_upgrade_schema_versions"
  versionTableName?: string;

  // Optional: Schema where the version table is stored
  // Default: "pgupgradeschema"
  schemaName?: string;
}

Advanced Usage Examples

Custom Schema and Table Names

await upgradeSchema({
  pgClient,
  dirname: "./migrations",
  schemaName: "myapp",
  versionTableName: "schema_versions",
});

Complex Migrations with TypeScript

// 3-seed-admin-user.ts
import { hash } from "bcrypt";
import { Client } from "pg";

export default async function (pgClient: Client) {
  const passwordHash = await hash("admin123", 10);

  await pgClient.query(
    "INSERT INTO users(username, email, password_hash, is_admin) VALUES($1, $2, $3, $4)",
    ["admin", "admin@example.com", passwordHash, true]
  );

  return "CREATE INDEX idx_users_is_admin ON users(is_admin)";
}

Development Tips

Database Management Scripts

Add these to your package.json for easier development:

{
  "scripts": {
    "resetdb": "dropdb yourdbname && createdb yourdbname",
    "schema:dump": "pg_dump -d yourdbname --schema-only > schema.sql",
    "migrate": "node -r ts-node/register scripts/migrate.ts"
  }
}