@lti-tool/mysql

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

Installation

``bash
npm install @lti-tool/mysql
`

Quick Start

`typescript
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
});
`

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

API Reference

- API Reference - Complete API documentation

Configuration

$3

`bash


Set your DATABASE_URL

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

Push schema to database

npx drizzle-kit push
`

$3

`bash


Apply migrations


npx drizzle-kit migrate
`

$3

- 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.

$3

| Column | Type | Constraints | Description |
| ---------- | ------------ | --------------------- | ------------------------------ |
| id | VARCHAR(36) | PRIMARY KEY, NOT NULL | Internal UUID for the client |
| name | VARCHAR(255) | NOT NULL | Human-readable platform name |
| iss | VARCHAR(255) | NOT NULL | Issuer URL (LMS platform) |
| clientId | VARCHAR(255) | NOT NULL | LMS-provided client identifier |
| authUrl | TEXT | NOT NULL | OAuth2 authorization endpoint |
| tokenUrl | TEXT | NOT NULL | OAuth2 token endpoint |
| jwksUrl | TEXT | NOT NULL | JWKS endpoint for public keys |

Indexes:

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

$3

| Column | Type | Constraints | Description |
| -------------- | ------------ | --------------------- | ---------------------------------- |
| id | VARCHAR(36) | PRIMARY KEY, NOT NULL | Internal UUID for the deployment |
| deploymentId | VARCHAR(255) | NOT NULL | LMS-provided deployment identifier |
| name | VARCHAR(255) | NULL | Optional human-readable name |
| description | TEXT | NULL | Optional description |
| clientId | VARCHAR(36) | NOT NULL, FOREIGN KEY | References clients.id |

Indexes:

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

$3

| Column | Type | Constraints | Description |
| ----------- | ----------- | --------------------- | ---------------------------- |
| id | VARCHAR(36) | PRIMARY KEY, NOT NULL | Session UUID |
| data | JSON | NOT NULL | Complete LTI session data |
| expiresAt | DATETIME | NOT NULL | Session expiration timestamp |

Indexes:

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

$3

| Column | Type | Constraints | Description |
| ----------- | ------------ | --------------------- | -------------------------- |
| nonce | VARCHAR(255) | PRIMARY KEY, NOT NULL | One-time use nonce value |
| expiresAt | DATETIME | NOT NULL | Nonce expiration timestamp |

$3

| Column | Type | Constraints | Description |
| ----------- | ----------- | --------------------- | --------------------------------- |
| id | VARCHAR(36) | PRIMARY KEY, NOT NULL | Registration session UUID |
| data | JSON | NOT NULL | Dynamic registration session data |
| expiresAt | DATETIME | NOT NULL | Session expiration timestamp |

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

$3

- 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)

$3

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

Deployment Patterns

$3

`typescript
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);
`

Connection Limits:

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

$3

`typescript
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...
};
`

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.

$3

⚠️ Not supported!

Periodic Cleanup

The adapter requires periodic cleanup of expired nonces and sessions.

`typescript
// 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 }
};
`

Development & Testing

$3

`bash


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
`

$3

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

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

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

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

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`