Drizzle Team•16mo ago

What's the best way to deploy migrations on production database?

I'm confused as to what is the best way to deploy migration on the production database. Locally when I test on my local database instance, I use the commands manually to generate and push the migrations But I want to automate this. Say, I'm using Vercel and I want to push the migration on every commit. In prisma to deploy the generated migrations I use the command prisma migrate deploy, I add this command to the build command like this: prisma generate && prisma migrate deploy && next build There are my scripts in package.json

"dev": "vite dev",
"build": "vite build",
.....
"db:start": "supabase start",
"db:stop": "supabase stop --backup",
"db:status": "supabase status",
"db:generate": "drizzle-kit generate:pg",
"db:push": "tsx ./src/lib/server/db/migrate.ts",
"db:migrate": "pnpm db:generate && pnpm db:push",
"db:studio": "drizzle-kit studio --port 3000 --verbose"

"dev": "vite dev",
"build": "vite build",
.....
"db:start": "supabase start",
"db:stop": "supabase stop --backup",
"db:status": "supabase status",
"db:generate": "drizzle-kit generate:pg",
"db:push": "tsx ./src/lib/server/db/migrate.ts",
"db:migrate": "pnpm db:generate && pnpm db:push",
"db:studio": "drizzle-kit studio --port 3000 --verbose"

14 Replies

nqhtrung•16mo ago

Sorry if this is not what you needed, but here is how I handle migration on live production I create a docker instance and install the bare necessary dependencies just to run the migration script Here is the Dockerfile

FROM --platform=$BUILDPLATFORM node:alpine AS deps
WORKDIR /app

# We only need these dependencies for the migration script
RUN yarn add drizzle-orm postgres dotenv tsx

FROM --platform=$BUILDPLATFORM node:alpine AS builder

WORKDIR /app
COPY --from=deps /app/node_modules ./node_modules
COPY . .

CMD ["yarn", "migration:run"]

FROM --platform=$BUILDPLATFORM node:alpine AS deps
WORKDIR /app

# We only need these dependencies for the migration script
RUN yarn add drizzle-orm postgres dotenv tsx

FROM --platform=$BUILDPLATFORM node:alpine AS builder

WORKDIR /app
COPY --from=deps /app/node_modules ./node_modules
COPY . .

CMD ["yarn", "migration:run"]

Here is the script section in my package.json

"scripts": {
    ...
    "migration:run": "tsx ./drizzle/migration.ts",
    ...
  },

"scripts": {
    ...
    "migration:run": "tsx ./drizzle/migration.ts",
    ...
  },

migration.ts is just a ts file where I define my migrate function. Here is my docker compose file.

version: '3.6'

services:
  postgres_db:
    // docker instance for the db
  app:
    // docker instance for the main app
  migration:
    depends_on:
      - postgres_db
      - app
    build:
      context: .
      dockerfile: Dockerfile.migration // the name of the Dockerfile for migration
    environment:
      - DB_USER=${DB_USER}
      - DB_PASS=${DB_PASS}
      - DB_NAME=${DB_NAME}
      - DB_PORT=5432
      - DB_DOMAIN=postgres_db
      - NODE_ENV=production
volumes:
  postgres_db:

version: '3.6'

services:
  postgres_db:
    // docker instance for the db
  app:
    // docker instance for the main app
  migration:
    depends_on:
      - postgres_db
      - app
    build:
      context: .
      dockerfile: Dockerfile.migration // the name of the Dockerfile for migration
    environment:
      - DB_USER=${DB_USER}
      - DB_PASS=${DB_PASS}
      - DB_NAME=${DB_NAME}
      - DB_PORT=5432
      - DB_DOMAIN=postgres_db
      - NODE_ENV=production
volumes:
  postgres_db:

This way, every time you run the docker compose, you start the db, the app and THEN the migration will run, the container will automatically shut down when the migration succeeds (or fails). Since migrations are safe to run when they have already been run, we can start migrations every time we start the app.

Startup Spells 🪄 Newsletter Guy•10mo ago

@sik did you find a way? i have the same question -> https://stackoverflow.com/q/77877947/6141587 @nqhtrung i dont wanna use dockerfile if it can be simplified. i do think its just running 1 script that can be done with npm run prestart ... lmk if you have other ways... im thinking of prestart (see my script on stackoverflow)

Stack Overflow

How to run generate & migrate in production on a VPS with SQLite da...

So I have a database in development that I run db:generate & db:migrate on to create the database & then run my app that uses that database. And it works fine. But how do I do that in produ...

Suji•10mo ago

here is my migration script

import { config } from 'dotenv';
import { drizzle } from 'drizzle-orm/postgres-js';
import { migrate } from 'drizzle-orm/postgres-js/migrator';
import postgres from 'postgres';
import { exit } from 'process';

config();

const migrateDb = async () => {
  const connectionString = process.env.DATABASE_URL;

  if (!connectionString) {
    throw new Error('DATABASE_URL environment variable is not set');
  }

  const sql = postgres(connectionString, { max: 1 });
  const db = drizzle(sql);

  await migrate(db, { migrationsFolder: 'drizzle' });

  console.info('Migrations are successfully applied');
  exit(0);
};

void migrateDb();

import { config } from 'dotenv';
import { drizzle } from 'drizzle-orm/postgres-js';
import { migrate } from 'drizzle-orm/postgres-js/migrator';
import postgres from 'postgres';
import { exit } from 'process';

config();

const migrateDb = async () => {
  const connectionString = process.env.DATABASE_URL;

  if (!connectionString) {
    throw new Error('DATABASE_URL environment variable is not set');
  }

  const sql = postgres(connectionString, { max: 1 });
  const db = drizzle(sql);

  await migrate(db, { migrationsFolder: 'drizzle' });

  console.info('Migrations are successfully applied');
  exit(0);
};

void migrateDb();

This file lives at the root of the project and the migration folder 'drizzle' is also on the same level i use this on railway and on eb by just appending it to the build step as you have done, works so far for me

Startup Spells 🪄 Newsletter Guy•10mo ago

i use https://www.jacobparis.com/content/remix-drizzle which is working really well. its in the schema folder itself so anytime you start the app, it runs. i'm gonna test it again to confirm & update my answer bcz i think i've found a simple solution.

jmandrosz•10mo ago

I did it like this, simple way and works In my package json build command, I am using my custom build script

jmandrosz•10mo ago

jmandrosz•10mo ago

which looks like this

jmandrosz•10mo ago

and here is migrate.js file

jmandrosz•10mo ago

and thats it, I hope that helps

Startup Spells 🪄 Newsletter Guy•10mo ago

now i like that. i think i can use bun shell from https://bun.sh instead of .sh to write some javascript -> https://bun.sh/blog/the-bun-shell & https://bun.sh/docs/runtime/shell

jmandrosz•10mo ago

sure, if u will do it with bun you can paste the solution here, I do not have time for this right now haha

Startup Spells 🪄 Newsletter Guy•10mo ago

so i learned docker & thought it would be easy but migrations was hard. took me a week on this thing due to too many issues working in parallel. however, i finally got a solution which you can use if you have this combination (drizzle + next.js + sqlite + docker) -> https://stackoverflow.com/a/78034626/6141587

Stack Overflow

Cannot find package 'drizzle-orm' in Docker Container even though I...

I install all my dependencies in Dockerfile using npm ci below. Dockerfile FROM node:20-alpine AS base 1. Install dependencies only when needed FROM base AS deps Check https://github.com/nodejs/

Gaming

Programming

What's the best way to deploy migrations on production database?