@dpfurner/payload-db-mysql

MySQL database adapter for Payload CMS using Drizzle ORM.

Installation

npm install @dpfurner/payload-db-mysql
# or
pnpm add @dpfurner/payload-db-mysql
# or
yarn add @dpfurner/payload-db-mysql

Usage

Configure the MySQL adapter in your Payload config:

import { buildConfig } from 'payload'
import { mysqlAdapter } from '@dpfurner/payload-db-mysql'

export default buildConfig({
  // ... your other config
  db: mysqlAdapter({
    pool: {
      host: 'localhost',
      port: 3306,
      user: 'root',
      password: 'your-password',
      database: 'your-database',
    },
  }),
})

Using Connection String

You can also use a connection string (URI):

import { buildConfig } from 'payload'
import { mysqlAdapter } from '@dpfurner/payload-db-mysql'

export default buildConfig({
  db: mysqlAdapter({
    pool: {
      uri: 'mysql://user:password@localhost:3306/database',
    },
  }),
})

Environment Variables

A common pattern is to use environment variables:

import { buildConfig } from 'payload'
import { mysqlAdapter } from '@dpfurner/payload-db-mysql'

export default buildConfig({
  db: mysqlAdapter({
    pool: {
      host: process.env.DB_HOST || 'localhost',
      port: parseInt(process.env.DB_PORT || '3306'),
      user: process.env.DB_USER || 'root',
      password: process.env.DB_PASSWORD,
      database: process.env.DB_NAME,
    },
  }),
})

Configuration Options

| Option | Type | Default | Description | |--------|------|---------|-------------| | pool | PoolOptions | required | MySQL2 pool configuration options | | idType | 'serial' \| 'uuid' | 'serial' | Primary key type for tables | | push | boolean | true | Enable automatic schema push in development | | migrationDir | string | ./migrations | Path to migrations directory | | logger | boolean \| DrizzleConfig['logger'] | false | Enable Drizzle query logging | | localesSuffix | string | '_locales' | Suffix for locale tables | | relationshipsSuffix | string | '_rels' | Suffix for relationship tables | | versionsSuffix | string | '_v' | Suffix for version tables | | schemaName | string | - | Database schema name | | disableCreateDatabase | boolean | false | Disable automatic database creation | | blocksAsJSON | boolean | false | Store blocks as JSON instead of relational structure | | transactionOptions | false \| MySqlTransactionConfig | - | Transaction configuration | | allowIDOnCreate | boolean | false | Allow custom IDs on document creation | | autoIncrement | boolean | true | Enable AUTO_INCREMENT for integer IDs | | beforeSchemaInit | MySQLSchemaHook[] | [] | Hooks to modify schema before initialization | | afterSchemaInit | MySQLSchemaHook[] | [] | Hooks to modify schema after initialization |

Pool Options

The pool option accepts all mysql2 pool options:

mysqlAdapter({
  pool: {
    host: 'localhost',
    port: 3306,
    user: 'root',
    password: 'password',
    database: 'payload',
    // Additional pool options
    waitForConnections: true,
    connectionLimit: 10,
    queueLimit: 0,
    enableKeepAlive: true,
    keepAliveInitialDelay: 0,
  },
})

Migrations

Creating Migrations

npx payload migrate:create

Running Migrations

npx payload migrate

Migration File Example

import { MigrateUpArgs, MigrateDownArgs, sql } from '@dpfurner/payload-db-mysql'

export async function up({ db, payload, req }: MigrateUpArgs): Promise<void> {
  await db.execute(sql`
    ALTER TABLE posts ADD COLUMN featured BOOLEAN DEFAULT FALSE
  `)
}

export async function down({ db, payload, req }: MigrateDownArgs): Promise<void> {
  await db.execute(sql`
    ALTER TABLE posts DROP COLUMN featured
  `)
}

Using Drizzle Directly

You can access the Drizzle instance directly for advanced queries:

import { sql } from '@dpfurner/payload-db-mysql'

// In a hook or custom endpoint
const results = await payload.db.drizzle.execute(
  sql`SELECT * FROM posts WHERE published = true`
)

Schema Hooks

Customize the generated schema with hooks:

mysqlAdapter({
  pool: { /* ... */ },
  afterSchemaInit: [
    async ({ schema, extendTable }) => {
      // Add a full-text index to a table
      return {
        ...schema,
        tables: {
          ...schema.tables,
          // Your customizations
        },
      }
    },
  ],
})

Production Migrations

For production deployments, you can pre-compile migrations:

import { mysqlAdapter } from '@dpfurner/payload-db-mysql'
import { migrations } from './migrations'

export default buildConfig({
  db: mysqlAdapter({
    pool: { /* ... */ },
    prodMigrations: migrations,
  }),
})

TypeScript

The adapter provides full TypeScript support. You can generate types from your schema:

npx payload generate:types

Supported MySQL Versions

• MySQL 8.0+
• MariaDB 10.5+

Features

• Full Payload CMS feature support
• Automatic schema generation
• Migration support
• Transaction support
• JSON field support
• Relationship handling
• Localization support
• Version history support
• Draft system support

License

MIT