TypeScript and Node.js ORM for Oracle, Postgres, MySQL, MariaDB, SQLite and SQL Server, and more.
It's somehow similar to Liquibase in the Java world.
Eg: gsm
https://sites.google.com/site/pawneecity/sequelize/iso-639-3-sequelize
https://sequelize.org/
https://sequelize.org/docs/v6/other-topics/aws-lambda/
https://sequelize.org/docs/v6/core-concepts/paranoid/
As of Sequelize v7-alpha, composite foreign keys are not currently supported by Sequelize's associations. See issue #311 for more information.
Best alternative:
Simply impose an unique index on the composite key and use an auto-increment primary key instead.
Model Subject has a composite primary key, model SubjectLiteral references it.
/**
* SubjectLiteral metadata including foreign keys, if needed.
* @param {*} sequelize
* @param {*} subjectLiteralModel
* @returns Response of model.sync()
*/
async function syncSubjectLiteral(sequelize, subjectLiteralModel) {
try {
const tableName = subjectLiteralModel.getTableName();
const fk_name = 'fk_subjectslitereals_subjects'
//[1/2] (Re)create the table
console.log('Sync model');
const resSync = await subjectLiteralModel.sync({ alter: true });
//[2/2] Add composite foreign key, if needed
console.log('Create foreign key');
sequelize.getQueryInterface().getForeignKeyReferencesForTable(tableName)
.then(constraints => {
const constraintExists = constraints.some(constraint => constraint.constraintName === fk_name);
if (!constraintExists) {
sequelize.getQueryInterface().addConstraint(tableName, {
fields: ['system_id', 'subject_id'], // Composite key fields in the Order model
type: 'foreign key',
name: 'fk_subjectslitereals_subjects', // Name of the constraint
references: {
table: 'subjects',
fields: ['system_id', 'subject_id'] // Composite key fields in the User model
},
//onDelete: 'CASCADE', onUpdate: 'CASCADE'
});
console.log('Foreing key does not exist. creating it:', fk_name);
} else {
console.log('Foreing key already exists, no action needed:', fk_name);
}
})
.catch(error => {
console.error('Error checking constraints:', error);
});
//
return resSync;
} catch (error) {
console.error('syncSubjectLiteral()', error);
throw error;
}
}
src/models/myModel.mjs
import { Sequelize, DataTypes, Model } from 'sequelize'; //{ DataTypes, Sequelize, Op, Model }
//Global constants
const G_CREATED_INSTANT = "created_instant"; // technical field name
const G_UPDATED_INSTANT = "modified_instant"; // technical field name
const G_DELETED_INSTANT = "deleted_instant"; // technical field name
/**
* SUBJECT MODEL, aka; Assignatura.
*/
class SubjectModel extends Model {
}
/**
* SubjectModel definition.
* @param {Sequelize} sequelize Sequelize instance
* @returns {SubjectModel} The model
*/
const initSubjectModel = (sequelize) => {
const modelName = 'Subject';
const tableName = 'subjects';
SubjectModel.init(
{
// Model attributes are defined here
id: {
type: DataTypes.STRING(8),
primaryKey: true
},
system_id: {
type: DataTypes.STRING(32),
primaryKey: false,
references: {
model: 'systems',
key: 'id'
},
//onUpdate: 'CASCADE', onDelete: 'SET NULL'
},
},
{
// Other model options go here
sequelize: sequelize, // Pass the connection instance
modelName: modelName,
tableName: tableName,
timestamps: true,
createdAt: G_CREATED_INSTANT,
updatedAt: G_UPDATED_INSTANT,
paranoid: true,
deletedAt: G_DELETED_INSTANT
},
);
return SubjectModel;
};
/**
* SubjectLiteralModel (Sequelize).
*/
class SubjectLiteralModel extends Model {
}
/**
* SubjectLiteralModel definition, name of the subject in a given language.
* @param {Sequelize} sequelize Sequelize instance
* @returns {SubjectLiteralModel} The model
*/
const initSubjectLiteralModel = (sequelize) => {
const modelName = 'SubjectLiteral';
const tableName = 'subjects_literals';
SubjectLiteralModel.init(
{
// Model attributes are defined here
id: { //Avoid composite primary key, as per Sequelize unsupported associations
type: DataTypes.INTEGER,
autoIncrement: true,
primaryKey: true
},
subject_id: {
type: DataTypes.STRING(8),
primaryKey: false,
allowNull: false,
references: {
model: 'subjects',
key: 'id'
},
//onUpdate: 'CASCADE', onDelete: 'SET NULL'
},
iso639p3: {
type: DataTypes.CHAR(2),
primaryKey: false,
allowNull: false,
references: {
model: 'languages',
key: 'iso639p3'
},
//onUpdate: 'CASCADE', //onDelete: 'SET NULL'
},
name: {
type: DataTypes.STRING(2000),
allowNull: false
},
},
{
// Other model options go here
sequelize: sequelize, // Pass the connection instance
modelName: modelName,
tableName: tableName,
timestamps: true,
createdAt: G_CREATED_INSTANT,
updatedAt: G_UPDATED_INSTANT,
paranoid: true,
deletedAt: G_DELETED_INSTANT,
indexes: [
{
unique: true,
fields: ['subject_id', 'iso639p3']
}
]
},
);
return SubjectLiteralModel;
};
/**
* Setup associations:
* SubjectModel 1 ----- N SubjectLiteralModel
* Remark: Call this after initializing models.
*/
const associateSubjectModel = () => {
// One SubjectModel has many SubjectLiteralModel
SubjectModel.hasMany(SubjectLiteralModel, {
foreignKey: {
name: 'subject_id',
},
as: 'literals' // Alias for easier query access
});
// Each SubjectLiteralModel belongs to one SubjectModel
SubjectLiteralModel.belongsTo(SubjectModel, {
foreignKey: {
name: 'subject_id',
allowNull: false //Default 'true'
},
targetKey: 'id', //Default: id of the target model (the another one)
as: 'subject'
});
};
// EXPORT
export {
SubjectModel,
SubjectLiteralModel,
initSubjectModel,
initSubjectLiteralModel,
associateSubjectModel,
};
src/functions/myfunction/index.mjs
import { DataTypes, Model, Sequelize } from 'sequelize'; //{ DataTypes, Sequelize, Op, Model }
import { associateSubjectModel, initSubjectModel, initSubjectLiteralModel } from '../../models/mydbModel.mjs';
const SubjectModel = initSubjectModel(g_sequelize);
const SubjectLiteralModel = initSubjectLiteralModel(g_sequelize);
associateSubjectModel();
await g_sequelize.sync({ alter: true });
REST operations can return an Slice or a Page for paginated results.
src/services/restService.mjs
/**
* REST helper.
*
* >Query string parameters for paginations and sorting
* - page: Requested Zero-based page index (0..N)
* - size: Desired page size (number of elements per page)
* - sort: Sort column and direction, e.g. 'name,asc' or 'name,desc'
*
* >Classes for paginating REST operations:
* - Pageable: Page request with pagination and sorting information
* - Slice: Represents a subset of a larger result set.
* - Page: Represents a page within a paginated result set
*/
// Slice intended to be returned by a paginated REST operation.
class Slice {
/**
* Constructor.
* @param {Object[]} content List of elements in this slice
* @param {Number} number Number of the current slice
* @param {Number} size Size of slice
*/
constructor(content, number, size) {
this.content = content;
this.number = number;
this.size = size;
}
/**Override, returning a shallow copy of this instance's attributes*/
toString() { return { ...this }; }
}
// Page intended to be returned by a paginated REST operation.
class Page extends Slice{
/**
* Constructor.
* @param {Object[]} content List of elements in this slice
* @param {Number} number Number of the current slice
* @param {Number} size Size of slice
* @param {Number} totalElements Total amount of elements
* @param {Number} totalPages Number of total pages
*/
constructor(content, number, size, totalElements, totalPages) {
super(content, number, size);
this.totalElements = totalElements;
this.totalPages = totalPages;
}
/**Override, returning a shallow copy of this instance's attributes*/
toString() { return { ...this }; }
}
// =====================================================================
export {
Slice,
Page,
};
Sample of a GET /subjects operation for retrieving all subjects w/ its name in a paginated way (using an AWS Lambda w/ Node.js 20).
src/functions/myFunction.mjs
export const handler = async (event, context) => {
const routeKey = event.requestContext.routeKey;
const headers = event.headers;
const pathParameters = event.pathParameters;
const queryStringParameters = event.queryStringParameters;
const lang = await iso639p3FromHeaderAcceptLanguage(headers['accept-language']);
let res = null;
if ('GET /v1/subjects' === routeKey) {
console.log(`XXX GET /v1/subjects`);
const page = queryStringParameters?.page || 0;
const size = queryStringParameters?.size ? (queryStringParameters.size <=1024 ? queryStringParameters.size : 1024) : 16;
res = await getSubjects(g_sequelize, lang, page, size); //await needed
} else {
console.log(`EXC Unsupported operation: ${routeKey}`); // Return a 404 for unsupported methods or paths
return {
statusCode: 404,
headers: { 'Content-Type': 'application/json' },
body: JSON.stringify({ detail: `Unsupported operation: ${routeKey}` }), // 'detail' as in ProblemDetail (SB)
};
}
// Return a 200 w/ the response
return {
statusCode: 200,
headers: { 'Content-Type': 'application/json', 'Content-Language': await ietfBcp47FromIso639p3(lang) },
body: JSON.stringify(res, null, 2),
};
src/services/subjectService.mjs
import { Page } from '../services/restService.mjs';
class SubjectProjWithName {
/**
* Constructor.
* @param {String} id -
* @param {String} systemId -
* @param {String?} name Nullable. The name of the subject in language 'iso639p3'
*/
constructor(id, systemId, name) {
this.id = id;
this.systemId = systemId;
this.name = name;
}
/**Override, returning a shallow copy of this instance's attributes*/
toString() { return { ...this }; }
}
/**
* Get subjects (used by REST operation response).
* @param {Sequelize} sequelize The sequelize instance
* @param {String} iso639p3 Lang.
* @param {Number} page Zero-based page index
* @param {Number} size Page size
* @returns {Page} Page of SubjectProjWithName objects
*/
async function getSubjects(sequelize, iso639p3, page, size) {
console.log(`INI getSubjects(${iso639p3}, ${page}, ${size})`);
try {
const SubjectModel = initSubjectModel(sequelize);
const SubjectLiteralModel = initSubjectLiteralModel(sequelize);
associateSubjectModel(); // Setup associations
//
const offset = page * size; //Rows to skip for pagination
// Fetch subjects with pagination and associated literals
const qryResult = await SubjectModel.findAndCountAll({
include: [
{
model: SubjectLiteralModel,
as: 'literals', // Use the alias defined in the association
where: {
iso639p3: iso639p3
},
required: false //left join
}
],
offset: offset, // Skip rows for pagination
limit: size, // Number of results to return
});
// Calculate total pages
const totalElements = qryResult.count; //total number of elements
const totalPages = Math.ceil(totalElements / size);
//Prepare the 'content' to be returned
const content = [];
for (const ex of qryResult.rows) {
const element = new SubjectProjWithName(ex['id'], ex['system_id'], ex['literals']?.[0]?.['name'] || null);
content.push(element);
}
//
const ret = new Page(
content, //elements in this page
page, //zero-based page number
size, //page size
totalElements, //total amount of elements
totalPages //total number of pages
);
return ret;
} catch (error) {
console.error(`EXC getSubjects(${iso639p3}, ${page}, ${size})`, error);
throw error;
}
}
export {
getSubjects,
};