Installation

npm install opticore-postgres

Summary

This package contains a PostgreSQL connection driver and a secure fluent query builder, built on top of postgres (postgres.js) (https://github.com/guyzoum77/opticore-postgresdb).

Environment variables

PostgresCore reads its connection parameters from environment variables, loaded from <project root>/config/env/.env:

| Variable | Purpose | |---|---| | DATA_BASE_USER | Database user | | DATA_BASE_PASSWORD | Database password | | DATA_BASE_HOST | Database host | | DATA_BASE_PORT | Database port | | DATA_BASE_NAME | Database name to connect to |

Optionally, set POSTGRES_SERVER=true and DATA_BASE_SERVER_URI=<connection string> to bypass the individual fields above and connect with a full connection string instead (e.g. for managed/hosted Postgres providers).

Usage

import { PostgresCore } from "opticore-postgres";

const postgresConn: PostgresCore = new PostgresCore(
    process.env.DATA_BASE_USER!,
    process.env.DATA_BASE_PASSWORD!,
    process.env.DATA_BASE_HOST!,
    Number(process.env.DATA_BASE_PORT),
    "en",
    process.env.DATA_BASE_NAME!
);

// Verifies connectivity and logs the result; the underlying pool stays open for reuse.
postgresConn.connection();

// Get the postgres.js connection pool to run tagged-template queries or pass to a QueryBuilder.
const sql = postgresConn.getConnection();
const rows = await sql`select * from users where id = ${1}`;

// Close the pool when your application shuts down.
postgresConn.closeConnection();

Details

• username / password / dbHost / dbPort: PostgreSQL connection credentials.
• localLang: default local language used for log/error messages.
• databaseOption: name of the database to connect to.
• options (optional): extra postgres.js client options (ssl, max, idle_timeout, connection, ...).

Transactions

await postgresConn.transaction(async (sql) => {
    await sql`insert into accounts (name) values ('Alice')`;
    await sql`insert into accounts (name) values ('Bob')`;
});

Query Builder

QueryBuilder is a fluent, chainable API for building parameterized SELECT/INSERT/UPDATE/DELETE statements without writing raw SQL by hand. Every bound value is sent to PostgreSQL as a query parameter (never concatenated into the SQL text), and every identifier (table/column name) is validated against a strict [a-zA-Z_][a-zA-Z0-9_]* pattern before being interpolated — this protects against SQL injection on both the value side and the identifier side.

import { QueryBuilder } from "opticore-postgres";

const sql = postgresConn.getConnection();

const activeAdults = await new QueryBuilder("users")
    .where("status", "active")
    .where("age", ">=", 18)
    .orderBy("createdAt", "DESC")
    .limit(20)
    .execute(sql);

Creating a builder

new QueryBuilder<T = Record<string, any>>(tableName: string, config?: QueryBuilderConfigInterface)

• tableName: name of the table to query (validated as a safe SQL identifier).
• T: optional type of the rows returned by the table.
• config (optional):
  • defaultLimit — limit applied to SELECT queries when .limit() was never called (default: 10).
  • maxLimit — upper bound .limit() is clamped to (default: 1000).
  • validateFields — allow-list of column names; calling a field-based method (where, whereIn, orderBy, ...) with a column outside this list throws an Error.

Building filters

| Method | Description | |---|---| | .where(column, value) | Equality condition, combined with AND | | .where(column, operator, value) | Condition with an explicit operator ("=", "!=", "<>", ">", ">=", "<", "<=", "like", "ilike", "not like") | | .orWhere(column, value \| operator, value?) | Same as .where(), combined with OR | | .whereIn(column, values[]) | IN (...) condition | | .whereNotIn(column, values[]) | NOT IN (...) condition | | .whereBetween(column, [low, high]) | BETWEEN low AND high condition | | .whereNull(column) | IS NULL condition | | .whereNotNull(column) | IS NOT NULL condition | | .like(column, pattern, caseInsensitive?) | LIKE/ILIKE pattern match | | .whereRaw(fragment, params?, connector?) | Trusted raw SQL condition; use ? in fragment for each entry in params — values are still bound as parameters |

Conditions are combined left-to-right using standard SQL operator precedence (AND binds tighter than OR). For complex boolean logic, group it explicitly with .whereRaw("(... ) AND (...)", params).

Shaping results

| Method | Description | |---|---| | .select(columns[]) | Sets the selected columns (defaults to *) | | .orderBy(column, direction?) | Adds an ORDER BY clause ("ASC" | "DESC", default "ASC") | | .limit(n) | Caps the number of returned rows (clamped to [0, maxLimit]) | | .offset(n) | Skips the first n rows (clamped to >= 0) |

Running the query

| Method | Description | |---|---| | .execute(sql) | Runs the built SELECT. Returns Promise<T[]> | | .first(sql) | Runs the built SELECT with LIMIT 1. Returns Promise<T \| null> | | .count(sql) | Counts rows matching the current WHERE conditions. Returns Promise<number> | | .exists(sql) | Returns Promise<boolean> — whether at least one row matches | | .insert(sql, data) | Inserts one row, returns Promise<T \| null> | | .insertMany(sql, rows[]) | Inserts multiple rows in one statement, returns Promise<T[]> | | .update(sql, data) | Updates rows matching the current WHERE conditions, returns Promise<T[]>. Throws if no WHERE condition was set, to avoid accidental full-table updates | | .delete(sql) | Deletes rows matching the current WHERE conditions, returns Promise<T[]>. Throws if no WHERE condition was set, to avoid accidental full-table deletes |

sql is the postgres.Sql connection/pool returned by PostgresCore.getConnection() (or the transaction-scoped sql passed to PostgresCore.transaction()).

// Insert
const created = await new QueryBuilder("users").insert(sql, { name: "Alice", email: "[email protected]" });

// Update (WHERE required)
const updated = await new QueryBuilder("users").where("id", 1).update(sql, { name: "Alice B." });

// Delete (WHERE required)
const deleted = await new QueryBuilder("users").where("id", 1).delete(sql);

Utilities

• .resetWhere() / .reset() — clear filters/params, or everything (select columns, ordering, pagination).
• .clone() — returns an independent copy of the builder.
• .toJSON() — plain object snapshot of the builder's state (table name, select columns, where fragments, params, ordering, pagination).
• .debug() — logs the current state to the console and returns this, so it can be inserted anywhere in a chain.

Contributors

This package is led by Guy-serge Kouacou.

Contributing

This project welcomes contributions from the community. Contributions are accepted using GitHub pull requests. If you're not familiar with making GitHub pull requests, please refer to the GitHub documentation "Creating a pull request."