Repository Public
1/**2 * =============================================================================3 * SP BOUCHER | SOFTWARE & RESEARCH4 * ARTIFICIAL INTELLIGENCE • FINANCE • ENGINEERING5 * =============================================================================6 *7 * Project : Sheet8 * File : scripts/migrate.ts9 * Module : scripts10 * Repository : https://git.spboucher.ai/sheet11 * Project Website : https://sheet.spboucher.ai12 * Created : 2026-11-1713 * Last Modified : 2026-11-1714 *15 * Author : Simon-Pierre Boucher16 * Position : Professor of Finance17 * Institution : Université du Québec en Outaouais (UQO)18 * Organization : SP Boucher19 * Website : https://spboucher.ai20 * Git : https://git.spboucher.ai21 *22 * Preferred Contact: contact@spboucher.ai23 * Development : dev@spboucher.ai24 * Support : support@spboucher.ai25 * Security/Admin : admin@spboucher.ai26 *27 * Copyright (c) 202628 * Simon-Pierre Boucher29 * All rights reserved where applicable. This file is governed by the30 * applicable project license, copyright notices and relevant law.31 * Existing third-party rights and notices remain unaffected.32 *33 * License : None specified (no license file in this repository)34 *35 * Do not embed credentials, private keys, access tokens,36 * certificates or confidential personal information.37 *38 * =============================================================================39 * END OF SP BOUCHER SOURCE HEADER40 * =============================================================================41 */4243import { readFile, readdir } from "node:fs/promises";44import path from "node:path";45import { createHash } from "node:crypto";46import "./_env";47import { sql } from "../src/lib/db";4849/** Applies db/migrations/*.sql in order, once each (checksummed). */50async function main() {51 await sql`CREATE TABLE IF NOT EXISTS schema_migrations (name text PRIMARY KEY, checksum text NOT NULL, applied_at timestamptz NOT NULL DEFAULT now())`;52 const dir = path.join(process.cwd(), "db/migrations");53 for (const name of (await readdir(dir)).filter((f) => f.endsWith(".sql")).sort()) {54 const body = await readFile(path.join(dir, name), "utf8");55 const checksum = createHash("sha256").update(body).digest("hex");56 const [done] = await sql<{ checksum: string }[]>`SELECT checksum FROM schema_migrations WHERE name = ${name}`;57 if (done) { if (done.checksum !== checksum) throw new Error(`Migration ${name} was modified after being applied`); continue; }58 await sql.begin(async (tx) => { await tx.unsafe(body); await tx`INSERT INTO schema_migrations (name, checksum) VALUES (${name}, ${checksum})`; });59 console.log(`applied ${name}`);60 }61 await sql.end();62}63main().catch((e) => { console.error(e.message); process.exit(1); });64