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:[email protected]: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

• SQLite3 Example
• Sequelize Documentation