HomeBlogAbout

Drizzle migrations to postgres in production

September 16, 2024

9 min read

coding drizzle railway sveltekit

I’ve begun working on a SvelteKit app with a PostgreSQL database, Drizzle as ORM, hosted on Railway.

I recently switched from Vercel to Railway as I got frustrated by articifial limitations and the serverless way of working. Long-running servers have existed for eternity and with AI, it’s been trivial to learn how to create a Dockerfile to smoothly host my apps.

In this new application, I’m locally running postgres and have a postgres service running on Railway, next to my application service. I wanted a workflow where I could locally develop using a locally hosted database (instead of connecting to a ‘dev’ database on Railway or another db provider) and run the same database migrations to my staging or production databases, which are hosted in the cloud.

In this guide, we’ll explore how to run Drizzle migrations for a SvelteKit app with a PostgreSQL database, hosted on Railway. We’ll cover:

  1. Running PostgreSQL locally
  2. Configuring Drizzle for both local and production environments
  3. Executing migrations locally and in production
  4. Deploying your app with automatic migrations using Docker and Railway

Running postgres locally

To run Postgres locally we’ll use Docker Compose. Create a docker-compose.yml config file, which allows you to download a postgres image, create a container with your config and start it.

# docker.compose.yml:

services:
    db:
        image: postgres:15
        container_name: project_db
        restart: always
        env_file:
            - .env
        ports:
            - "5432:5432"
        volumes:
            - project_data:/var/lib/postgresql/data

volumes:
    project_data:

We’re referring to a .env file in the config, which allows us to protect our secrets. Ensure there’s a .env file where you’ve defined at least the following postgres config items:

# .env:

POSTGRES_DB="project"
POSTGRES_USER="project_user"
POSTGRES_PASSWORD="hard_password"

Make sure you have Docker and Docker Compose installed on your system. Navigate to the folder of docker-compose.yml and run docker-compose up -d. Once it’s spinning, run docker exec <container_name> env to display the environment variables of the running local database.

From the displayed values, we’ll create your DEV_DATABASE_URL string as follows. Insert your values. Since we’re setting the port in the config file, we can use localhost as the host value.

postgresql://<POSTGRES_USER>:<POSTGRES_PASSWORD>@localhost:5432/<POSTGRES_DB>

Add this back to your .env file like so:

# .env:

POSTGRES_DB="project"
POSTGRES_USER="project_user"
POSTGRES_PASSWORD="hard_password"
DEV_DATABASE_URL="postgresql://project_user:hard_password@localhost:5432/project"

Great. Your database is now running and you have the URL to access it.

Drizzle

Understanding Drizzle: ORM vs. Kit

Before we dive in, it’s important to understand the difference between drizzle-orm and drizzle-kit:

  • drizzle-orm is the ORM that allows you to interact with your database with type safety.
  • drizzle-kit is a CLI companion for running migrations.

While drizzle-kit offers a migrate command, we’ll use drizzle-orm’s migrate() function for production migrations due to its reliability with Railway’s postgres setup.

Configuring Drizzle

Install Drizzle as detailed here. Your installation may differ from mine depending on whether you’re using a database provider like Supabase or Neon, or are running vanilla locally and in production like me. Learning how to define a schema is easy via the tutorials. The migration workflow will be largely the same, which we’ll cover.

In your Drizzle install, you’ll be asked to configure two files which can both be used to launch a Drizzle instance.

drizzle.config.ts is used to instruct drizzle-kit (the CLI) on your database configuration. Whenever you run a drizzle-kit migrate command, this config is used. Create it:

// `./drizzle.config.ts`:

import { defineConfig } from "drizzle-kit"
import { getDbUrl } from "./src/lib/utilities/constants"

// This Drizzle config is used for the drizzle-kit CLI tool, with which which we can generate migrations and migrate. In production, we're migrating with a custom migration script using Drizzle ORM, which is defined in our `db-server.ts` file in the app.
export default defineConfig({
	schema: "./src/lib/database/schema.ts",
	out: "./.drizzle/migrations",
	dialect: "postgresql",
	dbCredentials: {
		url: getDbUrl() as string,
		ssl: process.env.NODE_ENV === "production" ? "require" : undefined
	},
	strict: true
})

Note that I’ve set SSL to require in production, which is the only setting I’ve found to work with a Railway postgres database. This is because it self-signs SSL certificates.

I created a helper function for getting the right database URL.

// `src/lib/utilities/constants.ts`:

export function getDbUrl() {
	const dbUrl =
		process.env.NODE_ENV === "production"
			? process.env.DATABASE_URL
			: process.env.DEV_DATABASE_URL

	if (dbUrl) {
		return dbUrl
	}
	if (!dbUrl) {
		throw new Error("No database url found")
	}
}

You may notice we’re grabbing DATABASE_URL from our .env file, but that we haven’t defined it earlier. This is because Railway can inject this variable in runtime. You must reference the the DATABASE_URL variable of your postgres service in your main application, as detailed here.

Importantly, the postgres service on Railway exposes both a DATABASE_URL and DATABASE_PUBLIC_URL which you can reference in your main app. The DATABASE_URL uses Railway’s private networking feature, which allows the database to connect to other services hosted on Railway on a private network. Traffic on this private network does not incur any egress (data transfer) costs. And it’s more secure since you aren’t establishing a public connection. When possible, opt to use this private networking. You cannot connect to your production database locally using the private networking URL however, and must use the public URL for that.

Here’s how to do this:

Then there’s db-server.ts which configures the drizzle-orm package. You can name this file whatever, but I like having a file from which I can import my database that’s exclusively for server-side interactions, and optionally a different one for client-side interactions. Create it:

// `src/lib/database/db-server.ts`:

import { drizzle } from "drizzle-orm/postgres-js"
import postgres from "postgres"
import { getDbUrl } from "$utils/constants"
import * as schema from "$lib/database/schema"

const queryClient = postgres(getDbUrl() as string, {
	max: 20,
	ssl: process.env.NODE_ENV === "production" ? "require" : undefined
})
export const db = drizzle(queryClient, { schema })

Note the maximum amount of concurrent connections and the same SSL parameters as we’ve used in ./drizzle.config.ts before.

Migrations

Here are my migration scripts. Let’s walk through them:

# package.json:

	"scripts": {
		"db:studio": "drizzle-kit studio",
		"db:generate": "drizzle-kit generate",
		"db:migrate": "drizzle-kit migrate --config drizzle.config.ts",
		"db:migrateprod": "NODE_ENV=production vite-node .drizzle/migrate.ts",
		"start": "pnpm db:migrateprod && node build"
	},

For all migrations, first run pnpm db:generate. This compares the schema to the database to determine if there are any changes and stores these in ./.drizzle/migrations which we defined in drizzle.config.ts.

Local migrations

Run pnpm db:migrate which uses drizzle-kit to run a migration with the config in drizzle.config.ts. This should work perfectly using your DEV_DATABASE_URL variable in .env.

Production migrations

In production, we want a workflow where upon every new deployment of your app to Railway, the local database migrations are also applied to your production database. Additionally, it must be possible to update the remote production database whilst developing locally.

We previously configured the DATABASE_URL variable in Railway to reference the private networking URL of the database service. Whilst Railway’s CLI has a railway run command that allows you to run an app/service using the production environment’s variables, this won’t work for your database using the private networking URL since you’d be attempting to access it locally.

Even when using the public networking database URL, I couldn’t get drizzle-kit migrate to work on the production database. Instead, what I resolved to doing is to create a migration script using drizzle-orm and ensuring this is run whenever my app builds. The same script can also be run independently. Note that I’m not using the getDbUrl() function I had used earlier since I want to run this script prior to building the app via the Dockerfile. Having no imports on this file means I don’t need to copy these files into my Docker image to run the app.

// .drizzle/migrate.ts:
import { drizzle } from 'drizzle-orm/postgres-js'
import { migrate } from 'drizzle-orm/postgres-js/migrator'
import postgres from 'postgres'

async function runMigration() {
	console.log('Migration started ⌛')

	// Not using the getDbUrl helper function because we aren't copying that into our runtime app prior to deployment in our Dockerfile. We'll live with the code duplication.
	const dbUrl = (
		process.env.NODE_ENV === 'production'
			? process.env.DATABASE_URL
			: process.env.DEV_DATABASE_URL
	) as string

	if (!dbUrl) throw new Error('No database url found')

	const client = postgres(dbUrl, {
		max: 1,
		// SSL must be `require`. `true` or `verify-full` do not work since Railway uses self-signed certificates.
		ssl: process.env.NODE_ENV === 'production' ? 'require' : undefined
	})

	const db = drizzle(client)
	try {
		await migrate(db, { migrationsFolder: './.drizzle/migrations' })
		console.log('Migration completed ✅')
	} catch (error) {
		console.error('Migration failed 🚨:', error)
	} finally {
		await client.end()
	}
}

runMigration().catch((error) => console.error('Error in migration process 🚨:', error))

Deploying to Railway

Create a Dockerfile

I’m deploying my app using a Dockerfile that uses a multi-staged build and caching to speed up consecutive builds. If you want to use caching like I have, replace <service-id> with the service id of your Railway service.

# `Dockerfile`

FROM node:20-alpine AS base

#
# INSTALL STAGE
#
FROM base AS prod-deps

# Access PNPM with Corepack
RUN corepack enable
# Install apk and curl
RUN apk update && apk add curl bash
WORKDIR /app
COPY package.json pnpm-lock.yaml ./
# Fetch deps with caching
RUN --mount=type=cache,id=s/<service-id>-/root/.local/share/pnpm/store,target=/root/.local/share/pnpm/store \
    pnpm fetch --frozen-lockfile
# Install prod deps with caching
RUN --mount=type=cache,id=s/<service-id>-/root/.local/share/pnpm/store,target=/root/.local/share/pnpm/store \
    pnpm install --frozen-lockfile --prod

#
# BUILD STAGE
# 
FROM base AS build

RUN corepack enable
RUN apk update && apk add curl bash
WORKDIR /app
COPY package.json pnpm-lock.yaml ./
# Fetch deps with caching
RUN --mount=type=cache,id=s/<service-id>-/root/.local/share/pnpm/store,target=/root/.local/share/pnpm/store \
    pnpm fetch --frozen-lockfile
# Install all deps with caching
RUN --mount=type=cache,id=s/<service-id>-/root/.local/share/pnpm/store,target=/root/.local/share/pnpm/store \
    pnpm install --frozen-lockfile
COPY . .
# Set Node options for increased memory
ENV NODE_OPTIONS="--max-old-space-size=4096"
# Build the application with caching
RUN --mount=type=cache,id=s/<service-id>-/root/.cache/pnpm,target=/root/.cache/pnpm \
    NODE_ENV=production pnpm run build

#
# PRODUCTION STAGE
#
FROM base

RUN corepack enable
WORKDIR /app
COPY --from=prod-deps /app/node_modules ./node_modules
COPY --from=build /app/build ./build
# Need package.json to specify PNPM version which Corepack installs
COPY --from=build /app/package.json ./package.json
# Copy Drizzle config so we can migrate prior to building
COPY --from=build /app/.drizzle ./.drizzle
COPY --from=build /app/drizzle.config.ts ./drizzle.config.ts
# Expose the port the app runs on
EXPOSE 3000
CMD ["pnpm", "start"]

Configure package.json scripts

The Dockerfile’s CMD instruction starts the application. This refers to the start script in package.json, which runs the start script. This runs the migration prior to building the application. Note that I have one migration script for local use, and another for production use. In Railway’s builds you must force the NODE_ENV to production. Additionally I’m using vite-node to run the Typescript file, which you’ll have installed already if you’re using Vitest. Alternatively you can use tsx.

# package.json:

	"scripts": {
		"db:studio": "drizzle-kit studio",
		"db:generate": "drizzle-kit generate",
		"db:migrate": "drizzle-kit migrate --config drizzle.config.ts",
		"db:migrateprod": "NODE_ENV=production vite-node .drizzle/migrate.ts",
		"start": "pnpm db:migrateprod && node build"
	},

With this setup you should be up and running!

Finally, if you want to run a migration on the production database locally, you can either force a Railway redeploy with your latest branch’s code using the Railway CLI command railway up. Or you should hardcode the DATABASE_PUBLIC_URL from your prod database in your .env and integrate it in the migration script.

Hope this was useful 👊🏼.

Curious about deploying with Railway? It’s a breeze. Sign-up via this referral link to support my work.

Read more

← Back to blog