Skip to main content

​
Overview

The MySQL persistence adapter provides distributed locks, circuit breaker state management, caching, and checkpoint support using MySQL or MariaDB as the backend. It uses application-level locks with TTL tracking and InnoDB storage engine for ACID guarantees.

​
Installation

npm install @go-go-scope/persistence-mysql mysql2

​
Features

  • Distributed Locks: Application-level locks with TTL and automatic expiration
  • Circuit Breaker State: Persistent failure tracking with JSON storage
  • Caching: Database-backed cache with TTL support
  • Checkpoints: Full checkpoint support for task recovery
  • Idempotency: Prevent duplicate operations (via MySQLIdempotencyAdapter)
  • Auto Schema: Automatic table creation with InnoDB engine
  • Transactional Safety: ACID guarantees for lock operations
  • MariaDB Compatible: Works with both MySQL and MariaDB

​
Basic Usage

import { createPool } from 'mysql2/promise'
import { MySQLAdapter } from '@go-go-scope/persistence-mysql'
import { scope } from 'go-go-scope'

const pool = createPool({
  uri: process.env.DATABASE_URL,
})

const persistence = new MySQLAdapter(pool, { keyPrefix: 'myapp:' })

// Initialize tables
await persistence.connect()

await using s = scope({ persistence })

​
Configuration

​
pool
Pool
required
mysql2 Pool instance for MySQL connections
​
options
PersistenceAdapterOptions
Configuration options

​
Database Schema

The adapter automatically creates the following tables on connect():

​
go_goscope_locks

CREATE TABLE IF NOT EXISTS go_goscope_locks (
  `key` VARCHAR(255) PRIMARY KEY,
  owner VARCHAR(255) NOT NULL,
  expires_at DATETIME NOT NULL,
  INDEX idx_expires (expires_at)
) ENGINE=InnoDB;

​
go_goscope_circuit

CREATE TABLE IF NOT EXISTS go_goscope_circuit (
  `key` VARCHAR(255) PRIMARY KEY,
  state VARCHAR(20) NOT NULL,
  failure_count INT NOT NULL DEFAULT 0,
  last_failure_time DATETIME,
  last_success_time DATETIME,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  INDEX idx_updated (updated_at)
) ENGINE=InnoDB;

​
go_goscope_cache

CREATE TABLE IF NOT EXISTS go_goscope_cache (
  `key` VARCHAR(255) PRIMARY KEY,
  value JSON NOT NULL,
  expires_at DATETIME,
  INDEX idx_expires (expires_at)
) ENGINE=InnoDB;

​
go_goscope_checkpoints

CREATE TABLE IF NOT EXISTS go_goscope_checkpoints (
  id VARCHAR(255) PRIMARY KEY,
  task_id VARCHAR(255) NOT NULL,
  sequence INT NOT NULL,
  timestamp BIGINT NOT NULL,
  progress INT NOT NULL DEFAULT 0,
  data JSON NOT NULL,
  estimated_time_remaining BIGINT,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  INDEX idx_checkpoints_task (task_id),
  INDEX idx_checkpoints_sequence (task_id, sequence)
) ENGINE=InnoDB;

​
Lock Provider Methods

​
acquire(key, ttl, owner?)

Acquires a distributed lock with automatic TTL-based expiration. Uses transactions with FOR UPDATE for strong consistency.
​
key
string
required
Lock identifier
​
ttl
number
required
Time-to-live in milliseconds
​
owner
string
Optional lock owner identifier (auto-generated if not provided)
Returns: Promise<LockHandle | null> - Lock handle or null if already locked

​
extend(key, ttl, owner)

Extends the TTL of an existing lock.
​
key
string
required
Lock identifier
​
ttl
number
required
New time-to-live in milliseconds
​
owner
string
required
Lock owner identifier
Returns: Promise<boolean> - True if extended successfully

​
forceRelease(key)

Forces release of a lock regardless of owner.
​
key
string
required
Lock identifier

​
Cache Provider Methods

​
get(key)

Retrieves a cached value using JSON storage.
​
key
string
required
Cache key
Returns: Promise<T | null> - Cached value or null if not found/expired

​
set(key, value, ttl?)

Stores a value in the cache using JSON.
​
key
string
required
Cache key
​
value
T
required
Value to cache (must be JSON-serializable)
​
ttl
number
Time-to-live in milliseconds (optional)

​
delete(key)

Removes a cached value.
​
key
string
required
Cache key

​
clear()

Clears all cached values (respects key prefix if set).

​
keys(pattern?)

Lists all cache keys matching an optional pattern.
​
pattern
string
Optional pattern with wildcards (e.g., 'user:*')
Returns: Promise<string[]> - Array of matching keys

​
Checkpoint Provider Methods

​
save(checkpoint)

Saves a checkpoint for task recovery.
​
checkpoint
Checkpoint<T>
required
Checkpoint data including taskId, sequence, progress, and data

​
load(checkpointId)

Loads a specific checkpoint by ID.
​
checkpointId
string
required
Checkpoint identifier
Returns: Promise<Checkpoint<T> | undefined>

​
loadLatest(taskId)

Loads the most recent checkpoint for a task.
​
taskId
string
required
Task identifier
Returns: Promise<Checkpoint<T> | undefined>

​
list(taskId)

Lists all checkpoints for a task.
​
taskId
string
required
Task identifier
Returns: Promise<Checkpoint<unknown>[]>

​
cleanup(taskId, keepCount)

Deletes old checkpoints, keeping only the most recent N.
​
taskId
string
required
Task identifier
​
keepCount
number
required
Number of checkpoints to keep

​
deleteAll(taskId)

Deletes all checkpoints for a task.
​
taskId
string
required
Task identifier

​
Connection Example

import { createPool } from 'mysql2/promise'
import { MySQLAdapter } from '@go-go-scope/persistence-mysql'

const pool = createPool({
  host: process.env.MYSQL_HOST || 'localhost',
  port: parseInt(process.env.MYSQL_PORT || '3306'),
  database: process.env.MYSQL_DATABASE || 'myapp',
  user: process.env.MYSQL_USER,
  password: process.env.MYSQL_PASSWORD,
  waitForConnections: true,
  connectionLimit: 10,
  queueLimit: 0,
})

const persistence = new MySQLAdapter(pool, {
  keyPrefix: 'myapp:',
})

// Initialize tables
await persistence.connect()

// Use with scope
await using s = scope({ persistence })

// Cleanup
await persistence.disconnect()

​
Best Practices

Call connect() once at application startup to create tables. The adapter uses IF NOT EXISTS so it’s safe to call multiple times.
The adapter uses client-side time for lock expiration to avoid clock skew issues. Ensure servers are synchronized via NTP.
Lock acquisition uses FOR UPDATE which provides strong consistency. The adapter automatically cleans up expired locks on each acquire() call.

​
MariaDB Compatibility

The adapter is fully compatible with MariaDB. The schema uses standard SQL features supported by both MySQL 8.0+ and MariaDB 10.5+. 
import { createPool } from 'mysql2/promise'
import { MySQLAdapter } from '@go-go-scope/persistence-mysql'

const pool = createPool({
  host: 'mariadb.example.com',
  user: 'myapp',
  password: process.env.MARIADB_PASSWORD,
  database: 'myapp',
})

const persistence = new MySQLAdapter(pool)
await persistence.connect()

​
Performance Considerations

  • Lock operations use transactions with row-level locking
  • InnoDB engine provides ACID guarantees
  • JSON columns require MySQL 5.7+ or MariaDB 10.2+
  • Connection pooling is handled by the mysql2 Pool
  • Indexes on expires_at optimize cleanup queries

PostgreSQL Adapter

PostgreSQL-based persistence

SQLite Adapter

SQLite-based persistence

Build docs developers (and LLMs) love

Get started for freeTalk to us