1.2.0 • Published 5d ago

pg-dynamic-query

Licence

MIT

Version

1.2.0

Deps

Size

181 kB

Vulns

Weekly

Summary Dependency Versions

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.

Examples
- PostgreSQL Example
- MySQL Example
API Documentation
Migration Notes

Examples

PostgreSQL Example

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):

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

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);
})();

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.

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

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:

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

Basic Usage

// 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

// 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`

// 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:

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

table_department:

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

TableConfig configuration:

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:

// 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

// 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).

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.

@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.

@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.

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:

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):

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.

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.

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.

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.

// 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 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.

Keywords

pg postgres postgresql mysql dynamic