typescript+sequelize+mysql基础查询实战
涉及到的库:
sequelize我们都知道它是一个orm持久层框架,所以就是基于面向对象的操作方式,本文讲解的就是入门的单表操作,sequelize在初始化的时候需要做的除了链接数据库之外,还要代理所有的实体类,在所有实体类上加上自己的crud方法,这样我们要操作时只需要导入对应的实体类即可,下面上我封装的数据库链接管理对象:
import { Options } from 'sequelize';
import { Sequelize } from 'sequelize-typescript'
import MyError from '../utils/myError';
import logger from '../utils/logger';
export class DatabaseManager {
private static manager?: DatabaseManager = null;
private dbInstance?: Sequelize = null;
private dbInstanceMap?: Map<string, Sequelize> = new Map<string, Sequelize>();
private dbConfig?: Options = null;
private dbConfigs?: Options[] = [];
private constructor(dbConfig: Options);
private constructor(dbConfigs: Options[]);
private constructor(arg: Options & Options[]) {
if (Array.isArray(arg)) {
this.dbConfigs = arg;
return;
}
this.dbConfig = arg;
}
public static getInstance(dbConfig: Options & Options[]): DatabaseManager {
if (!DatabaseManager.manager) {
DatabaseManager.manager = new DatabaseManager(dbConfig);
}
return DatabaseManager.manager;
}
public async initDbInstance(models: string): Promise<void> {
if (!this.dbConfig) {
throw new MyError('db配置错误,请检查');
}
try {
if (this.dbInstance) await this.dbInstance.close()
this.dbInstance = new Sequelize(this.dbConfig);
await this.dbInstance.authenticate();
this.dbInstance.addModels([models]);
await this.dbInstance.sync();
} catch (error) {
throw new MyError(`数据源初始化失败${error}`);
}
}
public async initDbInstances(models: string[]): Promise<void> {
if (this.dbInstanceMap.size) {
for (const [databaseName, dbInstance] of this.dbInstanceMap.entries()) {
await dbInstance.close();
this.dbInstanceMap.delete(databaseName);
}
}
const databases: string[] = [];
for (let i = 0; i < this.dbConfigs.length; i++) {
const config = this.dbConfigs[i];
if (databases.includes(config.database)) {
continue;
}
databases.push(config.database);
try {
const instance = new Sequelize(config);
await instance.authenticate();
instance.addModels([models[i]]);
await instance.sync();
this.dbInstanceMap.set(config.database, instance);
} catch (error) {
throw new MyError(`数据源初始化失败${error}`);
}
}
}
public async setDbConfig(dbConfig: Options, models: string): Promise<void> {
this.dbConfig = dbConfig;
await this.initDbInstance(models);
logger.info('切换数据源成功')
}
public async setDbConfigs(dbConfigs: Options[], models: string[]): Promise<void> {
this.dbConfigs = dbConfigs;
await this.initDbInstances(models);
logger.info('切换数据源成功')
}
public getDbConfig(): Options {
return this.dbConfig;
}
public getDbConfigs(): Options[] {
return this.dbConfigs;
}
public getDbInstance(dbName?: string): Sequelize {
if (dbName) {
if (!this.dbInstanceMap.size) {
throw new MyError('请初始化数据源!')
}
return this.dbInstanceMap.get(dbName);
}
if (!this.dbInstance) {
throw new MyError('请初始化数据源!')
}
return this.dbInstance;
}
public getAllDbInstance(): Map<string, Sequelize> {
if (!this.dbInstanceMap.size) {
throw new MyError('请先初始化数据源!')
}
return this.dbInstanceMap;
}
public close(dbName?: string): void {
if (dbName) {
this.dbInstanceMap.get(dbName) && this.dbInstanceMap.get(dbName).close();
return;
}
this.dbInstance && this.dbInstance.close()
}
}
export default DatabaseManager;这里做了个封装,可以接收多个db数据源,也可以初始化后切换单个/多个数据源,其中核心部分无非就是给实体类绑定链接好的数据库操作对象,让实体类映射到对应的表
实体类的装饰器写法:
// more annotations see https://www.npmjs.com/package/sequelize-typescript
import { Table, Column, Model, AllowNull, AutoIncrement, PrimaryKey, Comment, Unique, CreatedAt, UpdatedAt, DeletedAt, DataType, NotEmpty } from 'sequelize-typescript'
import { Optional } from 'sequelize';
import { swaggerClass, swaggerProperty } from 'koa-swagger-decorator';
export interface MyInfoAttributes {
id?: number;
createdAt?: Date;
updatedAt?: Date;
deletionDate?: Date;
uName?: string;
qq?: string;
githubUrl?: string;
content?: string;
}
export interface MyInfoCreationAttributes extends Optional<MyInfoAttributes, 'id'> { }
@swaggerClass()
@Table({
tableName: 'myinfo'
})
export default class MyInfo extends Model<MyInfoAttributes, MyInfoCreationAttributes> {
@swaggerProperty({
type: 'number',
required: false
})
@Comment('主键id')
@AllowNull(false)
@NotEmpty
@AutoIncrement
@PrimaryKey
@Column({
type: DataType.INTEGER
})
id?: number;
@swaggerProperty({
type: 'object',
required: false
})
@Comment('创建时间')
@CreatedAt
@Column({
type: DataType.DATE
})
createdAt?: Date;
@swaggerProperty({
type: 'object',
required: false
})
@Comment('更新时间')
@UpdatedAt
@Column({
type: DataType.DATE
})
updatedAt?: Date;
@swaggerProperty({
type: 'object',
required: false
})
@Comment('删除时间')
@DeletedAt
@Column({
type: DataType.DATE
})
deletionDate?: Date;
@swaggerProperty({
type: 'string',
required: true
})
@Comment('博主名字')
@Column({
type: DataType.STRING
})
uName?: string;
@swaggerProperty({
type: 'string',
required: false
})
@Comment('博主qq')
@Column({
type: DataType.STRING
})
qq?: string;
@swaggerProperty({
type: 'string',
required: false
})
@Comment('博主github地址')
@Column({
type: DataType.STRING
})
githubUrl?: string;
@swaggerProperty({
type: 'string',
required: false
})
@Comment('博主个人描述')
@Column({
type: DataType.STRING
})
content?: string;
}通过注解的形式标明字段的特性,比如主键自动增长,字段类型,字段备注啥的信息,更多的注解还请查看sequelize-typescript官方文档,swaggerProperty不是sequelize的注解,是swagger接口用来标注入参类型啥的校验信息的
如何使用实体类操作数据库?这里封装了一个baseDao,所有的dao都可以继承从而得到curd基础方法:
export default class BaseDao {
private Entity: any;
public constructor(Entity: any) {
this.Entity = Entity;
}
public async findAll(): Promise<Array<any>> {
return await this.Entity.findAll()
}
public async findById(id: number): Promise<any> {
return await this.Entity.findByPk(id)
}
public async updateById(entity: any): Promise<number> {
const [affectRowCount] = await this.Entity.update(
entity.toJSON(),
{
where: {
id: entity.id
}
}
);
return affectRowCount;
}
public async insert(entity: any): Promise<any> {
const newRecord = await entity.save()
return newRecord;
}
public async deleteById(id: number): Promise<any> {
const record = await this.Entity.findByPk(id)
return await record.destroy();
}
}可以看到其实本质就是调用实体类Entity身上的sequelize的方法进行操作,至于多表关联,级联等操作就要看文档进行实体类之间的关系映射和绑定了,这里不做赘述,事务回滚操作官方文档也有,可自行查询或者留言up一起讨论.