Skip to content

Every analytics tool wants a subscription. And your data. I built Twiligent instead. YouTube stats. Instagram stats. One dashboard. Runs locally on your machine.

7.2 KB
Raw Blame
import { test, describe } from 'node:test';
import assert from 'node:assert/strict';
import { readdirSync, readFileSync } from 'node:fs';
import { fileURLToPath } from 'node:url';

const supabase = fileURLToPath(new URL('../../../supabase/', import.meta.url));
const migrations = readdirSync(`${supabase}migrations`).sort();
const rollbacks = readdirSync(`${supabase}rollbacks`).sort();
const read = (folder: string, name: string) => readFileSync(`${supabase}${folder}/${name}`, 'utf8');
const isBaseline = (name: string) => /^\d{14}_baseline_/.test(name);
// migrations before this version were applied to production before the rule and stay as recorded
const ONE_ENTITY_FROM = '20261016100000';
// a name starts with what the file does to its one entity, so the folder reads as a list of changes
const VERBS = ['baseline', 'create', 'alter', 'add', 'index', 'revoke', 'check', 'replace', 'drop', 'rename'];
const KINDS = ['table', 'index', 'function', 'trigger'] as const;
type ObjectKind = typeof KINDS[number];

function isRealUtcTime(version: string): boolean {
    const [year, month, day, hour, minute, second] = [[0, 4], [4, 6], [6, 8], [8, 10], [10, 12], [12, 14]].map(([from, to]) => Number(version.slice(from, to)));
    const time = new Date(Date.UTC(year, month - 1, day, hour, minute, second));
    return time.toISOString().replace(/\D/g, '').slice(0, 14) === version;
}

// replays every create and drop in order, so an index dropped and made again counts once
function objectsFromMigrations(): Record<ObjectKind, string[]> {
    const live = Object.fromEntries(KINDS.map(kind => [kind, new Set<string>()])) as Record<ObjectKind, Set<string>>;
    const statement = /\b(create|drop)\s+(?:or replace\s+)?(table|unique index|index|function|trigger)\s+(?:if (?:not )?exists\s+)?(?:public\.)?(\w+)/gi;
    for (const name of migrations) {
        const sql = read('migrations', name).replace(/--.*$/gm, '');
        for (const [, verb, kind, object] of sql.matchAll(statement)) {
            const set = live[kind.toLowerCase().replace('unique ', '') as ObjectKind];
            if (verb.toLowerCase() === 'create') set.add(object);
            else set.delete(object);
        }
    }
    return Object.fromEntries(KINDS.map(kind => [kind, [...live[kind]].sort()])) as Record<ObjectKind, string[]>;
}

function objectsInSchemaTree(): Record<ObjectKind, string[]> {
    const tables = readdirSync(`${supabase}schemas/public/tables`);
    const inTables = (pattern: RegExp) => tables.flatMap(file => [...read('schemas/public/tables', file).matchAll(pattern)].map(match => match[1]));
    return {
        table: tables.map(file => file.replace(/\.sql$/, '')).sort(),
        index: inTables(/CREATE (?:UNIQUE )?INDEX (\w+)/g).sort(),
        function: readdirSync(`${supabase}schemas/public/functions`).map(file => file.replace(/\.sql$/, '')).sort(),
        trigger: inTables(/CREATE TRIGGER (\w+)/g).sort(),
    };
}

describe('database migrations', () => {
    test('every migration is named <14 digit version>_<lowercase name>.sql with a unique version', () => {
        for (const name of [...migrations, ...rollbacks]) assert.match(name, /^\d{14}_[a-z0-9_]+\.sql$/, name);
        const versions = migrations.map(name => name.slice(0, 14));
        assert.equal(new Set(versions).size, versions.length);
    });

    test('every version is a real UTC date and time, as npm run db:new writes it', () => {
        for (const name of migrations) assert.ok(isRealUtcTime(name.slice(0, 14)), `${name}: make the file with npm run db:new`);
    });

    test('every name starts with what the file does: baseline, create, alter, add, index, revoke, check, replace, drop or rename', () => {
        for (const name of migrations) assert.ok(VERBS.includes(name.slice(15).split('_')[0]), name);
    });

    test('the schema tree in supabase/schemas has the same tables, indexes, functions and triggers as the migrations', () => {
        const fromMigrations = objectsFromMigrations();
        const inTree = objectsInSchemaTree();
        for (const kind of KINDS) assert.deepEqual(inTree[kind], fromMigrations[kind], `${kind}s differ; after npm run db:push, run npm run db:schema and commit supabase/schemas`);
    });

    test('the baseline files come before every change, so a new instance starts from the same tables', () => {
        const lastBaseline = migrations.findLastIndex(isBaseline);
        const firstChange = migrations.findIndex(name => !isBaseline(name));
        assert.ok(lastBaseline >= 0);
        assert.ok(lastBaseline < firstChange);
    });

    test('every change has a rollback with the same name, and every rollback has its migration', () => {
        assert.deepEqual(rollbacks, migrations.filter(name => !isBaseline(name)));
    });

    test('the baseline files can run again on a database that has them', () => {
        for (const name of migrations.filter(isBaseline)) {
            const sql = read('migrations', name);
            assert.doesNotMatch(sql, /create table (?!if not exists)/i, name);
            assert.doesNotMatch(sql, /create index (?!if not exists)/i, name);
            assert.doesNotMatch(sql, /add column (?!if not exists)/i, name);
        }
    });

    test('every function a migration creates is closed to the browser roles', () => {
        for (const name of migrations) {
            const sql = read('migrations', name);
            for (const [, fn] of sql.matchAll(/create or replace function (public\.\w+)/gi)) {
                assert.match(sql, new RegExp(`revoke execute on function ${fn.replace('.', '\\.')}\\(.*\\) from public, anon, authenticated`), `${name}: ${fn}`);
            }
        }
    });

    test('every migration from the one-entity rule on creates one entity: a table, its access, a trigger, an index or a function', () => {
        for (const name of migrations.filter(file => file.slice(0, 14) >= ONE_ENTITY_FROM)) {
            const sql = read('migrations', name);
            const creates = sql.match(/\bcreate (?:table|(?:unique )?index|trigger|or replace function|function|policy|view)\b/gi) ?? [];
            const access = /enable row level security|revoke all on table/i.test(sql) ? 1 : 0;
            assert.ok(creates.length + access <= 1, `${name} creates ${creates.length + access} entities; split it into one file each`);
            assert.ok(read('rollbacks', name).trim().length > 0, `${name} has an empty rollback`);
        }
    });

    test('every file opens with one plain sentence that says what it does', () => {
        for (const [folder, names] of [['migrations', migrations], ['rollbacks', rollbacks]] as const) {
            for (const name of names) {
                const [first, second = ''] = read(folder, name).split(/\r?\n/);
                assert.match(first, /^-- [a-z]\S*( \S+)+$/, `${folder}/${name} must open with "-- <what the file does>"`);
                assert.doesNotMatch(first, /\.\s/, `${folder}/${name}: keep the opening comment to one sentence`);
                assert.doesNotMatch(second, /^--/, `${folder}/${name}: keep the opening comment to one line`);
            }
        }
    });

    test('no file uses an em or en dash', () => {
        for (const name of migrations) assert.doesNotMatch(read('migrations', name), /[–—]/, name);
        for (const name of rollbacks) assert.doesNotMatch(read('rollbacks', name), /[–—]/, name);
    });
});