You can’t Sequelize me

preparation

The source address

Database link

Here is the use of the local docker image, specifically mapped to the host port 9000, you can run the following command to pull up a docker image:

docker run --name mysql1 -e MYSQL_ONETIME_PASSWORD=123456 -p 9000:3306 -d mysql/mysql-server:latest
Copy the code

We then Sequelize to connect to the database

// index.js
const { Sequelize } = require('sequelize')

const sequelize = new Sequelize('test1'."root"."123456", {
  dialect: 'mysql'.host: "127.0.0.1".protocol: "http".port: 9000
})


try {
    // Verify that the database is connected successfully
    await sequelize.authenticate()
} catch (err) {}
Copy the code

1. One-to-one

1.1 Simulation Scenario

Assume that a User can have only one BitrhAuth birth certificate, so here we need to create two tables User and BirthAuth and establish a one-to-one relationship between them

1.2 Table creation and association

1.2.1 definition table

Note that only the structure of the table is defined here; the table is not created in the actual database

// model.js
const { Model, INTEGER, STRING, TEXT } = require('sequelize')

// Create the table in Model mode
class User extends Model {
  static attributes = {
    id: {
      field: 'id'.autoIncrement: true.primaryKey: true.type: INTEGER
    },
    name: STRING(255)}// The name of the table is the name of the table created in the database
  static tableName = "Buyer"
}

class File extends Model {
  static attributes = {
    id: {
      field: 'id'.autoIncrement: true.primaryKey: true.type: INTEGER
    },
    title: STRING(255),
    content: TEXT("medium"),
    ownerId: INTEGER
  }
  static tableName = "Birth_Auth"
}
Copy the code

1.2.2 Creating and associated tables

Use the init method to create the corresponding table, link the model to the database with the Sequlize parameter, and then declare the corresponding relationship with hasOne

The operations are not synchronized to the database until the sync method is used

async function init() {
  try {
    await sequelize.authenticate()
    User.init(User.attributes, {
      sequelize,
      tableName: User.tableName
    })

    File.init(File.attributes, {
      sequelize,
      tableName: File.tableName
    })

    // Create one to one
    // We can then use setUser to create the corresponding 1-to-1 element
    // hasOne is an association between User and File
    // File can be created at the same time as User
    User.File =  User.hasOne(File, {
      // The foreignKey finally falls in the File table, which is the configuration of File
      foreignKey: 'ownerId'.// This sourceKey currently represents one
      sourceKey: "id".// This as is very important
      Sequelize adds a setXXX method to create the association at the same time
      // Use the as variable directly
      as: "birth"})}catch(e) {
      console.log(e) 
  }

  The table operation is not synchronized to the database until sync is used
  await sequelize.sync({ force:!!!!! isForce }) }Copy the code

1.2 create

With that done, let’s create a data line between User and File

// Add a birth certificate for an existing user
async function addInExist() {
  // Create one to one
  const [user] = await User.findOrCreate({
      where: {
        name: {
          [Op.like]: '%xiaodeng%'}},defaults: {
        name: "xiaodeng"}})const file = await File.create({
      title: `pi essay The ${Math.random()}`.content: `content The ${Math.random()}`
  })

  // Here is how to associate user and file via setBirth
  await user.setBirth(file)
}

// Create one-to-one and one-to-many relationships at the same time by include model
async function addInCreate() {
  await User.create({
    name: "xiaoliu".birth: {
      title: "xiaoliuzhengming".content: 'content'}}, {include: [{
      model: File,
      as: 'birth'})}}]Copy the code

1.3 the query

In the same way as in the created scenario, the existing association is queried through the include in the option parameter

async function query() {
  // One-to-one query
  // One-to-one and one-to-many queries
  const { rows, count } = await User.findAndCountAll({
    include: [{
      model: File,
      as: 'birth'})}}][{"id": 1, "name": "xiaodeng", "createdAt": "2022-04-05t05:28:08.000z ", "updatedAt": "2022-04-05T05:28:08.000z ", // Will also check out the birth" Birth ": {" ID ": 13, "title":" PI essay 0.5358826142709898", "content": "Content 0.42366009995271936", "createdAt":" 2022-04-05T05:33:41.000z ", "updatedAt": "2022-04-05T05:33:41.000z"}}] */
Copy the code

One to many

2.1 Simulation Scenario

Assume that a User can buy multiple different phones, so here we need to create two tables, User and Phone, and establish a one-to-many relationship between them

2.2 Table creation and association

2.2.1 definition table

class Phone extends Model {
  static attributes = {
    id: {
      field: 'id'.autoIncrement: true.primaryKey: true.type: INTEGER
    },
    name: STRING(255),
    type: STRING(255)}static tableName = "Phone"
}
Copy the code

2.2.2 Creating and associated tables

Use the init method to create the corresponding table, link the model to the database with the Sequlize parameter, and then declare the corresponding relationship through hasMany

function init() {
    // omit some of the code in 1
    // Create the Phone table
    Phone.init(Phone.attributes, {
      sequelize,
      tableName: Phone.tableName
    })

    // One-to-many relationship
    // You can query the contents of the corresponding table by means of Include
    User.Phones = User.hasMany(Phone, {
        // sourceKey is the User's field
        sourceKey: "id".// foreignKey here is the field associated with Phone
        foreignKey: "ownerId".as: "phones"})}Copy the code

2.3 create

// The User already exists, then add the corresponding mobile phone ID
async function addOne () {
  const [user] = await User.findOrCreate({
    where: {
      id: 1}})// 
  const phones = await Phone.bulkCreate([{
    name: "iphone 13".type: "Apple"
  }, {
    name: "Mate 40 Pro".type: "HuaWei"
  }])

  // this applies to setPhones, why Phones?
  // Because as in hasMany are phones in creating associations
  // So here are Phones
  await user.setPhones(phones)
}

async function addInCreate () {
  // Create one-to-many
  // Create a User and its associated Phone
  await User.create({
    name: "xiaoze".phones: [{
      name: "IPhone 13 pro max".type: "Apple"
    }, {
      name: "Mate P30".type: "Hua Wei"}]}, {include: [{
      model: Phone,
      as: 'phones'})}}]Copy the code

2.4 the query

Queries are the same as one-to-one queries. If you want to find specific fields, use include and associate them with the relevant model

async function query() {
  // One-to-one query
  // One-to-one and one-to-many queries
  const { rows, count } = await User.findAndCountAll({
    where: {
      name: {
        [Op.like]: '%xiaoze%'}},include: [{
      model: File,
      as: 'birth'}, {model: Phone,
      as: 'phones'})}}]11, / * [{" id ":" name ":" xiaoze ", "createdAt" : "the 2022-04-05 T07:29:49. 000 z", "updatedAt" : 2022-04-05T07:29:49.000z ", "Birth ": null, "phones": [{"id": 21, "name": "IPhone 13 Pro Max ", "type": "Apple", "createdAt": "2022-04-05T07:29:49.000z ", "updatedAt":" 2022-04-05T07:29:49.000z ", "ownerId": 11}, {"id": 22, "name": "Mate P30", "type": "Hua Wei", "createdAt": "2022-04-05T07:29:49.000z ", "updatedAt": "The 2022-04-05 T07:29:49. 000 z", "ownerId" : 11 * /}}]]
Copy the code

Many to many

3.1 Simulation Scenario

Suppose an Actor can act in more than one Movie, and a Movie can have more than one Actor, so here we need to create two tables Movie and actors, because of the many-to-many relationship, The relationship needs to be maintained through the intermediate table MovieActor, so three tables are needed this time.

3.2 Table creation and association

3.2.1 definition table

class Movie extends Model {
  static attributes = {
    id: {
      field: 'id'.autoIncrement: true.primaryKey: true.type: INTEGER
    },
    name: STRING(255),
    fee: INTEGER
  }

  static tableName = "Movie"
}

class Actor extends Model {
  static attributes = {
    id: {
      field: 'id'.autoIncrement: true.primaryKey: true.type: INTEGER
    },
    sex: STRING(255),
    name: STRING(255)}static tableName = 'Actor'
}

class MovieActor extends Model {
  static attributes = {
    actorId: INTEGER,
    movieId: INTEGER
  }

  static tableName = 'MovieActor'
}
Copy the code

3.2.2 Creating a table and associating a table

Create the corresponding table using the init method and link the model to the database with the Sequlize parameter, then declare the corresponding relationship through belongsToMany

// index.js

function init() {
    / /... Omit the repeated code above
    // Initialize the three tables above
    Actor.init(Actor.attributes, {
      sequelize,
      tableName: Actor.tableName
    })

    Movie.init(Movie.attributes, {
      sequelize,
      tableName: Movie.tableName
    })

    MovieActor.init(MovieActor.attributes, {
      sequelize,
      tableName: MovieActor.tableName
    })

    // Declare a one-to-many relationship to the opposite table
    // In the many-to-many scenario, we need to use an intermediate table to handle the relationship between the two
    // So the fields in the through table are required
    Actor.belongsToMany(Movie, { through: MovieActor, foreignKey: 'actorId'.as: 'movies' })
    Movie.belongsToMany(Actor, { through: MovieActor, foreignKey: 'movieId'.as: 'actors'})}Copy the code

3.1 create

function add () {
  const actors = await Actor.bulkCreate([{
    name: "wangbaoqiang".sex: "male"}, {name: "tongliya".sex: "female"
  }, {
    name: "SunHonglei".sex: "female"
  }])

  const Movies = await Movie.bulkCreate([{
    name: "Qianfu".fee: 100
  }, {
    name: "TangTan".fee: 100
  }, {
    name: "newMoview".fee: 200
  }])

  // The setActors are from as
  await Movies[0].setActors(actors.slice(0.2))
  await Movies[1].setActors([actors[2]])
  await Movies[2].setActors(actors)
}
Copy the code

3.2 the query

Because of the many-to-many relationship, you can either query Movie from Actors or look up Actors from Movie

function query() {
  const { rows: movies } = await Movie.findAndCountAll({
    include: [{
      model: Actor,
      as: 'actors'}]})const { rows: actors } = await Actor.findAndCountAll({
    include: [{
      model: Movie,
      as: 'movies'})}}]Copy the code

4. TS support

4.1 Part of model definition

Only a few points need to be clarified in the TS support section:

1. How attributes’ data structure can be passed. The way to prompt

2. What do setActors and other methods tell you about getActors that are mixed in with your code

You only need to declare ~

import { Model, INTEGER, STRING, HasManySetAssociationsMixin, HasManyGetAssociationsMixin } from 'sequelize'

class Actor extends Model {
  static attributes = {
    id: {
      field: 'id'.autoIncrement: true.primaryKey: true.type: INTEGER
    },
    sex: STRING(255),
    name: STRING(255)}static tableName = 'Actor'

  declare id: number;
  declare sex: string;
  declare name: string;

  declare setMovies: HasManySetAssociationsMixin<Movie, number>
  declare getMovies: HasManyGetAssociationsMixin<Movie>

}

class Movie extends Model {
  static attributes = {
    id: {
      field: 'id'.autoIncrement: true.primaryKey: true.type: INTEGER
    },
    name: STRING(255),
    fee: INTEGER
  }

  static tableName = "Movie"

  declare id: number;
  declare name: string;
  declare fee: number;

  declare getActors: HasManyGetAssociationsMixin<Actor>
  declare setActors: HasManySetAssociationsMixin<Actor, number>}class MovieActor extends Model {
  static attributes = {
    actorId: INTEGER,
    movieId: INTEGER
  }

  static tableName = 'MovieActor'
}


export {
  Actor,
  Movie,
  MovieActor
}
Copy the code

4.2 Reconstructing the actor’s movie part of the code

import { Sequelize } from "sequelize";
import { Movie, Actor, MovieActor } from "./models/Actor";

const sequelize = new Sequelize("seq"."root"."123456", {
  dialect: "mysql".host: "127.0.0.1".protocol: "http".port: 9000});async function init() {
  [Movie, Actor, MovieActor].forEach(async (model) => {
    await model.init(model.attributes, {
      sequelize,
      tableName: model.tableName,
    });
  });

  Movie.belongsToMany(Actor, {
    through: MovieActor,
    foreignKey: "movieId".as: "actors"}); Actor.belongsToMany(Movie, {through: MovieActor,
    foreignKey: "actorId".as: "movies"});return sequelize.sync();
}

async function addData() {
  const actors = await Actor.bulkCreate([
    {
      name: "wangbaoqiang".sex: "male"}, {name: "tongliya".sex: "female"}, {name: "SunHonglei".sex: "female",}]);const Movies = await Movie.bulkCreate([
    {
      name: "Qianfu".fee: 100}, {name: "TangTan".fee: 100}, {name: "newMoview".fee: 200,}]);await Movies[0].setActors(actors.slice(0.2));
  await Movies[1].setActors([actors[2]]);
  await Movies[2].setActors(actors);
}

async function query() {
  const { rows } = await Movie.findAndCountAll({
    include: [{model: Actor,
        as: "actors",}]});const jsonArray = rows.map((item) = > item.toJSON());
  console.log(JSON.stringify(jsonArray, null.2));
}

(async() = > {await init();
  await addData();
  await query();

  awaitsequelize.close(); }) ();Copy the code

Five, a few Key simple memory

There are several keys that we used in using associations: foreignKey, sourceKey and targetKey (not used this time) and the relationship between hasXX(One/Many) and BelongToXX.

Assuming that the relationship we want is N to M, the corresponding creation formula could be:

N.hasxxx (M, {foreignKey:'M ', sourceKey: 'N ', as:'M 'alias, setXX '})

N. belongtoxx (M, {foreignKey: 'N key', sourceKey:'M key', as:'M alias, setXX, getXX '})

BelongToXX and hasXX are basically opposites based on the above rules