Question on Handling Database Migrations in node-postgres Template

[jonathanpalma]

Hi there 👋

I wanted to ask how you typically handle database migrations in your Remix/RR7 apps, especially during deployment to platforms like Fly.io.

I ran into an issue when attempting to use drizzle-kit for migrations on Fly.io. Since drizzle-kit is in devDependencies, it is not available on my prod image. And, installing drizzle-kit as a production dependency felt like overkill since it’s primarily a development tool.

To work around this, I ended up implementing the following solution:

1. Custom Migration Script:

I created a script (database/migrate.ts) to run migrations programmatically using Drizzle ORM:

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

const migrationsClient = postgres(process.env.DATABASE_URL, { max: 1 });

async function runMigrations() {
  try {
    const db = drizzle(migrationsClient);
    await migrate(db, { migrationsFolder: "drizzle" });
    console.log("Migrations ran successfully.");
  } catch (error) {
    console.error("Error running migrations:", error);
    process.exit(1);
  } finally {
    await migrationsClient.end();
    process.exit(0);
  }
}

void runMigrations();

2. Fly.io Deployment:

I configured Fly.io’s release_command to execute the script during deployment:

[deploy]
  release_command = "npx tsx /app/database/migrate.ts"

3. Dockerfile:

Here’s the Dockerfile I’m using for the app:

FROM node:20-alpine AS dependencies-env
ENV DOCKER=true
RUN npm i -g pnpm

FROM dependencies-env AS development-dependencies-env
ENV DOCKER=true
COPY ./package.json ./pnpm-lock.yaml /app/
WORKDIR /app
RUN pnpm install --frozen-lockfile

FROM dependencies-env AS production-dependencies-env
ENV DOCKER=true
COPY ./package.json ./pnpm-lock.yaml /app/
WORKDIR /app
RUN pnpm install --prod --frozen-lockfile

FROM dependencies-env AS build-env
ENV DOCKER=true
COPY ./package.json ./pnpm-lock.yaml /app/
COPY --from=development-dependencies-env /app/node_modules /app/node_modules
COPY . /app
WORKDIR /app
RUN pnpm build

FROM dependencies-env
ENV DOCKER=true
COPY ./package.json ./pnpm-lock.yaml ./drizzle.config.ts ./server.js /app/
COPY ./database/ /app/database/
COPY ./drizzle/ /app/drizzle/
COPY --from=production-dependencies-env /app/node_modules /app/node_modules
COPY --from=build-env /app/build /app/build
WORKDIR /app
CMD ["node", "server.js"]

This approach works, but I’m curious to know your thoughts:

• How do you typically handle migrations in your Remix/RR7 apps?
• Do you use tools like drizzle-kit directly, and if so, how do you manage its usage in production environments?

Looking forward to your insights—thank you in advance for your help!

[undefinitely]

I was wondering the same thing.

Currently I have an almost identical Dockerfile but I went with the release command npx drizzle-kit migrate

[jonathanpalma]

Did that work for you?