Work with Postgres in Nodejs using @tqt/pg-extensions (Promise + Observable)

tqt/pg-extensions — pg with named parameters in queries & simple CRUD operations

To work with Postgres in Nodejs, we can use an ORM tool (like sequelize) or interact directly with databases using queries and node-postgres. I prefer the second way since it maximize the performance and I can optimize my query without doing any magic trick. However, for CRUD operations, writing repetitive queries is not a good choice. One more hassle is that the pg package doesn't support named parameters. So I write a new package which extends the original Pool from pg to solve my problems and may solve yours as well. Additional features in the extended pool:

• additional functions to support CRUD.
• support named parameters.
• support logging.

Installation

yarn add @tqt/pg-extensions

With Promise

1. Initialize pool (Promise)

2. Query (Promise)

Use this function instead of the original pool.query function.

You may query a table. It can use named parameters and resolve the problem camelCase property name in the query result.

Or use offset, limit options with the same result.

3. Count (Promise)

Count the number of records. Use the same params as pool.executeQuery. Properties queryText, table, whereClause and params are only included when using table.

4. Get by id (Promise)

Get a record in a table using id.

5. Create (Promise)

Create a new record in a specific table.

Update (Promise)

Update an existing record in a specific table.

6. Remove (Promise)

Remove a record in a specific table by id.

7. Execute transaction (Promise)

Run transaction. ExtendedPoolClient has similar extended functions like Pool. In case something wrong happens, the transaction is automatically rolled back.

With Observable

Similar api interfaces like Promise but using RxPool instead of Pool. See full documentation here: thinhtran3588/pg-extensions.

Originally published at https://thinhtran.pro on June 9, 2021.