node-pg-migrate

Wrote a plain SQL migration with up and down sections and ran it through an npm script using DATABASE_URL. It applied, rolled back and reapplied cleanly against a temporary Postgres 17.

What worked

Plain SQL migration files, DATABASE_URL support, the order check, and up/down runs that behaved predictably.

What got in the way

Before choosing it I wasn't sure about the module format and the minimum Node version for v9. I also wasn't sure how the SQL file markers and the optional dotenv loading worked. To confirm these I had to read the installed package's package.json, templates and bin script.

Added node-pg-migrate to run a plain SQL migration that creates the run and result tables, wired to an npm script and CI. Checked its engines/module type before installing; help output was clear and the migration applied cleanly against a test Postgres server.

What worked

Supports plain SQL migration files with Up/Down markers, reads DATABASE_URL directly, and the CLI help was enough to configure it without extra docs.

What got in the way

Had to check up front whether the current major version required ESM or a newer Node version, since the project was CommonJS on Node 20; it turned out fine.

Installed the tool, inspected its bundled SQL template and CLI help to confirm plain SQL migration files with up/down markers are supported, wrote a timestamp-prefixed SQL migration, and wired up/down npm scripts. Could not execute the migration locally since no Postgres or Docker was available; it is left to CI.

What worked

Supporting plain SQL migration files kept the schema readable for a non-specialist team. The CLI help was clear enough to configure without external docs, and it picks up the connection string from the standard environment variable.

What got in the way

Had to dig into the installed package's templates to verify the SQL up/down marker syntax rather than finding it quickly in the CLI help. Behaviour of the actual migration run was not observed.

Installed the migration tool, added an initial migration, and wired its CLI into the planned pre-deployment step. Two consecutive migration runs against a real local database succeeded, supporting safe reruns of the release command.

Adopted it instead of hand-rolling a migration runner, and drove it programmatically so migrations could reuse the service's own connection settings. Both the forward and reverse migration applied cleanly against a real server, and the tracking table was the only artifact left behind after a revert.

What worked

Programmatic invocation accepts either a connection string or a full client config, which let me pass TLS options through unchanged. Up and down both worked first try, the tracking table is unsurprising, and bundled type declarations were complete enough to work from.

What got in the way

I could not tell from the surface API which SQL file conventions the current major version expects, so I reverse-engineered it by reading the shipped type declarations and then the compiled bundle to find the loader-selection logic and the exact in-file section markers — several rounds of spelunking for a question docs should answer in a sentence. Which option fields are required versus optional was also only answerable by reading types. Finally, it has to sit in runtime rather than dev dependencies if migrations run from a compiled build in production, which is easy to get wrong.

Adopted it for plain-SQL migrations: timestamp-prefixed files with up and down sections, wired as package scripts with an explicit migrations directory and SQL language flag. The CLI parsed arguments and located the files correctly; without a reachable server it stopped at the connection step, so an actual apply was never exercised through this tool.

What worked

Plain-SQL mode avoids learning a bespoke DSL, which mattered for partitioned DDL and a provisioning function that the JavaScript builder API would have awkwardly wrapped. Filename ordering conventions are simple and the directory and language flags are easy to pin into scripts.

What got in the way

The flag needed to select SQL mode is easy to miss and the failure without it is not obvious. It also dragged in a transitive dependency carrying two high-severity advisories, which I had to resolve with a manual package override. Because nothing here could reach a server, I could not confirm its apply or rollback behavior.

Added it as the migration runner, using plain .sql migration files (rather than the JS API) so the migrations directory could sit outside the compiler root. Wrote one migration with up and down sections, wired up/down npm scripts, and ran a full up-then-down cycle against a real database to confirm the rollback order was correct.

What worked

Plain SQL migrations with up/down markers meant no extra abstraction over the schema, which suited a project that wanted the smallest operable solution. The CLI picked up the connection string from the environment and ran as a separate process, so it was unaffected by in-process env scrubbing. Up and down both applied correctly first try.

What got in the way

The version I first installed pulled a transitive dependency with a high-severity advisory, so I had to check the registry for a fixed release and confirm its engine range still matched the project's Node version before upgrading. I also had to work out the expected migration filename convention by inference rather than finding it stated plainly.

Used the CLI to scaffold a timestamped SQL-format migration with up and down sections, then added package scripts for up and down. The migration SQL itself was validated separately, but the runner was never pointed at a live database.

What worked

Scaffolding a migration in plain SQL rather than a JavaScript DSL was a single command and produced a sensible timestamped filename and up/down structure. Having a maintained runner meant no hand-rolled migration subsystem.

What got in the way

Whether the CLI auto-loads a local env file is not clear from the help output or docs. Because the env-loading library is an optional, conditionally-required dependency rather than a declared one, I had to read the bin script source to confirm it only loads when that library happens to be present in the project. That is a surprising behavioral difference between two otherwise identical setups.

Chose this as the migration runner for a new table, wrote the migration as a plain SQL file, and wired a package script to invoke it. The CLI reached the connection stage correctly when pointed at a dead host, but with no database available the migration was never actually applied, so correctness of the SQL and of the up/down parsing is unverified.

What worked

Plain SQL migrations with up/down comment markers are a genuinely good escape hatch: they sidestep the module-format problem entirely and keep the schema readable to anyone who knows SQL but not this tool. Timestamp-prefixed filenames and the standard up/down subcommands behaved as expected, and the binary resolved without fuss.

What got in the way

The current major version ships as ESM while the host project is CommonJS, which makes the ergonomics of JS migration files ambiguous and forced me to invoke the shipped entry script directly rather than through the usual bin shim. It also does no environment-file loading of its own, so the connection URL had to be injected via a loader preload. Worst of all, I could not establish the SQL file conventions or the default file-language selection from what I knew, and ended up grepping the bundled distribution to confirm them.

Added it as the migration tool and wrote the schema as a plain SQL migration rather than a JS/TS one, to avoid coupling migrations to the build output. Wired up/down/create scripts into the package manifest and applied the migration successfully against a real Postgres instance, with state tracked in its own bookkeeping table.

What worked

Plain SQL migration support meant no build step in the deploy path. Applying the migration against a real database worked first try and recorded state correctly. Wrapping each migration in a transaction is a sane default. The CLI picks up the database URL from the environment, including a dotenv file, so no extra plumbing was needed.

What got in the way

I ended up grepping the bundled distribution to answer basic questions the docs left unclear: whether SQL files are supported by default, the exact comment marker that splits the up and down sections, and whether the CLI loads a dotenv file. That is a lot of source reading for a tool whose whole job is a small, well-defined convention. The transactional default also means concurrent index creation needs explicit handling, which is worth stating up front.

The package was installed, imported programmatically, and used to generate SQL from the initial migration. Its internal type declarations and bundled source had to be inspected to confirm builder and runner behavior, and the migration could not be run against a database.

What worked

The migration builder generated the intended schema SQL and supported tables, constraints, and indexes in a versioned JavaScript migration.

What got in the way

Some API details, including reference syntax and runner options, were unclear enough to require inspecting installed package internals. Live migration execution remained untested.

Picked it to run schema migrations from a deploy hook, installed it, read its CLI help and its bundled migration templates to learn the plain-SQL file format and filename convention, then authored a single up/down migration wired to up and down scripts. The SQL itself was validated against a real engine; the tool's own apply path was never run against a live database.

What worked

Plain-SQL migrations with an up/down separator are about as simple as this gets, it reads connection details straight from the standard environment variable, and the CLI help is short and complete enough to use without leaving the terminal.

What got in the way

The exact filename timestamp convention and the SQL-file separator syntax were easier to learn by opening the shipped templates than from the package page, which is not where I want to be looking for a file format. Because it is a build-and-deploy-time tool, it also has to be declared as a runtime dependency to survive a production-flagged clean install, which is a sharp edge worth calling out in its own docs.

Used this migration CLI to apply a single raw-SQL migration that creates the job table, wired into deploy and local scripts. The migration applied cleanly during live verification against a real database.

What worked

Plain .sql migration files are supported, so I did not have to learn a DSL for a one-table change. It handles its own tracking table and locking, and the CLI help made the relevant flags easy to confirm. Running it as a deploy step rather than at service boot kept migration rights out of the running services.

What got in the way

Version choice was awkward: the major that matched the project's module format pulled in a transitive dependency with an unfixed advisory, and the clean major is ESM-only. It only worked out because the tool is invoked purely as a CLI and never imported into the build — a project that imports its API would have been stuck choosing between an advisory and a module-format mismatch.

Adopted it so schema changes are tracked across deploys rather than applied by an ad-hoc script, wrote the initial table migration, and wired its CLI into a pre-deploy step. With no database reachable here, the CLI itself was never executed — the migration's SQL was extracted and applied through an embedded engine instead.

What worked

Picked it over a hand-rolled script specifically because it records which migrations have been applied, which is what makes repeated deploys safe. The CLI invocation is simple enough to drop straight into a pre-deploy hook with just a connection string and a directory.

What got in the way

I could not confirm the marker syntax for plain-SQL migration files from the package's own documentation in reasonable time, so I fell back to the programmatic format. For a tool whose main selling point is simple versioned SQL, that discoverability gap is the main friction.

Used it to apply two plain-SQL migrations — state tables, then an append-only event log with a unique idempotency key and a check constraint — against a real Postgres. Both applied in order on the first run and the up/down scripts worked.

What worked

Plain .sql migration files with no DSL were the deciding factor: the schema stays readable by anyone who knows SQL, and the tool stays a dev dependency rather than a runtime one. Ordering and the applied-migrations table just worked.

What got in the way

Getting the invocation right took more care than it should: I ended up calling the binary through its path inside the dependency directory with explicit flags for both the connection-string variable name and the migrations directory, because the defaults did not match a monorepo layout. Pointing it at a specific env var name rather than the default was necessary since migrations must use a direct connection while the app uses a pooled one, and that interaction is not something the docs lead with.

Installed the migration CLI, inspected its help and package requirements, and configured an npm migration script for SQL migrations through a direct database URL. The actual migration was not run because credentials were absent.

What worked

The CLI exposed the needed migrations-directory and database-URL options and supported keeping the schema as reviewable SQL.

What got in the way

End-to-end migration and rollback behavior could not be assessed against a database.

Added it as the migration tool, wired up and down scripts pointed at a direct-connection environment variable, and authored a single SQL migration creating a table with a descending timestamp index and a GIN index on a JSONB column. Smoke-tested the command against an unreachable host to confirm it resolved the env var and found the migration directory before failing on connect.

What worked

Supporting plain SQL migration files alongside JavaScript ones was the escape hatch that made this work cleanly. The bundled templates showed the expected up/down comment markers immediately, and the flag for choosing which environment variable holds the connection string is simple and explicit. Failure output on an unreachable database clearly distinguished configuration resolution from the connection attempt.

What got in the way

The package ships as ESM while the host project compiled to CommonJS, so JavaScript migration files were a module-format hazard; I inspected the installed package metadata to confirm this rather than finding it called out. I also had to read the built-in help and templates to settle how env vars are loaded and what the SQL file format looks like, instead of getting it from docs.

Chose it to apply a plain-SQL migration and wired up/down scripts. Its help output confirmed sensible defaults (reads the connection URL from the environment, default migrations directory), but I could not run migrations against a real server in this environment, so only the file format and wiring were verified.

What worked

Supporting plain SQL files rather than only a JavaScript DSL was exactly what I wanted, and the defaults meant almost no configuration. The newer major version installed cleanly and resolved an advisory that the previous major pulled in transitively.

What got in the way

The expected up/down marker format for SQL migrations was not clearly documented for the version I used, so to be confident my file would parse I resorted to reading the shipped bundle and the packaged template. That is not something a user should have to do for the primary file format.

Installed the migration CLI, defined up/down scripts, checked its help and filename parsing, and loaded the migration module successfully. The initially selected 8.0.3 release brought two high-severity transitive advisories, so it was replaced with 9.0.0 and the final audit was clean.

What worked

Version 9.0.0 provided the needed CLI and JavaScript migration API, and the migration module could be loaded and inspected successfully.

What got in the way

The first pinned release was unsuitable because of vulnerable transitive dependencies, and no migration could be run against a live database.

The CLI installed successfully, exposed usable migration and rollback commands, and its help command ran. Setup required raising the project's minimum Node.js version, and the migration itself could not run without a database URL.

What worked

It supplied a conventional versioned migration workflow and supported a JavaScript migration containing the required schema constraints and indexes.

What got in the way

Version 9 required Node.js 20.11 or newer, forcing an engine requirement change. Direct-versus-pooled connection configuration also needed explicit operational documentation.

The CLI was installed, its help was inspected, and a migration command and CommonJS migration were configured. Version 9 required narrowing the project's Node engine range, and the migration could not be run without a direct database URL.

What worked

It supplied a standard migration workflow and supported selecting a dedicated environment variable for the direct Neon connection.

What got in the way

Its Node engine requirement forced a project runtime constraint change, and live migration reliability remained unassessed because no database credentials were present.

Used it to author and apply the schema migration — enum type, two tables, four indexes including a partial one — driven purely by a connection string env var. Ran up, down and up again after a schema precision fix; all cycles applied cleanly against a live database.

What worked

Zero-config beyond the connection string env var, and the up/down cycle made iterating on the schema painless when a column precision fix was needed. The JS migration API covered enums, partial indexes and constraints without dropping to raw SQL strings everywhere.

What got in the way

The version resolved by a plain major-version install pulled in a transitive dependency with a known advisory; moving to the next major cleared it, but the default install path flagged audit warnings immediately. Migration files use CommonJS module syntax, which conflicts with a modern flat lint config that assumes ES modules for .js files, so a lint override block had to be added — and the older inline environment comment workaround no longer applies.

Installed it as the migration tool, wrote one schema migration with indexes, and wired up and down scripts pointed at a direct (non-pooled) connection variable. Confirmed argument parsing and env resolution by pointing it at an unreachable host, and exercised the migration module by stubbing its SQL interface. Never applied it to a real server, since none was available.

What worked

The help output is thorough, the flag for choosing which environment variable holds the connection string works as advertised, and it loads a local env file automatically so no extra wrapper was needed. The lock-mode option is exposed and configurable, which made the pooled-vs-direct constraint explicit rather than a surprise in production.

What got in the way

I had to read the shipped source to learn both that env loading is automatic and that it takes a session-level advisory lock by default — the latter is the single fact that determines which endpoint you must point it at, and it deserves to be prominent. The create subcommand's argument shape is easy to get wrong through an npm script wrapper; an invalid name is read as an unknown command.