Sequelize介绍
- 为了快捷开发,社区出现了一系列的ORM(Object Relational Mapping)类库
- ORM的字面意思为对象关系映射,它提供了概念性的、易于理解的模型化数据的方法。通过ORM,可以降低操作数据库的成本。开发者不需要通过编写SQL脚本来操作数据库,直接通过访问对象的方式来查询、更新数据。这样做极大地提升了开发效率,降低了开发门槛。缺点也很明显,不够高效
- 在Node.js中,一般采用Sequelize这个ORM类库来操作数据库.
const Sequelize = require('sequelize');
const sequelize = new Sequelize('databaseName', 'userName', 'password', {
host:'localhost', // 数据库服务地址
dialect: 'mysql' // SQL语言类型
});
sequelize.authenticate().then(()=>{
console.log('Connected');
}).catch(err=>{
console.error('Connect failed');
})
- 使用docker创建一个数据库.
使用docker-compose.yml
写配置文件如下:
version: '3.1'
services:
mysql:
image: mysql
command: --default-authentication-plugin=mysql_native_password
restart: always
environment:
MTSQL_ROOT_PASSWORD: example
ports:
- 3306:3306
adminer:
image: adminer
restart: always
ports:
- 8080:8080
使用如下命令生成docker
docker-compose up
连接数据库
const Sequelize = require('sequelize');
const sequelize = new Sequelize('数据库名称','用户名(默认:root)','密码(docker-compose.yml中设置的)', {
host:'localhost',
dialect: 'mysql'
});
sequelize.authenticate().then(()=>{
console.log('Connected');
})
.catch(err=>{
console.error('Connect failed', err);
})
定义模型
- 常用
const Category = sequelize.define('category', {
id: Sequelize.UUID, // 定义id字段,类型为UUID
name: Sequelize.STRING // 定义name字段,类型为String
})
- 给模型加约束条件
const Project = sequelize.define('project', {
name: {
type: Sequelize.STRING, // 定位类型为String
allowNull: false, //不能为空
unique: true // 必须唯一,不允许重复
},
date: {
type: Sequelize.DATE,
defaultValue: Sequelize.NOW // 设置为当前时间
}
})
- 给字段定义Getter和Setter方法:
const Custom = sequelize.define('custom', {
name: {
type: Sequelize.STRING,
get(){
const title = this.getDataValue('title');
return `${this.getDataValue('name')} (${titile}) `
}
},
title: {
title: Sequelize.STRING,
set (val) {
this.setDataValue('title', val.toUpperCase())
}
}
})
查询数据
-
查询所有
await Product.findAll()
-
查询name和data字段
await Project.findAll({ attributes: ['name', 'date'] })
在使用一个库的时候,可以先把库的方法包装以下,变成自己的库
- 在下面的栗子中,首先把Sequelize库中的API抽离出来,根据实际的业务变成自己项目的函数.通过按需的方式引入到业务中.
- 这样做利于维护,(例如,Sequelize库变化了,不需要整个项目寻找使用到该接口的函数,或者具体改变了,重新改变接口)
栗子
- 项目结构如下:
- 设计Customer表的类型,如下:
/mysql/model/custom.js
const Sequelize = require('sequelize');
const sequelize = new Sequelize('custom', 'root', 'example', {
dialect:'mysql'
});
// 定义Customer模型
const Customer = sequelize.define('customer',{
id:{
type: Sequelize.UUID,
unique: true,
primaryKey: true,
allowNull: false
},
name: {
type: Sequelize.STRING,
allowNull: false
},
sex: {
type: Sequelize.ENUM(['男','女']),
allowNull: false
},
address:{
type: Sequelize.STRING
},
email: {
type: Sequelize.STRING,
allowNull: false
},
phone: {
type: Sequelize.STRING
},
country:{
type: Sequelize.STRING
},
city: {
type:Sequelize.STRING
}
});
- 在
/mysql/db.js
中,对表的功能进行加工
const { Customer } = require('./model/custom');
const { Op } = require('sequelize');
async function getAllCustomers() {
return Customer.findAndCountAll({
attributes: ['id', 'name', 'sex', 'fulladdress'],
order: [
['updatedAt', 'DESC']
]
})
}
async function getCustomerById(id) {
return Customer.findById(id);
}
async function getCustomerByName(name) {
return Customer.findAll({
where: {
name: {
[Op.like]: `${name}`
}
}
})
}
async function updateCustomer(id, customer) {
const item = await getCustomerById(id)
if (item) {
return item.update(customer);
} else {
throw new Error('the customer with id ${id} is not exist');
}
}
async function createCustomer(customer) {
return Customer.create(customer);
}
async function deleteCustomer(id) {
const customer = await getCustomerById(id);
if (customer) {
return customer.destroy();
}
}
- 在
/mysql/app.js
中对对应的路由设置数据库的操作方法(路由层还未抽离出来)
const {
getAllCustomers,
getCustomerById,
getCustomerByName,
createCustomer,
updateCustomer,
deleteCustomer
} = require('./db');
const koa = require('koa');
const app = new koa();
const router = new require('koa-router')();
const bodyParser = require('koa-bodyparser');
app.use(async (ctx, next) => {
try {
await next();
} catch (ex) {
// ctx.type = jsonMIME;
ctx.body = {
status: -1,
message: ex.message
}
}
})
router.get('/customer', async ctx => {
const customers = await getAllCustomers();
// ctx.type = jsonMIME;
ctx.body = {
status: 0,
data: customers
};
});
router.get('/customer/:id', async ctx => {
const customer = await getCUstomerById(ctx.params.id);
// ctx.type = jsonMIME;
ctx.body = {
status: 0,
data: customer
};
});
router.get('/customer/name/:name', async ctx => {
const customer = await getCUstomerByName(ctx.params.name);
// ctx.type = jsonMIME;
ctx.body = {
status: 0,
data: customer
};
});
router.post('/customer', async ctx => {
const customer = ctx.body;
await createCustomer(customer);
// ctx.type = jsonMIME;
ctx.body = {
status: 0
};
});
router.put('/customer/:id', async ctx => {
const id = ctx.params.id;
const customer = ctx.body;
await updateCustomer(id, customer);
// ctx.type = jsonMIME;
ctx.body = {
status: 0
};
});
router.delete('/customer/:id', async ctx => {
await deleteCustomer(ctx.params.id);
// ctx.type = jsonMIME;
ctx.body = {
stauts: 0
};
});
app.use(bodyParser());
app.use(router.routes());
app.listen(3000, async () => {
console.log('Server is running at http://localhost:3000');
})