跳到主要内容
版本:3.0.0

Sequelize

提示

本文档从 v3.4.0 版本起废弃。

本文档介绍如何在 Midway 中使用 Sequelize 模块。

相关信息:

描述
可用于标准项目
可用于 Serverless
可用于一体化

使用方法:

$ npm i @midwayjs/sequelize@3 sequelize --save

或者在 package.json 中增加如下依赖后,重新安装。

{
"dependencies": {
"@midwayjs/sequelize": "^3.0.0",
"sequelize": "^6.13.0"
// ...
},
"devDependencies": {
// ...
}
}

安装数据库 Driver

常用数据库驱动如下,选择你对应连接的数据库类型安装:

# for MySQL or MariaDB,也可以使用 mysql2 替代
npm install mysql --save
npm install mysql2 --save

# for PostgreSQL or CockroachDB
npm install pg --save

# for SQLite
npm install sqlite3 --save

# for Microsoft SQL Server
npm install mssql --save

# for sql.js
npm install sql.js --save

# for Oracle
npm install oracledb --save

# for MongoDB(experimental)
npm install mongodb --save

引入模块

在 configuration.ts 文件中

import { App, Configuration } from '@midwayjs/decorator';
import { ILifeCycle } from '@midwayjs/core';
import { Application } from 'egg';
import { join } from 'path';
import * as sequelize from '@midwayjs/sequelize';

@Configuration({
imports: [sequelize],
importConfigs: [join(__dirname, './config')],
})
export class ContainerLifeCycle implements ILifeCycle {
@App()
app: Application;

async onReady() {}
}

配置

在 config.default.ts 中配置:

// src/config/config.default.ts
export default {
// ...
sequelize: {
dataSource: {
default: {
database: 'test4',
username: 'root',
password: '123456',
host: '127.0.0.1', // 此处支持idb上面vipserver key的那种方式,也支持aliyun的地址。
port: 3306,
encrypt: false,
dialect: 'mysql',
define: { charset: 'utf8' },
timezone: '+08:00',
logging: console.log,
},
},
sync: false, // 本地的时候,可以通过sync: true直接createTable
},
};

业务层

定义 Entity

import { Column, Model, BelongsTo, ForeignKey } from 'sequelize-typescript';
import { BaseTable } from '@midwayjs/sequelize';
import { User } from './User';

@BaseTable
export class Photo extends Model {
@ForeignKey(() => User)
@Column({
comment: '用户Id',
})
userId: number;
@BelongsTo(() => User) user: User;

@Column({
comment: '名字',
})
name: string;
}
import { Model, Column, HasMany } from 'sequelize-typescript';
import { BaseTable } from '@midwayjs/sequelize';
import { Photo } from './Photo';

@BaseTable
export class User extends Model {
@Column name!: string;
@HasMany(() => Photo) Photo: Photo[];
}

使用 Entity:

查询列表

import { Config, Controller, Get, Provide } from '@midwayjs/decorator';
import { Photo } from '../entity/Photo';

@Provide()
@Controller('/')
export class HomeController {
@Get('/')
async home() {
let result = await Photo.findAll();
console.log(result);
return 'hello world';
}
}

增加数据:

import { Controller, Post, Provide } from '@midwayjs/decorator';
import { Photo } from '../entity/Photo';

@Provide()
@Controller('/')
export class HomeController {
@Post('/add')
async home() {
let result = await Photo.create({
name: '123',
});
console.log(result);
return 'hello world';
}
}

删除:

import { Controller, Post, Provide } from '@midwayjs/decorator';
import { Photo } from '../entity/Photo';

@Provide()
@Controller('/')
export class HomeController {
@Post('/delete')
async home() {
await Photo.destroy({
where: {
name: '123',
},
});
return 'hello world';
}
}

查找单个:

import { Controller, Post, Provide } from '@midwayjs/decorator';
import { Photo } from '../entity/Photo';

@Provide()
@Controller('/')
export class HomeController {
@Post('/delete')
async home() {
let result = await Photo.findOne({
where: {
name: '123',
},
});
return 'hello world';
}
}

联合查询:

import { Controller, Get, Provide } from '@midwayjs/decorator';
import { Photo } from '../entity/Photo';
import { Op } from 'sequelize';

@Provide()
@Controller('/')
export class HomeController {
@Get('/')
async home() {
// SELECT * FROM photo WHERE name = "23" OR name = "34";
let result = await Photo.findAll({
where: {
[Op.or]: [{ name: '23' }, { name: '34' }],
},
});
console.log(result);
return 'hello world';
}
}

连表查询

import { Controller, Get, Provide } from '@midwayjs/decorator';
import { User } from '../entity/User';
import { Photo } from '../entity/Photo';

@Provide()
@Controller('/users')
export class HomeController {
@Get('/')
async home() {
let result = await User.findAll({ include: [Photo] });
console.log(result);
return 'hello world';
}
}

关于 OP 的更多用法:https://sequelize.org/v5/manual/querying.html

midway + sequelize 完整使用案例 https://github.com/ddzyan/midway-practice

如果遇到比较复杂的,可以使用 raw query 方法: https://sequelize.org/v5/manual/raw-queries.html