Data Table MySQL Tutorial

In this tutorial you will connect to a MySQL database, create tables, insert data, and run queries using the data-table toolkit.

Prerequisites

You need a running MySQL server. Set the connection string in an environment variable:

export DATABASE_URL="mysql://user:password@localhost:3306/myapp"

Install the packages:

npm install remix mysql2

---

Step 1 --- Connect with a Pool

// db.ts
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,
  waitForConnections: true,
  connectionLimit: 10,
})

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

Always use mysql2/promise

Import from mysql2/promise for the promise-based API. The adapter does not work with callback-style connections.

Step 2 --- Define Tables

MySQL supports native ENUM types and unsigned integers:

// db/tables.ts
import { table, column as c } from 'remix/data-table'

export let categories = table({
  name: 'categories',
  columns: {
    id: c.integer().primaryKey().autoIncrement().unsigned(),
    name: c.varchar(100).notNull().unique('categories_name_uq'),
    createdAt: c.timestamp().notNull().defaultNow(),
  },
})

export let products = table({
  name: 'products',
  columns: {
    id: c.integer().primaryKey().autoIncrement().unsigned(),
    name: c.varchar(255).notNull(),
    price: c.decimal(10, 2).notNull(),
    status: c.enum(['active', 'discontinued']).notNull().default('active'),
    categoryId: c.integer().unsigned().notNull().references('categories', 'fk_products_category'),
    createdAt: c.timestamp().notNull().defaultNow(),
    updatedAt: c.timestamp().notNull().defaultNow(),
  },
  timestamps: true,
})

Step 3 --- Create Tables with a Migration

// migrations/001-initial.ts
import { createMigration, column as c } from 'remix/data-table/migrations'

export default createMigration({
  up(ctx) {
    ctx.createTable('categories', {
      id: c.integer().primaryKey().autoIncrement().unsigned(),
      name: c.varchar(100).notNull().unique('categories_name_uq'),
      createdAt: c.timestamp().notNull().defaultNow(),
    })

    ctx.createTable('products', {
      id: c.integer().primaryKey().autoIncrement().unsigned(),
      name: c.varchar(255).notNull(),
      price: c.decimal(10, 2).notNull(),
      status: c.enum(['active', 'discontinued']).notNull().default('active'),
      categoryId: c.integer().unsigned().notNull().references('categories', 'fk_products_category'),
      createdAt: c.timestamp().notNull().defaultNow(),
      updatedAt: c.timestamp().notNull().defaultNow(),
    })
  },

  down(ctx) {
    ctx.dropTable('products')
    ctx.dropTable('categories')
  },
})

Run migrations:

import { createMigrationRunner } from 'remix/data-table/migrations'
import { loadMigrations } from 'remix/data-table/migrations/node'

let migrations = await loadMigrations('./migrations')
let runner = createMigrationRunner(adapter)
await runner.up(migrations)

Step 4 --- Insert and Query Data

import { db } from './db'
import { categories, products } from './db/tables'
import { belongsTo } from 'remix/data-table'
import { gt } from 'remix/data-table/operators'

// Seed categories
let electronics = await db.create(categories, { name: 'Electronics' }, { returnRow: true })
let books = await db.create(categories, { name: 'Books' }, { returnRow: true })

// Seed products
await db.createMany(products, [
  { name: 'Laptop', price: 999.99, categoryId: electronics.id },
  { name: 'Keyboard', price: 79.99, categoryId: electronics.id },
  { name: 'Remix V3 Guide', price: 39.99, categoryId: books.id },
])

// Find expensive products
let expensive = await db.findMany(products, {
  where: gt('price', 50),
  orderBy: [['price', 'desc']],
})

// Find a product with its category
let productCategory = belongsTo(products, categories, { foreignKey: 'categoryId' })

let laptop = await db.findOne(products, {
  where: { name: 'Laptop' },
  with: { category: productCategory },
})
// laptop.category.name === 'Electronics'

What You Learned

• Connect to MySQL with a connection pool using mysql2/promise.
• Define tables with unsigned integers, native enums, and decimal columns.
• Run migrations (note: MySQL DDL causes implicit commits, so migrations are not fully transactional).
• Insert and query data with relations.

Related

• Data Table MySQL Overview --- When to use MySQL and key considerations.
• Data Table MySQL Reference --- Full API reference.
• Data Table Tutorial --- Comprehensive tutorial covering all data-table features.