Migrating from PostgreSQL to MySQL

Learn how to migrate from PostgreSQL to MySQL in your Next.js Drizzle SaaS application.

Important: This kit uses PostgreSQL by default. This guide describes a one-way migration for users who want to switch to MySQL. Tests continue using PGlite (PostgreSQL in-memory) regardless of your production database.

Why MySQL?

  • Widespread hosting: Supported by most hosting providers, from shared hosting to cloud platforms
  • PlanetScale: Serverless MySQL with branching, zero-downtime schema changes
  • AWS Aurora MySQL: High availability MySQL-compatible database
  • Existing infrastructure: Many organizations already run MySQL

Architecture

The database package uses an adapters pattern:

packages/database/src/
  client.ts              # Re-exports from active adapter
  adapters/
    postgres.ts          # PostgreSQL adapter (default)
    mysql.ts             # MySQL adapter (you'll create this)
  test-utils/
    pglite-db.ts         # Tests always use PGlite (unchanged)

You'll create a MySQL adapter and switch the client to use it.

Quick Start

1. Install mysql2 driver

pnpm --filter @kit/database add mysql2

2. Create MySQL adapter

Create packages/database/src/adapters/mysql.ts:

import { MySql2Database, drizzle } from 'drizzle-orm/mysql2';
import mysql from 'mysql2/promise';
import { databaseUrl } from '../database-url';
import * as schema from '../schema/schema';
declare global {
  var mysqlDb: MySql2Database<DatabaseSchema> | undefined;
}
export type DatabaseSchema = typeof schema;
export type Database = MySql2Database<DatabaseSchema>;
let db: Database;
if (process.env.NODE_ENV === 'production') {
  db = createDrizzle();
} else {
  if (!global.mysqlDb) {
    global.mysqlDb = createDrizzle();
  }
  db = global.mysqlDb;
}
export { db };
function createDrizzle() {
  const pool = mysql.createPool({
    uri: databaseUrl,
    waitForConnections: true,
    connectionLimit: 10,
    queueLimit: 0,
  });
  return drizzle(pool, { schema, mode: 'default' });
}

3. Update Better Auth provider

Edit packages/better-auth/src/auth.ts:

const database = drizzleAdapter(db, {
  provider: 'mysql',  // Change from 'pg'
  usePlural: true,
});

4. Generate MySQL schema

This regenerates packages/database/src/schema/core.ts with MySQL types:

pnpm --filter @kit/better-auth schema:generate

5. Switch to MySQL adapter

Edit packages/database/src/client.ts:

// Before
export { db, type DatabaseSchema } from './adapters/postgres';
// After
export { db, type DatabaseSchema } from './adapters/mysql';

6. Update Drizzle config

Edit packages/database/drizzle.config.mjs:

import { defineConfig } from 'drizzle-kit';
const dialect = 'mysql';  // Change from 'postgresql'
export default defineConfig({
  schema: '../../packages/database/src/schema/schema.ts',
  out: '../../packages/database/src/schema',
  dialect,
  dbCredentials: {
    url: process.env.DATABASE_URL ?? 'mysql://root:password@127.0.0.1:3306/saas_kit',
  },
  verbose: true,
  strict: true,
});

7. Update database URL validation

Edit packages/database/src/database-url.ts:

import * as z from 'zod';
export const databaseUrl = z
  .url({
    error:
      'Please provide the variable DATABASE_URL as a valid URL pointing to a MySQL database',
  })
  .default(
    process.env.NODE_ENV === 'production'
      ? process.env.DATABASE_URL!
      : 'mysql://root:password@127.0.0.1:3306/saas_kit',
  )
  .parse(process.env.DATABASE_URL);

8. Update docker-compose.dev.yml

Replace the PostgreSQL service with MySQL:

services:
  mysql:
    image: mysql:8.0
    container_name: next-drizzle-saas-kit-mysql
    environment:
      MYSQL_ROOT_PASSWORD: password
      MYSQL_DATABASE: saas_kit
    ports:
      - '3306:3306'
    volumes:
      - mysql_data:/var/lib/mysql
    healthcheck:
      test: ['CMD', 'mysqladmin', 'ping', '-h', 'localhost']
      interval: 10s
      timeout: 5s
      retries: 5
volumes:
  mysql_data:

9. Regenerate migrations

rm -rf packages/database/src/schema/meta
rm packages/database/src/schema/*.sql
pnpm --filter "@kit/database" drizzle:generate

10. Start MySQL and push migrations

Start the MySQL container:

pnpm compose:dev:up

Push the migrations to the database:

pnpm --filter "@kit/database" drizzle:migrate

11. Seed the database (optional)

If you have seed scripts configured:

pnpm seed

Environment Variables

Local MySQL

DATABASE_URL=mysql://root:password@127.0.0.1:3306/saas_kit

AWS RDS MySQL

DATABASE_URL=mysql://admin:password@your-instance.region.rds.amazonaws.com:3306/saas_kit

Cleanup

Remove unused PostgreSQL packages:

pnpm --filter @kit/database remove postgres

Keep @electric-sql/pglite - it's used by tests.