This is the 17th day of my participation in the November Gwen Challenge. Check out the event details: The last Gwen Challenge 2021

I. Simple modeling examples

Sequelize provides increment and Decrement instance methods

const { Sequelize, Model, DataTypes } = require("sequelize");
const sequelize = new Sequelize("sqlite::memory:");

const User = sequelize.define("user", {
  name: DataTypes.TEXT,
  favoriteColor: {
    type: DataTypes.TEXT,
    defaultValue: 'green'
  },
  age: DataTypes.INTEGER,
  cash: DataTypes.INTEGER
});

(async() = > {// Force update of model table fields
  await sequelize.sync({ force: true}); }) ();Copy the code

Two. Numerical increment and decrement integral realization

const jane = await User.create({ name: "Li hua".age: 100 });
const incrementResult = await jane.increment('age', { by: 2 });
Increment ('age'); increment('age'); increment('age')

// In PostgreSQL, unless '{RETURNING: false}' is specified (undefined),
// Otherwise 'incrementResult' will be the updated user.

// In other database dialects, 'incrementResult' will be undefined. If you need an updated instance, you need to call 'user.reload()'.
Copy the code
  • Multiple field increment
const jane = await User.create({ name: "Jane".age: 100.cash: 5000 });
await jane.increment({
  'age': 2.'cash': 100
});

// If the value is increased by the same amount, the following other syntax can also be used:
await jane.increment(['age'.'cash'] and {by: 2 });
Copy the code

3. Model Add, Delete, Modify and Check (CRUD)

Create (create)

1. Model.create()The method is to useModel.build()Build an unsaved instance and use itinstance.save()Save the short form of the instance.

Create a new user
const user = await User.create({ firstName: "Li hua".lastName: Bruce Lee });
Copy the code

2. fieldsParameters affect only fields in that range

const user = await User.create({
  username: 'hua'.is_auth: true
}, { fields: ['username']});// Assume the default isAdmin value is false
console.log(user.username); // 'Li Hua'
console.log(user.is_auth); // false
Copy the code

Query (select)

// Query all users
const users = await User.findAll(); SELECT * FROM users
Copy the code

1. Query specific attributes

User.findAll({
  attributes: ['username'.'age']});Copy the code

2. Rename fields for nested arrays

User.findAll({
  attributes: ['age'['username'.'name']]});Copy the code

3. Aggregate querysequelize.fn

  • When you use an aggregate function, you must provide it with an alias so that it can be accessed from the model
Model.findAll({
  attributes: [
    'id'.'display'.'margin'.'width'.'height'.'padding'.// We must list all attributes...
    [sequelize.fn('COUNT', sequelize.col('margin')), 'n_margin'] // Add aggregate...]});// This design is simpler
Model.findAll({
  attributes: {
    include: [
      [sequelize.fn('COUNT', sequelize.col('margin')), 'n_margin']]}});Copy the code
  • The queryexcludeIt’s easier to exclude certain attributes
Model.findAll({
  attributes: { exclude: ['width']}});Copy the code