PostgreSQL Client for Constructive.io (pgsql-client)

Common errors

• Error: RLS policy denied access for table "X" (or similar 'permission denied for relation')

  cause The Row-Level Security (RLS) policies on the PostgreSQL server prevented the current user/role or session context from accessing the requested rows or table.
  fix Review your RLS policies on the PostgreSQL server. Ensure `client.setContext` is correctly populating the session variables that your RLS policies rely on (e.g., `current_setting('jwt.claims.user_id', true)`).

• Error: Cannot connect to database: password authentication failed for user "X"

  cause The provided username, password, host, or port in the connection string is incorrect, or the database server is not reachable.
  fix Verify your `DATABASE_URL` environment variable or connection string. Check PostgreSQL server logs for connection attempts and ensure the database server is running and accessible from your application's host.

• TypeError: Cannot read properties of undefined (reading 'query')

  cause The `client` object was not successfully initialized or connected before attempting to call `client.query()` or other methods. This can happen if `client.connect()` throws an error or is not awaited.
  fix Ensure proper error handling around `new Client()` and `await client.connect()`. Always check if the `client` object is defined and connected before performing database operations.

Warnings

• gotcha This `pgsql-client` refers to a utility within the `constructive-io` ecosystem (github.com/constructive-io/constructive) and is NOT the npm package `postgresql-client` which was renamed to `postgrejs`. Confusing these two can lead to incorrect installations and API mismatches.
  Fix: Ensure you are installing from the correct scope or repository if consuming directly, or use it as intended within the `constructive-io` framework.
• breaking Major version upgrades of PostgreSQL servers (e.g., from PG16 to PG17 or later) can introduce protocol changes, deprecated features, or data type incompatibilities. While `pgsql-client` aims for compatibility, always review its release notes against your PostgreSQL server version upgrades to avoid unexpected behavior.
  Fix: Consult the `pgsql-client` and PostgreSQL server release notes before performing major database upgrades. Update `pgsql-client` if necessary.
• gotcha Incorrect or missing Row-Level Security (RLS) context can lead to authorization failures, either granting unintended access or blocking legitimate users from data. The `setContext` method is critical for enforcing RLS policies correctly.
  Fix: Thoroughly test RLS policies and ensure `client.setContext` is called with appropriate and verified claims/variables for every session or transaction where RLS applies. Follow PostgreSQL RLS best practices.
• gotcha Connection pooling is vital for performance in production environments. While `pgsql-client` likely handles pooling internally or expects it to be configured, mismanaging connections (e.g., creating a new client for every query) can lead to resource exhaustion and degraded performance.
  Fix: Refer to `pgsql-client` documentation for recommended connection pooling strategies. If not explicitly managed by the library, consider using a dedicated connection pooler alongside `pgsql-client` or ensuring clients are reused.

Install

• npm install pgsql-client npm
• yarn add pgsql-client yarn
• pnpm add pgsql-client pnpm

Imports

• Client
  wrong:

  const Client = require('pgsql-client')

  correct:

  import { Client } from 'pgsql-client'

  Primary class for establishing and managing PostgreSQL connections. Assumed to be ESM-first given modern TypeScript libraries.
• ConnectionOptions

  import { ConnectionOptions } from 'pgsql-client'

  Interface for configuring client connections, though a connection string is often sufficient. Useful for advanced configurations.
• setContext

  await client.setContext({...})

  Method on the Client instance for setting session-level Row-Level Security (RLS) context variables (e.g., user ID, tenant ID). This is a core feature for RLS-driven applications.

Quickstart

Demonstrates connecting to a PostgreSQL database, setting Row-Level Security (RLS) context, and executing a query that is automatically filtered by the server-side RLS policies based on the session context.

import { Client } from 'pgsql-client';

async function runPgsqlClientExample() {
  // Ensure DATABASE_URL is set in your environment or provide a default
  const connectionString = process.env.DATABASE_URL ?? 'postgresql://user:password@localhost:5432/mydb';

  let client: Client | undefined;
  try {
    // Initialize and connect the PostgreSQL client
    client = new Client(connectionString);
    await client.connect();
    console.log('Successfully connected to PostgreSQL database.');

    // Set RLS context for the current session, crucial for secure data access
    // In a real application, this context comes from an authenticated user's JWT claims or session data.
    const currentUser = {
      id: 'usr_abc123',
      role: 'authenticated',
      tenant_id: 'org_xyz'
    };
    await client.setContext({
      role: currentUser.role,
      'jwt.claims.user_id': currentUser.id,
      'app.tenant_id': currentUser.tenant_id // Custom context variable for multi-tenancy
    });
    console.log(`RLS context set for user '${currentUser.id}' in tenant '${currentUser.tenant_id}'.`);

    // Execute a query; RLS policies on the server will automatically filter results
    // based on the 'app.tenant_id' set in the context.
    const result = await client.query('SELECT id, name, description FROM products WHERE tenant_id = current_setting(\'app.tenant_id\', true)::text ORDER BY name LIMIT 3');
    console.log('Query executed. Products (filtered by RLS):', result.rows);

  } catch (error: any) {
    console.error('Database operation failed:', error.message);
  } finally {
    if (client) {
      // Always ensure the client connection is closed
      await client.close();
      console.log('PostgreSQL connection closed.');
    }
  }
}

runPgsqlClientExample();