node package manager

nicassa-parser-db

Nicassa DB Parser

nicassa-parser-db is a CLI toolkit for reading Database Schema for MS-SQL, MySQL, PostgreSQL and SQLite 3. It's intended to be used with nicassa-generator for source code generation.

Installation

$ sudo npm install -g nicassa-parser-db

Please note nicassa-parser-db is written in TypeScript.

Features

  • Support for MS-SQL, MySQL, PostgreSQL and SQLite 3
  • Creates a JSON Configuration file containing the Database Schema definition
  • Optional storage of the connection string (highly Sequelize compatible)
  • Updates the Schema (e.g. after a Database migration) and keeps the rest of the file

Usage

# create a new schema file
$ nicassa-parser-db init -f schema.json
# optional step: edit the schema and add a connection string
$ vi schema.json
  • example for sqlite
  • check the Sequelize Documentation for more informations
{
  "formatVersion": "1.0",
  "sequelizeConnect": {
    "options": {
      "dialect": "sqlite",
      "storage": "schema.db"
    }
  }
}
# create sqlite3 demo database
$ sqlite3 schema.db
CREATE TABLE Roles (
    RoleID INTEGER PRIMARY KEY AUTOINCREMENT,
    RoleName TEXT NOT NULL
);
 
CREATE TABLE Users (
    UserID INTEGER PRIMARY KEY AUTOINCREMENT,
    RoleID INTEGER NOT NULL,
    UserName TEXT NOT NULL,
 
    FOREIGN KEY (RoleID) REFERENCES Roles(RoleID)
);
# update the schema (if you added the connection string)
$ nicassa-db-parser update -f schema.json
# update the schema (connection string as parameter, e.g. MS-SQL, MySQL, PostgreSQL)
$ nicassa-db-parser update -f schema.json -u 'postgres://user:pass@example.com:5432/dbname'

Filters

{
    "filter": {
      "excludeTables": false,
      "excludeViews": false,
      "exculdeColumns": [],
      "exculde": [],
      "only": []
    }
}
  • excludeTables (boolean): enables / disables the use of tables
  • excludeViews (boolean): enables / disables the use of views
  • exculdeColumns (string[]): array with names for excluding columns in tables and views. You can also specify "table.column" to ignore only a specific column in a view or table.
  • exculde (string[]): array with names for excluding tables / views
  • only (string[]): ignores the exclude section, takes only views/tables with the given name, honors excludeColumns

Example output

{
  "nicassaParserDB": {
    "formatVersion": "1.0",
    "lastUpdateUTC": "Mon, 02 Jan 2017 23:43:48 GMT",
    "sequelizeConnect": {
      "options": {
        "dialect": "sqlite",
        "storage": "./test.db",
        "benchmark": true,
        "logging": false
      }
    },
    "filter": {
      "excludeTables": false,
      "excludeViews": false,
      "exculdeColumns": [],
      "exculde": [],
      "only": []
    },
    "schema": {
      "dialect": "sqlite",
      "tables": [
        {
          "name": "Roles",
          "columns": [
            {
              "name": "RoleID",
              "dataType": "INTEGER",
              "nullable": true,
              "defaultValue": null,
              "length": null,
              "precision": null,
              "pk": true,
              "referencedTableName": null,
              "referencedColumnName": null
            },
            {
              "name": "RoleName",
              "dataType": "TEXT",
              "nullable": false,
              "defaultValue": null,
              "length": null,
              "precision": null,
              "pk": false,
              "referencedTableName": null,
              "referencedColumnName": null
            }
          ]
        },
        {
          "name": "Users",
          "columns": [
            {
              "name": "UserID",
              "dataType": "INTEGER",
              "nullable": true,
              "defaultValue": null,
              "length": null,
              "precision": null,
              "pk": true,
              "referencedTableName": null,
              "referencedColumnName": null
            },
            {
              "name": "RoleID",
              "dataType": "INTEGER",
              "nullable": false,
              "defaultValue": null,
              "length": null,
              "precision": null,
              "pk": false,
              "referencedTableName": "Roles",
              "referencedColumnName": "RoleID"
            },
            {
              "name": "UserName",
              "dataType": "TEXT",
              "nullable": false,
              "defaultValue": null,
              "length": null,
              "precision": null,
              "pk": false,
              "referencedTableName": null,
              "referencedColumnName": null
            }
          ]
        }
      ],
      "views": []
    }
  }
}

Resources