Package Exports

@funktechno/sqlsimpleparser
@funktechno/sqlsimpleparser/lib/index.js

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 (@funktechno/sqlsimpleparser) to support the "exports" field. If that is not possible, create a JSPM override to customize the exports field for this package.

Readme

sql-simple-parser

sql ddl parser to support extensions for drawio

Getting started

npm install --save @funktechno/sqlsimpleparser
how to use:

import { SqlSimpleParser } from "@funktechno/sqlsimpleparser"

var sql = `CREATE TABLE "humanresources_department" (
    "departmentid" serial NOT NULL,
    "name" Name NOT NULL,
    "groupname" Name NOT NULL,
    "modifieddate" timestamp NOT NULL,
    PRIMARY KEY("departmentid")
);

CREATE TABLE "humanresources_employeedepartmenthistory" (
  "businessentityid" int(4) NOT NULL,
  "departmentid" int(2) NOT NULL,
  "shiftid" int(2) NOT NULL,
  "startdate" date NOT NULL,
  "enddate" date,
  "modifieddate" timestamp NOT NULL,
  PRIMARY KEY("businessentityid","departmentid","shiftid","startdate"),
  FOREIGN KEY ("departmentid") REFERENCES "humanresources_department"("departmentid")
);
`

// run parser
const parser = new SqlSimpleParser('postgres')

// get models
const models = parser
  .feed(sql)
  .ToModel();

outputs

{
  "TableList": [
    {
      "Name": "humanresources_department",
      "Properties": [
        {
          "Name": "departmentid",
          "ColumnProperties": "serial NOT NULL",
          "TableName": "humanresources_department",
          "ForeignKey": [
            {
              "PrimaryKeyTableName": "humanresources_employeedepartmenthistory",
              "PrimaryKeyName": "departmentid",
              "ReferencesPropertyName": "departmentid",
              "ReferencesTableName": "humanresources_department",
              "IsDestination": true
            }
          ],
          "IsForeignKey": false,
          "IsPrimaryKey": true
        },
        {
          "Name": "name",
          "ColumnProperties": "Name NOT NULL",
          "TableName": "humanresources_department",
          "ForeignKey": [],
          "IsForeignKey": false,
          "IsPrimaryKey": false
        },
        {
          "Name": "groupname",
          "ColumnProperties": "Name NOT NULL",
          "TableName": "humanresources_department",
          "ForeignKey": [],
          "IsForeignKey": false,
          "IsPrimaryKey": false
        },
        {
          "Name": "modifieddate",
          "ColumnProperties": "timestamp NOT NULL",
          "TableName": "humanresources_department",
          "ForeignKey": [],
          "IsForeignKey": false,
          "IsPrimaryKey": false
        }
      ]
    },
    {
      "Name": "humanresources_employeedepartmenthistory",
      "Properties": [
        {
          "Name": "businessentityid",
          "ColumnProperties": "int(4) NOT NULL",
          "TableName": "humanresources_employeedepartmenthistory",
          "ForeignKey": [],
          "IsForeignKey": false,
          "IsPrimaryKey": false
        },
        {
          "Name": "departmentid",
          "ColumnProperties": "int(2) NOT NULL",
          "TableName": "humanresources_employeedepartmenthistory",
          "ForeignKey": [
            {
              "PrimaryKeyTableName": "humanresources_department",
              "PrimaryKeyName": "departmentid",
              "ReferencesPropertyName": "departmentid",
              "ReferencesTableName": "humanresources_employeedepartmenthistory",
              "IsDestination": false
            }
          ],
          "IsForeignKey": true,
          "IsPrimaryKey": false
        },
        {
          "Name": "shiftid",
          "ColumnProperties": "int(2) NOT NULL",
          "TableName": "humanresources_employeedepartmenthistory",
          "ForeignKey": [],
          "IsForeignKey": false,
          "IsPrimaryKey": false
        },
        {
          "Name": "startdate",
          "ColumnProperties": "date NOT NULL",
          "TableName": "humanresources_employeedepartmenthistory",
          "ForeignKey": [],
          "IsForeignKey": false,
          "IsPrimaryKey": false
        },
        {
          "Name": "enddate",
          "ColumnProperties": "date",
          "TableName": "humanresources_employeedepartmenthistory",
          "ForeignKey": [],
          "IsForeignKey": false,
          "IsPrimaryKey": false
        },
        {
          "Name": "modifieddate",
          "ColumnProperties": "timestamp NOT NULL",
          "TableName": "humanresources_employeedepartmenthistory",
          "ForeignKey": [],
          "IsForeignKey": false,
          "IsPrimaryKey": false
        }
      ]
    }
  ],
  "Dialect": "postgres",
  "ForeignKeyList": [
    {
      "PrimaryKeyTableName": "humanresources_employeedepartmenthistory",
      "PrimaryKeyName": "departmentid",
      "ReferencesPropertyName": "departmentid",
      "ReferencesTableName": "humanresources_department",
      "IsDestination": true
    },
    {
      "PrimaryKeyTableName": "humanresources_department",
      "PrimaryKeyName": "departmentid",
      "ReferencesPropertyName": "departmentid",
      "ReferencesTableName": "humanresources_employeedepartmenthistory",
      "IsDestination": false
    }
  ],
  "PrimaryKeyList": [
    {
      "PrimaryKeyTableName": "humanresources_department",
      "PrimaryKeyName": "departmentid"
    },
    {
      "PrimaryKeyTableName": "humanresources_employeedepartmenthistory",
      "PrimaryKeyName": "businessentityid,departmentid,shiftid,startdate"
    }
  ]
}

there are some example sql files
see test examples_models.spec for more use cases

Supported

Databases: sqlite, postgres, sqlserver, mysql
table names, primary keys, foreign keys, column names with extended column information

Development

npm install
npm test or use vscode debugger Jest single run

Deploy

npm publish --access public
Testing
- npm pack