@memberjunction/generic-database-provider

Intermediate abstract base class for MemberJunction database providers. Sits between the lightweight DatabaseProviderBase (in @memberjunction/core) and platform-specific providers (SQLServerDataProvider, PostgreSQLDataProvider).

Why This Package Exists

DatabaseProviderBase in MJCore cannot depend on heavy packages like @memberjunction/actions, @memberjunction/aiengine, @memberjunction/encryption, or @memberjunction/core-entities. However, the entity action hooks, AI action processing, field-level encryption, and view WHERE clause rendering logic is shared between SQL Server and PostgreSQL providers.

This package provides a single implementation of that shared logic, eliminating code duplication while keeping MJCore lightweight.

Inheritance Chain

DatabaseProviderBase (@memberjunction/core — no heavy deps, abstract)
  └── GenericDatabaseProvider (this package — ActionEngine, AIEngine, EncryptionEngine)
      ├── SQLServerDataProvider (@memberjunction/sqlserver-dataprovider)
      └── PostgreSQLDataProvider (@memberjunction/postgresql-dataprovider)
Copy

What It Provides

Usage

Platform-specific providers should extend GenericDatabaseProvider instead of DatabaseProviderBase:

import { GenericDatabaseProvider } from '@memberjunction/generic-database-provider';

export class MyDatabaseProvider extends GenericDatabaseProvider {
    // Implement remaining abstract methods from DatabaseProviderBase
    // Override EnqueueAfterSaveAIAction if you need transaction-aware deferral
    // Override PostProcessRows if you need platform-specific row processing (call super first)
}
Copy

Keyset (Seek) Pagination

InternalRunView honors RunViewParams.AfterKey (a CompositeKey) to emit keyset-style SQL — WHERE pk > @lastSeen ORDER BY pk LIMIT N — instead of OFFSET ... FETCH NEXT N. Keyset stays O(log N) regardless of depth, making it the right tool for background jobs iterating large entities.

The same code path works on both SQL Server (uses TOP N via BuildTopClause) and PostgreSQL (uses LIMIT N via BuildNonPaginatedLimitSQL) — no per-provider override needed.

Keyset queries automatically bypass LocalCacheManager (both read and write) and are routed past the smart cache check in RunViewsWithCacheCheck because each call uses a different seek key.

Validation enforced by BuildKeysetSeekClause:

• Entity must have a single-column PK (composite throws AfterKeyNotSupportedError with Reason: 'CompositePK')
• PK type must be in the orderable allowlist (everything sensible is)
• OrderBy, if set, must reference only the PK column
• Cannot be combined with non-zero StartRow

See KEYSET_PAGINATION_GUIDE.md for the full pattern and reference implementations.

Server-Side Cache Backend

GenericDatabaseProvider owns the LocalStorageProvider property that all database providers (SQL Server, PostgreSQL) inherit. This is the cache backend used by LocalCacheManager, metadata persistence, and all server-side caching operations.

By default, it uses InMemoryLocalStorageProvider — data stored in a Map inside the Node.js process. For production deployments, swap it to a Redis-backed provider for shared, persistent caching across multiple MJAPI instances.

import { RedisLocalStorageProvider } from '@memberjunction/redis-provider';
import { Metadata } from '@memberjunction/core';
import type { GenericDatabaseProvider } from '@memberjunction/generic-database-provider';

// During server startup:
const redis = new RedisLocalStorageProvider({
    url: process.env.REDIS_URL ?? 'redis://localhost:6379',
    defaultTTLSeconds: 300,
});
(Metadata.Provider as GenericDatabaseProvider).SetLocalStorageProvider(redis);
Copy

See the @memberjunction/redis-provider README for full configuration options, cloud provider examples (Azure Managed Redis, AWS ElastiCache), and production recommendations.

SQL Logging

The SQL logging subsystem lives in GenericDatabaseProvider so it is available to all platform-specific providers (SQL Server, PostgreSQL, etc.). Sessions capture executed SQL statements to files with filtering, formatting, and Flyway migration support.

import { Metadata } from '@memberjunction/core';
import type { GenericDatabaseProvider } from '@memberjunction/generic-database-provider';

const provider = Metadata.Provider as GenericDatabaseProvider;

// Create a logging session
const session = await provider.CreateSqlLogger('./logs/operations.sql', {
    statementTypes: 'mutations',
    prettyPrint: true,
    filterPatterns: [/spCreateAIPromptRun/i],
    filterType: 'exclude',
});

try {
    // All SQL operations are automatically captured
    await provider.ExecuteSQL('INSERT INTO ...');
} finally {
    await session.dispose();
}
Copy

Key types exported from this package:

Switching Database Platforms (Developer Note)

When developing against both SQL Server and PostgreSQL on the same URL/port, clear your browser cache after switching backends. The client-side GraphQLDataProvider caches entity metadata and query results in the browser. UUID casing differs between platforms (SQL Server: uppercase, PostgreSQL: lowercase), so stale cached data from one platform will cause subtle mismatches on the other. Clear browser cache or use an incognito window whenever you switch the backend database.

Full-Text Search

This package implements the createViewUserSearchSQL() method which generates platform-specific full-text search SQL. When entity.FullTextSearchEnabled is true, it calls the CodeGen-generated search function. Otherwise, it falls back to LIKE on fields with IncludeInUserSearchAPI. See the Full-Text Search Guide for the complete architecture.

Dependencies

• @memberjunction/core — Base class and entity types
• @memberjunction/core-entities — MJUserViewEntityExtended, ViewInfo for view WHERE clause rendering
• @memberjunction/actions / @memberjunction/actions-base — Entity action engine
• @memberjunction/aiengine — AI action execution
• @memberjunction/encryption — Field-level encryption/decryption
• @memberjunction/global — Global object store, ensureRegExps for pattern filtering
• @memberjunction/queue — Task queue for after-save AI actions
• sql-formatter — SQL pretty-printing for log output
• uuid — Session ID generation