Migration

migration.ts

import * as fs from 'fs';
import * as path from 'path';
import { fileURLToPath } from 'url'
import { pool } from '../db/db.js';
import { sql } from '../util.js'
import { PoolClient } from 'pg';

const __filename = fileURLToPath(import.meta.url);
const __dirname = path.dirname(__filename);

export const runMigrations = async()=> {
    const migrationsDir = path.join(__dirname, '../../migrations');
    const migrationFiles = fs.readdirSync(migrationsDir)
        .filter(file => file.endsWith('.sql'));

    let migrations = migrationFiles.map(file => {
        let migration_number = Number(file.split('-')[0]); // Extract migration_number
        if(isNaN(migration_number))
            throw new Error(`File: ${file} is improperly named. format is <migration_number>-<direction>-<comment>.sql`)
        const filePath = path.join(migrationsDir, file);
        return { migration_number, migration_name: file.slice(0, -4), filePath };
    })
        .filter((obj)=> obj.migration_name.split("-")[1] === "up")
        .sort((a,b)=> a.migration_number - b.migration_number);

    let client: PoolClient;
    try {
        client = await pool.connect();

        await client.query(sql`
            CREATE TABLE IF NOT EXISTS migrations (
                id SERIAL PRIMARY KEY,
                migration_number INTEGER,
                migration_name VARCHAR(255),
                completed BOOLEAN,
                created_at TIMESTAMP DEFAULT NOW()
            );
        `);

        const res = await client.query(sql`SELECT migration_name FROM migrations WHERE completed = TRUE`);
        const completedMigrations = new Set<string>();
        for(let i = 0; i < res.rows.length; i++)
            completedMigrations.add(res.rows[i].migration_name)

        for (const migration of migrations) {
            if (!completedMigrations.has(migration.migration_name)) {
                const sqlQuery = fs.readFileSync(migration.filePath, 'utf-8');
                try {
                    await client.query("BEGIN")
                    const individualQueries = sqlQuery.split('---').map(q=>q.trim())
                    for(let i = 0; i  < individualQueries.length; i++)
                        await client.query(individualQueries[i])
                    await client.query("INSERT INTO migrations (migration_name, migration_number, completed) VALUES ($1, $2, TRUE)", [migration.migration_name, migration.migration_number])
                    await client.query("COMMIT")
                    console.log(`Migration ${migration.migration_name} applied successfully.`);
                } catch (err) {
                    // Rollback on error
                    await client.query('ROLLBACK');
                    console.error(`Error applying migration ${migration.migration_name}:`, err);
                    throw err; // Rethrow error after rollback
                }
            } else {
                console.log(`Migration ${migration.migration_name} already applied.`);
            }
        }
        console.log('All migrations completed.');
    } catch (err) {
        console.error('Migration failed:', err);
    } finally {
        //@ts-ignore
        if (client) client.release();
    }
}

if(process.env.NODE_ENV !== "test"){
    runMigrations()
        .then(() => {
                pool.end();
        })
        .catch(err => {
            console.error('Migration error:', err);
            if(process.env.NODE_ENV !== "test"){
                pool.end();
            }
        });
}