nodedll

Create, update or delete your MySQL databases and their content by writing no MySQL code at all.

Designed for creating and updating databases, their structure and their content within a single command to be executed whenever you want.

Examples of use cases:

• I have a Node.js web application with a MySQL database, and want to define a base structure for the database for local development. I also want to insert default data into it if not existing yet. I want the database structure to be modified automatically when I change the definition file, and I want new data to be inserted whenever I add it to the definition file, so I execute nodeddl's create() function in my app.js script.
• I want to create a defaults file for my MySQL database and don't want to type MySQL sentences. Instead, I define the database structure and default data in a database_definition.js file, which I store wherever I want (I'll be asked when executing the script), and create my database with nodeddl's create script. I do not want to create any configuration files, for security reasons, and wish to be asked for connection parameters when executing the script.

Updates since versions 1.1.*:

• Added MySQL unique constraint (option options.unique()) and unique indexes (array unique_indexes in column definition), which I forgot to implement in previous versions.
• Possibility to enable/disable verbose mode when executed as a code block, to hide module logs (errors will still be shown).
• Hide console input when typing password in interactive mode.
• Perform ALTER statements on primary keys, unique keys and foreign keys when definition is updated.
• Execute CLI scripts in debug mode.

How to use

There are two main scripts/functions that can be executed from the command line and from code: create, for database creation and updates, and delete, for database deletion (the third one, reset, is a combination of both). The two of them let you:

• Perform database creation with CREATE IF NOT EXISTS statement
• Perform database deletion with DROP DATABASE statement.
• Perform user creation with CREATE USER IF NOT EXISTS statement.
• Perform table creation with CREATE TABLE IF NOT EXISTS statement.
• Perform modifications on table columns, foreign keys, primary keys and unique keys with ALTER TABLE statements.
• Perform non-duplicate data insertion with INSERT statements and SELECT FROM statements.

If verbose mode is activated, you will see error logs, warning logs and info logs. This mode is activated by default; you can deactivate it when executing nodeddl from code, but not from the CLI. Debug mode will let you see extra logs, such as the full queries that are being executed.

nodeddl as a CLI script

nodeddl can be executed from the command line as a Node.js script. You can perform 3 different actions:

|Command (shortcut defined in package.json)|Equivalent (for execution from another folder) |Description | |------------------------------------------|----------------------------------------------------------------------------------------|-------------------------------------------------------------------------------------------------------------------------------| |npm run create [-- debug] |node $NODEDDL_LOCATION/src/main/create.js |Create or update your database, its tables or columns from any table (type -- debug option to execute in debug mode).| |npm run delete [-- debug] |node $NODEDDL_LOCATION/src/main/delete.js |Drop the database (type -- debug option to execute in debug mode). | |npm run reset |node $NODEDDL_LOCATION/src/main/delete.js && node $NODEDDL_LOCATION/src/main/create.js|Drop your database and re-create it afterwards. |

If you execute nodeddl from the CLI, the script will look for files connection_config.js and database_definition.js, and if they don't exist, you will be asked for some parameters (detailed explanation in section Configuration).

Verbose mode is activated by default on this execution mode, and you will always see info, warning, error and success messages.

nodeddl as a code block

nodeddl can also be executed from your application code (for example, if you want to apply changes to the database every time the server is started). If you choose this option, you can call the following functions:

|Function|Description | |--------|-----------------------------------------------------------------------------| |create|Create or update your database, its tables or columns from any table.| |delete|Drop the database. |

Required parameters

The above mentioned functions require the following parameters:

|Parameter |Type |Description | |----------|--------|--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------| |options |object |Object containing attributes connection_config (object) and database_definition (object), explained below in section Configuration. Optional parameters: verbose (boolean), to print error, warning and info logs, and debug (boolean), to enable debug mode and see debug logs.| |callback|function|Function that returns parameter error (string) when an error is found during execution. |

There is no better way to understand it than with an example:

// First, require nodeddl
const nodeddl = require('nodeddl')

// Then, create connection configuration object (see sample file connection_config.dist.js)
const connection_config = {
	host: 'localhost', /* custom */
	user: 'testuser', /* custom */
	password: 'testpass' /* custom */
}

// Also, require your database definition object (see sample file database_definition.dist.js)
// You can also define it here, but we recommend to save it as a separate file due to its usual length
const database_definition = require('./path/to/definition.js') /* custom */

// Create nodeddl options object
var nodeddl_options = {
	connection_config: connection_config,
	database_definition: database_definition,
	verbose: true, /* set to false to hide logs (optional; default is true) */
}

// Create database
nodeddl.create(nodeddl_options, function(error) {
	// Custom
	if(error)
		console.log(error)
	else
		console.log('Success!')
})

Configuration

In order to make nodeddl work, two objects are required: connection_config, which stores the MySQL connection settings, and database_definition, that stores the database creation instructions.

nodeddl configuration depends on the execution mode:

As a CLI script

• By default, you will be asked for connection parameters (host, user and password) via the CLI if file connection_config.js is not found in $NODEDDL_LOCATION.
• By default, you will be asked for the database definition location via the CLI if file database_definition.js is not found in $NODEDDL_LOCATION.

As a result, the execution will work in these cases:

• Files database_definition.js and connection_config.js both exist in $NODEDDL_LOCATION.
• Only database_definition.js file or connection_config.js file exists in $NODEDDL_LOCATION.
• Files database_definition.js and connection_config.js do NOT exist in $NODEDDL_LOCATION.

As a code block

• You have to provide connection_config and database_definition objects as attributes of parameter options of nodeddl functions (see section How to use - As a code block).
• You can either require them (recommended) or define them in the same script that calls nodeddl functions.

connection_config

The connection settings specified here will be used to connect to your MySQL server and perform the database creation or modification, so you must provide a user with the corresponding privileges (for example, root user in local environment).

|Parameter |Description |Example | |----------|---------------------------------------|-----------| |host |Your MySQL server host. |localhost| |user |Your MySQL user with write permissions.|root | |password|Password for your MySQL user. |password |

In order to create the connection_settings object, take a look at sample file connection_settings.dist.js.

database_definition

The database_definition object will contain information about the database to create, the new user to create and associate to the new database (optional) and the database schema (tables, columns, keys and indexes).

Before writing your database definition, you must require types and options, which contain translation functions from Javascript to MySQL, as follows:

// Require nodeddl
const nodeddl = require('nodeddl')

// Require types and options
const types = nodeddl.types
const options = nodeddl.options

The database_definition object must have the following attributes:

|Parameter |Description | |----------|------------------------------------------------------------------------------------------------------------------------------------------------------------| |database|Object containing general information about the new database. | |user |Object containing information about the new user to create and associate to the new database (optional). | |tables |Object containing information about the tables of the database (column definition, indexes and keys). Each key of this object corresponds to the table name.| |inserts |Object containing data to insert in previously crated tables, as an array of JavaScript objects with key-value pairs. |

In order to create your database_definition object, take a look at sample file database_definition.dist.js.

database

Here, you must specify your database name and collation.

|Attribute |Description | |-----------|-----------------------------------------------------------------------------| |name |Name of the database to create. | |collation|Collation of the database to create (optional). Default is utf8_general_ci.|

user

Here, you must specify the characteristics of the new user to create, if you want.

|Attribute |Description | |----------|--------------------------------------------| |name |Name of the new user. | |password|Password of your user. | |host |Host where you want your user to be created.|

This user will be granted all privileges on the new database, after it has been created.

tables

Each key of this object consists of the table name, whose value is another object containing the following attributes:

|Attribute |Description | |----------------|--------------------------------------------------------------------------------------------------------------------------------------------------------------------| |columns |Object containing information about the new column to create. Each key of this object is the column name, and the value, another object with the column information.| |primary_key |Call to function that creates primary key: options.primary_key(column_name). | |indexes |Array of calls to function that creates indexes: options.index(column1, column2, ...). | |unique_indexes|Array of calls to function that creates unique indexes: options.unique_index(column1, column2, ...). | |foreign_keys |Array of calls to function that creates foreign keys: options.foreign_key(column_name, foreign_key, foreign_table). |

Inside the columns attribute, you must declare an object whose keys are the column names and whose values are the instructions of each column. Each value of the object is another object containing the following attributes:

|Attribute|Description | |---------|--------------------------------------------------------------------------| |type |Call to the function that defines the type (see next section). | |options|Array of calls to functions that define column options (see next section).|

You can see examples of all the above configuration in database_definition.dist.js.

inserts

Each key of this object consists of the table name. Its value must be an array of JavaScript objects, each of which consists of a single or several key-value pairs where the key is the column name and the value is the value of that column.

You can see examples of all the above configuration in database_definition.dist.js.

Datasets

Functions that define colmun types

Defined in datasets/types.js, this object contains functions that translate Javascript type definition into MySQL ones.

If you required nodeddl types as told in previous step (const types = nodeddl.types), the syntax would be the following:

Numeric types

Reference: https://dev.mysql.com/doc/refman/5.7/en/numeric-type-overview.html.

|Column type |MySQL equivalent |Parameters | |-----------------------------------------|--------------------------------------------|----------------------------------------------------------------------------------------------------------------------------------------------------------------------| |types.bit(M) |BIT[(M)] |M: length [INT] | |types.boolean() |BOOLEAN |- | |types.tinyint(M, UNSIGNED, ZEROFILL) |TINYINT[(M)] [UNSIGNED] [ZEROFILL] |M: length [INT], UNSIGNED: whether this number is unsigned [BOOLEAN], ZEROFILL: whether you want to fill with zeros [BOOLEAN] | |types.smallint(M, UNSIGNED, ZEROFILL) |SMALLINT[(M)] [UNSIGNED] [ZEROFILL] |M: length [INT], UNSIGNED: whether this number is unsigned [BOOLEAN], ZEROFILL: whether you want to fill with zeros [BOOLEAN] | |types.mediumint(M, UNSIGNED, ZEROFILL) |MEDIUMINT[(M)] [UNSIGNED] [ZEROFILL] |M: length [INT], UNSIGNED: whether this number is unsigned [BOOLEAN], ZEROFILL: whether you want to fill with zeros [BOOLEAN] | |types.int(M, UNSIGNED, ZEROFILL) |INT[(M)] [UNSIGNED] [ZEROFILL] |M: length [INT], UNSIGNED: whether this number is unsigned [BOOLEAN], ZEROFILL: whether you want to fill with zeros [BOOLEAN] | |types.bigint(M, UNSIGNED, ZEROFILL) |BIGINT[(M)] [UNSIGNED] [ZEROFILL] |M: length [INT], UNSIGNED: whether this number is unsigned [BOOLEAN], ZEROFILL: whether you want to fill with zeros [BOOLEAN] | |types.decimal(M, D, UNSIGNED, ZEROFILL)|DECIMAL(M, D) [UNSIGNED] [ZEROFILL] |M: integer digits [INT], D: decimal digits [INT], UNSIGNED: whether this number is unsigned [BOOLEAN], ZEROFILL: whether you want to fill with zeros [BOOLEAN]| |types.float(UNSIGNED, ZEROFILL) |FLOAT [UNSIGNED] [ZEROFILL] |UNSIGNED: whether this number is unsigned [BOOLEAN], ZEROFILL: whether you want to fill with zeros [BOOLEAN] | |types.double(UNSIGNED, ZEROFILL) |DOUBLE [UNSIGNED] [ZEROFILL] |UNSIGNED: whether this number is unsigned [BOOLEAN], ZEROFILL: whether you want to fill with zeros [BOOLEAN] |

String types

Reference: https://dev.mysql.com/doc/refman/5.7/en/string-type-overview.html.

|Column type |MySQL equivalent |Parameters | |-----------------------------|-----------------------|----------------------------------------| |types.char(M) |CHAR[(M)] |M: length [INT] | |types.varchar(M) |VARCHAR(M) |M: length [INT] (default is 65535) | |types.binary(M) |BINARY[(M)] |M: length [INT] | |types.varbinary(M) |VARBINARY[(M)] |M: length [INT] | |types.tinyblob() |TINYBLOB |- | |types.mediumblob() |MEDIUMBLOB |- | |types.blob(M) |BLOB[(M)] |M: length [INT] | |types.longblob() |LONGBLOB |- | |types.tinytext() |TINYTEXT |- | |types.mediumtext() |MEDIUMTEXT |- | |types.text(M) |TEXT[(M)] |M: length [INT] | |types.longtext() |LONGTEXT |- | |types.enum(VAL1, VAL2, ...)|ENUM(VAL1, VAL2, ...)|Values for the enum (max.65535 elements)| |types.set(VAL1, VAL2, ...) |SET(VAL1, VAL2, ...) |Values for the set (max. 64 elements) |

Date and time types

Reference: https://dev.mysql.com/doc/refman/5.7/en/date-and-time-type-overview.html.

|Column type |MySQL equivalent |Parameters | |----------------------|------------------|-----------------------------------------| |types.date() |DATE |- | |types.datetime(fsp) |DATETIME[(fsp)] |fsp: fractional seconds precision [INT]| |types.timestamp(fsp)|TIMESTAMP[(fsp)]|fsp: fractional seconds precision [INT]| |types.time(fsp) |TIME[(fsp)] |fsp: fractional seconds precision [INT]| |types.year() |YEAR |- |

Functions that define colmun options

Defined in datasets/options.js, this object contains functions that translate Javascript column options into MySQL ones.

If you required nodeddl options as told in previous step (const options = nodeddl.options), the syntax would be the following:

|Column option |MySQL equivalent |Description | |--------------------------------|---------------------------------------------------------------------------|---------------------------------| |options.default(default_value)|DEFAULT default_value or DEFAULT NULL in case default_value is null|Default value for the column | |options.allow_null() |NULL (DEPRECATED) |Allow NULL values | |options.not_null() |NOT NULL |Don't allow NULL values | |options.unique() |UNIQUE |Don't allow duplicates | |options.auto_increment() |AUTO_INCREMENT |Auto increment value on new entry| |options.comment(comment_text) |COMMENT "comment_text" |Comment | |options.character_set(charset)|CHARACTER SET charset |Character set of a string column | |options.collate(collation) |COLLATE collation |Collation of a setring column | |options.on_update(value) |ON UPDATE value |Default value for the column that change in every update |

Output

You will be informed in the console about the progress and the execution status of each step. If any of the queries fail, you will be told about the error found.

The following is an example of output messages for database_definition.dist.js in verbose mode: