PJCHENder 未整理筆記

[npm] sequelize 使用筆記

2020-05-19

[npm] sequelize 使用筆記

CLI

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
$ npm install --save sequelize-cli

$ npx sequelize init

$ npx sequelize db:create
$ npx sequelize db:migrate
$ npx sequelize db:seed:all
$ npx sequelize db:seed:undo:all
$ npx sequelize db:drop

# 針對測試環境,在最前面加上 NODE_ENV=test
$ NODE_ENV=test npx sequelize db:create
$ NODE_ENV=test npx sequelize db:migrate

# 建立 User Model 及其對應欄位
$ npx sequelize model:generate --name User --attributes name:string,email:string,password:string
$ npx sequelize model:generate --name Restaurant --attributes name:string,tel:string,address:string,opening_hours:string,description:text

# 新增 migration 檔案,名稱為 add-isAdmin-to-users
$ npx sequelize migration:generate --name add-isAdmin-to-users

# 新增 seed 檔案
$ npx sequelize seed:generate --name my-seed-file

Controller 常用方法

Query

1
2
3
4
5
6
7
8
9
10
11
12
13
const db = require('../models');
const { User } = db;

// findAll
User.findAll({
raw: true,
order: [
createdAt:
]
}).then(users);

// findByPK
User.findByPk(userId).then(user)

Create

1
2
3
4
5
6
7
8
const db = require('../models');
const { User } = db;

User.create({
name,
tel,
address
});

Update

1
2
3
4
5
6
7
const db = require('../models');
const { User } = db;

const user = await User.findByPk(userId);
await user.update({
tel,
});

Delete

1
2
3
4
5
const db = require('../models');
const { User } = db;

const user = await User.findByPk(userId);
user.destroy();

Model 常用方法

hasMany

1
2
3
4
5
6
7
8
9
10
module.exports = (sequelize, DataTypes) => {
const Category = sequelize.define('Category', {
name: DataTypes.STRING,
}, {});
Category.associate = function (models) {
// 將可以使用 category.Restaurants
Category.hasMany(models.Restaurant);
};
return Category;
};

belongsTo

1
2
3
4
5
6
7
8
9
10
11

module.exports = (sequelize, DataTypes) => {
const Restaurant = sequelize.define('Restaurant', {
// ...
}, {});
Restaurant.associate = function (models) {
// 將可以使用 restaurant.Category
Restaurant.belongsTo(models.Category);
};
return Restaurant;
};

取得 dataValues 的資料

keywords: plain, raw, toJSON
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
// 使用 { raw: true }
// (?)hasMany 的 model 沒辦法使用 raw: true
Model.findAll({
raw: true,
});

// 針對取出來的資料,使用 instance.toJSON()
Model.findById(1).then(data => {
console.log(data.toJSON());
});

// 針對取出來的資料,使用 instance.get({ plain: true })
// const users = await db.users.findAll({}).map(el => el.get({ plain: true }))
Model.findById(1).then(data => {
console.log(data.get({ plain: true }));
});

// 使用 dataValues
Model.findAll().then((results) => {
results = results.map((result) => ({
...result.dataValues,
text: result.text,
}))
})

錯誤處理

SequelizeConnectionError

SequelizeConnectionError: Client does not support authentication protocol requested by server; consider upgrading MySQL client

執行下面的 SQL

1
ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'your_password';

MySQL 8.0 - Client does not support authentication protocol requested by server; consider upgrading MySQL client

Example / Sample

Express

sequelize/express-sample @ Github

Migration Samples

新增 table

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29

module.exports = {
up: (queryInterface, Sequelize) => queryInterface.createTable('Users', {
id: {
allowNull: false,
autoIncrement: true,
primaryKey: true,
type: Sequelize.INTEGER,
},
name: {
type: Sequelize.STRING,
},
email: {
type: Sequelize.STRING,
},
password: {
type: Sequelize.STRING,
},
createdAt: {
allowNull: false,
type: Sequelize.DATE,
},
updatedAt: {
allowNull: false,
type: Sequelize.DATE,
},
}),
down: (queryInterface, Sequelize) => queryInterface.dropTable('Users'),
};

新增欄位

1
2
3
4
5
6
7
8
9
10
// 對 Users Model 新增 isAdmin 的欄位
module.exports = {
up: (queryInterface, Sequelize) => queryInterface.addColumn('Users', 'isAdmin', {
type: Sequelize.BOOLEAN,
defaultValue: false,
}),
down: (queryInterface, Sequelize) => {
queryInterface.removeColumn('Users', 'isAdmin');
},
};

新增外鍵(foreign key)

1
2
3
4
5
6
7
8
9
10
11
12
13
// 對 Restaurants Model 新增一個名為 CategoryId 的 foreign key 欄位
module.exports = {
up: (queryInterface, Sequelize) => queryInterface.addColumn('Restaurants', 'CategoryId', {
type: Sequelize.INTEGER,
allowNull: false,
reference: {
model: 'Categories',
key: 'id',
},
}),

down: (queryInterface, Sequelize) => queryInterface.removeColumn('Restaurants', 'CategoryId'),
};

在 Sequelize,預設的外鍵欄位名稱是「Model 名稱 + Id」,而 Model 名稱一般都是以大寫命名,因此這裡會用 CategoryId 而非 categoryId

Seeder Samples

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
// ./seeders/my-seed-file.js
const bcrypt = require('bcryptjs');

module.exports = {
up: (queryInterface, Sequelize) => queryInterface.bulkInsert('Users', [{
email: 'root@example.com',
password: bcrypt.hashSync('12345678', bcrypt.genSaltSync(10), null),
isAdmin: true,
name: 'root',
createdAt: new Date(),
updatedAt: new Date(),
}, {
email: 'user1@example.com',
password: bcrypt.hashSync('12345678', bcrypt.genSaltSync(10), null),
isAdmin: false,
name: 'user1',
createdAt: new Date(),
updatedAt: new Date(),
}, {
email: 'user2@example.com',
password: bcrypt.hashSync('12345678', bcrypt.genSaltSync(10), null),
isAdmin: false,
name: 'user2',
createdAt: new Date(),
updatedAt: new Date(),
}]),

// 把 Users Table 中的資料清空,並且寫入 null
down: (queryInterface, Sequelize) => queryInterface.bulkDelete('Users', null, {})
,
};

掃描二維條碼,分享此文章