@ce-spgi/sequelize v0.1.9

Sequelize

Sequelize is a promise-based Node.js ORM for Postgres, MySQL, MariaDB, SQLite, DB2 and Microsoft SQL Server. It features solid transaction support, relations, eager and lazy loading, read replication and more.

Check the official documentation for more information about Sequelize.

Setup Code

Sequelize needs all the models to be linked to a database connection. The code bellow help to perform this database link and perform the model associations.

import { DatabaseConnect } from '@ce-spgi/sequelize';

const Database = {
  name: '<databaseName>',
  hostname: '<databaseHostname>',
  user: '<databaseUser>',
  password: '<databaseUserPassword>',
  dialect: 'db2' | 'postgres',
  port: parseInt('<databasePort>'),
  connectionPool: 10,
  connectionPoolStart: true
};

DatabaseConnect({
  connection: Database,
  models: [
    require('<Model1Path>'),
    require('<Model2Path>'),
    require('<ModelNPath>'),
  ]
})

Model example

A model should look like this in order to use the setup code.

import { Sequelize, DataTypes, Model } from 'sequelize';
import { jsonColumn, timestamps } from '@ce-spgi/sequelize';
import { Dashboard } from './index';

export interface IOrganizaction {
  id: number;
  name: string;
}

class Organizaction extends Model<IOrganizaction> implements IOrganizaction {
  public id!: number;
  public name!: string;
  public readonly createdAt: Date;
  public readonly updatedAt: Date;
}

const init = (sequelize): void => {
  Organizaction.init(
    {
      id: {
        type: DataTypes.INTEGER,
        primaryKey: true,
        autoIncrement: true,
        field: 'ID'
      },
      name: {
        type: DataTypes.STRING(255),
        allowNull: false,
        field: 'NAME'
      },
      ...timestamps(DataTypes)
    },
    {
      sequelize,
      schema: process.env.DB_SCHEMA,
      tableName: 'MINISTRIES',
      timestamps: true
    }
  );
};

const associations = (models): void => {
  Organizaction.hasMany(models.Project, { as: 'projects', foreignKey: 'OrganizactionId' });
};

export { Organizaction, init, associations };

DB2 Support

In order to use DB2 as database you need to install a custom version of sequelize. You can find this version inside this repository: https://github.com/ibmdb/sequelize.git. For the moment we force a specific version (2.7.4) of ibm_db in order to work with sequelize.

Example

import { Database } from '../env';
import { DatabaseConnect } from '@ce-spgi/sequelize';

export { Dashboard } from './Dashboard';
export { Organizaction } from './Organizaction';
export { Project } from './Project';

export const sequelize = DatabaseConnect({
  connection: Database,
  models: [
    require('./Dashboard'),
    require('./Organizaction'),
    require('./Project'),
  ]
});

Custom Functions/Classes

Paginated Model

Sometimes the amount of data inside a database bigger enough to need pagination. We extend the basic Sequelize model adding a pagiante function that add two more options ({page: number, perPage: number}) to the default findAll function that perform all the required actions to paginate a query.

import { Sequelize, DataTypes } from 'sequelize';
import { jsonColumn, timestamps } from './tools';
import { Dashboard } from './index';
import { ModelPaginate } from '@ce-spgi/sequelize';

export interface IOrganizaction {
  id: number;
  name: string;
}

class Organizaction extends ModelPaginate<IOrganizaction> implements IOrganizaction {
  public id!: number;
  public name!: string;
  public readonly createdAt: Date;
  public readonly updatedAt: Date;
}

await Organizaction.paginate({ page: 1, perPage: 10 })

Data types

JsonColumns

In order to save JSON values in database as string and use it as JSON in code, you should use define the column calling the function jsonColumn. Check the example for more information.

import { DataTypes, Model } from 'sequelize';
import { jsonColumn } from '@ce-spgi/sequelize';

export interface IDashboard {
  id: number;
  spec: string;
}

class Dashboard extends Model<IDashboard> implements IDashboard {
  public id!: number;
  public spec!: string;
}

const init = (sequelize): void => {
  Dashboard.init(
    {
      id: {
        type: DataTypes.INTEGER,
        primaryKey: true,
        autoIncrement: true,
        field: 'ID'
      },
      ...jsonColumn('spec', {
        type: DataTypes.BLOB,
        allowNull: false,
        field: 'SPEC'
      })
    },
    {
      sequelize,
      schema: process.env.DB_SCHEMA,
      tableName: 'DASHBOARDS',
      timestamps: true
    }
  );
};