@ololoepepe/postgres-client

A knex client for Postgres that keeps SQL in .sql files instead of in the code.

It renders those files as templates, binds their parameters, and — when asked — turns them into Postgres stored functions and calls those instead, without the calling code changing a line.

Installation

npm install @ololoepepe/postgres-client

Requires Node.js >= 24. Written in TypeScript — the type declarations ship with the package, so there is no @types/… to install.

Usage

import {createClient} from '@ololoepepe/postgres-client';

const pg = createClient({
  connection: process.env.DATABASE_URL
}, '/path/to/sql');

const users = await pg.rawQuery<{id: number; name: string}>('users/by-role', {role: 'admin'});

With sql/users/by-role.sql containing:

select id, name from users where role = :role;

The returned object is a knex instance, so everything knex offers is still there: pg('users').where(…), pg.raw(…), migrations, the connection pool.

SQL templates

rawSqlDirectory — the second argument of createClient — is the directory the templates are read from. A query name is a path relative to it, without the .sql extension.

The files are rendered by Eta with /*{ and }*/ as the tags, so a template stays valid SQL and readable in an editor:

select id, name
from users
/*{ if (it.role) { }*/
where role = :role
/*{ } }*/

Template variables arrive as it and are passed in the array form of rawQuery (see below). They are interpolated into the SQL text, so never build them from user input — use bindings for values.

rawQuery(query, bindings?)

Two forms, told apart by the type of the first argument.

A string runs the query and resolves with its rows:

const rows = await pg.rawQuery<User>('users/by-role', {role: 'admin'});

An array returns the knex builder instead, unexecuted — for composing, inspecting, or running inside someone else's transaction:

const builder = pg.rawQuery(['users/by-role'], {role: 'admin'});

console.log(builder.toQuery());

The array is [queryName, templateVariables?, options?]:

| Element | Meaning | | --- | --- | | queryName | Template path, or @name for a template registered at runtime. | | templateVariables | The it object the template is rendered with. | | options.preferQuery | Render the template even if a stored function of that name exists. |

bindings is passed straight to knex: an object binds :name placeholders, an array binds ? ones. Omit it for a query that takes no parameters.

Rows come back as Record<string, unknown>[] unless you name the row type — rawQuery<User>(…). The column values stay unknown on purpose: nothing here can check what the query actually selects. The type parameter is unconstrained, so a plain interface works as the row type.

Stored functions

createFunctions(list) drops and recreates Postgres functions whose bodies are the very same templates. After that, rawQuery calls the function rather than sending the SQL text:

await pg.createFunctions([{
  name: 'users-by-role',
  params: [{name: 'role', type: 'text'}],
  returns: [{name: 'id', type: 'numeric'}, {name: 'name', type: 'text'}],
  returnsTable: true
}]);

// Now sends `select * from users_by_role(:role)` instead of the template text
const users = await pg.rawQuery<User>('users-by-role', {role: 'admin'});

The :name placeholders of the template become the function's arguments; the Postgres function name is the template name with dashes replaced by underscores, unless functionName says otherwise. Only dashes are replaced, so a template in a subdirectory needs functionName spelled out — users/by-role would otherwise produce the invalid identifier users/by_role.

| Field | Default | Meaning | | --- | --- | --- | | name | — | Template name. Also the key rawQuery looks the function up by. | | params | — | Function arguments, in declaration order. | | returns | — | Result columns. | | returnsTable | false | Call it as select * from f(…) rather than select f(…). | | functionName | name with - → _ | Postgres function name. | | isExternal | false | The template came from templateList, not from a file. | | isDeleted | false | Drop the function and do not create it again. | | parallelism | FunctionParallelism.Safe | Postgres parallel attribute. | | volatility | FunctionVolatility.Stable | Postgres volatility category. |

parallelism and volatility are enums, so they are written by name rather than as strings — FunctionParallelism.Restricted, FunctionVolatility.Immutable. Both are exported from the package root.

createFunctions(list, {prefix}) prepends prefix to every function name, both when dropping and when creating.

Everything in name, functionName, prefix and the parameter types goes into the DDL as written. These are identifiers you write, never values a user sends — there is nothing to escape them with.

The whole list is dropped and recreated in one transaction, so a failure part of the way through leaves the old functions in place.

loadTemplate(name, text)

Registers a template that does not live in the SQL directory — one that came from a database, an API, or a string in the code. Refer to it with a leading @:

pg.loadTemplate('report', 'select count(*) from events where day = :day;');

const rows = await pg.rawQuery('@report', {day: '2026-08-28'});

The third argument of createClient does the same at construction time:

const pg = createClient(config, sqlDirectory, [{name: 'report', text: reportSql}]);

A template registered this way is still a file-less one, so a stored function built from it needs isExternal: true.

Transactions

pg.transaction(callback) is knex's own, except that the transaction handed to the callback has rawQuery on it too:

await pg.transaction(async tx => {
  await tx.rawQuery('users/insert', {name: 'Andrey'});
  await tx.rawQuery('audit/insert', {action: 'user-created'});
});

Without a SQL directory

rawSqlDirectory is optional, and everything above depends on it. Leave it out and rawQuery and loadTemplate throw when called, saying so — the client is then a plain knex instance and nothing more.

Errors

• rawQuery and loadTemplate throw Error when no SQL directory was given.
• A template that cannot be found, parsed or rendered throws from Eta — EtaFileResolutionError, EtaNameResolutionError, EtaParseError. Eta refuses to resolve a name outside the templates directory, so a name coming from user input cannot be used to read arbitrary files.
• Everything else — connection failures, SQL errors, constraint violations — comes from knex and pg unchanged.

Types

| Type | What it is | | --- | --- | | PostgresClient | What createClient returns: a knex instance plus the three methods. | | PostgresTransaction | A knex transaction with rawQuery. | | StoredFunctionDefinition | An entry of the createFunctions list. | | SqlTemplate | An entry of the templateList argument. | | RawQueryArguments | The array form of the first rawQuery argument. | | QueryBindings | Bindings: ? array or :name object. | | FunctionParallelism | Enum: Restricted, Safe, Unsafe. | | FunctionVolatility | Enum: Immutable, Stable, Volatile. |

Development

| Command | What it does | | --- | --- | | npm run lint | ESLint over the whole repository. | | npm run typecheck | tsc over src, test and scripts, no emit. | | npm test | The node:test suite, against the build. | | npm run build | Compiles src into dist/node/ — the ESM and .d.ts that get published. |

typecheck and test build first, so they always see the current sources.

Internal imports go through the #src/*.ts subpath map rather than relative paths. dist/node/ gets its own package.json remapping #src/*.ts to the compiled files, which is what makes those imports resolve for consumers.

The tests import the package by its own name rather than reaching into src, so they exercise exactly what a consumer gets — and so Node never has to load a source file containing an enum, which its type stripping cannot do.

The test suite runs without a database: it checks the SQL that would be sent, through knex's toQuery(), and stubs transaction where createFunctions needs one.

License

UNLICENSED — private package.