MySQL Extractor

This node package is to easily extract the MySQL Schema to a json format which then can be generated into various migration files.

Usage

Methods

createTableFile()

createForeignKeyFile()

createIndexFile()

createIssueFile()

Options

host, user, password, database

configuration to connect to your database

tableFile, keyFile, indexFile, issueFile

file path to your json files

issues

An array of issues you would like to inspect. If you do not specify an issues array, it will test against all available issues.

Example

var MySqlExtractor = require('mysql-extractor');
 
var opts = {
    host: 'hostip',
    user: 'user',
    password: 'pass',
    database: 'dbname',
    tableFile: 'output/tablesExample.json',
    keyFile: 'output/keysExample.json',
    indexFile: 'output/indexExample.json',
    issueFile: 'output/issueExample.json',
    issues: [
        'one_key_error',
        'integer_primary_error',
        'unsigned_primary_error',
        'unique_primary_error',
        'incrementing_primary_warning',
        'id_primary_warning',
        'reference_integer_foreign_error',
        'reference_unsigned_foreign_error',
        'reference_unique_foreign_error',
        'integer_foreign_error',
        'unsigned_foreign_error',
        'index_foreign_warning',
        'foreign_to_id_warning',
        'reference_primary_foreign_warning',
        'column_lowercase_warning'
    ],
    filterTableNames: function (tableNames) {
        return tableNames.filter(function (tableName) {
            return true;
        });
    }
};
 
mySqlExtractor = new MySqlExtractor(opts);
 
mySqlExtractor.createTableFile();
mySqlExtractor.createForeignKeyFile();
mySqlExtractor.createIndexFile();
mySqlExtractor.createIssueFile();
 

Getting Table/Column Structure

Supported Data Types

• boolean
• date
• dateTime
• decimal
• geometry
• integer
• string (VarChar)
• text
• bigInteger
• char
• double
• enum
• float
• json
• jsonb
• longText
• mediumInteger
• mediumText
• smallInteger
• time
• tinyInteger
• timestamp

Example Output

[
  {
    "name": "budgets",
    "columns": [
        {
            "name": "id",
            "type": "integer",
            "nullable": false,
            "primaryKey": true,
            "unsigned": false,
            "incrementing": true,
            "defaultValue": null,
            "constraints": {
                "maximum": 11
            },
            "rawType": "int(11)"
        },
        {
            "name": "program_id",
            "type": "integer",
            "nullable": false,
            "primaryKey": false,
            "unsigned": false,
            "incrementing": false,
            "defaultValue": null,
            "constraints": {
                "maximum": 11
            },
            "rawType": "int(11)"
        },
        {
            "name": "amount",
            "type": "decimal",
            "nullable": false,
            "primaryKey": false,
            "unsigned": false,
            "incrementing": false,
            "defaultValue": null,
            "constraints": {
                "precision": 8,
                "scale": 2
            },
            "rawType": "decimal(8,2)"
        }
      ]
  }	
]

Foreign Key Constraints

Example Output

[
  {
    "name": "budgets",
    "foreignKeys": [
        {
            "foreign": "program_id",
            "references": "id",
            "on": "programs",
            "onUpdate": "restrict",
            "onDelete": "restrict"
        }
    ]
  }
]
 

Indexes

Example Output

[
  {
  		"name": "budgets",
  		"indexes": [
  			{
  				"column": "program_id",
  				"unique": false,
  				"indexType": "BTREE"
  			},
  			{
  				"column": "create_by",
  				"unique": false,
  				"indexType": "BTREE"
  			},
  			{
  				"column": "status_id",
  				"unique": false,
  				"indexType": "BTREE"
  			}
  		]
  	}
  	...
]

Finding Issues

There are some standards to follow when setting up our database.

Rules To Inspect in Database (checked are implemented)

General

• one_key_error - A table must have only one primary key or one composite key

Primary Key Rules

• integer_primary_error - Primary Key must be integer or bigInteger
• unsigned_primary_error - Primary Key must be unsigned
• unique_primary_error - Primary Key must be uniquely index
• incrementing_primary_warning - Primary Key should be auto-incrementing.
• id_primary_warning - Primary Key column should be named id //need to see if the key is composite

Foreign Key Rules

• reference_integer_foreign_error - Foreign Key must reference to an integer (or bigInteger) column
• reference_unsigned_foreign_error - Foreign Key must reference to an unsigned column
• reference_unique_foreign_error - Foreign Key must reference to a column with an unique index
• integer_foreign_error - Foreign Key must be integer or bigInteger
• unsigned_foreign_error - Foreign Key must be unsigned
• index_foreign_warning - Foreign Key should be indexed
• foreign_to_id_warning - Foreign keys should point to id column
• reference_primary_foreign_warning - Foreign Key should reference to a Primary Key.
• foreign_ending_id_warning - Foreign key columns should end with _id.

Formatting

• column_lowercase_warning - lower snakecase for all and column names
• table_lowercase_warning - lower snakecase for all and column names
• ending_id_warning - All column names that ends with _id should be a foreign key

Example Output

 
[
    {
        "name": "budgets",
        "issues": [
            {
                "type": "error",
                "column": "id",
                "issueName": "unsigned_primary_error",
                "reason": "id is a primary key. it has a type of int(11), but it is not unsigned"
            },
            {
                "type": "error",
                "column": "id",
                "issueName": "reference_unsigned_foreign_error",
                "reason": "id is being referenced elsewhere, but it is not unsigned"
            },
            {
                "type": "error",
                "column": "program_id",
                "issueName": "unsigned_foreign_error",
                "reason": "program_id is a foreign key, but is not unsigned"
            }
        ]
    }
]

TODO

• Add Env File Consumption
• Add Indexes File Feature
• Add Database Inspection Feature
• Add Unit Tests
• Add Events