import { promises , constants } from 'fs' ; import { join } from 'path' ; import { JSONSchema7 , JSONSchema7Definition } from 'json-schema' ; import jsonfile from 'jsonfile' ; import mkdirp from 'mkdirp' ; import pgStructure , { Column , Entity , Schema } from 'pg-structure' ; import { IConfiguration } from './config' ; export class SchemaConverter { param { IConfiguration } config constructor ( private config ) { } returns { Promise < undefined > } public async checkConfiguration ( ) { if ( ! this . config ) { throw new Error ( 'No configuration supplied' ) ; } if ( ! this . config . pg ?. host || ! this . config . pg ?. database || ! this . config . pg ?. user ) { throw new Error ( 'Missing PGSQL config' ) ; } if ( this . config . output ?. outDir ) { await mkdirp ( this . config . output . outDir ) ; try { await promises . access ( this . config . output . outDir , constants . W_OK ) ; } catch ( err ) { console . error ( err ) ; throw new Error ( `Cannot write to ${ this . config . output . outDir }` ) ; } } return ; } returns { ( Promise < JSONSchema7 [ ] > ) } public async convert ( ) { await this . checkConfiguration ( ) ; console . warn ( 'Connecting to database...' ) ; const dbSchemas = this . config . input ?. schemas || [ 'public' ] ; const db = await pgStructure ( { database : this . config . pg . database , host : this . config . pg . host , port : this . config . pg . port , user : this . config . pg . user , password : this . config . pg . password , } , { includeSchemas : dbSchemas , includeSystemSchemas : true , } ) ; const includedEntities = this . config . input ?. include || [ ] ; const excludedEntities = this . config . input ?. exclude || [ ] ; const outputFolder = this . config . output ?. outDir ; const indentSpaces = this . config . output ?. indentSpaces === undefined ? 2 : this . config . output . indentSpaces ; const defaultDescription = this . config . output ?. defaultDescription || `${ new Date ( ) }` ; const additionalProperties = this . config . output ?. additionalProperties === true ; const baseUrl = ( this . config . output ?. baseUrl || '' ) . replace ( /\/$/ , '' ) ; const outputSchemas = [ ] ; for ( const dbSchema of dbSchemas ) { console . warn ( `Processing schema ${ dbSchema }` ) ; const schema = db . get ( dbSchema ) as Schema ; const schemaName = schema . name ; for ( const table of schema . tables ) { const tableName = table . name ; if ( excludedEntities . indexOf ( tableName ) === - 1 && ( includedEntities . length === 0 || includedEntities . indexOf ( tableName ) !== - 1 ) ) { console . warn ( `Processing table ${ tableName }` ) ; const jsonSchema = await this . convertEntity ( { additionalProperties , baseUrl , defaultDescription , indentSpaces , outputFolder , schemaName , entity : table , } ) ; outputSchemas . push ( jsonSchema ) ; } else { console . warn ( `Skipping excluded table ${ tableName }` ) ; } } for ( const view of schema . views ) { const viewName = view . name ; if ( excludedEntities . indexOf ( viewName ) === - 1 && ( includedEntities . length === 0 || includedEntities . indexOf ( viewName ) !== - 1 ) ) { console . warn ( `Processing view ${ viewName }` ) ; const jsonSchema = await this . convertEntity ( { additionalProperties , baseUrl , defaultDescription , indentSpaces , outputFolder , schemaName , entity : view , } ) ; outputSchemas . push ( jsonSchema ) ; } } for ( const view of schema . materializedViews ) { const viewName = view . name ; if ( excludedEntities . indexOf ( viewName ) === - 1 && ( includedEntities . length === 0 || includedEntities . indexOf ( viewName ) !== - 1 ) ) { console . warn ( `Processing materialized view ${ viewName }` ) ; const jsonSchema = await this . convertEntity ( { additionalProperties , baseUrl , defaultDescription , indentSpaces , outputFolder , schemaName , entity : view , } ) ; outputSchemas . push ( jsonSchema ) ; } } } return outputSchemas ; } private param { { additionalProperties : boolean , baseUrl : string , defaultDescription : string , indentSpaces : number , outputFolder ? : string , entity : Entity , * } } returns private async convertEntity ( { additionalProperties , baseUrl , defaultDescription , indentSpaces , outputFolder , schemaName , entity , } ) { const entityName = entity . name ; const baseName = entityName . replace ( `${ schemaName }_` , '' ) ; const jsonSchema = { additionalProperties , $schema : 'http://json-schema.org/draft-07/schema#' , $id : `${ baseUrl }/${ schemaName }/${ entityName }.json` , title : baseName , description : entity . comment || defaultDescription , properties : { } , required : [ ] , type : 'object' , } ; const columns = entity . columns ; for ( const column of columns ) { const columnName = column . name ; const columnType = column . type . name ; ( jsonSchema . properties as { [ key ] : JSONSchema7Definition ; } ) [ columnName ] = { ... this . convertColumnType ( { column } ) as Record < string , unknown > , description : `${ column . comment || defaultDescription }. Database type: ${ columnType }. Default value: ${ column . default }` , maxLength : column . length , } ; if ( column . notNull && ! column . default ) { ( jsonSchema . required as string [ ] ) . push ( columnName ) ; } } if ( outputFolder ) { const folderName = join ( outputFolder , schemaName ) ; await mkdirp ( folderName ) ; const fileName = join ( folderName , `${ entityName }.json` ) ; await jsonfile . writeFile ( fileName , jsonSchema , { spaces : indentSpaces } ) ; } return jsonSchema ; } private param { { column : Column , * } } returns { Partial < JSONSchema7Definition > } private convertColumnType ( { column , } ) { const columnType = column . type . name ; const isArray = column . arrayDimension > 0 ; switch ( columnType ) { case 'bit' : case 'bit varying' : case 'varbit' : case 'character' : case 'character varying' : case 'text' : { const typeDef = { type : 'string' , maxLength : column . length } ; if ( isArray ) { return { type : 'array' , items : typeDef } ; } return typeDef ; } case 'uuid' : { const typeDef = { type : 'string' , format : 'uuid' , maxLength : column . length } ; if ( isArray ) { return { type : 'array' , items : typeDef } ; } return typeDef ; } case 'date' : { const typeDef = { type : 'string' , format : 'date' , maxLength : column . length } ; if ( isArray ) { return { type : 'array' , items : typeDef } ; } return typeDef ; } case 'time with time zone' : case 'time without time zone' : { const typeDef = { type : 'string' , format : 'time' , maxLength : column . length } ; if ( isArray ) { return { type : 'array' , items : typeDef } ; } return typeDef ; } case 'timestamp with time zone' : case 'timestamp without time zone' : case 'timestamp' : { const typeDef = { type : 'string' , format : 'date-time' , maxLength : column . length } ; if ( isArray ) { return { type : 'array' , items : typeDef } ; } return typeDef ; } case 'boolean' : { const typeDef = { type : 'boolean' } ; if ( isArray ) { return { type : 'array' , items : typeDef } ; } return typeDef ; } case 'bigint' : case 'decimal' : case 'double precision' : case 'float8' : case 'int' : case 'integer' : case 'numeric' : case 'real' : case 'smallint' : { const typeDef = { type : 'number' , maxLength : column . length } ; if ( isArray ) { return { type : 'array' , items : typeDef } ; } return typeDef ; } case 'json' : case 'jsonb' : { const typeDef = { type : 'object' , properties : { } } ; if ( isArray ) { return { type : 'array' , items : typeDef } ; } return typeDef ; } case 'interval' : { const typeDef = { oneOf : [ { type : 'number' , description : 'Duration in seconds' } , { type : 'string' , description : 'Descriptive duration i.e. 8 hours' } , { type : 'object' , description : 'Duration object' , properties : { years : { type : 'number' } , months : { type : 'number' } , days : { type : 'number' } , hours : { type : 'number' } , minutes : { type : 'number' } , seconds : { type : 'number' } , milliseconds : { type : 'number' } } } , ] } ; if ( isArray ) { return { type : 'array' , items : typeDef } ; } return typeDef ; } default : { console . warn ( `Unsupported column type: ${ columnType }. Defaulting to null` ) ; return { type : 'null' } ; } } } }