Drizzle utility to list the status of Postgres migrations: Applied and Pending

ops.drizzle.pg.ts

#!/usr/bin/env bun
import { Command } from "commander";
import { sql } from "drizzle-orm";
import { readFileSync } from "fs";
import { join } from "path";

import dbFactory from "@src/db/factory";
import { Bindings } from "@src/domain/hono.types";

const program = new Command();

const env = process.env as unknown as Bindings;

// Helper function to get applied migrations from database
async function getAppliedMigrations() {
  const db = dbFactory.pgFromEnv(env);

  try {
    const migrations =
      await db.execute(sql`select id, created_at from drizzle.__drizzle_migrations order by created_at; 
   `);
    return migrations.map((m) => ({
      ...m,
      created_at: parseInt(m.created_at as string),
    }));
  } catch (error) {
    console.error("❌ Error fetching applied migrations:", error);
    throw error;
  }
}

// Helper function to get available migrations from filesystem
function getAvailableMigrations() {
  try {
    const journalPath = join(process.cwd(), "migrations/pg/meta/_journal.json");
    const journalData = JSON.parse(readFileSync(journalPath, "utf-8"));
    return journalData.entries;
  } catch (error) {
    console.error("❌ Error reading migration journal:", error);
    throw error;
  }
}

// Command to list pending migrations
program
  .command("list-pending-migrations")
  .description(
    "List migrations that exist in files but have not been applied to the database",
  )
  .action(async () => {
    try {
      console.log("🔍 Checking for pending migrations...\n");

      const appliedMigrations = await getAppliedMigrations();
      const availableMigrations = getAvailableMigrations();

      console.log(
        `📊 Found ${appliedMigrations.length} applied migrations and ${availableMigrations.length} available migrations\n`,
      );

      const appliedCreatedAts = appliedMigrations.map((m) => m.created_at);

      // Find pending migrations by comparing created_at
      const pendingMigrations = availableMigrations.filter(
        (migrationEntry: any) => {
          return !appliedCreatedAts.includes(migrationEntry.when);
        },
      );

      if (pendingMigrations.length === 0) {
        console.log(
          "✅ All migrations are up to date! No pending migrations found.",
        );
        process.exit(0);
      }

      console.log(
        `⏳ Found ${pendingMigrations.length} pending migration(s):\n`,
      );

      // Display pending migrations with details
      for (const migration of pendingMigrations) {
        console.log(`📄 ${migration.tag}`);
        console.log(`   Index: ${migration.idx}`);
        console.log(`   Created: ${new Date(migration.when).toISOString()}`);
      }

      process.exit(0);
    } catch (error) {
      console.error("❌ Error listing pending migrations:", error);
      process.exit(1);
    }
  });

program.parse(process.argv);