@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
- Connection Management: Always use try/finally to ensure connections are closed
- Transactions: Use transactions for related operations
- Prepared Statements: Statements are automatically prepared
- Error Handling: Implement proper error handling and retries
- 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