pg-dynamic-query

一个用于 PostgreSQL 和 MySQL 的动态查询库，提供简洁的 API 来执行数据库操作，支持复杂查询、分页、事务和导航查询。

目录

• 使用例子
  • PostgreSQL 例子
  • MySQL 例子
• 详细说明
  • DynamicQuery
    • DynamicQuery 用到的参数类
    • Query 要怎么写?
      • 基本用法
      • 操作符
      • 另一种 or、and 和 in
    • 导航查询
    • 事务
  • EntityDynamicQuery
    • 装饰器
  • switchSqlLog()
  • PageResponse
  • TableConfig
    • ParentConfig
  • DbClient, Postgresql, MySql

使用例子

PostgreSQL 例子

const {Postgresql, DynamicQuery, PageResponse, TableConfig} = require('pg-dynamic-query');

(async () => {
    // 配置 PostgreSQL 连接
    let postgresql = new Postgresql({
        "host": "****",
        "port": 1921,
        "database": "****",
        "user": "****",
        "password": "****"
    });  

    await postgresql.connect();
    const userConfig = new TableConfig("user_info", [
        "id",
        "name",
        "age"
    ]);
    const pageResponse = new PageResponse();
    const dynamicQuery = new DynamicQuery(userConfig, postgresql);
    
    // 保存数据
    const r = await dynamicQuery.save({name: 'zh', age: 13});
    console.log('Saved user:', r);
    
    // 删除数据
    await dynamicQuery.remove([r.id]);
    
    // 分页查询
    await dynamicQuery.find({}, pageResponse);
    console.log("PageResponse:");
    console.log(pageResponse);
})();

控制台输出示例:

create
insert into "user_info"("name","age") values('zh',13) RETURNING id
findById
select *
                 from user_info
                 where id = '12'
remove
delete
               from user_info
               where id in (12)
findBySql countSql
select count(1)
 from user_info
findBySql findSql
select * 
 from user_info
 order by id  desc  limit 10 offset 0
pageResponse:
PageResponse {
  totalElements: 3,
  page: 0,
  size: 10,
  content: [
    { id: 3, name: 'zh', age: 13 },
    { id: 2, name: 'zh', age: 12 },
    { id: 1, name: 'zh', age: 13 }
  ],
  orderBy: 'id',
  direction: 'desc',
  totalPages: 1
}

MySQL 例子

const {MySql, DynamicQuery, PageResponse, switchSqlLog, TableConfig} = require('pg-dynamic-query');

(async () => {
    let mySql = new MySql({
        "host": "****",
        "port": 3306,
        "database": "****",
        "user": "****",
        "password": "****"
    });

    await mySql.connect();
    const userConfig = new TableConfig("user_info", [
        "id",
        "name",
        "age"
    ]);
    const pageResponse = new PageResponse();
    const dynamicQuery = new DynamicQuery(userConfig, mySql);
    
    // 保存数据
    const r = await dynamicQuery.save({id: 2, name: 'zh1', age: 13});
    console.log('Saved user:', r);
    
    // 分页查询
    await dynamicQuery.find({}, pageResponse);
    console.log("PageResponse:");
    console.log(pageResponse);
})();

详细说明

DynamicQuery

DynamicQuery 是核心查询类，提供了丰富的数据库操作方法。

export class DynamicQuery {
    /**
     * 构造方法需要传入 TableConfig 和 DbClient 实例
     */
    constructor(tableConfig: TableConfig, client: DbClient);

    // --- 查询方法 ---

    /**
     * 单表分页查询方法，数据会在 PageResponse.content 属性中
     */
    find(query: Query, page: PageResponse, transaction?: Transaction): Promise<void>;

    /**
     * 导航分页查询方法，前提需在 TableConfig 配置 parents 信息
     */
    navigationFind(query: Query, page: PageResponse, transaction?: Transaction): Promise<void>;

    /**
     * 通过 Sql 对象分页查询
     */
    findBySql(sql: Sql, page: PageResponse, transaction?: Transaction): Promise<void>;

    /**
     * 通过 sql 查询所有匹配记录
     */
    findAllBySql(querySql: string, transaction?: Transaction): Promise<any[]>;

    /**
     * 传入 Query 对象和 OrderBy，返回所有符合条件记录
     */
    findAll(query: Query, transaction?: Transaction, page?: OrderBy): Promise<any[]>;

    /**
     * 导航查询全部
     */
    navigationFindAll(query: Query, transaction?: Transaction, page?: OrderBy): Promise<any>;

    /**
     * 导航查询，返回第一个匹配记录
     */
    navigationFindOne(query: Query, transaction?: Transaction): Promise<any>;

    /**
     * 传入 Query 对象，返回第一个符合的对象
     */
    findOne(query: Query, transaction?: Transaction): Promise<any>;

    /**
     * 传入 sql，返回第一个记录
     */
    findOneBySql(sql: string, transaction?: Transaction): Promise<any>;

    /**
     * 传入 Query，返回符合条件的数目
     */
    count(query: Query, transaction?: Transaction): Promise<number>;

    /**
     * 传入 from where sql，返回符合数目
     */
    countBySql(sql: string, transaction?: Transaction): Promise<number>;

    // --- 修改方法 ---

    /**
     * 创建新记录，hasReturn 控制是否返回创建的记录，默认返回
     */
    create(data: object, hasReturn?: boolean, transaction?: Transaction): Promise<any>;

    /**
     * 保存方法，有则更新，无则创建，只能用于单主键，会返回结果
     */
    save(data: object, transaction?: Transaction): Promise<any>;

    /**
     * 批量保存
     */
    saveAll(entities: object[], transaction?: Transaction): Promise<object[]>;

    /**
     * 更新方法，isAllUpdate 为 true 为全部更新，false 为部分更新，默认为 true，会返回结果
     */
    update(data: object, isAllUpdate?: boolean, transaction?: Transaction): Promise<any>;

    /**
     * 根据 ids 更新，会返回更新结果集
     */
    updateByIds(data: object, ids: any[], isAllUpdate?: boolean, transaction?: Transaction): Promise<any[]>;

    /**
     * 根据 Query 更新数据，不会返回结果集
     */
    updateByQuery(data: object, query: Query, isAllUpdate?: boolean, transaction?: Transaction): Promise<void>;

    /**
     * 根据 Query 更新数据，返回结果集
     */
    updateByQueryWithResult(data: object, query: Query, isAllUpdate?: boolean, transaction?: Transaction): Promise<any[]>;

    /**
     * 根据 id 删除
     */
    remove(ids: any[], transaction?: Transaction): Promise<void>;

    /**
     * 根据 Query 查询，删除相应记录
     */
    removeByQuery(query: Query, transaction?: Transaction): Promise<void>;

    // --- 事务方法 ---

    /**
     * 事务方法，func 中的所有数据库操作都会在一个事务中
     */
    tx(func: (transaction: Transaction) => Promise<any>): Promise<any>;

    // --- 工具方法 ---

    /**
     * 传入 query 对象，和表别名(可不传)，返回一个转化后的 sql 条件数组
     */
    getConditions(query: Query, tableAlias?: string): string[];

    /**
     * 传入 query 对象，和表别名(可不传)，返回 where sql 语句
     */
    getWhere(query: Query, tableAlias?: string): string;

    /**
     * 传入 OrderBy 对象，返回 order by sql 语句
     */
    static getOrderBy(orderBy: OrderBy): string;
}

DynamicQuery 用到的参数类

export type Sql = {
    selectSql: string;
    formWhereSql: string;
    orderBySql: string;
    countInSql?: string;
};

export type OrderBy = {
    orderBy?: string;
    direction?: Direction;
};

export type Query = object;

export type PageRequest = {
    page: number;
    size: number;
    orderBy?: string;
    direction?: Direction;
};

export type Direction = 'asc' | 'desc';

/**
 * DynamicQuery.tx 传入的 callback 中会获得此实例
 */
export abstract class Transaction {
    abstract oneOrNone(sql: string): Promise<any>;
    abstract one(sql: string): Promise<any>;
    abstract query(sql: string): Promise<any>;
    abstract none(sql: string): Promise<void>;
}

Query 要怎么写?

注意: Query 的段名必须与数据库中字段名一样。

假定表格 User 数据如下:

[
  { "id": 1, "name": "张三", "age": 10 },
  { "id": 2, "name": "李四", "age": 12 }
]

基本用法

// 表示 name = "张三" 的记录
let query = {name: "张三"};

// 表示 name like "%张%"
query = {name: "%张%"};

// 表示 name is null
query = {name: "$null"};

// 表示 name is not null
query = {name: "$nn"};

操作符

// 表示 name in ["张三"]
query = {name: {$in: ["张三"]}};

// 表示 name not in ["张三"]
query = {name: {$nin: ["张三"]}};

// 表示 name = "张三"
query = {name: {$eq: "张三"}};

// 表示 name != "张三"
query = {name: {$ne: "张三"}};

// 表示 age >= 18
query = {age: {$gte: 18}};

// 表示 age > 18
query = {age: {$gt: 18}};

// 表示 age <= 18
query = {age: {$lte: 18}};

// 表示 age < 18
query = {age: {$lt: 18}};

// 表示 age between 0 and 10
query = {age: {$between: [0, 10]}};

// 表示 name = "张三" or age = 18
query = {
  $or: {
    name: "张三",
    age: 18
  }
};

// 表示 name = "张三" and age = 18
query = {
  $and: {
    name: "张三",
    age: 18
  }
};

另一种 or、and 和 in

// 表示 (name = "张三") or (age = 18)
query = {
  $or: [
    { name: "张三" },
    { age: 18 }
  ]
};

// 表示 (name = "张三") and (age = 18)
query = {
  $and: [
    { name: "张三" },
    { age: 18 }
  ]
};

// 表示 name in ["张三"]
query = { name: ["张三"] };

导航查询

以 navigation 开始的方法都是导航查询。使用导航查询需在 TableConfig 中配置好 ParentConfig。

假定表数据如下:

table_user:

[
  { "id": 1, "name": "张三", "age": 10, "department_id": 1 },
  { "id": 2, "name": "李四", "age": 12, "department_id": 2 }
]

table_department:

[
  { "id": 1, "name": "部门1" },
  { "id": 2, "name": "部门2" }
]

TableConfig 配置如下:

const UserConfig = new TableConfig('table_user', [
  'id', 'name', 'age', 'department_id'
]);
UserConfig.parents.push({
  parentId: 'department_id',     // user 表中的外键字段
  parentIdName: 'id',           // department 表中的主键字段
  parentObject: 'department',    // 查询结果中，department 数据所在的字段名
  parentTable: 'table_department' // 父表表名
});

导航查询可对于父表字段进行过滤，query 如下:

// 表示 department.name = "部门1"
query = {
  department: {
    name: "部门1",
  }
};

事务

// 将方法传入 DynamicQuery 实例的 tx 方法中。
// 方法中会获得 transaction 对象，将它传入需要在同一事务中执行的方法。
await dynamicQuery.tx(async transaction => {
    const r = await dynamicQuery.save({name: 'zh', age: 13}, transaction);
    // 如果这里抛出错误，事务将回滚
    // throw new Error("test"); 
    
    await dynamicQuery.remove([r.id], transaction);
    await dynamicQuery.find({}, pageResponse, transaction);
    console.log("pageResponse:");
    console.log(pageResponse);
});

EntityDynamicQuery

EntityDynamicQuery 是 DynamicQuery 的增强版，利用 TypeScript 装饰器和元数据，自动配置 TableConfig，并将数据库结果直接映射为实体类实例，无需手动转换。

export class EntityDynamicQuery<T extends object> extends DynamicQuery {
    constructor(client: DbClient, entityClass: new () => T);
    
    // 所有方法签名与 DynamicQuery 类似，但返回值会自动映射为 T 或 T[]
    // 例如:
    async findAll(query: QueryCondition = {}, transaction?: Transaction, page?: OrderBy): Promise<T[]>;
    async findOne(query: QueryCondition = {}, transaction?: Transaction): Promise<T | null>;
    // ... 其他方法
}

装饰器

EntityDynamicQuery 的魔力来自于装饰器。

@Entity(tableName: string)

用于标记一个类为实体类，并指定其对应的数据库表名。

@Entity("user_info")
class User {
    id: number = 0;
    name: string = '';
    // ...
}

@Parent(config: ParentParams)

用于标记实体类中的导航属性（即父对象）。

@Entity("user_info")
class User {
    // ...

    @Parent({
        parentClass: Department,       // 父实体类
        parentId: 'department_id'      // 本实体中的外键字段
    })
    department: Department = {};
}

@TableField(config?: { ignore?: boolean })

用于标记字段。ignore: true 可以在生成 columnSet 时忽略该字段。

class User {
    name: string = '';
    
    @TableField({ ignore: true })
    internalStatus: string = ''; // 此字段不会被包含在 columnSet 中
}

完整示例:

import {Entity, EntityDynamicQuery, Parent, Postgresql} from "../index";

@Entity("department_info")
class Department {
    id: number = 0;
    name: string = '';
}

@Entity("user_info")
class User {
    id: number = 0;
    name: string = '';
    age: number = 0;
    department_id: number = 0;

    @Parent({parentClass: Department, parentId: 'department_id'})
    department: Department = {};
}

// 使用
const postgresql = new Postgresql({ /* ... */ });
await postgresql.connect();
const userQuery = new EntityDynamicQuery<User>(postgresql, User);

// 保存用户，返回的 user 对象是 User 类的实例
const user = await userQuery.save({ name: 'New User', age: 25, department_id: 1 });
console.log(user instanceof User); // true

// 查询用户，返回的 department 对象是 Department 类的实例
const foundUser = await userQuery.navigationFindOne({ name: 'New User' });
console.log(foundUser.department instanceof Department); // true

PageResponse

用于封装分页查询的结果。

export class PageResponse<T> {
    /**
     * 总条数
     */
    totalElements: number;
    /**
     * 当前页码，从 0 开始
     */
    page: number;
    /**
     * 每页展示数据条数
     */
    size: number;
    /**
     * 当前页的数据数组
     */
    content: T[];
    /**
     * 总页数
     */
    totalPages: number;
    /**
     * 排序字段
     */
    orderBy: string;
    /**
     * 排序方向: 'asc' | 'desc'
     */
    direction: Direction;

    constructor();

    /**
     * 从 Express 的 req.query 对象创建 PageResponse 实例
     */
    static of<T>(req: any): PageResponse<T>;

    /**
     * 从 req.query 获取分页参数对象
     */
    static getPageAndSize(req: any): PageRequest;
}

TableConfig

用于配置表与实体的映射关系。

export class TableConfig {
    /**
     * 数据库表名
     */
    table: string;
    /**
     * 表的所有字段名数组
     */
    columnSet: string[];
    /**
     * 主键字段名，默认为 'id'
     */
    idName: string | string[];
    /**
     * 是否自增主键，默认为 true
     */
    idAutoCreate: boolean;
    /**
     * JSON 格式的字段名数组
     */
    jsonColumn: string[];
    /**
     * 创建时间字段名，设置后，创建时会自动赋值
     */
    createTime: string;
    /**
     * 更新时间字段名，设置后，更新时会自动赋值
     */
    updateTime: string;
    /**
     * 创建操作是否返回对象，默认为 true
     */
    createReturn: boolean;
    /**
     * 父表（导航查询）配置信息
     */
    parents: ParentConfig[];

    constructor(table: string, columnSet: string[]);
}

ParentConfig

用于配置导航查询中的父表信息。

export interface ParentConfig<T = any> {
    /**
     * 本表中的外键字段名
     */
    parentId: string;
    /**
     * 查询结果中，父表数据所在的字段名
     */
    parentObject: string;
    /**
     * 父表表名
     */
    parentTable: string;
    /**
     * 父表主键字段名
     */
    parentIdName: string;
    /**
     * 父实体类（用于 EntityDynamicQuery）
     */
    parentClass?: new () => T;
}

DbClient, Postgresql, MySql

DbClient 是抽象基类，Postgresql 和 MySql 是其具体实现。

// Postgresql 是 DbClient 的 PostgreSQL 实现
export class Postgresql extends DbClient {
    constructor({host, port, database, user, password}: DbConfig);
}

// MySql 是 DbClient 的 MySQL 实现，构造方法可额外配置 connectionLimit 参数
export class MySql extends DbClient {
    constructor({host, port, database, user, password, connectionLimit}: DbConfig);
}

// DbClient 抽象基类
export abstract class DbClient {
    /**
     * 连接配置信息
     */
    host: string;
    port: number;
    database: string;
    user: string;
    password: string;

    constructor({host, port, database, user, password}: DbConfig);

    /**
     * 建立数据库连接
     */
    connect(): Promise<void>;
    
    // ... 其他抽象方法如 oneOrNone, query, tx 等
}

export type DbConfig = {
    host: string;
    port: number;
    database: string;
    user: string;
    password: string;
    connectionLimit?: number; // MySQL 特有
};