The query

find– Searches for a specific element in the database

// Query based on the known ID
Project.findByPk(123).then(project= > {
  // project will be an instance of project and will store the contents of the table with id 123
  // Null if no such entry is defined
})

// Query by attribute
Project.findOne({ where: {title: 'aProject'} }).then(project= > {
  / / project will be matched to the first ` title ` for 'aProject project | | null
})


Project.findOne({
  where: {title: 'aProject'},
  attributes: ['id'['name'.'title']]
}).then(project= > {
  / / project will be matched to the first ` title ` for 'aProject project | | null
  // project.get('title') will contain the name of the project
})
Copy the code

findOrCreate– Searches for a specific element that does not exist and creates a new one

The findOrCreate method can be used to check whether an element already exists in the database. If it already exists, the corresponding instance is returned. If the element does not exist, it will be created.

Suppose we have an empty database with a User model that has a username and a job attribute.

In the creation case, you can add defaults after the WHERE option.

User
  .findOrCreate({where: {username: 'sdepold'}, defaults: {job: 'Technical Lead JavaScript'}})
  .then(([user, created]) = > {
    console.log(user.get({
      plain: true
    }))
    console.log(created)

    /* findOrCreate returns an array of found or created objects and a Boolean value, true if a new object is created, false otherwise: [{username: 'sdepold', job: 'Technical Lead JavaScript', id: 1, createdAt: Fri Mar 22 2013 21: 28: 34 GMT + 0100(CET), updatedAt: Fri Mar 22 2013 21: 28: 34 GMT + 0100(CET) }, true ] In the example above, the array spread on line 3 divides the array into its 2 parts and passes them as arguments to the callback function defined beginning at line 39, which treats them as "user" and "created" in this case. (So "user" will be the object from index 0 of the returned array  and "created" will equal "true".) * /
  })
Copy the code

findAndCountAll– Searches for multiple elements in the database, returning both data and totals

FindAndCountAll is a convenient method that combines findAll and count (see below) and is useful when dealing with page-related queries. In this query, you can retrieve data with limits and offsets and return a total number of records in addition to the retrieved data.

After this method is successfully queried, you will receive an object with the following two attributes:

  • count– The value is an integerwhereThe total amount of data retrieved by clauses and other filtering criteria
  • rows– Array of objects, bywhereClause matched with other filter criteria, andlimitandoffsetData in the range of data
Project
  .findAndCountAll({
     where: {
        title: {
          [Op.like]: 'foo%'}},offset: 10.limit: 2
  })
  .then(result= > {
    console.log(result.count);
    console.log(result.rows);
  });
Copy the code

It also supports include queries, which only add include items marked required to the count section.

If, at the same time as retrieving the user, you want to find out their profile information:

User.findAndCountAll({
  include: [{model: Profile, required: true}].limit: 3
});
Copy the code

Because the Profile is set to Required, the result is an inline query, where only users with Profile information are counted. If required is not set, the statistics will be counted regardless of whether a profile is present.

In addition, adding the WHERE condition automatically sets required:

User.findAndCountAll({
  include: [{model: Profile, where: { active: true}}].limit: 3
});
Copy the code

In the above query, required is automatically set to true because the WHERE condition was added.

The option argument passed to findAndCountAll is the same as findAll

findAll– Search for multiple elements in the database

// Multiple elements
Project.findAll().then(projects= > {
  // Return an array
})

// Search for specific attributes - hash usage
Project.findAll({ where: { name: 'A Project' } }).then(projects= > {
  // Items will be an array of project instances with the specified name
})

// Search within a specified range
Project.findAll({ where: { id: [1.2.3] } }).then(projects= > {
  // The items will be an array of items with id 1, 2, or 3
  // This is actually an IN query
})

Project.findAll({
  where: {
    id: {
      [Op.and]: {a: 5},           // AND (a = 5)
      [Op.or]: [{a: 5}, {a: 6}].// (a = 5 OR a = 6)
      [Op.gt]: 6.// id > 6
      [Op.gte]: 6.// id >= 6
      [Op.lt]: 10.// id < 10
      [Op.lte]: 10.// id <= 10
      [Op.ne]: 20.// id ! = 20
      [Op.between]: [6.10].// BETWEEN 6 AND 10
      [Op.notBetween]: [11.15].// NOT BETWEEN 11 AND 15
      [Op.in]: [1.2].// IN [1, 2]
      [Op.notIn]: [1.2].// NOT IN [1, 2]
      [Op.like]: '%hat'.// LIKE '%hat'
      [Op.notLike]: '%hat'.// NOT LIKE '%hat'
      [Op.iLike]: '%hat'.// ILIKE '%hat' (case insensitive) (PG only)
      [Op.notILike]: '%hat'.// NOT ILIKE '%hat' (PG only)
      [Op.overlap]: [1.2].// && [1, 2] (PG array overlap operator)
      [Op.contains]: [1.2].// @> [1, 2] (PG array contains operator)
      [Op.contained]: [1.2].// <@ [1, 2] (PG array contained by operator)
      [Op.any]: [2.3]            // ANY ARRAY[2, 3]::INTEGER (PG only)
    },
    status: {
      [Op.not]: false           // status NOT FALSE}}})Copy the code

Compound filter/OR/NOT query

In queries with multiple nested AND, OR, AND NOT conditions, it can be complex. To do this, use the OR, and, or NOT operators:

Project.findOne({
  where: {
    name: 'a project',
    [Op.or]: [
      { id: [1.2.3] {},id: { [Op.gt]: 10 } }
    ]
  }
})

Project.findOne({
  where: {
    name: 'a project'.id: {
      [Op.or]: [
        [1.2.3],
        { [Op.gt]: 10}}}})Copy the code

Both pieces of code generate the following query:

SELECT *
FROM `Projects`
WHERE (
  `Projects`.`name` = 'a project'
   AND (`Projects`.`id` IN (1.2.3) OR `Projects`.`id` > 10)
)
LIMIT 1;
Copy the code

Not the sample:

Project.findOne({
  where: {
    name: 'a project',
    [Op.not]: [
      { id: [1.2.3] {},array: { [Op.contains]: [3.4.5]}}]}});Copy the code

Will be generated:

SELECT *
FROM `Projects`
WHERE (
  `Projects`.`name` = 'a project'
   AND NOT (`Projects`.`id` IN (1.2.3) OR `Projects`.`array` @> ARRAY[3.4.5]::INTEGER[])
)
LIMIT 1;
Copy the code

Use on data setslimit,offset,orderandgroup

To get more relevant data, use limit, offset, order, and group:

// Restrict the results of the query
Project.findAll({ limit: 10 })

// skip over the first 10 elements
Project.findAll({ offset: 10 })

// Skip over the first 10 elements and select 2
Project.findAll({ offset: 10.limit: 2 })
Copy the code

The syntax for grouping and sorting is the same. Here is an example of grouping and sorting.

Project.findAll({order: [['title'.'DESC']]})
// yields ORDER BY title DESC

Project.findAll({group: 'name'})
// yields GROUP BY name
Copy the code

Note that in the two examples above, the supplied strings are inserted directly into the query, that is, the column names are not escaped. This is always the case when you supply strings to sort/grouping. If you want to escape column names, you should provide an array of parameters even if you want to sort/group by a single column.

something.findOne({
  order: [
    // will return `name`
    ['name'].// will return `username` DESC
    ['username'.'DESC'].// will return max(`age`)
    sequelize.fn('max', sequelize.col('age')),
    // will return max(`age`) DESC
    [sequelize.fn('max', sequelize.col('age')), 'DESC'].// will return otherfunction(`col1`, 12, 'lalala') DESC
    [sequelize.fn('otherfunction', sequelize.col('col1'), 12.'lalala'), 'DESC'].// will return otherfunction(awesomefunction(`col`)) DESC, This nesting is potentially infinite!
    [sequelize.fn('otherfunction', sequelize.fn('awesomefunction', sequelize.col('col'))), 'DESC']]})Copy the code

To sum up, the elements of the order/group array can look like this:

  • String – will be quoted

  • Array – The first element will be quoted and the second will be appended directly

  • Object

    • Raw will be added directly without reference
    • Everything else will be ignored if not setraw, the query will fail

  • Sequelize.fn and sequelize.col – Returns the function and quoted column names

The original query

Sometimes, you might want to have a huge data set that just needs to be displayed without processing. For each row selected, Sequelize creates an instance that has the ability to update, delete, get associations, and so on. If there is a large amount of data, it may take some time. If you just need the raw data and don’t want to update anything, you can get the raw data directly like the following.

// If you need to query a lot of data and don't want to spend time building DAOs for each piece of data
// You can pass in a 'raw' option to get raw data:
Project.findAll({ where: {... },raw: true })
Copy the code

count– Statistics the number of elements in the database

This is a way to count database objects:

Project.count().then(c= > {
  console.log("There are " + c + " projects!")
})

Project.count({ where: {'id': {[Op.gt]: 25}} }).then(c= > {
  console.log("There are " + c + " projects with an id greater than 25.")})Copy the code

max– Gets the maximum value of a specific attribute in the table

Used to get the maximum value of an attribute:

/* Let's assume 3 person objects with an attribute age. The first one is 10 years old, the second one is 5 years old, the third one is 40 years old. */
Project.max('age').then(max= > {
  // this will return 40
})

Project.max('age', { where: { age: { [Op.lt]: 20 } } }).then(max= > {
  // will be 10
})
Copy the code

min– Gets the minimum value for a specific attribute in the table

Used to get the maximum value of an attribute:

/* Let's assume 3 person objects with an attribute age. The first one is 10 years old, the second one is 5 years old, the third one is 40 years old. */
Project.min('age').then(min= > {
  // this will return 5
})

Project.min('age', { where: { age: { [Op.gt]: 5 } } }).then(min= > {
  // will be 10
})
Copy the code

sum– Sums the values of a particular attribute

To calculate the sum of a specified column in a table, use the sum method:

/* Let's assume 3 person objects with an attribute age. The first one is 10 years old, the second one is 5 years old, the third one is 40 years old. */
Project.sum('age').then(sum= > {
  // this will return 55
})

Project.sum('age', { where: { age: { [Op.gt]: 5 } } }).then(sum= > {
  // will be 50
})
Copy the code

preload

When retrieving data from a database, you may want to get items associated with the same query – this is called “preloading.” The basic idea behind this is to use the include attribute when calling find or findAll. Assume the following Settings:

class User extends Model {}
User.init({ name: Sequelize.STRING }, { sequelize, modelName: 'user' })
class Task extends Model {}
Task.init({ name: Sequelize.STRING }, { sequelize, modelName: 'task' })
class Tool extends Model {}
Tool.init({ name: Sequelize.STRING }, { sequelize, modelName: 'tool' })

Task.belongsTo(User)
User.hasMany(Task)
User.hasMany(Tool, { as: 'Instruments' })

sequelize.sync().then(() = > {
  // this is where we continue ...
})
Copy the code

Now, let’s get all tasks and their associated users:

Task.findAll({ include: [ User ] }).then(tasks= > {
  console.log(JSON.stringify(tasks))

  / * [{" name ":" A Task ", "id" : 1, "createdAt" : "the 2013-03-20 T20: drought consumed. 000 z", "updatedAt" : "The 2013-03-20 T20: drought consumed 000 z", "userId" : 1, "user" : {" name ":" John Doe ", "id" : 1, "createdAt" : "The 2013-03-20 T20:31:45. 000 z", "updatedAt" : "the 2013-03-20 T20:31:45. 000 z"}}] * /
})
Copy the code

Note that the accessor (the User attribute in the result instance) is singular because the association is one-to-one.

Next, load the data in many-to-many form:

User.findAll({ include: [ Task ] }).then(users= > {
  console.log(JSON.stringify(users))

  / * [{" name ":" John Doe ", "id" : 1, "createdAt" : "the 2013-03-20 T20:31:45. 000 z", "updatedAt" : "The 2013-03-20 T20:31:45. 000 z", "tasks" : [{" name ":" A Task ", "id" : 1, "createdAt" : "The 2013-03-20 T20: drought consumed 000 z", "updatedAt" : "the 2013-03-20 T20: drought consumed. 000 z", "userId" : 1}}]] * /
})
Copy the code

Note that the accessors (the Tasks attribute in the result instance) are plural because the association is many-to-many.

If you use an alias for the association (specified by AS), you can specify the alias when you associate the model. For example, in the following example, alias Instruments is specified for user’s Tool. To handle correctly, you must specify the model to load along with the alias:

User.findAll({ include: [{ model: Tool, as: 'Instruments' }] }).then(users= > {
  console.log(JSON.stringify(users))

  / * [{" name ":" John Doe ", "id" : 1, "createdAt" : "the 2013-03-20 T20:31:45. 000 z", "updatedAt" : "2013-03-20T20:31:45.000Z", "Instruments": [{"name": "Toothpick", "ID ": 1, "createdAt": null, "updatedAt": null, "userId": 1 }] }] */
})
Copy the code

You can also include by alias by specifying a string that matches the associated alias:

User.findAll({ include: ['Instruments'] }).then(users= > {
  console.log(JSON.stringify(users))

  / * [{" name ":" John Doe ", "id" : 1, "createdAt" : "the 2013-03-20 T20:31:45. 000 z", "updatedAt" : "2013-03-20T20:31:45.000Z", "Instruments": [{"name": "Toothpick", "ID ": 1, "createdAt": null, "updatedAt": null, "userId": 1 }] }] */
})

User.findAll({ include: [{ association: 'Instruments' }] }).then(users= > {
  console.log(JSON.stringify(users))

  / * [{" name ":" John Doe ", "id" : 1, "createdAt" : "the 2013-03-20 T20:31:45. 000 z", "updatedAt" : "2013-03-20T20:31:45.000Z", "Instruments": [{"name": "Toothpick", "ID ": 1, "createdAt": null, "updatedAt": null, "userId": 1 }] }] */
})
Copy the code

When preloading, you can also associate the model with where. All Tool model records that match where criteria are returned for User:

User.findAll({
    include: [{
        model: Tool,
        as: 'Instruments'.where: { name: { [Op.like]: '%ooth%' } }
    }]
}).then(users= > {
    console.log(JSON.stringify(users))

    / * [{" name ":" John Doe ", "id" : 1, "createdAt" : "the 2013-03-20 T20:31:45. 000 z", "updatedAt" : "2013-03-20T20:31:45.000Z", "Instruments": [{"name": "Toothpick", "ID ": 1, "createdAt": null, "updatedAt": Null, "userId" : 1}}]], [{" name ":" John Smith ", "id" : 2, "createdAt" : "the 2013-03-20 T20:31:45. 000 z", "updatedAt" : "2013-03-20T20:31:45.000Z", "Instruments": [{"name": "Toothpick", "ID ": 1, "createdAt": null, "updatedAt": null, "userId": 1 }] }], */
  })
Copy the code

If the preload uses include.where filtering, include.required is set to true. This means that the relationship between the parent model and the child model is an inner join.

On the top floorwherePreloading model in

To move the WHERE condition from the ON condition of the containment model to the top-level WHERE, you can use the ‘$nested.column$’ syntax:

User.findAll({
    where: {
        '$Instruments.name$': { [Op.iLike]: '%ooth%'}},include: [{
        model: Tool,
        as: 'Instruments'
    }]
}).then(users= > {
    console.log(JSON.stringify(users));

    / * [{" name ":" John Doe ", "id" : 1, "createdAt" : "the 2013-03-20 T20:31:45. 000 z", "updatedAt" : "2013-03-20T20:31:45.000Z", "Instruments": [{"name": "Toothpick", "ID ": 1, "createdAt": null, "updatedAt": Null, "userId" : 1}}]], [{" name ":" John Smith ", "id" : 2, "createdAt" : "the 2013-03-20 T20:31:45. 000 z", "updatedAt" : "2013-03-20T20:31:45.000Z", "Instruments": [{"name": "Toothpick", "ID ": 1, "createdAt": null, "updatedAt": null, "userId": 1 }] }], */
Copy the code

Contains all the

To include all attributes, pass all: true as a single object:

User.findAll({ include: [{ all: true }]});
Copy the code

Contains soft delete records

If you want to load soft-deleted records, you can set include.paranoid to false:

User.findAll({
    include: [{
        model: Tool,
        where: { name: { [Op.like]: '%ooth%'}},paranoid: false // query and loads the soft deleted records}}]);Copy the code

Associative sorting of preloads

Here is a one-to-many relationship:

Company.findAll({ include: [ Division ], order: [ [ Division, 'name']]}); Company.findAll({include: [ Division ], order: [ [ Division, 'name'.'DESC']]}); Company.findAll({include: [{model: Division, as: 'Div'}].order: [[{model: Division, as: 'Div' }, 'name']]}); Company.findAll({include: [{model: Division, as: 'Div'}].order: [[{model: Division, as: 'Div' }, 'name'.'DESC']]}); Company.findAll({include: [{model: Division, include: [ Department ] } ],
  order: [ [ Division, Department, 'name']]});Copy the code

In this many-to-many join, it is also possible to sort the associated table:

Company.findAll({
  include: [ { model: Division, include: [ Department ] } ],
  order: [ [ Division, DepartmentDivision, 'name' ] ]
});
Copy the code

Nested preloading

Nested preloads can be used to load all related models of related models:

User.findAll({
  include: [{model: Tool, as: 'Instruments'.include: [{model: Teacher, include: [ /* etc */]}
    ]}
  ]
}).then(users= > {
  console.log(JSON.stringify(users))

  / * [{" name ":" John Doe ", "id" : 1, "createdAt" : "the 2013-03-20 T20:31:45. 000 z", "updatedAt" : "2013-03-20T20:31:45.000Z", "Instruments": [{// 1:M and N:M association "name": "Toothpick", "id": 1, "createdAt": null, "updatedAt": null, "userId": 1, "Teacher": { // 1:1 association "name": "Jimi Hendrix" } }] }] */
})
Copy the code

This generates an outer join. However, the WHERE clause on the related model creates an inner join and only returns instances with matching submodels. To return all parent instances, add required: false.

User.findAll({
  include: [{
    model: Tool,
    as: 'Instruments'.include: [{
      model: Teacher,
      where: {
        school: "Woodstock Music School"
      },
      required: false
    }]
  }]
}).then(users= > {
  / *... * /
})
Copy the code

The query above will return all users and all instruments, but only those teachers associated with Woodstock Music School.

All included also supports nested loading:

User.findAll({ include: [{ all: true.nested: true }]});
Copy the code
// Query all users
User.findAll().then(users= > {
  console.log("All users:".JSON.stringify(users, null.4));
});

Create a new user
User.create({ firstName: "Jane".lastName: "Doe" }).then(jane= > {
  console.log("Jane's auto-generated ID:", jane.id);
});

// Delete each record named "Jane"
User.destroy({
  where: {
    firstName: "Jane"
  }
}).then(() = > {
  console.log("Done");
});

// Change each record whose 'lastName' is' null 'to' Doe '
User.update({ lastName: "Doe" }, {
  where: {
    lastName: null
  }
}).then(() = > {
  console.log("Done");
});
Copy the code

The data type

Sequelize.STRING // VARCHAR(255) Sequelize.STRING(1234) // VARCHAR(1234) Sequelize.STRING.BINARY // VARCHAR BINARY Sequelize.TEXT // TEXT Sequelize.TEXT('tiny') // TINYTEXT Sequelize.CITEXT // CITEXT PostgreSQL and SQLite only. Sequelize.INTEGER // INTEGER Sequelize.BIGINT // BIGINT Sequelize.BIGINT(11) // BIGINT(11) Sequelize.FLOAT // FLOAT Sequelize.FLOAT(11) // FLOAT(11) Sequelize.FLOAT(11, 5) // FLOAT(11,10) sequelize.real // REAL PostgreSQL only.sequelize.real (11) // REAL(11) PostgreSQL only. Sequelize.REAL(11, DOUBLE(11) // DOUBLE(11) Sequelize.DOUBLE(11) // DOUBLE(11) Sequelize.DOUBLE(11) // DOUBLE(11) Sequelize. 10) // DOUBLE(11,10) sequelize.decimal // DECIMAL sequelize.decimal (10, 2) // DECIMAL(10,2) sequelize.date // DATETIME for mysql/sqlite, TIMESTAMP WITH TIME ZONE for postgres sequelize.date (6) // DATETIME(6) for mysql 5.6.4+.fractional seconds support WITH  up to 6 digits of precision Sequelize.DATEONLY // DATE without time. Sequelize.BOOLEAN // TINYINT(1) Sequelize.ENUM('value 1', 'value 2') // An ENUM with allowed values 'value 1' and 'value 2' Sequelize.ARRAY(Sequelize.TEXT) // Defines an array. PostgreSQL only. Sequelize.ARRAY(Sequelize.ENUM) // Defines an array of ENUM. PostgreSQL only. Sequelize.JSON // JSON column. PostgreSQL, SQLite and MySQL only. Sequelize.JSONB // JSONB column. PostgreSQL only. Sequelize.BLOB // BLOB (bytea for PostgreSQL) Sequelize.BLOB('tiny') // TINYBLOB (bytea for PostgreSQL. Other options are medium and long) Sequelize.UUID // UUID datatype for PostgreSQL and SQLite, CHAR(36) BINARY for MySQL (use defaultValue: Sequelize.UUIDV1 or Sequelize.UUIDV4 to make sequelize generate the ids automatically) Sequelize.CIDR // CIDR datatype for PostgreSQL Sequelize.INET // INET datatype for PostgreSQL Sequelize.MACADDR // MACADDR datatype for PostgreSQL Sequelize.RANGE(Sequelize.INTEGER) // Defines int4range range. PostgreSQL only. Sequelize.RANGE(Sequelize.BIGINT) // Defined int8range range. PostgreSQL only. Sequelize.RANGE(Sequelize.DATE) // Defines tstzrange range. PostgreSQL only. Sequelize.RANGE(Sequelize.DATEONLY) // Defines daterange range. PostgreSQL only. Sequelize.RANGE(Sequelize.DECIMAL) // Defines numrange range. PostgreSQL only. Sequelize.ARRAY(Sequelize.RANGE(Sequelize.DATE)) // Defines array of tstzrange ranges. PostgreSQL only. Sequelize.GEOMETRY // Spatial column. PostgreSQL (with PostGIS) or MySQL only. Sequelize.GEOMETRY('POINT') // Spatial column with geometry type. PostgreSQL (with PostGIS) or MySQL only. Sequelize.GEOMETRY('POINT', 4326) // Spatial column with geometry type and SRID. PostgreSQL (with PostGIS) or MySQL only.Copy the code

The appendix reference

Itbilu.com/nodejs/npm/…

Sequelize.org/master/iden…