使用 sequelize 操作 MYSQL 数据库

https://sequelize.org/

mkdir mysql-koa

npm init -y

npm install mysql2@1.7.0 -D

npm install sequelize@5.17.1 -D

新建一个 src 文件夹，里面新建一个 seq.js

const { Sequelize, } = require('sequelize');

const conf = {
    host: 'localhost',
    dialect: 'mysql'
}
// koa_weibo 自己的数据库名称，root 用户（一般情况就是这个），406103 自己数据库的密码
const seq = new Sequelize('koa_weibo', 'root', '406103', conf);

// 测试连接是否成功
seq.authenticate().then(() => {
    console.log('success')
}).catch(() => {
    console.log('fail')
})

module.exports = seq

在同级目录下新建一个 model.js

const { Sequelize, } = require('sequelize');
const seq = require('./seq.js')

// User 模型
const User = seq.define('user', {
    userName: {
    	// 指定数据的类型
        type: Sequelize.STRING,
        // 是否允许为空
        allowNull: false
    },
    password: {
        type: Sequelize.STRING,
        allowNull: false
    },
    nickName: {
        type: Sequelize.STRING,
        comment: '昵称'
    }
})

// Blog 模型
const Blog = seq.define('blog', {
    title: {
        type: Sequelize.STRING,
        allowNull: false
    },
    content: {
        type: Sequelize.STRING,
        allowNull: false
    },
    userId: {
        type: Sequelize.INTEGER,
        allowNull: false
    }
})

// 外键关联

module.exports = {
    User,
    Blog
}

同级目录下新建一个 sync.js 文件

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

require('./model.js')

seq.authenticate().then(() => {
    console.log('sync success')
}).catch(() => {
    console.log('sync fail')
})

// 强制同步
seq.sync({ force: true }).then(() => {
    console.log('sync ok')
    process.exit()
})

module.exports = {
    User,
    Blog
}

运行 node src/sync.js

打开 workbench，可以看到表已经成功建立

建立外键关系

const { Sequelize, } = require('sequelize');
const seq = require('./seq.js')

// User 模型
const User = seq.define('user', {
    。。。
})

// Blog 模型
const Blog = seq.define('blog', {
    。。。。
})

// 外键关联
Blog.belongsTo(User, {
    // Blog.userId -> User.id
    foreignKey: 'userId'
})

User.hasMany(Blog, {
    foreignKey: 'userId'
})

module.exports = {
    User,
    Blog
}

一切准备好之后，我们就可以开始对数据库进行操作了。

1. 插入数据
   （同级目录下新建一个 create.js 文件）

const { Blog, User } = require('./model.js')

!(async function () {
    const zhangsan = await User.create({
        userName: 'zhangsan',
        password: '123',
        nickName: '张三'
    })

    console.log('zhangsan', zhangsan.dataValues)

    const zhangsanId = zhangsan.dataValues.id

    const lisi = await User.create({
        userName: 'lisi',
        password: '123',
        nickName: '李四'
    })

    const lisiId = lisi.dataValues.id

    console.log('lisi', lisi.id)

    const blog1 = await Blog.create({
        title: '标题1',
        content: '内容1',
        userId: zhangsanId
    })

    const blog2 = await Blog.create({
        title: '标题2',
        content: '内容2',
        userId: zhangsanId
    })

    const blog3 = await Blog.create({
        title: '标题3',
        content: '内容3',
        userId: lisiId
    })

    const blog4 = await Blog.create({
        title: '标题4',
        content: '内容4',
        userId: lisiId
    })

    console.log('blog1', blog1.dataValues)
})()

运行 node/create.js 进行数据的插入

2. 查询数据
   （同级目录下新建一个 select.js 文件）

const { Blog, User } = require('./model.js')

!(async function () {
    // 查询一条记录
    const zhangsan = await User.findOne({
        userName: 'zhangsan',
    })

    console.log('zhangsan', zhangsan.dataValues)

    // 查询特定的列
    const zhangsanName = await User.findOne({
        attributes: ['userName', 'nickName'],
        where: {
            userName: 'zhangsan'
        }
    })

    console.log('zhangsanName', zhangsanName.dataValues)

    // 查询一个列表

    const zhangsanBlogList = await Blog.findAll({
        where: {
            userId: 1
        },
        order: [
            ['id', 'desc']
        ]
    })

    console.log('zhangsanBlogList', zhangsanBlogList.map(blog => blog.dataValues))


    // 分页
    const blogPageList = await Blog.findAll({
        limit: 2,
        offset: 2,
        order: [
            ['id', 'desc']
        ]
    })

    console.log('blogPageList', blogPageList.map(blog => blog.dataValues))

    const blogListAndCount = await Blog.findAndCountAll({
        limit: 2,
        offset: 0,
        order: [
            ['id', 'desc']
        ]
    })

    console.log('blogListAndCount', 
        blogListAndCount.count,
        blogListAndCount.rows.map(blog => blog.dataValues))

    // 外键关联查询
    const blogListWithUser = await Blog.findAndCountAll({
        order: [
            ['id', 'desc']
        ],
        include: [
            {
                model: User,
                attributes: ['userName', 'nickName'],
                where: {
                    userName: 'zhangsan'
                }
            }
        ]
    })

    console.log('blogListWithUser', 
    blogListWithUser.count,
    blogListWithUser.rows.map(blog => {
        const blogVal = blog.dataValues
        blogVal.user = blogVal.user.dataValues
        return blogVal
    }))

    // 连表查询2
    const userListWithUser = await User.findAndCountAll({
        attributes: ['userName', 'nickName'],
        include: [
            {
                model: Blog,
            }
        ]
    })

    console.log('userListWithUser', 
    userListWithUser.count,
    userListWithUser.rows.map(user => {
        const userVal = user.dataValues
        userVal.blogs = userVal.blogs.map(blog => blog.dataValues)
        return userVal
    }))

})()

运行 node/select.js 进行数据的插入

3. 数据的更新
   （同级目录下新建一个 update.js 文件）

const { Blog, User } = require('./model.js')

!(async function () {
    const zhangsan = await User.update({
        nickName: '张sa'
    }, {
        where: {
            userName: 'zhangsan'
        }
    })

    console.log('zhangsan', zhangsan[0] > 0)
})()

4. 数据的删除
   （同级目录下新建一个 update.js 文件）

const { Blog, User } = require('./model.js')

!(async function () {
    
    // 删除一条博客
    const delBlogRes = await Blog.destroy({
        where: {
            id: 4
        }
    })

    console.log('delete', delBlogRes > 0)

    const delUserRes = await User.destroy({
        where: {
            id: 1
        }
    })

    console.log('delUserRes', delUserRes > 0)

})()