mysql-fill
Simple Node.js utility to help maintaining compatibility between MySQL databases.
This little program helps you to maintain the compatibility between MySQL databases by comparing their structure with one specified in a JSON file: if it finds a table, column or constraint (primary key, foreign key, unique and index) that is not described in the JSON file, it gets added to the database.
This program does not perform alterations or deletions - it will only "expand" the databases, in order to avoid conflicts with current versions.
Obs: conflict with 'unique' constraints may occur, if they are specified in the JSON structure but the tables are holding duplicate values.
To install it, use npm:
$ npm install mysql-fill --save-dev
And to run it, simply use node and run it directly from node_modules, passing the 2 configuration files (their structure is described below) as arguments:
$ node ./node_modules/mysql-fill ./connection.json ./structure.json
The configuration files have the following structure:
connection.json - Describes how to connect to the database
{
host: string, // Host
user: string, // Your username
password: string, // Your password
database: string, // Name of the database
connectTimeout: number // Recommended: 10000
}
Example:
{
"host": "192.168.0.1",
"user": "root",
"password": "password",
"database": "myDatabase",
"connectTimeout": 10000
}
structure.json - Describes the structure of the database
[
{ // Represents a table
name: string, // Name for the table
description?: string, // Description (optional, not used for queries)
disabled?: boolean, // If true, the table will be ignored
columns: [{ // List of columns
name: string, // Name of the column
type: string, // Type (use the same name as in MySQL: int, varchar, etc)
null?: boolean, // Is the column nullable?
disabled?: boolean, // If true, the column will be ignored
size?: number, // Size of the data type (like varchar(20))
autoIncrement?: boolean, // True for auto-increment
primaryKey?: boolean // Is this field a primary key?
options?: [string] // "unsigned", "zerofill" or "unsigned zerofill"
default?: any // Default
}],
foreignKeys?: [{ // Foreign keys in this table
column: string, // Name of the local column
referenceTable: string, // Name of the table to be referenced
referenceColumn: string, // Name of the column to be referenced (in the referenced table)
onDelete?: string, // ON DELETE action (setNull, restrict, cascade, or noAction)
onUpdate?: string // ON UPDATE actin (setNull, restrict, cascade, or noAction)
}],
unique?: [string], // List of unique columns (by name)
index?: [string] // List of index columns (by name)
}
]
Example:
[
{
"name": "User",
"description": "This table has the users",
"disabled": false,
"columns": [{
"name": "id",
"type": "int",
"null": false,
"autoIncrement": true,
"primaryKey": true
}, {
"name": "name",
"type": "varchar",
"size": 30,
"null": true
}],
"unique": ["name"],
"index": ["id"]
}, {
"name": "Company",
"description": "This table has the companies",
"disabled": false,
"columns": [{
"name": "id",
"type": "int",
"null": false,
"autoIncrement": true,
"primaryKey": true
}, {
"name": "name",
"type": "varchar",
"size": 30,
"null": true
}, {
"name": "owner",
"type": "int",
"null": false
}],
"foreignKeys": [{
"column": "owner",
"referenceTable": "User",
"referenceColumn": "id",
"onDelete": "restrict",
"onUpdate": "noAction"
}],
"unique": ["name"],
"index": ["id", "name"]
}
]