@lti-tool/mysql

Production-ready MySQL storage adapter for LTI 1.3. Includes caching and optimized for AWS Lambda.

Installation

npm install @lti-tool/mysql
Copy

Quick Start

import { MySqlStorage } from '@lti-tool/mysql';
import { LTITool } from '@lti-tool/core';

const storage = new MySqlStorage({
  connectionUrl: process.env.DATABASE_URL!,
});

const ltiTool = new LTITool({
  storage,
  // ... other config
});
Copy

Features

• Production Ready - Handles high-scale LTI deployments
• Built-in Caching - LRU cache for frequently accessed data
• Type-safe - Uses Drizzle ORM for database operations
• Transaction Support - Handles data integrity on deletes
• Tuned Connection Pool Defaults - Connection pool defaults based on hosting environment

Configuration

Using Drizzle Kit Push (Recommended for Development)

# Set your DATABASE_URL
export DATABASE_URL="mysql://user:password@host:port/database"

# Push schema to database
npx drizzle-kit push
Copy

Using Migrations (Recommended for Production)

# Apply migrations
npx drizzle-kit migrate
Copy

MySqlStorageConfig

• connectionUrl (required): MySQL connection URL Format: mysql://user:password@host:port/database

• poolOptions (optional): mysql2 pool configuration

  • connectionLimit: Max connections (auto: 1 for serverless, 10 for servers)
  • queueLimit: Max queued requests (default: 0 = unlimited)

• nonceExpirationSeconds (optional): Nonce TTL in seconds (default: 600)

• logger (optional): Pino logger for debugging

Database Schema

The adapter uses these tables:

• clients: LTI platform clients Unique constraint: (iss, clientId)
• deployments: Platform deployments (many-to-one with clients) Unique constraint: (clientId, deploymentId)
• sessions: LTI sessions with expiration Indexed: expiresAt
• nonces: One-time use nonces Primary key: nonce Indexed: expiresAt
• registrationSessions: Dynamic registration sessions Indexed: expiresAt

All tables use UUIDs for primary keys and include indexes for performance.

clients

Indexes:

• issuer_client_idx: (clientId, iss) - For fast client lookups
• iss_client_id_unique: (iss, clientId) - Unique constraint preventing duplicate clients

deployments

Indexes:

• deployment_id_idx: (deploymentId) - For fast deployment lookups
• client_deployment_unique: (clientId, deploymentId) - Unique constraint per client

sessions

Indexes:

• expires_at_idx: (expiresAt) - For cleanup queries and expiration checks

nonces

registrationSessions

Indexes:

• expires_at_idx: (expiresAt) - For cleanup queries and expiration checks

Connection Pool Behavior

The adapter automatically detects your deployment environment:

• Serverless (Lambda, Cloud Functions, Vercel, Netlify): connectionLimit: 1
• Traditional Servers (EC2, containers, VMs): connectionLimit: 10

How Connection Pooling Works

• Starts with 0 connections
• Creates connections on-demand when queries execute
• Reuses idle connections before creating new ones
• Increases to connectionLimit based on concurrent load
• Keeps connections alive for reuse (no reconnection overhead)

Manual Override

const storage = new MySqlStorage({
  connectionUrl: process.env.DATABASE_URL!,
  poolOptions: {
    connectionLimit: 20, // Override auto-detection
  },
});
Copy

Deployment Patterns

Long-Running Servers

import { MySqlStorage } from '@lti-tool/mysql';

export const storage = new MySqlStorage({
  connectionUrl: process.env.DATABASE_URL!,
});

// Optional: Graceful shutdown
const shutdown = async () => {
  await storage.close();
  process.exit(0);
};

process.on('SIGTERM', shutdown);
process.on('SIGINT', shutdown);
Copy

Connection Limits:

• Low traffic: 5-10 connections
• Medium traffic: 10-20 connections
• High traffic: 20-50 connections
• Never exceed MySQL max_connections

AWS Lambda / Serverless

import { MySqlStorage } from '@lti-tool/mysql';

let storage: MySqlStorage | undefined;

export const handler = async (event) => {
  if (!storage) {
    storage = new MySqlStorage({
      connectionUrl: process.env.DATABASE_URL!,
      // Auto-detects Lambda, uses connectionLimit: 1
    });
  }

  // Use storage...
};
Copy

Why connectionLimit: 1? Lambda containers handle one request at a time. The connection is reused across warm invocations.

Do I need close()? No! Lambda freezes containers efficiently. Calling close() destroys reusable connections.

Edge Runtime Warning

⚠️ Not supported!

Periodic Cleanup

The adapter requires periodic cleanup of expired nonces and sessions.

// Example - AWS Lambda with EventBridge (every 30 minutes)
export const handler = async () => {
  const result = await storage.cleanup();
  console.log('Cleanup:', result);
  // { noncesDeleted: 42, sessionsDeleted: 15, registrationSessionsDeleted: 3 }
};
Copy

Development & Testing

Start Local MySQL

# Using Docker
docker-compose up -d

# Using Podman
podman-compose up -d

# Or Podman directly
podman run -d \
  --name lti-mysql \
  -e MYSQL_ROOT_PASSWORD=root \
  -e MYSQL_DATABASE=lti_test \
  -e MYSQL_USER=lti_user \
  -e MYSQL_PASSWORD=lti_password \
  -p 3306:3306 \
  mysql:8.0
Copy

Run Tests

DATABASE_URL="mysql://lti_user:lti_password@localhost:3306/lti_test" npm test
Copy

Important: Always close the pool(s) after tests:

afterAll(async () => {
  // close the drizzle pool
  await storage.close();

  // close the vitest pool
  await pool.end();
});
Copy

Environment Detection

Auto-detects serverless by checking:

• AWS Lambda: AWS_LAMBDA_FUNCTION_NAME, AWS_EXECUTION_ENV
• Google Cloud: FUNCTION_NAME, K_SERVICE
• Azure: FUNCTIONS_WORKER_RUNTIME
• Vercel: VERCEL
• Netlify: NETLIFY