sequelize-patterns

Sequelize Skill

Sequelize node.js orm for sql databases. use for database models, migrations, associations, queries, transactions, validations, hooks, and working with postgresql, mysql, mariadb, sqlite, sql server., generated from official documentation.

When to Use This Skill

This skill should be triggered when:

• Working with sequelize
• Asking about sequelize features or APIs
• Implementing sequelize solutions
• Debugging sequelize code
• Learning sequelize best practices

Quick Reference

Common Patterns

Pattern 1: Connection PoolIf you're connecting to the database from a single process, you should create only one Sequelize instance. Sequelize will set up a connection pool on initialization. This connection pool can be configured through the constructor's options parameter (using options.pool), as is shown in the following example: const sequelize = new Sequelize(/_ ... _/, { // ... pool: { max: 5, min: 0, acquire: 30000, idle: 10000 }}); Learn more in the API Reference for the Sequelize constructor. If you're connecting to the database from multiple processes, you'll have to create one instance per process, but each instance should have a maximum connection pool size of such that the total maximum size is respected. For example, if you want a max connection pool size of 90 and you have three processes, the Sequelize instance of each process should have a max connection pool size of 30.

options

Pattern 2: Naming StrategiesThe underscored option​ Sequelize provides the underscored option for a model. When true, this option will set the field option on all attributes to the snakecase version of its name. This also applies to foreign keys automatically generated by associations and other automatically generated fields. Example: const User = sequelize.define( 'user', { username: Sequelize.STRING }, { underscored: true, },);const Task = sequelize.define( 'task', { title: Sequelize.STRING }, { underscored: true, },);User.hasMany(Task);Task.belongsTo(User); Above we have the models User and Task, both using the underscored option. We also have a One-to-Many relationship between them. Also, recall that since timestamps is true by default, we should expect the createdAt and updatedAt fields to be automatically created as well. Without the underscored option, Sequelize would automatically define: A createdAt attribute for each model, pointing to a column named createdAt in each table An updatedAt attribute for each model, pointing to a column named updatedAt in each table A userId attribute in the Task model, pointing to a column named userId in the task table With the underscored option enabled, Sequelize will instead define: A createdAt attribute for each model, pointing to a column named created_at in each table An updatedAt attribute for each model, pointing to a column named updated_at in each table A userId attribute in the Task model, pointing to a column named user_id in the task table Note that in both cases the fields are still camelCase in the JavaScript side; this option only changes how these fields are mapped to the database itself. The field option of every attribute is set to their snake_case version, but the attribute itself remains camelCase. This way, calling sync() on the above code will generate the following: CREATE TABLE IF NOT EXISTS "users" ( "id" SERIAL, "username" VARCHAR(255), "created_at" TIMESTAMP WITH TIME ZONE NOT NULL, "updated_at" TIMESTAMP WITH TIME ZONE NOT NULL, PRIMARY KEY ("id"));CREATE TABLE IF NOT EXISTS "tasks" ( "id" SERIAL, "title" VARCHAR(255), "created_at" TIMESTAMP WITH TIME ZONE NOT NULL, "updated_at" TIMESTAMP WITH TIME ZONE NOT NULL, "user_id" INTEGER REFERENCES "users" ("id") ON DELETE SET NULL ON UPDATE CASCADE, PRIMARY KEY ("id")); Singular vs. Plural​ At a first glance, it can be confusing whether the singular form or plural form of a name shall be used around in Sequelize. This section aims at clarifying that a bit. Recall that Sequelize uses a library called inflection under the hood, so that irregular plurals (such as person -> people) are computed correctly. However, if you're working in another language, you may want to define the singular and plural forms of names directly; sequelize allows you to do this with some options. When defining models​ Models should be defined with the singular form of a word. Example: sequelize.define('foo', { name: DataTypes.STRING }); Above, the model name is foo (singular), and the respective table name is foos, since Sequelize automatically gets the plural for the table name. When defining a reference key in a model​ sequelize.define('foo', { name: DataTypes.STRING, barId: { type: DataTypes.INTEGER, allowNull: false, references: { model: 'bars', key: 'id', }, onDelete: 'CASCADE', },}); In the above example we are manually defining a key that references another model. It's not usual to do this, but if you have to, you should use the table name there. This is because the reference is created upon the referenced table name. In the example above, the plural form was used (bars), assuming that the bar model was created with the default settings (making its underlying table automatically pluralized). When retrieving data from eager loading​ When you perform an include in a query, the included data will be added to an extra field in the returned objects, according to the following rules: When including something from a single association (hasOne or belongsTo) - the field name will be the singular version of the model name; When including something from a multiple association (hasMany or belongsToMany) - the field name will be the plural form of the model. In short, the name of the field will take the most logical form in each situation. Examples: // Assuming Foo.hasMany(Bar)const foo = Foo.findOne({ include: Bar });// foo.bars will be an array// foo.bar will not exist since it doens't make sense// Assuming Foo.hasOne(Bar)const foo = Foo.findOne({ include: Bar });// foo.bar will be an object (possibly null if there is no associated model)// foo.bars will not exist since it doens't make sense// And so on. Overriding singulars and plurals when defining aliases​ When defining an alias for an association, instead of using simply { as: 'myAlias' }, you can pass an object to specify the singular and plural forms: Project.belongsToMany(User, { as: { singular: 'líder', plural: 'líderes', },}); If you know that a model will always use the same alias in associations, you can provide the singular and plural forms directly to the model itself: const User = sequelize.define( 'user', { / ... _/ }, { name: { singular: 'líder', plural: 'líderes', }, },);Project.belongsToMany(User); The mixins added to the user instances will use the correct forms. For example, instead of project.addUser(), Sequelize will provide project.getLíder(). Also, instead of project.setUsers(), Sequelize will provide project.setLíderes(). Note: recall that using as to change the name of the association will also change the name of the foreign key. Therefore it is recommended to also specify the foreign key(s) involved directly in this case. // Example of possible mistakeInvoice.belongsTo(Subscription, { as: 'TheSubscription' });Subscription.hasMany(Invoice); The first call above will establish a foreign key called theSubscriptionId on Invoice. However, the second call will also establish a foreign key on Invoice (since as we know, hasMany calls places foreign keys in the target model) - however, it will be named subscriptionId. This way you will have both subscriptionId and theSubscriptionId columns. The best approach is to choose a name for the foreign key and place it explicitly in both calls. For example, if subscription_id was chosen: // Fixed exampleInvoice.belongsTo(Subscription, { as: 'TheSubscription', foreignKey: 'subscription_id',});Subscription.hasMany(Invoice, { foreignKey: 'subscription_id' });