ocbesbn-db-init

This module combines a more general and common setup for initializing a database connection for OpusCapita Business Network. This setup includes registering database models, applying data migrations and importing test data for development purposes.

The module also uses Consul in order to gather its configuration and database-backend data. Though it is possible to customize, it's basic configuration set should be treated as a convention by any developer in order to maintain a common setup across different services.

---

Minimum setup

First got to your local code directory and run:

npm install ocbesbn-db-init

To go with the minimum setup, you need to prepare a couple of things.

1. Consul server

Make sure, a Consul instance is ready, running and accessible through it's default ports. Also make sure, Consul is providing the following configuration keys:

• {{your-service-name}}/db-init/database
• {{your-service-name}}/db-init/user
• {{your-service-name}}/db-init/password
• {{your-service-name}}/db-init/populate-test-data

Configure Consul to provide an endpoint configuration for the key mysql including the required properties host and port.

In case you do not want to set the consul key populate-test-data, please set config.consul.keys.addTestData to null when calling the .init() method.

2. Directories

Create a directory ./src/server/db/models and a directory ./src/server/db/migrations inside the working directory of your service.

If you wish to load models or run data migrations, you can proceed with the sections Creating database models and Applying data migrations.

3. Code!

Go to your code file and put in the minimum setup code.

const db = require('ocbesbn-db-init');
 
// You might want to pass a configuration object to the init method. A list of parameters and their default values
// can be found at the .DefaultConfig module property.
db.init({}).then(console.log);

If everything worked as expected, you will get a bluebird promise as a result, containing the initialized database connection.

---

Default configuration

The default configuration object provides hints about what the module's standard behavior is like. It is mostly recommended to leave most settings as they are and treat them more as general conventions to a common structure in order to maintain a common setup across different services.

{
    mode : process.env.NODE_ENV === 'development' ? this.Mode.Dev : this.Mode.Productive,
    dialect : this.Dialects.MySQL,
    connectionPool : { min : 5, max : 10, idle : 30000 },
    retryTimeout : 500,
    retryCount : 10,
    logger : new Logger({ context : { serviceName : 'db-init' } }),
    events : {
        onBeforeModels : function(db) { },
        onBeforeDataMigration : function(db) { }
    },
    consul : {
        host : 'localhost',
        keys : {
            dbName : 'db-init/database',
            dbUser : 'db-init/user',
            dbPassword : 'db-init/password',
            addTestData : 'db-init/populate-test-data',
        },
    },
    data : {
        registerModels : true,
        modelPaths : [ process.cwd() + '/src/server/db/models' ],
        addTestData : process.env.NODE_ENV !== 'production',
        runDataMigration : true,
        migrationDataPath: process.cwd() + '/src/server/db/migrations'
    },
    sequelize : {
    }
}

---

Creating database models

By convention, database models should be put into the ./src/server/db/models directory. This means, that the models directory is interpreted as a module. So the easiest way to create a simple model registration, is, creating an index.js file inside the provided directory. The minimum setup to run this configuration would look like this:

const Promise = require('bluebird');
 
module.exports.init = function(db, config)
{
    return Promise.resolve();
}

This, of course, does not apply a lot of the magic we as developers like so much. So let's go on and create an absolute basic database model for a user.

const Promise = require('bluebird');
const Sequelize = require('sequelize');
 
module.exports.init = function(db, config)
{
    db.define('User', {
        id : {
            type : Sequelize.BIGINT(),
            primaryKey : true,
            autoIncrement: true,
            allowNull : false
        },
        username : {
            type : Sequelize.STRING(64),
            allowNull : false
        }
    }, {
        timestamps : false
    });
 
    return Promise.resolve();
}

If the required Users-table does not already exist, proceed with the steps described under Applying data migrations in order to initially create the database table.

---

Applying data migrations

Data migrations help you, to keep your database structure up-to-date. This includes the process of initially creating required data structures. Another part of the data migration concept used here consists of adding optional test data.

In the ocbesbn-db-init module, migration files should be put inside the ./src/server/db/migrations directory. Each .js file in that directory is processed in alphabetical order. To differentiate between structural changes and test data, a structure file always has to end with .main.js while a test data file always has to end with .test.js.

This module only applies pending migrations. Once a migration file has successfully been processed, it is not loaded a second time. To identify which files have successfully been processed, a migration's filename is used.

A migration file alway has to publish an up() and a down() method each taking a db and a config parameter. If a migration fails, the down() method is called in order to revert all changes made in the up() method.

To initially create the database table for the model defined in Creating database models, the following code would be enough:

const Sequelize = require('sequelize');
 
module.exports.up = function(db, config)
{
    var model = {
        id: {
            type: Sequelize.BIGINT(),
            primaryKey : true,
            autoIncrement: true,
            allowNull : false
        },
        username {
            type : Sequelize.STRING(64),
            allowNull : false
        }
    };
 
    return db.getQueryInterface().createTable('Users', model);
}
 
module.exports.down = function(db, config)
{
    return db.getQueryInterface().dropTable('Users');
}

Test data files can be designed in a similar way by adding data instead of tables. The only mandatory thing here is reliably working up() and down() methods.