How to run migrations in database inside Dockerfile using Node.js?

[deadcoder0904]

I've been trying to get Database migrations working in Drizzle + SQLite but have no luck so far.

I've had questions that aren't solved. Like for example, drizzle-orm is not found because node_modules doesn't have a copy of it.

I tried using anonymous volumes as suggested here but despite doing that, I still get the same error when i try docker logs nextjs-sqlite:

> easypanel-nextjs-sqlite@0.1.0 db:migrate:prod
> node --env-file .env.production ./scripts/drizzle-migrate.mjs

node:internal/modules/esm/resolve:853
  throw new ERR_MODULE_NOT_FOUND(packageName, fileURLToPath(base), null);
        ^

Error [ERR_MODULE_NOT_FOUND]: Cannot find package 'drizzle-orm' imported from /app/scripts/drizzle-migrate.mjs
    at packageResolve (node:internal/modules/esm/resolve:853:9)
    at moduleResolve (node:internal/modules/esm/resolve:910:20)
    at defaultResolve (node:internal/modules/esm/resolve:1130:11)
    at ModuleLoader.defaultResolve (node:internal/modules/esm/loader:396:12)
    at ModuleLoader.resolve (node:internal/modules/esm/loader:365:25)
    at ModuleLoader.getModuleJob (node:internal/modules/esm/loader:240:38)
    at ModuleWrap.<anonymous> (node:internal/modules/esm/module_job:85:39)
    at link (node:internal/modules/esm/module_job:84:36) {
  code: 'ERR_MODULE_NOT_FOUND'
}

Node.js v20.11.1

docker-compose.yml

version: '3.8'

services:
  web:
    build:
      context: .
      dockerfile: docker/web/Dockerfile
    # depends_on:
    #   db:
    #     condition: service_healthy
    #   redis:
    #     condition: service_started
    image: easypanel-nextjs
    container_name: nextjs-sqlite
    env_file:
      - .env.production
    ports:
      - 3000:3000
    volumes:
      - ./data:/data
      - /app/node_modules
  # migration:
  #   build:
  #     context: .
  #     dockerfile: docker/migrations/Dockerfile
  #   image: easypanel-nextjs
  #   depends_on:
  #     web:
  #       condition: service_completed_successfully

Dockerfile

FROM node:20-alpine AS base

# 1. Install dependencies only when needed
FROM base AS deps
# Check https://github.com/nodejs/docker-node/tree/b4117f9333da4138b03a546ec926ef50a31506c3#nodealpine to understand why libc6-compat might be needed.
RUN apk add --no-cache libc6-compat

RUN mkdir /app
WORKDIR /app

# Install dependencies based on the preferred package manager
COPY package.json yarn.lock* package-lock.json* pnpm-lock.yaml* ./
COPY /src/app/db/migrations ./migrations

RUN \
  if [ -f yarn.lock ]; then yarn --frozen-lockfile; \
  elif [ -f package-lock.json ]; then npm ci; \
  elif [ -f pnpm-lock.yaml ]; then corepack enable pnpm && pnpm i; \
  else echo "Lockfile not found." && exit 1; \
  fi
# USER node

# 2. Rebuild the source code only when needed
FROM base AS builder
WORKDIR /app
COPY --from=deps /app/node_modules ./node_modules
RUN chown -R node:node ./node_modules

COPY . .

# This will do the trick, use the corresponding env file for each environment.
COPY .env.production .env.production
RUN mkdir -p /data

ENV NODE_ENV=production
ENV NEXT_TELEMETRY_DISABLED=1

# Create /data/users.prod.sqlite using Volume Mount
# RUN npm run db:migrate:prod
RUN npm run build

# 3. Production image, copy all the files and run next
FROM base AS runner
WORKDIR /app

ENV NODE_ENV=production
ENV NEXT_TELEMETRY_DISABLED=1

RUN addgroup -g 1001 -S nodejs
RUN adduser -S nextjs -u 1001

COPY --from=builder /app/public ./public
# COPY --from=builder --chown=nextjs:nodejs /data /data

# Automatically leverage output traces to reduce image size
# https://nextjs.org/docs/advanced-features/output-file-tracing
COPY --from=builder --chown=nextjs:nodejs /app/.next/standalone ./
COPY --from=builder --chown=nextjs:nodejs /app/.next/static ./.next/static

# Move the drizzle directory to the runtime image
COPY --from=builder --chown=nextjs:nodejs /app/src/app/db/migrations ./migrations

# Move the run script and litestream config to the runtime image
COPY --from=builder /app/scripts/drizzle-migrate.mjs ./scripts/drizzle-migrate.mjs
# COPY --from=builder /app/scripts/init.sh ./init.sh
COPY --from=builder /app/scripts/run.sh ./run.sh
# RUN chmod +x init.sh
RUN chmod +x run.sh

# Create data directory or else `npm run db:migrate:prod` will fail with TypeError: Cannot open database because the directory does not exist
# RUN mkdir -p /data

USER nextjs

EXPOSE 3000

# ENV PORT 3000
# ENV HOSTNAME localhost

# CMD ["npm", "run", "start"]
# ENTRYPOINT ["init.sh"]
RUN ls -la

CMD ["sh", "run.sh"]

run.sh

#!/bin/bash
set -e

npm run db:migrate:prod & PID=$!
# Wait for migration to finish
wait $PID

echo "Starting production server..."
node server.js & PID=$!

wait $PID

I can't run migrations in dockerfile as it will build it in the image so .sqlite file won't be created.

I have to run it in the container just before I run my next.js server so .sqlite file gets created.

Locally, I do npm run db:generate to create migrations/ folder & npm run db:migrate to create users.dev.sqlite which reads files from migrations/ folder.

My basic goal is to get *.sqlite file in the production while using bind mount. But unsuccessful for a week.

Other ORMs do it easily like I found this blog that does it easily with Prisma + Nest -> https://notiz.dev/blog/prisma-migrate-deploy-with-docker

But I'm using Drizzle instead of Prisma which doesn't have a direct command like prisma migrate deploy so I have to run an npm script.

How would I do it?

I have a sample proof-of-concept -> https://github.com/deadcoder0904/easypanel-nextjs-sqlite (Next.js + Drizzle + SQLite + Docker)

My entire goal is to make SQLite file successfully created in production docker container. Unfortunately, there are no answers for Drizzle + Docker rather than complex hacks which I only found one person implement.

Is there a solution to this?

[BretFisher]

OK, so there's a lot here and it's unclear what the exact question/problem is. I see you've updated your SO question saying you've fixed it. In general, you don't create a db, or run db migrations in a Dockerfile at image-build-time, you run them as a job in swarm/kubernetes (if that's what you're using) outside of the apps. One of the main reasons it's not a good idea to run migrations with the app (nextjs, etc.) container is that you'll usually run many of those for load balancing and redundancy in production, and you only want one migration job running (not one per container replica).

This has lead to many different designs of how to do migrations during an apps update (replacing all the containers with the new version of the app), and it sometimes depends on the features of the ORM/app and sometimes limited by the features of the runtime (docker, compose, swarm, kubernetes, etc.)

[deadcoder0904]

thanks @BretFisher for the answer. i don't think my app needs to use swarm/kubernetes although the application i'm using easypanel.io uses docker swarm underneath. i'm deploying this thing on sqlite for the same reason as i don't think i'll have multiple writers either since it is a simple app. anyways, the solution works for now.

i read what you said and i have 2 questions:

1. how to run migrations in docker? whether its an orm like prisma/drizzle/kysely or raw sqlite?

locally, when i do pnpm db:generate, it creates migrations/ folder. this folder is needed to generate .sqlite file in both development & production.

i run migrations using pnpm db:migrate:prod which uses migrations/ folder above to create a .sqlite file.

you said that i have to use a job. what if i don't have swarm/kubernetes access? just simple dockerfile. what then? what i'm doing is perfect, right? also, can you point links to what you said like using a job. i've never seen anything like it as i'm new to devops.

2. my question is why my sqlite doesn't work when enabled wal mode on docker? im on windows. like if i open the database file in a desktop browser & add items in web browser. then it shows the updated database in browser but doesn't update it in the desktop app. and when i stop the app (docker-compose down), the data is lost. funny thing is the same thing works without wal mode. not sure if its a sqlite error, windows error, wal mode error, or something else. lmk if you have any ideas.

anyways, thanks for your blog & repo. picked up some best practices in quite little time.

[BretFisher]

good point... you'll only have one writer with sqlite.

• Migrations, yea either in container startup (k8s makes this easier with initContainer, which I have an example of over here) or Job resources that work outside of the app pods. In Docker/Swarm we're more limited, so either it would be an ENTRYPOINT script before the app starts or in a Swarm Job.

Note, that when using something like ENTRYPOINT to run a shell script, before it passes execution to CMD, you have to ensure other containers aren't also trying to migrate, so a real simple approach is to start the ENTRYPOINT script with something that sets "migrations started", which could be a simple unique db table that you set true in, or a shared VOLUME that you write a file to. The point there is to prevent the 2nd container starting from also starting migrations if another is currently running them. Some ORMs may have a detection feature already that helps avoid any clashing of "dual migration runs." Note this was an issue before containers when we had multiple servers getting updated at the same time, but it's just more common now in containers.

I'm not a sqlite person, so I don't know about the errors, but ideally docker compose down doesn't remove your data. If you name your VOLUME, and ensure the sqlite db/log is in that volume, then a docker compose down won't touch it. You'd only be able to delete the VOLUME with docker compose down -v. If you have an anonymous volume that's not named, then a standard down will delete the volume.

[deadcoder0904]

@BretFisher thanks for the answers. i figured it out. see the comment on this serverfault post. this was really interesting.

i used ENTRYPOINT but it was tedious (I could've figured out but found a simpler way) so i went with just putting it on top of run.sh.

drizzle doesn't run the same migrations so you are right on "dual migration" race condition. i like the database flag idea. i think that's how drizzle & keysley do it by having small tables in the database. even litestream.

if you ever use SQLite, this contains a working demo with your best practices -> https://github.com/deadcoder0904/easypanel-nextjs-sqlite/ (if you read the README, i found issues with sqlite + wal mode in docker but they were gone when using litestream as litestream uses its own wal mode plus linux behaves differently when a file is opened. i think it was a classic case of me trying every edge case even though most of it won't happen on a real production usecase)

i'll continue using sqlite (wal) + litestream as i figured out data loss issues mostly thanks to litestream. again, thanks for your help. your docker + node.js combo was supercool. if you can, do one video on docker + bun. i can't figure out why they make this supercomplicated?