# pg-dynamic-query

> 基于postgresql,mysql数据库的增删改查api,还有类似jpa动态查询,导航查询功能

Latest version **1.2.0** (published 2026-09-09) · MIT license · 0 weekly downloads

## Install

```sh
npm install pg-dynamic-query
pnpm add pg-dynamic-query
yarn add pg-dynamic-query
bun add pg-dynamic-query
```

## Health

**Score 70/100 (B)** — status: active.

Positive: has types; esm support; no vulnerabilities; recently updated; high maintenance score; high quality score.

Warnings: low downloads.

## Facts

| | |
|---|---|
| Version | 1.2.0 |
| Published | 2026-09-09 |
| First published | 2022-03-23 |
| Weekly downloads | 0 |
| License | MIT |
| TypeScript types | bundled |
| Module format | ESM + CommonJS |
| Dependencies | 4 |
| Unpacked size | 181.2 KB |
| Known vulnerabilities | 0 |
| Install scripts | no |
| Maintainers | p-nickzhang-q |
| Keywords | pg, postgres, postgresql, mysql, dynamic |

## Links

- npm: https://www.npmjs.com/package/pg-dynamic-query
- Repository: https://gitee.com/zhanghao161512/pg-dynamic-query
- npm.io page: https://npm.io/package/pg-dynamic-query

## Dependencies (4)

- [mysql](https://npm.io/package/mysql.md) ^2.18.1
- [pg-promise](https://npm.io/package/pg-promise.md) ^11.14.0
- [colors-console](https://npm.io/package/colors-console.md) ^1.0.3
- [reflect-metadata](https://npm.io/package/reflect-metadata.md) ^0.1.13

## Recent versions

- 1.2.0 (latest) — 2026-09-09
- 1.1.1 — 2026-05-08
- 1.0.3 — 2025-11-18
- 1.0.2 — 2025-11-18
- 1.0.1 — 2025-10-10
- 1.0.0 — 2025-10-09
- 0.4.8 — 2024-05-11
- 0.4.7 — 2024-05-08
- 0.4.6 — 2024-05-08
- 0.4.5 — 2024-04-18
- 0.4.4 — 2024-04-17
- 0.4.3 — 2024-01-03
- 0.4.2 — 2024-01-03
- 0.4.1 — 2024-01-03
- 0.3.67 — 2023-09-07
- … 75 more at https://npm.io/package/pg-dynamic-query/versions

## README

# pg-dynamic-query

A dynamic query library for PostgreSQL and MySQL, providing a concise API for database operations. It supports complex queries, pagination, transactions, and navigational queries.

## Table of Contents

- [Examples](#examples)
    - [PostgreSQL Example](#postgresql-example)
    - [MySQL Example](#mysql-example)
- [API Documentation](#api-documentation)
    - [DynamicQuery](#dynamicquery)
        - [Parameter Types](#parameter-types)
        - [How to Write a Query](#how-to-write-a-query)
            - [Basic Usage](#basic-usage)
            - [Operators](#operators)
            - [Alternative Syntax for `or`, `and`, and `in`](#alternative-syntax-for-or-and-and-in)
        - [Navigational Queries](#navigational-queries)
        - [Transactions](#transactions)
    - [EntityDynamicQuery](#entitydynamicquery)
        - [Decorators](#decorators)
    - [Enabling SQL Logging](#enabling-sql-logging)
    - [PageResponse](#pageresponse)
    - [TableConfig](#tableconfig)
        - [ParentConfig](#parentconfig)
    - [DbClient, Postgresql, MySql](#dbclient-postgresql-mysql)
- [Migration Notes](#migration-notes)

---

## Examples

### PostgreSQL Example

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

(async () => {
    // Configure PostgreSQL connection
    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, {enableSqlLog: true});

    // Save data
    const r = await dynamicQuery.save({name: 'zh', age: 13});
    console.log('Saved user:', r);

    // Delete data
    await dynamicQuery.remove([r.id]);

    // Paginated query
    await dynamicQuery.find({}, pageResponse);
    console.log("PageResponse:");
    console.log(pageResponse);
})();
```

**Sample Console Output (with `enableSqlLog: true`):**

```shell
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) AS count 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 Example

```javascript
const {MySql, DynamicQuery, PageResponse, 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, {enableSqlLog: true});

    // Save data (with idAutoCreate enabled, the id field is managed by the database)
    const r = await dynamicQuery.save({name: 'zh1', age: 13});
    console.log('Saved user:', r);

    // Paginated query
    await dynamicQuery.find({}, pageResponse);
    console.log("PageResponse:");
    console.log(pageResponse);
})();
```

---

## API Documentation

### DynamicQuery

`DynamicQuery` is the core query class, providing a rich set of methods for database operations. It is generic: `DynamicQuery<T = any>` — row-mapping hooks (`toRow`/`toRows`) convert raw rows into `T`, so subclasses (like `EntityDynamicQuery`) can return entity instances. With the default `T = any`, all methods behave like plain dynamic queries.

```typescript
export class DynamicQuery<T = any> {
    /**
     * Constructor requires a TableConfig and a DbClient instance.
     * Optional: {enableSqlLog?: boolean}
     */
    constructor(tableConfig: TableConfig, client: DbClient, options?: {enableSqlLog?: boolean});

    // --- Query Methods ---

    /**
     * Paginated query for a single table. Results are populated in PageResponse.content.
     */
    find(query: Query, page: PageResponse<T>, transaction?: Transaction): Promise<void>;

    /**
     * Paginated navigational query. Requires `parents` to be configured in TableConfig.
     */
    navigationFind(query: Query, page: PageResponse<T>, transaction?: Transaction): Promise<void>;

    /**
     * Paginated query using a custom Sql object.
     */
    findBySql(sql: Sql, page: PageResponse<T>, transaction?: Transaction): Promise<void>;

    /**
     * Query all matching records using a raw SQL string.
     */
    findAllBySql(querySql: string, transaction?: Transaction): Promise<any[]>;

    /**
     * Find all records matching the Query and OrderBy.
     */
    findAll(query: Query, transaction?: Transaction, page?: OrderBy): Promise<T[]>;

    /**
     * Navigational query to find all matching records.
     */
    navigationFindAll(query: Query, transaction?: Transaction, page?: OrderBy): Promise<T[]>;

    /**
     * Navigational query to find the first matching record.
     */
    navigationFindOne(query: Query, transaction?: Transaction): Promise<T | null>;

    /**
     * Find the first record matching the Query.
     */
    findOne(query: Query, transaction?: Transaction): Promise<T | null>;

    /**
     * Find the first record using a raw SQL string.
     */
    findOneBySql(sql: string, transaction?: Transaction): Promise<any>;

    /**
     * Count the number of records matching the Query.
     */
    count(query: Query, transaction?: Transaction): Promise<number>;

    /**
     * Count records using a raw FROM/WHERE SQL string.
     */
    countBySql(sql: string, transaction?: Transaction): Promise<number>;

    // --- Modification Methods ---

    /**
     * Create a new record. `hasReturn` controls whether the created record is returned (default: true).
     */
    create(data: object, hasReturn?: boolean, transaction?: Transaction): Promise<T | null>;

    /**
     * Batch-create records. Multi-record input generates ONE multi-row INSERT statement
     * (1 round trip, statement-level atomic). With createReturn enabled, each entity's
     * primary key is written back — via RETURNING on PostgreSQL; on MySQL it is derived
     * from the first auto-increment id (assumes auto_increment_increment = 1).
     * A single record falls back to create().
     */
    createAll(entities: object[], transaction?: Transaction): Promise<void>;

    /**
     * Save a record (updates if it exists, inserts otherwise). Works only with single primary keys.
     */
    save(data: object, transaction?: Transaction): Promise<T | null>;

    /**
     * Batch save multiple records (executed row by row).
     */
    saveAll(entities: object[], transaction?: Transaction): Promise<(T | null)[]>;

    /**
     * Delete and create new records.
     */
    removeAndCreate(removeQuery: Query, entities: object[], transaction?: Transaction): Promise<void>;

    /**
     * Update a record. `isAllUpdate` (default: true) controls whether all columns are updated.
     */
    update(data: object, isAllUpdate?: boolean, transaction?: Transaction): Promise<T | null>;

    /**
     * Update records by their IDs and return the updated result set.
     */
    updateByIds(data: object, ids: any[], isAllUpdate?: boolean, transaction?: Transaction): Promise<T[]>;

    /**
     * Update records matching a Query. Does not return the result set.
     */
    updateByQuery(data: object, query: Query, isAllUpdate?: boolean, transaction?: Transaction): Promise<void>;

    /**
     * Update records matching a Query and return the result set.
     */
    updateByQueryWithResult(data: object, query: Query, isAllUpdate?: boolean, transaction?: Transaction): Promise<T[]>;

    /**
     * Delete records by their IDs.
     */
    remove(ids: any[], transaction?: Transaction): Promise<void>;

    /**
     * Delete records matching a Query.
     */
    removeByQuery(query: Query, transaction?: Transaction): Promise<void>;

    // --- Transaction Methods ---

    /**
     * Execute a function within a database transaction. All operations inside the func will be in the same transaction.
     */
    tx(func: (transaction: Transaction) => Promise<any>): Promise<any>;
}
```

#### Parameter Types

```typescript
export type Sql = {
    selectSql: string;
    formWhereSql: string;
    orderBySql: string | null;
    countInSql: string | null;
};

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';

/**
 * An instance of this class is passed to the callback in DynamicQuery.tx.
 */
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>;
}
```

#### Input Validation (throws `ValidationError`)

To prevent SQL injection through paginated queries, the following inputs are validated:

- **ORDER BY whitelist**: `orderBy` must be a column in the table's `columnSet`, or `parentObject.column` for a configured parent table (e.g. `department.id`). Anything else throws `ValidationError`.
- **direction**: must be `'asc'` or `'desc'`.
- **page / size**: `page` must be a non-negative integer, `size` a positive integer. `PageResponse.of(req)` reads these from HTTP input, so validation happens before any SQL is built.
- **`$in` / `$nin` / array shorthand**: value must be a non-empty array; empty arrays throw (an empty `$in` would otherwise skip the condition and match the whole table).

#### How to Write a Query?

> **Note**: The keys in the Query object must match the database field names.

Assume a `User` table with the following data:

```json
[
  { "id": 1, "name": "Zhang San", "age": 10 },
  { "id": 2, "name": "Li Si", "age": 12 }
]
```

##### Basic Usage

```js
// Represents records where name = "Zhang San"
let query = {name: "Zhang San"};

// Represents records where name like "%Zhang%"
query = {name: "%Zhang%"};

// Represents records where name is null
query = {name: "$null"};

// Represents records where name is not null
query = {name: "$nn"};
```

##### Operators

```js
// Represents records where name in ["Zhang San"]
query = {name: {$in: ["Zhang San"]}};

// Represents records where name not in ["Zhang San"]
query = {name: {$nin: ["Zhang San"]}};

// Represents records where name = "Zhang San"
query = {name: {$eq: "Zhang San"}};

// Represents records where name != "Zhang San"
query = {name: {$ne: "Zhang San"}};

// Represents records where age >= 18
query = {age: {$gte: 18}};

// Represents records where age > 18
query = {age: {$gt: 18}};

// Represents records where age <= 18
query = {age: {$lte: 18}};

// Represents records where age < 18
query = {age: {$lt: 18}};

// Represents records where age between 0 and 10
query = {age: {$between: [0, 10]}};

// Represents records where name = "Zhang San" or age = 18
query = {
  $or: {
    name: "Zhang San",
    age: 18
  }
};

// Represents records where name = "Zhang San" and age = 18
query = {
  $and: {
    name: "Zhang San",
    age: 18
  }
};
```

##### Alternative Syntax for `or`, `and`, and `in`

```js
// Represents (name = "Zhang San") or (age = 18)
query = {
  $or: [
    { name: "Zhang San" },
    { age: 18 }
  ]
};

// Represents (name = "Zhang San") and (age = 18)
query = {
  $and: [
    { name: "Zhang San" },
    { age: 18 }
  ]
};

// Represents name in ["Zhang San"]
query = { name: ["Zhang San"] };
```

> **Note**: `$or`/`$and` groups are fully parenthesized when combined with other conditions, so precedence always matches the Query object's intent.

#### Navigational Queries

Methods starting with `navigation` are for navigational queries. You must configure `ParentConfig` in `TableConfig` to use them.

**Assume the following table data:**

`table_user`:
```json
[
  { "id": 1, "name": "Zhang San", "age": 10, "department_id": 1 },
  { "id": 2, "name": "Li Si", "age": 12, "department_id": 2 }
]
```

`table_department`:
```json
[
  { "id": 1, "name": "Department 1" },
  { "id": 2, "name": "Department 2" }
]
```

**TableConfig configuration:**

```js
const UserConfig = new TableConfig('table_user', [
  'id', 'name', 'age', 'department_id'
]);
UserConfig.parents.push({
  parentId: 'department_id',     // Foreign key field in the user table
  parentIdName: 'id',           // Primary key field in the department table
  parentObject: 'department',    // Field name for the department data in the result
  parentTable: 'table_department' // Parent table name
});
```

**Navigational queries can filter by parent table fields:**

```js
// Represents records where department.name = "Department 1"
query = {
  department: {
    name: "Department 1",
  }
};
```

**Sorting by parent columns** is also supported via the whitelist: `orderBy: 'department.id'`.

#### Transactions

```javascript
// Pass a function to the DynamicQuery.tx method.
// The function receives a `transaction` object, which should be passed to other methods to be included in the same transaction.
await dynamicQuery.tx(async transaction => {
    const r = await dynamicQuery.save({name: 'zh', age: 13}, transaction);
    // If an error is thrown here, the transaction will be rolled back.
    // throw new Error("test"); 
    
    await dynamicQuery.remove([r.id], transaction);
    await dynamicQuery.find({}, pageResponse, transaction);
    console.log("pageResponse:");
    console.log(pageResponse);
});
```

### EntityDynamicQuery

`EntityDynamicQuery` is an enhanced version of `DynamicQuery` that leverages TypeScript decorators and metadata to automatically configure `TableConfig` and map database results directly to entity class instances, eliminating manual conversion.

> **Prerequisite**: enable `experimentalDecorators` in tsconfig.json and `import 'reflect-metadata'` once at your application entry point (the library does this internally, but your decorators also need the same setup).

```typescript
export class EntityDynamicQuery<T extends object> extends DynamicQuery<T> {
    constructor(client: DbClient, entityClass: new () => T);

    // All method signatures are inherited from DynamicQuery<T> —
    // results are automatically mapped to T or T[] via toRow/toRows.
}
```

#### Decorators

The magic of `EntityDynamicQuery` comes from its decorators.

##### `@Entity(tableName: string)`

Marks a class as an entity and specifies its corresponding database table name.

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

##### `@Parent(config: ParentParams)`

Marks a navigational property (i.e., a parent object) within an entity class.

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

    @Parent({
        parentClass: Department,       // Parent entity class
        parentId: 'department_id'      // Foreign key field in this entity
    })
    department: Department = new Department();
}
```

Alternatively, `@TableField({parentConfig: {...}})` on a regular field achieves the same thing while also excluding that foreign-key field from `columnSet`.

##### `@TableField(config?: { ignore?: boolean, parentConfig?: ParentParams })`

Marks a field. `ignore: true` will exclude the field from the generated `columnSet`. `parentConfig` registers a navigational parent (same as `@Parent`) and also excludes the field.

```typescript
class User {
    name: string = '';
    
    @TableField({ ignore: true })
    internalStatus: string = ''; // This field will not be included in columnSet

    @TableField({ parentConfig: { parentId: 'department_id', parentTable: 'table_department', parentIdName: 'id' } })
    department: Department = new Department();
}
```

**Complete Example:**

```typescript
import {Entity, EntityDynamicQuery, Parent, Postgresql} from "pg-dynamic-query";
import 'reflect-metadata';

@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 = new Department();
}

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

// The returned user object is an instance of the User class
const user = await userQuery.save({ name: 'New User', age: 25, department_id: 1 });
console.log(user instanceof User); // true

// The returned department object is an instance of the Department class
const foundUser = await userQuery.navigationFindOne({ name: 'New User' });
console.log(foundUser.department instanceof Department); // true
```

### Enabling SQL Logging

SQL logging is configured per instance through the constructor options (the old global `switchSqlLog()` is deprecated and only prints a warning):

```javascript
const dynamicQuery = new DynamicQuery(userConfig, postgresql, {enableSqlLog: true});
```

Logged SQL is printed on a single line; the method name is printed in red as a prefix (e.g. `findBySql countSql`).

### PageResponse

A class to encapsulate the results of a paginated query.

```typescript
export class PageResponse<T> {
    /**
     * Total number of elements.
     */
    totalElements: number;
    /**
     * Current page number, starting from 0.
     */
    page: number;
    /**
     * Number of elements per page.
     */
    size: number;
    /**
     * Array of data for the current page.
     */
    content: T[];
    /**
     * Total number of pages.
     */
    totalPages: number;
    /**
     * Sort field (must be in the ORDER BY whitelist).
     */
    orderBy: string;
    /**
     * Sort direction: 'asc' | 'desc'
     */
    direction: Direction;

    constructor();

    /**
     * Creates a PageResponse instance from an Express req.query object.
     */
    static of<T>(req: any): PageResponse<T>;

    /**
     * Gets pagination parameters from req.query.
     */
    static getPageAndSize(req: any): PageRequest;
}
```

### TableConfig

Used to configure the mapping between a table and an entity.

```ts
export class TableConfig {
    /**
     * Database table name.
     */
    table: string;
    /**
     * Array of all field names in the table.
     */
    columnSet: string[];
    /**
     * Primary key field name, defaults to 'id'.
     */
    idName: string;
    /**
     * Whether the primary key is auto-incrementing, defaults to true.
     */
    idAutoCreate: boolean;
    /**
     * Array of JSON-formatted field names. Values are serialized on write and
     * parsed on read (for both single rows and lists); null stays SQL NULL.
     */
    jsonColumn: string[];
    /**
     * Field name for creation time. If set, it's automatically assigned on creation.
     */
    createTime: string;
    /**
     * Field name for update time. If set, it's automatically assigned on update.
     */
    updateTime: string;
    /**
     * Whether to return the object after creation, defaults to true.
     */
    createReturn: boolean;
    /**
     * Configuration for parent tables (for navigational queries).
     */
    parents: ParentConfig[];

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

#### ParentConfig

Used to configure parent table information for navigational queries.

```typescript
export interface ParentConfig<T = any> {
    /**
     * Foreign key field name in the current table.
     */
    parentId: string;
    /**
     * Field name where the parent table data will be placed in the result.
     */
    parentObject: string;
    /**
     * Parent table name.
     */
    parentTable: string;
    /**
     * Parent table primary key field name.
     */
    parentIdName: string;
    /**
     * Parent entity class (for EntityDynamicQuery).
     */
    parentClass?: new () => T;
}
```

### DbClient, Postgresql, MySql

`DbClient` is an abstract base class, with `Postgresql` and `MySql` as its concrete implementations.

```typescript
// Postgresql is the PostgreSQL implementation of DbClient
export class Postgresql extends DbClient {
    constructor({host, port, database, user, password}: DbConfig);
}

// MySql is the MySQL implementation of DbClient, with an optional connectionLimit parameter
export class MySql extends DbClient {
    constructor({host, port, database, user, password, connectionLimit}: DbConfig);
}

// Abstract base class DbClient
export abstract class DbClient {
    // Connection configuration (protected)
    protected host: string;
    protected port: number;
    protected database: string;
    protected user: string;
    protected password: string;

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

    /**
     * Establish a database connection.
     */
    abstract connect(): Promise<void>;

    // ... other abstract methods like oneOrNone, query, tx, etc.
}

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

#### Custom DbClient Implementations

If you implement your own `DbClient` (for other databases or drivers), these dialect primitives make up the interface:

| Method | Purpose |
| --- | --- |
| `escapeValue(val)` | Escape a value into a SQL literal (quoting, backslashes, control chars). Public since 1.2.0. |
| `columnProcess(s)` | Quote/transform a column name for this dialect. |
| `insertSql(data, columns, tableConfig)` | Build an INSERT statement. `columns` is a plain `string[]` (was a pg-promise ColumnSet before 1.2.0). |
| `updateSql(data, columns, tableConfig)` | Build an UPDATE statement (no WHERE). Same `columns` change. |
| `insertAllSql(datas, columns, tableConfig)` | Build a multi-row INSERT (new in 1.2.0). |
| `extractInsertIds(result, tableConfig)` | Extract auto-increment ids from an INSERT result, for `createAll` id write-back (new in 1.2.0). |

## Migration Notes

Behavioral and breaking changes in 1.2.0 — see [CHANGELOG.md](./CHANGELOG.md) for the full list:

- `$or`/`$and` groups are now fully parenthesized (fixes precedence when combined with other conditions).
- ORDER BY whitelist, `direction` enum, `page`/`size` validation, and empty-array `$in`/`$nin` now throw `ValidationError` instead of silently producing dangerous SQL.
- `switchSqlLog()` is deprecated; use the constructor option `{enableSqlLog: true}`.
- `DbClient.insertSql`/`updateSql` take `string[]` columns instead of a pg-promise ColumnSet; custom subclasses must adapt.
- `createAll` is now a single multi-row INSERT (statement-atomic); MySQL id write-back assumes `auto_increment_increment = 1`.

---
_Source: https://npm.io/package/pg-dynamic-query · Machine-readable twin of the npm.io package page. Health data is recomputed on every publish._