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

import { stripSslQueryParams } from "@alchemy.run/sql/PostgresDriver";
import {
  applySqlImports,
  splitSqliteStatements,
  splitSqlStatements,
} from "@alchemy.run/sql/SqlFile";
import { describe, expect, layer, test } from "@alchemy.run/test";
import * as NodeServices from "@effect/platform-node/NodeServices";
import * as Effect from "effect/Effect";
import * as FileSystem from "effect/FileSystem";
import * as Path from "effect/Path";

describe("SqlFile", { tags: ["unit", "local"] }, () => {
  describe("splitSqlStatements", () => {
    test("splits statements separated by breakpoints on their own line", () => {
      const sql = [
        "CREATE TABLE `users` (\n\t`id` int NOT NULL\n);",
        "--> statement-breakpoint",
        "CREATE TABLE `sessions` (\n\t`id` int NOT NULL\n);",
      ].join("\n");
      expect(splitSqlStatements(sql)).toEqual([
        "CREATE TABLE `users` (\n\t`id` int NOT NULL\n);",
        "CREATE TABLE `sessions` (\n\t`id` int NOT NULL\n);",
      ]);
    });

    // drizzle-kit puts the marker on the same line after single-line
    // statements (`...;--> statement-breakpoint`). A newline-anchored split
    // misses those, and the leftover `-->` makes Vitess bail with
    // "syntax error at position 2".
    test("splits breakpoints appended on the same line as the statement", () => {
      const sql = [
        "CREATE TABLE `users` (\n\t`id` int NOT NULL\n);",
        "--> statement-breakpoint",
        "CREATE INDEX `users_email` ON `users` (`email`);--> statement-breakpoint",
        "CREATE INDEX `users_name` ON `users` (`name`);",
      ].join("\n");
      expect(splitSqlStatements(sql)).toEqual([
        "CREATE TABLE `users` (\n\t`id` int NOT NULL\n);",
        "CREATE INDEX `users_email` ON `users` (`email`);",
        "CREATE INDEX `users_name` ON `users` (`name`);",
      ]);
    });

    test("splits CRLF-separated breakpoints", () => {
      const sql =
        "CREATE TABLE `users` (`id` int);\r\n--> statement-breakpoint\r\nCREATE TABLE `sessions` (`id` int);";
      expect(splitSqlStatements(sql)).toEqual([
        "CREATE TABLE `users` (`id` int);",
        "CREATE TABLE `sessions` (`id` int);",
      ]);
    });

    test("drops empty segments and keeps sql without markers intact", () => {
      expect(splitSqlStatements("--> statement-breakpoint\n")).toEqual([]);
      expect(splitSqlStatements("SELECT 1;")).toEqual(["SELECT 1;"]);
    });
  });

  describe("splitSqliteStatements", () => {
    test("splits on semicolons and drops the terminators", () => {
      expect(
        splitSqliteStatements("CREATE TABLE t (id INTEGER);\nINSERT INTO t VALUES (1);\n"),
      ).toEqual(["CREATE TABLE t (id INTEGER)", "INSERT INTO t VALUES (1)"]);
    });

    test("keeps a final statement without a semicolon", () => {
      expect(splitSqliteStatements("SELECT 1; SELECT 2")).toEqual(["SELECT 1", "SELECT 2"]);
    });

    test("ignores semicolons inside strings, quoted identifiers, and comments", () => {
      const sql = [
        "INSERT INTO t VALUES ('a;b', 'it''s; fine');",
        'CREATE TABLE "x;y" (`a;` TEXT, [b;] TEXT);',
        "-- a comment; not a statement",
        "/* block; comment */ SELECT 1;",
      ].join("\n");
      expect(splitSqliteStatements(sql)).toEqual([
        "INSERT INTO t VALUES ('a;b', 'it''s; fine')",
        'CREATE TABLE "x;y" (`a;` TEXT, [b;] TEXT)',
        "-- a comment; not a statement\n/* block; comment */ SELECT 1",
      ]);
    });

    test("keeps a trigger body as one statement", () => {
      const trigger = [
        "CREATE TEMP TRIGGER log_insert AFTER INSERT ON t",
        "BEGIN",
        "  INSERT INTO log VALUES (new.id);",
        "  UPDATE counts SET n = n + 1;",
        "END",
      ].join("\n");
      expect(splitSqliteStatements(`${trigger};\nSELECT 1;`)).toEqual([trigger, "SELECT 1"]);
    });

    test("drops comment-only and empty segments", () => {
      expect(splitSqliteStatements(";;\n-- only a comment\n")).toEqual([]);
      expect(splitSqliteStatements("")).toEqual([]);
    });
  });
});

const withFs = layer(NodeServices.layer);

withFs("applySqlImports", (it) => {
  it.effect(
    "applies new and changed files once, and keeps hashes only for listed files",
    () =>
      Effect.gen(function* () {
        const fs = yield* FileSystem.FileSystem;
        const path = yield* Path.Path;
        const rootDir = yield* fs.makeTempDirectoryScoped({ prefix: "alchemy-imports-" });
        yield* fs.writeFileString(path.join(rootDir, "a.sql"), "SELECT 1;");
        yield* fs.writeFileString(path.join(rootDir, "b.sql"), "SELECT 2;");
        const applied: string[] = [];
        const apply = (file: { id: string }) => Effect.sync(() => void applied.push(file.id));

        const first = yield* applySqlImports({
          importFiles: ["a.sql", "b.sql"],
          rootDir,
          previous: { "gone.sql": "stale" },
          apply,
        });
        expect(Object.keys(first).sort()).toEqual(["a.sql", "b.sql"]);

        yield* fs.writeFileString(path.join(rootDir, "b.sql"), "SELECT 3;");
        const second = yield* applySqlImports({
          importFiles: ["a.sql", "b.sql"],
          rootDir,
          previous: first,
          apply,
        });
        expect(second["a.sql"]).toEqual(first["a.sql"]);
        expect(second["b.sql"]).not.toEqual(first["b.sql"]);
        expect(applied).toEqual(["a.sql", "b.sql", "b.sql"]);
      }),
    { tags: ["unit", "local"] },
  );
});

describe("stripSslQueryParams", { tags: ["unit", "local"] }, () => {
  test("drops sslmode and channel_binding and keeps everything else", () => {
    expect(
      stripSslQueryParams(
        "postgresql://u:p@host/db?sslmode=require&channel_binding=require&application_name=x",
      ),
    ).toEqual("postgresql://u:p@host/db?application_name=x");
    expect(stripSslQueryParams("not a url")).toEqual("not a url");
  });
});