db.ts

/*
 * About migrations:
 *   File path: src/lib/server/database/migrations
 *   File name: \d+_.*\.sql
 *   Naming: <order>_<description>.sql
 *     <order> - number that represents the order in which the migration should be run
 *     <description> - short description of the migration
 *     The order is interpreted as integer, leading zeros are ignored.
 *   File content: SQL code
 *   Important: after execution/committing neither filename nor SQL code should be changed.
 *   The filename is used as key, so *any* to it change will be treated as a new migration.
 */

import postgres, { type Sql } from "postgres";
import fs from "node:fs/promises";
import { env } from "$env/dynamic/private";
import { building, dev } from "$app/environment";

// fix for ARRAY_AGG on LEFT JOIN returning {NULL} instead of {null} or empty array
(() => {
	const original = JSON.parse;
	JSON.parse = (text, reviver) => {
		if (text === "NULL") return null;
		else return original(text, reviver);
	};
})();

export const sql = building ? null as unknown as Sql : await init(postgres({
	host: env.POSTGRES_HOST || "localhost",
	database: env.POSTGRES_DB,
	user: env.POSTGRES_USER,
	password: env.POSTGRES_PASSWORD,
	port: parseInt(env.POSTGRES_PORT || "5432"),
	transform: postgres.camel,
	ssl: dev ? false : {
		rejectUnauthorized: true,
	},
}));

async function init(sql: Sql) {
	await sql`
	CREATE TABLE IF NOT EXISTS migrations (
		id SERIAL PRIMARY KEY,
		name TEXT,
		dateMigrated TIMESTAMP NOT NULL DEFAULT NOW()
	);
	`;
	const basePath = dev ? "src/lib/server/database/migrations" : "/app/migrations";
	const migrationFiles = (await fs.readdir(basePath))
		.filter(name => name.endsWith(".sql"))
		.map(name => ({
			filePath: `${basePath}/${name}`,
			name: name.substring(0, name.length - 4),
			sortKey: parseInt(name.split("_")[0]),
		}))
		.sort((a, b) => a.sortKey - b.sortKey);
	const migrations = (await sql`SELECT name FROM migrations`).map(row => row.name as string);
	for (const migration of migrationFiles) {
		if (migrations.includes(migration.name)) continue;
		console.log(`Running migration: ${migration.name}`);
		// migrations must run in order
		// eslint-disable-next-line no-await-in-loop
		await sql.begin(sql => [
			sql.file(migration.filePath),
			sql`INSERT INTO migrations (name) VALUES (${migration.name})`,
		]);
	}
	return sql;
}