Sequelize Common

This package provides common functionality for services using a relational database using sequelize. Sequelize is a promise-based Node.js ORM for Postgres, MySQL, MariaDB, SQLite and Microsoft SQL Server. It features solid transaction support, relations, eager and lazy loading, read replication and more. We usually use Sequelize with MySQL (AWS RDS Aurora).

Initialization & Connection

The following snippet illustrates how a sequelize database can be initialized and connected to a service. In summary, this does the following:

• the database connection is set up
• models (rawModels) are connected to the sequelize instance, and associations between them are created.
• database migrations from the specified path with the configured naming pattern are applied

const { SequelizeDb } = require('@plmtest/cosmos-common-db-sequelize')
const rawModels = require('./models')

new SequelizeDb({ configuration, logger })
  .connect(rawModels)
  .migrate({
    // Skip the initial migration if we are in a different environment than local where we start from an empty DB
    migrations: config.env !== 'local' ? { from: '20190204090956-initial.js' } : undefined,
    umzug: {
      path: `${__dirname}/migrations`,
      pattern: /\.js$/
    }
  })
  .then(({ models, hasDbConnection }) => {
    // your actual application code goes here
  })

The configuration that is passed into new SequelizeDb() could look like this:

{
  username: process.env.DB_USERNAME,
  password: process.env.DB_PASSWORD,
  database: process.env.DB_NAME,
  host: process.env.DB_HOST,
  port: process.env.DB_PORT,
  dialect: 'mysql',
  pool: {
    max: 50, // Maximum number of connection in pool (default: 5)
    min: 0, // Minimum number of connection in pool (default: 0)
    idle: 10000, // The maximum time, in milliseconds, that a connection can be idle before being released (default: 10000)
    acquire: 60000, // The maximum time, in milliseconds, that pool will try to get connection before throwing error (default: 60000)
    evict: 1000 // The time interval, in milliseconds, after which sequelize-pool will remove idle connections (default: 1000)
  },
  retry: {
    match: [ // Only retry a query if the error matches one of these strings
      /SequelizeConnectionError/,
      /SequelizeConnectionRefusedError/,
      /SequelizeHostNotFoundError/,
      /SequelizeHostNotReachableError/,
      /SequelizeInvalidConnectionError/,
      /SequelizeConnectionTimedOutError/,
      /TimeoutError/
    ],
    max: 10 // How many times a failing query is automatically retried. Set to 0 to disable retrying on SQL_BUSY error
  }
}

Data Migration

This package provides data migration functionality that is suitable for replicated services, i.e., that run multiple instances that are all connected to the same database.

Data migration is based on Sequelize and Umzug.

General Concept

The basic idea behind this part of the package is to wrap the programmatic execution of Sequelize migrations done using Umzug in a mechanism for locking.

Before a service executes a data migration, it attempts to create a "lock" entry in a "mutex" table. If that is possible, the service continues with the data migration. Otherwise, it just exits so that Kubernetes will reschedule the service instance.

Once a service finished the data migration successfully, the lock is removed from the mutex.

If something fails when the service attempts to release the lock, it just continues normal execution because locks in the "mutex" table sort of expire: In case a service encounters an old "lock" entry that is older than a certain lock timeout period, it is removed, and the new service continues the execution.

Configuration

Database migration uses two main configuration objects: migrations and umzug.

What is defined as migrations will be passed into the umzug.up() call that executes pending migrations. It can be used to select which migrations to apply, e.g., skip certain migrations or only execute a defined set of migrations. For further details see the Umzug documentation.

The umzug object defines the parameters to initialize Umzug, i.e., the path to the migration files and a pattern identifies migration files. By default, all .js files inside /.migrations are considered database migration files.

Base Service

This package provides a base service implementation for CRUD services. The following operations are supported:

• create(attributes, req)
• readAll(params, req)
• readOne(id, params, req)
• update(id, attributes, req, params)
• delete(id, req)
• activate(id, req)
• deactivate(id, req)
• archive(id, req)
• unarchive(id, req)

These functions can either be overridden or extended in custom classes extending the base service.

The below snippet illustrates how a new service that extends the base service can be created:

const { SequelizeBaseService } = require('@plmtest/cosmos-common-db-sequelize').Services

class MyService extends SequelizeBaseService {
  constructor ({ models, logger, remotes, services }) {
    super({
      baseIncludes: [
        {
          as: 'myIncludedModel',
          model: models.MyModelToInclude
        }
      ],
      models,
      logger,
      model: models.MyModel,
      remotes,
      services
    })
  }
}

Error Handling

The package also provides means for error handling, i.e., a utility that maps Sequelize database errors to corresponding HTTP errors. If the error to be handled is not a database error, it is rethrown.

NOTE: This requires @plmtest/cosmos-service-builder as a peer dependency.

The snippet below illustrates the usage of the error handler:

const { SequelizeErrors } = require('@plmtest/cosmos-common-db-sequelize').Errors

async doSomething () {
  try {
    ...
  } catch (err) {
    throw SequelizeErrors.handleSequelizeError(err, { modelName: this.modelName, message: { reference: 'The expected reference does not exist' } })
  }
}

The message property in the handleSequelizeError call allows for custom error messages for these cases:

• default: the message that is returned if no more specific message is returned (same as providing a string for message)
• conflict: the message that is returned if unique constraints in the database fail
• reference: the message that is returned for failing foreign key constraints

Further Information

• Sequelize documentation (v5)