{"id":16488,"library":"pgsql-client","title":"PostgreSQL Client for Constructive.io (pgsql-client)","description":"pgsql-client is a PostgreSQL client utility specifically designed as a core component of the constructive-io ecosystem. It provides robust query helpers, integrated Row-Level Security (RLS) context management, and tools for database administration, emphasizing a database-first approach to application development. This library ships with comprehensive TypeScript types, enabling developers to build type-safe, modular PostgreSQL applications by treating the database as a version-controlled system. Its current stable version, 3.9.3, reflects ongoing development within the constructive-io framework. The project's release cadence is tied to the broader constructive-io ecosystem, which maintains active development. Key differentiators include its deep integration with RLS for fine-grained access control, support for modular database design via tools like `pgpm`, and rich developer tooling to streamline secure and scalable PostgreSQL backend development. It is important to distinguish this package from the `postgresql-client` NPM package, which was renamed to `postgrejs`.","status":"active","version":"3.9.3","language":"javascript","source_language":"en","source_url":"https://github.com/constructive-io/constructive","tags":["javascript","postgres","postgresql","client","pg","rls","row-level-security","database","admin","typescript"],"install":[{"cmd":"npm install pgsql-client","lang":"bash","label":"npm"},{"cmd":"yarn add pgsql-client","lang":"bash","label":"yarn"},{"cmd":"pnpm add pgsql-client","lang":"bash","label":"pnpm"}],"dependencies":[{"reason":"Likely underlying Node.js PostgreSQL driver for core database connectivity, abstracted by pgsql-client.","package":"pg","optional":false}],"imports":[{"note":"Primary class for establishing and managing PostgreSQL connections. Assumed to be ESM-first given modern TypeScript libraries.","wrong":"const Client = require('pgsql-client')","symbol":"Client","correct":"import { Client } from 'pgsql-client'"},{"note":"Interface for configuring client connections, though a connection string is often sufficient. Useful for advanced configurations.","symbol":"ConnectionOptions","correct":"import { ConnectionOptions } from 'pgsql-client'"},{"note":"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.","symbol":"setContext","correct":"await client.setContext({...})"}],"quickstart":{"code":"import { Client } from 'pgsql-client';\n\nasync function runPgsqlClientExample() {\n  // Ensure DATABASE_URL is set in your environment or provide a default\n  const connectionString = process.env.DATABASE_URL ?? 'postgresql://user:password@localhost:5432/mydb';\n\n  let client: Client | undefined;\n  try {\n    // Initialize and connect the PostgreSQL client\n    client = new Client(connectionString);\n    await client.connect();\n    console.log('Successfully connected to PostgreSQL database.');\n\n    // Set RLS context for the current session, crucial for secure data access\n    // In a real application, this context comes from an authenticated user's JWT claims or session data.\n    const currentUser = {\n      id: 'usr_abc123',\n      role: 'authenticated',\n      tenant_id: 'org_xyz'\n    };\n    await client.setContext({\n      role: currentUser.role,\n      'jwt.claims.user_id': currentUser.id,\n      'app.tenant_id': currentUser.tenant_id // Custom context variable for multi-tenancy\n    });\n    console.log(`RLS context set for user '${currentUser.id}' in tenant '${currentUser.tenant_id}'.`);\n\n    // Execute a query; RLS policies on the server will automatically filter results\n    // based on the 'app.tenant_id' set in the context.\n    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');\n    console.log('Query executed. Products (filtered by RLS):', result.rows);\n\n  } catch (error: any) {\n    console.error('Database operation failed:', error.message);\n  } finally {\n    if (client) {\n      // Always ensure the client connection is closed\n      await client.close();\n      console.log('PostgreSQL connection closed.');\n    }\n  }\n}\n\nrunPgsqlClientExample();","lang":"typescript","description":"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."},"warnings":[{"fix":"Ensure you are installing from the correct scope or repository if consuming directly, or use it as intended within the `constructive-io` framework.","message":"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.","severity":"gotcha","affected_versions":"All versions"},{"fix":"Consult the `pgsql-client` and PostgreSQL server release notes before performing major database upgrades. Update `pgsql-client` if necessary.","message":"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.","severity":"breaking","affected_versions":">=3.0"},{"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.","message":"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.","severity":"gotcha","affected_versions":">=3.0"},{"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.","message":"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.","severity":"gotcha","affected_versions":"All versions"}],"env_vars":null,"last_verified":"2026-04-22T00:00:00.000Z","next_check":"2026-07-21T00:00:00.000Z","problems":[{"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)`).","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.","error":"Error: RLS policy denied access for table \"X\" (or similar 'permission denied for relation')"},{"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.","cause":"The provided username, password, host, or port in the connection string is incorrect, or the database server is not reachable.","error":"Error: Cannot connect to database: password authentication failed for user \"X\""},{"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.","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.","error":"TypeError: Cannot read properties of undefined (reading 'query')"}],"ecosystem":"npm"}