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.

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

const Database = {
name: '',
hostname: '',
user: '',
password: '',
dialect: 'db2' | 'postgres',
port: parseInt(''),
connectionPool: 10,
connectionPoolStart: true
};

DatabaseConnect({
connection: Database,
models: [
require(''),
require(''),
require(''),
]
})
`

$3

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

`typescript
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 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 };
`

$3

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.

$3

`typescript
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

$3

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.

`typescript
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 implements IOrganizaction {
public id!: number;
public name!: string;
public readonly createdAt: Date;
public readonly updatedAt: Date;
}

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

$3

#### 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.

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

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

class Dashboard extends Model 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
}
);
};
``