Next.js Discord

Discord Forum

Run Drizzle migration function before next start on Vercel

Unanswered
Black Turnstone posted this in #help-forum
Open in Discord
Black TurnstoneOP
I searched almost everywhere, but I really can't find a correct way of handling Drizzle migrations before the server starts.

I have the code below, I run with using npx tsx, this works. However, how and when would you run this during in a production environment? How do I run it on Vercel, preferrably before the server starts and after the build. I can't find any correct examples online, they are all development focused. No real-world setup can be found.

Thanks in advance.

// migrate.ts
import { drizzle } from "drizzle-orm/postgres-js";
import { migrate } from "drizzle-orm/postgres-js/migrator";
import postgres from "postgres";

export async function runMigrate() {
  if (!process.env.POSTGRES_URL) {
    throw new Error("POSTGRES_URL is not defined");
  }

  if (process.env.NODE_ENV === "development") {
    console.log(":white_check_mark: Migrations skipped in development");
    return;
  }

  const connection = postgres(process.env.POSTGRES_URL, { max: 1 });
  const db = drizzle(connection);

  console.log(":hourglass_flowing_sand: Running migrations...");

  const start = Date.now();
  await migrate(db, { migrationsFolder: "./src/lib/db/migrations" });
  const end = Date.now();

  console.log(":white_check_mark: Migrations completed in", end - start, "ms");
}

runMigrate()
  .then(() => {
    process.exit(0);
  })
  .catch((error) => {
    console.error(":x: Migration failed");
    console.error(error);
    process.exit(1);
  });

21 Replies

@Black Turnstone I searched almost everywhere, but I really can't find a correct way of handling Drizzle migrations before the server starts. I have the code below, I run with using npx tsx, this works. However, how and when would you run this during in a production environment? How do I run it on Vercel, preferrably before the server starts and after the build. I can't find any correct examples online, they are all development focused. No real-world setup can be found. Thanks in advance. // migrate.ts import { drizzle } from "drizzle-orm/postgres-js"; import { migrate } from "drizzle-orm/postgres-js/migrator"; import postgres from "postgres"; export async function runMigrate() { if (!process.env.POSTGRES_URL) { throw new Error("POSTGRES_URL is not defined"); } if (process.env.NODE_ENV === "development") { console.log(":white_check_mark: Migrations skipped in development"); return; } const connection = postgres(process.env.POSTGRES_URL, { max: 1 }); const db = drizzle(connection); console.log(":hourglass_flowing_sand: Running migrations..."); const start = Date.now(); await migrate(db, { migrationsFolder: "./src/lib/db/migrations" }); const end = Date.now(); console.log(":white_check_mark: Migrations completed in", end - start, "ms"); } runMigrate() .then(() => { process.exit(0); }) .catch((error) => { console.error(":x: Migration failed"); console.error(error); process.exit(1); });
Yi Lon Ma
https://nextjs.org/docs/app/api-reference/file-conventions/instrumentation
you will need to create instrumentation.ts|js and call your migrate code inside register()
or you can also do inside next.config
Asian black bear
Also, automated migrations are a bad idea to begin with. If the build succeeds and the migration doesn't you end up with a broken app. Similarly if the migration succeeds but your app doesn't work then you can't rollback to the previous deployment which will be incompatible with the new database schema.
Black TurnstoneOP
@Asian black bear I saw this same response online. I agree, but whats the alternative?
@Yi Lon Ma Thanks for the quick response. I actually tried this already. But I get the following error:

I forgot to add, I have this code above the function in the same file:
config({
  path: ".env",
});
@Black Turnstone <@141252432054190080> I saw this same response online. I agree, but whats the alternative?
Asian black bear
Using non-breaking migrations by expanding the schema and then contracting it afterwards. For serious projects this is the most recommended approach: https://planetscale.com/blog/backward-compatible-databases-changes
Black TurnstoneOP
Mmm. But at what point in the deployment does the actual migration happen?
Asian black bear
This is not a migration that is automated.
Black TurnstoneOP
What if the migrations succeeds after the build. Example I remove a column and add a new column, starting up the new instance takes some seconds, the old instance is live and is still connected to the with the now new schema database, it would be broken for the time till the new instance is live because the schemas don’t add up. Correct?

How would I do it manually the? And when?
Or what is your recommended way in my case, which is a common case. Appreciate it already.
Asian black bear
The blog post explains it pretty much. If you plan to rename a column for example you add a new one, write into it, backfill, rewire reads, verify everything works with a new deployment and remove the old column afterwards.
You never want to attempt to remove or alter columns that are in use.
You deprecate them and adjust it in multiple steps.
Black TurnstoneOP
I see. So if I made sure these migrations are safe. How would I automate the migration or manually. Let’s say I just add a new table called “comments”. I added some application logic and locally I did db:generate, push to GitHub (migrations included), and then?
Because locally I just did db:push for quick prototyping, this is connected to my local pg instance.
Asian black bear
It depends how much you want to version and document schema changes. If you don't need any of that it's as easy as literally pushing the new schema manually into production if you're confident enough it won't break anything or purge the table due to missing key constraints (for example accidentally pushing a new non-null column into a table that would otherwise purge all rows).

Alternatively run database migrations as part of your CI as a parallel task to deploying your app and write them in such a way that neither step failing can bring down your production app.
Black TurnstoneOP
That’s exactly what I try to do with the script I posted in this post. I want to run this script after (I think) after the build and before the start script. But it fails when I add it before the next start.

Yes, simply pushing it would also work in my case, if I made sure these schema changes are safe.
Asian black bear
Split the migration from the deployment entirely. That should not be depending on next build or next start
Black TurnstoneOP
Then how do I make it part of my CI? Lets make the script simple, because it doesn’t run. If want to run “drizzle-kit push” somewhere in my CI process, where should it run? If it should be decoupled from the next build or start?
Asian black bear
Keep in mind, pushing is very risky if you don't pay attention to it. A conventional migration is much safer if you follow the details outlined in the blog post.

Assuming you host your code on GitHub you create a GitHub Actions workflow that runs the Drizzle migration command whatever that is in a separate job.