data-table-mysql

The data-table-mysql package provides a MySQL adapter for the data-table query toolkit. It uses the mysql2 driver.

Installation

npm install remix mysql2

Or install the standalone package:

npm install @remix-run/data-table-mysql mysql2

Import

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

Or from the standalone package:

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

---

API

createMysqlDatabaseAdapter(client, options?)

Creates a DatabaseAdapter for use with createDatabase().

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

let pool = mysql.createPool({ uri: process.env.DATABASE_URL })
let adapter = createMysqlDatabaseAdapter(pool)
let db = createDatabase(adapter)

Parameters

Parameter	Type	Description
client	Pool | Connection | PoolConnection (from mysql2/promise)	A mysql2 connection pool or individual connection.
options	MysqlDatabaseAdapterOptions	Optional adapter configuration.

MysqlDatabaseAdapterOptions

Option	Type	Description
capabilities	AdapterCapabilityOverrides	Override detected adapter capabilities.

Capabilities

The MySQL adapter reports the following capabilities by default:

Capability	Default	Description
returning	false	MySQL does not support RETURNING clauses (use returnRow: true with a follow-up SELECT).
savepoints	true	MySQL supports savepoints for nested transactions.
upsert	true	MySQL supports ON DUPLICATE KEY UPDATE.
transactionalDdl	false	MySQL does not support DDL inside transactions (DDL causes an implicit commit).
migrationLock	true	MySQL supports GET_LOCK() for migration safety.

---

MySQL-Specific Considerations

• No RETURNING: MySQL does not support RETURNING clauses. When you use db.create(table, values, { returnRow: true }), the adapter inserts the row and then performs a follow-up SELECT using LAST_INSERT_ID().
• DDL and transactions: DDL statements (CREATE TABLE, ALTER TABLE, etc.) cause an implicit commit in MySQL. Migrations cannot be wrapped in a single transaction.
• ILIKE: MySQL does not have ILIKE. The adapter translates ilike queries to LIKE (MySQL's LIKE is case-insensitive for case-insensitive collations by default).
• Boolean columns: MySQL stores booleans as TINYINT(1). The adapter handles conversion.
• Connection pooling: Always use mysql.createPool() in production rather than individual connections.
• Enum columns: MySQL has native ENUM types. The c.enum() builder uses them.
• Auto-increment: Use c.integer().primaryKey().autoIncrement() for MySQL auto-increment columns.

---

Example

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

let pool = mysql.createPool({
  uri: process.env.DATABASE_URL,
  waitForConnections: true,
  connectionLimit: 10,
})

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

let users = table({
  name: 'users',
  columns: {
    id: c.integer().primaryKey().autoIncrement().unsigned(),
    email: c.varchar(255).notNull().unique('users_email_uq'),
    name: c.varchar(255).notNull(),
    role: c.enum(['admin', 'user']).notNull().default('user'),
    createdAt: c.timestamp().notNull().defaultNow(),
    updatedAt: c.timestamp().notNull().defaultNow(),
  },
  timestamps: true,
})

let allUsers = await db.findMany(users, {
  orderBy: ['createdAt', 'desc'],
  limit: 20,
})

---

Related

• data-table --- The core query toolkit.
• data-table-sqlite --- SQLite adapter.
• data-table-postgres --- PostgreSQL adapter.
• Database Guide --- Practical database patterns.