Skip to content

Instantly share code, notes, and snippets.

@virtuallyunknown
Last active July 23, 2024 07:40
Show Gist options
  • Save virtuallyunknown/0c9d603561e60cf37bfe5c47985a614a to your computer and use it in GitHub Desktop.
Save virtuallyunknown/0c9d603561e60cf37bfe5c47985a614a to your computer and use it in GitHub Desktop.
/* 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;
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();
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment