Koa 项目中集成 MySQL 与 Sequelize

发布时间：2025-05-01 | 分类：数据库集成

安装依赖

npm install sequelize mysql2

配置数据库连接

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

const sequelize = new Sequelize('database', 'username', 'password', {
  host: 'localhost',
  dialect: 'mysql',
  logging: false
});

定义数据模型

const { DataTypes } = require('sequelize');

const User = sequelize.define('User', {
  id: {
    type: DataTypes.INTEGER,
    primaryKey: true,
    autoIncrement: true
  },
  username: {
    type: DataTypes.STRING(50),
    allowNull: false,
    unique: true
  },
  email: {
    type: DataTypes.STRING(100),
    allowNull: false
  }
}, {
  tableName: 'users',
  timestamps: true
});

在 Koa 中使用

router.get('/users', async (ctx) => {
  const users = await User.findAll();
  ctx.body = users;
});

router.post('/users', async (ctx) => {
  const user = await User.create(ctx.request.body);
  ctx.status = 201;
  ctx.body = user;
});

router.put('/users/:id', async (ctx) => {
  await User.update(ctx.request.body, {
    where: { id: ctx.params.id }
  });
  ctx.body = { success: true };
});

事务处理

router.post('/transfer', async (ctx) => {
  const t = await sequelize.transaction();
  try {
    await Account.decrement('balance', {
      by: 100,
      where: { id: 1 },
      transaction: t
    });
    await Account.increment('balance', {
      by: 100,
      where: { id: 2 },
      transaction: t
    });
    await t.commit();
    ctx.body = { success: true };
  } catch (err) {
    await t.rollback();
    ctx.throw(500, '转账失败');
  }
});