npm package: @moneypot/pg-upgrade-schema
Description: Simple opinionated postgres schema upgrader
Latest version: 2.2.1

# 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

```bash
npm install pg-upgrade-schema
```

## Quick Start

1. Create a directory for your migration scripts:

```bash
mkdir -p migrations
```

2. Create migration scripts with version numbers as prefixes:

```bash
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.

```typescript
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:

```sql
-- 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:

```typescript
// 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:

```typescript
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

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

### Complex Migrations with TypeScript

```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:

```json
{
  "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.