The MySqlDatabase class is the core interface for interacting with a MySQL database. It provides methods for executing queries, managing transactions, and working with Common Table Expressions (CTEs).
Database Methods
select()
Creates a SELECT query. Call with no arguments to select all columns, or pass a selection object to specify columns.
select(): MySqlSelectBuilder
select<TSelection>(fields: TSelection): MySqlSelectBuilder<TSelection>
Selection object specifying which columns to select. Each key is the result field name, each value is a column or expression.
Examples
// Select all columns
const allCars = await db.select().from(cars);
// Select specific columns
const carsIdsAndBrands = await db.select({
id: cars.id,
brand: cars.brand
}).from(cars);
// Select with expressions
const carsWithLowerNames = await db.select({
id: cars.id,
lowerBrand: sql<string>`lower(${cars.brand})`
}).from(cars);
selectDistinct()
Creates a SELECT DISTINCT query to return only unique rows.
selectDistinct(): MySqlSelectBuilder
selectDistinct<TSelection>(fields: TSelection): MySqlSelectBuilder<TSelection>
Selection object specifying which columns to select for uniqueness.
Examples
// Select all unique rows
await db.selectDistinct()
.from(cars)
.orderBy(cars.id, cars.brand, cars.color);
// Select unique brands
await db.selectDistinct({ brand: cars.brand })
.from(cars)
.orderBy(cars.brand);
insert()
Creates an INSERT query to add new rows to a table.
insert<TTable extends MySqlTable>(
table: TTable
): MySqlInsertBuilder<TTable>
The table to insert into.
Examples
// Insert one row
await db.insert(cars).values({ brand: 'BMW' });
// Insert multiple rows
await db.insert(cars).values([
{ brand: 'BMW' },
{ brand: 'Porsche' }
]);
// Insert with on duplicate key update
await db.insert(users)
.values({ id: 1, name: 'John' })
.onDuplicateKeyUpdate({ set: { name: 'John Updated' } });
update()
Creates an UPDATE query to modify existing rows.
update<TTable extends MySqlTable>(
table: TTable
): MySqlUpdateBuilder<TTable>
Always use a .where() clause with updates unless you intend to update all rows.
Examples
// Update all rows
await db.update(cars).set({ color: 'red' });
// Update with filters
await db.update(cars)
.set({ color: 'red' })
.where(eq(cars.brand, 'BMW'));
// Update with expressions
await db.update(users)
.set({ loginCount: sql`${users.loginCount} + 1` })
.where(eq(users.id, 1));
delete()
Creates a DELETE query to remove rows from a table.
delete<TTable extends MySqlTable>(
table: TTable
): MySqlDeleteBase<TTable>
The table to delete from.
Calling delete without a .where() clause will delete all rows in the table.
Examples
// Delete all rows
await db.delete(cars);
// Delete with filters
await db.delete(cars).where(eq(cars.color, 'green'));
// Delete with limit
await db.delete(cars)
.where(eq(cars.brand, 'BMW'))
.limit(10);
$with()
Defines a Common Table Expression (CTE) for use in queries.
$with(alias: string): { as: (query) => WithSubquery }
The alias name for the CTE.
Examples
// Create and use a CTE
const sq = db.$with('sq').as(
db.select().from(users).where(eq(users.id, 42))
);
const result = await db.with(sq).select().from(sq);
// CTE with aliased SQL expressions
const sq = db.$with('sq').as(
db.select({
name: sql<string>`upper(${users.name})`.as('name')
}).from(users)
);
const result = await db.with(sq)
.select({ name: sq.name })
.from(sq);
with()
Incorporates previously defined CTEs into the main query.
with(...queries: WithSubquery[]): {
select: () => MySqlSelectBuilder,
selectDistinct: () => MySqlSelectBuilder,
update: <TTable>(table: TTable) => MySqlUpdateBuilder<TTable>,
delete: <TTable>(table: TTable) => MySqlDeleteBase<TTable>
}
One or more CTEs to incorporate.
Examples
// Single CTE
const sq = db.$with('sq').as(db.select().from(users));
const result = await db.with(sq).select().from(sq);
// Multiple CTEs
const sq1 = db.$with('sq1').as(db.select().from(users));
const sq2 = db.$with('sq2').as(db.select().from(posts));
const result = await db.with(sq1, sq2)
.select()
.from(sq1)
.leftJoin(sq2, eq(sq1.id, sq2.userId));
$count()
Creates a count query builder for efficient row counting.
$count(
source: MySqlTable | MySqlViewBase | SQL,
filters?: SQL
): MySqlCountBuilder
source
MySqlTable | MySqlViewBase | SQL
required
The table, view, or SQL expression to count rows from.
Optional WHERE clause filters.
Examples
// Count all rows
const totalUsers = await db.$count(users);
// Count with filters
const activeUsers = await db.$count(
users,
eq(users.status, 'active')
);
execute()
Executes raw SQL queries.
execute<T = ResultSetHeader>(
query: SQLWrapper | string
): Promise<T>
query
SQLWrapper | string
required
The SQL query to execute. Can be a string or a SQL template tagged expression.
Examples
// Execute raw SQL string
await db.execute('SELECT * FROM users');
// Execute SQL template
await db.execute(sql`SELECT * FROM users WHERE id = ${userId}`);
// Get result metadata
const result = await db.execute<ResultSetHeader>(
sql`INSERT INTO users (name) VALUES ('John')`
);
console.log(result.insertId);
transaction()
Executes a function within a database transaction.
transaction<T>(
transaction: (tx: MySqlTransaction, config?: MySqlTransactionConfig) => Promise<T>,
config?: MySqlTransactionConfig
): Promise<T>
Callback function that receives a transaction object. All database operations within this function will be part of the transaction.
Transaction configuration options like isolation level.
Examples
// Basic transaction
await db.transaction(async (tx) => {
await tx.insert(users).values({ name: 'John' });
await tx.insert(posts).values({ title: 'Hello' });
});
// Transaction with isolation level
await db.transaction(async (tx) => {
const user = await tx.select().from(users).where(eq(users.id, 1));
await tx.update(users).set({ balance: user[0].balance - 100 });
}, {
isolationLevel: 'serializable'
});
// Transaction with error handling
try {
await db.transaction(async (tx) => {
await tx.insert(users).values({ name: 'John' });
throw new Error('Rollback!');
});
} catch (error) {
// Transaction automatically rolled back
}
Relational Queries
query
Access relational query API when a schema is provided.
query: {
[TableName]: RelationalQueryBuilder
}
The query property is only available when you pass a schema to the database constructor.
Examples
import { drizzle } from 'drizzle-orm/mysql2';
import * as schema from './schema';
const db = drizzle(pool, { schema });
// Relational query with nested relations
const result = await db.query.users.findMany({
with: {
posts: true,
profile: true
}
});
// Relational query with filters
const result = await db.query.users.findFirst({
where: (users, { eq }) => eq(users.id, 1),
with: {
posts: {
where: (posts, { eq }) => eq(posts.published, true)
}
}
});
Read Replicas
withReplicas()
Configures read replicas for read/write splitting.
withReplicas<Q extends MySqlDatabase>(
primary: Q,
replicas: [Q, ...Q[]],
getReplica?: (replicas: Q[]) => Q
): MySQLWithReplicas<Q>
The primary (write) database connection.
Array of replica (read) database connections. Must have at least one replica.
Custom function to select which replica to use. Defaults to random selection.
Examples
import { drizzle } from 'drizzle-orm/mysql2';
import { withReplicas } from 'drizzle-orm/mysql-core';
const primaryDb = drizzle(primaryPool);
const replica1 = drizzle(replica1Pool);
const replica2 = drizzle(replica2Pool);
const db = withReplicas(primaryDb, [replica1, replica2]);
// Read queries go to replicas
const users = await db.select().from(users);
// Write queries go to primary
await db.insert(users).values({ name: 'John' });
// Explicitly use primary
await db.$primary.select().from(users);
// Custom replica selection (e.g., round-robin)
let replicaIndex = 0;
const db = withReplicas(
primaryDb,
[replica1, replica2],
(replicas) => replicas[replicaIndex++ % replicas.length]
);
Type Helpers
MySqlDatabase
class MySqlDatabase<
TQueryResult extends MySqlQueryResultHKT,
TPreparedQueryHKT extends PreparedQueryHKTBase,
TFullSchema extends Record<string, unknown> = {},
TSchema extends TablesRelationalConfig = ExtractTablesWithRelations<TFullSchema>
>
The query result type for the specific MySQL driver being used.
The prepared query type for the specific MySQL driver.
The full schema definition including all tables and relations.
Extracted relational configuration from the full schema.