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 { 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();

      console.log('[DEBUG] lastid', lastID);

      for (const file of files) {
        const m = /^(\d{12})_(.*)\.sql$/.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;

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

        Logger.info('[INFO] Applying 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;
        }
      }
    }
  }

  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');
  }
}