Skip to content

Instantly share code, notes, and snippets.

@vsviridov
Created September 30, 2023 07:37
Show Gist options
  • Save vsviridov/f09d82768fba96459870b80b9168f914 to your computer and use it in GitHub Desktop.
Save vsviridov/f09d82768fba96459870b80b9168f914 to your computer and use it in GitHub Desktop.
sqlite to @typed-query-builder code generator
export type Brand<K, T> = K & { __brand: T };
import Database from "bun:sqlite";
import assert from "node:assert";
import { VisitorAction, cstVisitor, parse } from "sql-parser-cst";
import {
InterfaceDeclaration,
Project,
StructureKind,
VariableDeclarationKind,
WriterFunction,
} from "ts-morph";
import { toPascalCase } from "js-convert-case";
import { singularize } from "inflection";
// import { Normalize } from "..";
const [, , dbName, targetPath] = process.argv;
assert(dbName, "Usage 'bun run generator.ts <PATH_TO_DB> <PATH_TO_TARGET_FILE>'");
assert(targetPath, "Usage 'bun run generator.ts <PATH_TO_DB> <PATH_TO_TARGET_FILE>'");
const project = new Project();
const sourceFile = project.createSourceFile("__generated__/index.ts");
sourceFile.addStatements("// This file is automatically generated and should not be edited by hand");
sourceFile.addImportDeclarations([
{
kind: StructureKind.ImportDeclaration,
moduleSpecifier: "../../brand.ts",
namedImports: ["Brand"],
isTypeOnly: true,
},
{
kind: StructureKind.ImportDeclaration,
moduleSpecifier: "@typed-query-builder/builder",
namedImports: ["tableFromType"],
},
]);
const connection = new Database(dbName, { readonly: true, create: false });
type Schema = {
tbl_name: string;
name: string;
type: string;
sql: string;
};
const tablesQuery = connection.query<Schema, never[]>(
`SELECT
tbl_name,
name,
type,
sql
FROM
sqlite_schema
WHERE
1=1
AND type = 'table'
AND sql IS NOT NULL
AND name != 'sqlite_sequence'
`,
);
const schema = tablesQuery.all();
const typeMap: Record<string, string> = {
INTEGER: "number",
NUMBER: "number",
TEXT: "string",
BOOL: "boolean",
DATETIME: "unknown",
};
type ColumnMapEntry = {
name: string;
type: string;
nullable?: boolean;
isPrimaryKey?: boolean;
isForeignKey?: boolean;
references?: { tableName: string; column: string };
};
type ColumnMap = Record<string, ColumnMapEntry>;
type TableMapEntry = {
className: string;
tableName: string;
columns: ColumnMap;
primaryIdTypes: Record<string, string>;
isLinkTable?: boolean;
};
type TableMap = Record<string, TableMapEntry>;
const tableMap: TableMap = {};
const enumerateTablesVisitor = cstVisitor({
create_table_stmt(node) {
switch (node.name.type) {
case "identifier": {
const table: TableMapEntry = (tableMap[node.name.name] = {
className: toPascalCase(node.name.name),
tableName: node.name.name,
columns: {},
primaryIdTypes: {},
});
node.columns?.expr.items.forEach((item) => {
switch (item.type) {
case "column_definition": {
const {
name: { name },
dataType,
constraints,
} = item;
if (!dataType) {
return;
}
const { nameKw } = dataType;
if (!Array.isArray(nameKw)) {
const column: ColumnMapEntry = (table.columns[name] = {
name,
type: typeMap[nameKw.name] || "SENTINEL",
});
constraints.forEach((constraint) => {
switch (constraint.type) {
case "constraint_null": {
column.nullable = true;
break;
}
case "constraint_not_null": {
column.nullable = false;
break;
}
case "constraint_primary_key": {
column.isPrimaryKey = true;
break;
}
}
});
}
break;
}
case "constraint_primary_key": {
const { columns } = item;
columns?.expr.items.forEach((item) => {
switch (item.type) {
case "identifier": {
table.columns[item.name].isPrimaryKey = true;
break;
}
}
});
break;
}
case "constraint_foreign_key": {
const { columns, references } = item;
columns?.expr.items.forEach((item) => {
switch (item.type) {
case "identifier": {
table.columns[item.name].isForeignKey = true;
if (references.table.type === "identifier") {
table.columns[item.name].references = {
tableName: references.table.name,
column: references.columns!.expr!.items[0].name,
};
}
break;
}
}
});
break;
}
}
});
}
}
return VisitorAction.SKIP;
},
});
const statements = schema.map((x) => x.sql).join(";\r\n");
const cst = parse(statements, { dialect: "sqlite" });
enumerateTablesVisitor(cst);
function getBrandedType(name: string, tableName: string, type: string) {
return `Brand<${type}, '${singularize(tableName)}:${name}'>`;
}
function addProperty(iface: InterfaceDeclaration, name: string, type: string, isReadonly = false) {
iface.addProperty({
name,
type,
kind: StructureKind.PropertySignature,
isReadonly,
});
}
// First pass
Object.values(tableMap).forEach((table) => {
table.isLinkTable = Object.values(table.columns).every((column) => column.isForeignKey);
Object.values(table.columns).forEach((column) => {
if (column.isPrimaryKey) {
const brandedTypeName = `${singularize(table.className)}${toPascalCase(column.name)}`;
if (!column.isForeignKey) {
const brandedType = getBrandedType(column.name, table.tableName, column.type);
table.primaryIdTypes[column.name] = brandedTypeName;
sourceFile.addTypeAlias({ name: brandedTypeName, isExported: true, type: brandedType });
}
}
});
});
const tableFromTypeWriter: (table: TableMapEntry) => WriterFunction = (table) => (writer) => {
writer.write(`tableFromType<${table.className}DTO>()(`);
writer.inlineBlock(() => {
const fields = Object.keys(table.columns)
.map((x) => `'${x}'`)
.join(", ");
writer.writeLine(`name: '${table.tableName}',`);
writer.writeLine(`fields: [${fields}],`);
});
writer.write(")");
};
// Second pass
Object.values(tableMap).forEach((table) => {
const iface = sourceFile.addInterface({
name: `${table.className}DTO`,
kind: StructureKind.Interface,
isExported: true,
});
sourceFile.addVariableStatement({
declarationKind: VariableDeclarationKind.Const,
isExported: true,
declarations: [{ name: table.className, initializer: tableFromTypeWriter(table) }],
});
Object.values(table.columns).forEach((column) => {
if (column.isPrimaryKey) {
if (column.isForeignKey) {
addProperty(
iface,
column.name,
tableMap[column.references!.tableName].primaryIdTypes[column.references!.column],
true,
);
} else {
addProperty(iface, column.name, table.primaryIdTypes[column.name], true);
}
} else if (column.isForeignKey) {
addProperty(iface, column.name, tableMap[column.references!.tableName].primaryIdTypes[column.references!.column]);
} else {
addProperty(iface, column.name, column.type);
}
});
});
sourceFile.formatText();
await Bun.write(targetPath, sourceFile.getText());
console.log("Done");
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment