Install dependencies

  • Install dependencies
  • Database Configuration
  • Initialize Sequelize
  • Test the connection
  • Define a test data model
  • Synchronize data structures to the database
  • Define Controller to operate using Model
    • The new data
    • Modify the data
    • Query list
    • Query a single
    • Delete the data
  • Writing routing interfaces
npm install sequelize mysql2 --save
Copy the code

Database Configuration

Create a config.js file to configure some basic mysql configuration

module.exports = {
    environment: 'dev'.database: {
        dbName: 'island'.host: 'localhost'.port: 3306.user: 'root'.password: 'root'}}Copy the code

Initialize Sequelize

const Sequelize = require('sequelize')

const { dbName, host, port, user, password } = require('.. /config').database

const sequelize = new Sequelize(dbName, user, password, {
    port,
    host,
    dialect: 'mysql'.pool: {
        max: 5.min: 0.acquire: 30000.idle: 10000,},dialectOptions: {
        / / character set
        charset: "utf8mb4".collate: "utf8mb4_unicode_ci".supportBigNumbers: true.bigNumberStrings: true
    },
    timezone: '+ 08:00' // Time zone conversion
})
Copy the code

Test the connection

sequelize.authenticate().
    then((a)= > console.log('Link ok')).
    catch(err= > console.log('Link failed', err))
Copy the code

Define a test data model

const { Model, Sequelize } = require('sequelize')
const { sequelize } = require('.. /.. /core/db')
class Test extends Model {

}

Test.init(
    {
        id: {
            type: Sequelize.INTEGER,
            primaryKey: true.autoIncrement: true
        },
        userName: {
            type: Sequelize.STRING,
            allowNull: false.// Custom verification
            validate: { min: 1.max: 100}},password: {
            type: Sequelize.STRING,
            set(val) {
                / / form a salt
                //const salt = bcryptjs.genSaltSync(10);
                // Encrypt the password
                //const pwd = bcryptjs.hashSync(val, salt);
                this.setDataValue('password', val + 123}}}, {// Do not add timestamp attributes (updatedAt, createdAt)
        timestamps: true.// Instead of actually deleting data, set a new deletedAt property whose value is the current date when timestamps is enabled
        //paranoid: true,
        // 'createdAt' is not required
        createdAt: false.// 'updatedAt' is required, but the column name is' updateTime '
        updatedAt: 'updateTime'.// Automatically set the field to the snake naming rule
        underscored: true.// Define the table name
        tableName: 'test'.// Add a comment
        comment: 'I'm a test sheet',
        sequelize,
        // If the specified table name is in the plural form, it does not change
        freezeTableName: true})module.exports = Test
Copy the code

Where primaryKey is the primaryKey, autoIncrement is set to increment, and default createdAt and updatedAt are Sequelize

Synchronize data structures to the database

When the model is defined, the corresponding data table needs to be set up in the database. At this time, the structure needs to be synchronized. You can use the following methods for synchronization:

sequelize.sync()
Copy the code

If tables corresponding to this model already exist in the database, we can not synchronize the structure:

sequelize.sync({
    force: false
})
Copy the code

Define Controller to operate using Model

Create testController.js file and write a simple add, delete, change, check RESTful API

const Test = require('.. /models/test')
const { Faild } = require('.. /.. /core/httpException')

class TestController {}Copy the code

The new data

static async add(obj) {
    const res = await Test.create(obj)
    if(! res) {throw new Faild('Add failed')}return res
}
Copy the code

Modify the data

static async update(obj) {
    const res = await Test.update(obj, {
        where: {
            id: obj.id
        }
    })
    if(! res) {throw new Faild('Modification failed')}return res
}
Copy the code

Query list

static async findAll(obj) {
    et res= await Test.findAll()

    if(! res) {throw new Faild('Modification failed')}return res
}
Copy the code

Query a single

static async find(id) {
    let data = await Test.findByPk(id)
    / / or
    // Test.findOne({
    // where:{
    // id
    / /}
    // })
    if(! data) {throw new Faild('Search failed')}return data
}
Copy the code

Delete the data

static async dele(id) {
    const res = await Test.destroy({
        where: {
            id
        }
    })
    if(! res) {throw new Faild('Delete failed')}return res
}
Copy the code

Writing routing interfaces


const Router = require("koa-router");
const router = new Router()

const testController = require('.. /controllers/test')

router.get('/test'.async (ctx, next) => {

    const data = await testController.findAll(ctx.params.id)

    ctx.body = data
})

router.get('/test/:id'.async (ctx, next) => {

    const data = await testController.find(ctx.params.id)

    ctx.body = data
})

router.post('/test'.async (ctx, next) => {
    const data = await testController.add(ctx.request.body)
})

router.put('/test'.async (ctx, next) => {
    const data = await testController.update(ctx.request.body)
    ctx.body = data
})

router.delete('/test/:id'.async (ctx, next) => {
    const data = await testController.dele(ctx.params.id)
    ctx.body = data
})

module.exports = router
Copy the code

So far, we’ve done a simple add, delete, change, and query using Sequelize

  • Sequelize has more uses