Node 操作 MySQL 数据库(Sequelize+Migration)

1、安装

Mac 版

地址
mysql-5.7.24-macos10.14-x86_64.dmg

问题
mac版MySQL初始密码和登入问题
mac下启动/停止/重启mysql服务

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

创建 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 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'),
  ]),
};

• 执行 migrations（创建/撤销表）

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] } }, {});
  },
};

• 执行 seed

npx sequelize db:seed:all // 使用所有 seed
npx sequelize db:seed --seed xxxxxxxxx-init-shopsjs // 使用指定 seed

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

npx sequelize model:create --name shops --attributes “name:string”

该命令会同时创建 models/shops.js 和 migrations/ xxx-create-shops.js，所以上述的migration命令用的不多，并且xxx-create-shops.js 会自动增加三个字段 id , createdAt , updatedAt

// models/shops.js
module.exports = (sequelize, DataTypes) => sequelize.define(
  'shops',
  {
    name: {
      type: DataTypes.STRING,
      allowNull: false,
    }
  },
  {
    tableName: 'shops',
  },
);
// migrations/xxx-create-shops.js
'use strict';
module.exports = {
  up: (queryInterface, Sequelize) => {
    return queryInterface.createTable('shops', {
      id: {
        allowNull: false,
        autoIncrement: true,
        primaryKey: true,
        type: Sequelize.INTEGER
      },
      name: {
        type: Sequelize.STRING
      },
      createdAt: {
        allowNull: false,
        type: Sequelize.DATE
      },
      updatedAt: {
        allowNull: false,
        type: Sequelize.DATE
      }
    });
  },
  down: (queryInterface, Sequelize) => {
    return queryInterface.dropTable('shops');
  }
};

参考

JavaScript 全栈教程

基于 hapi 的 Node.js 小程序后端开发实践指南

---

作者：风之化身呀
链接：https://www.jianshu.com/p/b3689f3cbd01
来源：简书
简书著作权归作者所有，任何形式的转载都请联系作者获得授权并注明出处。