db-types.ts

/* File generated automatically, do not edit. */

import type { ColumnType, Selectable, Insertable, Updateable } from 'kysely';

export type DBEventType = 
    'goal' |
    'own-goal' |
    'penalty-goal' |
    'penalty-miss' |
    'yellow-card' |
    'red-card' |
    'yellow-red-card' |
    'substitution';

export type DBStageType = 
    '1st-preliminary-round' |
    'qualifying-round' |
    '1st-round' |
    '2nd-round' |
    '3rd-round' |
    'group-stage' |
    'intermediate-stage' |
    'round-of-16' |
    'quarter-finals' |
    'semi-finals' |
    'final';

export type DBMatchStatus = 
    'ft' |
    'aet' |
    'pen' |
    'uncontested';

export type DBTeamsId = number;

export interface DBTeams {
    id: ColumnType<number, number, number | null>;
    name: ColumnType<string, string, string | null>;
};

export type DBTeamsSelectable = Selectable<DBTeams>;
export type DBTeamsInsertable = Insertable<DBTeams>;
export type DBTeamsUpdateable = Updateable<DBTeams>;

export type DBPlayersId = number;

export interface DBPlayers {
    id: ColumnType<number, number, number | null>;
    name: ColumnType<string, string, string | null>;
};

export type DBPlayersSelectable = Selectable<DBPlayers>;
export type DBPlayersInsertable = Insertable<DBPlayers>;
export type DBPlayersUpdateable = Updateable<DBPlayers>;

export type DBCoachesId = number;

export interface DBCoaches {
    id: ColumnType<number, number, number | null>;
    name: ColumnType<string, string, string | null>;
};

export type DBCoachesSelectable = Selectable<DBCoaches>;
export type DBCoachesInsertable = Insertable<DBCoaches>;
export type DBCoachesUpdateable = Updateable<DBCoaches>;

export type DBLeaguesId = string;

export interface DBLeagues {
    id: ColumnType<string, string, string | null>;
    name: ColumnType<string, string, string | null>;
    logo: ColumnType<string | null, string | null, string | null>;
};

export type DBLeaguesSelectable = Selectable<DBLeagues>;
export type DBLeaguesInsertable = Insertable<DBLeagues>;
export type DBLeaguesUpdateable = Updateable<DBLeagues>;

export type DBSeasonsId = string;

export interface DBSeasons {
    id: ColumnType<string, string, string | null>;
    leagueId: ColumnType<DBLeaguesId, DBLeaguesId, DBLeaguesId | null>;
    year: ColumnType<number, number, number | null>;
};

export type DBSeasonsSelectable = Selectable<DBSeasons>;
export type DBSeasonsInsertable = Insertable<DBSeasons>;
export type DBSeasonsUpdateable = Updateable<DBSeasons>;

export type DBStagesId = string;

export interface DBStages {
    id: ColumnType<string, string, string | null>;
    leagueId: ColumnType<DBLeaguesId, DBLeaguesId, DBLeaguesId | null>;
    seasonId: ColumnType<DBSeasonsId, DBSeasonsId, DBSeasonsId | null>;
    type: ColumnType<DBStageType, DBStageType, DBStageType | null>;
    sortOrder: ColumnType<number, number, number | null>;
};

export type DBStagesSelectable = Selectable<DBStages>;
export type DBStagesInsertable = Insertable<DBStages>;
export type DBStagesUpdateable = Updateable<DBStages>;

export type DBMatchesId = number;

export interface DBMatches {
    id: ColumnType<number, number, number | null>;
    date: ColumnType<Date, Date, Date | null>;
    status: ColumnType<DBMatchStatus, DBMatchStatus, DBMatchStatus | null>;
    leagueId: ColumnType<DBLeaguesId, DBLeaguesId, DBLeaguesId | null>;
    seasonId: ColumnType<DBSeasonsId, DBSeasonsId, DBSeasonsId | null>;
    stageId: ColumnType<DBStagesId, DBStagesId, DBStagesId | null>;
    homeTeamId: ColumnType<DBTeamsId, DBTeamsId, DBTeamsId | null>;
    awayTeamId: ColumnType<DBTeamsId, DBTeamsId, DBTeamsId | null>;
    homeTeamScore: ColumnType<number, number, number | null>;
    awayTeamScore: ColumnType<number, number, number | null>;
    homeTeamPenScore: ColumnType<number | null, number | null, number | null>;
    awayTeamPenScore: ColumnType<number | null, number | null, number | null>;
    homeTeamCoachId: ColumnType<DBCoachesId | null, DBCoachesId | null, DBCoachesId | null>;
    awayTeamCoachId: ColumnType<DBCoachesId | null, DBCoachesId | null, DBCoachesId | null>;
};

export type DBMatchesSelectable = Selectable<DBMatches>;
export type DBMatchesInsertable = Insertable<DBMatches>;
export type DBMatchesUpdateable = Updateable<DBMatches>;

export type DBEventsId = string;

export interface DBEvents {
    id: ColumnType<string, string, string | null>;
    matchId: ColumnType<DBMatchesId, DBMatchesId, DBMatchesId | null>;
    type: ColumnType<DBEventType, DBEventType, DBEventType | null>;
    teamId: ColumnType<DBTeamsId, DBTeamsId, DBTeamsId | null>;
    minute: ColumnType<number | null, number | null, number | null>;
    extraMinute: ColumnType<number | null, number | null, number | null>;
    playerId: ColumnType<DBPlayersId, DBPlayersId, DBPlayersId | null>;
    relatedPlayerId: ColumnType<DBPlayersId | null, DBPlayersId | null, DBPlayersId | null>;
};

export type DBEventsSelectable = Selectable<DBEvents>;
export type DBEventsInsertable = Insertable<DBEvents>;
export type DBEventsUpdateable = Updateable<DBEvents>;



export interface DBLineups {
    matchId: ColumnType<DBMatchesId, DBMatchesId, DBMatchesId | null>;
    teamId: ColumnType<DBTeamsId, DBTeamsId, DBTeamsId | null>;
    playerId: ColumnType<DBPlayersId, DBPlayersId, DBPlayersId | null>;
};

export type DBLineupsSelectable = Selectable<DBLineups>;
export type DBLineupsInsertable = Insertable<DBLineups>;
export type DBLineupsUpdateable = Updateable<DBLineups>;



export interface DBSubstitutes {
    matchId: ColumnType<DBMatchesId, DBMatchesId, DBMatchesId | null>;
    teamId: ColumnType<DBTeamsId, DBTeamsId, DBTeamsId | null>;
    playerId: ColumnType<DBPlayersId, DBPlayersId, DBPlayersId | null>;
};

export type DBSubstitutesSelectable = Selectable<DBSubstitutes>;
export type DBSubstitutesInsertable = Insertable<DBSubstitutes>;
export type DBSubstitutesUpdateable = Updateable<DBSubstitutes>;

export type DBColumnNames = { readonly [K in keyof DB]: ReadonlyArray<keyof DB[K]>; };

export interface DB {
    teams: DBTeams;
    players: DBPlayers;
    coaches: DBCoaches;
    leagues: DBLeagues;
    seasons: DBSeasons;
    stages: DBStages;
    matches: DBMatches;
    events: DBEvents;
    lineups: DBLineups;
    substitutes: DBSubstitutes;
};

export const dbColumnNames: DBColumnNames = {
    teams: ['id', 'name'],
    players: ['id', 'name'],
    coaches: ['id', 'name'],
    leagues: ['id', 'name', 'logo'],
    seasons: ['id', 'leagueId', 'year'],
    stages: ['id', 'leagueId', 'seasonId', 'type', 'sortOrder'],
    matches: ['id', 'date', 'status', 'leagueId', 'seasonId', 'stageId', 'homeTeamId', 'awayTeamId', 'homeTeamScore', 'awayTeamScore', 'homeTeamPenScore', 'awayTeamPenScore', 'homeTeamCoachId', 'awayTeamCoachId'],
    events: ['id', 'matchId', 'type', 'teamId', 'minute', 'extraMinute', 'playerId', 'relatedPlayerId'],
    lineups: ['matchId', 'teamId', 'playerId'],
    substitutes: ['matchId', 'teamId', 'playerId'],
} as const;

kysely-db-typegen.js

import { recase } from '@kristiandupont/recase';
import extractPGSchema from 'extract-pg-schema';
import { readFile, writeFile } from 'node:fs/promises';
import pg from 'pg';
const { extractSchemas } = extractPGSchema;

export class DbTypegen {
    #connection;
    #outFile;
    #preRunSqlFiles;
    #prefix;

    /**
     * Represents a database type configuration.
     * @constructor
     * @param {{
     *   connection: {
     *         user: string,
     *         password: string,
     *         host: string,
     *         port: number,
     *         database: string
     *     };
     *     preRunSqlFiles: string[];
     *     outFile: string;
     *     prefix?: string;
     * }}
     */
    constructor({ connection, preRunSqlFiles, outFile, prefix = 'DB' }) {
        this.#connection = connection;
        this.#preRunSqlFiles = preRunSqlFiles ?? [];
        this.#outFile = outFile;
        this.#prefix = prefix;
    }

    #toPascalCase(name) {
        return recase('snake', 'pascal')(name);
    }

    #toCamelCase(name) {
        return recase('snake', 'camel')(name);
    }

    #unionize(values) {
        return values.map(v => `    '${v}'`).join(' |\n')
    }

    async #getPublicSchema() {
        const schemas = await extractSchemas(this.#connection);

        if (!schemas.public) {
            throw new Error('No public schema found');
        }

        return schemas.public;
    }

    async #mapSchema() {
        const schema = await this.#getPublicSchema();

        return ({
            enums: schema.enums.map(e => ({
                name: this.#getEntityName(e.name),
                values: e.values
            })),
            tables: schema.tables.map(t => ({
                entityName: this.#getEntityName(t.name),
                propName: this.#toCamelCase(t.name),
                kind: 'table',
                columns: t.columns.map(c => this.#mapColumn(c))
            })),
            views: schema.views.map(v => ({
                entityName: this.#getEntityName(v.name),
                propName: this.#toCamelCase(v.name),
                kind: 'view',
                columns: v.columns.map(c => this.#mapColumn(c))
            })),
            materializedViews: schema.materializedViews.map(mv => ({
                entityName: this.#getEntityName(mv.name),
                propName: this.#toCamelCase(mv.name),
                kind: 'mView',
                columns: mv.columns.map(c => this.#mapColumn(c))
            }))
        })
    }

    #mapColumn(column) {
        if (column.type.kind !== 'base' && column.type.kind !== 'enum') {
            throw new Error(`Unsupported type kind: ${column.type.kind}`);
        }

        if (column?.references?.length > 1) {
            throw new Error('Multiple references not supported');
        }

        return ({
            name: this.#toCamelCase(column.name),
            type: this.#getColumnType(column),
            isPrimaryKey: column.isPrimaryKey,
            isNullable: column.isNullable,
            isUpdatable: column.isUpdatable,
            isArray: column.isArray,
            defaultValue: column.defaultValue
        })
    }

    #getColumnType(column) {
        if (column?.references?.length === 1) {
            return this.#getEntityName(column.references[0].tableName, column.references[0].columnName);
        }
        if (column.type.kind === 'enum') {
            return this.#getEnumNameFromType(column.type.fullName);
        }
        if (column.type.kind === 'base') {
            return this.#getBaseName(column);
        }
        throw new Error(`Unsupported type kind: ${column.type.kind}`);
    }

    #getEntityName(tableName, columName) {
        return columName
            ? `${this.#prefix}${this.#toPascalCase(`${tableName}_${columName}`)}`
            : `${this.#prefix}${this.#toPascalCase(tableName)}`
    }

    #getEnumNameFromType(pgEnum) {
        const type = pgEnum.split('.').at(1);
        return `${this.#prefix}${this.#toPascalCase(type)}`;
    }

    #getBaseName(column) {
        switch (column.type.fullName) {
            case 'pg_catalog.int2': return `number${column.isArray ? '[]' : ''}`;
            case 'pg_catalog.int4': return `number${column.isArray ? '[]' : ''}`;
            case 'pg_catalog.int8': return `number${column.isArray ? '[]' : ''}`;
            case 'pg_catalog.numeric': return `string${column.isArray ? '[]' : ''}`;
            case 'pg_catalog.uuid': return `string${column.isArray ? '[]' : ''}`;
            case 'pg_catalog.text': return `string${column.isArray ? '[]' : ''}`;
            case 'pg_catalog.char': return `string${column.isArray ? '[]' : ''}`;
            case 'pg_catalog.varchar': return `string${column.isArray ? '[]' : ''}`;
            case 'pg_catalog.bool': return `boolean${column.isArray ? '[]' : ''}`;
            case 'pg_catalog.timestamp': return `Date${column.isArray ? '[]' : ''}`;
            case 'pg_catalog.timestamptz': return `Date${column.isArray ? '[]' : ''}`;
            case 'pg_catalog.json': return `object${column.isArray ? '[]' : ''}`;
            case 'pg_catalog.jsonb': return `object${column.isArray ? '[]' : ''}`;
            default:
                throw new Error(`Unsupported base type: ${column.type.name}`);
        }
    }

    #getEnumLine(enumColumn) {
        const values = this.#unionize(enumColumn.values);
        return `export type ${enumColumn.name} = \n${values};`;
    }

    #getIdentifierLine(table) {
        const primaryKeyColumn = table.columns.find(c => c.isPrimaryKey);

        if (!primaryKeyColumn) {
            return '';
        }

        const name = this.#toPascalCase(`${table.entityName}_${primaryKeyColumn.name}`);
        return `export type ${name} = ${primaryKeyColumn.type};`;
    }

    #getColumnTypeLine(table, column) {
        if (table.kind === 'table') {
            const selectable = column.isNullable ? `${column.type} | null` : column.type;
            const insertable = (column.isNullable || column.defaultValue) ? `${column.type} | null` : column.type;
            const updateable = column.isUpdatable ? `${column.type} | null` : 'never';

            return `ColumnType<${selectable}, ${insertable}, ${updateable}>`;
        }

        const selectable = column.isNullable ? `${column.type} | null` : column.type;
        return `ColumnType<${selectable}, never, never>`;
    }

    #getTableLine(tableLike) {
        const props = [];

        for (const column of tableLike.columns) {
            props.push(`    ${column.name}: ${this.#getColumnTypeLine(tableLike, column)};`);
        }

        return `export interface ${tableLike.entityName} {\n${props.join('\n')}\n};`
    }

    #getKyselyExportsLine(tableLike) {
        if (tableLike.kind === 'table') {
            return [
                `export type ${tableLike.entityName}Selectable = Selectable<${tableLike.entityName}>;`,
                `export type ${tableLike.entityName}Insertable = Insertable<${tableLike.entityName}>;`,
                `export type ${tableLike.entityName}Updateable = Updateable<${tableLike.entityName}>;`,
            ].join('\n');
        }

        return `export type ${tableLike.entityName}Selectable = Selectable<${tableLike.entityName}>;`
    }

    #getDatabaseLine(tables, views, materializedViews) {
        const props = [];

        for (const table of tables) {
            props.push(`    ${table.propName}: ${table.entityName};`);
        }

        for (const view of views) {
            props.push(`    ${view.propName}: ${view.entityName};`);
        }

        for (const mView of materializedViews) {
            props.push(`    ${mView.propName}: ${mView.entityName};`);
        }

        return `export interface DB {\n${props.join('\n')}\n};`
    }

    #getDbColumnNamesLine(tables, views, materializedViews) {
        /**
         * here it probably doesn't make sense to export views and
         * materielized views, since they are not updatable
         */
        const props = [];

        for (const table of tables) {
            props.push(`    ${table.propName}: [${table.columns.map(c => `'${c.name}'`).join(', ')}],`);
        }
        for (const view of views) {
            props.push(`    ${view.propName}: [${view.columns.map(c => `'${c.name}'`).join(', ')}],`);
        }
        for (const mView of materializedViews) {
            props.push(`    ${mView.propName}: [${mView.columns.map(c => `'${c.name}'`).join(', ')}],`);
        }

        return `export const dbColumnNames: DBColumnNames = {\n${props.join('\n')}\n} as const;`
    }


    async generate() {
        if (this.#preRunSqlFiles.length > 0) {
            const { Client } = pg;
            const client = new Client(this.#connection);
            await client.connect();

            const files = await Promise.all(this.#preRunSqlFiles.map(file => readFile(file, 'utf-8')));

            for (const file of files) {
                await client.query(file);
            }

            await client.end();
        }

        const { enums, tables, views, materializedViews } = await this.#mapSchema();
        const header = `/* File generated automatically, do not edit. */`;
        const imports = `import type { ColumnType, Selectable, Insertable, Updateable } from 'kysely';`;
        const exports = `export type DBColumnNames = { readonly [K in keyof DB]: ReadonlyArray<keyof DB[K]>; };`

        const result = {
            enums: [],
            tables: [],
            views: [],
            mViews: [],
            database: '',
            columnNames: ''
        }

        for (const pgEnum of enums) {
            result.enums.push(this.#getEnumLine(pgEnum));
        }

        for (const table of tables) {
            result.tables.push(this.#getIdentifierLine(table));
            result.tables.push(this.#getTableLine(table));
            result.tables.push(this.#getKyselyExportsLine(table));
        }

        for (const view of views) {
            result.views.push(this.#getTableLine(view));
            result.views.push(this.#getKyselyExportsLine(view));
        }

        for (const mView of materializedViews) {
            result.mViews.push(this.#getTableLine(mView));
            result.mViews.push(this.#getKyselyExportsLine(mView));
        }

        result.database = this.#getDatabaseLine(tables, views, materializedViews);
        result.columnNames = this.#getDbColumnNamesLine(tables, views, materializedViews);

        const output = [
            header,
            imports,
            ...result.enums,
            ...result.tables,
            ...result.views,
            ...result.mViews,
            exports,
            result.database,
            result.columnNames
        ].join('\n\n');

        await writeFile(this.#outFile, output, { encoding: 'utf-8' });
    }
}

const typegen = new DbTypegen({
    connection: {
        user: 'user',
        password: 'password',
        database: 'database-name',
        host: 'localhost',
        port: 5432,
    },
    outFile: 'src/db/db-types.ts',
});

await typegen.generate();