Handling PostgreSQL Migrations in Node.js
Never thought you'd need to handle database migrations in Node.js? Me neither. Anyway, here’s one way to do it. Oh, and I think Node.js blog posts need emojis, right? 💯💡
Background
Recently, we faced an issue in one of our Node.js projects: we needed a strategy to consistently modify the database without doing it manually each time.
Coming from a PHP and Symfony background, this isn’t usually something you spend a lot of time worrying about. Doctrine integrates seamlessly into most PHP projects, is well-documented, and offers a lot of convenient features to handle database tasks.
As I started exploring the Node.js ecosystem, I quickly realized that migration tools vary a lot — in scope, philosophy, and even maintenance. In other words, the classic JavaScript experience.
Enter node-pg-migrate
Since we didn’t need a full-blown database abstraction layer and were really just looking for flexible, scriptable
migrations, we searched for a lightweight solution.
That’s when I stumbled upon node-pg-migrate
.
It’s a migration management tool for Node.js built specifically for PostgreSQL (though you can technically use it with
any DB conforming to the SQL standard). It has TypeScript support and uses simple up()
and down()
functions in the
migration scripts to define the steps.
Handling existing Databases
After installing it, following the Getting Started guide, I ran into my first roadblock: It’s great that I can create migrations to change the DB state going forward, but... what about my existing schema?
Unfortunately, node-pg-migrate
doesn’t provide a built-in way to generate migrations from an existing database schema.
But I wasn’t the first to run into this. In this GitHub issue comment, someone shared a helpful workaround:
# Dump current schema to a raw SQL file for use as an initial migration.
pg_dump --clean --if-exists --no-owner --no-acl $DATABASE_URL > 0000000000000-init.sql
The hidden feature 👀
That got me wondering — does node-pg-migrate
actually support raw SQL files for migrations? Surprisingly, yes! 🎉
This feature has been around for over 7 years, yet it’s not documented.
Any file with the format
TIMESTAMP_MigrationName.sql
will be executed when migrating up. The TIMESTAMP
prefix refers to the number of
milliseconds since the Unix epoch (January 1st, 1970), which determines the execution order of migrations.
It’s likely not promoted because there’s no built-in way to roll back raw SQL migrations using a down() function.
There is an open issue addressing this topic to
finalize SQL support, but it's been open for almost 4 years.
In our case, this limitation didn’t matter. We just needed to "freeze" the current schema — essentially a snapshot — which would always be the first migration executed on a fresh database (e.g., for dev setups).
Faking it in Production
Still, I didn’t want to run this initial migration on our production database. Luckily, node-pg-migrate
offers a handy --fake
option.
As the name suggests, it fakes executing a migration, marking it as done in the migrations table without actually
running it.
# Fake the execution of the initial migration — useful for existing databases like production
./node_modules/.bin/node-pg-migrate up --fake
# Can't determine timestamp for 0000000000000-init
# > Migrating files:
# > - 0000000000000-init
# ### MIGRATION 0000000000000-init (UP) ###
# Migrations complete!
Why not use Knex, Sequelize, or TypeORM?
We briefly looked at other tools like:
- Knex.js: Great query builder, but its migration tooling felt too tightly coupled with its own DSL. We wanted more flexibility.
- Sequelize: Comes with migration support, but also a full ORM—which we didn’t need.
- TypeORM: Another full-featured ORM, but we found the learning curve steep and migrations less predictable in complex schemas.
In the end, node-pg-migrate
hit the sweet spot: focused, flexible, and
lightweight. Just what we were looking for.
TL;DR
- 🪶 node-pg-migrate is a lightweight, open source postgres migration tool for node.js
- ↕️ It supports
up()
anddown()
functions in typescript migration scripts - 🐘 It also supports raw SQL files as migrations, even though this feature is not documented or promoted because it
lacks the
down()
functionality - ✌🏼 Great for generating a one-off migration from an existing schema
- 🌀 Use the --fake option to mark a migration as applied without running it
Gotchas aside, node-pg-migrate gave us exactly the level of control we needed. If you're working in a Node.js + Postgres setup and don’t want to pull in a full ORM, this tool is definitely worth a look. 🔍