{"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,"search_vec":"'3.9.3':86 'access':126 'activ':112 'admin':179 'administr':45 'applic':53,71 'approach':51 'backend':147 'broader':105 'build':65 'cadenc':100 'client':2,7,10,14,160,171 'compon':21 'comprehens':59 'construct':25,93,107 'constructive-io':24,92,106 'constructive.io':4 'context':39 'control':80,127 'core':20 'current':83 'databas':44,49,75,131,178 'database-first':48 'deep':118 'design':17,132 'develop':54,63,89,113,139,148 'differenti':115 'distinguish':153 'ecosystem':27,109 'emphas':46 'enabl':62 'fine':124 'fine-grain':123 'first':50 'framework':95 'grain':125 'helper':32 'import':151 'includ':116 'integr':33,119 'io':26,94,108 'javascript':168 'key':114 'level':36,176 'librari':56 'like':135 'maintain':111 'manag':40 'modular':69,130 'npm':161 'ongo':88 'packag':155,162 'pg':172 'pgpm':136 'pgsql':6,9 'pgsql-client':5,8 'postgr':169 'postgrej':167 'postgresql':1,13,70,146,159,170 'postgresql-cli':158 'project':97 'provid':29 'queri':31 'reflect':87 'releas':99 'renam':165 'rich':138 'rls':38,121,173 'robust':30 'row':35,175 'row-level':34 'row-level-secur':174 'safe':68 'scalabl':145 'secur':37,143,177 'ship':57 'specif':16 'stabl':84 'streamlin':142 'support':128 'system':81 'tie':102 'tool':42,134,140 'treat':73 'type':61,67 'type-saf':66 'typescript':60,180 'util':15 'version':79,85 'version-control':78 'via':133 'within':90","created_at":"2026-04-22T04:50:38.842762+00:00","updated_at":"2026-04-22T04:50:38.842762+00:00","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","meta_description":null,"install_score":null,"quickstart_score":null,"quickstart_tag":null,"pypi_latest":null,"cli_name":"","cli_version":null,"type":"library","homepage":"https://constructive.io","github":"https://github.com/constructive-io/constructive","docs":null,"changelog":null,"pypi":null,"npm":"https://www.npmjs.com/package/pgsql-client","openapi_spec":null,"status_page":null,"smithery":null,"categories":["database","auth-security","serialization"],"base_url":null,"auth_type":null,"provenance":{"verified_status":null,"verified_at":null,"last_verified":"2026-06-17","next_check":"2026-07-21","install_tag":null}}