### Initial Migration SQL Example (New Project)

Source: https://github.com/clickup/pg-mig/blob/main/README.md

Example of SQL DDL statements for an initial migration's up and down files. The 'up' file creates a 'users' table, and the 'down' file drops it. pg-mig automatically sets the search path.

```sql
-- 20251203493744.initial.public.up.sql
CREATE TABLE users(
  id bigserial PRIMARY KEY,
  email varchar(256) NOT NULL
);
```

```sql
-- 20251203493744.initial.public.dn.sql
DROP TABLE users;
```

--------------------------------

### pg-microsharding Integration: Before Migration Script (SQL)

Source: https://github.com/clickup/pg-mig/blob/main/docs/README.md

This SQL snippet shows the setup required before running migrations with the pg-microsharding library. It includes creating a schema, setting the search path, and running the pg-microsharding setup and before migration functions.

```sql
-- mig/before.sql
CREATE SCHEMA IF NOT EXISTS microsharding;
SET search_path TO microsharding;
\ir ../pg-microsharding/sql/pg-microsharding-up.sql
SELECT microsharding.microsharding_migration_before();
```

--------------------------------

### CLI Example: pg-mig Usage

Source: https://github.com/clickup/pg-mig/blob/main/docs/functions/main.md

Demonstrates various ways to invoke the pg-mig command-line tool for different migration operations. This includes creating new migrations, undoing specific migrations, and listing available migrations.

```bash
pg-mig --make=my-migration-name@sh
pg-mig --make=other-migration-name@sh0000
pg-mig --undo=20191107201239.my-migration-name.sh
pg-mig --list
pg-mig --list=digest
pg-mig
```

--------------------------------

### pg-microsharding: Before Migration Setup

Source: https://github.com/clickup/pg-mig/blob/main/README.md

SQL script to be run before migrations when using the pg-microsharding library. It sets up the microsharding schema, includes the pg-microsharding up migration scripts, and calls the initial setup function.

```sql
-- mig/before.sql
CREATE SCHEMA IF NOT EXISTS microsharding;
SET search_path TO microsharding;
\ir ../pg-microsharding/sql/pg-microsharding-up.sql
SELECT microsharding.microsharding_migration_before();

```

--------------------------------

### pg-mig Configuration File Example (TypeScript)

Source: https://github.com/clickup/pg-mig/blob/main/docs/README.md

Illustrates how to configure pg-mig using a TypeScript configuration file (`pg-mig.config.ts`). This example shows how to dynamically fetch connection details from an 'Ent Framework' cluster configuration and set environment-like variables for the migration tool. It also includes an `after` hook for post-migration tasks.

```typescript
import { cluster } from "ents/cluster";

export default async function(action: "apply" | "undo" | string) {
  const islands = cluster.options.islands();
  const firstNode = islands[0].node[0];
  return {
    PGHOST: islands
      .map((island) => island.nodes.map(({ host }) => host)
      .flat()
      .join(","),
    PGPORT: 5432, // we don't want to use pgbouncer port here
    PGUSER: firstNode.user,
    PGPASSWORD: firstNode.password,
    PGDATABASE: firstNode.database,
    PGSSLMODE: firstNode.ssl ? "prefer" : undefined,
    PGMIGDIR: `${__dirname}/mig`,
    PGCREATEDB: process.env.NODE_ENV === "development",
    after: async () => {
      // Will be called after the migrations succeed. Here, you can e.g.
      // upsert some initial objects in the database if they don't exist.
    },
  };
}
```

--------------------------------

### pg-mig Command Line Usage Example

Source: https://github.com/clickup/pg-mig/blob/main/docs/README.md

Demonstrates the various command-line arguments available for the pg-mig tool. These options control aspects like migration directory, host configuration, user credentials, database name, and migration actions like applying, undoing, or listing.

```bash
pg-mig
  [--migdir=path/to/my-migrations/directory]
  [--hosts=host1,host2,...]
  [--hosts=host[:port][/db],...]
  [--hosts=postgres://[user][:password][@]host[:port][/db],...]
  [--port=5432]
  [--user=user-that-can-apply-ddl]
  [--pass=password]
  [--db=my-database-name]
  [--createdb]
  [--undo=20191107201239.my-migration-name.sh]
  [--make=my-migration-name@sh]
  [--list | --list=digest]
  [--parallelism=8]
  [--dry]
```

--------------------------------

### pg-mig Migration File Naming Convention Example

Source: https://github.com/clickup/pg-mig/blob/main/README.md

Illustrates the standard naming format for pg-mig migration files. The format includes a timestamp, a descriptive name, an optional schema prefix, and an 'up' or 'dn' indicator for migration direction.

```sql
mig/
  before.sql
  20231017204837.do-something.sh.up.sql
  20231017204837.do-something.sh.dn.sql
  20241107201239.add-table-abc.sh0000.up.sql
  20241107201239.add-table-abc.sh0000.dn.sql
  20241201204837.change-other-thing.sh.up.sql
  20241201204837.change-other-thing.sh.dn.sql
  20251203493744.install-pg-extension.public.up.sql
  20251203493744.install-pg-extension.public.dn.sql
  after.sql
```

--------------------------------

### SQL Concurrent Index Creation and Reversal

Source: https://context7.com/clickup/pg-mig/llms.txt

This example shows how to create a unique index concurrently without blocking write operations, which is crucial for large tables. It also includes the corresponding down migration to drop the index. The pattern involves committing the current transaction, creating the index, and then starting a new transaction to ensure pg-mig can track the migration version.

```sql
-- File: mig/20241028194500.add-concurrent-index.sh.up.sql
-- $parallelism_per_host = 2

-- Close transaction that pg-mig opens automatically
COMMIT;

-- Drop any broken index from previous failed attempt
DROP INDEX IF EXISTS users_email_verified_idx;

-- Create index without blocking writes (can take hours on large tables)
CREATE UNIQUE INDEX CONCURRENTLY users_email_verified_idx
  ON users(email)
  WHERE email_verified = true;

-- Reopen transaction so pg-mig can record version
BEGIN;

-- This makes migration non-transactional with retry-safe pattern
-- If CREATE INDEX CONCURRENTLY fails, DROP INDEX removes broken index
```

```sql
-- File: mig/20241028194500.add-concurrent-index.sh.dn.sql

COMMIT;
DROP INDEX CONCURRENTLY IF EXISTS users_email_verified_idx;
BEGIN;
```

--------------------------------

### TypeScript: Programmatic API for pg-mig Migrations

Source: https://context7.com/clickup/pg-mig/llms.txt

This TypeScript code demonstrates how to use the pg-mig programmatic API from Node.js. It shows how to configure and run migrations, including setting up hosts, migration directory, parallelism, and defining 'after' hooks. It also includes examples for creating new migrations, undoing migrations, and checking digests.

```typescript
import { migrate, MigrateOptions } from "@clickup/pg-mig";

async function runMigrations() {
  const options: MigrateOptions = {
    migDir: "./database/migrations",
    hosts: [
      "postgres://user:pass@pg-master-1.internal:5432/production",
      "postgres://user:pass@pg-master-2.internal:5432/production"
    ],
    parallelism: 10,
    dry: false,
    createDB: false,
    action: {
      type: "apply",
      after: [
        async () => {
          console.log("First after hook");
          await updateCachedStatistics();
        },
        async () => {
          console.log("Second after hook");
          await notifyServices();
        }
      ]
    }
  };

  const success = await migrate(options);

  if (!success) {
    console.error("Migration failed!");
    process.exit(1);
  }

  console.log("Migration completed successfully");
}

// Make new migration programmatically
async function createMigration() {
  await migrate({
    migDir: "./database/migrations",
    hosts: ["localhost"],
    action: { type: "make", name: "add-new-table@sh" }
  });
}

// Undo last migration
async function undoLastMigration() {
  await migrate({
    migDir: "./database/migrations",
    hosts: ["localhost"],
    action: { type: "undo", version: "20241028193000.add-new-table.sh" }
  });
}

// Get digest
async function checkDigest() {
  await migrate({
    migDir: "./database/migrations",
    hosts: [], // Not needed for digest-only action
    action: { type: "digest" }
  });
}

```

--------------------------------

### SQL Down Migration Example for Reversing Changes

Source: https://context7.com/clickup/pg-mig/llms.txt

This SQL snippet demonstrates a down migration file designed to reverse changes made by an up migration. It includes commands to drop triggers, functions, indexes, and tables. Using `IF EXISTS` ensures idempotency, making the migration safe to retry if it fails.

```sql
-- File: mig/20241028193000.add-users-table.sh.dn.sql
-- Must reverse all changes from up migration

DROP TRIGGER IF EXISTS users_updated_at ON users;
DROP FUNCTION IF EXISTS update_updated_at();
DROP INDEX IF EXISTS users_email_idx;
DROP TABLE IF EXISTS users;

-- Use IF EXISTS for idempotency
-- Down migrations should be safe to retry if they fail partway
```

--------------------------------

### JavaScript pg-mig Configuration with File Reading

Source: https://context7.com/clickup/pg-mig/llms.txt

This JavaScript configuration file (`pg-mig.config.js`) provides a simpler setup, reading database credentials from a local JSON file. It configures connection parameters using environment variables or defaults, specifies the migration directory, and conditionally defines an `after` hook that logs a success message only when migrations are applied (not undone).

```javascript
// File: pg-mig.config.js
const { readFileSync } = require("fs");

module.exports = async function(action) {
  const credentials = JSON.parse(
    readFileSync(__dirname + "/.secrets/db-credentials.json")
  );

  return {
    PGHOST: process.env.DB_HOSTS || "localhost",
    PGPORT: parseInt(process.env.DB_PORT || "5432"),
    PGUSER: credentials.username,
    PGPASSWORD: credentials.password,
    PGDATABASE: credentials.database,
    PGMIGDIR: __dirname + "/db/migrations",
    PGCREATEDB: process.env.NODE_ENV !== "production",

    // Different behavior for undo vs apply
    after: action === "apply" ? async () => {
      console.log("✓ Migrations applied successfully");
    } : undefined
  };
};
```

--------------------------------

### Assign and Use psql Variables with \gset

Source: https://github.com/clickup/pg-mig/blob/main/docs/README.md

This example demonstrates assigning a query result to a psql variable using '\gset' and then using that variable in subsequent SQL commands. This allows for dynamic SQL generation within migrations.

```sql
-- mig/20231017204837.do-something.sh.up.sql
SELECT 'hello' AS var1 \gset
\echo :var1
UPDATE my_table SET some=:'var1';
```

--------------------------------

### SQL Maintenance Script - Before Migrations

Source: https://context7.com/clickup/pg-mig/llms.txt

This SQL script runs once per host before any migrations are executed. It installs pg-microsharding functions, prepares for migration, updates statistics, and creates a temporary monitoring table. It is executed in an independent transaction.

```sql
-- File: mig/before.sql
-- Runs once per host before any migrations
-- Executed in independent transaction

-- Install pg-microsharding support functions
CREATE SCHEMA IF NOT EXISTS microsharding;
SET search_path TO microsharding;
\ir ../node_modules/@clickup/pg-microsharding/sql/pg-microsharding-up.sql

-- Prepare for migration
SELECT microsharding.microsharding_migration_before();

-- Update statistics before migration
ANALYZE;

-- Create temporary monitoring table
CREATE TEMP TABLE migration_start_time AS
  SELECT NOW() as started_at;

\echo Before script completed

```

--------------------------------

### Get Code Migration Digest with pg-mig CLI

Source: https://github.com/clickup/pg-mig/blob/main/docs/README.md

This command retrieves the version digest of the current migration files on disk. It's useful for comparing against the database's current digest to ensure compatibility.

```bash
pg-mig --list=digest
```

--------------------------------

### pg-mig Down-Migration for Concurrent Index (SQL)

Source: https://github.com/clickup/pg-mig/blob/main/docs/README.md

This snippet illustrates the down-migration process for an index created concurrently. It involves closing the current transaction, dropping the concurrently created index, and then starting a new transaction. This ensures a clean rollback if needed.

```sql
COMMIT;
DROP INDEX CONCURRENTLY IF EXISTS users_email;
BEGIN;
```

--------------------------------

### Load and Compare DB Digests with loadDBDigest()

Source: https://github.com/clickup/pg-mig/blob/main/docs/functions/loadDBDigest.md

This TypeScript function, loadDBDigest, takes an array of database destinations and a SQL runner function. It queries each destination to get its schema digest and returns the digest that best represents the current schema status. It's crucial for managing schema consistency across multiple database instances.

```typescript
loadDBDigest<TDest>(dests: TDest[], sqlRunner: (dest: TDest, sql: string) => Promise<Record<string, string>[]): Promise<string>
```

--------------------------------

### Error Handling: Timeline Validation with pg-mig

Source: https://context7.com/clickup/pg-mig/llms.txt

This example illustrates `pg-mig`'s timeline validation feature, which prevents migration conflicts when multiple developers work on a project. If a developer pulls changes and their local database has a migration newer than one they are attempting to apply, `pg-mig` will throw a "Migration timeline violation" error, prompting them to rebase their work. This ensures that migrations are applied in a consistent and predictable order across all environments.

```bash
# Developer A's local database state:
# ✔ 20241201000000.feature-a.sh
# ✔ 20241202000000.feature-a-part2.sh

# Developer B pushes:
# ✔ 20241201500000.feature-b.sh (timestamp between A's migrations)

# Developer A pulls and tries to migrate
pg-mig

# ERROR OUTPUT:
# Migration timeline violation: you're asking to apply
# version 20241201500000.feature-b.sh, although version
# 20241202000000.feature-a-part2.sh has already been applied.
# Hint: make sure that you've rebased on top of the main
# branch, and new migration versions are still the most recent.

```

--------------------------------

### TypeScript: pg-mig CLI Entry Point

Source: https://github.com/clickup/pg-mig/blob/main/docs/functions/main.md

The main function for the pg-mig CLI tool, written in TypeScript. It parses command-line arguments and initiates the migration process. It defaults to using standard PostgreSQL environment variables (PGHOST, PGPORT, PGUSER, PGPASSWORD, PGDATABASE) if no arguments are provided.

```typescript
/**
 * CLI tool entry point. This function is run when `pg-mig` is called from the
 * command line. Accepts parameters from process.argv. See `migrate()` for
 * option names.
 *
 * If no options are passed, uses `PGHOST`, `PGPORT`, `PGUSER`, `PGPASSWORD`,
 * `PGDATABASE` environment variables which are standard for e.g. `psql`.
 *
 * You can pass multiple hosts separated by comma or semicolon.
 */
main(argsIn: string[]): Promise<boolean>
```

--------------------------------

### Create Initial Migration with pg-mig

Source: https://github.com/clickup/pg-mig/blob/main/docs/README.md

This command initializes a new database migration using the pg-mig tool. It's typically used with the '--make' flag to create the first migration version, often targeting the 'public' schema. The output files define the database schema's initial state.

```bash
pg-mig --make=initial@public
```

--------------------------------

### Undo and Reapply Migrations with pg-mig

Source: https://context7.com/clickup/pg-mig/llms.txt

Demonstrates how to undo a specific migration, apply a pulled migration, and then reapply your local migration using the pg-mig command-line tool. Also shows how to rename migration files to ensure correct application order.

```bash
pg-mig --undo=20241202000000.feature-a-part2.sh
pg-mig  # Applies 20241201500000.feature-b.sh
pg-mig  # Applies 20241202000000.feature-a-part2.sh
```

```bash
mv mig/20241202000000.feature-a-part2.sh.up.sql mig/20241203000000.feature-a-part2.sh.up.sql
mv mig/20241202000000.feature-a-part2.sh.dn.sql mig/20241203000000.feature-a-part2.sh.dn.sql
pg-mig  # Now applies in correct order
```

--------------------------------

### TypeScript pg-mig Configuration with Dynamic Settings

Source: https://context7.com/clickup/pg-mig/llms.txt

This TypeScript configuration file (`pg-mig.config.ts`) sets up dynamic database connection parameters by loading cluster information. It defines connection details like host, port, user, password, and database, and specifies the migration directory. It also includes custom environment variables and an `after` hook to run post-migration tasks such as initializing data and refreshing materialized views.

```typescript
// File: pg-mig.config.ts
import { Cluster } from "./database/cluster";

export default async function(action: "apply" | "undo" | string) {
  const cluster = await Cluster.load();
  const masterNodes = cluster.getMasterNodes();

  return {
    // Comma-separated list of master nodes
    PGHOST: masterNodes.map(n => `${n.host}:${n.port}/${n.database}`).join(","),
    PGPORT: 5432,
    PGUSER: cluster.credentials.user,
    PGPASSWORD: cluster.credentials.password,
    PGDATABASE: cluster.config.database,
    PGSSLMODE: "prefer",
    PGMIGDIR: `${__dirname}/migrations`,

    // Auto-create database in development
    PGCREATEDB: process.env.NODE_ENV === "development" ? 1 : 0,

    // Custom environment variables accessible in .sql files
    APP_VERSION: require("./package.json").version,
    CLUSTER_ID: cluster.id,

    // Hook called after successful migrations
    after: async () => {
      console.log("Migrations succeeded, running post-migration tasks...");

      // Initialize default data
      await cluster.query(`
        INSERT INTO config (key, value)
        VALUES ('initialized', 'true')
        ON CONFLICT (key) DO NOTHING
      `);

      // Refresh materialized views
      await cluster.query("REFRESH MATERIALIZED VIEW CONCURRENTLY user_stats");
    }
  };
}
```

--------------------------------

### SQL: Define Initial Table Structure

Source: https://github.com/clickup/pg-mig/blob/main/docs/README.md

This SQL snippet demonstrates the content of an initial migration's UP file. It defines the creation of a 'users' table with an 'id' (primary key) and an 'email' column. This is a common pattern for setting up the basic structure of a PostgreSQL database.

```sql
-- 20251203493744.initial.public.up.sql
CREATE TABLE users(
  id bigserial PRIMARY KEY,
  email varchar(256) NOT NULL
);
```

--------------------------------

### List and Inspect Migrations with pg-mig CLI

Source: https://context7.com/clickup/pg-mig/llms.txt

Lists migration versions in the specified directory and provides a code digest for deployment compatibility checking. The output format for digests helps in ensuring database consistency.

```bash
# List all migration versions in directory
pg-mig --list

# Output:
# All versions:
#  > 20231017204837.do-something.sh
#  > 20241107201239.add-table-abc.sh0000
#  > 20241201204837.change-other-thing.sh

# Get code digest for deployment compatibility checking
pg-mig --list=digest

# Output: 20241201204837.a3f2c1b8e9d4f5a6 (format: timestamp.hash)
# Compare this with loadDBDigest() result to ensure DB compatibility
```

--------------------------------

### Generate Initial Migration from Existing Schema

Source: https://github.com/clickup/pg-mig/blob/main/README.md

Command to generate an initial migration file for an existing project by dumping only the schema of a specific database. This is useful for bringing pg-mig into a project with an already established schema.

```bash
pg_dump --schema-only --schema=public your-db-name > mig/20251203493744.initial.public.up.sql
```

--------------------------------

### Run Up Migrations with pg-mig

Source: https://github.com/clickup/pg-mig/blob/main/README.md

Execute the up migration for your PostgreSQL database using pg-mig. This can be done via pnpm, yarn, npm, or deno. Ensure Deno v2+ compatibility by updating package.json scripts if necessary.

```shell
pnpm pg-mig
yarn pg-mig
npm exec pg-mig
den
o run pg-mig
```

--------------------------------

### Include External SQL Files

Source: https://github.com/clickup/pg-mig/blob/main/README.md

Demonstrates the use of the `\ir` psql meta-command to include the content of another SQL file into the current migration script. This promotes modularity and reusability of SQL.

```sql
-- mig/20231017204837.do-something.sh.up.sql
\ir ../vendor/path/to/another/file.sql
ALTER TABLE ...;
```

--------------------------------

### Create New Migration File with pg-mig

Source: https://github.com/clickup/pg-mig/blob/main/docs/README.md

Generate a new pair of SQL migration files (.up.sql and .dn.sql) using the --make flag followed by a migration name and optional schema prefix. Files are automatically timestamped.

```shell
pg-mig --make=my-migration-name@sh
```

--------------------------------

### Echo Diagnostics in Migrations

Source: https://github.com/clickup/pg-mig/blob/main/README.md

Shows how to use the `\echo` psql meta-command within migration files for debugging purposes. It prints messages to the console during the migration process.

```sql
-- mig/20231017204837.do-something.sh.up.sql
\echo Running a dangerous query...
ALTER TABLE ...;
```

--------------------------------

### Apply Migrations with pg-mig

Source: https://github.com/clickup/pg-mig/blob/main/docs/README.md

Execute the 'up' migration using pg-mig. This command can be run with various package managers or directly with Deno. Ensure Deno v2+ has 'pg-mig' added to package.json scripts if using Deno.

```shell
pnpm pg-mig
yarn pg-mig
npm exec pg-mig
denno run pg-mig
```

--------------------------------

### Apply Migrations with pg-mig CLI

Source: https://context7.com/clickup/pg-mig/llms.txt

Command-line interface for applying migrations to PostgreSQL clusters. It supports specifying migration directories, hosts, ports, users, passwords, databases, and parallelism. It can also create databases, perform dry runs, and force run specific files.

```bash
# Basic migration application (uses environment variables or config file)
pg-mig

# Specify migration directory and hosts explicitly
pg-mig \
  --migdir=path/to/migrations \
  --hosts=pg1.example.com,pg2.example.com \
  --port=5432 \
  --user=dbadmin \
  --pass=secretpass \
  --db=myapp_production \
  --parallelism=8

# Using PostgreSQL DSN format
pg-mig --hosts=postgres://user:pass@host1:5432/mydb,postgres://user:pass@host2:5432/mydb

# Create database if it doesn't exist (useful in dev)
pg-mig --createdb

# Dry run mode (preview without applying)
pg-mig --dry

# Force run before/after files even if no migrations pending
pg-mig --force
```

--------------------------------

### pg-mig Database Schema Version Validation

Source: https://context7.com/clickup/pg-mig/llms.txt

Shows how to use pg-mig's digest system for deployment orchestration and database-code compatibility validation within CI/CD pipelines. It involves loading the current database state and comparing it with the code's expected state.

```bash
# Example of checking database schema compatibility

# Load current database state (e.g., in a health check endpoint or pre-deployment script)
# Assume loadDBDigest() is a function that returns the current database digest string
dbDigest=$(loadDBDigest())

# Get the expected database state from the code (e.g., from a config file or build artifact)
# Assume codeDigest is a string like "20241201204837.hash"
codeDigest="20241201204837.hash"

# Perform lexicographic comparison to determine if deployment is safe
if [[ "$dbDigest" ">= ""$codeDigest"" ]]
  echo "Database schema is compatible. Proceeding with deployment."
else
  echo "Database schema is NOT compatible. Deployment blocked."
  # Exit with an error code or take other appropriate action
  exit 1
fi

# pg-mig also provides a --list=digest option for CI/CD pipelines
# pg-mig --list=digest
```

--------------------------------

### Create Migration Files with pg-mig CLI

Source: https://context7.com/clickup/pg-mig/llms.txt

Generates new migration file pairs (up and down) for PostgreSQL schemas. It supports targeting specific schema prefixes, individual shards, or the public schema. Migration names must follow a specific format.

```bash
# Create migration targeting 'sh' schema prefix (all shards starting with 'sh')
pg-mig --make=add-users-table@sh

# Creates two files:
# - 20251028193000.add-users-table.sh.up.sql
# - 20251028193000.add-users-table.sh.dn.sql

# Create migration for specific shard
pg-mig --make=fix-special-shard@sh0000

# Create migration for public schema
pg-mig --make=install-extension@public

# Migration names must be lowercase with hyphens/underscores only
pg-mig --make=update-indexes-for-performance@public
```

--------------------------------

### SQL: Drop Initial Table Structure

Source: https://github.com/clickup/pg-mig/blob/main/docs/README.md

This SQL snippet shows the corresponding DOWN file for the initial migration. It contains the command to drop the 'users' table, allowing for rollback of the initial schema creation. This ensures data integrity during migration rollbacks.

```sql
-- 20251203493744.initial.public.dn.sql
DROP TABLE users;
```

--------------------------------

### Include Other SQL Files with \ir

Source: https://github.com/clickup/pg-mig/blob/main/docs/README.md

This psql meta-command allows including the content of other SQL files into the current migration script. It supports relative paths, enabling modularity and code reuse across migration files.

```sql
-- mig/20231017204837.do-something.sh.up.sql
\ir ../vendor/path/to/another/file.sql
ALTER TABLE ...
```

--------------------------------

### SQL psql Meta-Commands for Advanced Migrations

Source: https://context7.com/clickup/pg-mig/llms.txt

This SQL snippet illustrates the use of psql meta-commands within a migration file for advanced operations. It includes commands to include external SQL files (`\ir`), echo messages (`\echo`), set psql variables from environment variables or query results (`\set`, `\gset`), and use these variables within SQL statements.

```sql
-- File: mig/20241028195000.advanced-migration.sh.up.sql

-- Include external SQL file (relative to migration file location)
\ir ../vendor/extensions/postgis-setup.sql

-- Echo diagnostic messages during execution
\echo Creating orders table...

-- Use environment variables from pg-mig.config.ts
\set app_version `echo "$APP_VERSION"`
\echo Application version: :app_version

-- Query result into variable
SELECT current_database() AS dbname \gset
\echo Working on database: :dbname

-- Conditional SQL using psql variables
CREATE TABLE orders (
  id BIGSERIAL PRIMARY KEY,
  user_id BIGINT NOT NULL REFERENCES users(id),
  total DECIMAL(10,2) NOT NULL,
  status VARCHAR(50) NOT NULL DEFAULT 'pending',
  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

-- Use variable in SQL
UPDATE config SET version = :'app_version' WHERE key = 'schema_version';
```

--------------------------------

### Generate Initial Schema Dump with pg_dump

Source: https://github.com/clickup/pg-mig/blob/main/docs/README.md

This bash command uses pg_dump to create a schema-only dump of a PostgreSQL database. The output is redirected to an initial migration UP file, useful for migrating existing databases into the pg-mig system.

```bash
pg_dump --schema-only --schema=public your-db-name \
  > mig/20251203493744.initial.public.up.sql
```

--------------------------------

### MigrateOptions Interface

Source: https://github.com/clickup/pg-mig/blob/main/docs/interfaces/MigrateOptions.md

The MigrateOptions interface defines the configuration parameters for the pg-mig migration function.

```APIDOC
## Interface: MigrateOptions

### Description
Options for the migrate() function.

### Properties

#### Path Parameters
None

#### Query Parameters
None

#### Request Body

- **migDir** (string) - Required - The directory the migration versions are loaded from.
- **hosts** (string[]) - Required - List of PostgreSQL master hostnames or DSNs in the format: "host[:port][/database]" or "postgres://[user][:password][@]host[:port][/database]". The migration versions in `migDir` will be applied to all of them.
- **port** (number) - Optional - PostgreSQL port on each hosts.
- **user** (string) - Optional - PostgreSQL user on each host.
- **pass** (string) - Optional - PostgreSQL password on each host.
- **db** (string) - Optional - PostgreSQL database name on each host.
- **createDB** (boolean) - Optional - If true, tries to create the given database. This is helpful when running the tool on a developer's machine.
- **parallelism** (number) - Optional - How many schemas to process in parallel (defaults to 10).
- **dry** (boolean) - Optional - If true, prints what it plans to do, but doesn't change anything.
- **force** (boolean) - Optional - If true, runs before/after files on apply even if nothing is changed.
- **action** (object) - Required - What to do. This can be one of the following:
  - `{ type: "make"; name: string; }`
  - `{ type: "chain"; }`
  - `{ type: "list"; }`
  - `{ type: "digest"; }`
  - `{ type: "undo"; version: string; }`
  - `{ type: "apply"; after?: () => void | Promise<void>; }`

### Request Example
```json
{
  "migDir": "./migrations",
  "hosts": ["localhost:5432/mydb"],
  "user": "myuser",
  "pass": "mypass",
  "action": {
    "type": "apply"
  }
}
```

### Response
#### Success Response (200)
(No specific success response defined for the options interface itself, but the `migrate` function would return results based on the action performed.)

#### Response Example
(N/A - This describes configuration options, not a direct API response.)
```

--------------------------------

### Programmatic API: loadDBDigest() for Database Migration Status

Source: https://context7.com/clickup/pg-mig/llms.txt

The `loadDBDigest()` function from `@clickup/pg-mig` allows you to programmatically check the migration status of your database cluster. It takes an array of database pools and a SQL runner function as input, returning a database digest string for compatibility checking. This is crucial for deployment validation to ensure the database is up-to-date with the application code.

```typescript
import { loadDBDigest } from "@clickup/pg-mig";
import { Pool } from "pg";

// Deployment compatibility checking
async function checkDatabaseCompatibility() {
  // Connect to your database cluster
  const pools = [
    new Pool({
      host: "pg-master-1.internal",
      database: "production",
      user: "app",
      password: process.env.DB_PASSWORD
    }),
    new Pool({
      host: "pg-master-2.internal",
      database: "production",
      user: "app",
      password: process.env.DB_PASSWORD
    })
  ];

  // Custom SQL runner for your pool type
  const sqlRunner = async (pool: Pool, sql: string) => {
    const result = await pool.query(sql);
    return result.rows as Array<Record<string, string>>;
  };

  try {
    // Load digest from databases (chooses best/latest across cluster)
    const dbDigest = await loadDBDigest(pools, sqlRunner);
    console.log("Database digest:", dbDigest);

    // Get code digest by running: pg-mig --list=digest
    const codeDigest = "20241201204837.a3f2c1b8e9d4f5a6";

    // Lexicographic comparison determines compatibility
    if (dbDigest >= codeDigest) {
      console.log("✓ Database is compatible with application code");
      console.log("  Safe to deploy");
      return true;
    } else {
      console.error("✗ Database is BEHIND application code");
      console.error("  Must run migrations before deploying");
      return false;
    }
  } finally {
    await Promise.all(pools.map(p => p.end()));
  }
}

// Health check endpoint for monitoring
async function healthCheck(req, res) {
  const pool = req.app.get("dbPool");

  try {
    const dbDigest = await loadDBDigest(
      [pool],
      async (p, sql) => (await p.query(sql)).rows
    );

    res.json({
      status: "healthy",
      database: {
        digest: dbDigest,
        timestamp: dbDigest.split(".")[0],
        hash: dbDigest.split(".")[1]
      }
    });
  } catch (error) {
    res.status(503).json({
      status: "unhealthy",
      error: error.message
    });
  }
}
```

--------------------------------

### Output Debugging Messages with \echo

Source: https://github.com/clickup/pg-mig/blob/main/docs/README.md

The \echo psql meta-command is used within migration scripts to print diagnostic messages. This is helpful for tracking the execution flow and identifying issues during the migration process.

```sql
-- mig/20231017204837.do-something.sh.up.sql
\echo Running a dangerous query...
ALTER TABLE ...
```

--------------------------------

### pg-microsharding: After Migration Cleanup

Source: https://github.com/clickup/pg-mig/blob/main/README.md

SQL script to be run after migrations when using the pg-microsharding library. It retrieves the PG_MIG_HOSTS environment variable and calls the `microsharding_migration_after` function to create debug views.

```sql
-- mig/after.sql
\set PG_MIG_HOSTS `echo "$PG_MIG_HOSTS"`
SELECT microsharding.microsharding_migration_after(:'PG_MIG_HOSTS');

```

--------------------------------

### pg-mig Migration File Format: Up Migration

Source: https://context7.com/clickup/pg-mig/llms.txt

Defines the structure for forward migration SQL files in pg-mig. It includes naming conventions, optional directives for controlling parallelism and delays, and standard SQL DDL statements. Migrations are executed transactionally per schema.

```sql
-- File: mig/20241028193000.add-users-table.sh.up.sql
-- Schema prefix: sh (applies to all schemas starting with 'sh')

-- Optional: Limit parallelism to avoid overloading database
-- $parallelism_per_host = 2
-- $parallelism_global = 4
-- $delay = 100
-- $run_alone = 0

CREATE TABLE users (
  id BIGSERIAL PRIMARY KEY,
  email VARCHAR(256) NOT NULL UNIQUE,
  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE INDEX users_email_idx ON users(email);

CREATE FUNCTION update_updated_at()
RETURNS TRIGGER AS $$
BEGIN
  NEW.updated_at = NOW();
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER users_updated_at
  BEFORE UPDATE ON users
  FOR EACH ROW
  EXECUTE FUNCTION update_updated_at();

-- Migration runs in single transaction automatically
-- search_path already set to target schema
```

--------------------------------

### Apply New Migration Version (Happy Path)

Source: https://github.com/clickup/pg-mig/blob/main/docs/README.md

Demonstrates the scenario where a new migration version is appended to the existing sequence and is applied successfully by pg-mig. This occurs when the new version's timestamp is later than all previously applied versions.

```shell
20231017204837.do-something.sh
20241201204837.change-other-thing.sh
20241202001000.and-one-more-thing.sh
20241202002000.their-thing.sh.up.sql    <-- new version pulled
```

--------------------------------

### Debug Migration with Undo and Apply

Source: https://github.com/clickup/pg-mig/blob/main/docs/README.md

This bash command facilitates debugging by undoing a specific migration version and then reapplying all subsequent migrations. It's useful for testing changes and ensuring migration integrity in a development environment.

```bash
pg-mig --undo=20251203493744.initial.public; pg-mig
```

--------------------------------

### Non-Transactional Concurrent Index Creation with pg-mig (SQL)

Source: https://github.com/clickup/pg-mig/blob/main/docs/README.md

This snippet demonstrates how to create indexes concurrently outside of a transaction block using pg-mig. It includes closing the existing transaction, performing the concurrent index creation, and then beginning a new transaction. It also shows how to handle potential index failures by dropping existing broken indexes before creation. This approach is essential for large tables to avoid write locks.

```sql
-- $parallelism_per_host = 2
COMMIT;
DROP INDEX IF EXISTS users_email;
CREATE UNIQUE INDEX CONCURRENTLY users_email ON users(email);
BEGIN;
```

--------------------------------

### pg-microsharding Integration: After Migration Script (SQL)

Source: https://github.com/clickup/pg-mig/blob/main/docs/README.md

This SQL snippet defines the actions to be performed after migrations when using pg-microsharding. It sets the PG_MIG_HOSTS variable and calls the microsharding_migration_after function to generate debug views across all shards.

```sql
-- mig/after.sql
\set PG_MIG_HOSTS `echo "$PG_MIG_HOSTS"`
SELECT microsharding.microsharding_migration_after(:'PG_MIG_HOSTS');
```

--------------------------------

### Load Database Migration Digest with pg-mig Node Library

Source: https://github.com/clickup/pg-mig/blob/main/README.md

The `loadDBDigest()` function, exported by the pg-mig Node library, retrieves the current migration digest from the database. This 'database digest' can be used for comparison with the 'code digest' to ensure compatibility before code deployment. It handles multiple database nodes for resilience.

```javascript
const { loadDBDigest } = require('pg-mig');

async function getDatabaseDigest() {
  try {
    const dbDigest = await loadDBDigest();
    console.log('Database Digest:', dbDigest);
    return dbDigest;
  } catch (error) {
    console.error('Error loading database digest:', error);
    throw error;
  }
}
```

--------------------------------

### Control Parallelism in pg-mig

Source: https://github.com/clickup/pg-mig/blob/main/docs/README.md

Adjust the level of parallelism for migration operations across multiple hosts and schemas using the --parallelism command-line option. The default value is 10.

```shell
pg-mig --parallelism=N
```

--------------------------------

### pg-mig Partial Failure Recovery

Source: https://context7.com/clickup/pg-mig/llms.txt

Illustrates pg-mig's automatic retry and recovery mechanism for partial migration failures across multiple database shards. The tool remembers failed shards and retries them on subsequent runs, ensuring transactional consistency.

```bash
# Scenario: Migration fails on 2 out of 10 shards due to network issue

pg-mig
# Output:
# Running on pg1.internal,pg2.internal
# Applying 20241028193000.create-tables.sh to 10 schemas...
# ✓ sh0000 on pg1.internal
# ✓ sh0001 on pg1.internal
# ✗ sh0002 on pg1.internal (connection timeout)
# ✓ sh0003 on pg2.internal
# ...
# ✗ sh0007 on pg2.internal (connection timeout)
#
# FAILED. See complete error list below.
# Failed with 2 error(s).

# Simply rerun - pg-mig remembers what succeeded
pg-mig

# Output:
# Running on pg1.internal,pg2.internal
# Applying 20241028193000.create-tables.sh to 2 schemas...
# ✓ sh0002 on pg1.internal (retried)
# ✓ sh0007 on pg2.internal (retried)
# Succeeded.

# Transaction-per-schema guarantees: if sh0002 failed, its
# transaction rolled back completely. Retry is safe.
```

--------------------------------

### Parallelism Control: Directives for Limiting Concurrent Migrations

Source: https://context7.com/clickup/pg-mig/llms.txt

This section demonstrates how to control the concurrency of PostgreSQL migrations using special directives within migration files. Directives like `$parallelism_per_host`, `$parallelism_global`, `$delay`, and `$run_alone` allow you to manage resource utilization and prevent database overload during intensive operations. These directives are commented out at the beginning of the SQL file and are parsed by `pg-mig` before execution.

```sql
-- File: mig/20241028200000.heavy-migration.sh.up.sql

-- Limit to 2 concurrent executions per physical host
-- Useful when migration is CPU/IO intensive
-- $parallelism_per_host = 2

-- Limit to 4 concurrent executions across ALL hosts
-- Useful for operations that stress shared resources
-- $parallelism_global = 4

-- Delay 500ms between starting each migration
-- Useful to stagger load across time
-- $delay = 500

-- Run completely alone (no other migrations active anywhere)
-- Useful for extensions or schema-wide changes
-- $run_alone = 1

-- Heavy operation that could max out CPU
CREATE INDEX items_complex_idx ON items(name, created_at)
  WHERE deleted_at IS NULL;

VACUUM FULL items;
ANALYZE items;

-- Without these directives, default parallelism is 10

```

--------------------------------

### Create Unique Index Concurrently in PostgreSQL

Source: https://github.com/clickup/pg-mig/blob/main/README.md

Demonstrates how to create a unique index concurrently in PostgreSQL, outside of a transaction block, to avoid write locks on large tables. It includes dropping an existing index if present and resuming transaction after creation.

```sql
-- $parallelism_per_host = 2
COMMIT;
DROP INDEX IF EXISTS users_email;
CREATE UNIQUE INDEX CONCURRENTLY users_email ON users(email);
BEGIN;

```

--------------------------------

### Use Environment Variables in psql Migrations

Source: https://github.com/clickup/pg-mig/blob/main/docs/README.md

This SQL snippet shows how to set and use environment variables within psql migration files. The '\set' command combined with a shell command substitution allows injecting external environment variables into the SQL execution context.

```sql
-- mig/20231017204837.initial.public.up.sql
\set HOSTS `echo "$VAR"`
SELECT my_function(:'VAR');
```

--------------------------------

### SQL Maintenance Script - After Migrations

Source: https://context7.com/clickup/pg-mig/llms.txt

This SQL script runs once per host after all migrations have successfully completed. It creates debug views, refreshes materialized views, logs migration completion details, and performs a vacuum analyze on changed tables. It is executed in an independent transaction.

```sql
-- File: mig/after.sql
-- Runs once per host after all migrations succeed
-- Executed in independent transaction

-- Create debug views across all shards
\set PG_MIG_HOSTS `echo "$PG_MIG_HOSTS"`
SELECT microsharding.microsharding_migration_after(:'PG_MIG_HOSTS');

-- Refresh cached aggregates
REFRESH MATERIALIZED VIEW CONCURRENTLY global_statistics;

-- Log migration completion
INSERT INTO migration_log (completed_at, duration)
SELECT NOW(), NOW() - started_at
FROM migration_start_time;

-- Vacuum analyze all tables that changed
VACUUM ANALYZE;

\echo After script completed

```

--------------------------------

### Load Database Migration Digest with pg-mig Node.js Library

Source: https://github.com/clickup/pg-mig/blob/main/docs/README.md

This function, exported by the pg-mig Node.js library, retrieves the current migration digest from the database. It can be integrated into application health endpoints for deployment checks. It handles multiple database nodes, selecting the most recent digest.

```javascript
const { loadDBDigest } = require('pg-mig');

async function checkDatabaseDigest() {
  const dbDigest = await loadDBDigest();
  console.log('Database Digest:', dbDigest);
  return dbDigest;
}
```

--------------------------------

### Microsharding: Schema Prefix Patterns for Targeted Migrations

Source: https://context7.com/clickup/pg-mig/llms.txt

This section explains how to use schema prefix patterns in migration file names to target specific shards or ranges of shards within a sharded PostgreSQL database. By naming files like `20241028193000.create-base-tables.sh.up.sql` or `20241028194000.fix-shard-zero.sh0000.up.sql`, you can control which schemas a migration applies to. The longest prefix match determines the target, ensuring precise control over schema-level operations.

```bash
# Database has schemas: public, sh0000, sh0001, sh0002, ..., sh0099

# Migration file targeting ALL shards (sh*)
# File: 20241028193000.create-base-tables.sh.up.sql
# Applies to: sh0000, sh0001, sh0002, ..., sh0099

# Migration file targeting ONE specific shard
# File: 20241028194000.fix-shard-zero.sh0000.up.sql
# Applies to: sh0000 ONLY

# Migration file targeting public schema
# File: 20241028195000.install-extension.public.up.sql
# Applies to: public ONLY

# Multiple migrations with different prefixes run in parallel:
# - 20241028193000.create-base-tables.sh.up.sql → all sh* except sh0000
# - 20241028194000.fix-shard-zero.sh0000.up.sql → sh0000
# - 20241028195000.install-extension.public.up.sql → public
#
# Longest prefix match wins: sh0000 matches .sh0000. not .sh.

```

```sql
-- Example migration applying to all shards
-- File: mig/20241028193000.create-base-tables.sh.up.sql

CREATE TABLE items (
  id BIGSERIAL PRIMARY KEY,
  name TEXT NOT NULL,
  shard_id INT NOT NULL, -- Which shard this belongs to
  created_at TIMESTAMPTZ DEFAULT NOW()
);

-- This table created in sh0000, sh0001, sh0002, etc.
-- Each shard has identical structure but independent data

```

--------------------------------

### Undo Migrations with pg-mig

Source: https://github.com/clickup/pg-mig/blob/main/docs/README.md

Revert a specific migration version using the --undo flag followed by the migration version identifier. This is primarily useful during development for testing DDL statements or resolving conflicts.

```shell
pg-mig --undo=20231017204837.do-something.sh
```

--------------------------------

### Resolve Explicit Merge Conflict with pg-mig Undo

Source: https://github.com/clickup/pg-mig/blob/main/docs/README.md

Illustrates how to resolve an explicit merge conflict where a new migration version is inserted in the middle of the applied sequence. This requires undoing the later version and rerunning pg-mig.

```shell
pg-mig --undo=20241202001000.and-one-more.sh
pg-mig
```

--------------------------------

### Drop Index Concurrently in PostgreSQL

Source: https://github.com/clickup/pg-mig/blob/main/README.md

Shows how to drop an index concurrently in PostgreSQL. This is typically used in down-migration scripts to remove an index without blocking write operations.

```sql
COMMIT;
DROP INDEX CONCURRENTLY IF EXISTS users_email;
BEGIN;

```

--------------------------------

### Fix Implicit Conflict by Renaming Migration File

Source: https://github.com/clickup/pg-mig/blob/main/docs/README.md

Shows how to resolve an implicit merge conflict by renaming a local migration file to ensure its timestamp is the latest after pulling changes. This prevents migration timeline violations in other environments.

```shell
mv 20241202001000.your-new-thing.sh.up.sql \
  20241202002200.your-new-thing.sh.up.sql
```

--------------------------------

### Undo Migrations with pg-mig CLI

Source: https://context7.com/clickup/pg-mig/llms.txt

Rolls back the most recent migration version applied to the PostgreSQL cluster. It can also be used in conjunction with apply commands for development workflows. Note that migrations must be undone in reverse order of application.

```bash
# Undo specific migration (must be the latest applied)
pg-mig --undo=20241201204837.change-other-thing.sh

# Undo and immediately reapply (useful during development)
pg-mig --undo=20241201204837.change-other-thing.sh && pg-mig

# Cannot undo older migrations - must undo in reverse order
# If version 3 is applied, cannot undo version 2 without first undoing 3
```