An introduction to

The installation

$ npm install --save sequelize
Copy the code

You must also manually install drivers for the selected database:

# Select one of the following:
$ npm install --save pg pg-hstore # Postgres
$ npm install --save mysql2
$ npm install --save mariadb
$ npm install --save sqlite3
$ npm install --save tedious # Microsoft SQL Server
Copy the code

We’re using mysql here so we’re going to install mysql2

Connecting to the database

To connect to the database, you must create a Sequelize instance. This can be done by passing the connection parameter separately to the Sequelize constructor or by passing a connection URI:

const { Sequelize } = require('sequelize'); / / method 1: Const Sequelize = new Sequelize(' SQLite :: Memory :') // sqLite example const Sequelize = new Sequelize('postgres://user:[email protected]:5432/dbname') Const sequelize = new Sequelize({dialect: 'sqlite', storage: 'path/to/database.sqlite'}); Const sequelize = new sequelize ('database', 'username', 'password', {host: const sequelize = new sequelize ('database', 'username', 'password', {host: const sequelize = new sequelize ('database', 'username', 'password', {host: 'localhost', the dialect: / * choose 'mysql' | 'mariadb' | 'postgres' |' MSSQL one * /}); // option examples const sequelize = new sequelize ('database', 'username', 'password', {// Support: 'mysql', 'sqlite', 'postgres', 'MSSQL' dialect: 'mysql', // custom host; Default: localhost Host: 'my.server.tld', // for postgres, you can also specify an absolute path to a directory // containing a UNIX socket to connect over // host: '/sockets/psql_sockets'. // Custom port; Default: dialect default port: 12345, // custom protocol; default: 'tcp' // postgres only, useful for Heroku protocol: null, // disable logging or provide a custom logging function; default: console.log logging: false, // you can also pass any dialect options to the underlying dialect library // - default is empty // - currently supported: 'mysql', 'postgres', 'mssql' dialectOptions: { socketPath: '/Applications/MAMP/tmp/mysql/mysql.sock', supportBigNumbers: true, bigNumberStrings: true }, // the storage engine for sqlite // - default ':memory:' storage: 'path/to/database.sqlite', // disable inserting undefined values as NULL // - default: false omitNull: true, // a flag for using a native library or not. // in the case of 'pg' -- set this to true will allow SSL support // - default: false native: true, // Specify options, which are used when sequelize.define is called. // The following example: // define: { timestamps: false } // is basically the same as: // Model.init(attributes, { timestamps: false }); // sequelize.define(name, attributes, { timestamps: false }); // so defining the timestamps for each model will be not necessary define: { underscored: false, freezeTableName: false, charset: 'utf8', dialectOptions: { collate: 'utf8_general_ci' }, timestamps: true }, // similar for sync: you can define this to always force sync for models sync: { force: true }, // pool configuration used to pool database connections pool: { max: 5, idle: 30000, acquire: 60000, }, // isolation level of each transaction // defaults to dialect default isolationLevel: Transaction.ISOLATION_LEVELS.REPEATABLE_READ })Copy the code

Sequelize constructor parameter address

Test the connection

You can test the connection using the.authenticate() function:

try {
  await sequelize.authenticate();
  console.log('Connection has been established successfully.');
} catch (error) {
  console.error('Unable to connect to the database:', error);
}
Copy the code

Close the connection

By default,Sequelize keeps the connection open and uses the same connection for all queries. If you need to close the connection, call sequelize.close()(this is asynchronous and returns a Promise).

Model Model

Define the Model

Models can be defined in Sequelize in two equivalent ways:

  1. callsequelize.define(modelName, attributes, options)
  2. Extend Model and callinit(attributes, options)

Use sequelize.define(recommended):

const { Sequelize, DataTypes } = require('sequelize'); const sequelize = new Sequelize('sqlite::memory:'); Const User = sequelize.define('User', {// here define model attributes firstName: {type: DataTypes.STRING, allowNull: false}, lastName: {type: DataTypes.STRING // allowNull defaults to true}}, {// These are other model parameters}); // 'sequelize.define' returns model console.log(User === sequelize.models.user); // trueCopy the code

Extension Model

onst { Sequelize, DataTypes, Model } = require('sequelize'); const sequelize = new Sequelize('sqlite::memory'); Class User extends Model {} user.init ({// define Model attributes here firstName: {type: DataTypes.STRING, allowNull: false}, lastName: {type: DataTypes.STRING // allowNull defaults to true}}, {// This is the other model parameter sequelize, // we need to pass the connection instance modelName: 'User' // we need to select the model name}); // The model defined is the class itself console.log(User === sequelize.models.user); // trueCopy the code

Internally, Sequelize.define calls model.init, so the two methods are essentially equivalent.

The name of the table that

Note that the table name (Users) is never explicitly defined in either of these methods. However, the model name (User) is given.

By default, when no table name is provided,Sequelize automatically multiplies the model name and uses it as the table name. This complex number is done behind the scenes through a library called inflection, so that irregular complex numbers (such as person -> people) can be correctly evaluated.

Of course, this behavior is easy to configure.

Force the table name to be equal to the model name

sequelize.define('User', { // ... (Attribute)}, {freezeTableName: true});Copy the code

You can also define this behavior globally for the Sequelize instance

const sequelize = new Sequelize('sqlite::memory:', {
  define: {
    freezeTableName: true
  }
});
Copy the code

Provide the table name directly

sequelize.define('User', { // ... (Attribute)}, {tableName: 'Employees'});Copy the code

Model synchronization

Single model synchronization

User.sync() - Creates the table if it does not exist (no action is performed if it already exists) user.sync ({force: true}) - creates the table and deletes it first if it already exists user.sync ({alter: True}) - this checks the current state of the table in the database (what columns it has, their data types, etc.) and then makes the necessary changes in the table to match the model.Copy the code

Synchronize all models at once

await sequelize.sync({ force: true }); Console. log(" All models have been successfully synchronized.");Copy the code

Delete table

Off the table:
await User.drop(); Console. log(" User table deleted!" );Copy the code
Drop all tables:
await sequelize.drop(); Console. log(" All tables deleted!" );Copy the code

Model query

check

FindOne ({}) 2. FindAll ({where: {id: "123"}}) 3. Const {count, rows} = await project. findAndCountAll({where: {title: {[op.like]: 'foo%' } }, offset: 10, limit: 2 }); console.log(count); console.log(rows);Copy the code

increase

1. User.create({})
Copy the code

delete

Destroy ({where: {firstName: "Jane"}}); // Delete all await user.destroy ({where: {firstName: "Jane"}});Copy the code

change

Update ({lastName: "Doe"}, {where: {lastName: null}});Copy the code