Node 操作 MySQL 数据库

2019-01-07 本文已影响23人风之化身呀

1、安装

Mac 版

Linux 版

Windows 版

2、使用 Sequelize

一般会有两个依赖： mysql2 驱动和 Sequelize ORM 框架

const Sequelize = require("sequelize");
const config = require("./config");

// 第一步：建立 ORM 连接
var sequelize = new Sequelize(
  config.database,
  config.username,
  config.password,
  {
    host: config.host,
    dialect: "mysql",
    pool: {
      max: 5,
      min: 0,
      idle: 30000
    }
  }
);

// 第二步：建立 Model
var Pet = sequelize.define(
  "pet",
  {
    id: {
      type: Sequelize.STRING(50),
      primaryKey: true
    },
    name: Sequelize.STRING(100),
    gender: Sequelize.BOOLEAN,
    birth: Sequelize.STRING(10),
    createdAt: Sequelize.BIGINT,
    updatedAt: Sequelize.BIGINT,
    version: Sequelize.BIGINT
  },
  {
    timestamps: false
  }
);

var now = Date.now();
// 第三步：利用 Model 创建实例
Pet.create({
  id: "g-" + now,
  name: "Gaffey",
  gender: false,
  birth: "2007-07-07",
  createdAt: now,
  updatedAt: now,
  version: 0
})
  .then(function(p) {
    console.log("created." + JSON.stringify(p));
  })
  .catch(function(err) {
    console.log("failed: " + err);
  });
// 查询操作 findAll
(async () => {
  var pets = await Pet.findAll({
    where: {
      name: "Gaffey"
    }
  });
  console.log(`find ${pets.length} pets:`);
  for (let p of pets) {
    console.log(JSON.stringify(p));
  }
})();
// 更新操作 save
(async () => {
    var p = await queryFromSomewhere();
    p.gender = true;
    p.updatedAt = Date.now();
    p.version ++;
    await p.save();
})();
// 删除操作
(async () => {
    var p = await queryFromSomewhere();
    await p.destroy();
})()

使用Sequelize操作数据库的一般步骤就是：

1、首先，通过某个Model对象的findAll()方法获取实例；
2、如果要更新实例，先对实例属性赋新值，再调用save()方法；
3、如果要删除实例，直接调用destroy()方法。

注意findAll()方法可以接收where、order这些参数，这和将要生成的SQL语句是对应的。

3、使用 sequelize-cli

方便快速创建数据库

npm i sequelize-cli -D
npm i sequelize
npm i mysql2

配置.sequelizerc，如果不配置的话，sequelize init 初始化的文件夹会出现在项目目录下面，如果配置了.sequelizerc 就可以指定到相应的目录

const path = require('path')
module.exports = {
    'config': path.resolve('./app','config.json'),
    'migrations-path': path.resolve('./app','migrations'),
    'models-path': path.resolve('./app','models'),
    'seeders-path': path.resolve('./app','seeders'),
}

在项目根目录下执行

npx sequelize init

创建model（与数据库表结构做对应）

npx sequelize model:create  --name shops

// models/shops.js
module.exports = (sequelize, DataTypes) => sequelize.define(
  'shops',
  {
    id: {
      type: DataTypes.INTEGER,
      primaryKey: true,
      autoIncrement: true,
    },
    name: {
      type: DataTypes.STRING,
      allowNull: false,
    },
    thumb_url: DataTypes.STRING,
  },
  {
    tableName: 'shops',
  },
);

创建 migrations（定义如何创建表）

npx sequelize migration:create --name create-shops-table

// xxxxxxxxx-create-shops-table.js
module.exports = {
  up: (queryInterface, Sequelize) => queryInterface.createTable(
    'shops',
    {
      id: {
        type: Sequelize.INTEGER,
        autoIncrement: true,
        primaryKey: true,
      },
      name: {
        type: Sequelize.STRING,
        allowNull: false,
      },
      thumb_url: Sequelize.STRING,
      created_at: Sequelize.DATE,
      updated_at: Sequelize.DATE,
    },
  ),

  down: queryInterface => queryInterface.dropTable('shops'),
};

执行迁移（创建/撤销表）

npx sequelize db:migrate

创建seed（初始化数据）

npx sequelize seed:create --name init-shops

// seeders/xxxxxxxxx-init-shops.js

const timestamps = {
  created_at: new Date(),
  updated_at: new Date(),
};

module.exports = {
  up: queryInterface => queryInterface.bulkInsert(
    'shops',
    [
      { id: 1, name: '店铺1', thumb_url: '1.png', ...timestamps },
      { id: 2, name: '店铺2', thumb_url: '2.png', ...timestamps },
      { id: 3, name: '店铺3', thumb_url: '3.png', ...timestamps },
      { id: 4, name: '店铺4', thumb_url: '4.png', ...timestamps },
    ],
    {},
  ),

  down: (queryInterface, Sequelize) => {
    const { Op } = Sequelize;
    // 删除 shop 表 id 为 1，2，3，4 的记录
    return queryInterface.bulkDelete('shops', { id: { [Op.in]: [1, 2, 3, 4] } }, {});
  },
};

追加字段

npx sequelize migration:create --name add-columns-to-shops-table
// add-columns-to-shops-table.js
module.exports = {
  up: (queryInterface, Sequelize) => Promise.all([
    queryInterface.addColumn('shops', 'address', { type: Sequelize.STRING }),
  ]),

  down: queryInterface => Promise.all([
    queryInterface.removeColumn('shops', 'address'),
  ]),
};