Skip to main content

Handling PostgreSQL Migrations in Node.js

· 4 min read
Boas Falke
Boas Falke
PHP Developer

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() and down() 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. 🔍