sailfork / t3code-github / .repos/ alchemy-effect/ packages/ gcp/ src/ Spanner/ Migrations.ts

import * as crypto from "node:crypto";
import {
  MigrationError,
  migrationError,
  runMigrations,
  splitSqlScript,
  type MigrationRecord,
  type NormalizedMigrationsInput,
  type SqlExecutor,
  type StampedMigrationsState,
} from "@alchemy.run/sql/Migrations";
import * as spanner from "@distilled.cloud/gcp/spanner_v1";
import * as Effect from "effect/Effect";
import * as Result from "effect/Result";
import { waitForOperation } from "../Operation.ts";

/**
 * Spanner names must start with a letter, so the bookkeeping table drops
 * the default `__alchemy_migrations` prefix.
 */
export const SPANNER_MIGRATIONS_TABLE = "alchemy_migrations";

type SpannerDialect = "googlesql" | "postgres";

const DDL = /^(?:CREATE|ALTER|DROP|RENAME|GRANT|REVOKE|ANALYZE)\b/i;

/** Consecutive statements of one kind: DDL goes to `updateDdl`, DML to a transaction. */
const groupStatements = (statements: ReadonlyArray<string>) => {
  const groups: Array<{ ddl: boolean; statements: string[] }> = [];
  for (const statement of statements) {
    const ddl = DDL.test(statement);
    const last = groups.at(-1);
    if (last && last.ddl === ddl) last.statements.push(statement);
    else groups.push({ ddl, statements: [statement] });
  }
  return groups;
};

/** Attempts per DDL statement whose earlier operations failed. */
const MAX_DDL_ATTEMPTS = 20;

/**
 * Spanner as a {@link SqlExecutor} over one session. Spanner cannot commit
 * DDL with DML, so DDL runs as `updateDdl` operations and each migration's
 * trailing DML commits in one transaction with its bookkeeping row.
 *
 * A migration's DDL runs one statement per operation, named after the
 * migration, the statement's position, and its text. A deploy after a
 * crash finds the operations it already started and waits for them
 * instead of rerunning their statements; after a failed statement is
 * fixed, the statements before it are found applied and only the fixed
 * one runs.
 */
const makeSpannerExecutor = Effect.fn(function* (options: {
  database: string;
  dialect: SpannerDialect;
  session: string;
}) {
  const { database, dialect, session } = options;
  const executeSql = yield* spanner.executeSqlProjectsInstancesDatabasesSessions;
  const executeBatchDml = yield* spanner.executeBatchDmlProjectsInstancesDatabasesSessions;
  const commit = yield* spanner.commitProjectsInstancesDatabasesSessions;
  const updateDdl = yield* spanner.updateDdlProjectsInstancesDatabases;
  const getOperation = yield* spanner.getProjectsInstancesDatabasesOperations;

  const awaitDdl = (operation: spanner.Operation) =>
    waitForOperation(operation, (name) => getOperation({ name }), {
      budget: "30 minutes",
      interval: "2 seconds",
    }).pipe(
      Effect.asVoid,
      Effect.mapError((cause) =>
        cause._tag === "GCP.OperationFailed"
          ? new MigrationError({
              message: `Spanner DDL on ${database} failed: ${cause.message}`,
              cause,
            })
          : migrationError(`Spanner DDL on ${database}`)(cause),
      ),
    );

  const runDdl = (statements: ReadonlyArray<string>) =>
    updateDdl({ database, body: { statements: [...statements] } }).pipe(
      Effect.mapError(migrationError(`Spanner DDL on ${database}`)),
      Effect.flatMap(awaitDdl),
    );

  /**
   * Run one DDL statement exactly once under deterministic operation ids
   * `{base}_0`, `{base}_1`, …: an operation a previous deploy started is
   * awaited, not resubmitted. A single statement applies atomically, so an
   * earlier attempt that failed changed nothing and the next attempt id
   * runs it again; a failure of this deploy's own attempt fails the deploy.
   */
  const runDdlOnce = (statement: string, base: string) =>
    Effect.gen(function* () {
      for (let attempt = 0; attempt < MAX_DDL_ATTEMPTS; attempt++) {
        const operationId = `${base}_${attempt}`;
        const submitted = yield* updateDdl({
          database,
          body: { statements: [statement], operationId },
        }).pipe(
          Effect.map((operation) => ({ operation, replayed: false })),
          Effect.catchTag("DdlOperationIdExists", () =>
            getOperation({ name: `${database}/operations/${operationId}` }).pipe(
              Effect.map((operation) => ({ operation, replayed: true })),
            ),
          ),
          Effect.mapError(migrationError(`Spanner DDL on ${database}`)),
        );
        const outcome = yield* Effect.result(awaitDdl(submitted.operation));
        if (Result.isSuccess(outcome)) return;
        if (!submitted.replayed) return yield* outcome.failure;
      }
      return yield* new MigrationError({
        message: `Spanner DDL on ${database} failed ${MAX_DDL_ATTEMPTS} times: ${statement}`,
      });
    });

  const runDml = (statements: ReadonlyArray<string>) =>
    Effect.gen(function* () {
      const result = yield* executeBatchDml({
        session,
        body: {
          transaction: { begin: { readWrite: {} } },
          seqno: "1",
          statements: statements.map((sql) => ({ sql })),
        },
      }).pipe(Effect.mapError(migrationError(`Spanner DML on ${database}`)));
      if (result.status?.code) {
        return yield* new MigrationError({
          message: `Spanner DML on ${database} failed: ${result.status.message ?? `code ${result.status.code}`}`,
        });
      }
      const transactionId = result.resultSets?.[0]?.metadata?.transaction?.id;
      if (!transactionId) {
        return yield* new MigrationError({
          message: `Spanner returned no transaction for DML on ${database}`,
        });
      }
      yield* commit({ session, body: { transactionId } }).pipe(
        Effect.mapError(migrationError(`Committing DML on ${database}`)),
      );
    });

  const splitAll = (statements: ReadonlyArray<string>) =>
    statements.flatMap((statement) => splitSqlScript(statement, dialect));

  const runGroups = (statements: ReadonlyArray<string>) =>
    Effect.forEach(
      groupStatements(splitAll(statements)),
      (group) => (group.ddl ? runDdl(group.statements) : runDml(group.statements)),
      { discard: true },
    );

  const operationBaseOf = (record: MigrationRecord, index: number, statement: string) =>
    Effect.sync(
      () =>
        `alchemy_${crypto.createHash("sha256").update(`${record.name}\n${index}\n${statement}`).digest("hex").slice(0, 32)}`,
    );

  return {
    dialect,
    transactionalDdl: false,
    ...(dialect === "postgres"
      ? { migrationTableId: "spanner" as const, defaultSchema: "public" }
      : {}),
    query: (sql) =>
      executeSql({ session, body: { sql: splitAll([sql])[0] ?? sql } }).pipe(
        Effect.mapError(migrationError(`Spanner query on ${database}`)),
        Effect.map((result) => {
          const fields = (result.metadata?.rowType?.fields ?? []).map((field) => field.name ?? "");
          return (result.rows ?? []).map((row) =>
            Object.fromEntries(
              fields.map((name, index) => [name, (row as ReadonlyArray<unknown>)[index] ?? null]),
            ),
          );
        }),
      ),
    batch: runGroups,
    applyMigration: (record, bookkeeping) =>
      Effect.gen(function* () {
        const groups = groupStatements(splitAll(record.statements));
        const dml = groups.findIndex((group) => !group.ddl);
        if (dml >= 0 && dml < groups.length - 1) {
          return yield* new MigrationError({
            message: `Spanner migration ${record.name} runs DML between DDL statements. Spanner can't commit DDL and DML together, so put the DML after the migration's DDL or in its own migration.`,
          });
        }
        let index = 0;
        for (const group of groups) {
          if (!group.ddl) continue;
          for (const statement of group.statements) {
            yield* runDdlOnce(statement, yield* operationBaseOf(record, index++, statement));
          }
        }
        // The trailing DML commits with the bookkeeping row: exactly once.
        const trailing = groups.at(-1);
        yield* runDml([...(trailing && !trailing.ddl ? trailing.statements : []), bookkeeping]);
      }),
  } satisfies SqlExecutor;
});

/**
 * Apply migrations to a Spanner database. Call once the database is ready.
 */
export const runDatabaseMigrations = Effect.fn(function* (options: {
  database: string;
  dialect: SpannerDialect;
  input: NormalizedMigrationsInput;
  stamped: StampedMigrationsState;
}) {
  const createSession = yield* spanner.createProjectsInstancesDatabasesSessions;
  const deleteSession = yield* spanner.deleteProjectsInstancesDatabasesSessions;
  return yield* runMigrations({
    input: { ...options.input, table: options.input.table ?? SPANNER_MIGRATIONS_TABLE },
    stamped: options.stamped,
    withExecutor: (apply) =>
      Effect.acquireUseRelease(
        createSession({ database: options.database, body: {} }).pipe(
          Effect.mapError(migrationError(`Opening a session on ${options.database}`)),
        ),
        (session) =>
          makeSpannerExecutor({
            database: options.database,
            dialect: options.dialect,
            session: session.name ?? "",
          }).pipe(Effect.flatMap(apply)),
        (session) => deleteSession({ name: session.name ?? "" }).pipe(Effect.ignore),
      ),
  });
});