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

1. Create a directory for your migration scripts:

mkdir -p migrations

2. 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

3. 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:

1. Creates a version tracking table if it doesn't exist
2. Acquires a PostgreSQL advisory lock to prevent concurrent migrations
3. Determines the current schema version from the tracking table
4. Loads all migration scripts from the specified directory
5. Applies each migration in order, starting from the current version + 1
6. Records each successful migration in the tracking table
7. 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", "[email protected]", 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"
  }
}

Migration Script Naming Convention

For better organization, use a consistent naming pattern:

{version}-{description}.{extension}

Examples:

• 001-initial-schema.sql
• 002-add-users-table.sql
• 003-add-email-verification.ts
• skip-004-create-triggers.sql - This will be ignored

The version number is parsed from the beginning of the filename, so you can use leading zeros for better sorting in file explorers.

Any file that doesn't start with a number will be ignored.