1import * as Effect from "effect/Effect";
2import {
3 MigrationError,
4 MigrationHistoryConflictError,
5 type MigrationRecord,
6 type SqlExecutor,
7} from "./Format.ts";
8import { classifyTable, tableColumns } from "./Introspect.ts";
9import { quoteIdentifier, sqlLiteral, timestampPrefixMillis } from "./Utils.ts";
10
11/**
12 * A row of applied history harvested from a foreign migration tool's table,
13 * normalized for insertion into `__alchemy_migrations`.
14 */
15export interface ConvertedRow {
16 name: string;
17 hash: string | undefined;
18 createdAtMillis: number | undefined;
19 appliedAt: string | undefined;
20}
21
22export interface ForeignHistory {
23 /** Which tool's bookkeeping this history came from. */
24 tool: "drizzle" | "prisma" | "wrangler" | "legacy-alchemy";
25 /** Display name of the source table (schema-qualified where relevant). */
26 source: string;
27 rows: ConvertedRow[];
28}
29
30/**
31 * Normalize a timestamp-ish column value for re-insertion as a SQL
32 * literal. Drivers differ: pg hands back JS Dates (whose default
33 * stringification pg itself cannot parse — "GMT-0700 (…)"), sqlite hands
34 * back strings.
35 */
36export const toTimestampString = (value: unknown): string | undefined =>
37 value === null || value === undefined
38 ? undefined
39 : value instanceof Date
40 ? value.toISOString()
41 : String(value);
42
43const qualify = (
44 table: string,
45 dialect: SqlExecutor["dialect"],
46 schema?: string,
47) =>
48 schema
49 ? `${quoteIdentifier(schema, dialect)}.${quoteIdentifier(table, dialect)}`
50 : quoteIdentifier(table, dialect);
51
52/**
53 * Discover applied-migration history left behind by the tool a user is
54 * migrating FROM — drizzle-kit, Prisma, or wrangler — so it can be copied
55 * into Alchemy's own table once. This is a ONE-WAY migration: the foreign
56 * table is read but never written or dropped (it is simply frozen), and
57 * from that point on Alchemy's table is the only bookkeeping.
58 *
59 * Sources probed, most-specific first:
60 * - drizzle: `__drizzle_migrations` (in the `drizzle` schema on Postgres) —
61 * drizzle's columns are Alchemy's columns, so rows copy verbatim.
62 * - prisma: `_prisma_migrations` — `migration_name`/`checksum` map to
63 * `name`/`hash` (both are sha256 of `migration.sql`). A failed migration
64 * (`finished_at IS NULL`, not rolled back) aborts the conversion: it must
65 * be repaired with `prisma migrate resolve` first. Rolled-back rows are
66 * skipped.
67 * - wrangler (sqlite): `d1_migrations` in wrangler's shape — names carry
68 * over; hashes backfill from local records.
69 */
70export const findForeignHistory = (options: {
71 executor: SqlExecutor;
72 /** The resolved Alchemy table — a source with this name is not foreign. */
73 table: string;
74}): Effect.Effect<ForeignHistory | undefined, MigrationError> =>
75 Effect.gen(function* () {
76 const { executor, table } = options;
77 const dialect = executor.dialect;
78
79 // drizzle
80 const drizzleSchema = dialect === "postgres" ? "drizzle" : undefined;
81 if (table !== "__drizzle_migrations") {
82 const columns = yield* tableColumns(
83 executor,
84 "__drizzle_migrations",
85 drizzleSchema,
86 );
87 if (classifyTable(columns) === "drizzle-shaped") {
88 const rows = yield* executor.query(
89 `SELECT hash, created_at, name, applied_at FROM ${qualify("__drizzle_migrations", dialect, drizzleSchema)} ORDER BY id;`,
90 );
91 return {
92 tool: "drizzle" as const,
93 source: drizzleSchema
94 ? `${drizzleSchema}.__drizzle_migrations`
95 : "__drizzle_migrations",
96 rows: rows
97 .filter((row) => row.name !== null && row.name !== undefined)
98 .map((row) => ({
99 name: String(row.name),
100 hash:
101 row.hash === null || row.hash === undefined
102 ? undefined
103 : String(row.hash),
104 createdAtMillis:
105 row.created_at === null || row.created_at === undefined
106 ? undefined
107 : Number(row.created_at),
108 appliedAt: toTimestampString(row.applied_at),
109 })),
110 };
111 }
112 }
113
114 // prisma
115 if (table !== "_prisma_migrations") {
116 const columns = yield* tableColumns(executor, "_prisma_migrations");
117 const names = new Set(columns.map((c) => c.name));
118 if (names.has("migration_name") && names.has("checksum")) {
119 const rows = yield* executor.query(
120 `SELECT migration_name, checksum, started_at, finished_at, rolled_back_at FROM ${quoteIdentifier("_prisma_migrations", dialect)};`,
121 );
122 const failed = rows.filter(
123 (row) =>
124 (row.finished_at === null || row.finished_at === undefined) &&
125 (row.rolled_back_at === null || row.rolled_back_at === undefined),
126 );
127 if (failed.length > 0) {
128 return yield* new MigrationError({
129 message:
130 `_prisma_migrations records ${failed.length} failed migration(s) ` +
131 `(${failed.map((r) => String(r.migration_name)).join(", ")}). ` +
132 `Repair them with "prisma migrate resolve" before migrating this ` +
133 `database's bookkeeping to Alchemy.`,
134 });
135 }
136 return {
137 tool: "prisma" as const,
138 source: "_prisma_migrations",
139 rows: rows
140 .filter(
141 (row) =>
142 row.finished_at !== null &&
143 row.finished_at !== undefined &&
144 (row.rolled_back_at === null ||
145 row.rolled_back_at === undefined),
146 )
147 .map((row) => ({
148 name: String(row.migration_name),
149 hash:
150 row.checksum === null || row.checksum === undefined
151 ? undefined
152 : String(row.checksum),
153 createdAtMillis: timestampPrefixMillis(
154 String(row.migration_name),
155 ),
156 appliedAt: toTimestampString(row.finished_at),
157 })),
158 };
159 }
160 }
161
162 // wrangler / pre-registry Alchemy on D1 (which shared the
163 // d1_migrations name — covers state-lost adoption of old deploys)
164 if (dialect === "sqlite" && table !== "d1_migrations") {
165 const shape = classifyTable(
166 yield* tableColumns(executor, "d1_migrations"),
167 );
168 if (
169 shape === "wrangler" ||
170 shape === "legacy-alchemy" ||
171 shape === "legacy-2col"
172 ) {
173 const nameExpr = shape === "legacy-2col" ? "id" : "name";
174 const rows = yield* executor.query(
175 `SELECT ${nameExpr} AS name, applied_at FROM ${quoteIdentifier("d1_migrations", dialect)} ORDER BY id;`,
176 );
177 return {
178 tool:
179 shape === "wrangler"
180 ? ("wrangler" as const)
181 : ("legacy-alchemy" as const),
182 source: "d1_migrations",
183 rows: rows
184 .filter((row) => row.name !== null && row.name !== undefined)
185 .map((row) => ({
186 name: String(row.name),
187 hash: undefined,
188 createdAtMillis: timestampPrefixMillis(String(row.name)),
189 appliedAt: toTimestampString(row.applied_at),
190 })),
191 };
192 }
193 }
194
195 return undefined;
196 });
197
198/**
199 * Validate foreign history against the local migrations directory and fill
200 * in missing hashes. Every foreign row must match a local record by name
201 * (or by the `<dir>/migration.sql` ⇄ `<dir>` aliasing between flat and
202 * directory layouts) — an unmatched row means migrations were applied that
203 * this checkout does not have.
204 */
205export const matchForeignRows = (options: {
206 history: ForeignHistory;
207 records: ReadonlyArray<MigrationRecord>;
208}): Effect.Effect<ConvertedRow[], MigrationHistoryConflictError> =>
209 Effect.gen(function* () {
210 const { history, records } = options;
211 const byName = new Map(records.map((r) => [r.name, r]));
212 const byAlias = new Map(
213 records.flatMap((r) => [
214 [`${r.name}/migration.sql`, r] as const,
215 [r.name.replace(/\/migration\.sql$/, ""), r] as const,
216 ]),
217 );
218 const matched: ConvertedRow[] = [];
219 const unmatched: string[] = [];
220 for (const row of history.rows) {
221 const record = byName.get(row.name) ?? byAlias.get(row.name);
222 if (!record) {
223 unmatched.push(row.name);
224 continue;
225 }
226 matched.push({
227 ...row,
228 hash: row.hash ?? record.hash,
229 createdAtMillis: row.createdAtMillis ?? record.createdAtMillis,
230 });
231 }
232 if (unmatched.length > 0) {
233 return yield* new MigrationHistoryConflictError({
234 table: history.source,
235 unmatched,
236 message:
237 `While migrating ${history.tool} bookkeeping (${history.source}) to ` +
238 `Alchemy, ${unmatched.length} recorded migration(s) match no local ` +
239 `file: ${unmatched.join(", ")}. Migrations were applied to this ` +
240 `database that are missing from the local environment.`,
241 });
242 }
243 return matched;
244 });
245
246/** Render an INSERT for a converted history row into Alchemy's table. */
247export const convertedRowInsertSql = (
248 table: string,
249 dialect: SqlExecutor["dialect"],
250 row: ConvertedRow,
251): string =>
252 `INSERT INTO ${quoteIdentifier(table, dialect)} (hash, created_at, name, applied_at) VALUES (${sqlLiteral(row.hash ?? "")}, ${sqlLiteral(row.createdAtMillis ?? null)}, ${sqlLiteral(row.name)}, ${sqlLiteral(row.appliedAt ?? null)});`;