PG Migrate

Straightforward wrapper around @supabase/pg-delta. Helps make schema diffing, and forward-only migrations w/o docker, pg. Uses pglite to diff your schemas.

Install

• npm i @messcat/pg-migrate
• npm i @electric-sql/pglite (if you're using pglite as your spawnDb type -- the default. Skip if you plan on using Docker containers instead)

Usage - Config

The package is especially useful when working with a larger set of interconnected Postgres systems via a plugin system.

For example, you work on two systems where B depends on A. Then, when planning for migrations in B, the package can setup A as a dependency, but only write migrations for B (if it lives in a separate schema). Useful for monorepos, and cleanly managing schema changes across multiple systems.

First, create a pg-migrate.config.[ts/js/mjs] file in your project root. This should look like:

import type { PgMigrateConfig } from '@messcat/pg-migrate'
export default {
	plugins: {
		A: {
			schemas: ['system_a'],
			migrations: { directory: './packages/a/migrations' },
			current: { directory: './packages/a/schema' }
		},
		B: {
			schemas: ['system_b'],
			migrations: { directory: './packages/b/migrations' },
			current: { directory: './packages/b/schema' }
		},
	},
} satisfies PgMigrateConfig

If your shcmea needs pg contrib extensions, you can specify them in the contribExtensions field:

const config: PgMigrateConfig = {
	spawnDb: {
		type: 'pglite',
		contribExtensions: ['pgcrypto'],
	},
	...rest
}

If you want to use Docker, not pglite -- maybe you use custom extensions unavailable in pglite:

const config: PgMigrateConfig = {
	spawnDb: { type: 'docker' },
	...rest
}

You can override the default config file by setting an env var: PG_MIGRATE_CONFIG_FILE=/path/to/config.ts.

Usage - CLI

Next, you can directly fire the cli: npm run pgm <command> [options].

1. plan --plugin <plugin name> [--name <name>]. Plans, stores a migration based on the schema diff between the migration directory and current schema directory of the specified plugin. Eg. npm run pgm plan --plugin waba --name new_index Notes:
   • uses pglite by default. Use the API to specify a different database URI.
   • will write the migration file to the migration directory as timestamped <ts>.<name>.sql (or <ts>.sql if --name is omitted).
   • --name is optional. Omitting it produces a filename of just <ts>.sql.
   • --schema is optional. Defaults to public.
   • PLEASE CHECK THE MIGRATION FILE BEFORE APPLYING.
2. apply [--database-uri <uri>]. Applies a migration based on the schema diff between the migration directory and the database schema. If --database-uri is not specified, uses the DATABASE_URL, DB_URI environment variables. Eg. npm run pgm apply Notes:
   • Will be auto creating a migrations table in the public schema, if it doesn't exist. Refer to the migrations table DDL here.

Footnotes

Why another migration tool? This is just a wrapper on pg-delta with automatic migration generation support. pg-delta is the most comprehensive diffing tool I could find, most others lacked the features we needed (eg. functions, custom types, etc.). Plus, we needed diffs independently for multiple interdependent systems. Perhaps a niche use case, but hopefully helps someone else out.