pg-2-json-schema.js

/**
 * PostgreSQL Schema to JSON Schema Converter
 * Exports database schema and converts it to JSON Schema format compatible with AJV
 */

import { toJSON } from 'pg-json-schema-export';
import { writeFileSync } from 'fs';

/**
 * Convert PostgreSQL database schema to JSON Schema
 * @param {Object} dbSchema - The database schema from pg-json-schema-export
 * @returns {Object} JSON Schema compatible with AJV
 */
function convertToJsonSchema(dbSchema) {
  const result = {
    $schema: 'http://json-schema.org/draft-07/schema#',
    definitions: {},
    properties: {}
  };

  // Process each table
  for (const tableName in dbSchema.tables) {
    const table = dbSchema.tables[tableName];
    const tableSchema = {
      type: 'object',
      title: tableName,
      description: table.obj_description || `${tableName} table`,
      required: [],
      properties: {}
    };

    // Process each column
    for (const columnName in table.columns) {
      const column = table.columns[columnName];

      // Add to required array if not nullable
      if (column.is_nullable === false) {
        tableSchema.required.push(columnName);
      }

      // Define property type based on PostgreSQL data type
      const property = {
        description: column.col_description || `${columnName} column`
      };

      // Map PostgreSQL data types to JSON Schema types
      mapDataType(column.data_type, property);

      // Add check constraints if they exist
      addConstraints(dbSchema, tableName, columnName, property);

      // Add property to table schema
      tableSchema.properties[columnName] = property;
    }

    // Add to definitions
    result.definitions[tableName] = tableSchema;

    // Add reference to root properties
    result.properties[tableName] = {
      $ref: `#/definitions/${tableName}`
    };
  }

  return addRelationships(result, dbSchema);
}

/**
 * Maps PostgreSQL data types to JSON Schema types
 * @param {string} dataType - PostgreSQL data type
 * @param {Object} property - JSON Schema property object to modify
 */
function mapDataType(dataType, property) {
  switch (dataType) {
    case 'integer':
    case 'bigint':
    case 'smallint':
      property.type = 'integer';
      break;
    case 'numeric':
    case 'decimal':
    case 'real':
    case 'double precision':
      property.type = 'number';
      break;
    case 'text':
    case 'character':
    case 'character varying':
    case 'varchar':
    case 'char':
      property.type = 'string';
      break;
    case 'boolean':
      property.type = 'boolean';
      break;
    case 'timestamp':
    case 'timestamp with time zone':
    case 'timestamp without time zone':
    case 'date':
      property.type = 'string';
      property.format = 'date-time';
      break;
    case 'jsonb':
    case 'json':
      property.type = 'object';
      property.additionalProperties = true;
      break;
    case 'ARRAY':
      property.type = 'array';
      property.items = { type: 'string' };
      break;
    case 'uuid':
      property.type = 'string';
      property.format = 'uuid';
      break;
    default:
      property.type = 'string';
  }
}

/**
 * Add check constraints to property
 * @param {Object} dbSchema - Database schema
 * @param {string} tableName - Table name
 * @param {string} columnName - Column name
 * @param {Object} property - JSON Schema property to update
 */
function addConstraints(dbSchema, tableName, columnName, property) {
  const constraints = dbSchema.constraints[tableName]?.[columnName] || [];
  for (const constraint of constraints) {
    if (constraint.constraint_type === 'CHECK') {
      if (constraint.check_clause?.includes('>=')) {
        const match = constraint.check_clause.match(/>=\s*\(?([0-9.]+)\)?/);
        if (match) {
          property.minimum = parseFloat(match[1]);
        }
      }
    }
  }
}

/**
 * Add foreign key relationships to schema
 * @param {Object} schema - JSON Schema object
 * @param {Object} dbSchema - Database schema
 * @returns {Object} Updated JSON Schema
 */
function addRelationships(schema, dbSchema) {
  // For each table
  for (const tableName in dbSchema.tables) {
    const tableSchema = schema.definitions[tableName];

    // Check each column for foreign key relationships
    for (const columnName in dbSchema.tables[tableName].columns) {
      const constraints = dbSchema.constraints[tableName]?.[columnName] || [];

      // Find foreign key constraints
      const foreignKeys = constraints.filter(c => c.constraint_type === 'FOREIGN KEY');

      if (foreignKeys.length > 0) {
        for (const fk of foreignKeys) {
          const refTable = fk.referenced_table;
          const refColumn = fk.referenced_column;

          // Add relationship information to property
          if (tableSchema.properties[columnName]) {
            tableSchema.properties[columnName].description =
              `${tableSchema.properties[columnName].description || ''} References ${refTable}.${refColumn}`;

            // Add foreign key metadata
            tableSchema.properties[columnName]['x-foreignKey'] = {
              table: refTable,
              column: refColumn
            };
          }
        }
      }
    }
  }

  return schema;
}

/**
 * Export PostgreSQL schema and convert to JSON Schema
 * @param {Object} dbConfig - Database connection configuration
 * @param {string} schema - Database schema name (default: 'public')
 * @param {string} outputFile - Output file path
 * @returns {Promise<Object>} - JSON Schema object
 */
async function exportAndConvert(dbConfig, schema = 'public', outputFile = null) {
  try {
    // Export database schema
    const dbSchema = await toJSON(dbConfig, schema);

    // Convert to JSON Schema
    const jsonSchema = convertToJsonSchema(dbSchema);

    // Save to file if outputFile is provided
    if (outputFile) {
      // Save original schema
      writeFileSync('schema.json', JSON.stringify(dbSchema, null, 2));

      // Save JSON Schema
      writeFileSync(outputFile, JSON.stringify(jsonSchema, null, 2));
      console.log('Database schema exported to schema.json');
      console.log(`JSON Schema exported to ${outputFile}`);
    }

    return jsonSchema;
  } catch (error) {
    console.error('Error exporting or converting schema:', error);
    throw error;
  }
}

/**
 * Main function
 */
async function main() {
  // Database connection configuration
  const connection = {
    user: process.env.DB_USER || 'postgres',
    password: process.env.DB_PASSWORD || 'postgres',
    host: process.env.DB_HOST || 'localhost',
    port: parseInt(process.env.DB_PORT || '54322'),
    database: process.env.DB_NAME || 'postgres'
  };

  const schemaName = process.argv[2] || 'public';
  const outputFile = process.argv[3] || 'json-schema.json';

  try {
    await exportAndConvert(connection, schemaName, outputFile);
  } catch (error) {
    console.error('Failed to export and convert schema:', error);
    process.exit(1);
  }
}

main();