Running Migrations in Drizzle for PostgreSQL

Yesterday I wrote a guide on getting started with Drizzle.

If you haven't set up your Drizzle project, you can run through that guide here.

I'll assume you have your schema defined and run from there:

Confirm Your Configuration

Make sure you have your drizzle.config.ts configured and have the path to your schema and where you would like your migrations outputted. It should look something like this:

// drizzle.config.ts
import type { Config } from "drizzle-kit";

export default {
  schema: './src/schema.ts',
  out: './drizzle',
  // Include database credentials or other configurations...
} satisfies Config;

Generate Migrations

With your schema and configuration in place, you can generate migration files by running drizzle-kit generate:pg command:

npx drizzle-kit generate:pg

This command creates SQL migration files based on your schema changes. These files are stored in the directory specified in your drizzle.config.ts configuration.

Apply Migrations

Finally, to apply your migrations to the database, you'll need a script that calls Drizzle's migrate() function, specifying the migrations folder. Here's an example of what that script might look like:

// migrate.ts
import "dotenv/config";
import { migrate } from "drizzle-orm/postgres-js/migrator";
import { drizzle, type PostgresJsDatabase } from "drizzle-orm/postgres-js";
import postgres from "postgres";

const DATABASE_URL = process.env.DATABASE_URL;

if (!DATABASE_URL) {
  throw new Error("DATABASE_URL is not set");
}

const migrationClient = postgres(DATABASE_URL, { max: 1 });
const db: PostgresJsDatabase = drizzle(migrationClient);

const main = async () => {
  console.log("Migrating database...");
  await migrate(db, { migrationsFolder: "./drizzle" });
  await migrationClient.end();
  console.log("Database migrated successfully!");
};

main();

Running this script applies all pending migrations to your database.

You can run this with something like tsx to make your life easy:

npx tsx -r dotenv/config ./migrate.ts

And that's it; now you have to follow the two steps to create and run migrations:

1. Create a migration with npx drizzle-kit generate:pg.
2. Run the migrations with npx tsx -r dotenv/config ./migrate.ts.