node最佳实践3-sequelize连接数据库

语法：sequelize.org/api/v6/clas…

简介：sequelize.org/docs/v6/

最佳实践规则：climbtheladder.com/10-sequeliz…

配置sequelizerc： sequelize.org/docs/v6/oth…

使用sequelize工具，数据库使用pg

在node项目上安装pg和sequelize相关套件

npm install sequelize
npm install pg
npm install --save-dev sequelize-cli
npm install -g sequelize-cli // 全局安装

在项目根目录下执行初始化命令，如果有支持es6语法就切换到src文件下执行

npx sequelize-cli init

该命令会创建几个文件夹，分别是

• config 包含config.json
• models 数据模型
• migrations 迁移文件
• seeders 种子文件

由于我要使用pg，所以把config.json修改为

{
  "development": {
    "username": "postgres",
    "password": "postgres",
    "database": "demo_development",
    "host": "127.0.0.1",
    "dialect": "postgres"
  },
  "test": {
    "username": "postgres",
    "password": "postgres",
    "database": "database_test",
    "host": "127.0.0.1",
    "dialect": "postgres"
  },
  "production": {
    "username": "postgres",
    "password": "postgres",
    "database": "database_production",
    "host": "127.0.0.1",
    "dialect": "postgres"
  }
}

按需求修改为自己的数据库信息，默认环境是development，默认端口号5432（postgres），4406（mysql），如需指定端口可添加port 字段。

如果需要新建表，可以执行npx sequelize-cli db:create ，系统会根据配置自动创建相应的数据表。

创建model和migration

创建一个book的model

npx sequelize-cli model:generate --name Book --attributes name:string,author:string

执行指令后会

• 自动创建models/book.js
• 自动创建migrations/xxxxxxxx-create-book.js

两个文件，然后执行迁移npx sequelize-cli db:migrate 这个指令会执行最新的迁移

// 回撤最新的迁移
npx sequelize-cli db:migrate
//回撤所有迁移
db:migrate:undo:all
//回撤到某一个文件： 
db:migrate:undo:all —to xxxxx-create-book.js

创建seed

npx sequelize-cli seed:generate --name demo-book

自动创建seeds/xxxxx-demo-book.js 文件，添加默认数据

'use strict';

/** @type {import('sequelize-cli').Migration} */
module.exports = {
  async up (queryInterface, Sequelize) {
    await queryInterface.bulkInsert('Books',[
      {
        name: "Spare",
        author: "harry",
        createdAt: new Date(),
        updatedAt: new Date()
      },
      {
        name: "If you tell",
        author: "Gregg Olsen",
        createdAt: new Date(),
        updatedAt: new Date()
      },
      {
        name: "Reminders of him",
        author: "Colleen Hoover",
        createdAt: new Date(),
        updatedAt: new Date()
      }
    ])
  },

  async down (queryInterface, Sequelize) {
    await queryInterface.bulkDelete('Book',null,{})
  }
};

注意要使用复数Books

执行种子文件npx sequelize-cli db:seed:all

npx sequelize-cli db:seed:undo //回撤最后一条文件
npx sequelize-cli db:seed:undo --seed name_of_seed // 回撤指定seed文件
npx sequelize-cli db:seed:undo:all // 回撤所有seed

以上关于数据库的设置基本完成

创建controller

创建controllers 文件夹，内部新增book.controller.js 文件

const Book = require('../models').Book
module.exports = {
  let {page,pageSize} = req.query
  getRecordsByPage: (req,res) => {
    Book.findAll({
    attributes: ['id','name','author'],
    limit: pageSize,
    offset: (page-1)*pageSize,
    order: [['id','DESC']]
    }).then(books => {
      return res.status(200).json({books})
    }).catch(err => {
      return res.status(400).json({err})
    })
  }
}

如果使用es6语法也可以写成

import {Book} from '../models'

export const getRecordsByPage = (req,res) => {
  const { page,pageSize } = req.query
  Book.findAll({
    attributes: ['id','name','author'],
    limit: pageSize,
    offset: (page-1) * pageSize,
    order: [['id','DESC']]
  }).then(books => {
    return res.status(200).json({books})
  }).catch(err => {
    return res.status(400).json({err})
  })
}

创建route

创建routes文件夹，内部新增book.route.js文件

import express from 'express'
const router = express.Router()
import {
  getRecordByPage
} from '../controller/book.controller.js'
router.get('/',getRecordsByPage)
export default router

用apifox访问一下localhost:3000/books?page=1&pageSize=10 已经可以正常返回数据


完善接口

增加其他的接口请求和路由

book.controller.js

import {Book} from '../models'

export const getRecordsByPage = (req,res) => {
  console.log('params',req.query);
  const { page,pageSize } = req.query
  // findAndCountAll返回count和rows
  Book.findAndCountAll({
    attributes: ['id','name','author'],
    limit: pageSize,
    offset: (page-1) * pageSize,
    order: [['id','DESC']]
  }).then(books => {
    return res.status(200).json({books})
  }).catch(err => {
    return res.status(400).json({err})
  })
}
export const getRecordById = (req,res) => {
  const id = req.params.id
  Book.findByPk(id).then(book => {
    return res.status(200).json({book})
  }).catch(err => {
    return res.status(400).json({err})
  })
}
export const deleteRecordById = (req,res) => {
  const {id} = req.body
  Book.destroy({
    where: {id: id}
  }).then(() => {
    return res.status(200).json({
      message: "success"
    })
  }).catch(err => {
    return res.status(400).json({err})
  })
}
export const addRecord = (req,res) => {
  let { name, author } = req.body
  Book.create({
    name, author
  }).then(book => {
    return res.status(200).json({
      message: "success",
      book
    })
  }).catch(err => {
    return res.status(400).json({err})
  })
}
export const updateRecord = (req,res) => {
  let {id,name,author} = req.body
  Book.findOne({
    where:{id: id}
  }).then(book => {
    if(book){
      book.update({
        name,author
      }).then(newBook =>{
        return res.status(202).json({
          message:"success",
          book: newBook
        })
      })
    }else{
      return res.status(206).json({
        message: "record not found"
      })
    }
  }).catch(err => {
    return res.status(400).json({err})
  })
}

book.route.js

import express from 'express'

const router = express.Router()
import {
  getRecordsByPage,
  getRecordById,
  deleteRecordById,
  addRecord,
  updateRecord
} from '../controllers/book.controller'

router.get('/',getRecordsByPage)
router.get('/:id',getRecordById)
router.post('/create',addRecord)
router.post('/delete',deleteRecordById)
router.post('/update',updateRecord)
export default router

更多

climbtheladder.com/10-sequeliz…