How do I run postgres migrations using Node.js/knex?

Questions / Help
1

Hi,

I’m a bit lost on how to run Postgres migrations against the production database. I am using Node.js and specifically the knex library to create/run migrations.

Current approach

  • Create a Postgres cluster using fly (Docs Link)
  • Add read replicas (Docs Link)
  • Pull down the fly.toml configuration file to it’s own folder (_db) to expose an external port (Docs Link)
  • In the same _db directory add a package.json and install knex and pg
  • Create the migration files
    • npx knex migrate:make MIGRATION_NAME
  • Add [deploy] settings to the fly.toml
    • release_command = "./node_modules/knex/bin/cli.js migrate:latest --env production"
  • Deploy the app
    • fly deploy . --app app_name --image flyio/postgres:14

When deployed I get the error /node_modules/knex/bin/cli.js: No such file or directory. I’m guessing this is probably due to the flyio/postgres:14 image not copying over the node_modules folder (Source) but I’m not 100% sure. I was basing my approach on this guide on the community forum however I think it may have left out a few steps.

Questions

  1. Am I correct that the reason this is failing is because the flyio/postgres:14 image does not copy over the node_modules?
  2. Should I be creating a seperate app specifically for the migrations instead of trying to add the migrations to the existing postgres app/toml?
  3. Is there a docker image well suited to this task or do I have to build my own?
  4. Are there any other steps I am missing, e.g. If I have read replicas do I need to somehow run the migration against those as well?

Any help would be much appreciated, I’m not very familiar with Docker or best practices around how to run migrations on fly.io postgres clusters. If anyone has this same setup (running migrations with knex) it would be great to know how you did it.

2

Are you running a NodeJS app that accesses the database? If so, the release command on your NodeJS app’s fly.toml. Does that make sense?

If you don’t have an app like this, you’ll need to create one solely for the purpose of running migrations. While you could modify the postgres image to do this, I’d recommend against deviating from the official Fly images.

4
  1. Am I correct that the reason this is failing is because the flyio/postgres:14 image does not copy over the node_modules?

You should add your migrations command to your node app fly.toml instead of the fly postgres one. Knex should be installed together with your app.

  1. Should I be creating a seperate app specifically for the migrations instead of trying to add the migrations to the existing postgres app/toml?

If I’m guessing your architecture right you have a Fly Nodejs app and a Fly Postgres DB. The db should not know about migrations, it’s the NodeJS app that should run them.

  1. Is there a docker image well suited to this task or do I have to build my own?

As long as your nodejs docker image has node_modules (which should be the case otherwise your node app will probably not run)

  1. Are there any other steps I am missing, e.g. If I have read replicas do I need to somehow run the migration against those as well?

Probably what I talked for your first question: run migrations on your node app

6

Thanks for the speedy responses!

We were going to have a few Node.js apps that will access the DB so it seems like it’ll make sense to create another app specifically for the migrations (otherwise it’s a bit arbitrary which app the migrations should go in).

Two follow up questions:

  1. Would it make sense to use the heroku/buildpacks:20 image for the migrations app?
  2. Would running the release command on this migration app automatically run the migration against the read replicas as well? Or is that something I have to handle manually?
7

We were going to have a few Node.js apps that will access the DB so it seems like it’ll make sense to create another app specifically for the migrations (otherwise it’s a bit arbitrary which app the migrations should go in).

I agree with you

  1. Would it make sense to use the heroku/buildpacks:20 image for the migrations app?

This image will work just fine though if you want to optimize things a little bit more and since your migration app will be simple perhaps you can go for a NodeJS alpine image? But don’t worry too much about it

  1. Would running the release command on this migration app automatically run the migration against the read replicas as well? Or is that something I have to handle manually?

Yea, the cluster should be on sync :wink: