Parse PostgreSQL schema, and output types

parse-postgresql-to-typescript.ts

import { deparse, parse } from "npm:pgsql-parser";
import type { Node, ParseResult } from "npm:@pgsql/types";

const schemaSql = `
-- PostgreSQL schema
CREATE TYPE my_enum_example AS ENUM ('good', 'bad', 'ugly', 'dont know');

CREATE TABLE
    "various_types" (
        "bigserial" bigserial primary key,
        "serial" serial,
        "test_int32" integer,
        "test_int64" bigint,
        "test_bigint" numeric,
        "test_float32" real,
        "test_float64" double precision,
        "test_decimal" decimal(10, 2),
        "test_uuid" uuid,
        "test_string" text,
        "test_varchar" varchar(255),
        "test_boolean" boolean,
        "test_datetime" timestamptz,
        "test_datetime2" timestamp,
        "test_datepart" date,
        "test_timepart" time,
        "test_jsonb" jsonb,
        "test_json" json,
        "test_enum" my_enum_example,
        "test_array" text[],
        "text_array_ints" integer[],
        "test_bytea" bytea,
        "test_xml" xml,
        "test_point" point,
        "test_circle" circle
    );

CREATE TABLE
    "foo" (
        "test_pk" uuid primary key default gen_random_uuid (),
        "test_rowversion" bigint default 1,
        "test_concurrencyStamp" uuid default gen_random_uuid (),
        "test_updated_at" timestamptz default current_timestamp,
        "test_created_at" timestamptz default current_timestamp
    );
`;

// https://doxygen.postgresql.org/parsenodes_8h.html#aa2da3f289480b73dbcaccf0404657c65
type FKCONSTR_ACTION =
    | "c" // FKCONSTR_ACTION_CASCADE
    | "a" // FKCONSTR_ACTION_NOACTION
    | "r" // FKCONSTR_ACTION_RESTRICT
    | "d" // FKCONSTR_ACTION_SETDEFAULT
    | "n"; // FKCONSTR_ACTION_SETNULL

type FKCONSTR_MATCHTYPE =
    | "f" // FKCONSTR_MATCH_FULL
    | "p" // FKCONSTR_MATCH_PARTIAL
    | "s"; // FKCONSTR_MATCH_SIMPLE

// https://pglast.readthedocs.io/en/v3/parsenodes.html#pglast.enums.parsenodes.pglast.enums.parsenodes.ConstrType

type Column = {
    name: string;
    type: string;
    array: boolean;
    notnull: boolean;
    primarykey: boolean;
    unique: boolean;
    foreignKey?: {
        relname: string;
        colname: string;
        updateAction: FKCONSTR_ACTION;
        deleteAction: FKCONSTR_ACTION;
        matchType: FKCONSTR_MATCHTYPE;
    };
};

type Table = {
    name: string;
    columns: Column[];
};

type EnumTypes = {
    [key: string]: string[];
};

function sval(node: Node | undefined): string {
    if (node && "String" in node && node.String.sval) {
        return node.String.sval;
    }
    throw new Error("Node does not contain a String property.");
}

export async function parseTables(str: string) {
    const enums: EnumTypes = {};
    const tables: Table[] = [];
    const tableParse = (await parse(str)) as ParseResult;
    if (!tableParse.stmts) {
        throw new Error("No statements found in the parsed result.");
    }

    for (const { stmt } of tableParse.stmts) {
        if (!stmt) {
            console.warn("No statement found in the current iteration.");
            continue;
        }

        if ("CreateStmt" in stmt) {
            const {
                CreateStmt: { tableElts, relation },
            } = stmt;

            const tableName = relation?.relname;
            if (!tableName) {
                console.warn("No table name found in CreateStmt.");
                continue;
            }

            if (!tableElts) {
                console.warn("No table elements found in CreateStmt.");
                continue;
            }
            const table: Table = {
                name: tableName,
                columns: [],
            };

            // Iterate columns
            for (const node of tableElts || []) {
                if ("ColumnDef" in node) {
                    const {
                        ColumnDef: { colname, typeName, constraints },
                    } = node;
                    const colName = colname;
                    const typeNames = typeName?.names;
                    let notnull = false;
                    let primarykey = false;
                    let unique = false;
                    let array = false;
                    let foreignkeyRelation = undefined as
                        | {
                              relname: string;
                              colname: string;
                              updateAction: FKCONSTR_ACTION;
                              deleteAction: FKCONSTR_ACTION;
                              matchType: FKCONSTR_MATCHTYPE;
                          }
                        | undefined;

                    if (!colName) {
                        console.warn("No column name found in ColumnDef.");
                        continue;
                    }
                    if (!typeNames) {
                        console.warn("No type names found in ColumnDef.");
                        continue;
                    }
                    const type = typeNames
                        .map(sval)
                        .filter((name) => name !== "pg_catalog")
                        .join(".");

                    if (colName.startsWith("test_array")) {
                        console.dir(node, { depth: 9999 });
                    }

                    if (typeName.arrayBounds && typeName.arrayBounds.length > 0) {
                        array = true;
                    }

                    for (const constraint of constraints || []) {
                        if ("Constraint" in constraint) {
                            const {
                                Constraint: {
                                    contype,
                                    pktable,
                                    pk_attrs,
                                    fk_matchtype,
                                    fk_del_action,
                                    fk_upd_action,
                                },
                            } = constraint;
                            if (!contype) {
                                console.warn("No constraint type found.");
                                continue;
                            } else if (contype === "CONSTR_NOTNULL") {
                                notnull = true;
                            } else if (contype === "CONSTR_PRIMARY") {
                                primarykey = true;
                            } else if (contype === "CONSTR_UNIQUE") {
                                unique = true;
                            } else if (contype === "CONSTR_FOREIGN") {
                                if (!pktable) {
                                    console.warn("Foreign key constraint missing pktable.");
                                    continue;
                                }
                                if (!pk_attrs) {
                                    console.warn("Foreign key constraint missing pk_attrs.");
                                    continue;
                                }

                                foreignkeyRelation = {
                                    relname: pktable?.relname || "",
                                    colname: sval(pk_attrs[0]),
                                    updateAction: fk_upd_action as FKCONSTR_ACTION,
                                    deleteAction: fk_del_action as FKCONSTR_ACTION,
                                    matchType: fk_matchtype as FKCONSTR_MATCHTYPE,
                                };
                            }
                        }
                    }

                    table.columns.push({
                        name: colName,
                        type,
                        array,
                        notnull,
                        primarykey,
                        unique,
                        foreignKey: foreignkeyRelation,
                    });
                }
            }
            tables.push(table);
        } else if ("CreateEnumStmt" in stmt) {
            const {
                CreateEnumStmt: { typeName, vals },
            } = stmt;

            if (!typeName || !vals) {
                console.warn("No values or type name found for CreateEnumStmt.");
                continue;
            }

            // Extract enum type name
            const enumTypeName = typeName?.map(sval).join(".");
            if (!enumTypeName) {
                console.warn("No enum type name found in CreateEnumStmt.");
                continue;
            }

            // Extract enum values
            const enumValues = vals?.map(sval);
            if (!enumValues || enumValues.length === 0) {
                console.warn("No enum values found in CreateEnumStmt.");
                continue;
            }

            enums[enumTypeName] = enumValues;
        }
    }

    return { tables, enums };
}

// It should use `type Example = "a" | "b" | "c";` format for TypeScript enums.
function generateTypeScriptEnums(enums: EnumTypes): string {
    let result = "";
    for (const [enumName, values] of Object.entries(enums)) {
        const formattedValues = values.map((v) => `"${v}"`).join(" | ");
        result += `type ${enumName} = ${formattedValues};\n`;
    }
    return result;
}

function generateTypeScriptInterfaces(tables: Table[]): string {
    let result = "";
    for (const table of tables) {
        result += `interface ${table.name} {\n`;
        for (const column of table.columns) {
            const type = column.array ? `${column.type}[]` : column.type;
            result += `  ${column.name}: ${type};\n`;
        }
        result += "}\n\n";
    }
    return result;
}

if (import.meta.main) {
    const result = await parseTables(schemaSql);
    console.log("Tables:", result.tables);
    console.log("Enums:", result.enums);
    console.log("TypeScript Enums:\n", generateTypeScriptEnums(result.enums));
    console.log("TypeScript Interfaces:\n", generateTypeScriptInterfaces(result.tables));
    // Uncomment the next
}