1import * as Effect from "effect/Effect";
2import type { SqlExecutor } from "./Format.ts";
3import { quoteIdentifier, sqlLiteral } from "./Utils.ts";
4
5export interface TableColumn {
6 name: string;
7 type: string;
8}
9
10/**
11 * List a table's columns (empty when the table doesn't exist), normalized
12 * across dialects. `schema` defaults to the connection's current/default
13 * schema.
14 */
15export const tableColumns = (
16 executor: SqlExecutor,
17 table: string,
18 schema?: string,
19): Effect.Effect<TableColumn[], never, never> => {
20 switch (executor.dialect) {
21 case "sqlite":
22 return executor
23 .query(`PRAGMA table_info(${quoteIdentifier(table, "sqlite")});`)
24 .pipe(
25 Effect.map((rows) =>
26 rows.map((row) => ({
27 name: String(row.name),
28 type: String(row.type ?? "").toUpperCase(),
29 })),
30 ),
31 // A missing table yields an empty PRAGMA result, not an error, but
32 // some tunnels surface it as one — treat both as "absent".
33 Effect.catch(() => Effect.succeed([])),
34 );
35 case "postgres":
36 return executor
37 .query(
38 `SELECT column_name AS name, data_type AS type
39 FROM information_schema.columns
40 WHERE table_name = ${sqlLiteral(table)}
41 AND table_schema = ${schema ? sqlLiteral(schema) : "current_schema()"}
42 ORDER BY ordinal_position;`,
43 )
44 .pipe(
45 Effect.map((rows) =>
46 rows.map((row) => ({
47 name: String(row.name),
48 type: String(row.type ?? "").toUpperCase(),
49 })),
50 ),
51 Effect.catch(() => Effect.succeed([])),
52 );
53 case "mysql":
54 return executor
55 .query(
56 `SELECT COLUMN_NAME AS name, DATA_TYPE AS type
57 FROM information_schema.columns
58 WHERE table_name = ${sqlLiteral(table)}
59 AND table_schema = ${schema ? sqlLiteral(schema) : "DATABASE()"}
60 ORDER BY ORDINAL_POSITION;`,
61 )
62 .pipe(
63 Effect.map((rows) =>
64 rows.map((row) => ({
65 name: String(row.name),
66 type: String(row.type ?? "").toUpperCase(),
67 })),
68 ),
69 Effect.catch(() => Effect.succeed([])),
70 );
71 }
72};
73
74/**
75 * Classify an existing applied-migrations table by column set — the same
76 * versioning trick drizzle uses (there is deliberately no version column):
77 *
78 * - `absent` — no table
79 * - `drizzle-shaped` — has `hash` (drizzle v1 / alchemy current)
80 * - `legacy-alchemy` — `name` + `applied_at`, no `hash` (pre-registry Alchemy)
81 * - `legacy-2col` — two columns, no `name`/`hash` (oldest Alchemy shape,
82 * where the primary column carried the migration name)
83 * - `wrangler` — `id`/`name`/`applied_at` with an INTEGER id (already
84 * wrangler's real shape)
85 * - `unknown` — anything else
86 */
87export type TableShape =
88 | "absent"
89 | "drizzle-shaped"
90 | "legacy-alchemy"
91 | "legacy-2col"
92 | "wrangler"
93 | "unknown";
94
95export const classifyTable = (columns: TableColumn[]): TableShape => {
96 if (columns.length === 0) return "absent";
97 const names = new Set(columns.map((c) => c.name));
98 if (names.has("hash")) return "drizzle-shaped";
99 if (names.has("name") && names.has("applied_at")) {
100 const id = columns.find((c) => c.name === "id");
101 return id && /INT/.test(id.type) ? "wrangler" : "legacy-alchemy";
102 }
103 if (columns.length === 2 && !names.has("name")) return "legacy-2col";
104 return "unknown";
105};