@opuscapita/db-init

Starting with version 2.0 some small things have changed:

• Umzug is no longer used to run migrations but everything should work as before.
• The methods init(), up() and down() of the module registration and migration files do not have to return bluebird promises anymore. JavaScript promises or async/await can be used now.
• Some default config values for init() have been changed: connectionPool.max set from 10 to 25, retryTimeout from 500 to 1000, retryCount from 10 to 50, logger.context.serviceName from db-init to current service name.
• It is now possible to have triggers running db-init connections .

This is a general module for database connection initialization and management. It includes database model registration, data migrations and importing test data for development purposes.

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

The module follows a singleton based approach giving the same, cached database connection for each unique configuration passed to the init() method.

Minimum setup

First go to your local code directory and run:

npm install @opuscapita/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 its 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 want to run your system on an endpoint configuration that is not called mysql you can either set the configuration consul.dbEndpoint or the consul key {{your-service-name}}/db-init/service-name.

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('@opuscapita/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(db => this.db = db);

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

Calling init() as second time with the same configuration passed, db-init will return the same (cached) database instance as it hat been initialized before.

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 : 25, idle : 30000 },
    retryTimeout : 1000,
    retryCount : 50,
    logger : new Logger({ context : { serviceName : configService.serviceName } }),
    events : {
        onBeforeModels : function(db) { },
        onBeforeDataMigration : function(db) { }
    },
    consul : {
        host : 'consul',
        dbEndpoint : 'mysql',
        keys : {
            dbServiceName : 'db-init/service-name',
            dbName : 'db-init/database',
            dbUser : 'db-init/user',
            dbPassword : 'db-init/password',
            addTestData : 'db-init/populate-test-data',
            migrationLockTimeoutms: 'db-init/migration-lock-timeout-ms',
            migrationLockIntervalms: 'db-init/migration-lock-interval-ms'
        },
    },
    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:

module.exports.init = async function(db, config)
{
    ...
}

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 Sequelize = require('sequelize');

module.exports.init = async 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
    });
}

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 @opuscapita/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:

module.exports.up = async function(db, config)
{
    const 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 = async 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.