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"]
  }
]