Data Table MySQL Overview

The data-table-mysql package connects the data-table query toolkit to MySQL via the mysql2 driver. Use it when your infrastructure runs MySQL or when you need MySQL-specific features like native ENUM types.

When to Use MySQL

Use Case	MySQL Fits?
Existing MySQL infrastructure	Yes --- plug in and go.
Production web applications	Yes --- mature, well-supported, widely hosted.
Multi-server deployments	Yes --- networked connections and GET_LOCK() for migration safety.
Advanced SQL (CTEs, JSONB, window functions)	Partial --- MySQL 8+ supports CTEs and window functions, but JSONB is PostgreSQL-only.

Quick Setup

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

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

// Create the adapter and database
let adapter = createMysqlDatabaseAdapter(pool)
let db = createDatabase(adapter)

// Define a table
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,
})

// Query
let all = await db.findMany(users)

Key Considerations

• No RETURNING: MySQL does not support RETURNING clauses. When you use { returnRow: true }, the adapter performs a follow-up SELECT using LAST_INSERT_ID().
• DDL and transactions: DDL statements (CREATE TABLE, ALTER TABLE) cause an implicit commit in MySQL. Migrations cannot be fully transactional.
• Case-insensitive LIKE: MySQL's LIKE is case-insensitive by default for common collations, so ilike maps to LIKE.
• Booleans: MySQL uses TINYINT(1) for booleans. The adapter handles conversion.
• Native ENUMs: MySQL supports native ENUM column types. The c.enum() builder uses them.
• Connection pooling: Always use mysql.createPool() in production.

mysql2/promise

Always import from mysql2/promise (not mysql2). The data-table-mysql adapter requires the promise-based API.

Installation

npm install remix mysql2

Related

• Data Table MySQL Tutorial --- Step-by-step setup, table creation, and querying.
• Data Table MySQL Reference --- Full API reference.
• Data Table Overview --- The core query toolkit.
• Data Table PostgreSQL --- PostgreSQL adapter for advanced features.
• Data Table SQLite --- SQLite adapter for development.