Package Exports

sql-ddl-to-json-schema

This package does not declare an exports field, so the exports above have been automatically detected and optimized by JSPM instead. If any package subpath is missing, it is recommended to post an issue to the original package (sql-ddl-to-json-schema) to support the "exports" field. If that is not possible, create a JSPM override to customize the exports field for this package.

Readme

SQL DDL to JSON Schema converter

Transforms SQL DDL statements into JSON format (a compact format and JSON Schema).

CREATE TABLE users (
  id INT(11) NOT NULL AUTO_INCREMENT,
  nickname VARCHAR(255) NOT NULL,
  deleted_at TIMESTAMP NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL,
  updated_at TIMESTAMP,
  PRIMARY KEY (id)
) ENGINE MyISAM COMMENT 'All system users';

ALTER TABLE users ADD UNIQUE KEY unq_nick (nickname);

Delivers a compact JSON format:

[
  {
    "name": "users",
    "columns": [
      {
        "name": "id",
        "type": {
          "datatype": "int",
          "width": 11
        },
        "options": {
          "nullable": false,
          "autoincrement": true
        }
      },
      {
        "name": "nickname",
        "type": {
          "datatype": "varchar",
          "length": 255
        },
        "options": {
          "nullable": false
        }
      },
      {
        "name": "deleted_at",
        "type": {
          "datatype": "timestamp",
          "fractional": 0
        },
        "options": {
          "nullable": true
        }
      },
      {
        "name": "created_at",
        "type": {
          "datatype": "timestamp",
          "fractional": 0
        },
        "options": {
          "nullable": false,
          "default": "CURRENT_TIMESTAMP"
        }
      },
      {
        "name": "updated_at",
        "type": {
          "datatype": "timestamp",
          "fractional": 0
        },
        "options": {
          "nullable": true
        }
      }
    ],
    "primaryKey": {
      "columns": [
        {
          "column": "id"
        }
      ]
    },
    "uniqueKeys": [
      {
        "columns": [
          {
            "column": "nickname"
          }
        ],
        "name": "unq_nick"
      }
    ],
    "options": {
      "comment": "All system users",
      "engine": "MyISAM"
    }
  }
]

Output to JSON Schema is still work in progress - Check out the roadmap

Usage

yarn add sql-ddl-to-json-schema or npm i sql-ddl-to-json-schema

const Parser = require('sql-ddl-to-json-schema');
const parser = new Parser('mysql');

parser.feed(`
CREATE TABLE users (
  id INT(11) NOT NULL AUTO_INCREMENT,
  nickname VARCHAR(255) NOT NULL,
  deleted_at TIMESTAMP NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL,
  updated_at TIMESTAMP,
  PRIMARY KEY (id)
) ENGINE MyISAM COMMENT 'All system users';

ALTER TABLE users ADD UNIQUE KEY unq_nick (nickname);
`);

const tablesArray = parser.toCompactJson();
// ...

About

No SQL server, client or DBMS is required.

To see which DDL statements / SQL dialects are supported, check out the roadmap.

This project is a grammar and stream-friendly SQL parser based on nearley.

Contributing

You are welcome to contribute!

Preferably use yarn instead of npm, as all scripts in package.json are run through yarn.

Clone this repo
Install nodemon: yarn global add nodemon
Install dependencies: yarn

Commiting

To commit, use commitizen: git cz (you will need to have installed commitizen: yarn global add commitizen).

Understanding the internals

Folder structure:

/
|- index.js               Entrypoint file, imports from lib/index.js
|- lib/                   Compiled (dist) library folder, product of this project.
|
|- src/
|  |- shared/             Shared files used by dialects, parsers and formatters.
|  |- mysql/
|     |- example.js       Serves development purpose for testing isolated statements.
|     |- formatter/       Formats the parsed JSON (output of parser) to other format.
|        |- compact/      Formatter for a compact JSON format.
|        |- jsonschema/   Formatter for a JSON Schema format (not yet implemented).
|     |- parser/
|        |- dictionary/   JS files with array of keywords used in lexer.ne.
|        |- rules/        Nearley files with grammar rules.
|        |- lexer.ne      Entrypoint and first lines of the grammar.
|
|- tasks/
|  |- mysql/
|     |- assembly.js      Script that concatenates all .ne files to grammar.ne to lib folder.
|     |- formatters.js    Script that sends a copy of formatters to lib folder.
|
|- test/                  Tests.

There are naming rules for tokens in ne files, as stated in lexer.ne. They are prepended with:

K_ -> Keywords
P_ -> Phrase (aka statements)
O_ -> Options (one of several keywords or phrases)
S_ -> Symbol (not a keyword, but chars and other matches by RegExp's)

The dictionary/keywords.js file contains keywords, but they are prepended with K_ when used in .ne files. Take a look to make sure you understand how it is exported.
The compiled grammar.ne file comprises an assembly (concatenation) of lexer.ne and files in rules folder. So don't worry about importing .ne files in other .ne files. This prevents circular dependency and grammar rules in lexer.ne are scoped to all files (thus not having to repeat them in every file).

Scripts at hand

Valid to all SQL dialects:

Assemble grammar.ne and compile to grammar.js: yarn run build
Same as above, but watch for changes: yarn run build:watch
Run tests: yarn run test
Test and watch for changes: yarn run test:watch
Test against nearley tester: yarn run nearley-test lib/mysql/parser/grammar.js --input 'CREATE TABLE test (test CHAR(1));'

Debugging

Taking the example file as an example, you may debug with the following configurations, for each IDE:

Visual Studio Code

Place the launch config below. To debug tests you may want to change the args as you go.

{
  "version": "0.2.0",
  "configurations": [
    {
      "type": "node",
      "request": "launch",
      "name": "Debug Compilation",
      "args": [
        "lib/mysql/parser/grammar.ne"
      ],
      "program": "${workspaceFolder}/node_modules/nearley/bin/nearleyc.js"
    },
    {
      "type": "node",
      "request": "launch",
      "name": "Debug Tests",
      "args": [
        "test/mysql/parser/parser.spec.js"
      ],
      "program": "${workspaceFolder}/node_modules/ava/profile.js"
    },
    {
      "type": "node",
      "request": "launch",
      "name": "Debug Example",
      "program": "${workspaceFolder}/src/mysql/example.js"
    }
  ]
}