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

import crypto from "node:crypto";
import * as Effect from "effect/Effect";
import * as FileSystem from "effect/FileSystem";
import * as Path from "effect/Path";

export interface SqlFile {
  id: string;
  sql: string;
  hash: string;
}

/**
 * Recursively list `.sql` files under `directory`, sorted by their numeric
 * prefix (e.g. `0001_init.sql`) and then by name.
 */
export const listSqlFiles = (directory: string) =>
  Effect.gen(function* () {
    const fs = yield* FileSystem.FileSystem;
    const entries = yield* fs.readDirectory(directory, { recursive: true });

    const sqlFiles = entries
      .filter((name) => name.endsWith(".sql"))
      .sort((a, b) => {
        const aNum = getPrefix(a);
        const bNum = getPrefix(b);
        if (aNum !== null && bNum !== null) return aNum - bNum;
        if (aNum !== null) return -1;
        if (bNum !== null) return 1;
        return a.localeCompare(b);
      });

    return yield* Effect.forEach(sqlFiles, (id) => readSqlFile(directory, id));
  });

/**
 * Read a single `.sql` file relative to `directory` and compute its content
 * hash. The `sql` field is marked non-enumerable so it isn't serialized into
 * resource state.
 */
export const readSqlFile = (directory: string, name: string) =>
  Effect.gen(function* () {
    const fs = yield* FileSystem.FileSystem;
    const path = yield* Path.Path;
    const sql = yield* fs.readFileString(path.resolve(directory, name));
    const hash = crypto.createHash("sha256").update(sql).digest("hex");
    const file: SqlFile = { id: name, sql, hash };
    Object.defineProperty(file, "sql", { enumerable: false });
    return file;
  });

export const hashMigrations = (migrationsDir: string) =>
  listSqlFiles(migrationsDir).pipe(
    Effect.map((files) => {
      const hashes: Record<string, string> = {};
      for (const file of files) hashes[file.id] = file.hash;
      return hashes;
    }),
  );

/**
 * Apply `importFiles` (paths relative to `rootDir`): each file runs through
 * `apply` once, and again only when its contents change. `previous` is the
 * `importHashes` a prior deploy persisted; the result is the new
 * `importHashes`, covering exactly the files still listed.
 */
export const applySqlImports = <E, R>(options: {
  importFiles: ReadonlyArray<string> | undefined;
  rootDir: string;
  previous: Record<string, string> | undefined;
  apply: (file: SqlFile) => Effect.Effect<void, E, R>;
}) =>
  Effect.gen(function* () {
    const hashes: Record<string, string> = {};
    for (const filePath of options.importFiles ?? []) {
      const file = yield* readSqlFile(options.rootDir, filePath);
      if (options.previous?.[filePath] !== file.hash) {
        yield* options.apply(file);
      }
      hashes[filePath] = file.hash;
    }
    return hashes;
  });

export const hashImports = (importFiles: ReadonlyArray<string>, rootDir: string) =>
  Effect.gen(function* () {
    const hashes: Record<string, string> = {};
    for (const filePath of importFiles) {
      const file = yield* readSqlFile(rootDir, filePath);
      hashes[filePath] = file.hash;
    }
    return hashes;
  });

const getPrefix = (name: string): number | null => {
  const prefix = name.split("_")[0];
  const num = Number.parseInt(prefix, 10);
  return Number.isNaN(num) ? null : num;
};

// Drizzle (and other migration tools) emit `--> statement-breakpoint` between
// statements. drizzle-kit puts the marker directly after single-line
// statements (`...;--> statement-breakpoint`), so the split can't anchor on a
// preceding newline.
const STATEMENT_BREAKPOINT = /\s*-->\s*statement-breakpoint\s*/g;

/**
 * Split a migration file into individual statements on its
 * `--> statement-breakpoint` markers. MySQL engines need this: they only
 * treat `--` as a comment when followed by whitespace, so the `-->` token is
 * a syntax error ("syntax error at position 2" on Vitess). Postgres and
 * sqlite comment on `--` regardless of the next character, so their runners
 * send files whole.
 */
export const splitSqlStatements = (sql: string): string[] =>
  sql
    .split(STATEMENT_BREAKPOINT)
    .map((s) => s.trim())
    .filter((s) => s.length > 0);

type SqliteToken = "semi" | "ws" | "other" | "explain" | "create" | "temp" | "trigger" | "end";
type SqliteState =
  | "invalid"
  | "start"
  | "normal"
  | "explain"
  | "create"
  | "trigger"
  | "semi"
  | "end";

// SQLite's `sqlite3_complete` state machine: a `;` ends a statement unless
// it sits inside a `CREATE [TEMP] TRIGGER ... BEGIN ... END` body.
const SQLITE_TRANSITIONS: Record<SqliteState, Record<SqliteToken, SqliteState>> = {
  invalid: {
    semi: "start",
    ws: "invalid",
    other: "normal",
    explain: "explain",
    create: "create",
    temp: "normal",
    trigger: "normal",
    end: "normal",
  },
  start: {
    semi: "start",
    ws: "start",
    other: "normal",
    explain: "explain",
    create: "create",
    temp: "normal",
    trigger: "normal",
    end: "normal",
  },
  normal: {
    semi: "start",
    ws: "normal",
    other: "normal",
    explain: "normal",
    create: "normal",
    temp: "normal",
    trigger: "normal",
    end: "normal",
  },
  explain: {
    semi: "start",
    ws: "explain",
    other: "explain",
    explain: "normal",
    create: "create",
    temp: "normal",
    trigger: "normal",
    end: "normal",
  },
  create: {
    semi: "start",
    ws: "create",
    other: "normal",
    explain: "normal",
    create: "normal",
    temp: "create",
    trigger: "trigger",
    end: "normal",
  },
  trigger: {
    semi: "semi",
    ws: "trigger",
    other: "trigger",
    explain: "trigger",
    create: "trigger",
    temp: "trigger",
    trigger: "trigger",
    end: "trigger",
  },
  semi: {
    semi: "semi",
    ws: "semi",
    other: "trigger",
    explain: "trigger",
    create: "trigger",
    temp: "trigger",
    trigger: "trigger",
    end: "end",
  },
  end: {
    semi: "start",
    ws: "end",
    other: "trigger",
    explain: "trigger",
    create: "trigger",
    temp: "trigger",
    trigger: "trigger",
    end: "trigger",
  },
};

const SQLITE_IDENTIFIER_CHAR = /[A-Za-z0-9_$\u0080-￿]/;

const classifySqliteIdentifier = (identifier: string): SqliteToken => {
  switch (identifier.toLowerCase()) {
    case "explain":
      return "explain";
    case "create":
      return "create";
    case "temp":
    case "temporary":
      return "temp";
    case "trigger":
      return "trigger";
    case "end":
      return "end";
    default:
      return "other";
  }
};

/**
 * Split a SQLite script into its statements, without their terminating `;`.
 * Semicolons inside quoted strings, identifiers (`"…"`, `` `…` ``, `[…]`),
 * comments, and trigger bodies don't split. Comments before a statement stay
 * with it, and comment-only segments are dropped. Matches how
 * `wrangler d1 execute --file` splits a file.
 */
export const splitSqliteStatements = (sql: string): string[] => {
  const statements: string[] = [];
  let start = 0;
  let state: SqliteState = "invalid";
  let hasContent = false;
  const flush = (end: number) => {
    const statement = sql.slice(start, end).trim();
    if (hasContent && statement.length > 0) statements.push(statement);
  };
  for (let index = 0; index < sql.length; index++) {
    const char = sql[index];
    let token: SqliteToken;
    if (char === ";") {
      token = "semi";
    } else if (/\s/.test(char)) {
      token = "ws";
    } else if (char === "/" && sql[index + 1] === "*") {
      const end = sql.indexOf("*/", index + 2);
      index = end === -1 ? sql.length : end + 1;
      token = "ws";
    } else if (char === "-" && sql[index + 1] === "-") {
      const end = sql.indexOf("\n", index + 2);
      index = end === -1 ? sql.length : end;
      token = "ws";
    } else if (char === "'" || char === '"' || char === "`" || char === "[") {
      const end = sql.indexOf(char === "[" ? "]" : char, index + 1);
      index = end === -1 ? sql.length : end;
      token = "other";
    } else if (SQLITE_IDENTIFIER_CHAR.test(char)) {
      const identifierStart = index;
      while (index + 1 < sql.length && SQLITE_IDENTIFIER_CHAR.test(sql[index + 1])) index++;
      token = classifySqliteIdentifier(sql.slice(identifierStart, index + 1));
    } else {
      token = "other";
    }
    hasContent ||= token !== "semi" && token !== "ws";
    state = SQLITE_TRANSITIONS[state][token];
    if (token === "semi" && state === "start") {
      flush(index);
      start = index + 1;
      hasContent = false;
    }
  }
  flush(sql.length);
  return statements;
};