MySQL Utilities

API reference for MySQL utility functions and helpers in Drizzle ORM

Drizzle ORM provides various utility functions for working with MySQL schemas, tables, and query building.

Schema Utilities

getTableConfig()

Extracts complete configuration from a table definition.

getTableConfig(table: MySqlTable): {
  columns: MySqlColumn[];
  indexes: Index[];
  foreignKeys: ForeignKey[];
  checks: Check[];
  primaryKeys: PrimaryKey[];
  uniqueConstraints: UniqueConstraint[];
  name: string;
  schema: string | undefined;
  baseName: string;
}

table · MySqlTable · required

The table to extract configuration from.

Examples

import { getTableConfig } from 'drizzle-orm/mysql-core';
import { users } from './schema';

const config = getTableConfig(users);

console.log(config.name); // 'users'
console.log(config.columns.length); // Number of columns
console.log(config.indexes); // Array of indexes
console.log(config.foreignKeys); // Array of foreign keys

// Iterate over columns
for (const column of config.columns) {
  console.log(column.name, column.getSQLType());
}

// Check for specific index
const hasEmailIndex = config.indexes.some(
  idx => idx.config.columns.some(col => col.name === 'email')
);

---

getViewConfig()

Extracts configuration from a view definition.

getViewConfig<TName extends string, TExisting extends boolean>(
  view: MySqlView<TName, TExisting>
): ViewConfig

view · MySqlView · required

The view to extract configuration from.

Examples

import { getViewConfig } from 'drizzle-orm/mysql-core';
import { activeUsersView } from './schema';

const config = getViewConfig(activeUsersView);

console.log(config.name);
console.log(config.query);
console.log(config.selectedFields);

---

Index Utilities

index()

Creates an index definition.

index(name?: string): IndexBuilder

name · string

The name of the index. If not provided, a name will be generated.

Examples

import { mysqlTable, int, varchar, index } from 'drizzle-orm/mysql-core';

export const users = mysqlTable('users', {
  id: int().primaryKey(),
  email: varchar({ length: 255 }),
  name: varchar({ length: 255 }),
  createdAt: timestamp()
}, (table) => [
  // Named index
  index('email_idx').on(table.email),
  
  // Auto-named index
  index().on(table.name),
  
  // Composite index
  index('name_created_idx').on(table.name, table.createdAt),
  
  // Index with algorithm
  index('email_btree_idx').on(table.email).using('btree'),
  
  // Index with order
  index('created_desc_idx').on(table.createdAt.desc())
]);

---

uniqueIndex()

Creates a unique index definition.

uniqueIndex(name?: string): IndexBuilder

Examples

export const users = mysqlTable('users', {
  id: int().primaryKey(),
  email: varchar({ length: 255 }),
  username: varchar({ length: 100 })
}, (table) => [
  uniqueIndex('email_unique_idx').on(table.email),
  uniqueIndex('username_unique_idx').on(table.username)
]);

---

primaryKey()

Defines a composite primary key.

primaryKey(config: { columns: MySqlColumn[] }): PrimaryKeyBuilder

config.columns · MySqlColumn[] · required

Array of columns that form the composite primary key.

Examples

import { primaryKey } from 'drizzle-orm/mysql-core';

export const userRoles = mysqlTable('user_roles', {
  userId: int().notNull(),
  roleId: int().notNull(),
  assignedAt: timestamp().notNull()
}, (table) => [
  primaryKey({ columns: [table.userId, table.roleId] })
]);

---

foreignKey()

Defines a foreign key constraint.

foreignKey(config: {
  columns: MySqlColumn[];
  foreignColumns: MySqlColumn[];
  name?: string;
}): ForeignKeyBuilder

config.columns · MySqlColumn[] · required

Columns in the current table.

config.foreignColumns · MySqlColumn[] · required

Referenced columns in the foreign table.

config.name · string

Name for the foreign key constraint.

Examples

import { foreignKey } from 'drizzle-orm/mysql-core';

export const posts = mysqlTable('posts', {
  id: int().primaryKey(),
  authorId: int().notNull(),
  categoryId: int()
}, (table) => [
  // Basic foreign key
  foreignKey({
    columns: [table.authorId],
    foreignColumns: [users.id]
  }),
  
  // With cascade delete
  foreignKey({
    columns: [table.authorId],
    foreignColumns: [users.id]
  }).onDelete('cascade'),
  
  // Named constraint
  foreignKey({
    columns: [table.categoryId],
    foreignColumns: [categories.id],
    name: 'posts_category_fk'
  }).onDelete('set null')
]);

---

unique()

Defines a unique constraint.

unique(name?: string): UniqueConstraintBuilder

Examples

import { unique } from 'drizzle-orm/mysql-core';

export const users = mysqlTable('users', {
  id: int().primaryKey(),
  email: varchar({ length: 255 }),
  tenantId: int(),
  username: varchar({ length: 100 })
}, (table) => [
  // Single column unique
  unique('email_unique').on(table.email),
  
  // Composite unique constraint
  unique('tenant_username_unique').on(table.tenantId, table.username)
]);

---

check()

Defines a check constraint.

check(name: string, condition: SQL): CheckBuilder

name · string · required

Name of the check constraint.

condition · SQL · required

The check condition as a SQL expression.

Examples

import { check, sql } from 'drizzle-orm/mysql-core';

export const products = mysqlTable('products', {
  id: int().primaryKey(),
  price: decimal({ precision: 10, scale: 2 }),
  discountPercent: int(),
  stock: int()
}, (table) => [
  check('price_positive', sql`${table.price} > 0`),
  check('valid_discount', sql`${table.discountPercent} BETWEEN 0 AND 100`),
  check('stock_non_negative', sql`${table.stock} >= 0`)
]);

---

Query Utilities

QueryBuilder

Helper class for building queries programmatically.

const qb = new QueryBuilder();

const query = qb.select()
  .from(users)
  .where(eq(users.status, 'active'));

---

sql

Template tag for raw SQL expressions.

import { sql } from 'drizzle-orm';

// Raw SQL in select
await db.select({
  id: users.id,
  upperName: sql<string>`UPPER(${users.name})`
}).from(users);

// Raw SQL in where clause
await db.select()
  .from(users)
  .where(sql`${users.createdAt} > DATE_SUB(NOW(), INTERVAL 1 DAY)`);

// Parameterized SQL
const name = 'John';
await db.select()
  .from(users)
  .where(sql`${users.name} = ${name}`);

---

sql.placeholder()

Creates a placeholder for prepared statements.

sql.placeholder(name: string): Placeholder

Examples

import { sql } from 'drizzle-orm';

const stmt = db.select()
  .from(users)
  .where(sql`${users.id} = ${sql.placeholder('userId')}`)
  .prepare();

await stmt.execute({ userId: 1 });
await stmt.execute({ userId: 2 });

---

sql.raw()

Creates a SQL expression from a raw string.

sql.raw(query: string): SQL

Use sql.raw() with caution. It bypasses parameter binding and can be vulnerable to SQL injection if used with user input.

Examples

import { sql } from 'drizzle-orm';

// Safe: no user input
await db.execute(sql.raw('OPTIMIZE TABLE users'));

// Unsafe: don't do this with user input!
// const userId = getUserInput();
// await db.execute(sql.raw(`SELECT * FROM users WHERE id = ${userId}`));

// Safe alternative: use parameterized queries
const userId = getUserInput();
await db.execute(sql`SELECT * FROM users WHERE id = ${userId}`);

---

Type Utilities

InferSelectModel

Infers the TypeScript type for SELECT operations.

import { type InferSelectModel } from 'drizzle-orm';

const users = mysqlTable('users', {
  id: int().primaryKey(),
  name: varchar({ length: 255 }).notNull(),
  email: varchar({ length: 255 }),
  createdAt: timestamp().notNull().defaultNow()
});

type User = InferSelectModel<typeof users>;
// { id: number; name: string; email: string | null; createdAt: Date }

---

InferInsertModel

Infers the TypeScript type for INSERT operations.

import { type InferInsertModel } from 'drizzle-orm';

type NewUser = InferInsertModel<typeof users>;
// { id?: number; name: string; email?: string | null; createdAt?: Date }

const newUser: NewUser = {
  name: 'John',
  email: '[e-mail]'
  // id and createdAt are optional
};

---

Helper Functions

extractUsedTable()

Extracts table names from a table reference, subquery, or SQL expression.

extractUsedTable(
  table: MySqlTable | Subquery | MySqlViewBase | SQL
): string[]

Examples

import { extractUsedTable } from 'drizzle-orm/mysql-core/utils';
import { users } from './schema';

const tables = extractUsedTable(users);
console.log(tables); // ['users']

const sq = db.select().from(users).as('sq');
const subqueryTables = extractUsedTable(sq);
console.log(subqueryTables); // ['users']

---

convertIndexToString()

Converts index builders or names to string array.

convertIndexToString(indexes: IndexForHint[]): string[]

Examples

import { convertIndexToString } from 'drizzle-orm/mysql-core/utils';

const emailIdx = index('email_idx').on(users.email);
const nameIdx = index('name_idx').on(users.name);

const indexNames = convertIndexToString([emailIdx, nameIdx, 'custom_idx']);
console.log(indexNames); // ['email_idx', 'name_idx', 'custom_idx']

---

Column Helpers

These functions are available on column builders:

$type()

Overrides the inferred TypeScript type.

json().$type<{ name: string; age: number }>()
varchar({ length: 50 }).$type<'admin' | 'user' | 'guest'>()

---

$default()

Sets a runtime default value.

timestamp().$default(() => new Date())
varchar({ length: 36 }).$default(() => crypto.randomUUID())

---

$onUpdate()

Sets a runtime update value.

timestamp().$onUpdate(() => new Date())

---

Alias Utilities

alias()

Creates an alias for a table, useful for self-joins.

import { alias } from 'drizzle-orm/mysql-core';

const users = mysqlTable('users', {
  id: int().primaryKey(),
  name: varchar({ length: 255 }),
  managerId: int()
});

const managers = alias(users, 'managers');

// Self-join to get user with their manager
const result = await db.select({
  userName: users.name,
  managerName: managers.name
})
.from(users)
.leftJoin(managers, eq(users.managerId, managers.id));

---

Migration Utilities

These utilities are used internally by the migration system but can be useful for custom migration tools:

getTableName()

Gets the fully qualified table name.

import { getTableName } from 'drizzle-orm';
import { users } from './schema';

const tableName = getTableName(users);
console.log(tableName); // 'users' or 'schema.users' if schema is defined

---

Connection Utilities

withReplicas()

Configures read replicas for the database connection. See Database documentation for details.

---

Best Practices

Use typed models: Always use InferSelectModel and InferInsertModel to maintain type safety throughout your application.

Parameterized queries: Use the sql template tag with interpolation instead of sql.raw() to prevent SQL injection.

Raw SQL: Only use sql.raw() with static strings or trusted input. Never use it with user-provided data.

Schema introspection: Use getTableConfig() to programmatically inspect table definitions for documentation, validation, or code generation.