Sequelize utility code (@keeex/utils-sequelize)

• Gitea
• SonarQube
• API
• Changelog

Generic functions and code for Sequelize shared among multiple projects. This library provides the following features:

• Initialization of sequelize and loading of defined models using standardized definitions
• Migration system that supports transactions
• Helper for common migration operations
• Helper for transactions
• Helper for using Postgresql notification mechanism

This documentation mainly provides usage examples on how to use most features. The library is written in TypeScript and the JsDoc should be enough to fill the gap left by this README.

Extra concepts

When applicable this library follows Sequelize conventions, naming and expected types.

Here are the outlines of the additions provided to use this library over Sequelize.

• models are defined using a standardized interface, that describes:
  • model name
  • standard attributes (id, createdAt, updatedAt…)
  • attributes
  • associations
• migrations are defined in terms of "up" and "down" operations, similar to usual tools. However, they are defined using an higher level function call that accepts a transaction parameter to properly handles rollback if a migration fail at some point.

Model definition

Each model is defined using a single object of type ModelInfo. Here's an example on how to define two basic model to mimic a phonebook with contact groups:

import Sequelize from "sequelize";
import {ModelInfo} from "@keeex/utils-sequelize/models/types.js";
import {throughTableInfo} from "@keeex/utils-sequelize/models/tables.js";

export const contactModelInfo: ModelInfo = {
  name: "Contact",
  attributes: {
    name: {
      type: Sequelize.DataTypes.STRING,
      allowNull: false,
    },
    phone: Sequelize.DataTypes.STRING,
  },
  associations: [{
    type: "belongsToMany",
    target: "Group",
    options: {through: "ContactGroup"},
  }],
};

export const groupModelInfo: ModelInfo = {
  name: "Group",
  attributes: {
    name: {
      type: Sequelize.DataTypes.STRING,
      allowNull: false,
    },
  },
  associations: [{
    type: "belongsToMany",
    target: "Contact",
    options: {through: "ContactGroup"},
  }],
};

export const contactGroupInfo: ModelInfo = throughTableInfo({
  ref1: {model: contactModelInfo},
  ref2: {model: groupModelInfo},
});

The above example provides the definition for three models: Contact, Group and the pivot table linking the two ContactGroup. These model definitions can then be used to both initialize Sequelize with a list of usable models and to generate migrations to create the tables associated with them.

The default values also create the id, createdAt and updatedAt attributes. The id attribute is a BIGINT, and is handled as a string by sequelize.

There is an additional helper called throughTableInfo() that can create a basic "through" table for N:M relations (for example, belongs to many ones). This helper returns a ModelInfo object that can be used as usual.

There is also an helper to define foreign keys named foreignKey() which helps define foreign key referencing another model.

Migrations

Migrations are usually scripts that contain a single migrations, and all these files are held in a single directory. Their name is important, as it is used to determine if a migration was already run or not.

The migration expected for this library follows the same pattern as migrations from the old sequelize-cli tool, meaning that a single migration file exports an object with the two properties up and down. These properties are both a function that takes a sequelize's QueryInterface instance to interact with the database.

An addition to this basic scheme is the function mergeAllMigrations() that is able to merge multiple separate migrations steps and run them under a single transaction. The parameter of mergeAllMigrations() is an array of transaction-based migration. A transaction-based migration is defined this way:

const transactionBasedMigration: TransactionMigration = transaction => ({
  up: queryInterface => {},
  down: queryInterface => {},
});

There are also a handful of helpers that directly returns TransactionMigration objects for most common table operations.

Running these migrations is usually done by running the migrationScript() function.

Migrations replacement

At some point it might be interesting to discard old migrations in favor of new ones. A migration object exported by a migration file can additionally have a property named replaces which is an array of strings, being the name of the migrations it replaces.

It should be safe to leave the old migrations in the code; the logic will see that they are replaced and skip them accordingly.

Note that the replaces array indicates that all the older migrations are replaced; if only some of them were applied, the remaining ones will be applied, to keep the system consistent. A migration that replaces other must always bring the database to the same state as the migrations it replaces.

Sequelize initialization

Initializing Sequelize is done by providing both the basic connection configuration and the list of models to use.

Automatic tool

If you have a directory containing all ModelInfo, where each file exports one model, you can take advantage of the automatic generation tool to create the TypeScript interfaces and initialization code:

npx seqpg_modelgen --output src/server/db/models src/server/db/modelsinfo

The above command will take models in src/server/db/modelsinfo/*.ts and generate appropriate interfaces and initialization code in src/server/db/models/*.ts (including index.ts).

To use the generated DB instance import from the index.ts file. The function exported by the generated index must be called with the appropriate sequelize configuration. The recommended way is to have a module that imports the generated files and setup the configuration, and use that as the general database import instead.

Manual definitions

You have to define interfaces for all models, then provide them to the initialization function as a generic template.

Here is the example for only the Contact model:

import {
  BelongsToManyGetAssociationsMixin,
  BelongsToManySetAssociationsMixin,
  BelongsToManyAddAssociationsMixin,
  BelongsToManyAddAssociationMixin,
  BelongsToManyCreateAssociationMixin,
  BelongsToManyRemoveAssociationMixin,
  BelongsToManyRemoveAssociationsMixin,
  BelongsToManyHasAssociationMixin,
  BelongsToManyHasAssociationsMixin,
  BelongsToManyCountAssociationsMixin,
  Model,
} from "sequelize";

export const contactModelInfo: ModelInfo = {/* ... stuff from example above */};

interface Attributes {
  id: string;
  createdAt: Date;
  updatedAt: Date;
  name: string;
  phone: string | null;
}

interface CreationAttributes {
  id?: string;
  createdAt?: Date;
  updatedAt?: Date;
  name: string;
  phone?: string | null;
}

export interface ContactInstance extends Model<Attributes, CreationAttributes>, Attributes {
  getGroups: BelongsToManyGetAssociationsMixin<GroupInstance>;
  setGroups: BelongsToManySetAssociationsMixin<GroupInstance, number>;
  addGroups: BelongsToManyAddAssociationsMixin<GroupInstance, number>;
  addGroup: BelongsToManyAddAssociationMixin<GroupInstance, number>;
  createGroup: BelongsToManyCreateAssociationMixin<GroupInstance>;
  removeGroup: BelongsToManyRemoveAssociationMixin<GroupInstance, number>;
  removeGroups: BelongsToManyRemoveAssociationsMixin<GroupInstance, number>;
  hasGroup: BelongsToManyHasAssociationMixin<GroupInstance, number>;
  hasGroups: BelongsToManyHasAssociationsMixin<GroupInstance, number>;
  countGroups: BelongsToManyCountAssociationsMixin;
}

The initialization is:

import initDb from "@keeex/utils-sequelize/init.js";
import {ModelStaticEx} from "@keeex/utils-sequelize/types.js";
import {getDefinitionFromInfo} from "@keeex/utils-sequelize/models/tables.js";
import sequelizeConfig from "./db_config.js";
import {
  contactModelInfo,
  ContactInstance,
  groupModelInfo,
  GroupInstance,
  contactGroupInfo,
  ContactGroupInstance,
} from "./models.js";

const modelsDefinitions = [
  contactModelInfo,
  groupModelInfo,
  contactGroupInfo,
];

export interface Models {
  Contact: ModelStaticEx<ContactInstance>;
  Group: ModelStaticEx<GroupInstance>;
  ContactGroup: ModelStaticEx<ContactGroupInstance>;
}

export default await initDb<Models>(
  {
    sequelizeConfig,
    modelsDefinitions,
  },
);

Sequelize configuration

The sequelize configuration must be passed appropriately to the various init or migration functions. The configuration object must conform to what Sequelize expects, so it should include things like dialect and such. The logging property of the sequelize configuration is handled by @keeex/utils-sequelize. If true is used, it will use the provided logger (fallback to the default logger) and output queries as info. If false (or nothing) is used, it will log as debug. If no custom logger is provided, it will use @keeex/log with the sequelize tags.

Initialization and reinitialization

The object returned by initDb() have an init() and close() method. These, accordingly, close and reopen the database connection. They can be called repeatedly with no ill-effect.

Migration helpers

Migration file name

Although migration names are free named, it is highly recommended to order them lexicographically so that their execution order is clear. The suggested naming scheme is YYYYMMDD_##_name.ts.

Creating new migration files can be done using the helper script seqpg_migrationgen.

Basic migrations helpers

As describe above, this library provides the mergeAllMigrations() function that supports providing transaction-based migrations.

In addition to being able to define migrations by hand, the library also provides a handful of helpers:

• addConstraint()/removeConstraint(): add SQL constraints
• addAttribute()/removeAttribute(): add a new attribute on a table
• addAttributes()/removeAttributes(): add multiple attributes on a table
• addAttributeFromBelongsTo()/removeAttributeFromBelongsTo(): add an attribute based on a belongs to association
• renameAttribute(): rename an existing attribute
• changeAttribute(): change the properties of an existing attribute
• changeEnum(): change the possible values of an enum
• createTableFromInfo(): create a table matching a model info description
• updateAttributeToBigInt(): migrate an attribute from integer to bigint

There is also a reverse() function that can take an existing transaction-based migration and swap up and down.

Data migration and "frozen" models

When running data migration it can be useful to have access to existing models, both to read and update values. Sequelize takes a lazy approach to model; there are no check in place to ensure the table and models are 100% in sync; checks only happens when querying or writing. We can take advantage of this by mixing table updates and using partial models to migrate data.

As a simple example, if we have a model with two attributes "first name" and "last name" and we want to migrate towards only having "name", merging existing data, we could follow these steps:

• add the "name" attribute as a nullable string
• load an intermediate version of the model that have the three attributes first name, last name and name
• perform the migration over all rows by setting name to last_name + first_name
• remove the first name and last name attributes
• change the constraint on name to not be nullable

All the attribute manipulation can be done using helpers and the data migration can be done by loading a frozen version of our model.

The full example would be:

import Sequelize, {Model, Op} from "sequelize";
import {mergeAllMigrations} from "@keeex/utils-sequelize/migrations/index.js";
import {addAttribute, changeAttribute, removeAttributes} from "@keeex/utils-sequelize/migrations/attributes.js";
import {getTableNameFromInfo} from "@keeex/utils-sequelize/models/utils.js";
import {TransactionMigration} from "@keeex/utils-sequelize/migrations/types.js";
import {loadFrozenModels} from "@keeex/utils-sequelize/migrations/freeze.js";
import {ModelStaticEx} from "./types.js";

const tempModel = {
  name: "Contact",
  attributes: {
    firstName: Sequelize.DataTypes.STRING,
    lastName: Sequelize.DataTypes.STRING,
    name: Sequelize.DataTypes.STRING,
  },
};

interface Attributes {
  firstName: string | null;
  lastName: string | null;
  name: string | null;
}

interface CreationAttributes {
  firstName?: string | null;
  lastName?: string | null;
  name?: string | null;
}

export interface ContactInstance extends Model<Attributes, CreationAttributes>, Attributes {
}

interface Models {
  Contact: ModelStaticEx<ContactInstance>;
}

const tableName = getTableNameFromInfo(tempModel);

const migrateNames: TransactionMigration = transaction => ({
  up: async queryInterface => {
    const frozenModels = loadFrozenModels<Models>(queryInterface, [tempModel]);
    let candidate = await frozenModels.Contact.findOne(
      {where: {name: {[Op.eq]: null}}, transaction},
    );
    while (candidate !== null) {
      candidate.name = `${candidate.lastName ?? ""} ${candidate.firstName ?? ""}`;
      await candidate.save({transaction});
      candidate = await frozenModels.Contact.findOne(
        {where: {name: {[Op.eq]: null}}, transaction},
      );
    }
  },
  down: async queryInterface => {
    const frozenModels = loadFrozenModels<Models>(queryInterface, [tempModel]);
    let candidate = await frozenModels.Contact.findOne(
      {where: {name: {[Op.ne]: null}}, transaction},
    );
    while (candidate !== null) {
      const split = (candidate.name ?? "").split(" ");
      candidate.lastName = split[0] ?? "";
      candidate.firstName = split[1] ?? "";
      candidate.name = null;
      await candidate.save({transaction});
      candidate = await frozenModels.Contact.findOne(
        {where: {name: {[Op.ne]: null}}, transaction},
      );
    }
  },
});

export default mergeAllMigrations([
  changeAttribute(
    tableName,
    "firstName",
    {type: tempModel.attributes.firstName, allowNull: false},
    {type: tempModel.attributes.firstName, allowNull: true},
  ),
  changeAttribute(
    tableName,
    "lastName",
    {type: tempModel.attributes.lastName, allowNull: false},
    {type: tempModel.attributes.lastName, allowNull: true},
  ),
  addAttribute(tableName, "name", tempModel.attributes.name),
  migrateNames,
  removeAttributes(
    tableName,
    {firstName: tempModel.attributes.firstName, lastName: tempModel.attributes.lastName},
  ),
  changeAttribute(
    tableName,
    "name",
    {type: tempModel.attributes.name, allowNull: true},
    {type: tempModel.attributes.name, allowNull: false},
  ),
]);

Note that in this case, we can skip non important attributes on the model, as well as associations as long as we don't need them. This example is very simplified, and only one way to do data migrations; usual methods remains available, such as doing bulk update through queryInterface and the like. The transaction object is passed through as an argument, but the migration system uses the same system as regular Sequelize, so it is possible to omit it in most calls.

Utility code

The @keeex/utils-sequelize/findoptions.js import provides functions that helps manipulating and constructing parameters for findAll() etc.

Nesting transactions

Before Sequelize@7 gets released, it is problematic to nest transactions calls. However, code splitting and reusing can lead to situations where a function tries to start a transaction while already in one.

To handle these cases gracefully, a transaction() function is provided on the object returned by the init function. This call behave like the regular sequelize's transaction() call for managed transactions, as you must pass it a callback that will operate in the transaction.

Using this helper, if any of the call (the original one or the nested ones) throws, the transaction will rollback and all further DB calls while in that transaction will fail.

This call will be updated when switching to Sequelize@7 to use the "reuse" mode, which is planned to behave the same way as this helper.

Postgresql notifications

By default when using the postgresql dialect, the notification mechanism that works through the database itself is enabled. It allows all connected instances to send and receive basic text messages through named channels. This can be disabled in the settings passed to the initialization function.

If not disabled, a notify property exists on the object returned by the initialization call. It will be an instance of the Notifications class that allows registering message listeners and send messages through its methods.

⚠️ For this to work the dialect in the database config MUST strictly be postgres, it the string is anything else, the db.notifications property will be undefined.

Migration between major versions

Migration from v6 to v7

Code should be 100% compatible with the exception that the import changed. Instead of importing @keeex/js-utils-sequelize/lib/*, remove the lib and rename the package to be @keeex/utils-sequelize/*.

Migration from v5 to v6

When migrating from @keeex/js-utils-sequelize@5 to @keeex/js-utils-sequelize@6 it is necessary to apply a migration to the database schema to update the row identifier type from INTEGER to BIGINT.

To apply this migration, create a migration that directly export migrate5To6() and provide it with all the ModelInfo. It is advised to check the outcome of the migration, as some corner case with foreign keys might be missed.

Other changes include:

• ModelInfo.belongsToAttributes was removed; these attributes are automatically built using the associations
• The nmRelation() helper was removed; instead use throughTableInfo() which integrates better with the rest of the library
• Removed ModelCtorEx in favor of ModelStaticEx to follow sequelize's naming convention
• Removed addField() and all other migrations helper named "*field"; use those named "*attribute" instead