How to manage database migrations in a Next.js app?

[lancejpollard]

I am using Kysely to run migrations on a Supabase Postgres database, but so far am only doing all this without any traffic/users so I can make mistakes. I basically write the migration locally in a migrations/0001.ts file (that would be the first migration), and run it using pnpm tsx scripts/migrate, where that migration script for Kysely looks like this:

import * as path from 'path'
import db from '~/configurations/kysely'
import { promises as fs } from 'fs'
import { Migrator, FileMigrationProvider } from 'kysely'
import 'dotenv/config'

async function migrateToLatest() {
  const migrator = new Migrator({
    db,
    provider: new FileMigrationProvider({
      fs,
      migrationFolder: path.resolve('./migrations'),
      path,
    }),
  })

  const { error, results } = await migrator.migrateToLatest()

  results?.forEach(it => {
    if (it.status === 'Success') {
      console.log(
        `migration "${it.migrationName}" was executed successfully`,
      )
    } else if (it.status === 'Error') {
      console.error(`failed to execute migration "${it.migrationName}"`)
    }
  })

  if (error) {
    console.error('failed to migrate')
    console.error(error)
    process.exit(1)
  }

  await db.destroy()
}

migrateToLatest()

If the migration fails, then it doesn't change the database, so it appears there's no need for a rollback? Not totally sure how rollbacks fit in.

But my main concern/question is around how a production app on Next.js should run and deploy database migrations/changes to production. When you do a git push to your GitHub Next.js app, hosted on Vercel, it automagically deploys if the build succeeds. How do I fit in a database migration step into this workflow/system? What are the main stumbling blocks to watch out for even as well?

I am coming from Ruby on Rails, though for the past 5+ years I've been doing mostly frontend work so I've forgotten the nitty-gritty of how to do git push and database migrations in a Rails environment, and not sure how it parallels with a Next.js / Vercel deployed app. Can you paint a high-level picture of how this should work in best-practices or the practical way?

After brushing up by asking ChatGPT 3.5 about how Rails migrations and deploys work, it seems to be such that you first run your migration in a git branch (call it dev branch), and that updates the schema.rb with the final database state. Then when you merge this to the main branch, this triggers a deploy (I'm imagining Heroku here, but don't remember how it works in detail). The deploy means git clone the latest main branch, and start the server, on a new "thread" so to speak, which we can point the load balancer to once it is ready/successfully started. When it is successful, we point the system to the newly running app and stop that previously running app. What I am not visualizing clearly though is where in this process the production migration is run.

It seems to be that rake db:migrate is called in production at some point, maybe in a circleci script or something like that, I don't remember. What I'm wondering is how do you avoid deploying an app with breaking data model / schema changes?? If I change a few column names and types, and my new code references those types, it seems the old code will break while the new app is spinning up. What am I missing here?

The main thing I'm wondering is how to apply these learnings to Next.js on Vercel, running migrations in general. How do I deploy my database changes and not have the old or new app break basically? Would you be able to describe at a high level how this works in practice in todays environment?

Additional Information

ChatGTP also mentioned "blue-green deployments", which I've never heard of:

For larger applications, you can implement blue-green deployments to minimize downtime further. In this setup, you maintain two separate environments (blue and green). You deploy updates to one environment while the other continues to serve traffic. This allows you to switch between environments quickly if issues arise.

I don't see how this is possible to do with a centralized database server, but maybe some light can possibly be shed on here too if it helps illustrate how to properly do database migrations with Next.js / Vercel. Any help/perspective would be helpful, I know this is a potentially broad and deep question to answer, but only looking for the high-level bullet points basically, or even just some of them.

Note: Feel free to focus on prisma, knex.js, or any other database tool instead, as that would probably be similar in the end to Kysely, and I don't care too much about specific tools at this part in the journey yet.

Some Reading

• https://www.bytebase.com/blog/how-to-handle-database-schema-change/
• https://vercel.com/blog/incremental-migrations
• https://supabase.com/docs/guides/cli/managing-environments
• https://vercel.com/guides/set-up-a-staging-environment-on-vercel
• https://vercel.com/guides/how-can-i-use-github-actions-with-vercel

[markomitranic]

Hey @lancejpollard

I don't have an answer for you.
Just leaving some context on how this is done elsewhere, hope its useful :)

The only reason that I'm writing is to say that everything you've discovered in this process is in fact ABSOLUTELY commonplace in other ecosystems. Ruby, Java, PHP, Elixir, C#, Nest, Python all have very reliable and well thought-out tooling for this stuff. I've used it all my life. I suggest you check out how Elixir-Phoenix or Symfony and Laravel do this. I believe NestJS uses a npm package “db-migrate”. You would usually see a migration run either at the very end of build step, or preferably immediately before running the executable. I wish to find something similar in our ecosystem and make it serverless friendly by running after build. Mostly because next by its nature often relies on the db data to prerender stuff as part of the build process, that is tricky and an antipattern elsewhere.

There are many different models for doing the switch, and it can get quite complex. However, most applications are perfectly fine with simply letting it slide, migrations usually take sub-second to finish, which means someone might see an error buu-huu no biggie. A simple way to avoid this for websites with higher throughput is to have a tiny downtime while the migration runs, it can be powered by a very fast feature flag switch, and masked via good caching practices.

When it comes to the unhappy path case when the build fails, but the migration has already been run, other ecosystems deal with it by separating compilation from deployment and ensuring the compilation does not need a database. In addition some tools try to run migrations as a series of rollbackable transactions, or a single transaction, so that the steps are run but db schema is not migrated until the actual flush happens - removing the need for a rollback, and helping the old code work until the very last second. For reference, Elixir is good at this.

Blue-green gets a bit more complex. You wouldn't go that far unless you have to. It is similar to the above example, but usually means that you are running two databases, or a backwards-compatibile dataset. Your new depoyment will essentially use a different database instance, with the data being synchronized between the two, into the new format. Depending on your requirements (is it write heavy, can we disable write during migration etc) this can get more complex. The worst case I've dealt with is doing blue-green on a distributed or sharded set.

Anyway, please update us on if you figured something out!