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一起讨论.