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: mysqlcommand: --default-authentication-plugin=mysql_native_passwordrestart: alwaysenvironment:MTSQL_ROOT_PASSWORD: exampleports:- 3306:3306adminer:image: adminerrestart: alwaysports:- 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字段,類型為UUIDname: Sequelize.STRING    // 定義name字段,類型為String
})

• 給模型加約束條件

const Project = sequelize.define('project', {name: {type: Sequelize.STRING,   // 定位類型為StringallowNull: 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');
})