sqlite to @typed-query-builder code generator

brand.ts

export type Brand<K, T> = K & { __brand: T };

generator.ts

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");