@bernierllc/database-adapter-postgresql

PostgreSQL/Supabase database adapter with Row Level Security, real-time subscriptions, and full-text search.

Installation

npm install @bernierllc/database-adapter-postgresql

Features

• Connection Pooling - Efficient connection management with pg Pool
• Row Level Security - Multi-tenant security with Clerk JWT integration
• Real-time Subscriptions - Supabase real-time for collaborative editing
• Full-Text Search - pg_trgm fuzzy matching and ts_vector search
• Transaction Support - ACID transactions with savepoints
• Automatic Retries - Connection retry with exponential backoff
• Type Safety - Strict TypeScript with comprehensive type definitions
• Logging Integration - Structured logging with @bernierllc/logger

Usage

Basic Connection

import { PostgreSQLAdapter } from '@bernierllc/database-adapter-postgresql';

const adapter = new PostgreSQLAdapter({
  type: 'postgresql',
  host: 'localhost',
  port: 5432,
  database: 'myapp',
  username: 'postgres',
  password: 'password',
  pooling: {
    min: 2,
    max: 10
  }
});

await adapter.connect();

With Supabase

const adapter = new PostgreSQLAdapter({
  type: 'postgresql',
  host: 'db.project.supabase.co',
  port: 5432,
  database: 'postgres',
  username: 'postgres',
  password: 'password',
  ssl: true,
  supabaseUrl: 'https://project.supabase.co',
  supabaseAnonKey: 'your-anon-key'
});

Query Execution

// Simple query
const result = await adapter.query('SELECT * FROM users WHERE id = $1', ['user-id']);

// Typed query
interface User {
  id: string;
  email: string;
  role: string;
}

const users = await adapter.query<User>('SELECT * FROM users');
console.log(users.rows[0].email);

Transactions

const txn = await adapter.beginTransaction();

try {
  await txn.query('INSERT INTO users (email) VALUES ($1)', ['[email protected]']);
  await txn.query('INSERT INTO profiles (user_id) VALUES ($1)', ['user-id']);

  await txn.commit();
} catch (error) {
  await txn.rollback();
  throw error;
}

Savepoints

const txn = await adapter.beginTransaction();

await txn.query('INSERT INTO content (title) VALUES ($1)', ['Draft 1']);
await txn.savepoint('draft1');

await txn.query('INSERT INTO content (title) VALUES ($1)', ['Draft 2']);

// Rollback to savepoint
await txn.rollbackToSavepoint('draft1');

await txn.commit(); // Only 'Draft 1' is committed

Row Level Security

import { RLSPolicyGenerator } from '@bernierllc/database-adapter-postgresql';

// Generate user-scoped policy
const policy = RLSPolicyGenerator.generateUserPolicy('content');
await adapter.query(policy);

// Set RLS context for queries
adapter.setRLSContext('user_123', 'user', 'tenant_456');

// All subsequent queries respect RLS policies
const content = await adapter.query('SELECT * FROM content');
// Only returns content for user_123 in tenant_456

Clerk Integration

import { ClerkIntegration } from '@bernierllc/database-adapter-postgresql';

// Set up Clerk authentication functions
const setup = ClerkIntegration.generateCompleteSetup();
await adapter.query(setup);

// RLS policies automatically use Clerk JWT
const policy = RLSPolicyGenerator.generateUserPolicy('content');
await adapter.query(policy);

Real-time Subscriptions

import { RealtimeManager } from '@bernierllc/database-adapter-postgresql';

const realtimeManager = new RealtimeManager(adapter);

// Subscribe to content changes
const unsubscribe = realtimeManager.subscribeToContent('content-id', {
  onInsert: (payload) => {
    console.log('New version created:', payload.new);
  },
  onUpdate: (payload) => {
    console.log('Content updated:', payload.new);
  },
  onDelete: (payload) => {
    console.log('Content deleted:', payload.old);
  }
});

// Broadcast presence for collaborative editing
await realtimeManager.broadcastPresence('content-id', 'user-id', {
  line: 10,
  column: 5
});

// Get current presence state
const presence = realtimeManager.getPresence('content-id');

// Cleanup
unsubscribe();

Full-Text Search

import { FullTextSearchManager } from '@bernierllc/database-adapter-postgresql';

const searchManager = new FullTextSearchManager(adapter);

// Create indexes first
await searchManager.createIndexes();

// Fuzzy search with typo tolerance
const results = await searchManager.searchContent('databse', {
  minSimilarity: 0.3,
  limit: 10
});

results.forEach(result => {
  console.log(result.title, 'Relevance:', result.totalSimilarity);
});

// Combined search (pg_trgm + ts_vector)
const combined = await searchManager.searchCombined('PostgreSQL performance', {
  minSimilarity: 0.2,
  limit: 20
});

// Search specific fields
const titleResults = await searchManager.searchContent('guide', {
  fields: ['title'],
  minSimilarity: 0.4
});

Migrations

import { migration_001_initial_schema } from '@bernierllc/database-adapter-postgresql';

// Run migration
await migration_001_initial_schema.up(adapter);

// Rollback
await migration_001_initial_schema.down(adapter);

Health Checks

const health = await adapter.healthCheck();

console.log('Connected:', health.connected);
console.log('Latency:', health.latencyMs, 'ms');
console.log('Version:', health.version);
console.log('Active connections:', health.activeConnections);
console.log('Max connections:', health.maxConnections);

Pool Statistics

const stats = adapter.getPoolStats();

console.log('Total connections:', stats.total);
console.log('Idle connections:', stats.idle);
console.log('Waiting requests:', stats.waiting);

API Reference

PostgreSQLAdapter

Main adapter class extending BaseDatabaseAdapter from @bernierllc/database-adapter-core.

Methods

• connect(): Promise<void> - Connect to PostgreSQL with retry
• disconnect(): Promise<void> - Close connection pool
• query<T>(sql: string, params?: any[]): Promise<IQueryResult<T>> - Execute query
• beginTransaction(): Promise<ITransaction> - Start transaction
• healthCheck(): Promise<IDatabaseHealth> - Check connection health
• setRLSContext(userId: string, userRole: string, tenantId?: string): void - Set RLS context
• clearRLSContext(): void - Clear RLS context
• getSupabaseClient(): SupabaseClient | null - Get Supabase client
• getPoolStats(): object | null - Get connection pool statistics

PostgreSQLTransaction

Transaction implementation with savepoint support.

Methods

• query<T>(sql: string, params?: any[]): Promise<IQueryResult<T>> - Execute query in transaction
• commit(): Promise<void> - Commit transaction
• rollback(): Promise<void> - Rollback transaction
• savepoint(name: string): Promise<void> - Create savepoint
• rollbackToSavepoint(name: string): Promise<void> - Rollback to savepoint
• releaseSavepoint(name: string): Promise<void> - Release savepoint
• isActive(): boolean - Check if transaction is active

RLSPolicyGenerator

Static utility for generating RLS policies.

Methods

• generateUserPolicy(table: string): string - User-scoped access
• generateAdminPolicy(table: string): string - Admin bypass
• generateTenantPolicy(table: string): string - Multi-tenant isolation
• generatePublicReadPolicy(table: string, condition?: string): string - Public read access
• generateSharedResourcePolicy(table: string, sharingTable?: string): string - Shared resources
• generateRoleBasedPolicy(table: string, roles: string[]): string - Role-based access
• generateTimeBasedPolicy(table: string): string - Time-based access
• disableRLS(table: string): string - Disable RLS (use with caution)

ClerkIntegration

Static utility for Clerk JWT integration.

Methods

• generateCompleteSetup(): string - Generate all functions and schema
• generateAuthSchema(): string - Create auth schema
• generateClerkUserIdFunction(): string - Extract Clerk user ID
• generateUserIdFunction(): string - Get internal user ID
• generateIsAdminFunction(): string - Check admin status
• generateUserRoleFunction(): string - Get user role
• generateHasRoleFunction(): string - Check specific role
• generateUserTenantIdFunction(): string - Get user tenant ID

RealtimeManager

Manages Supabase real-time subscriptions.

Methods

• subscribeToContent(contentId: string, callbacks: RealtimeCallbacks): () => void - Subscribe to content
• subscribeToTable(table: string, filter: string | null, callbacks: RealtimeCallbacks): () => void - Subscribe to table
• broadcastPresence(contentId: string, userId: string, cursorPosition?: object): Promise<void> - Broadcast presence
• getPresence(contentId: string): object | null - Get presence state
• unsubscribe(contentId: string): Promise<void> - Unsubscribe from channel
• unsubscribeAll(): Promise<void> - Unsubscribe from all channels

FullTextSearchManager

PostgreSQL full-text search with pg_trgm.

Methods

• searchContent(query: string, options?: SearchOptions): Promise<SearchResult[]> - Fuzzy search
• searchWithTsVector(query: string, options?: SearchOptions): Promise<any[]> - ts_vector search
• searchCombined(query: string, options?: SearchOptions): Promise<SearchResult[]> - Combined search
• createIndexes(): Promise<void> - Create search indexes
• dropIndexes(): Promise<void> - Drop search indexes
• getSearchStats(): Promise<object> - Get index statistics

Configuration

IDatabaseConfig

interface IDatabaseConfig {
  type: 'postgresql';
  host: string;
  port?: number; // Default: 5432
  database: string;
  username: string;
  password: string;
  ssl?: boolean | object;
  pooling?: {
    min?: number; // Default: 2
    max?: number; // Default: 10
    idleTimeoutMs?: number; // Default: 30000
  };
  connectionTimeoutMs?: number; // Default: 5000
}

ISupabaseConfig

interface ISupabaseConfig {
  supabaseUrl?: string;
  supabaseAnonKey?: string;
  supabaseServiceKey?: string;
}

Integration Status

• Logger: ✅ Integrated - Uses @bernierllc/logger for structured logging
• Docs-Suite: ✅ Ready - TypeDoc API documentation, complete README
• NeverHub: 🔔 Optional - Health metrics and pool statistics publishing

NeverHub Integration

This package provides optional NeverHub integration for enhanced observability:

What NeverHub Provides:

• Real-time connection pool monitoring
• Query performance metrics aggregation
• Database health status broadcasting
• Automatic service discovery for database adapters

Integration Approach:

• Graceful Degradation: All core functionality works without NeverHub
• Auto-Detection: Package detects NeverHub availability at runtime
• Opt-In Publishing: Metrics are published only when NeverHub is present

Usage Example:

import { PostgreSQLAdapter } from '@bernierllc/database-adapter-postgresql';
import { NeverHubAdapter } from '@bernierllc/neverhub-adapter';

const adapter = new PostgreSQLAdapter({ /* config */ });
await adapter.connect();

// Optional: Publish metrics to NeverHub if available
if (await NeverHubAdapter.detect()) {
  const neverhub = new NeverHubAdapter();
  await neverhub.register({
    type: 'database-adapter',
    name: '@bernierllc/database-adapter-postgresql',
    capabilities: [
      { type: 'storage', name: 'postgresql', version: '1.0.0' }
    ]
  });

  // Publish pool statistics
  setInterval(async () => {
    const stats = adapter.getPoolStats();
    await neverhub.publishEvent({
      type: 'database.pool.stats',
      data: stats
    });
  }, 30000); // Every 30 seconds
}

Metrics Published:

• database.pool.stats - Connection pool statistics (total, idle, waiting)
• database.health - Health check results (connected, latency, version)
• database.query.performance - Query execution metrics (optional, requires instrumentation)

Why Optional:

• Core packages should minimize dependencies
• Database operations must work in all environments
• NeverHub primarily benefits distributed systems with multiple services
• Single-instance applications don't need service discovery

Performance

• Query Latency: <5ms (p95) for indexed queries
• Connection Pool Reuse: >90%
• Real-time Latency: <100ms for subscriptions
• Search: Fuzzy matching with typo tolerance via pg_trgm

Security

• SQL Injection Protection: Parameterized queries only
• RLS Enforcement: Row-level security for all user data
• Clerk JWT Verification: Automatic user authentication
• Tenant Isolation: Multi-tenant data separation

Testing

This package uses testcontainers for real PostgreSQL testing (no mocks).

npm test                 # Run tests in watch mode
npm run test:run         # Run tests once
npm run test:coverage    # Run with coverage report

See Also

• @bernierllc/database-adapter-core - Abstract base classes
• @bernierllc/logger - Structured logging
• @bernierllc/retry-policy - Retry logic
• @bernierllc/crypto-utils - Cryptographic utilities

License

Copyright (c) 2025 Bernier LLC. All rights reserved.