create postgres db schema from swagger schema

README.md

janky way to generate db schema from swagger

run node schema.js

this will create output.sql

you will need to massage a few things:

• there are duplicate tables like rsv_address and rsv_revenue_and_balances
• address needs to be named to two separate things
• revenue is the same table, delete the second instance and make sure it has two foreign key to point to both potential parents

schema.mjs

import SwaggerParser from "swagger-parser";
import fs from "fs";
import { snakeCase } from "snake-case";

const parseSwaggerAndGenerateSchema = async (swaggerFile) => {
  const api = await SwaggerParser.parse(swaggerFile);
  const definitions = api.definitions || api.components.schemas;

  if (!definitions) {
    logger.error("No definitions found in the Swagger file.");
    return;
  }

  // only codegen reservationInfoType
  for (const [name, schema] of Object.entries({
    reservation: definitions.reservationInfoType,
  })) {
    await createTableForSchema(snakeCase(name), schema);
  }
};

let sqlStatements = [];
let foreignKeys = [];
let junctionTables = [];

const createTableForSchema = async (
  schemaName,
  schema,
  parentSchemaName = undefined,
) => {
  let createTableQuery = `CREATE TABLE rsv_${schemaName} (\n`;
  let columns = [];

  if (parentSchemaName) {
    columns.push(`rsv_${parentSchemaName}_id UUID`);
    foreignKeys.push(
      `ALTER TABLE rsv_${schemaName} ADD FOREIGN KEY (rsv_${parentSchemaName}_id) REFERENCES rsv_${parentSchemaName}(id)`,
    );
  }

  columns.push("id UUID PRIMARY KEY");

  for (const [propName, propSchema] of Object.entries(schema.properties)) {
    const colName = propName === "id" ? "rsv_id" : snakeCase(propName);

    if (
      propSchema.description &&
      propSchema.description.includes("primary-key")
    ) {
      columns.push(`-- ${colName} is primary key`);
    }

    if (propSchema.type === "object") {
      const ENABLE_DENORMALIZATION = true;
      // If the object has no properties that are objects, we can just store it inline
      // Otherwise, we create a separate table for the nested object

      const hasNestedObjects = Object.values(propSchema.properties || {}).some(
        (nestedPropSchema) => nestedPropSchema.type === "object",
      );

      if (hasNestedObjects) {
        // Recursively create a table for the nested object
        await createTableForSchema(colName, propSchema, schemaName);
        columns.push(`rsv_${colName}_id UUID`);
        foreignKeys.push(
          `ALTER TABLE rsv_${schemaName} ADD FOREIGN KEY (rsv_${colName}_id) REFERENCES rsv_${colName}(id) ON DELETE CASCADE`,
        );
      } else {
        // Store inline
        for (const [nestedPropName, nestedPropSchema] of Object.entries(
          propSchema.properties,
        )) {
          const nestedColName = `${colName}_${snakeCase(nestedPropName)}`;
          const type = getPostgresType(nestedColName, nestedPropSchema);
          columns.push(`${nestedColName} ${type}`);
        }
      }
    } else if (propSchema.type === "array") {
      if (propSchema.items.type === "object") {
        junctionTables.push(generateJunctionTableSQL(schemaName, colName));
        await createTableForSchema(colName, propSchema.items);
      } else {
        console.log(propSchema.items);
        const pgType = getPostgresType(colName, propSchema.items);
        columns.push(`${colName} ${pgType}[]`);
      }
    } else {
      const type = getPostgresType(colName, propSchema);
      columns.push(`${colName} ${type}`);
    }
  }

  createTableQuery += columns.join(",\n  ");

  createTableQuery += "\n);";

  sqlStatements = [...sqlStatements, createTableQuery];
};

// Helper function to generate SQL for junction tables
function generateJunctionTableSQL(parentTableName, childTableName) {
  return `CREATE TABLE rsv_${parentTableName}_${childTableName} (
    rsv_${parentTableName}_id UUID REFERENCES rsv_${parentTableName}(id),
    rsv_${childTableName}_id UUID REFERENCES rsv_${childTableName}(id)
  );`;
}

const generateCreateEnumSQL = (colName, schema) => {
  const enumTypeName = colName;
  const values = schema.enum.map((value) => `'${value}'`).join(", ");
  return `CREATE TYPE rsv_${enumTypeName}_enum AS ENUM (${values});`;
};

const getPostgresType = (colName, schema) => {
  if (schema.enum) {
    const createEnumTypeSQL = generateCreateEnumSQL(colName, schema);
    sqlStatements.push(createEnumTypeSQL);

    // Assuming the enum type name is derived from the description or a predefined naming convention
    const enumTypeName = colName;
    return `rsv_${enumTypeName}_enum`;
  }

  if (schema.type === "string") {
    if (schema.format === "date") {
      return "DATE";
    }
    if (schema.format === "date-time") {
      return "TIMESTAMP";
    }
    if (schema.format === "uuid") {
      return "UUID";
    }
    if (schema.maxLength) {
      return `VARCHAR(${schema.maxLength})`;
    }
    return "TEXT";
  }

  if (schema.type === "integer") {
    if (schema.format === "int32") {
      return "INTEGER";
    }
    if (schema.format === "int64") {
      return "BIGINT";
    }
    return "INTEGER";
  }

  if (schema.type === "number") {
    if (schema.format === "float") {
      return "REAL";
    }
    if (schema.format === "double") {
      return "DOUBLE PRECISION";
    }
    return "NUMERIC";
  }

  if (schema.type === "boolean") {
    return "BOOLEAN";
  }

  // if (schema.type === "array") {
  //   return "INTEGER"; // Assuming array refers to a foreign key relationship
  // }

  // if (schema.type === "object") {
  //   return "JSONB"; // For complex objects, you might store them as JSONB
  // }

  return "TEXT";
};

// create deref.json: swagger-cli bundle -r -o deref.json openapi/rsv.json
parseSwaggerAndGenerateSchema("deref.json")
  .then(() => {
    console.log(sqlStatements); // For debugging purposes

    fs.writeFileSync(
      `schema.sql`,
      [
        Array.from(new Set(sqlStatements)).join("\n\n\n"),
        Array.from(new Set(junctionTables)).join("\n"),
        Array.from(new Set(foreignKeys)).join(";\n"),
      ].join("\n\n\n"),
    );
    console.log(`generated schema.sql`);
  })
  .catch((err) => logger.error("Error generating schema:", err));