MemberJunction SQL Server Data Provider

A comprehensive SQL Server data provider implementation for the MemberJunction framework, serving as the primary bridge between MemberJunction applications and Microsoft SQL Server databases. This package implements all core data provider interfaces -- entity CRUD, metadata management, view/query/report execution, and transaction handling. SQL logging is inherited from @memberjunction/generic-database-provider.

Architecture Overview

%%{init: {'theme': 'base', 'themeVariables': { 'lineColor': '#888' }}}%%
graph TB
    subgraph Application["Application Layer"]
        MJS["MJServer / MJAPI"]
        MS["MetadataSync"]
        Custom["Custom Applications"]
    end

    subgraph Provider["SQLServerDataProvider"]
        style Provider fill:#2d6a9f,stroke:#1a4971,color:#fff
        SDP["SQLServerDataProvider"]
        TXG["SQLServerTransactionGroup"]
        UC["UserCache"]
        QPP["QueryParameterProcessor"]
        NFS["NodeFileSystemProvider"]
    end

    subgraph GenericDP["GenericDatabaseProvider (inherited)"]
        style GenericDP fill:#b8762f,stroke:#8a5722,color:#fff
        SL["SQL Logging Sessions"]
        EH["Entity/AI Action Hooks"]
        ENC["Field Encryption"]
    end

    subgraph Interfaces["MJ Core Interfaces"]
        style Interfaces fill:#7c5295,stroke:#563a6b,color:#fff
        IED["IEntityDataProvider"]
        IMP["IMetadataProvider"]
        IRV["IRunViewProvider"]
        IRR["IRunReportProvider"]
    end

    subgraph Database["SQL Server"]
        style Database fill:#2d8659,stroke:#1a5c3a,color:#fff
        Pool["Connection Pool"]
        SP["Stored Procedures"]
        Views["Database Views"]
        Tables["Entity Tables"]
    end

    MJS --> SDP
    MS --> SDP
    Custom --> SDP
    SDP --> IED
    SDP --> IMP
    SDP --> IRV
    SDP --> IRR
    SDP --> TXG
    SDP --> UC
    SDP --> QPP
    SDP --> NFS
    SDP --> SL
    SDP --> EH
    SDP --> ENC
    SDP --> Pool
    Pool --> SP
    Pool --> Views
    Pool --> Tables
Copy

Key Features

• Full CRUD Operations -- Create, Read, Update, Delete for all MemberJunction entities via generated stored procedures
• Transaction Support -- Both transaction groups (multi-entity atomic operations) and instance-level transactions with nested savepoint support
• View Execution -- Run database views with filtering, sorting, pagination (both OFFSET-based StartRow and keyset-based AfterKey — see KEYSET_PAGINATION_GUIDE.md), and aggregation
• Report and Query Execution -- Execute reports and parameterized queries with Nunjucks template processing
• Connection Pooling -- Efficient shared connection pool management with configurable sizing
• SQL Logging -- Real-time SQL statement capture to files with session management, pattern filtering, and Flyway migration formatting (inherited from GenericDatabaseProvider)
• User and Role Caching -- Server-side singleton cache for user information and role assignments
• Record Change Tracking -- Integrated audit trail for entity modifications
• Duplicate Detection -- AI-powered duplicate record detection via vector similarity
• Record Merging -- Merge duplicate records with dependency resolution
• Row-Level Security -- Enforced data access controls at the database level
• Metadata Refresh -- Automatic and on-demand metadata refresh with configurable intervals
• Field Encryption -- Transparent encryption and decryption of sensitive entity fields
• DateTime Offset Handling -- Automatic detection and adjustment for SQL Server timezone behavior
• Pluggable Cache Backend -- Default in-memory caching inherited from GenericDatabaseProvider, with optional Redis support for shared, persistent caching across multiple server instances

Installation

npm install @memberjunction/sqlserver-dataprovider
Copy

Dependencies

Exported API

The package exports the following public symbols from its entry point:

Data Flow

%%{init: {'theme': 'base', 'themeVariables': { 'lineColor': '#888' }}}%%
sequenceDiagram
    participant App as Application
    participant SDP as SQLServerDataProvider
    participant Queue as SQL Queue (RxJS)
    participant Logger as SqlLoggingSessions
    participant Pool as Connection Pool
    participant DB as SQL Server

    App->>SDP: Save(entity, user, options)
    SDP->>SDP: Generate SP call SQL
    SDP->>SDP: Encrypt sensitive fields

    alt Transaction Active
        SDP->>Queue: Enqueue (sequential)
        Queue->>Pool: Execute via Transaction
    else No Transaction
        SDP->>Pool: Execute directly (parallel)
    end

    SDP-->>Logger: Log SQL (parallel, non-blocking)
    Pool->>DB: Execute stored procedure
    DB-->>Pool: Return result set
    Pool-->>SDP: Raw result
    SDP->>SDP: Process rows (decrypt, timezone adjust)
    SDP-->>App: BaseEntityResult
Copy

Usage

Initialization with setupSQLServerClient

The setupSQLServerClient helper handles full provider initialization: connecting to the pool, configuring the provider, loading the user cache, setting up the global MJ provider, and running the startup manager.

import { setupSQLServerClient } from '@memberjunction/sqlserver-dataprovider';
import { SQLServerProviderConfigData } from '@memberjunction/sqlserver-dataprovider';
import sql from 'mssql';

// Create and connect a connection pool
const pool = new sql.ConnectionPool({
  server: 'your-server.database.windows.net',
  port: 1433,
  database: 'YourMJDatabase',
  user: 'your-username',
  password: 'your-password',
  options: {
    encrypt: true,
    trustServerCertificate: false,
  },
  pool: {
    max: 50,
    min: 5,
    idleTimeoutMillis: 30000,
  },
});
await pool.connect();

// Initialize the provider (sets global MJ provider, loads user cache, runs startup)
const config = new SQLServerProviderConfigData(
  pool,
  '__mj',           // MJ core schema name
  60,               // metadata refresh interval in seconds (0 to disable)
  undefined,        // includeSchemas (undefined = all)
  undefined,        // excludeSchemas
  true              // ignoreExistingMetadata (true for first instance)
);

const provider = await setupSQLServerClient(config);
Copy

Working with Entities

import { Metadata, CompositeKey } from '@memberjunction/core';
import { UserEntity } from '@memberjunction/core-entities';

const md = new Metadata();

// Load an entity by primary key
const user = await md.GetEntityObject<UserEntity>('Users', contextUser);
const key = new CompositeKey([{ FieldName: 'ID', Value: userId }]);
await user.Load(key);
console.log(`Loaded: ${user.Name}`);

// Create a new entity
const newUser = await md.GetEntityObject<UserEntity>('Users', contextUser);
newUser.Name = 'John Doe';
newUser.Email = 'john@example.com';
const saved = await newUser.Save();
if (saved) {
  console.log(`Created user with ID: ${newUser.ID}`);
}

// Delete an entity
await newUser.Delete();
Copy

Transaction Groups

Transaction groups execute multiple entity operations within a single database transaction, with automatic rollback on failure.

import { SQLServerTransactionGroup } from '@memberjunction/sqlserver-dataprovider';

const transaction = await provider.CreateTransactionGroup();

const order = await md.GetEntityObject('Orders', contextUser);
order.CustomerID = customerId;
order.Status = 'New';
order.TransactionGroup = transaction;

const item = await md.GetEntityObject('Order Items', contextUser);
item.ProductID = productId;
item.Quantity = 2;
item.TransactionGroup = transaction;

// Both saves are queued, then executed atomically on Submit
await order.Save();
await item.Save();
const results = await transaction.Submit();
Copy

Instance-Level Transactions

For multi-user server environments, each provider instance supports isolated transaction state with nested savepoints.

try {
  await provider.BeginTransaction();

  await provider.Save(entity1, contextUser, {});
  await provider.Save(entity2, contextUser, {});

  await provider.CommitTransaction();
} catch (error) {
  await provider.RollbackTransaction();
  throw error;
}
Copy

Running Views

import { RunView } from '@memberjunction/core';

const rv = new RunView();
const result = await rv.RunView({
  EntityName: 'Users',
  ExtraFilter: "Status = 'Active'",
  OrderBy: 'Name',
  MaxRows: 100,
  ResultType: 'entity_object',
}, contextUser);

if (result.Success) {
  console.log(`Found ${result.Results.length} active users`);
}
Copy

Running Parameterized Queries

The QueryParameterProcessor validates parameters and processes Nunjucks templates for parameterized queries.

import { RunQuery } from '@memberjunction/core';

const rq = new RunQuery();
const result = await rq.RunQuery({
  QueryName: 'ActiveUsersByDepartment',
  CategoryPath: '/Reports/Users/',
  Parameters: {
    department: 'Engineering',
    minHireDate: '2023-01-01',
  },
}, contextUser);

if (result.Success) {
  console.log('Query results:', result.Results);
}

// Ad-hoc SQL execution (SELECT/WITH only — validated via SQLExpressionValidator, read-only)
const adhocResult = await rq.RunQuery({
  SQL: 'SELECT TOP 50 Name, Status FROM __mj.vwUsers WHERE IsActive = 1',
}, contextUser);
Copy

Executing Raw SQL

// Instance method
const rows = await provider.ExecuteSQL(
  'SELECT * FROM Users WHERE Department = @dept',
  { dept: 'Engineering' }
);

// Static method (useful when you have a pool but not a provider)
const rows2 = await SQLServerDataProvider.ExecuteSQLWithPool(
  pool,
  'SELECT TOP 10 * FROM Users ORDER BY Name'
);
Copy

SQL Logging

The provider includes a comprehensive SQL logging subsystem for capturing executed SQL statements to files. Logging sessions run in parallel with query execution and do not impact performance.

Logging Architecture

%%{init: {'theme': 'base', 'themeVariables': { 'lineColor': '#888' }}}%%
graph LR
    subgraph Execution["SQL Execution"]
        style Execution fill:#2d6a9f,stroke:#1a4971,color:#fff
        EX["ExecuteSQL"]
    end

    subgraph Sessions["Active Logging Sessions"]
        style Sessions fill:#b8762f,stroke:#8a5722,color:#fff
        S1["Session 1: mutations only"]
        S2["Session 2: migration format"]
        S3["Session 3: user-filtered"]
    end

    subgraph Output["Output Files"]
        style Output fill:#2d8659,stroke:#1a5c3a,color:#fff
        F1["operations.sql"]
        F2["migration.sql"]
        F3["user-audit.sql"]
    end

    EX -->|parallel, non-blocking| S1
    EX -->|parallel, non-blocking| S2
    EX -->|parallel, non-blocking| S3
    S1 -->|filtered + formatted| F1
    S2 -->|schema placeholders| F2
    S3 -->|user-scoped| F3
Copy

Creating a Logging Session

const session = await provider.CreateSqlLogger('./logs/operations.sql', {
  sessionName: 'Debug session',
  statementTypes: 'both',         // 'queries', 'mutations', or 'both'
  prettyPrint: true,              // Format SQL with sql-formatter
  formatAsMigration: false,       // Replace schema names with Flyway placeholders
  logRecordChangeMetadata: false, // Log only core SP calls, not change tracking wrapper
  retainEmptyLogFiles: false,     // Delete file if no statements were logged
  filterByUserId: 'user@example.com', // Only capture this user's SQL
  filterPatterns: [/spCreateAIPromptRun/i],  // Exclude matching patterns
  filterType: 'exclude',          // 'exclude' or 'include'
});

try {
  // All SQL operations are automatically captured
  await provider.ExecuteSQL('INSERT INTO ...');
  console.log(`Captured ${session.statementCount} statements`);
} finally {
  await session.dispose(); // Stop logging, close file, clean up
}
Copy

Migration-Ready Format

When formatAsMigration: true, the logger automatically:

• Replaces schema names with ${flyway:defaultSchema} placeholders
• Escapes ${...} patterns within SQL string literals to prevent Flyway interpretation
• Splits string literals exceeding SQL Server's 4000-character NVARCHAR limit into concatenated chunks with CAST(... AS NVARCHAR(MAX))

const session = await provider.CreateSqlLogger(
  './migrations/V20250207120000__entity_updates.sql',
  {
    formatAsMigration: true,
    batchSeparator: 'GO',
    description: 'Entity schema updates',
  }
);
Copy

Pattern Filtering

Filter which SQL statements are logged using simple wildcard patterns or full regular expressions.

Simple wildcards use * as a wildcard character:

• *AIPrompt* -- matches anything containing "AIPrompt"
• spCreate* -- matches anything starting with "spCreate"

Regex patterns provide full regular expression support:

• /spCreate.*Run/i -- case-insensitive regex
• /^SELECT.*FROM.*vw/ -- queries from views

// Exclude noisy patterns
const session = await provider.CreateSqlLogger('./logs/filtered.sql', {
  filterPatterns: [
    /spCreateAIPromptRun/i,
    /^SELECT.*FROM.*vw.*Metadata/i,
    '*EntityFieldValue*',
  ],
  filterType: 'exclude', // Default: skip matching statements
});

// Include only specific patterns
const auditSession = await provider.CreateSqlLogger('./logs/audit.sql', {
  filterPatterns: [/INSERT INTO Users/i, /UPDATE Users/i, /DELETE FROM Users/i],
  filterType: 'include', // Only log matching statements
});
Copy

Session Management

// List all active sessions
const active = provider.GetActiveSqlLoggingSessions();
console.log(`${active.length} sessions active`);

// Get a specific session
const session = provider.GetSqlLoggingSessionById(sessionId);

// Dispose all sessions (cleanup on shutdown)
await provider.DisposeAllSqlLoggingSessions();
Copy

Transaction Processing

%%{init: {'theme': 'base', 'themeVariables': { 'lineColor': '#888' }}}%%
graph TD
    subgraph TransactionGroup["Transaction Group Flow"]
        style TransactionGroup fill:#7c5295,stroke:#563a6b,color:#fff
        TG1["AddTransaction(entity)"]
        TG2["Submit()"]
        TG3["Begin SQL Transaction"]
        TG4["Execute items sequentially"]
        TG5{"All succeeded?"}
        TG6["Commit"]
        TG7["Rollback"]
    end

    subgraph InstanceTx["Instance Transaction Flow"]
        style InstanceTx fill:#2d6a9f,stroke:#1a4971,color:#fff
        IT1["BeginTransaction()"]
        IT2["Queue serializes queries"]
        IT3["Save / Delete / ExecuteSQL"]
        IT4["CommitTransaction()"]
        IT5["RollbackTransaction()"]
    end

    TG1 --> TG2
    TG2 --> TG3
    TG3 --> TG4
    TG4 --> TG5
    TG5 -->|Yes| TG6
    TG5 -->|No| TG7

    IT1 --> IT2
    IT2 --> IT3
    IT3 --> IT4
    IT3 -->|Error| IT5
Copy

The provider supports two transaction mechanisms:

Transaction Groups (SQLServerTransactionGroup) -- bundle multiple entity save/delete operations and execute them within a single SQL Server transaction. If any operation fails, the entire group is rolled back. Transaction groups also support inter-entity variable references, allowing a newly created entity's ID to be passed to dependent entities in the same batch.

Instance-Level Transactions -- each SQLServerDataProvider instance maintains its own transaction state. When a transaction is active, all SQL queries from that instance are serialized through an RxJS queue (concatMap) and executed against the same sql.Transaction object. Non-transactional queries bypass the queue for maximum parallelism. Nested transactions use SQL Server savepoints.

UserCache

The UserCache is a singleton that loads all users and their role assignments from the database and keeps them in memory. It is used for user lookups during authentication and authorization.

import { UserCache } from '@memberjunction/sqlserver-dataprovider';

// Access the singleton
const cache = UserCache.Instance;

// Look up users
const user = cache.UserByName('john@example.com');
const systemUser = cache.GetSystemUser();
const allUsers = cache.Users;

// Refresh from database (with optional auto-refresh interval in ms)
await cache.Refresh(pool, 60000);
Copy

QueryParameterProcessor

Handles parameter validation and Nunjucks template rendering for parameterized queries.

%%{init: {'theme': 'base', 'themeVariables': { 'lineColor': '#888' }}}%%
graph LR
    subgraph Input["Query Input"]
        style Input fill:#2d6a9f,stroke:#1a4971,color:#fff
        QI["QueryInfo with SQL template"]
        P["Parameters"]
    end

    subgraph Processing["QueryParameterProcessor"]
        style Processing fill:#b8762f,stroke:#8a5722,color:#fff
        V["Validate parameters"]
        T["Type conversion"]
        R["Nunjucks render"]
    end

    subgraph Output["Result"]
        style Output fill:#2d8659,stroke:#1a5c3a,color:#fff
        SQL["Processed SQL"]
    end

    QI --> V
    P --> V
    V --> T
    T --> R
    R --> SQL
Copy

• Validates required parameters, applies defaults, and performs type conversion (string, number, date, boolean, array)
• Renders parameterized SQL using Nunjucks with custom SQL-safe filters registered through RunQuerySQLFilterManager
• Rejects unknown parameters to prevent injection

Configuration Reference

SQLServerProviderConfigData

Connection Pool Settings

Configure via mj.config.cjs at the repository root:

module.exports = {
  databaseSettings: {
    connectionPool: {
      max: 50,                     // Maximum connections
      min: 5,                      // Minimum connections
      idleTimeoutMillis: 30000,    // Idle timeout in ms
      acquireTimeoutMillis: 30000, // Acquire timeout in ms
    },
  },
};
Copy

Recommended pool sizes:

SqlLoggingOptions

Build and Development

# Build the package
cd packages/SQLServerDataProvider && npm run build

# Run tests
cd packages/SQLServerDataProvider && npm test

# Run tests with coverage
cd packages/SQLServerDataProvider && npm run test:coverage

# Run tests in watch mode
cd packages/SQLServerDataProvider && npm run test:watch
Copy

Key Implementation Details

Connection Pool Best Practices

The provider follows SQL Server connection pool best practices:

1. Single shared pool -- one sql.ConnectionPool is created at server startup and reused for the application's lifetime
2. Fresh request per query -- each ExecuteSQL call creates a new sql.Request from the pool, enabling safe parallel execution
3. No pool close in handlers -- the pool remains open; the caller is responsible for closing it on shutdown
4. Configurable pool sizing -- pool max/min are tunable through mj.config.cjs

DateTime Offset Adjustment

The provider automatically detects whether the SQL Server + mssql driver combination produces incorrect DATETIMEOFFSET values. On first query, it runs a diagnostic test and caches the result. If adjustment is needed, all DATETIMEOFFSET fields are corrected during row processing.

Metadata Refresh

When checkRefreshIntervalSeconds > 0, the provider periodically checks whether database metadata has changed (new entities, field modifications, etc.) and reloads if needed. The RefreshIfNeeded() method can also be called on demand.

Multi-Instance Pattern

In server environments like MJAPI, a new SQLServerDataProvider instance is created per request. Setting ignoreExistingMetadata: false on subsequent instances allows them to reuse the metadata loaded by the first instance, avoiding redundant database queries.

Troubleshooting

IS-A Type Relationship Transaction Support

MemberJunction supports IS-A type relationships where child entities inherit from parent entities (e.g., MeetingEntity IS-A ProductEntity). The SQLServerDataProvider manages SQL transactions to ensure atomic save and delete operations across the entire entity hierarchy.

How IS-A Transactions Work

When you save or delete an entity that participates in an IS-A hierarchy, SQLServerDataProvider automatically:

1. Creates a SQL Transaction: The initiating (leaf) entity calls BeginISATransaction() to create a new sql.Transaction on the connection pool
2. Propagates the Transaction: The transaction is stored in BaseEntity.ProviderTransaction and shared across all entities in the parent chain
3. Executes Operations in Order:
   • For saves: Parent entities are saved first, then the child entity uses the parent's ID
   • For deletes: The child entity is deleted first, then parents are deleted in reverse order
4. Commits or Rolls Back: CommitISATransaction() commits all changes, or RollbackISATransaction() reverts everything on failure

Transaction Lifecycle

// Example: Saving a MeetingEntity (which IS-A ProductEntity)
const meeting = await md.GetEntityObject<MeetingEntity>('Meetings');
meeting.Name = 'Project Planning';
meeting.MeetingDate = new Date();
// ... set other fields

// When you call Save(), the provider automatically:
// 1. Begins a SQL transaction
// 2. Saves the Product parent entity first
// 3. Uses the Product ID to save the Meeting child entity
// 4. Commits the transaction
const result = await meeting.Save();

// If any step fails, the entire transaction is rolled back
Copy

Key Methods

• BeginISATransaction(): Creates a new sql.Transaction on the connection pool and stores it in BaseEntity.ProviderTransaction
• CommitISATransaction(): Commits the shared transaction across the entire IS-A chain
• RollbackISATransaction(): Rolls back all changes if any operation in the chain fails

Benefits

• Atomicity: All saves/deletes in the hierarchy succeed or fail together
• Consistency: No orphaned child records or missing parent data
• Transparent: The transaction management is automatic - no manual transaction handling required
• Shared State: All entities in the chain use the same sql.Transaction instance via BaseEntity.ProviderTransaction

For more details on IS-A relationships and how they work across MemberJunction, see MJCore IS-A Relationships Documentation.

Virtual Entity Support

MemberJunction supports virtual entities that are backed by SQL views instead of physical tables. Virtual entities provide read-only access to data and are commonly used for reporting, aggregations, and denormalized views.

Read Operations

Virtual entities work seamlessly with the SQLServerDataProvider for all read operations:

• RunView: Execute queries against the virtual entity's underlying SQL view
• Get: Load individual records by primary key (if the view supports it)
• Filtering, Sorting, Pagination: All standard query operations work as expected

// Example: Querying a virtual entity backed by a view
const rv = new RunView();
const result = await rv.RunView<UserSummaryEntity>({
    EntityName: 'User Summary',  // Virtual entity backed by vwUserSummary
    ExtraFilter: "Department = 'Engineering'",
    OrderBy: 'LastLoginDate DESC',
    ResultType: 'entity_object'
});

// result.Results contains fully-typed UserSummaryEntity objects
const users = result.Results;
Copy

Write Operations

Write operations (Save, Delete) are automatically blocked for virtual entities at the BaseEntity level before they reach the data provider:

• BaseEntity.Save(): Returns an error if called on a virtual entity
• BaseEntity.Delete(): Returns an error if called on a virtual entity
• Why: Virtual entities represent read-only views and cannot be modified directly

Use Cases for Virtual Entities

• Aggregated Data: Summary views that combine data from multiple tables
• Denormalized Views: Flattened representations of complex relationships
• Calculated Fields: Views that include computed columns or transformations
• Security Views: Row-level filtering applied at the database view level
• Reporting: Pre-joined data optimized for reporting queries

For comprehensive documentation on virtual entities, their configuration, and advanced usage patterns, see MJCore Virtual Entities Documentation.

License

ISC