1项棠、安裝
Linux 版
Mac 版
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 創(chuàng)建實例
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
方便快速創(chuàng)建數據庫
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'),
}
在項目根目錄下執(zhí)行
npx sequelize init
- 創(chuàng)建 migrations（定義如何創(chuàng)建表）
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'),
]),
};
- 執(zhí)行 migrations（創(chuàng)建/撤銷表）
npx sequelize db:migrate
- 創(chuàng)建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] } }, {});
},
};
- 執(zhí)行 seed
npx sequelize db:seed:all // 使用所有 seed
npx sequelize db:seed --seed xxxxxxxxx-init-shopsjs // 使用指定 seed
- 創(chuàng)建model（與數據庫表結構做對應）
npx sequelize model:create --name shops --attributes "name:string"
該命令會同時創(chuàng)建 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');
}
};
