sailfork / t3code-github / .repos/ alchemy-effect/ packages/ sql/ src/ Migrations/ AlchemyFormat.ts

import * as Effect from "effect/Effect";
import {
  convertedRowInsertSql,
  findForeignHistory,
  matchForeignRows,
  toTimestampString,
} from "./Convert.ts";
import {
  describeRewrittenHistory,
  MigrationError,
  RewrittenMigrationHistoryError,
  type MigrationApplyError,
  type MigrationDialect,
  type MigrationHistoryConflictError,
  type MigrationRecord,
  type SqlExecutor,
} from "./Format.ts";
import { classifyTable, tableColumns } from "./Introspect.ts";
import { bookkeepingColumns, quoteIdentifier, scalarText, sqlLiteral } from "./Utils.ts";

export const ALCHEMY_DEFAULT_TABLE = "__alchemy_migrations";

/**
 * THE Alchemy applied-migrations table: `id, hash, created_at, name,
 * applied_at`, name-keyed detection. This is deliberately drizzle's column
 * shape — a database whose history lives in `__drizzle_migrations` adopts
 * with a verbatim row copy — but Alchemy owns the table and it is the only
 * format Alchemy ever writes. Migrating from drizzle/prisma/wrangler
 * bookkeeping is a one-way conversion performed once (see `Convert.ts`).
 */
const createTableSql = (
  table: string,
  dialect: MigrationDialect,
  id?: SqlExecutor["migrationTableId"],
): string => {
  const quoted = quoteIdentifier(table, dialect);
  switch (dialect) {
    case "sqlite":
      return `CREATE TABLE IF NOT EXISTS ${quoted} (
  id INTEGER PRIMARY KEY,
  hash text NOT NULL,
  created_at numeric,
  name text,
  applied_at TEXT
);`;
    case "postgres":
      if (id === "spanner") {
        return `CREATE TABLE IF NOT EXISTS ${quoted} (
  id varchar(36) NOT NULL DEFAULT spanner.generate_uuid(),
  hash text NOT NULL,
  created_at bigint,
  name text,
  applied_at timestamptz DEFAULT now(),
  PRIMARY KEY (id)
)`;
      }
      return `CREATE TABLE IF NOT EXISTS ${quoted} (
  id ${id === "uuid" ? "uuid DEFAULT gen_random_uuid()" : "SERIAL"} PRIMARY KEY,
  hash text NOT NULL,
  created_at bigint,
  name text,
  applied_at timestamp with time zone DEFAULT now()
);`;
    case "googlesql":
      return `CREATE TABLE IF NOT EXISTS ${quoted} (
  \`id\` STRING(36) NOT NULL DEFAULT (GENERATE_UUID()),
  \`hash\` STRING(MAX) NOT NULL,
  \`created_at\` INT64,
  \`name\` STRING(MAX),
  \`applied_at\` TIMESTAMP DEFAULT (CURRENT_TIMESTAMP())
) PRIMARY KEY (\`id\`)`;
    case "mysql":
      return `CREATE TABLE IF NOT EXISTS ${quoted} (
  id SERIAL PRIMARY KEY,
  hash TEXT NOT NULL,
  created_at BIGINT,
  name TEXT,
  applied_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);`;
  }
};

const insertSql = (
  table: string,
  dialect: MigrationDialect,
  record: Pick<MigrationRecord, "name" | "hash" | "createdAtMillis">,
): string => {
  const quoted = quoteIdentifier(table, dialect);
  const applied = dialect === "sqlite" ? ", datetime('now')" : "";
  const columns =
    dialect === "sqlite"
      ? bookkeepingColumns(dialect, "hash", "created_at", "name", "applied_at")
      : bookkeepingColumns(dialect, "hash", "created_at", "name");
  return `INSERT INTO ${quoted} (${columns}) VALUES (${sqlLiteral(record.hash, dialect)}, ${sqlLiteral(record.createdAtMillis ?? null, dialect)}, ${sqlLiteral(record.name, dialect)}${applied});`;
};

const renameSql = (from: string, to: string, dialect: MigrationDialect): string =>
  dialect === "mysql"
    ? `RENAME TABLE ${quoteIdentifier(from, dialect)} TO ${quoteIdentifier(to, dialect)};`
    : `ALTER TABLE ${quoteIdentifier(from, dialect)} RENAME TO ${quoteIdentifier(to, dialect)};`;

/**
 * Rebuild an in-place table (legacy Alchemy 3-column / oldest 2-column /
 * wrangler-shaped) into the Alchemy shape, backfilling `hash` and
 * `created_at` from local records matched by name. A recorded row with no
 * matching local file is a hard error — mirrors the same rule conversion
 * applies (see `matchForeignRows`).
 */
const rebuildInPlace = (options: {
  executor: SqlExecutor;
  table: string;
  records: ReadonlyArray<MigrationRecord>;
  /** SQL expression yielding the migration name from the old table. */
  nameExpr: string;
  tool: "legacy-alchemy" | "wrangler";
}) =>
  Effect.gen(function* () {
    const { executor, table, records, nameExpr } = options;
    if (executor.transactionalDdl === false) {
      return yield* new MigrationError({
        message: `Cannot automatically rebuild migration history in "${table}" on a target without transactional DDL. Convert the history explicitly before deploying.`,
      });
    }
    const dialect = executor.dialect;
    const quoted = quoteIdentifier(table, dialect);
    const rows = yield* executor.query(
      `SELECT ${nameExpr} AS name, applied_at FROM ${quoted} ORDER BY id;`,
    );
    const matched = yield* matchForeignRows({
      history: {
        tool: options.tool,
        source: table,
        rows: rows
          .filter((row) => row.name !== null && row.name !== undefined)
          .map((row) => ({
            name: String(row.name),
            hash: undefined,
            createdAtMillis: undefined,
            appliedAt: toTimestampString(row.applied_at),
          })),
      },
      records,
    });

    const temp = `${table}_alchemy_upgrade`;
    yield* executor.batch([
      `DROP TABLE IF EXISTS ${quoteIdentifier(temp, dialect)};`,
      createTableSql(temp, dialect, executor.migrationTableId).replace(
        "CREATE TABLE IF NOT EXISTS",
        "CREATE TABLE",
      ),
      ...matched.map((row) => convertedRowInsertSql(temp, dialect, row)),
      `DROP TABLE ${quoted};`,
      renameSql(temp, table, dialect),
    ]);
  });

const ensureTable = (options: {
  executor: SqlExecutor;
  table: string;
  records: ReadonlyArray<MigrationRecord>;
}) =>
  Effect.gen(function* () {
    const { executor, table, records } = options;
    const shape = classifyTable(yield* tableColumns(executor, table));
    switch (shape) {
      case "absent": {
        // Greenfield for us — but possibly not for the database. Adopt any
        // history the previous tool (drizzle-kit / prisma / wrangler) left
        // behind: copy it into our table ONCE and freeze theirs. One-way.
        const history = yield* findForeignHistory({ executor, table });
        const converted = history ? yield* matchForeignRows({ history, records }) : [];
        if (converted.length > 0 && executor.transactionalDdl === false) {
          return yield* new MigrationError({
            message: `Cannot automatically copy foreign migration history into "${table}" without transactional DDL. Convert the history explicitly before deploying.`,
          });
        }
        yield* executor.batch([
          createTableSql(table, executor.dialect, executor.migrationTableId),
          ...converted.map((row) => convertedRowInsertSql(table, executor.dialect, row)),
        ]);
        return;
      }
      case "drizzle-shaped":
        // Already our column shape (shared with drizzle v1) — including
        // the case where the user pointed `table` straight at an existing
        // `__drizzle_migrations`. Adopt in place.
        return;
      case "legacy-alchemy":
        yield* rebuildInPlace({
          executor,
          table,
          records,
          nameExpr: "name",
          tool: "legacy-alchemy",
        });
        return;
      case "legacy-2col":
        yield* rebuildInPlace({
          executor,
          table,
          records,
          nameExpr: "id",
          tool: "legacy-alchemy",
        });
        return;
      case "wrangler":
        // A wrangler table at OUR resolved table name (e.g. legacy D1
        // state pinned to `d1_migrations`, or a wrangler user's table
        // adopted under an explicit `table:`): convert it in place.
        yield* rebuildInPlace({
          executor,
          table,
          records,
          nameExpr: "name",
          tool: "wrangler",
        });
        return;
      case "unknown":
        return yield* new MigrationError({
          message:
            `Migrations table "${table}" has an unrecognized column layout; ` +
            `refusing to write bookkeeping into it.`,
        });
    }
  });

const aliasesOf = (name: string): readonly string[] => [
  name,
  `${name}/migration.sql`,
  name.replace(/\/migration\.sql$/, ""),
];

const appliedHistory = (executor: SqlExecutor, table: string) =>
  executor
    .query(
      `SELECT ${bookkeepingColumns(executor.dialect, "name", "hash")} FROM ${quoteIdentifier(table, executor.dialect)};`,
    )
    .pipe(
      Effect.map((rows) => {
        const byName = new Map<string, string | undefined>();
        for (const row of rows) {
          if (row.name === null || row.name === undefined) continue;
          byName.set(
            scalarText(row.name),
            row.hash === null || row.hash === undefined ? undefined : scalarText(row.hash),
          );
        }
        return byName;
      }),
    );

const lookupApplied = (
  byName: Map<string, string | undefined>,
  name: string,
): { name: string; hash: string | undefined } | undefined => {
  for (const alias of aliasesOf(name)) {
    if (byName.has(alias)) {
      return { name: alias, hash: byName.get(alias) };
    }
  }
  return undefined;
};

const applyPending = (options: {
  executor: SqlExecutor;
  table: string;
  records: ReadonlyArray<MigrationRecord>;
  applied: Map<string, string | undefined>;
}) =>
  Effect.gen(function* () {
    const { executor, table, records, applied } = options;
    for (const record of records) {
      if (lookupApplied(applied, record.name)) continue;
      const bookkeeping = insertSql(table, executor.dialect, record);
      const send = executor.applyMigration
        ? executor.applyMigration(record, bookkeeping)
        : executor.batch([...record.statements, bookkeeping]);
      // A batch can commit and still lose its reply. Its bookkeeping row says
      // whether it landed, so a lost reply is not a failure and only a
      // transient failure that did not land is resent (#1889).
      yield* send.pipe(
        Effect.catchTag(
          "MigrationError",
          Effect.fn(function* (error) {
            const after = yield* appliedHistory(executor, table);
            if (lookupApplied(after, record.name)) return;
            if (!error.transient) return yield* error;
            yield* send;
          }),
        ),
      );
    }
  });

/**
 * Apply pending migrations with Alchemy's bookkeeping. Idempotent: each
 * migration's statements and its bookkeeping INSERT go through
 * `executor.batch` as one unit (a transaction on pg/mysql, one batched
 * query on D1, which has no transactions over HTTP). Nontransactional DDL
 * targets can supply `applyMigration` to checkpoint individual statements.
 *
 * Applied-detection is name-keyed with layout aliasing: pre-registry
 * Alchemy recorded drizzle-layout migrations under `<dir>/migration.sql`
 * while current records key them by `<dir>`, so both keys are honored.
 * An already-applied name whose local hash changed, or a recorded name
 * with no local file, is a hard {@link RewrittenMigrationHistoryError}
 * rather than a silent skip that would persist the new hashes.
 */
export const applyAlchemyFormat = (options: {
  executor: SqlExecutor;
  table: string;
  records: ReadonlyArray<MigrationRecord>;
}): Effect.Effect<void, MigrationApplyError> =>
  Effect.gen(function* () {
    const { executor, table, records } = options;
    yield* ensureTable({ executor, table, records });
    const applied = yield* appliedHistory(executor, table);
    const localNames = new Set(records.flatMap((record) => aliasesOf(record.name)));
    const changed: string[] = [];
    const removed: string[] = [];
    for (const name of applied.keys()) {
      if (!localNames.has(name)) removed.push(name);
    }
    for (const record of records) {
      const row = lookupApplied(applied, record.name);
      if (row?.hash && row.hash !== record.hash) {
        changed.push(record.name);
      }
    }
    if (changed.length > 0 || removed.length > 0) {
      changed.sort();
      removed.sort();
      return yield* new RewrittenMigrationHistoryError({
        changed,
        removed,
        message:
          `Applied migration history in "${table}" does not match the local files ` +
          `(${describeRewrittenHistory({ changed, removed })}). ` +
          "Add a new forward migration instead of editing or deleting already-applied files.",
      });
    }
    yield* applyPending({ executor, table, records, applied });
  });

/**
 * {@link applyAlchemyFormat} without the rewritten-history check: applied
 * names are skipped regardless of their hash. For runtime appliers (Durable
 * Object SQLite) where failing would take every instance down after a
 * deploy that already succeeded.
 */
export const applyPendingAlchemyFormat = (options: {
  executor: SqlExecutor;
  table: string;
  records: ReadonlyArray<MigrationRecord>;
}): Effect.Effect<void, MigrationError | MigrationHistoryConflictError> =>
  Effect.gen(function* () {
    const { executor, table, records } = options;
    yield* ensureTable({ executor, table, records });
    const applied = yield* appliedHistory(executor, table);
    yield* applyPending({ executor, table, records, applied });
  });