hostbbq-monitoring/server/src/ctrl/db-migration.class.ts

import fs from 'fs';
import fsp from 'fs/promises';
import path from 'path';
import { Database } from 'sqlite3';
import { Logger } from '../../../common/util/logger.class';

import { MigrationException } from '../lib/migration-exception.class';
import { MigrationRunner } from '../lib/migration-runner.interface';
import { SQLiteController } from './sqlite-controller.base';

export class DBMigration extends SQLiteController {
  constructor(protected db: Database) {
    super();
  }

  public async update() {
    const migrationsDir = path.resolve(__dirname, '../migrations');
    if (!fs.existsSync(migrationsDir)) return;

    const files = await fsp.readdir(migrationsDir);
    if (files.length) {
      files.sort((a, b) => Number(a.substring(0, 12)) - Number(b.substring(0, 12)));

      await this.createMigrationsTable();
      const lastID = await this.getLastID();

      Logger.debug('[DEBUG] lastid', lastID);

      for (const file of files) {
        const m = /^(\d{12})_(.*)\.(sql|js)$/.exec(file);

        if (!m) {
          throw new MigrationException(`File ${file} does not match migration file pattern. Aborted processing migrations.`);
        }

        const id = Number(m[1]);
        if (id <= lastID) continue;

        if (m[3] === 'sql') {
          const migFilepath = path.join(migrationsDir, file);
          const migration = await fsp.readFile(migFilepath, { encoding: 'utf-8' });

          Logger.info('[INFO] Applying SQL DB migration', file);
          await this.beginTransaction();
          try {
            await this.exec(migration);
            await this.run('INSERT INTO db_migrations(id, title, migrated) VALUES(?, ?, ?);', [id, m[2], new Date().getTime()]);
            await this.commit();
            Logger.info('[INFO] DB migration', file, 'succeeded.');
          } catch (error) {
            Logger.error('[ERROR] DB migration failed at', file, '- Rolling back...');
            await this.rollback();

            throw error;
          }
        } else {
          const migFilepath = path.join(migrationsDir, file.substring(0, file.length - 3));
          const imp = require(migFilepath).default;
          if (typeof imp !== 'function') {
            throw new MigrationException(`File ${file} is not a valid <MigrationRunner> implementation!`);
          }
          const mig = imp as MigrationRunner;

          Logger.info('[INFO] Applying TypeScript DB migration', file);
          await this.beginTransaction();
          try {
            await mig(this);
            await this.run('INSERT INTO db_migrations(id, title, migrated) VALUES(?, ?, ?);', [id, m[2], new Date().getTime()]);
            await this.commit();
            Logger.info('[INFO] DB migration', file, 'succeeded.');
          } catch (error) {
            Logger.error('[ERROR] DB migration failed at', file, '- Rolling back...');
            await this.rollback();

            throw error;
          }
        }
      }
    }
  }

  private async createMigrationsTable() {
    await this.run(
      `CREATE TABLE IF NOT EXISTS db_migrations (
        id INTEGER PRIMARY KEY,
        title TEXT NOT NULL,
        migrated INTEGER NOT NULL
      );`,
      []
    );
  }

  private async getLastID() {
    const results = await this.stmt(`SELECT id FROM db_migrations ORDER BY id DESC LIMIT 0, 1;`, []);
    return Number(results.rows[0]?.['id'] ?? '0');
  }
}