This is the 23rd day of my participation in the Gwen Challenge in November. Check out the details: The last Gwen Challenge in 2021″

Code in addition to need to update ~

Following up on the previous article, we successfully connected to the mysql database. Today we are going to learn about model.

Create the model

The model is to be mapped to the table. We still need to use Sequelize, which is the 📟 introduction to the Model on its website

/ / deconstructed
const { Sequelize, Model, DataTypes } = require("sequelize");
// The created instance takes the type of the database
const sequelize = new Sequelize("sqlite::memory:");
The first parameter is the table name of the database
const User = sequelize.define("user", {
  DataTypes. DataTypes
  name: DataTypes.TEXT,
  // If there are multiple configurations, such as adding table defaults, write them as objects
  favoriteColor: {
    type: DataTypes.TEXT,
    defaultValue: 'green'
  },
  age: DataTypes.INTEGER,
  cash: DataTypes.INTEGER
},{
// These are the other model parameters
sequelize, // We need to pass the connection instance
modelName: 'User' // We need to select the model name
});

(async() = > {// Perform database creation
  await sequelize.sync({ force: true });
  // Here is the code}) ();Copy the code

Three parameters of sequelize.define

  • Table name but sometimes it is prefixed with the table, and you can add attributes to the third object parametermodelName: 'User'
  • The data model is an object.

First, the common data types, this is for the [MySQL] database.

/ / string
DataTypes.STRING // VARCHAR(255)
DataTypes.STRING(1234) // VARCHAR(1234)
DataTypes.STRING.BINARY // VARCHAR BINARY
DataTypes.TEXT // TEXTDataTypes.TEXT('tiny') // TINYTEXT
/ / a Boolean
DataTypes.BOOLEAN // TINYINT(1)
/ / values
DataTypes.INTEGER // INTEGER
DataTypes.BIGINT // BIGINT
DataTypes.BIGINT(11) // BIGINT(11) 
DataTypes.FLOAT // FLOAT
DataTypes.FLOAT(11) // FLOAT(11)
DataTypes.FLOAT(11.10) / / FLOAT (11, 10)
DataTypes.DOUBLE // DOUBLE
DataTypes.DOUBLE(11) // DOUBLE(11)
DataTypes.DOUBLE(11.10) / / DOUBLE (11, 10)
DataTypes.DECIMAL // DECIMAL
DataTypes.DECIMAL(10.2) / / a DECIMAL (1, 2)
/ / date
DataTypes.DATE DATETIME applies to mysql/SQLite, TIMESTAMP with time zone applies to PostgresDatatypes.date (6) // DATETIME(6) applies to mysql 5.6.4+. DataTypes.DATEONLY // DATE without time
// UUID 
{ type: DataTypes.UUID, defaultValue: Sequelize.UUIDV4 / / or Sequelize UUIDV1}
Copy the code

Then look at some common parameters

// Whether it is empty
allowNull: false./ / the default value
defaultValue: true
/ / the primary key
primaryKey: true
// Automatic growth
autoIncrement: true
Copy the code
  • Some database configurations have optional objects such as:
{
 timestamps: false // By default, a timestamp field is created for the table. If a secondary attribute is added, the timestamp field is not added at creation time
 modelName: 'User' // We need to select the model name
}
Copy the code

You can see that there are two extra fields, which are time stamps.

Model synchronization

When defining a model, you tell Sequelize some information about the tables in the database. But what if the table doesn’t actually exist in the database? What if there are, but with different columns, fewer columns or any other differences?

This is where model synchronization comes in. You can do this by calling an asynchronous function (which returns a Promise)model.sync(options). With this call,Sequelize automatically executes SQL queries against the database. Note that this only changes the tables in the database, not the model on the JavaScript side.

  • User.sync()– If the table does not exist, create the table (if it already exists, do nothing)
  • User.sync({ force: true })– The table will be created, and if the table already exists, it will be deleted first
  • User.sync({ alter: true })– This checks the current state of the table in the database (which columns it has, their data types, etc.) and then makes the necessary changes in the table to match the model.

Write our model

Create a new model folder and create user.model.js under it

User_name, password, is_admin(sequelize);

// Deconstruct the DataTypes of Sequelize
const { DataTypes } = require('sequelize')
// This is what we created to connect to the database
const seq = require('.. /db/seq')

// Create a model that prefixes the table because it automatically inferences the table name, or that doesn't
const User = seq.define('User', {
    // The id is automatically created
    user_name: {
        // Go to the file to check
        type: DataTypes.STRING,
        // Whether the constraint is null
        allowNull: false./ / the only
        unique: true.comment: 'Unique username'
    },
    password: {
        type: DataTypes.CHAR(64),
        allowNull: false.comment: 'password'
    },
    is_admin: {
        // boolean 就是 tinity(1)
        type: DataTypes.BOOLEAN,
        allowNull: false.defaultValue: 0.comment: Is it an administrator? 0 Is not an administrator.}})// force This table will delete the reconstruction document if it existed before: comment out the model reconstruction after it is used
// User.sync({ force: true })

module.exports = User
Copy the code

Create a table

Execute commands on the terminal

node src/model/user.model.js
Copy the code

It’s actually executing a construction sentenceCheck out our new database