Sequelize ORM BaseRepository and connection factory
npm install @tsrt/sequelize   
Basic Sequlize Connection Factory and BaseRepository build on top of sequelize (v5*).
Could be used (and i'm personally recommend) with sequelize-typescript.
Until version 1.0.0 Api should be considered as unstable and may be changed.
So prefer using exact version instead of version with ~ or ^.
More examples (w/ models and all methods) could be found in tests.
#### Databse connection factory
For example using sequelize-typescript and PostgreSql:
``ts
import { SequelizeOptions } from 'sequelize-typescript'
import { Database } from '@tsrt/sequelize';
import * as Models from 'path/to/models';
async function bootstrap(): Promise
const sequelizeOptions: SequelizeOptions = {
logging: false,
dialect: 'postgres',
database: 'database',
username: 'username',
password: 'password',
host: 'localhost',
port: 5432,
models: Database.getModelsList(Models),
};
const database1 = await Database.createConnection(Sequelize, sequelizeOptions);
const database2 = await Database.createConnection(Sequelize, { ... });
// Close connection:
// await Database.closeConnection(database1);
// Use current connection (for example, models):
// console.log(database1.connection.models);
// ... start application
}
`
#### Repository usage
##### Example 1
`ts
import { BaseRepository } from '@tsrt/sequelize';
import { SomeModel, ISomeModelEntity } from 'path/to/models';
export const SomeModelRepository = new BaseRepository
// ... later in code
class SomeService {
public async someMethod(id: number): Promise
// ... some logic
const entity = await SomeModelRepository.read({
skip: 10,
limit: 100,
select: ['id', 'title'],
sort: 'title:asc',
include: ['nested', 'nested.deeply', { model: OtherModel, as: 'otherModel' }],
where: {
$or: {
id: { $gt: 10 },
title: { $iLike: '%hello%' },
'nested.id': 10,
},
},
}, id);
// ... some logic
}
}
`
##### Example 2
Imagine usage in express:
`ts`
router.get('/entities', async (req, res) => {
const { skip, limit, sort, select, include, filter } = req.query;
try {
const result = await SomeModelRepository.read({ skip, limit, sort, select, include, filter });
res.status(200).send(result); // { total: number, nextSkip: number, value: [] };
} catch (err) {
res.status(err.status).send(err);
}
});
##### Enhanced usage of some query params
`ts
SomeRepository.read({
limit: 1, // -> basic limit
limit: 'none', // -> will remove limit at all, as by default it is used limit = 10. Default limit could be changed when instantiating BaseRepository by providing options.
sort: 'id:asc,title:desc', // -> [['id', 'ASC'], ['title', 'DESC']]
sort: ['id:asc', 'title:desc'], // -> same as example above
sort: ['nested.id:asc'], // -> [['nested', 'id', 'ASC']] - so it is possible to sort by nested includes.
/**
* In filter and where options we can provide any of Sequelize compatible query operators$
* prefixed with .
* So we can compose complex queries here.
* @example: { id: { $eq: 1 } }
*
* @see https://sequelize.org/master/manual/model-querying-basics.html#operators
*/
filter: {
$and: {
id: { $eq: 1 },
$or: {
title: { $iLike: '%hello%' },
age: { $gt: 18 },
'nested.id': { $gt: { 18 } }, // Yes, we can filter by nested columns
}
}
},
// or where { ... },
include: 'nested1, nested2', // -> [{ duplicating: false, association: 'nested1' }, { duplicating: false, association: 'nested2' }]
include: ['nested1', 'nested2'], // -> same as example above
include: ['nested1.deepNested1', 'nested2'], // -> [{ duplicating: false, association: 'nested1', include: [{ duplicating: false, association: 'deepNested1' }] }, { duplicating: false, association: 'nested2' }]
include: [{ association: 'nested1', duplicating: true, ... }] // -> here we can use all Sequelize appropriate options.
})
`
##### Left join + limit and count problem solved
This problem is described here.
Problem - we cannot achieve expected result using include and limit for FindAndCountAll.duplicating: false
In different cases we can play w/ , subQuery: false sequelize params, but:
- here we need universal solution.
- still there is a problem w/ incorrect count (total amount of carthesian product).
This problem is solved under the hood of BaseRepository.
All methods will create its own transaction, execute all queries inside it and commits/rollback at the end.
If method provided w/ transaction manually - it will execute all queries inside it and wiil __NOT__ commit/rollback at the end, it should be done manually by developer when needed.
In such a way it is possible to execute a bunch of different queries inside 1 transaction.
`ts
interface IBaseRepository<
/* DatabaseEntity interface. Common return type and body type for update/create methods. /
I extends GenericObject & O,
/**
* Entity specific interface without database specific fields like pk, deletedAt.
* Common body type for update/create methods.
* Here could be defined virtual properties such as associations primaryKeys array.
* Could be used to improve TS typechecking and intellisense.
*/
R = Partial,
/* Interface for Entity with ordering properties: primaryKey and orderKey. /
O extends GenericObject = IOrderingItemDefault,
/* Repository Model. /
M extends Model = Model
> {
/* A reference to Model, provided for BaseRepository constructor. Could be used as native sequelize Active Record pattern. /
model: Model;
/**
* Creates a transaction or executes a transaction callback.
*
* @param [options] - Transactions options.
* @param [cb] - Transaction callback to be executed for managed transactions.
*
* @see https://sequelize.org/master/manual/transactions
*/
createTransaction
/**
* Creates entity w/ additional ICreateOptions options.
*
* @param body - Entity data.
* @param [customOptions] - Custom options for entity creation.
* @param [through] - Data to add into association for Many to Many relations.
*
* Under the hood can create relations if they are define in model definition.
* For example if model has a BelongsToMany association w/ model, and association alias is types.types
* Is is possible to provide next body (types array is list of ids):
*
* @example:
* create({ name: 'test', types: [1, 2] }, { ... }, { defaultType: 'test' }).
* So it will create entity w/ name 'test', created references w/ and inserts { defaultType: 'test' } into those references.
*/
create(body: Partial, createOptions?: ICreateOptions, through?: GenericObject): Promise;
/**
* Creates multiple entities from provided list (inside transaction).
*
* @param body - List of entities to be created.
* @param [createOptions] - Custom options for record creation. Include QueryOptions and CreateOptions.
* @param [through] - Data to add into association for Many to Many relations.
*/
bulkCreate(body: Array
/**
* Alias for common read operations. Works for both readOne and readMany.
*
* @param [readOptionsOrPk] - Optional read options or primaryKey.
* @param [readOptions] - Reqd options if primaryKey provided as first arument.
*
* If entity has orderKey column, will ensure for each request that all entities for similar conditionsorderKey
* have valid (no NULL(s) and duplicates).
*/
read(readOptionsOrPk?: number | string | IReadOptions, readOptions?: IReadOptions): Promise>
/**
* Reads one record (by pk or options).
*
* @param readOptionsOrPk - Read options or primaryKey.
* @param [readOptions] - Reqd options if primaryKey provided as first arument.
*
* If entity has orderKey column, will ensure for each request that all entities for similar conditionsorderKey
* have valid (no NULL(s) and duplicates).
*/
readOne(readOptionsOrPk: number | string | IReadOptions, pk?: number | string): Promise
/**
* Reads multiple entities and returns paged response.
*
* @param [readOptions] - Optional read options.
*
* If entity has orderKey column, will ensure for each request that all entities for similar conditionsorderKey
* have valid (no NULL(s) and duplicates).
*/
readMany(readOptions: IReadOptions = { }): Promise
/**
* Updates entity.
*
* @param body - Data to be updated.
* @param pk - Entity primaryKey or updateOptions.
* @param [updateOptions] - Custom options for updating operation or through.
* @param [through] - Data to add into association for Many to Many relations.
*
* Creates associations same as create method.
*/
update(body: Partial, pk: number | string | IUpdateOptions, updateOptions?: IUpdateOptions, through?: GenericObject): Promise;
/**
* Updates multiple entities from provided list.
*
* @param body - List of entities to be updated.
* @param [updateOptions] - Custom options for updating operation.
* @param [through] - Data to add into association for Many to Many relations.
*/
bulkUpdate(body: Array
/**
* Update entities order (reordering).
*
* @param body - Items with new orders or array of changes
* @param [options] - Additional Query options
* @param [checkPermissions=true] - Whether to check permissions or not
*/
updateItemsOrder
/**
* Deletes entity by pk.
* If paranoid mode is enabled - soft deletes. Alternatively deletes entity totally.
*
* @param deleteOptionsOrPk - Entity primaryKey or deleteOptions.
* @param [deleteOptions] - Custom options for entity deletion if first argument is primaryKey.
*/
delete(deleteOptionsOrPk: string | number | IDeleteOptions, deleteOptions?: IDeleteOptions): Promise
/**
* Soft deletes entity by pk (only if paranoid mode enabled). Alternatively deletes entity totally.
* Alias for delete(pk, { force: false }) or just delete(pk);
*
* @param deleteOptionsOrPk - Entity primaryKey or deleteOptions.
* @param [deleteOptions] - Custom options for entity deletion if first argument is primaryKey.
*/
softDelete(deleteOptionsOrPk: string | number | IDeleteOptions, deleteOptions?: IDeleteOptions): Promise
/**
* Totally deletes entity by pk.
* Alias for delete(pk, { force: true });
*
* @param deleteOptionsOrPk - Entity primaryKey or deleteOptions.
* @param [deleteOptions] - Custom options for entity deletion if first argument is primaryKey.
*/
forceDelete(deleteOptionsOrPk: string | number | IDeleteOptions, deleteOptions?: IDeleteOptions): Promise
/**
* Restores soft deleted entity(-ies).
*
* @param restoreOptionsOrPk - Entity primaryKey or restoreOptions.
* @param [restoreOptions] - Custom options for restore operation if first argument is primaryKey.
*
* @returns restored entity / list of restored entities.
*/
restore(restoreOptionsOrPk: string | number | IRestoreOptions, restoreOptions?: IRestoreOptions): Promise;
}
`
Using hooks, it is possible to extend BaseRepository and provide some extra logic/validations/context.
For example here we can provide some __tenant__ specific context in a multi-tenant system.
##### Example:
`tsquery
export class CustomRepository, IOrderingItem, M extends Model = Model> extends BaseRepository
protected async onAfterQueryBuilt(query?: FindAndCountOptions): Promise
const context = await this.provideContext();
// ... altering original ;
return queryWithContext;
}
protected async onBeforeCreate(body: R, createOptions?: ICreateOptions, through?: GenericObject): Promise
await this.insertContext(body, createOptions);
}
protected async onBeforeBulkCreate(body: R[], createOptions: ICreateOptions): Promise
await this.insertContext(body, createOptions);
}
protected async onBeforeUpdate(
body: Partial
): Promise
await this.insertContext(body, updateoptions);
}
protected async onBeforeInsertAssociations(
_entity: M, _body: Partial
): Promise
await this.insertContext(through, through);
}
protected async onBeforeDelete(deleteOptions: IDeleteOptions, pk?: number | string): Promise
const context = await this.provideContext();
if (someCondition) throw new Error('Cannot delete');
// ...other logic ...
}
protected async insertContext(target: GenericObject | GenericObject[], options?: GenericObject): Promise
const context = await this.provideContext();
if (!context) return;
if (options) options.context = context;
if (!Array.isArray(target)) Object.keys(context).forEach((key) => { target[key] = (context as GenericObject)[key]; });
else target.forEach((item) => Object.keys(context).forEach((key) => { item[key] = (context as GenericObject)[key]; }));
}
protected async provideContext(): Promise
// ...retrieve some context, for example using express-http-context package. Or async_hooks.
}
}
// ... and then
export const SomeModelRepository = new CustomRepository
// ... or with all types for better typization
export const SomeModelRepository = new CustomRepository
// Note, that it is alos possible to set default Ordering type by declaring IBaseOrderingItem for @tsrt/sequelize module.
// Example: declare module '@tsrt/sequelize' { export interface IBaseOrderingItem { pk: number; order?: number; } }
`
##### Hooks
`ts
interface IHooks {
/**
* Hook called after query was built.
*
* @param [parsedQuery] - Previously parsed query params into Sequelize appropriate find and count options.
*
* @note Unlike other hooks should return updated query.
*/
onAfterQueryBuilt(parsedQuery?: FindAndCountOptions): Promise
/**
* Hook which invokes directly before create operation.
*
* @param _body - Body for entity creation.
* @param [_createOptions] - Custom options for record creation.
* @param [_through] - Data to add into association for Many to Many relations.
*/
onBeforeCreate(_body: GenericObject, _createOptions?: ICreateOptions, _through?: GenericObject): Promise
/**
* Hook which invokes directly before bulk create operation.
*
* @param _body - Body for record creation.
* @param [_createOptions] - Custom options for record creation.
* @param [_through] - Data to add into association for Many to Many relations.
*/
onBeforeBulkCreate(_body: GenericObject[], _createOptions?: ICreateOptions, _through?: GenericObject): Promise
/**
* Hook which invokes directly before read operations.
*
* @param [_options] - Read options.
* @param [_pk] - PrimaryKey.
*/
onBeforeRead(_options?: IReadOptions, _pk?: string | number | boolean): Promise
/**
* Hook which invokes directly before update operation.
*
* @param _body - Body for record creation.
* @param _pk - Entity primaryKey or query.
* @param [_updateOptions] - Custom options for record update.
* @param [_through] - Data to add into association for Many to Many relations.
*
* @note _pk arg could be null.
*/
onBeforeUpdate(
_body: GenericObject, _pk: number | string, _updateOptions?: IUpdateOptions, _through?: GenericObject,
): Promise
/**
* Hook which is called before updating items order.
*
* @param _body - Orders changes.
* @param _options - Optional readOptions for getiing range of all items which need to be reordered.
*/
onBeforeUpdateItemsOrder
/**
* Hook which invokes directly before delete operation.
*
* @param [_deleteOptions] - Custom options for record (s) destroy.
* @param [_pk] - primaryKey.
*/
onBeforeDelete(_deleteOptions: IDeleteOptions, _pk?: number | string): Promise
/**
* Hook which fires right before adding associated data.
*
* @param _entity - Previously created / updated entity.
* @param _body - Data / body to find associated data in.
* @param [_insertOptions] - Optional params.
* @param [_through] - Data which should be added into associated entities (for Many to Many relations).
*/
onBeforeInsertAssociations(_entity: M, _body: Partial
/**
* Hook which is called before restoring.
*
* @param _restoreOptions - Restore options.
*/
onBeforeRestore(_restoreOptions: IRestoreOptions, _pk?: number | string): Promise
}
`
##### Default BaseRepository options
`ts`
export const defaultBaseRepositoryConfig: IBaseRepositoryConfig = {
defaults: {
restrictedProperties: ['createdAt', 'updatedAt', 'deletedAt'],
limit: 10,
logError: process.env.NODE_ENV !== 'production',
},
orderingServiceOptions: {
orderKey: 'order',
clampRange: true,
insertAfterOnly: true,
},
};
##### All available options
`ts
import { CreateOptions, UpdateOptions, DestroyOptions, RestoreOptions, WhereAttributeHash, IncludeOptions } from 'sequelize';
type GenericObject
export interface IPagedData
total?: number;
nextSkip?: number;
value: T[];
}
/* Database factory config /
export interface IDatabaseConfig {
/* Whether to sync (Sequelize sync()) after connection established. /
sync?: boolean;
/* Whether to log into console connection info after connection established. /
logConnectionInfo?: boolean;
/**
* Callback, which would be called after connection establised.
* Here it is possible, for example, associate Models, if using pure Sequelize (not sequelize-typescript).
*
* @param sequelize - Sequelize connection.
*/
cbAfterConnected?: (sequelize: Sequelize) => Promise
}
/* Default repository options. /
export interface IBaseRepositoryDefaults {
/**
* Properties, which would ne stripped while update/create operations.
* Default: ['createdAt', 'updatedAt', 'deletedAt'].
*/
restrictedProperties: string[],
/* Defalt limit param for read operations. Default: 10. /
limit: number;
/* Defalt order param for read operations. Default: [primaryKey, 'asc']. /
order: string[];
/* Whether to log BaseRepository errors. /
logError?: boolean;
}
export interface IBaseRepositoryConfig {
/* Default repository options. /
defaults: Partial
/* Config for OrderingService. @see @tsrt/ordering package for details /
orderingServiceConfig: IOrderingServiceConfig;
}
export interface IBaseRepositorySilentQuery {
/**
* Whether to throw an Error if query fails.
* For example while reading/creating/updating data.
*
* @note that error will be throw even if silent: true and it is some validation error, for example incorrect body for update method.
* @default false;
*/
silent?: boolean;
}
export interface IBaseRepositoryOptions extends IBaseRepositorySilentQuery {
/* Limit for Sql query. Applies for limitation of main entity records. @default: 10. /
limit?: number | 'none';
/* Offset for Sql query. @default: 0. /
skip?: number;
/**
* Applies only in case of reading by pk (readOne or read methods) and gives an alias for reading by value if some field.
* @default: model primaryKey.
*/
getBy?: string;
/* Select attributes from main entity to query for. @example: 'pk, title' or ['pk', 'title'] /
select?: string | string[];
/**
* Sorting conditions. Key:value paires.
* @example: 'pk:asc,title:desc' or ['pk:asc', 'title:desc'].
* @default: 'pk:asc'.
*/
sort?: string | string[];
/**
* Associations for eager loading.
*
* Could a string of aliases, define in model definition (for example if come as queryString from client).
* @example: 'nested1, nested2'.
*
* Array of aliases, define in model definition.
* @example: ['nested1', 'nested2'].
*
* Also could be nested in both above cases.
* @example: ['nested1.deepNested1'].
*
* Or list of full IncludeOptions from Sequelize.
* @example: [{ association: 'test' }, { model: SomeModel, as: 'test2', required: false }].
*/
include?: string | Array
/**
* Filtering (aka where) conditions. This one could be shared to get filters from client side.where
* Will be merged and replaced by conditions from property.$
*
* @note - Supports for nested conditions by dot notation.
* @note - Supports all Sequelize operators. Operators should be prefixed with .
* @see https://sequelize.org/master/manual/model-querying-basics.html#operators
*
* @example:
* filter: {
* $or: {
* id: 1,
* title: { $iLike: '%hello%' }
* 'nested.id': 10,
* }
* }
*/
filter?: WhereAttributeHash;
/**
* Where conditions. Has priority over filter property.$
*
* @note - Supports for nested conditions by dot notation.
* @note - Supports all Sequelize operators. Operators should be prefixed with .
* @see https://sequelize.org/master/manual/model-querying-basics.html#operators
*
* @example:
* where: {
* $or: {
* id: 1,
* title: { $iLike: '%hello%' }
* 'nested.id': 10,
* }
* }
*/
where?: WhereAttributeHash;
}
type IBaseRepositoryCroppedOptions = Omit
/**
* Interface for CRUD controller method options, which influence on adding association data and
* response object (whether to create association between tables, or return JOINed result).
*/
export interface IBaseRepositoryExtendedOptions
extends Transactionable, Omit
/* Whether it is necessary to associate (create reference) if reference primaryKeys list provided. Default: true /
associate?: boolean;
/**
* Whether it should replace associatins w/ new reference list (delete and add).
*
* Example for replaceAssociations: true(default):
* Model.update({ id: 1, files: [1, 2, 3] }) -> after this query Model will
* be associated only w/ files w/ ids [1, 2, 3] even if previously it was associted w/ some others.
*
* Example for replaceAssociations: false:
* Model.update({ id: 1, files: [1, 2, 3] }) -> after this query Model will be associted w/ those files
* it was associated before + new unique ids (if they were unique in provided list).
* So if previously Model was associated with [1, 2, 4] Files, after query it will be: [1, 2, 3, 4].
*/
replaceAssociations?: boolean;
/* Whether to return values with associations, after adding them. If false -> returns value without associations. /
returnAssociations?: boolean;
}
/* Interface for possible options of create method /
export interface ICreateOptions extends Omit
/* Interface for possible options of bulk create method /
export type IBulkCreateOptions = ICreateOptions;
/* Interface for possible options of read method /
export interface IReadOptions extends IBaseRepositoryOptions, Omit
/* Interface for possible options of update method /
export interface IUpdateOptions extends IBaseRepositoryExtendedOptions, Omit
/* Interface for possible options of bulk update method /
export type IBulkUpdateOptions = Omit
/* Interface for possible options of delete method /
export interface IDeleteOptions extends Pick
/* Interface for possible options of restore method /
export interface IRestoreOptions extends Pick
/* Type for transaction callback function /
export type TransactionCallBack
/**
* Empty interface for TS augumentation in importing module.
*
* Example: declare module '@tsrt/sequelize' { export interface IBaseOrderingItem { pk: number; order?: number; } }
*/
export interface IBaseOrderingItem { }
``
This project is licensed under the terms of the MIT license.