json-schema-to-sql

Convert a JSON schema into SQL DDL (create table) statements.

Table of Contents

• Installation
• Usage
• Features
  • JSON Schema Compatible
    • JSON Schema Extensions
    • Unmapped Features
  • Fully Typed
  • Supported Dialects
  • Nested Structures
    • Objects
    • Arrays of Objects
    • Arrays of Literals
  • Validation
• Caveats
  • Inferred Primary Keys
• Limitations
  • Composite Primary Keys
  • Arrays in Arrays
• Contributing

Installation

npm install json-schema-to-sql

Usage

import { generateSQLFromJSONSchema } from "json-schema-to-sql";

const myDatabaseJsonSchema = {
  type: 'object',
  properties: {
    users: {
      type: 'object',
      properties: {
        id: { type: 'string', format: 'uuid' },
        name: { type: 'string' },
      },
      required: ['id'],
      'x-primaryKey': ['id'],
    },
  },
};

const { queries, errors } = generateSQLFromJSONSchema(myDatabaseJsonSchema);

Features

JSON Schema Compatible

The library aims to map features of the JSON Schema spec as closely as possible to their logical counterpart in relational databases.

A few examples:

| JSON Schema Feature | Database Feature | |---------------------|------------------------------------------| | type + format | Type of the column | | required | Non nullable columns | | enum | CHECK constraint for the possible values | | default | Default value of the column | | description | Column comment |

JSON Schema Extensions

Where a logical mapping is not possible, the library extends the JSON Schema spec using the x- syntax

For example:

| JSON Schema Feature | Database Feature | |---------------------|--------------------------------| | x-primaryKey | Sets primary keys in the table |

In the future, things like x-onDelete and x-onUpdate will be added to support cascades.

Unmapped Features

JSON Schema features, which are not yet implemented or will never be implemented are just ignored by the converter.

Fully Typed

All the internals use TypeScript.

Supported Dialects

Currently, it supports PostgreSQL and MySQL by passing pg and mysql respectively to the function. Default is pg.

Every example in this README will be using PostgreSQL.

Nested Structures

Nested structures are normalized into tables, which reference their parent table by a dynamically generated foreign key.

For example, let's take the following JSON schema:

import { JSONSchema7 } from 'json-schema';

const mySchema = {
  type: 'object',
  properties: {
    users: {
      type: 'object',
      properties: {
        id: { type: 'string', format: 'uuid' },
        settings: {
          type: 'object',
          properties: {
            theme: {
              type: 'string',
              enum: ['light', 'dark', 'system'],
            },
          },
        },
        notes: {
          type: 'array',
          items: {
            type: 'object',
            properties: {
              content: { type: 'string' },
            },
          },
        },
        tags: {
          type: 'array',
          items: { type: 'string' },
        },
      },
    },
  },
} satisfies JSONSchema7

In this schema:

• settings is a nested object
• notes is a nested array of objects
• tags is a nested array of strings

The library will take care of the nesting by mapping each of the nested structures into a table. This table will have its name prefixed by the name of the parent table like <parent-table-name>_<table-name>. An additional column with name in the format <parent-table-name>_<parent-table-primary-key> will be added as a foreign key to the parent in each table generated from a nested structure. Depending on the type of the nested structure, each table will be handled differently:

Objects

Each property will be treated as a column of the nested table. A unique constraint will be added for the foreign key to the parent table. This relationship is considered 1:1 (One-to-One).

Arrays of Objects

Each property will be treated as a column of the nested table. This relationship is considered 1:N (One-to-Many).

Arrays of Literals

A table with a single column called value with the type of the items will be created. This relationship is considered 1:N (One-to-Many).

Below are the resulting SQL queries:

[
  `create table "users_settings" ("theme" varchar(255), check (theme IN ('light', 'dark', 'system')));`,
  'create table "users_notes" ("content" varchar(255));',
  'create table "users_tags" ("value" varchar(255));',
  'create table "users" ("id" uuid, constraint "users_pkey" primary key ("id"));',
  'alter table "users_settings" add column "users_id" uuid;',
  'alter table "users_settings" add constraint "users_settings_users_id_foreign" foreign key ("users_id") references "users" ("id");',
  'alter table "users_settings" add constraint "users_settings_users_id_unique" unique ("users_id");',
  'alter table "users_notes" add column "users_id" uuid;',
  'alter table "users_notes" add constraint "users_notes_users_id_foreign" foreign key ("users_id") references "users" ("id");',
  'alter table "users_tags" add column "users_id" uuid;',
  'alter table "users_tags" add constraint "users_tags_users_id_foreign" foreign key ("users_id") references "users" ("id");'
]

Validation

Incoming schemas are treated as of type unknown. Internally, there are three steps of validation happening sequentially:

1. Syntax validation with Ajv
2. Structural validation using Zod
3. Semantic validation for resolving references between tables

If somewhere along the validation sequence there is an error, it's collected and reported in the result of the function with the following interface:

interface ValidationError {
  message: string;   // The error message 
  path: string[];    // The place in the schema where the error occured
}

Caveats

Inferred Primary Keys

If a table has an id property, it will be considered a primary key, unless otherwise specified with x-primaryKey.

Limitations

Composite Primary Keys

Specifying multiple keys in x-primaryKey is currently not supported due to the complex logic required to map the foreign keys of nested structures to them.

Arrays in Arrays

Arrays nested in arrays are not supported and are considered an error in the schema due to a lack of a proper relational database representation. I'm open to suggestions for implementing this.

Contributing

The project is open to contributions. Just create a pull request at https://github.com/VasilVelikov00/json-schema-to-sql/pulls