@memberjunction/sqlserver-dataprovider v2.48.0

MemberJunction SQL Server Data Provider

A robust SQL Server data provider implementation for MemberJunction applications, providing seamless database connectivity, query execution, and entity management.

Overview

The @memberjunction/sqlserver-dataprovider package implements MemberJunction's data provider interface specifically for Microsoft SQL Server databases. It serves as the bridge between your MemberJunction application and SQL Server, handling data access, entity operations, view execution, and more.

Key Features

• Full CRUD Operations: Complete Create, Read, Update, Delete operations for all entities
• Transaction Support: Manage atomic operations with transaction groups
• View Execution: Run database views with filtering, sorting, and pagination
• Report Generation: Execute reports with parameters
• Query Execution: Run raw SQL queries with parameter support
• Connection Pooling: Efficient database connection management
• Entity Relationships: Handle complex entity relationships automatically
• User/Role Management: Integrated with MemberJunction's security model
• Type-Safe Operations: Fully TypeScript compatible
• AI Integration: Support for AI-powered features through entity actions
• Duplicate Detection: Built-in support for duplicate record detection
• Audit Logging: Comprehensive audit trail capabilities
• Row-Level Security: Enforce data access controls at the database level

Installation

npm install @memberjunction/sqlserver-dataprovider

Dependencies

This package relies on the following key dependencies:

• @memberjunction/core: Core MemberJunction functionality
• @memberjunction/core-entities: Entity definitions
• @memberjunction/global: Shared utilities and constants
• @memberjunction/actions: Action execution framework
• @memberjunction/ai: AI integration capabilities
• @memberjunction/ai-vector-dupe: Duplicate detection using AI vectors
• @memberjunction/aiengine: AI engine integration
• @memberjunction/queue: Queue management for async operations
• mssql: SQL Server client for Node.js (v11+)
• typeorm: ORM for database operations (v0.3+)

Usage

Basic Setup

import { SQLServerDataProvider } from '@memberjunction/sqlserver-dataprovider';
import { ConfigHelper } from '@memberjunction/global';

// Configure database connection
const config = {
  host: 'your-server.database.windows.net',
  port: 1433,
  database: 'YourMJDatabase',
  user: 'your-username',
  password: 'your-password',
  options: {
    encrypt: true,
    trustServerCertificate: false
  }
};

// Create data provider instance
const dataProvider = new SQLServerDataProvider(config);

// Or using environment variables
const dataProvider = new SQLServerDataProvider({
  host: ConfigHelper.getConfigValue('MJ_HOST'),
  port: ConfigHelper.getConfigValue('MJ_PORT', 1433),
  database: ConfigHelper.getConfigValue('MJ_DATABASE'),
  user: ConfigHelper.getConfigValue('MJ_USER'),
  password: ConfigHelper.getConfigValue('MJ_PASSWORD')
});

// Initialize the data provider (connects to the database)
await dataProvider.initialize();

Working with Entities

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

// Setup data provider
const dataProvider = new SQLServerDataProvider(/* config */);
await dataProvider.initialize();

// Get entity metadata
const md = new Metadata();
const userEntity = md.EntityByName('User');

// Load an entity by ID
const userKey = new CompositeKey([{ FieldName: 'ID', Value: 1 }]);
const userResult = await dataProvider.Get(userEntity, userKey);

if (userResult.Success) {
  const user = userResult.Entity;
  console.log(`Loaded user: ${user.FirstName} ${user.LastName}`);
  
  // Update the entity
  user.Email = 'new.email@example.com';
  const saveResult = await dataProvider.Save(user, contextUser);
  
  if (saveResult.Success) {
    console.log(`User updated successfully, ID: ${saveResult.Entity.ID}`);
  }
}

// Create a new entity
const newUserEntity = await md.GetEntityObject<UserEntity>('User');
newUserEntity.FirstName = 'John';
newUserEntity.LastName = 'Doe';
newUserEntity.Email = 'john.doe@example.com';
// set other required fields...

const createResult = await dataProvider.Save(newUserEntity, contextUser);
if (createResult.Success) {
  console.log(`New user created with ID: ${createResult.Entity.ID}`);
}

// Delete an entity
const deleteKey = new CompositeKey([{ FieldName: 'ID', Value: 5 }]);
const deleteResult = await dataProvider.Delete(userEntity, deleteKey, contextUser);
if (deleteResult.Success) {
  console.log('User deleted successfully');
}

Transaction Management

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

// Setup data provider
const dataProvider = new SQLServerDataProvider(/* config */);
await dataProvider.initialize();

// Create a transaction group
const transaction = new SQLServerTransactionGroup('CreateOrderWithItems');

// Get entity objects
const md = new Metadata();
const orderEntity = await md.GetEntityObject('Order');
const orderItemEntity1 = await md.GetEntityObject('Order Item');
const orderItemEntity2 = await md.GetEntityObject('Order Item');

// Set up the order
orderEntity.CustomerID = 123;
orderEntity.OrderDate = new Date();
orderEntity.Status = 'New';

// Add to transaction - this will get ID after save
await transaction.AddTransaction(orderEntity);

// Set up order items with references to the order
orderItemEntity1.OrderID = '@Order.1'; // Reference to the first Order in this transaction
orderItemEntity1.ProductID = 456;
orderItemEntity1.Quantity = 2;
orderItemEntity1.Price = 29.99;

orderItemEntity2.OrderID = '@Order.1'; // Same order reference
orderItemEntity2.ProductID = 789;
orderItemEntity2.Quantity = 1;
orderItemEntity2.Price = 49.99;

// Add items to transaction
await transaction.AddTransaction(orderItemEntity1);
await transaction.AddTransaction(orderItemEntity2);

// Execute the transaction group
const results = await transaction.Submit();

// Check results
const success = results.every(r => r.Success);
if (success) {
  console.log('Transaction completed successfully');
  const orderResult = results.find(r => r.Entity.EntityInfo.Name === 'Order');
  console.log('Order ID:', orderResult?.Entity.ID);
} else {
  console.error('Transaction failed');
  results.filter(r => !r.Success).forEach(r => {
    console.error(`Failed: ${r.Entity.EntityInfo.Name}`, r.Message);
  });
}

Running Views and Reports

import { SQLServerDataProvider } from '@memberjunction/sqlserver-dataprovider';
import { RunViewParams, RunReportParams } from '@memberjunction/core';

// Setup data provider
const dataProvider = new SQLServerDataProvider(/* config */);
await dataProvider.initialize();

// Run a view with filtering and pagination
const viewOptions: RunViewParams = {
  EntityName: 'vwActiveUsers',
  ExtraFilter: "Role = 'Administrator'",
  OrderBy: 'LastName, FirstName',
  PageSize: 10,
  PageNumber: 1
};

const viewResult = await dataProvider.RunView(viewOptions);

if (viewResult.success) {
  console.log(`Found ${viewResult.Results.length} users`);
  console.log(`Total matching records: ${viewResult.TotalRowCount}`);
  
  viewResult.Results.forEach(user => {
    console.log(`${user.FirstName} ${user.LastName} (${user.Email})`);
  });
}

// Run a report
const reportParams: RunReportParams = {
  ReportID: 'report-id-here',
  // Other parameters as needed
};

const reportResult = await dataProvider.RunReport(reportParams);

if (reportResult.Success) {
  console.log('Report data:', reportResult.Results);
  console.log('Row count:', reportResult.RowCount);
  console.log('Execution time:', reportResult.ExecutionTime, 'ms');
}

Executing Raw Queries

import { SQLServerDataProvider } from '@memberjunction/sqlserver-dataprovider';
import { RunQueryParams } from '@memberjunction/core';

// Setup data provider
const dataProvider = new SQLServerDataProvider(/* config */);
await dataProvider.initialize();

// Execute raw SQL with parameters
const sqlResult = await dataProvider.ExecuteSQL(
  'SELECT * FROM Users WHERE Department = @dept AND HireDate > @date',
  {
    dept: 'Engineering',
    date: '2022-01-01'
  }
);

console.log(`Query returned ${sqlResult.length} rows`);
sqlResult.forEach(row => {
  console.log(row);
});

// Execute a stored procedure
const spResult = await dataProvider.ExecuteSQL(
  'EXEC sp_GetUserPermissions @UserID',
  {
    UserID: 123
  }
);

console.log('User permissions:', spResult);

// Using RunQuery for pre-defined queries
const queryParams: RunQueryParams = {
  QueryID: 'query-id-here', // or use QueryName
  // CategoryID: 'optional-category-id',
  // CategoryName: 'optional-category-name'
};

const queryResult = await dataProvider.RunQuery(queryParams);

if (queryResult.Success) {
  console.log('Query results:', queryResult.Results);
  console.log('Execution time:', queryResult.ExecutionTime, 'ms');
}

User Management and Caching

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

// Setup data provider
const dataProvider = new SQLServerDataProvider(/* config */);
await dataProvider.initialize();

// Set current user context
dataProvider.setCurrentUser(123); // User ID

// Get current user
const currentUser = dataProvider.getCurrentUser();
console.log(`Current user: ${currentUser.FirstName} ${currentUser.LastName}`);

// User caching is handled automatically by the provider
// but you can clear the cache if needed
dataProvider.clearUserCache();

Configuration Options

The SQL Server data provider accepts the following configuration options:

Advanced Usage

Custom SQL Execution Hooks

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

class CustomSQLProvider extends SQLServerDataProvider {
  // Override to add custom logging or modifications
  async ExecuteSQL(sql: string, params?: any, maxRows?: number): Promise<any> {
    console.log(`Executing SQL: ${sql}`);
    console.log('Parameters:', params);
    
    // Add timing
    const startTime = Date.now();
    const result = await super.ExecuteSQL(sql, params, maxRows);
    const duration = Date.now() - startTime;
    
    console.log(`Query executed in ${duration}ms`);
    console.log(`Rows returned: ${result?.length || 0}`);
    
    return result;
  }
  
  // Custom error handling
  protected async HandleExecuteSQLError(error: any, sql: string): Promise<void> {
    console.error('SQL Error:', error);
    console.error('Failed SQL:', sql);
    // Add custom error handling logic here
    await super.HandleExecuteSQLError(error, sql);
  }
}

Error Handling

The SQL Server Data Provider includes comprehensive error handling:

try {
  const result = await dataProvider.Save(entity, user);
  if (!result.Success) {
    console.error('Save failed:', result.ErrorMessage);
    // Handle validation or business logic errors
  }
} catch (error) {
  console.error('Unexpected error:', error);
  // Handle system-level errors
}

Build & Development

Building the Package

# From the package directory
npm run build

# Or from the repository root
turbo build --filter="@memberjunction/sqlserver-dataprovider"

Development Scripts

• npm run build - Compile TypeScript to JavaScript
• npm run start - Run the package with ts-node-dev for development

TypeScript Configuration

This package is configured with TypeScript strict mode enabled. The compiled output is placed in the dist/ directory with declaration files for type support.

API Reference

SQLServerDataProvider

The main class that implements IEntityDataProvider, IMetadataProvider, IRunViewProvider, IRunReportProvider, and IRunQueryProvider interfaces.

Key Methods

• Config(configData: SQLServerProviderConfigData): Promise<boolean> - Configure the provider with connection details
• Get(entity: EntityInfo, CompositeKey: CompositeKey, user?: UserInfo): Promise<BaseEntityResult> - Load an entity by primary key
• Save(entity: BaseEntity, user: UserInfo, options?: EntitySaveOptions): Promise<BaseEntityResult> - Save (create/update) an entity
• Delete(entity: EntityInfo, CompositeKey: CompositeKey, user?: UserInfo, options?: EntityDeleteOptions): Promise<BaseEntityResult> - Delete an entity
• RunView(params: RunViewParams, contextUser?: UserInfo): Promise<RunViewResult> - Execute a database view
• RunReport(params: RunReportParams, contextUser?: UserInfo): Promise<RunReportResult> - Execute a report
• RunQuery(params: RunQueryParams, contextUser?: UserInfo): Promise<RunQueryResult> - Execute a query
• ExecuteSQL(sql: string, params?: any, maxRows?: number): Promise<any[]> - Execute raw SQL

SQLServerProviderConfigData

Configuration class for the SQL Server provider.

Properties

• DataSource: DataSource - TypeORM DataSource instance
• CurrentUserEmail: string - Email of the current user
• CheckRefreshIntervalSeconds: number - Interval for checking metadata refresh (0 to disable)
• MJCoreSchemaName: string - Schema name for MJ core tables (default: '__mj')
• IncludeSchemas?: string[] - List of schemas to include
• ExcludeSchemas?: string[] - List of schemas to exclude

SQLServerTransactionGroup

SQL Server implementation of TransactionGroupBase for managing database transactions.

Methods

• HandleSubmit(): Promise<TransactionResult[]> - Execute all pending transactions in the group

UserCache

Server-side cache for user and role information.

Static Methods

• Instance: UserCache - Get singleton instance
• Users: UserInfo[] - Get all cached users

Instance Methods

• Refresh(dataSource: DataSource, autoRefreshIntervalMS?: number): Promise<void> - Refresh user cache
• UserByName(name: string, caseSensitive?: boolean): UserInfo | undefined - Find user by name

setupSQLServerClient

Helper function to initialize and configure the SQL Server data provider.

setupSQLServerClient(config: SQLServerProviderConfigData): Promise<SQLServerDataProvider>

Troubleshooting

Common Issues

1. Connection Timeout Errors

   • Increase connectionTimeout and requestTimeout in configuration
   • Verify network connectivity to SQL Server
   • Check SQL Server firewall rules

2. Authentication Failures

   • Ensure correct username/password or Windows authentication
   • Verify user has appropriate database permissions
   • Check if encryption settings match server requirements

3. Schema Not Found

   • Verify MJCoreSchemaName matches your database schema (default: __mj)
   • Ensure user has access to the schema
   • Check if MemberJunction tables are properly installed

4. Transaction Rollback Issues

   • Check for constraint violations in related entities
   • Verify all required fields are populated
   • Review transaction logs for specific error details

5. Performance Issues

   • Adjust connection pool settings (pool.max, pool.min)
   • Enable query logging to identify slow queries
   • Consider adding database indexes for frequently queried fields

Debug Logging

Enable detailed logging by setting environment variables:

# Enable SQL query logging
export MJ_LOG_SQL=true

# Enable detailed error logging
export MJ_LOG_LEVEL=debug

License

ISC