CKN Technology Data · Postgresql Class

CKN Technology Data
Postgresql

This page documents the Postgresql class – a PostgreSQL adapter built on top of the pg library that extends a generic Database base class, with high-level utilities for transactions, queries, inserts, updates, and schema inspection.

Import

The Postgresql class is provided by @ckn-technology/data and integrates with:

  • Database base class (connection configuration, logging).
  • Row objects for structured row handling.
  • PostgreSQL driver (pg package).
  • Logging utilities from @ckn-technology/core.
import { Postgresql } from "@ckn-technology/data";

const db = new Postgresql({
    host: "localhost",
    user: "root",
    password: "password",
    database: "test"
});

1. Overview

Postgresql is a database adapter built on top of the pg library that provides:

  • Running SQL queries and returning rows.
  • Transaction management with BEGIN, COMMIT, ROLLBACK.
  • Insert operations for one or more rows.
  • Update operations with dynamic SQL.
  • Checking table existence.
  • Reading table schema from information_schema.
// Basic usage
import { Postgresql } from "@ckn-technology/data";

const pgdb = new Postgresql();

pgdb.host = "localhost";
pgdb.database = "mydb";

const rows = await pgdb.query("SELECT id, name FROM users");
console.log(rows[0]);

2. Properties

The Postgresql class defines the following key properties:

Property Type Description
isTransaction boolean Indicates whether the current client is in a transaction block.
client pg.Client The underlying PostgreSQL client connection instance.

3. Constructor

3.1 constructor()

Description
Initializes a new PostgreSQL adapter instance. The adapter uses connection configuration inherited from the Database base class, such as host, user, password, and database.

Signature

constructor()

Example

const pgdb = new Postgresql();
pgdb.host = "localhost";
pgdb.database = "mydb";

4. Transaction Control

4.1 begin()

Description
Starts a database transaction.

  • Creates a new pg.Client if necessary.
  • Executes BEGIN on the server.
  • Sets isTransaction = true.

Signature

begin(): Promise<any>

Example

await pgdb.begin();
// perform multiple queries here...
await pgdb.commit();

4.2 rollback()

Description
Rolls back the current transaction, undoing all changes since the last BEGIN.

Signature

rollback(): Promise<any>

Example

await pgdb.begin();
try {
    await pgdb.query("UPDATE accounts SET amount = amount - 100");
    throw new Error("Unexpected failure");
} catch (e) {
    await pgdb.rollback();
}

4.3 commit()

Description
Commits the current transaction, persisting all changes.

Signature

commit(): Promise<any>

Example

await pgdb.begin();
await pgdb.query("UPDATE accounts SET active = TRUE");
await pgdb.commit();

4.4 end()

Description
Ends the active PostgreSQL client session and resets the transaction flag.

Signature

end(): Promise<void>

Example

await pgdb.begin();
await pgdb.commit();
await pgdb.end();

5. Query Execution

5.1 query(query: string)

Description
Executes a SQL query and returns PostgreSQL rows.

  • Creates a new pg.Client if not inside a transaction.
  • Logs the query using the internal logger.
  • Executes the SQL statement.
  • Returns res.rows as an array of objects.

Signature

query(query: string): Promise<Row[]>

Example

const rows = await pgdb.query("SELECT id, name FROM users");
console.log(rows[0]);

6. Insert & Update Operations

6.1 insert(tableName, objs, returnValue?)

Description
Inserts one or more rows into a table using dynamically generated SQL.

Handles:

  • String escaping (' → '').
  • null values.
  • Multiple rows in one statement.
  • Optional RETURNING column clause.

Signature

insert(
  tableName: string,
  objs: Row | Row[],
  returnValue?: string
): Promise<any>

Example

await pgdb.insert("users", new Row({
    id: 1,
    name: "Alice"
}), "id");

6.2 update(tableName, objs, condition)

Description
Updates one or more rows with dynamic SQL.

  • Escapes string values.
  • Converts empty or missing values to NULL where appropriate.
  • Constructs UPDATE statements such as:
UPDATE "users" SET "name" = 'Alice' WHERE id = 1

Signature

update(
  tableName: string,
  objs: Row | Row[],
  condition: string
): Promise<any>

Example

await pgdb.update(
    "users",
    new Row({ name: "Alice Updated" }),
    "id = 1"
);

7. Schema & Table Utilities

7.1 getFields(table)

Description
Retrieves column metadata from PostgreSQL information_schema.columns.

Signature

getFields(table: string): Promise<Row[]>

Example

const columns = await pgdb.getFields("users");
console.log(columns);

7.2 checkTable(table)

Description
Checks whether a table exists in the current schema.

  • Returns true if the table exists.
  • Returns false otherwise.

Signature

checkTable(table: string): Promise<boolean>

Example

if (await pgdb.checkTable("orders")) {
    console.log("Table exists");
}

8. Behavior Notes

8.1 Connection Handling

Connection behavior depends on whether a transaction is active:

Scenario Behavior
Inside transaction Connection stays open for all queries until commit() or rollback().
Outside transaction New pg.Client is created per query and closed afterward.

8.2 Value Conversion Rules

When building SQL expressions, the following conversions are applied:

JavaScript Type PostgreSQL Value
string Escaped 'value' (single quotes doubled).
null / undefined / "" NULL
Date 'date.toString()' (raw string)
boolean 'true' or 'false'
array Ignored in auto-generated SQL expressions.

8.3 Logging

Every SQL query executed by Postgresql is logged using the internal logger, typically with: 

this.log.info("QUERY", sql)

This provides consistent, timestamped logging for observability and debugging across all services using the adapter.

9. Summary

The Postgresql class provides a complete, high-level API for PostgreSQL database interactions, built on top of the pg driver and a generic Database base class.

  • Supports transaction lifecycle methods: begin, rollback, commit, and end.
  • Exposes a query() method that returns PostgreSQL rows as JavaScript objects.
  • Provides high-level helpers for data modification: insert() and update().
  • Includes schema utilities: getFields() and checkTable().
  • Defines clear connection handling rules and value conversion semantics for common JavaScript types.
  • Logs every executed SQL statement for observability.

With these features, Postgresql serves as a consistent, opinionated PostgreSQL adapter within @ckn-technology/data, simplifying database interactions for application and service code.