data-table-mysql

Overview

data-table-mysql provides a MySQL-specific adapter for data-table. It supports the core data-table API with MySQL-optimized features including upserts via ON DUPLICATE KEY UPDATE and savepoints for nested transactions. Key Features:

Native upsert (ON DUPLICATE KEY UPDATE) support
Savepoint support for nested transactions
Connection pooling support
Compatible with mysql2 package
Table-level locking for migrations

Limitations:

No RETURNING clause support (MySQL doesn’t support RETURNING)
DDL statements cannot be run in transactions
Migration locks use table-level locks instead of advisory locks

Installation

npm i remix
npm i mysql2

API Reference

createMysqlDatabaseAdapter

Creates a MySQL database adapter.

client

MysqlDatabasePool

required

MySQL client or pool instance from the mysql2 package.

options.capabilities

AdapterCapabilityOverrides

Optional capability overrides.

options.capabilities.returning

boolean

Enable/disable RETURNING clause support. Defaults to false (not supported in MySQL).

options.capabilities.savepoints

boolean

Enable/disable savepoint support. Defaults to true.

options.capabilities.upsert

boolean

Enable/disable upsert support. Defaults to true.

options.capabilities.transactionalDdl

boolean

Enable/disable transactional DDL. Defaults to false (not supported in MySQL).

options.capabilities.migrationLock

boolean

Enable/disable table-level lock for migrations. Defaults to true.

adapter

MysqlDatabaseAdapter

A MySQL database adapter instance.

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

let pool = createPool({
  host: 'localhost',
  user: 'root',
  password: 'password',
  database: 'mydb',
})

let adapter = createMysqlDatabaseAdapter(pool)
let db = createDatabase(adapter)

MysqlDatabaseAdapter

MySQL adapter class implementing the DatabaseAdapter interface.

class MysqlDatabaseAdapter implements DatabaseAdapter {
  dialect: 'mysql'
  capabilities: AdapterCapabilities
  
  compileSql(
    operation: DataManipulationOperation | DataMigrationOperation
  ): SqlStatement[]
  
  execute(
    request: DataManipulationRequest
  ): Promise<DataManipulationResult>
  
  migrate(
    request: DataMigrationRequest
  ): Promise<DataMigrationResult>
  
  hasTable(
    table: TableRef,
    transaction?: TransactionToken
  ): Promise<boolean>
  
  hasColumn(
    table: TableRef,
    column: string,
    transaction?: TransactionToken
  ): Promise<boolean>
  
  beginTransaction(
    options?: TransactionOptions
  ): Promise<TransactionToken>
  
  commitTransaction(
    token: TransactionToken
  ): Promise<void>
  
  rollbackTransaction(
    token: TransactionToken
  ): Promise<void>
  
  createSavepoint(
    token: TransactionToken,
    name: string
  ): Promise<void>
  
  rollbackToSavepoint(
    token: TransactionToken,
    name: string
  ): Promise<void>
  
  releaseSavepoint(
    token: TransactionToken,
    name: string
  ): Promise<void>
}

Type Definitions

MysqlDatabaseConnection

Single MySQL connection contract.

type MysqlDatabaseConnection = {
  query(text: string, values?: unknown[]): Promise<MysqlQueryResponse>
  beginTransaction(): Promise<void>
  commit(): Promise<void>
  rollback(): Promise<void>
  release?: () => void
}

MysqlDatabasePool

MySQL pool contract.

type MysqlDatabasePool = {
  query(text: string, values?: unknown[]): Promise<MysqlQueryResponse>
  getConnection(): Promise<MysqlDatabaseConnection>
}

MysqlQueryResponse

Supported MySQL query() response tuple.

type MysqlQueryResponse = [result: unknown, fields?: unknown]

MysqlQueryRows

Row-array response shape for MySQL query calls.

type MysqlQueryRows = Record<string, unknown>[]

MysqlQueryResultHeader

Metadata shape for MySQL write results.

type MysqlQueryResultHeader = {
  affectedRows: number
  insertId: unknown
}

MysqlDatabaseAdapterOptions

MySQL adapter configuration.

type MysqlDatabaseAdapterOptions = {
  capabilities?: AdapterCapabilityOverrides
}

Capabilities

The MySQL adapter has the following default capabilities:

Capability	Default	Description
`returning`	`false`	MySQL does not support RETURNING clauses
`savepoints`	`true`	Support for nested transactions via savepoints
`upsert`	`true`	Support for ON DUPLICATE KEY UPDATE
`transactionalDdl`	`false`	DDL statements cannot be run in transactions
`migrationLock`	`true`	Table-level locks for migration coordination

Usage Examples

Basic Setup

import { createPool } from 'mysql2/promise'
import { column as c, createDatabase, table } from 'remix/data-table'
import { createMysqlDatabaseAdapter } from 'remix/data-table-mysql'

// Define tables
let users = table({
  name: 'users',
  columns: {
    id: c.integer().autoIncrement().primaryKey(),
    email: c.varchar(255).notNull().unique(),
    name: c.varchar(255).notNull(),
    created_at: c.timestamp().defaultNow(),
  },
})

// Create database
let pool = createPool({
  host: 'localhost',
  user: 'root',
  database: 'mydb',
  waitForConnections: true,
  connectionLimit: 10,
})

let db = createDatabase(createMysqlDatabaseAdapter(pool))

// Use the database
let result = await db.create(users, {
  email: '[email protected]',
  name: 'Ada Lovelace',
})

console.log('Inserted user ID:', result.insertId)

Working Without RETURNING

MySQL doesn’t support RETURNING, so you need to query after writes if you need the full row.

// Insert and get insertId
let result = await db.create(users, {
  email: '[email protected]',
  name: 'Grace Hopper',
})

// Query back the created row
let user = await db.find(users, result.insertId)

// Or use auto-increment ID directly
let userId = result.insertId as number

createMany with returnRows

The returnRows option is not supported in MySQL due to lack of RETURNING support.

// Good: Get metadata only
let result = await db.createMany(users, [
  { email: '[email protected]', name: 'User 1' },
  { email: '[email protected]', name: 'User 2' },
])

console.log('Inserted rows:', result.affectedRows)
console.log('First insert ID:', result.insertId)

// Bad: Trying to use returnRows throws
try {
  await db.createMany(users, [...], { returnRows: true })
} catch (error) {
  // Error: createMany({ returnRows: true }) is not supported by this adapter
}

Transactions and Savepoints

MySQL supports nested transactions via savepoints.

// Simple transaction
await db.transaction(async (tx) => {
  await tx.create(users, { email: '[email protected]', name: 'Test User' })
  await tx.update(users, userId, { name: 'Updated Name' })
})

// Nested transactions with savepoints
await db.transaction(async (tx) => {
  await tx.create(users, { email: '[email protected]', name: 'Outer' })
  
  // Inner transaction uses a savepoint
  await tx.transaction(async (inner) => {
    await inner.create(users, { email: '[email protected]', name: 'Inner' })
    // If this throws, only the inner transaction is rolled back
  })
})

Upserts with ON DUPLICATE KEY UPDATE

MySQL’s ON DUPLICATE KEY UPDATE is exposed via the upsert method.

// Upsert by primary key
let result = await db.query(users).upsert(
  {
    id: 1,
    email: '[email protected]',
    name: 'Updated Name',
  },
  {
    update: { name: 'Updated Name' },
  }
)

// Upsert by unique key
await db.query(users).upsert(
  {
    email: '[email protected]',
    name: 'New Name',
  },
  {
    update: { name: 'New Name' },
  }
)

// Note: RETURNING is not available in MySQL
try {
  await db.query(users).upsert({ ... }, { returning: '*' })
} catch (error) {
  // Error: upsert with returning is not supported by this adapter
}

Transaction Isolation Levels

MySQL supports transaction isolation levels.

import type { TransactionOptions } from 'remix/data-table'

// Custom isolation level
await db.transaction(async (tx) => {
  await tx.create(users, { email: '[email protected]', name: 'Test' })
}, {
  isolationLevel: 'read committed',
})

// Available isolation levels:
// - 'read uncommitted'
// - 'read committed'
// - 'repeatable read' (default)
// - 'serializable'

Auto-Increment Columns

MySQL uses AUTO_INCREMENT for identity columns.

let users = table({
  name: 'users',
  columns: {
    id: c.integer().autoIncrement().primaryKey(),
    email: c.varchar(255),
  },
})

// Insert without specifying id
let result = await db.create(users, {
  email: '[email protected]',
})

console.log('Generated ID:', result.insertId)

Working with UNSIGNED Integers

MySQL’s UNSIGNED modifier is supported.

let products = table({
  name: 'products',
  columns: {
    id: c.integer().unsigned().autoIncrement().primaryKey(),
    price: c.decimal(10, 2).unsigned(),
    quantity: c.integer().unsigned().default(0),
  },
})

Connection Pooling

The adapter works with mysql2’s connection pooling.

import { createPool } from 'mysql2/promise'

let pool = createPool({
  host: 'localhost',
  user: 'root',
  password: 'password',
  database: 'mydb',
  waitForConnections: true,
  connectionLimit: 10,
  queueLimit: 0,
})

let db = createDatabase(createMysqlDatabaseAdapter(pool))

// Pool automatically manages connections
let users = await db.findMany(users)

// Transactions automatically acquire and release connections
await db.transaction(async (tx) => {
  await tx.create(users, { email: '[email protected]', name: 'Test' })
})

// Clean up pool when done
await pool.end()

MySQL-Specific Features

Charset and Collation

MySQL column-level charset and collation are supported.

let posts = table({
  name: 'posts',
  columns: {
    id: c.integer(),
    title: c.varchar(255).charset('utf8mb4').collate('utf8mb4_unicode_ci'),
    content: c.text(),
  },
})

ENUM Columns

MySQL native ENUM type is supported.

let users = table({
  name: 'users',
  columns: {
    id: c.integer(),
    role: c.enum(['admin', 'user', 'guest']).default('user'),
  },
})

Generated Columns

MySQL computed columns (virtual and stored) are supported.

let products = table({
  name: 'products',
  columns: {
    id: c.integer(),
    price: c.decimal(10, 2),
    tax_rate: c.decimal(5, 4),
    // Virtual generated column (computed on read)
    price_with_tax: c.decimal(10, 2).computed('price * (1 + tax_rate)', { stored: false }),
  },
})

Differences from Other Adapters

vs. PostgreSQL Adapter

Feature	MySQL	PostgreSQL
RETURNING	❌ No	✅ Yes
Savepoints	✅ Yes	✅ Yes
Transactional DDL	❌ No	✅ Yes
Migration Lock	Table Lock	Advisory Lock
Upsert	ON DUPLICATE KEY	ON CONFLICT
Identity	AUTO_INCREMENT	IDENTITY
Unsigned	✅ Yes	❌ No

vs. SQLite Adapter

Feature	MySQL	SQLite
RETURNING	❌ No	✅ Yes
Savepoints	✅ Yes	✅ Yes
Transactional DDL	❌ No	✅ Yes
Migration Lock	Table Lock	❌ No
Concurrent Writes	✅ Good	⚠️ Limited
Connection Pooling	✅ Yes	❌ N/A

Best Practices

Use Connection Pooling

Always use a connection pool for production applications.

// Good: Use a pool
let pool = createPool({
  host: 'localhost',
  user: 'root',
  database: 'mydb',
  connectionLimit: 10,
})
let db = createDatabase(createMysqlDatabaseAdapter(pool))

Handle insertId for Created Rows

Since RETURNING isn’t available, use insertId to reference created rows.

// Create parent record
let userResult = await db.create(users, {
  email: '[email protected]',
  name: 'Parent',
})

// Use insertId for related records
await db.create(profiles, {
  user_id: userResult.insertId,
  bio: 'Profile bio',
})

Be Careful with DDL in Transactions

MySQL cannot roll back DDL statements even inside transactions.

// Bad: DDL in transaction (changes are committed immediately)
await db.transaction(async (tx) => {
  // This ALTER TABLE commits immediately, even if transaction rolls back
  await tx.exec(sql`ALTER TABLE users ADD COLUMN foo VARCHAR(255)`)
  
  await tx.create(users, { ... })
  throw new Error('Rollback')
  // The ALTER TABLE is NOT rolled back!
})

// Good: Run DDL outside transactions
await db.exec(sql`ALTER TABLE users ADD COLUMN foo VARCHAR(255)`)

await db.transaction(async (tx) => {
  await tx.create(users, { ... })
})

Use AUTO_INCREMENT Instead of UUIDs for Primary Keys

MySQL performs better with integer primary keys.

// Good: Integer auto-increment primary key
let users = table({
  name: 'users',
  columns: {
    id: c.integer().autoIncrement().primaryKey(),
    email: c.varchar(255),
  },
})

// Acceptable: UUID for distributed systems
let distributedUsers = table({
  name: 'users',
  columns: {
    id: c.uuid().primaryKey(),
    email: c.varchar(255),
  },
})

Leverage Upserts for Idempotent Operations

Use upserts to make operations idempotent.

// Idempotent user creation/update
await db.query(users).upsert(
  {
    email: '[email protected]',
    name: 'User Name',
  },
  {
    update: { name: 'User Name' },
  }
)

Routing

Components

Data

Server

Middleware

Utilities

​Overview

​Installation

​API Reference

​createMysqlDatabaseAdapter

​MysqlDatabaseAdapter

​Type Definitions

​MysqlDatabaseConnection

​MysqlDatabasePool

​MysqlQueryResponse

​MysqlQueryRows

​MysqlQueryResultHeader

​MysqlDatabaseAdapterOptions

​Capabilities

​Usage Examples

​Basic Setup

​Working Without RETURNING

​createMany with returnRows

​Transactions and Savepoints

​Upserts with ON DUPLICATE KEY UPDATE

​Transaction Isolation Levels

​Auto-Increment Columns

​Working with UNSIGNED Integers

​Connection Pooling

​MySQL-Specific Features

​Charset and Collation

​ENUM Columns

​Generated Columns

​Differences from Other Adapters

​vs. PostgreSQL Adapter

​vs. SQLite Adapter

​Best Practices

​Use Connection Pooling

​Handle insertId for Created Rows

​Be Careful with DDL in Transactions

​Use AUTO_INCREMENT Instead of UUIDs for Primary Keys

​Leverage Upserts for Idempotent Operations

Build docs developers (and LLMs) love

Overview

Installation

API Reference

createMysqlDatabaseAdapter

MysqlDatabaseAdapter

Type Definitions

MysqlDatabaseConnection

MysqlDatabasePool

MysqlQueryResponse

MysqlQueryRows

MysqlQueryResultHeader

MysqlDatabaseAdapterOptions

Capabilities

Usage Examples

Basic Setup

Working Without RETURNING

createMany with returnRows

Transactions and Savepoints

Upserts with ON DUPLICATE KEY UPDATE

Transaction Isolation Levels

Auto-Increment Columns

Working with UNSIGNED Integers

Connection Pooling

MySQL-Specific Features

Charset and Collation

ENUM Columns

Generated Columns

Differences from Other Adapters

vs. PostgreSQL Adapter

vs. SQLite Adapter

Best Practices

Use Connection Pooling

Handle insertId for Created Rows

Be Careful with DDL in Transactions

Use AUTO_INCREMENT Instead of UUIDs for Primary Keys

Leverage Upserts for Idempotent Operations