Koa2 connects to MySQL database to realize the operation of adding, deleting, changing and querying

Create project

install ko2 The scaffold ：

$ npm install -g koa-generator

Create the project and initialize the dependencies ：

$ koa2 -e kao2-demo
$ cd koa2-demo
$ npm install

Configure startup commands under different environment variables ：

#  install  cross-env
$ npm install cross-env --save-dev

modify package.json file ：

{
    
    "scripts": {
    
        "start": "node bin/www",
        "dev": "cross-env NODE_ENV=dev ./node_modules/.bin/nodemon bin/www",
        "prd": "cross-env NODE_ENV=production pm2 start bin/www",
        "test": "echo \"Error: no test specified\" && exit 1"
    }
}

Send a request

Process in routing file http request , With routes/index.js File as an example ：

const router = require('koa-router')()

// get request 
router.get('/profile/:username', async (ctx, next) => {
    
  //  Get dynamic parameters 
  const {
     username } = ctx.params
  //  Respond to json data 
  ctx.body = {
    
    title: 'this is profile page',
    user: {
    
      username
    }
  }
})

// post request 
router.post('/login', async (ctx, next) => {
    
  //  obtain post Request parameters 
  const {
     username, password } = ctx.request.body
  ctx.body = {
    
    code: 200,
    msg: 'login success',
    user: {
    
      username,
      password
    }
  }
})

module.exports = router

Use postman Send separately get and post Request test ：

# get request 
http://localhoost:3000/profile/tom

# post request 
http://localhost:3000/login

mysql Use

Installation dependency ：

$ npm install mysql2 sequelize --save

Connect to database ：

// seq.js

const Sequelize = require('sequelize')

const conf = {
    
  host: 'localhost',
  dialect: 'mysql'
}

//  Online environments use connection pools 
// conf.pool = {
    
// max: 5, //  The maximum number of connections in the connection pool 
// min: 0, //  The minimum number of connections in the connection pool 
// idle: 10000, //  A connection in 10s Not used within , Then release 
// }

const seq = new Sequelize('datasets', 'root', 'root', conf)

module.exports = seq

Create connection ：

// sync.js

const seq = require('./seq')
require('./model')

//  Test connection 
seq.authenticate().then(() => {
    
  console.log('mysql ok')
}).catch(() => {
    
  console.log('mysql no')
})

//  Perform synchronization ,force  by true Delete the old table and create a new table 
seq.sync({
     force: false }).then(() => {
    
  console.log('sync ok')
  process.exit()
})

Creating a data model ：

// model.js

const Sequelize = require('sequelize')
const seq = require('./seq')

//  establish Users Model 
const Users = seq.define('users', {
    
  id: {
    
    type: Sequelize.INTEGER, // varchar(255)
    allowNull: false, //  Not empty 
    primaryKey: true, //  Primary key 
    comment: ' user ID' //  remarks 
  },
  username: {
    
    type: Sequelize.STRING,
  },
  pwd: {
    
    type: Sequelize.STRING
  },
  nikename: {
    
    type: Sequelize.STRING
  }
})

//  establish blog Model 
const Blogs = seq.define('blogs', {
    
  title: {
    
    type: Sequelize.STRING,
    allowNull: false
  },
  desc: {
    
    type: Sequelize.STRING,
    allowNull: false
  },
  content: {
    
    type: Sequelize.TEXT, //  Big text types 
    allowNull: false
  },
  userId: {
    
    type: Sequelize.INTEGER,
    allowNull: false
  }
})

//  Foreign key link 
Blogs.belongsTo(Users, {
    
  foreignKey: 'userId' //  Create foreign keys  Blogs.userId -> Users.id
})

Users.hasMany(Blogs, {
    
  foreignKey: 'userId' //  Create foreign keys  Blogs.userId -> Users.id
})

// Blogs.belongsTo(Users)

module.exports = {
    
  Users,
  Blogs
}

insert data

// create.js

const {
     Users } = require('./model')

!(async function() {
    
  //  Create user 
  const zhangsan = await Users.create({
    
    id: '112',
    username: ' Li Si ',
    pwd: '123456',
    nikename: 'hello'
  })

  console.log(' insert data ：', zhangsan.dataValues)
})()

Query data

// select.js

const {
     Blogs, Users } = require('./model')

!(async function() {
    
  //  Query a piece of data 
  const zhangsan = await Users.findOne({
    
    where: {
    
      username: ' Zhang San '
    }
  })
  console.log(' Query a piece of data ：', zhangsan.dataValues)

  //  Query specific columns 
  const names = await Users.findOne({
    
    attributes: ['username', 'nikename']
  })
  console.log(' Query specific columns ：', names.dataValues)

  //  Query list 
  const list = await Users.findAll({
    
    where: {
    
      id: 1
    },
    order: [
      ['id', 'desc'] //  Descending 
    ]
  })
  console.log(' Query all the data ：', list.map(users => users.dataValues))

  //  Pagination 
  const pageList = await Users.findAll({
    
    limit: 2, //  Limit the number of queries this time （ Number of entries per page ）
    offset: 0, //  How many （ The starting position ）
    order: [
      ['id', 'asc'] //  Ascending 
    ]
  })
  console.log(' Paging query ：', pageList.map(users => users.dataValues))

  //  Total number of queries 
  const usersCount = await Users.findAndCountAll({
    
    limit: 2, //  Limit the number of queries this time （ Number of entries per page ）
    offset: 0, //  How many （ The starting position ）
    order: [
      ['id', 'asc'] //  Ascending 
    ]
  })
  console.log(' Total number of queries ：',
    usersCount.count, //  All totals , Do not consider paging 
    usersCount.rows.map(users => users.dataValues)
  )

  //  Even the table query 1： adopt Blogs Inquire about Users
  const blogListWithUser = await Blogs.findAndCountAll({
    
    order: [
      ['id', 'desc']
    ],
    include: [
      {
    
        model: Users,
        attributes: ['username', 'nikename'],
        where: {
    
          username: ' Zhang San '
        }
      }
    ]
  })
  console.log(' Query results ：',
    blogListWithUser.count,
    blogListWithUser.rows.map(blogs => {
    
      const blogVals = blogs.dataValues
      blogVals.user = blogVals.user.dataValues //  many-to-one 
      return blogVals
    })
  )

  //  Even the table query 2： adopt Users Inquire about Blogs
  const usersListWithBlogs = await Users.findAndCountAll({
    
    attributes: ['username', 'nikename'],
    include: [
      {
    
        model: Blogs
      }
    ]
  })
  console.log(' Query results ：',
    usersListWithBlogs.count,
    usersListWithBlogs.rows.map(users => {
    
      const userVals = users.dataValues
      userVals.blogs = userVals.blogs.map(blog => blog.dataValues) //  One-to-many relation 
      return userVals
    })
  );
})()

Modifying data

// update.js

const {
     Users } = require('./model')

!(async function() {
    
  //  Modify user information 
  const updateRes = await Users.update({
    
    nikename: 'zhangsan' //  Modified value 
  }, {
    
    where: {
    
      username: ' Zhang San ' //  Conditions 
    }
  })
  console.log(' Modify the result ：', updateRes[0] > 0)
})()

Delete data

// delete.js

const {
     Users } = require('./model')

!(async function() {
    
  //  Delete a piece of data 
  const deleteRes = await Users.destroy({
    
    where: {
    
      id: 111
    }
  })
  console.log(' Delete result ：', deleteRes > 0)
})()

Fuzzy query

// select.js

const {
     Blogs, Users } = require('./model')
const Op = require('sequelize').Op //  You have to import 

!(async function(keywords) {
    
  //  Paging query 
  const pageList = await Users.findAll({
    
    limit: 2, //  Limit the number of queries this time （ Number of entries per page ）
    offset: 0, //  How many （ The starting position ）
    order: [
      ['id', 'asc'] //  Ascending 
    ],
    where: {
     //  Query criteria 
        nikename: {
    
            [Op.like]: `%${
      keywords}%`  //  key word 
        },
        id: '1234565'
    }
  })
  console.log(' Paging query ：', pageList.map(users => users.dataValues))
})()