MySQL storage for LTI 1.3 @lti-tool
npm install @lti-tool/mysqlProduction-ready MySQL storage adapter for LTI 1.3. Includes caching and optimized for AWS Lambda.
``bash`
npm install @lti-tool/mysql
`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
});
`
- 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 - Complete API documentation
`bashSet your DATABASE_URL
export DATABASE_URL="mysql://user:password@host:port/database"
$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: expiresAtAll 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 checksConnection 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 -dUsing Podman
podman-compose up -dOr 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`