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'),
]),
};
网友评论