data-table-mysql

data-table-mysql

MySQL adapter for remix/data-table. Use this package when you want data-table APIs backed by mysql2.

Features

• Native mysql2 Integration: Creates a pool from mysql2 configuration or uses an existing pool or connection
• Full data-table API Support: Queries, relations, writes, and transactions
• Adapter-Owned Compiler: SQL compilation lives in this adapter, with optional shared pure helpers from data-table
• Multi-Statement Migrations: executeScript() runs up.sql / down.sql files via mysql2 (requires multipleStatements: true)
• MySQL Capabilities Enabled By Default:
  • returning: false
  • savepoints: true
  • upsert: true
  • transactionalDdl: false
  • migrationLock: true

Installation

npm i remix mysql2

Usage

import { createDatabase } from 'remix/data-table'
import { createMysqlDatabaseAdapter } from 'remix/data-table/mysql'

let db = createDatabase(
  createMysqlDatabaseAdapter({
    uri: process.env.DATABASE_URL,
    multipleStatements: true,
  }),
)

Use db.query(...), relation loading, and transactions from remix/data-table. Import any driver-specific types you need directly from mysql2/promise.

Adapter Capabilities

data-table-mysql reports this capability set by default:

• returning: false
• savepoints: true
• upsert: true
• transactionalDdl: false
• migrationLock: true

Advanced Usage

Multi-Statement Migrations

remix/data-table/migrations sends each migration to the adapter as a single multi-statement SQL script. mysql2 only accepts multi-statement scripts when the connection is created with multipleStatements: true:

import { createMysqlDatabaseAdapter } from 'remix/data-table/mysql'

let adapter = createMysqlDatabaseAdapter({
  uri: process.env.DATABASE_URL,
  multipleStatements: true,
})

Config-backed adapters support db.wipe() and db.reset(). You may continue passing an existing mysql2 pool or connection when your application owns the driver lifecycle, but destructive lifecycle methods are unavailable in that mode. db.wipe() requires a database name in the connection config (database, or the path of a connection URI) and throws when none is present.

Migration runs reserve one connection for the MySQL named lock, migration SQL, and journal updates. Lock acquisition waits up to 60 seconds and fails with an error instead of allowing the migration to proceed. After a successful run the connection is unlocked and returned to the pool; if the migration or unlock fails, the reserved connection is destroyed instead of being reused, so a dirty session can never leak back into the pool. Calling withMigrationLock() from inside a migration callback throws instead of deadlocking.

returning On MySQL

MySQL does not natively support SQL RETURNING. In this adapter, using returning on write operations throws DataTableQueryError.

Use write metadata (affectedRows, insertId) on MySQL, or switch adapters when returned rows are required.

import { DataTableQueryError } from 'remix/data-table'

try {
  await db
    .query(Accounts)
    .insert({ email: 'a@example.com', status: 'active' }, { returning: ['id'] })
} catch (error) {
  if (error instanceof DataTableQueryError) {
    // insert() returning is not supported by this adapter
  }
}

Running integration tests locally

To start a local MySQL container matching CI:

podman run --name mysql \
  -e MYSQL_ROOT_PASSWORD=root \
  -e MYSQL_DATABASE=remix \
  -p 3306:3306 \
  -d mysql:8

Then run:

REMIX_DATA_TABLE_MYSQL_TEST_URL=mysql://root:root@127.0.0.1:3306/remix \
pnpm test src/lib/adapter.integration.test.ts

Remove the container when you are done:

podman rm -f mysql

Related Packages

• data-table - Core query/relations API
• data-schema - Schema parsing and validation
• data-table-postgres - PostgreSQL adapter
• data-table-sqlite - SQLite adapter

License

See LICENSE