@memberjunction/postgresql-dataprovider

Overview

Full-featured PostgreSQL data provider for MemberJunction. This package implements DatabaseProviderBase from @memberjunction/core to provide a complete PostgreSQL backend for MemberJunction applications. It also includes a CodeGen provider for generating PostgreSQL-native database objects (views, PL/pgSQL functions, triggers, indexes, full-text search, and more).

Key capabilities:

• Connection pooling via pg.Pool with configurable min/max connections
• Transaction support with BEGIN/COMMIT/ROLLBACK and variable dependencies between items
• Parameterized queries using PostgreSQL $1, $2, ... positional parameters
• Full CRUD operations through generated PL/pgSQL functions
• CodeGen provider that produces PostgreSQL-native DDL from MemberJunction metadata
• Type conversion for booleans, dates, UUIDs, numbers, and binary data
• Pluggable cache backend inherited from GenericDatabaseProvider — default in-memory, with optional Redis support for shared, persistent caching

Architecture

Class Hierarchy

DatabaseProviderBase (@memberjunction/core)
    └── PostgreSQLDataProvider
            ├── PGConnectionManager (connection pooling)
            ├── PGQueryParameterProcessor (type conversion)
            └── PostgreSQLTransactionGroup (transaction support)

CodeGenDatabaseProvider (@memberjunction/codegen-lib)
    └── PostgreSQLCodeGenProvider (DDL generation)
            └── PostgreSQLCodeGenConnection (query execution)

Source Files

| File | Purpose | |------|---------| | PostgreSQLDataProvider.ts | Main provider: CRUD, queries, view execution, dataset handling, caching | | pgConnectionManager.ts | Connection pool lifecycle with pg.Pool, shared pool support | | queryParameterProcessor.ts | Boolean, date, UUID, number, and binary type conversion | | types.ts | PostgreSQLProviderConfigData and PostgreSQLProviderConfigOptions interfaces | | PostgreSQLTransactionGroup.ts | BEGIN/COMMIT/ROLLBACK with variable dependencies between transaction items | | codegen/PostgreSQLCodeGenProvider.ts | CodeGen: views, CRUD functions, triggers, indexes, FTS, permissions | | codegen/PostgreSQLCodeGenConnection.ts | Translates @ParamName notation to $N positional parameters |

Configuration

Connection Configuration

interface PGConnectionConfig {
    Host: string;
    Port?: number;                    // Default: 5432
    Database: string;
    User: string;
    Password: string;
    SSL?: boolean | pg.ConnectionConfig['ssl'];
    MaxConnections?: number;          // Default: 20
    MinConnections?: number;          // Default: 2
    IdleTimeoutMillis?: number;       // Default: 30000
    ConnectionTimeoutMillis?: number; // Default: 30000
    MJCoreSchemaName?: string;        // Default: '__mj'
}

Provider Configuration

The PostgreSQLProviderConfigData class wraps the connection config and adds MemberJunction-specific options:

const configData = new PostgreSQLProviderConfigData(
    connectionConfig,         // PGConnectionConfig
    '__mj',                   // MJCoreSchemaName (default: '__mj')
    0,                        // CheckRefreshIntervalSeconds (0 = disabled)
    ['public', '__mj'],       // includeSchemas (optional)
    [],                       // excludeSchemas (optional)
    true                      // ignoreExistingMetadata (default: true)
);

Environment Variables

For CodeGen shell execution (psql):

• PGHOST -- PostgreSQL server host
• PGPORT -- PostgreSQL server port
• PGDATABASE -- Database name
• PGUSER -- Username
• PGPASSWORD -- Password (or use .pgpass file)

Usage

Basic Setup

import { PostgreSQLDataProvider } from '@memberjunction/postgresql-dataprovider';

const provider = new PostgreSQLDataProvider();
await provider.Config(new PostgreSQLProviderConfigData(
    {
        Host: 'localhost',
        Port: 5432,
        Database: 'memberjunction',
        User: 'mj_user',
        Password: 'secret',
        MJCoreSchemaName: '__mj'
    },
    '__mj'
));

Running Queries

// Execute raw SQL with positional parameters
const result = await provider.ExecuteSQL<MyType>(
    'SELECT * FROM __mj."User" WHERE "ID" = $1',
    [$userId]
);

// Run views (MJ pattern)
const results = await provider.InternalRunView<UserEntity>({
    EntityName: 'Users',
    ExtraFilter: "Status='Active'",
    OrderBy: 'Name',
    MaxRows: 100
});

Transactions

const txGroup = await provider.CreateTransactionGroup();
// Add operations to the transaction group...
await txGroup.Submit(); // Executes all items in a single BEGIN/COMMIT block

The PostgreSQLTransactionGroup supports variable dependencies between transaction items, allowing later items to reference values produced by earlier items in the same transaction.

Nested transactions (SAVEPOINTs)

BeginTransaction() / CommitTransaction() / RollbackTransaction() support arbitrary nesting that mirrors the SQLServerDataProvider's depth/savepoint-stack model:

| Depth | Begin emits | Commit emits | Rollback emits | |---|---|---|---| | 1 (outermost) | BEGIN on a fresh client | COMMIT + release client | ROLLBACK + release client | | 2+ (nested) | SAVEPOINT mj_sp_<n> | RELEASE SAVEPOINT mj_sp_<n> | ROLLBACK TO SAVEPOINT mj_sp_<n> + RELEASE SAVEPOINT |

await provider.BeginTransaction();      // BEGIN; depth=1
await provider.BeginTransaction();      // SAVEPOINT mj_sp_1; depth=2
//   ...do work...
await provider.RollbackTransaction();   // ROLLBACK TO + RELEASE mj_sp_1; depth=1
await provider.CommitTransaction();     // COMMIT; depth=0

Use provider.TransactionDepth to inspect the current nesting depth (0 = no active transaction). If COMMIT or ROLLBACK itself fails at depth 1, the provider force-releases the client to avoid leaking a poisoned connection back to the pool.

Connection Pool Sharing

The PGConnectionManager supports shared pools for scenarios where multiple provider instances need to share a single connection pool (e.g., per-request providers):

// Primary provider creates the pool
const primary = new PGConnectionManager();
await primary.Initialize(config);

// Per-request providers share the pool (won't close it on Close())
const perRequest = new PGConnectionManager();
perRequest.InitializeWithExistingPool(primary.Pool, config);

CodeGen Provider

What It Generates

The PostgreSQLCodeGenProvider generates PostgreSQL-native database objects during the MemberJunction code generation process:

• Base Views -- CREATE OR REPLACE VIEW with joins and soft-delete filtering
• CRUD Functions -- PL/pgSQL functions (not stored procedures) with RETURNING clause
• Timestamp Triggers -- PL/pgSQL trigger functions for automatic __mj_UpdatedAt maintenance
• Foreign Key Indexes -- CREATE INDEX IF NOT EXISTS with 63-character name limit enforcement
• Full-Text Search -- tsvector columns, GIN indexes, PL/pgSQL trigger for auto-update, and search functions
• Cascade Deletes -- Recursive delete/update-to-NULL operations for related records
• Permissions -- GRANT statements per entity role
• Drop Guards -- DROP ... IF EXISTS ... CASCADE statements before object creation

Key PostgreSQL-Specific Patterns

• Uses PL/pgSQL CREATE OR REPLACE FUNCTION instead of SQL Server stored procedures
• Functions return query results via RETURNS TABLE(...) or RETURNS SETOF
• Uses dollar-quoted strings ($$...$$) for function bodies
• Implements LATERAL joins instead of SQL Server's OUTER APPLY
• Full-text search uses tsvector/tsquery with GIN indexes (not CONTAINSTABLE)
• Index names are truncated to 63 characters to respect the PostgreSQL identifier limit
• Boolean columns use native true/false instead of SQL Server's 1/0
• Uses LIMIT/OFFSET for pagination instead of TOP/OFFSET-FETCH
• Identifier quoting uses "double quotes" instead of SQL Server's [brackets]

Registration

The CodeGen provider is registered via the MemberJunction class factory:

@RegisterClass(CodeGenDatabaseProvider, 'PostgreSQLCodeGenProvider')
export class PostgreSQLCodeGenProvider extends CodeGenDatabaseProvider { ... }

Relationship to Other Packages

@memberjunction/sql-dialect

• Provides PostgreSQLDialect for identifier quoting, pagination syntax, and type mapping
• Used by both PostgreSQLDataProvider and PostgreSQLCodeGenProvider
• Zero-dependency abstraction layer shared across all database-specific code

@memberjunction/codegen-lib

• Provides CodeGenDatabaseProvider abstract base class with ~55 methods
• PostgreSQLCodeGenProvider implements all abstract methods with PostgreSQL-native SQL
• The CodeGen orchestration layer calls these methods to generate DDL during code generation runs

@memberjunction/core

• Provides DatabaseProviderBase abstract class defining the full MJ data provider contract
• PostgreSQLDataProvider implements CRUD, view execution, dataset operations, and metadata access

@memberjunction/global

• Provides @RegisterClass for class factory registration
• PostgreSQLCodeGenProvider registers itself so the CodeGen system can discover it at runtime

Dependencies

| Package | Purpose | |---------|---------| | @memberjunction/core | Base provider interfaces and entity framework | | @memberjunction/codegen-lib | CodeGen base class and orchestration types | | @memberjunction/sql-dialect | SQL dialect abstraction (quoting, pagination, types) | | @memberjunction/global | Class registration via @RegisterClass | | pg | PostgreSQL Node.js driver (connection pooling, queries) |

Testing

cd packages/PostgreSQLDataProvider
npm run test          # Run all tests
npm run test:watch    # Watch mode
npm run test:coverage # Coverage report

Tests use Vitest with mocked database connections (no live database required).

Platform Key

The provider identifies itself with PlatformKey: 'postgresql', which is used throughout MemberJunction to select the correct SQL dialect, CodeGen templates, and runtime behavior for PostgreSQL deployments.