@drizzle-adapter/mysql2

MySQL adapter implementation for the Drizzle Adapter ecosystem using the mysql2 driver.

Overview

The @drizzle-adapter/mysql2 package provides the MySQL implementation for the Drizzle Adapter interface using the high-performance mysql2 driver. While you don't interact with this package directly (you use @drizzle-adapter/core instead), it enables MySQL support in the Drizzle Adapter ecosystem.

Why mysql2?

The mysql2 driver offers significant advantages:

• High Performance: Optimized for speed and efficiency
• Prepared Statements: Native support for prepared statements
• Connection Pooling: Built-in connection pool management
• Binary Protocol: Efficient binary protocol support
• Promise API: Modern promise-based interface
• Compression: Network compression support
• SSL/TLS: Secure connection support
• Charset/Collation: Full character set and collation support

Installation

# Install both the core package and the MySQL adapter
pnpm install @drizzle-adapter/core @drizzle-adapter/mysql2

Important: Adapter Registration

For the adapter to work correctly with the DrizzleAdapterFactory, you must import it for its self-registration side effects:

// Import for side effects - adapter will self-register
import '@drizzle-adapter/mysql2';

// Now you can use the factory
import { DrizzleAdapterFactory } from '@drizzle-adapter/core';

Usage

Configuration

import { DrizzleAdapterFactory, TypeDrizzleDatabaseConfig } from '@drizzle-adapter/core';

const config: TypeDrizzleDatabaseConfig = {
  DATABASE_DRIVER: 'mysql2',
  DATABASE_HOST: 'localhost',
  DATABASE_PORT: 3306,
  DATABASE_USER: 'user',
  DATABASE_PASSWORD: 'password',
  DATABASE_NAME: 'database'
};

const factory = new DrizzleAdapterFactory();
const adapter = factory.create(config);

Schema Definition

const dataTypes = adapter.getDataTypes();

const users = dataTypes.dbTable('users', {
  id: dataTypes.dbBigInt('id', { mode: 'number' }).primaryKey().autoincrement(),
  name: dataTypes.dbVarChar('name', { length: 255 }).notNull(),
  email: dataTypes.dbVarChar('email', { length: 255 }).notNull().unique(),
  status: dataTypes.dbEnum('status', ['active', 'inactive']).default('active'),
  metadata: dataTypes.dbJson('metadata'),
  createdAt: dataTypes.dbTimestamp('created_at').defaultNow()
});

const posts = dataTypes.dbTable('posts', {
  id: dataTypes.dbBigInt('id', { mode: 'number' }).primaryKey().autoincrement(),
  userId: dataTypes.dbBigInt('user_id', { mode: 'number' })
    .references(() => users.id),
  title: dataTypes.dbVarChar('title', { length: 255 }).notNull(),
  content: dataTypes.dbText('content').notNull(),
  published: dataTypes.dbBoolean('published').default(false),
  createdAt: dataTypes.dbTimestamp('created_at').defaultNow(),
  updatedAt: dataTypes.dbTimestamp('updated_at').onUpdateNow()
});

Basic CRUD Operations

import { eq, and, or, desc, sql } from 'drizzle-orm';

const connection = await adapter.getConnection();
const client = connection.getClient();

try {
  // INSERT
  // Single insert with returning
  const [newUser] = await client
    .insert(users)
    .values({
      name: 'John Doe',
      email: '[email protected]',
      metadata: { role: 'user' }
    })
    .returning();

  // Bulk insert
  await client
    .insert(posts)
    .values([
      {
        userId: newUser.id,
        title: 'First Post',
        content: 'Hello, world!'
      },
      {
        userId: newUser.id,
        title: 'Second Post',
        content: 'Another post'
      }
    ]);

  // SELECT
  // Select all
  const allUsers = await client
    .select()
    .from(users);

  // Select with conditions
  const user = await client
    .select()
    .from(users)
    .where(eq(users.email, '[email protected]'));

  // Select with join
  const userPosts = await client
    .select({
      userName: users.name,
      postTitle: posts.title,
      content: posts.content,
      metadata: users.metadata
    })
    .from(posts)
    .leftJoin(users, eq(posts.userId, users.id))
    .where(eq(posts.published, true))
    .orderBy(desc(posts.createdAt));

  // UPDATE
  // Update with JSON operations
  await client
    .update(users)
    .set({
      name: 'John Smith',
      metadata: sql`JSON_SET(${users.metadata}, '$.role', 'admin')`
    })
    .where(eq(users.id, newUser.id));

  // DELETE
  await client
    .delete(posts)
    .where(
      and(
        eq(posts.userId, newUser.id),
        eq(posts.published, false)
      )
    );

} finally {
  await connection.disconnect();
}

Advanced Features

Transaction Support

const connection = await adapter.getConnection();
const client = connection.getClient();

try {
  await client.transaction(async (tx) => {
    const [user] = await tx
      .insert(users)
      .values({
        name: 'John Doe',
        email: '[email protected]'
      })
      .returning();

    await tx
      .insert(posts)
      .values({
        userId: user.id,
        title: 'My First Post',
        content: 'Hello, world!'
      });
  });
} finally {
  await connection.disconnect();
}

JSON Operations

// Query with JSON conditions
const premiumUsers = await client
  .select()
  .from(users)
  .where(sql`JSON_EXTRACT(${users.metadata}, '$.subscription') = 'premium'`);

// Update nested JSON
await client
  .update(users)
  .set({
    metadata: sql`JSON_SET(
      ${users.metadata},
      '$.subscription', 'premium',
      '$.validUntil', CURRENT_TIMESTAMP
    )`
  })
  .where(eq(users.id, userId));

Full-Text Search

// Create FULLTEXT index
await client.execute(sql`
  ALTER TABLE ${posts} 
  ADD FULLTEXT INDEX posts_fulltext (title, content)
`);

// Search posts
const searchResults = await client
  .select()
  .from(posts)
  .where(sql`MATCH(title, content) AGAINST(${searchTerm} IN BOOLEAN MODE)`)
  .orderBy(desc(posts.createdAt));

Best Practices

1. Connection Management: Always use try/finally to ensure connections are closed
2. Transactions: Use transactions for related operations
3. Prepared Statements: Statements are automatically prepared
4. Error Handling: Implement proper error handling and retries
5. Query Optimization: Use proper indexes and query planning

Contributing

We welcome contributions! Please feel free to submit a Pull Request. For major changes, please open an issue first to discuss what you would like to change.

Related Packages

• @drizzle-adapter/core - Core interfaces and types
• @drizzle-adapter/mysql-core - Shared MySQL functionality