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 snake*case 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' }); ``` underscored ``` **Pattern 3:** Models should be defined with the singular form of a word. Example: ``` sequelize.define('foo', { name: DataTypes.STRING }); ``` **Pattern 4:** By default, null is an allowed value for every column of a model. This can be disabled setting the allowNull: false option for a column, as it was done in the username field from our code example: ``` null ``` **Pattern 5:** Query InterfaceAn instance of Sequelize uses something called Query Interface to communicate to the database in a dialect-agnostic way. Most of the methods you've learned in this manual are implemented with the help of several methods from the query interface. The methods from the query interface are therefore lower-level methods; you should use them only if you do not find another way to do it with higher-level APIs from Sequelize. They are, of course, still higher-level than running raw queries directly (i.e., writing SQL by hand). This guide shows a few examples, but for the full list of what it can do, and for detailed usage of each method, check the QueryInterface API. Obtaining the query interface​ From now on, we will call queryInterface the singleton instance of the QueryInterface class, which is available on your Sequelize instance: const { Sequelize, DataTypes } = require('sequelize');const sequelize = new Sequelize(/_ ... _/);const queryInterface = sequelize.getQueryInterface(); Creating a table​ queryInterface.createTable('Person', { name: DataTypes.STRING, isBetaMember: { type: DataTypes.BOOLEAN, defaultValue: false, allowNull: false, },}); Generated SQL (using SQLite): CREATE TABLE IF NOT EXISTS `Person` ( `name` VARCHAR(255), `isBetaMember` TINYINT(1) NOT NULL DEFAULT 0); Note: Consider defining a Model instead and calling YourModel.sync() instead, which is a higher-level approach. Adding a column to a table​ queryInterface.addColumn('Person', 'petName', { type: DataTypes.STRING }); Generated SQL (using SQLite): ALTER TABLE `Person` ADD `petName` VARCHAR(255); Changing the datatype of a column​ queryInterface.changeColumn('Person', 'foo', { type: DataTypes.FLOAT, defaultValue: 3.14, allowNull: false,}); Generated SQL (using MySQL): ALTER TABLE `Person` CHANGE `foo` `foo` FLOAT NOT NULL DEFAULT 3.14; Removing a column​ queryInterface.removeColumn('Person', 'petName', { /_ query options _/}); Generated SQL (using PostgreSQL): ALTER TABLE "public"."Person" DROP COLUMN "petName"; Changing and removing columns in SQLite​ SQLite does not support directly altering and removing columns. However, Sequelize will try to work around this by recreating the whole table with the help of a backup table, inspired by these instructions. For example: // Assuming we have a table in SQLite created as follows:queryInterface.createTable('Person', { name: DataTypes.STRING, isBetaMember: { type: DataTypes.BOOLEAN, defaultValue: false, allowNull: false, }, petName: DataTypes.STRING, foo: DataTypes.INTEGER,});// And we change a column:queryInterface.changeColumn('Person', 'foo', { type: DataTypes.FLOAT, defaultValue: 3.14, allowNull: false,}); The following SQL calls are generated for SQLite: PRAGMA TABLE_INFO(`Person`);CREATE TABLE IF NOT EXISTS `Person_backup` ( `name` VARCHAR(255), `isBetaMember` TINYINT(1) NOT NULL DEFAULT 0, `foo` FLOAT NOT NULL DEFAULT '3.14', `petName` VARCHAR(255));INSERT INTO `Person_backup` SELECT `name`, `isBetaMember`, `foo`, `petName` FROM `Person`;DROP TABLE `Person`;CREATE TABLE IF NOT EXISTS `Person` ( `name` VARCHAR(255), `isBetaMember` TINYINT(1) NOT NULL DEFAULT 0, `foo` FLOAT NOT NULL DEFAULT '3.14', `petName` VARCHAR(255));INSERT INTO `Person` SELECT `name`, `isBetaMember`, `foo`, `petName` FROM `Person_backup`;DROP TABLE `Person_backup`; Other​ As mentioned in the beginning of this guide, there is a lot more to the Query Interface available in Sequelize! Check the QueryInterface API for a full list of what can be done. ``` queryInterface ``` **Pattern 6:** Advanced M:N AssociationsMake sure you have read the associations guide before reading this guide. Let's start with an example of a Many-to-Many relationship between User and Profile. const User = sequelize.define( 'user', { username: DataTypes.STRING, points: DataTypes.INTEGER, }, { timestamps: false },);const Profile = sequelize.define( 'profile', { name: DataTypes.STRING, }, { timestamps: false },); The simplest way to define the Many-to-Many relationship is: User.belongsToMany(Profile, { through: 'User_Profiles' });Profile.belongsToMany(User, { through: 'User_Profiles' }); By passing a string to through above, we are asking Sequelize to automatically generate a model named User_Profiles as the through table (also known as junction table), with only two columns: userId and profileId. A composite unique key will be established on these two columns. We can also define ourselves a model to be used as the through table. const User_Profile = sequelize.define('User_Profile', {}, { timestamps: false });User.belongsToMany(Profile, { through: User_Profile });Profile.belongsToMany(User, { through: User_Profile }); The above has the exact same effect. Note that we didn't define any attributes on the User_Profile model. The fact that we passed it into a belongsToMany call tells sequelize to create the two attributes userId and profileId automatically, just like other associations also cause Sequelize to automatically add a column to one of the involved models. However, defining the model by ourselves has several advantages. We can, for example, define more columns on our through table: const User_Profile = sequelize.define( 'User_Profile', { selfGranted: DataTypes.BOOLEAN, }, { timestamps: false },);User.belongsToMany(Profile, { through: User_Profile });Profile.belongsToMany(User, { through: User_Profile }); With this, we can now track an extra information at the through table, namely the selfGranted boolean. For example, when calling the user.addProfile() we can pass values for the extra columns using the through option. Example: const amidala = await User.create({ username: 'p4dm3', points: 1000 });const queen = await Profile.create({ name: 'Queen' });await amidala.addProfile(queen, { through: { selfGranted: false } });const result = await User.findOne({ where: { username: 'p4dm3' }, include: Profile,});console.log(result); Output: { "id": 4, "username": "p4dm3", "points": 1000, "profiles": [ { "id": 6, "name": "queen", "User_Profile": { "userId": 4, "profileId": 6, "selfGranted": false } } ]} You can create all relationship in single create call too. Example: const amidala = await User.create( { username: 'p4dm3', points: 1000, profiles: [ { name: 'Queen', User_Profile: { selfGranted: true, }, }, ], }, { include: Profile, },);const result = await User.findOne({ where: { username: 'p4dm3' }, include: Profile,});console.log(result); Output: { "id": 1, "username": "p4dm3", "points": 1000, "profiles": [ { "id": 1, "name": "Queen", "User_Profile": { "selfGranted": true, "userId": 1, "profileId": 1 } } ]} You probably noticed that the User_Profiles table does not have an id field. As mentioned above, it has a composite unique key instead. The name of this composite unique key is chosen automatically by Sequelize but can be customized with the uniqueKey option: User.belongsToMany(Profile, { through: User_Profiles, uniqueKey: 'my_custom_unique',}); Another possibility, if desired, is to force the through table to have a primary key just like other standard tables. To do this, simply define the primary key in the model: const User_Profile = sequelize.define( 'User_Profile', { id: { type: DataTypes.INTEGER, primaryKey: true, autoIncrement: true, allowNull: false, }, selfGranted: DataTypes.BOOLEAN, }, { timestamps: false },);User.belongsToMany(Profile, { through: User_Profile });Profile.belongsToMany(User, { through: User_Profile }); The above will still create two columns userId and profileId, of course, but instead of setting up a composite unique key on them, the model will use its id column as primary key. Everything else will still work just fine. Through tables versus normal tables and the "Super Many-to-Many association"​ Now we will compare the usage of the last Many-to-Many setup shown above with the usual One-to-Many relationships, so that in the end we conclude with the concept of a "Super Many-to-Many relationship". Models recap (with minor rename)​ To make things easier to follow, let's rename our User_Profile model to grant. Note that everything works in the same way as before. Our models are: const User = sequelize.define( 'user', { username: DataTypes.STRING, points: DataTypes.INTEGER, }, { timestamps: false },);const Profile = sequelize.define( 'profile', { name: DataTypes.STRING, }, { timestamps: false },);const Grant = sequelize.define( 'grant', { id: { type: DataTypes.INTEGER, primaryKey: true, autoIncrement: true, allowNull: false, }, selfGranted: DataTypes.BOOLEAN, }, { timestamps: false },); We established a Many-to-Many relationship between User and Profile using the Grant model as the through table: User.belongsToMany(Profile, { through: Grant });Profile.belongsToMany(User, { through: Grant }); This automatically added the columns userId and profileId to the Grant model. Note: As shown above, we have chosen to force the grant model to have a single primary key (called id, as usual). This is necessary for the Super Many-to-Many relationship that will be defined soon. Using One-to-Many relationships instead​ Instead of setting up the Many-to-Many relationship defined above, what if we did the following instead? // Setup a One-to-Many relationship between User and GrantUser.hasMany(Grant);Grant.belongsTo(User);// Also setup a One-to-Many relationship between Profile and GrantProfile.hasMany(Grant);Grant.belongsTo(Profile); The result is essentially the same! This is because User.hasMany(Grant) and Profile.hasMany(Grant) will automatically add the userId and profileId columns to Grant, respectively. This shows that one Many-to-Many relationship isn't very different from two One-to-Many relationships. The tables in the database look the same. The only difference is when you try to perform an eager load with Sequelize. // With the Many-to-Many approach, you can do:User.findAll({ include: Profile });Profile.findAll({ include: User });// However, you can't do:User.findAll({ include: Grant });Profile.findAll({ include: Grant });Grant.findAll({ include: User });Grant.findAll({ include: Profile });// On the other hand, with the double One-to-Many approach, you can do:User.findAll({ include: Grant });Profile.findAll({ include: Grant });Grant.findAll({ include: User });Grant.findAll({ include: Profile });// However, you can't do:User.findAll({ include: Profile });Profile.findAll({ include: User });// Although you can emulate those with nested includes, as follows:User.findAll({ include: { model: Grant, include: Profile, },}); // This emulates the `User.findAll({ include: Profile })`, however// the resulting object structure is a bit different. The original// structure has the form `user.profiles[].grant`, while the emulated// structure has the form `user.grants[].profiles[]`. The best of both worlds: the Super Many-to-Many relationship​ We can simply combine both approaches shown above! // The Super Many-to-Many relationshipUser.belongsToMany(Profile, { through: Grant });Profile.belongsToMany(User, { through: Grant });User.hasMany(Grant);Grant.belongsTo(User);Profile.hasMany(Grant);Grant.belongsTo(Profile); This way, we can do all kinds of eager loading: // All these work:User.findAll({ include: Profile });Profile.findAll({ include: User });User.findAll({ include: Grant });Profile.findAll({ include: Grant });Grant.findAll({ include: User });Grant.findAll({ include: Profile }); We can even perform all kinds of deeply nested includes: User.findAll({ include: [ { model: Grant, include: [User, Profile], }, { model: Profile, include: { model: User, include: { model: Grant, include: [User, Profile], }, }, }, ],}); Aliases and custom key names​ Similarly to the other relationships, aliases can be defined for Many-to-Many relationships. Before proceeding, please recall the aliasing example for belongsTo on the associations guide. Note that, in that case, defining an association impacts both the way includes are done (i.e. passing the association name) and the name Sequelize chooses for the foreign key (in that example, leaderId was created on the Ship model). Defining an alias for a belongsToMany association also impacts the way includes are performed: Product.belongsToMany(Category, { as: 'groups', through: 'product_categories',});Category.belongsToMany(Product, { as: 'items', through: 'product_categories' });// [...]await Product.findAll({ include: Category }); // This doesn't workawait Product.findAll({ // This works, passing the alias include: { model: Category, as: 'groups', },});await Product.findAll({ include: 'groups' }); // This also works However, defining an alias here has nothing to do with the foreign key names. The names of both foreign keys created in the through table are still constructed by Sequelize based on the name of the models being associated. This can readily be seen by inspecting the generated SQL for the through table in the example above: CREATE TABLE IF NOT EXISTS