sailfork / t3code-github / .repos/ alchemy-effect/ packages/ integration-tests/ test/ GCP/ SQL/ Database.test.ts

import * as GCP from "@alchemy.run/gcp";
import { GcpEnvironment } from "@alchemy.run/gcp/Environment";
import { describe, expect } from "@alchemy.run/test";
import * as Test from "@alchemy.run/test/Alchemy";
import * as Core from "@alchemy.run/test/Core";
import * as secretmanager from "@distilled.cloud/gcp/secretmanager_v1";
import * as sqladmin from "@distilled.cloud/gcp/sqladmin_v1";
import type { StackServices } from "alchemy/Stack";
import * as Effect from "effect/Effect";
import * as FileSystem from "effect/FileSystem";
import * as Layer from "effect/Layer";
import * as Path from "effect/Path";
import { MinimumLogLevel } from "effect/References";
import * as Schedule from "effect/Schedule";

const testOptions = {
  providers: GCP.providers() as Layer.Layer<GCP.ProviderRequirements, never, StackServices>,
};
const { test, beforeAll, afterAll } = Test.make(testOptions);

const logLevel = Effect.provideService(MinimumLogLevel, process.env.DEBUG ? "Debug" : "Info");

// Runs against an existing Cloud SQL instance (creating one takes well over
// 5 minutes); set GCP_SQL_INSTANCE to its name.
const sqlInstance = process.env.GCP_SQL_INSTANCE || process.env.GCP_TEST_SQL_INSTANCE;
const runLifecycle = !!sqlInstance && !process.env.FAST;

const waitUntilGone = (instance: string, databaseName: string) =>
  GcpEnvironment.current.pipe(
    Effect.flatMap(({ project }) =>
      sqladmin
        .getDatabases({
          project,
          instance,
          database: databaseName,
        })
        .pipe(
          Effect.as("found" as const),
          Effect.catchTag("NotFound", () => Effect.succeed("gone" as const)),
          Effect.repeat({
            schedule: Schedule.spaced("1 second"),
            until: (status) => status === "gone",
            times: 10,
          }),
        ),
    ),
  );

test.provider(
  "getDatabases on a missing instance fails with SqlInstanceNotAuthorized",
  (stack) =>
    Effect.gen(function* () {
      const { project } = yield* GcpEnvironment.current;
      yield* stack.destroy();

      const error = yield* Effect.flip(
        sqladmin.getDatabases({
          project,
          instance: "alchemy-sql-instance-does-not-exist",
          database: "alchemy_db_does_not_exist",
        }),
      );
      // Cloud SQL hides unknown instances behind 403 rather than 404.
      expect(error._tag).toBe("SqlInstanceNotAuthorized");

      yield* stack.destroy();
    }).pipe(logLevel),
  { tags: ["provider:gcp", "provider:gcp:sql", "live"], timeout: 90_000 },
);

test.provider(
  "listDatabases on a missing instance fails with SqlInstanceNotAuthorized",
  (stack) =>
    Effect.gen(function* () {
      const { project } = yield* GcpEnvironment.current;
      yield* stack.destroy();

      const error = yield* Effect.flip(
        sqladmin.listDatabases({
          project,
          instance: "alchemy-sql-instance-does-not-exist",
        }),
      );
      expect(error._tag).toBe("SqlInstanceNotAuthorized");

      yield* stack.destroy();
    }).pipe(logLevel),
  { tags: ["provider:gcp", "provider:gcp:sql", "live"], timeout: 90_000 },
);

test.provider.skipIf(!runLifecycle)(
  "create, update, and delete a sql database",
  (stack) =>
    Effect.gen(function* () {
      const { project } = yield* GcpEnvironment.current;
      yield* stack.destroy();

      const instance = sqlInstance!;

      const created = yield* stack.deploy(
        Effect.gen(function* () {
          return yield* GCP.SQL.Database("App", {
            instance,
          });
        }),
      );

      expect(created.databaseName).toEqual(expect.any(String));
      expect(created.databaseName).toMatch(/^[a-z][a-z0-9_]{0,62}$/);
      expect(created.instance).toEqual(instance);
      expect(created.project).toEqual(project);

      const fetched = yield* sqladmin.getDatabases({
        project: created.project,
        instance: created.instance,
        database: created.databaseName,
      });
      expect(fetched.name).toEqual(created.databaseName);
      expect(fetched.instance).toEqual(created.instance);

      const live = yield* sqladmin.getInstances({
        project: created.project,
        instance: created.instance,
      });
      const isMysql = (live.databaseVersion ?? "").toUpperCase().startsWith("MYSQL");

      const updated = yield* stack.deploy(
        Effect.gen(function* () {
          return yield* GCP.SQL.Database("App", {
            instance,
            databaseName: created.databaseName,
            charset: isMysql ? "utf8mb4" : created.charset,
            collation: isMysql ? "utf8mb4_unicode_ci" : created.collation,
          });
        }),
      );

      expect(updated.databaseName).toEqual(created.databaseName);
      expect(updated.instance).toEqual(created.instance);
      if (isMysql) {
        expect(updated.charset?.toLowerCase()).toEqual("utf8mb4");
      }

      const refetched = yield* sqladmin.getDatabases({
        project: updated.project,
        instance: updated.instance,
        database: updated.databaseName,
      });
      expect(refetched.name).toEqual(updated.databaseName);
      if (isMysql) {
        expect(refetched.charset?.toLowerCase()).toEqual("utf8mb4");
      }

      yield* stack.destroy();

      const gone = yield* waitUntilGone(created.instance, created.databaseName);
      expect(gone).toEqual("gone");
    }).pipe(logLevel),
  { tags: ["provider:gcp", "provider:gcp:sql", "live"], timeout: 120_000 },
);

// Shared-core, zonal, no backups: the cheapest Cloud SQL there is. Both
// allow the Data API so the tests can verify through it.
const MIGRATIONS_PASSWORD = "Alchemy-migrations-test-1";
const migrationsOptions = { providers: GCP.providers() };
const migrationsStack = Core.scratchStack(migrationsOptions, "SqlMigrations", import.meta.filename);

const MigrationsInstances = Effect.gen(function* () {
  const instance = (id: string, databaseVersion: string) =>
    GCP.SQL.Instance(id, {
      databaseVersion,
      edition: "ENTERPRISE",
      tier: "db-f1-micro",
      availabilityType: "ZONAL",
      backup: { enabled: false },
      deletionProtectionEnabled: false,
      dataApiAccess: true,
    });
  const postgres = yield* instance("MigrationsPostgres", "POSTGRES_17");
  const mysql = yield* instance("MigrationsMySql", "MYSQL_8_0");
  const user = (id: string, of: typeof postgres) =>
    GCP.SQL.User(id, {
      instance: of.instanceName,
      password: MIGRATIONS_PASSWORD,
      // Deleting the instance removes it.
      deletionPolicy: "ABANDON",
    });
  const postgresUser = yield* user("MigrationsPostgresUser", postgres);
  const mysqlUser = yield* user("MigrationsMySqlUser", mysql);
  // Where the test's own Data API queries read the password.
  const secret = yield* GCP.SecretManager.LocationsSecret("MigrationsVerifyPassword", {
    location: postgres.region,
  });
  return {
    postgres: postgres.instanceName,
    postgresUser: postgresUser.userName,
    mysql: mysql.instanceName,
    mysqlUser: mysqlUser.userName,
    region: postgres.region,
    secret: secret.name,
  };
});

// Writes `0001_users.sql` into a fresh temp directory; `add` writes later
// migrations into the same directory between deploys.
const migrationsFixture = Effect.gen(function* () {
  const fs = yield* FileSystem.FileSystem;
  const path = yield* Path.Path;
  const dir = yield* fs.makeTempDirectoryScoped({ prefix: "alchemy-cloudsql-migrations-" });
  const add = (name: string, sql: string) => fs.writeFileString(path.join(dir, name), sql);
  yield* add("0001_users.sql", "CREATE TABLE users (id INT PRIMARY KEY, name TEXT NOT NULL);");
  return { dir, add };
});

describe.skipIf(!!process.env.FAST)(
  "Database migrations",
  { tags: ["provider:gcp", "provider:gcp:sql", "live"] },
  () => {
    let shared: {
      postgres: string;
      postgresUser: string;
      mysql: string;
      mysqlUser: string;
      region: string;
      secret: string;
    };

    beforeAll(
      Effect.gen(function* () {
        shared = yield* migrationsStack.deploy(MigrationsInstances);
        const data = yield* Effect.sync(() =>
          Buffer.from(MIGRATIONS_PASSWORD, "utf8").toString("base64"),
        );
        yield* Core.withProviders(
          secretmanager.addVersionProjectsLocationsSecrets({
            parent: shared.secret,
            body: { payload: { data } },
          }),
          migrationsOptions,
          "SqlMigrations",
        );
      }),
      { timeout: 1_800_000 },
    );

    afterAll.skipIf(!!process.env.NO_DESTROY)(migrationsStack.destroy(), { timeout: 1_200_000 });

    /** Applied migration names, read out-of-band through the Data API. */
    const appliedMigrations = (instance: string, database: string, user: string) =>
      Effect.gen(function* () {
        const { project } = yield* GcpEnvironment.current;
        const response = yield* sqladmin.executeSqlInstances({
          project,
          instance,
          body: {
            database,
            user,
            passwordSecretVersion: `${shared.secret}/versions/latest`,
            sqlStatement: "SELECT name FROM __alchemy_migrations ORDER BY id",
          },
        });
        expect(response.status?.message).toBeUndefined();
        return (response.results?.[0]?.rows ?? []).map((row) => row.values?.[0]?.value);
      });

    const stagedSecretGone = (instance: string, database: string) =>
      Effect.gen(function* () {
        const { project } = yield* GcpEnvironment.current;
        const error = yield* Effect.flip(
          secretmanager.getProjectsLocationsSecrets({
            name: `projects/${project}/locations/${shared.region}/secrets/alchemy-migrations-${instance}-${database}`,
          }),
        );
        expect(error._tag).toEqual("NotFound");
      });

    const migrationsLifecycle = (options: {
      engine: "postgres" | "mysql";
      transport: GCP.SQL.MigrationsTransport | undefined;
    }) =>
      Effect.fn(function* (stack: Core.ScratchStack) {
        const instance = options.engine === "postgres" ? shared.postgres : shared.mysql;
        const user = options.engine === "postgres" ? shared.postgresUser : shared.mysqlUser;
        const migrations = yield* migrationsFixture;
        const database = GCP.SQL.Database("App", {
          instance,
          migrations: migrations.dir,
          migrationsTransport: options.transport,
          migrationsUser: user,
          migrationsPassword: MIGRATIONS_PASSWORD,
        });

        yield* stack.destroy();

        const created = yield* stack.deploy(database);
        expect(created.migrationsTable).toEqual("__alchemy_migrations");
        expect(Object.keys(created.migrationsHashes)).toEqual(["0001_users.sql"]);
        expect(yield* appliedMigrations(instance, created.databaseName, user)).toEqual([
          "0001_users.sql",
        ]);
        yield* stagedSecretGone(instance, created.databaseName);

        // Nothing changed: the next deploy has nothing to apply.
        const unchanged = yield* stack.plan(database);
        expect(unchanged.resources.App?.action).toEqual("noop");

        yield* migrations.add(
          "0002_posts.sql",
          "CREATE TABLE posts (id INT PRIMARY KEY, title TEXT NOT NULL);",
        );
        const pending = yield* stack.plan(database);
        expect(pending.resources.App?.action).toEqual("update");

        const updated = yield* stack.deploy(database);
        expect(updated.databaseName).toEqual(created.databaseName);
        expect(Object.keys(updated.migrationsHashes).sort()).toEqual([
          "0001_users.sql",
          "0002_posts.sql",
        ]);
        expect(yield* appliedMigrations(instance, updated.databaseName, user)).toEqual([
          "0001_users.sql",
          "0002_posts.sql",
        ]);

        yield* stack.destroy();
        expect(yield* waitUntilGone(instance, updated.databaseName)).toEqual("gone");
      });

    for (const engine of ["postgres", "mysql"] as const) {
      const label = engine === "postgres" ? "PostgreSQL" : "MySQL";
      test.provider(
        `${label} applies migrations over the Data API, then only pending ones`,
        (stack) => migrationsLifecycle({ engine, transport: undefined })(stack),
        { tags: ["provider:gcp", "provider:gcp:sql", "live"], timeout: 600_000 },
      );

      test.provider(
        `${label} applies migrations through the Cloud SQL connector, then only pending ones`,
        (stack) => migrationsLifecycle({ engine, transport: "connector" })(stack),
        { tags: ["provider:gcp", "provider:gcp:sql", "live"], timeout: 600_000 },
      );
    }
  },
);