How to perform Drizzle Migrations in SQLite using Docker on production database on a VPS using Next.js?

Question

I want to perform a Drizzle Migration on Production on a VPS.

I can't run 2 commands pnpm db:migrate:prod & pnpm start in a Dockerfile.

Dockerfile

# Where & how do I run `db:migrate:prod`?

CMD ["npm", "run", "start"]

I want to create users.prod.sqlite on the production server that is persisted even if the container goes down.

Starter repo -> https://github.com/deadcoder0904/easypanel-nextjs-sqlite/tree/1fb34233283b1ff7b07b7f18e6973125ff96cbba

How would I do it?

Answer 1

Latest answer (USE THIS)

I had to create a new package.json with just Drizzle Migration dependencies.

package.json

{
  "name": "scripts",
  "version": "1.0.0",
  "description": "",
  "main": "index.js",
  "scripts": {
    "test": "echo \"Error: no test specified\" && exit 1"
  },
  "dependencies": {
    "better-sqlite3": "^9.4.3",
    "drizzle-orm": "^0.29.4",
    "std-env": "^3.7.0"
  },
  "keywords": [],
  "author": "",
  "license": "ISC"
}

I also removed node_modules from the final image as it wasn't required anymore. This reduced the image size a lot down to just 175mb.

I removed prod-dependencies FROM-block from Dockerfile so it simplified it a bit.

And copied scripts/package.json to install dependencies from it.

Dockerfile

FROM node:20-alpine AS base

# mostly inspired from https://github.com/BretFisher/node-docker-good-defaults/blob/main/Dockerfile & https://github.com/remix-run/example-trellix/blob/main/Dockerfile

# 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 corepack enable && corepack prepare [email protected] --activate 
# set the store dir to a folder that is not in the project
RUN pnpm config set store-dir ~/.pnpm-store
RUN pnpm fetch

# 1. Install all dependencies including dev dependencies
FROM base AS deps
# Root user is implicit so you don't have to actually specify it. From https://stackoverflow.com/a/45553149/6141587
# USER root
USER node
# WORKDIR now sets correct permissions if you set USER first so `USER node` has permissions on `/app` directory
WORKDIR /app

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

USER root
RUN pnpm install --frozen-lockfile --prefer-offline

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

COPY --chown=node:node . .

# This will do the trick, use the corresponding env file for each environment.
COPY --chown=node:node .env.production .env.production

# Copied from https://stackoverflow.com/a/69867550/6141587
USER root
# Give /data directory correct permissions otherwise WAL mode won't work. It means you can't have 2 users writing to the database at the same time without this line as *.sqlite-wal & *.sqlite-shm are automatically created & deleted when *.sqlite is busy.
RUN mkdir -p /data && chown -R node:node /data

ENV NODE_ENV=production
ENV NEXT_TELEMETRY_DISABLED=1

RUN pnpm build

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

EXPOSE 3000

ENV PORT 3000
ENV HOSTNAME '0.0.0.0'
ENV NODE_ENV=production
ENV NEXT_TELEMETRY_DISABLED=1

COPY --from=builder --chown=node:node /app/public ./public

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

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

# Move the run script and litestream config to the runtime image
COPY --from=builder --chown=node:node /app/scripts/drizzle-migrate.mjs ./scripts/drizzle-migrate.mjs
COPY --from=builder --chown=node:node /app/scripts/package.json ./scripts/package.json
COPY --from=builder --chown=node:node /app/scripts/pnpm-lock.yaml ./scripts/pnpm-lock.yaml
COPY --from=builder --chown=node:node /app/scripts/run.sh ./run.sh
RUN chmod +x run.sh

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

I install the only needed dependencies for migration here.

run.sh

#!/bin/bash
set -e

# Only install dependencies for drizzle migration. Those are not bundled via `next build` as its optimized to only install dependencies that are used`
echo "Installing production dependencies"
cd scripts
pnpm config set store-dir ~/.pnpm-store
pnpm fetch
pnpm install --prod --prefer-offline
cd ..

echo "Creating '/data/users.prod.sqlite' using bind volume mount"
pnpm run db:migrate:prod & PID=$!
# Wait for migration to finish
wait $PID

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

wait $PID

New Answer

I removed the pnpm install from run.sh that was slow to boot container (docker compose up) up. So the 1 minute wait went away. And I copied node_modules with production dependencies in the final stage which made my image go from 198mb to 611mb but that's okay for me.

Then I tried adding SQLite WAL mode which resulted in data loss due to networking issue. Idk why that doesn't work properly but its neither Docker's fault nor my Volume syntax was wrong. So I commented out WAL mode & everything works fine now.

The new solution uses security best practices in Node.js by using non-privileged users. And the solution is much cleaner & simpler to understand.

docker-compose.yml

version: '3.8'

services:
  web:
    image: easypanel-nextjs:0.0.1
    build:
      context: .
      dockerfile: Dockerfile
    container_name: nextjs-sqlite
    env_file:
      - .env.production
    ports:
      - 3000:3000
    volumes:
      - ./data:/data

Dockerfile

FROM node:20-alpine AS base

# mostly inspired from https://github.com/BretFisher/node-docker-good-defaults/blob/main/Dockerfile & https://github.com/remix-run/example-trellix/blob/main/Dockerfile

# 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 corepack enable && corepack prepare [email protected] --activate 
# set the store dir to a folder that is not in the project
RUN pnpm config set store-dir ~/.pnpm-store
RUN pnpm fetch

# 1. Install all dependencies including dev dependencies
FROM base AS deps
# Root user is implicit so you don't have to actually specify it. From https://stackoverflow.com/a/45553149/6141587
# USER root
USER node
# WORKDIR now sets correct permissions if you set USER first so `USER node` has permissions on `/app` directory
WORKDIR /app

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

USER root
RUN pnpm install

# 2. Setup production node_modules
FROM base as production-deps
WORKDIR /app

COPY --from=deps --chown=node:node /app/node_modules ./node_modules
COPY --chown=node:node package.json pnpm-lock.yaml* ./
RUN pnpm prune --prod

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

COPY --chown=node:node . .

# This will do the trick, use the corresponding env file for each environment.
COPY --chown=node:node .env.production .env.production

# Copied from https://stackoverflow.com/a/69867550/6141587
USER root
# Give /data directory correct permissions otherwise WAL mode won't work. It means you can't have 2 users writing to the database at the same time without this line as *.sqlite-wal & *.sqlite-shm are automatically created & deleted when *.sqlite is busy.
RUN mkdir -p /data && chown -R node:node /data

ENV NODE_ENV=production
ENV NEXT_TELEMETRY_DISABLED=1

RUN pnpm build

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

EXPOSE 3000

ENV PORT 3000
ENV HOSTNAME '0.0.0.0'
ENV NODE_ENV=production
ENV NEXT_TELEMETRY_DISABLED=1

COPY --from=builder --chown=node:node /app/public ./public
COPY --from=production-deps --chown=node:node /app/node_modules ./node_modules

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

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

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

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

run.sh

#!/bin/bash
set -e

echo "Creating '/data/users.prod.sqlite' using bind volume mount"
pnpm run db:migrate:prod & PID=$!
# Wait for migration to finish
wait $PID

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

wait $PID

Old Answer (DO NOT USE THIS)

So I figured it out myself. I used a run.sh script to run both those scripts.

run.sh contains both pnpm db:migrate:prod & pnpm start but I used direct node server.js because its a best practice as it handles PID well.

I also used anonymous volumes to maintain node_modules courtesy of https://michalzalecki.com/docker-compose-node/. I install dependencies in run.sh script so it takes time to get container up. Like 1 minute. But its a one-time process so I don't mind however I'd like to optimize.

Anyways, the full solution is at https://github.com/deadcoder0904/easypanel-nextjs-sqlite/

I also used permissions like chown as I learned its a Node.js best practice.

docker-compose.yml

version: '3.8'

services:
  web:
    build:
      context: .
      dockerfile: Dockerfile
    image: easypanel-nextjs
    container_name: nextjs-sqlite
    env_file:
      - .env.production
    ports:
      - 3000:3000
    volumes:
      - ./data:/data
      - /app/node_modules

Dockerfile

FROM node:20-alpine AS base

# mostly inspired from https://github.com/BretFisher/node-docker-good-defaults/blob/main/Dockerfile & https://github.com/remix-run/example-trellix/blob/main/Dockerfile

# 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 corepack enable && corepack prepare [email protected] --activate 
# set the store dir to a folder that is not in the project
RUN pnpm config set store-dir ~/.pnpm-store
RUN pnpm fetch

# 1. Install all dependencies including dev dependencies
FROM base AS deps

# Root user is implicit so you don't have to actually specify it. From https://stackoverflow.com/a/45553149/6141587
# USER root
USER node
# WORKDIR now sets correct permissions if you set USER first so `USER node` has permissions on `/app` directory
WORKDIR /app

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

USER root
RUN pnpm install
USER node

# 2. Setup production node_modules
FROM base as production-deps
WORKDIR /app

COPY --from=deps /app/node_modules ./node_modules
COPY --chown=node:node package.json pnpm-lock.yaml* ./
RUN pnpm prune --prod

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

COPY --chown=node:node . .

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

ENV NODE_ENV=production
ENV NEXT_TELEMETRY_DISABLED=1

RUN pnpm 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

COPY --from=builder --chown=node:node /app/public ./public
COPY --from=production-deps --chown=node:node /app/node_modules ./node_modules

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

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

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

EXPOSE 3000

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

run.sh

#!/bin/bash
set -e

echo "Installing dependencies using pnpm..."
pnpm install & PID=$!
wait $PID

echo "Creating 'data/users.prod.sqlite' using bind volume mount"
pnpm run db:migrate:prod & PID=$!
# Wait for migration to finish
wait $PID

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

wait $PID

The full working version is at https://github.com/deadcoder0904/easypanel-nextjs-sqlite/

The only caveat right now is when I start the container, it takes time because it is doing pnpm install.

I'd love to figure out how to make it so that it doesn't have to pnpm install every time I do make start-production in my project. I use Makefile which you can check at the linked repo.