Drizzle Kit squash migrations

[AlexBlokh]

the problem:

• there are projects where developers have 100+ migrations in Drizzle migration folder and they want to squash tail(old) migrations into one, that leads to a simpler migrations folder and less disk space usage
• there are projects where developers want to squash migrations from the head. They do generate migrations while developing locally and then wanna squash them before release

for the second one it does seem drizzle-kit push should be preferred solution, yet we could still explore

tail squash:
drizzle-kit squash --tail will let you squash old migrations, you should be able to select range.
There are multiple ways we can do it and it does seem there's no silver bullet here

first approach would be to combine all sql files into one, while preserving metadata:

-- SQUASHED

-- delimiter drizzle/0000_magenta_kronos.sql // skipped
CREATE TABLE "users" (
	"id" serial NOT NULL,
	"name" text
);

-- delimiter drizzle/0001_overjoyed_azazel.sql
CREATE INDEX "nameIdx" ON "users" USING btree ("name");

-- delimiter drizzle/0003_overjoyed_azazel.sql
DELETE INDEX "nameIdx";

this way we will eliminate the problem of partially applied squashed migrations in the database(different stages), yet we will have to have complete SQL history, which will make this file bloated

We can provide a flag for developer to squash migrations into compact SQL file, in the example above we can eliminate index creation at all, which will result in a init sql migration file:

CREATE TABLE "users" (
	"id" serial NOT NULL,
	"name" text
);

and the last DX piece would be to let developer provide us database connection and let drizzle kit decide which migrations to squash in a compact way based on the list of applied database migrations automatically

[vladinator1000]

If anyone is looking for a workaround, I use this script to squash migrations for local development, I'll leave it up to you to figure out the details

import { glob } from "node:fs/promises"
import { mkdir, rm, writeFile } from "node:fs/promises"
import { parseArgs } from "node:util"
import { $ } from "zx"
import drizzleJournal from "../../migrations/meta/_journal.json" with {
  type: "json",
}
import { getMigrationsToSquash } from "./migration-utils"

/**
 * Deletes the local database and all migration files,
 * regenerates a single migration file.
 */

let { values } = parseArgs({
  args: process.argv,
  options: {
    after: {
      type: "string",
    },
    all: {
      type: "boolean",
    },
  },
  strict: true,
  allowPositionals: true,
})

if (values.after) {
  let filePaths = []
  // Delete migration files after the one supplied
  for await (const filePath of glob("./migrations/*.sql")) {
    filePaths.push(filePath)
  }

  let toSquash = getMigrationsToSquash(filePaths, values.after)

  let newEntries = drizzleJournal.entries.filter(
    (entry) => !toSquash.names.includes(entry.tag),
  )

  let newJournal = JSON.stringify(
    { ...drizzleJournal, entries: newEntries },
    null,
    2,
  )

  await writeFile("./migrations/meta/_journal.json", newJournal)

  for (const filePath of toSquash.migrationPaths) {
    await rm(filePath)
  }

  for (const filePath of toSquash.snapshotPaths) {
    await rm(filePath)
  }
} else if (values.all) {
  // Delete all migration files
  await rm("migrations", { recursive: true, force: true })
  await mkdir("migrations")
}

// Delete database
await rm(".wrangler", { recursive: true, force: true })

// Create new database and apply all migrations
await $`pnpm wrangler d1 execute gyarns-preview --local --env preview --command "pragma foreign_keys = ON;"`
await $`pnpm db-migrate-gen`
await $`pnpm db-migrate-apply`

// migration-utils.ts
import { basename, dirname, join } from "node:path"

export function parseMigrationNumber(name: string): number {
  let migrationNumberRaw = name.split("_")[0]

  let result = Number.parseInt(migrationNumberRaw ?? "")

  if (Number.isNaN(result)) {
    throw new Error(
      "Please provide a migration name in this shape: 0001_my_migration",
    )
  }

  return result
}

export function getMigrationsToSquash(
  filePaths: string[],
  afterMigrationName?: string,
) {
  let afterNumber = afterMigrationName
    ? parseMigrationNumber(afterMigrationName)
    : -1

  let names = []
  let migrationPaths = []
  let snapshotPaths = []

  for (const filePath of filePaths) {
    let name = basename(filePath, ".sql")
    let migrationNumber = parseMigrationNumber(name)

    if (migrationNumber <= afterNumber) {
      continue
    }

    names.push(name)
    migrationPaths.push(filePath)

    let migrationNumberRaw = name.split("_")[0]
    let migrationsDirectory = dirname(filePath)
    let snapshotName = `${migrationNumberRaw}_snapshot.json`
    let snapshotPath = join(migrationsDirectory, "meta", snapshotName)
    snapshotPaths.push(snapshotPath)
  }

  return { names, migrationPaths, snapshotPaths }
}