Relevant source files
The following files were used as context for generating this wiki page:
- drizzle-orm/type-tests/pg/tables.ts
- drizzle-orm/type-tests/mysql/tables.ts
- drizzle-orm/type-tests/geldb/tables.ts
- drizzle-orm/type-tests/singlestore/db.ts
- drizzle-orm/type-tests/sqlite/tables.ts
- drizzle-kit/src/api.ts
- drizzle-kit/src/cli/connections.ts
- drizzle-orm/type-tests/singlestore/tables.ts
- drizzle-orm/type-tests/pg/select.ts
- drizzle-orm/type-tests/kysely/index.ts
- drizzle-orm/type-tests/pg/db.ts
- drizzle-orm/src/mysql2/driver.ts
- drizzle-orm/src/postgres-js/driver.ts
- drizzle-orm/type-tests/mysql/db.ts
- drizzle-orm/type-tests/geldb/select.ts
- drizzle-orm/type-tests/mysql/select.ts
- drizzle-orm/src/node-postgres/driver.ts
- drizzle-orm/type-tests/mysql/db-rel.ts
- drizzle-orm/type-tests/singlestore/select.ts
- drizzle-orm/type-tests/sqlite/select.ts
- drizzle-orm/type-tests/geldb/db.ts
- drizzle-orm/type-tests/pg/db-rel.ts
- drizzle-orm/type-tests/sqlite/db.ts
- drizzle-kit/src/index.ts
- drizzle-orm/src/better-sqlite3/index.ts
- drizzle-orm/src/node-postgres/index.ts
Overview
Get up and running with Drizzle ORM Overview by connecting schema definitions, driver initialization, and query execution across multiple database engines. This guide provides a foundational walkthrough to help developers define type-safe database tables Type System Verification, establish client connections using drivers like node-postgres Drivers And Runtimes or mysql2, and execute complex queries with type-safe selection operators.
Sources: drizzle-orm/src/node-postgres/driver.ts:1-140, drizzle-orm/src/mysql2/driver.ts:1-183, drizzle-orm/type-tests/pg/tables.ts:963-1054
Declaring Tables Across Dialects
Dialect Table APIs
Drizzle ORM provides dialect-specific table builder functions—such as pgTable, mysqlTable, sqliteTable, singlestoreTable, and gelTable—to define schema structures with precise compile-time type verification Type System Verification. Each table builder accepts a table name, a column mapping object (or a callback builder function returning column definitions), and an optional extra-config callback for defining table-level constraints, primary keys, and unique indexes.
Table definitions can be specified using a direct column object or a getter function callback across all supported storage engines. Column configurations support modifiers such as .primaryKey(), .notNull(), .default(), and dialect-specific type constraints.
const cities1 = pgTable('cities_table', {
id: serial('id').primaryKey(),
name: text('name').notNull().primaryKey(),
role: text('role', { enum: ['admin', 'user'] }).default('user').notNull(),
population: integer('population').default(0),
});
const cities2 = pgTable('cities_table', ({ serial, text, integer }) => ({
id: serial('id').primaryKey(),
name: text('name').notNull().primaryKey(),
role: text('role', { enum: ['admin', 'user'] }).default('user').notNull(),
population: integer('population').default(0),
}));Sources: drizzle-orm/type-tests/pg/tables.ts:963-974
Similarly, SQLite and SingleStore support builders with advanced column mapping configurations, including explicit length constraints, enums, and generated expressions Dialects And Schema Schema Definition.
const test = sqliteTable('test', {
test1: text('test', { length: 255, enum: ['a', 'b', 'c'] as const }).notNull(),
test2: text('test', { length: 255, enum: ['a', 'b', 'c'] }).notNull(),
test3: text('test', { enum: ['a', 'b', 'c'] as const }).notNull(),
test4: text('test', { enum: ['a', 'b', 'c'] }).notNull(),
});Sources: drizzle-orm/type-tests/sqlite/tables.ts:450-455
const emailLog = singlestoreTable(
'email_log',
{
id: int('id', { unsigned: true }).autoincrement().notNull(),
clientId: int('id_client', { unsigned: true }),
receiverEmail: varchar('receiver_email', { length: 255 }).notNull(),
messageId: varchar('message_id', { length: 255 }),
contextId: int('context_id', { unsigned: true }),
contextType: singlestoreEnum('context_type', ['test']).$type<['test']>(),
action: varchar('action', { length: 80 }).$type<['test']>(),
events: json('events').$type<{ t: 'test' }[]>(),
createdAt: timestamp('created_at', { mode: 'string' }).defaultNow().notNull(),
updatedAt: timestamp('updated_at', { mode: 'string' }).defaultNow().onUpdateNow(),
},
(table) => {
return {
emailLogId: primaryKey({ columns: [table.id], name: 'email_log_id' }),
emailLogMessageIdUnique: unique('email_log_message_id_unique').on(table.messageId),
};
},
);Sources: drizzle-orm/type-tests/singlestore/tables.ts:737-757
Custom Types and Schema Scoping
Schema scoping functions such as pgSchema, mysqlSchema, and singlestoreSchema isolate tables within named database namespaces. Custom column types can be constructed using customType, specifying data types, driver transformations, and required configuration parameters.
Note
Custom type definitions require explicitly declaring whether configuration options are mandatory via configRequired: true or optional in the generic configuration structure.
const getUsersTable = <TSchema extends string>(schemaName: TSchema) => {
return pgSchema(schemaName).table('users', {
id: integer('id').primaryKey(),
name: text('name').notNull(),
});
};Sources: drizzle-orm/type-tests/pg/tables.ts:1088-1093
const customTextRequired = customType<{
data: string;
driverData: string;
config: { length: number };
configRequired: true;
}>({
dataType(config) {
Expect<Equal<{ length: number }, typeof config>>;
return `varchar(${config.length})`;
},
toDriver(value) {
Expect<Equal<string, typeof value>>();
return value;
},
fromDriver(value) {
Expect<Equal<string, typeof value>>();
return value;
},
});Sources: drizzle-orm/type-tests/pg/tables.ts:904-924
Initializing PostgreSQL and MySQL Clients
PostgreSQL Client Initialization
Establishing connections to PostgreSQL and MySQL databases requires instantiating the appropriate driver client and passing it to the dialect initialization function. Drizzle supports multiple initialization signatures for both Node.js pg, postgres-js, and mysql2 clients, accepting direct client instances, connection strings, or configuration objects containing client, connection, schema, casing, logger, cache, and driver-specific options.
The PostgreSQL driver supports connection setup via node-postgres and postgres-js. For node-postgres, the drizzle entrypoint accepts either a Pool or Client instance, a raw connection string, or a configuration object with connection or client keys drizzle-orm/src/node-postgres/driver.ts:91-140. Similarly, postgres-js accepts an Sql instance, a connection URL string, or a configuration object drizzle-orm/src/postgres-js/driver.ts:70-118.
import pg from 'pg';
import { drizzle } from '~/node-postgres/index.ts';
const { Client } = pg;
export const db = drizzle(new Client());Sources: drizzle-orm/type-tests/pg/db.ts:1-6
Note
When initializing postgres-js, Drizzle automatically overrides default date, JSON, and binary parsers (1184, 1082, 1083, 1114, 1182, 1185, 1115, 1231, 114, 3802) with transparent identity parsers to prevent unwanted timezone transformations drizzle-orm/src/postgres-js/driver.ts:28-36.
Sources: drizzle-orm/src/postgres-js/driver.ts:28-36
MySQL Client Initialization
MySQL configurations use the mysql2 driver, accepting callback or promise-based pools and connections. Callback pools passed to drizzle are automatically wrapped with .promise() to support async query execution drizzle-orm/src/mysql2/driver.ts:77.
import { createPool } from 'mysql2/promise';
import { drizzle } from '~/mysql2/index.ts';
const pool = createPool({});
export const db = drizzle(pool);Sources: drizzle-orm/type-tests/mysql/db.ts:1-6
Warning
When providing a relational schema object to MySQL drizzle configuration, you must explicitly specify a mode property set to either 'default' or 'planetscale'. Omitting the mode when a schema is present throws a runtime DrizzleError.
Sources: drizzle-orm/src/mysql2/driver.ts:80-86
Configuring Embedded and Alternative Engines
SQLite and Serverless Drivers
Embedded databases and alternative engines such as SQLite variants, SingleStore, and Gel use specialized driver initialization entrypoints. SQLite supports multiple runtimes including better-sqlite3, Bun's native SQLite, Cloudflare D1, and Durable Object storage. Each runtime requires its respective driver instance initialized before passing to the dialect-specific drizzle function.
import Database from 'better-sqlite3';
import { Database as BunDatabase } from 'bun:sqlite';
import { drizzle as drizzleBetterSqlite3 } from '~/better-sqlite3/index.ts';
import { drizzle as drizzleBun } from '~/bun-sqlite/index.ts';
import { drizzle as drizzleD1 } from '~/d1/index.ts';
import { drizzle as durableSqlite } from '~/durable-sqlite/index.ts';
const client = new Database(':memory:');
const bunClient = new BunDatabase(':memory:');
declare const d1: D1Database;
declare const durableSql: DurableObjectStorage;
export const db = drizzleBetterSqlite3(client);
export const bunDb = drizzleBun(bunClient);
export const d1Db = drizzleD1(d1);
export const durableSqliteDb = durableSqlite(durableSql);Sources: drizzle-orm/type-tests/sqlite/db.ts:1-16
Note
SQLite initialization functions accept either a direct client instance or configuration objects, allowing memory-backed databases or persistent storage bindings like Cloudflare D1 and Durable Objects to operate transparently within type definitions.
Sources: drizzle-orm/type-tests/sqlite/db.ts:8-16
Alternative Database Dialects
SingleStore and Gel use independent driver connectors and initialization modules. SingleStore integrates through mysql2/promise connection pools, accepting optional configuration objects with schema definitions. Gel connects via the official gel package client instance.
import { createPool } from 'mysql2/promise';
import { drizzle } from '~/singlestore/index.ts';
const pool = createPool({});
export const db = drizzle(pool);
{
drizzle(pool);
drizzle(pool, { schema: {} });
drizzle(pool, { schema: {} });
drizzle(pool, {});
}Sources: drizzle-orm/type-tests/singlestore/db.ts:1-13
import * as gel from 'gel';
import { drizzle } from '~/gel/index.ts';
export const db = drizzle(gel.createClient());Sources: drizzle-orm/type-tests/geldb/db.ts:1-4
Tip
SingleStore initialization permits passing an empty or populated schema object alongside the connection pool during setup, matching the configuration patterns used across relational database drivers.
Sources: drizzle-orm/type-tests/singlestore/db.ts:8-13
Executing Queries with Selection Operators
Query Construction and Joins
Selection queries start from a database table using .select() combined with .from(), and can be extended with inner joins, left joins, or full outer joins to combine related entities. Operators such as eq, and, and raw SQL fragments via the sql template tag define join conditions and filter predicates.
const join4 = await db
.select({
user: {
id: users.id,
},
city: {
id: cities.id,
},
class: classes,
friend: friends,
})
.from(users)
.innerJoin(cities, sql`${users.id} = ${cities.id}`)
.innerJoin(classes, sql`${cities.id} = ${classes.id}`)
.innerJoin(friends, sql`${friends.id} = ${users.id}`)
.where(sql`${users.age1} > 0`);Sources: drizzle-orm/type-tests/geldb/select.ts:817-832, drizzle-orm/type-tests/singlestore/select.ts:548-563
Warning
Attempting to chain multiple .where() clauses on the same select statement triggers a TypeScript compilation error because query builders restrict filtering to a single invocation per statement.
Sources: drizzle-orm/type-tests/geldb/select.ts:923-943, drizzle-orm/type-tests/singlestore/select.ts:657-677
Row Locking and Modifiers
Queries support row-level locking modifiers via the .for() method to handle concurrent transactions. Depending on the dialect and requirements, locking strategies can specify exclusivity, wait behavior, or lock skipping.
await db.select().from(users).for('update');
await db.select().from(users).for('no key update', { of: users });
await db.select().from(users).for('no key update', { of: users, skipLocked: true });
await db.select().from(users).for('share', { of: users, noWait: true });Sources: drizzle-orm/type-tests/geldb/select.ts:885-904, drizzle-orm/type-tests/singlestore/select.ts:615-617
Caution
Combining both skipLocked: true and noWait: true in the same .for() options object causes a TypeScript type error and is disallowed by locking constraint rules.
Sources: drizzle-orm/type-tests/geldb/select.ts:905-910, drizzle-orm/type-tests/singlestore/select.ts:619-623
Querying Relational Schemas with Relations
Relational Queries and Schema Definition
Relational queries access structured graphs of data via db.query.<tableName>.findMany() by bundling schema definitions directly into client initialization. When configuring a PostgreSQL or MySQL client via drizzle(client, { schema }), extractTablesRelationalConfig and createTableRelationsHelpers process the passed schema object to map relationships, table configurations, and name lookups.
import pg from 'pg';
import { drizzle } from '~/node-postgres/index.ts';
import * as schema from './tables-rel.ts';
const { Pool } = pg;
const pdb = new Pool({ connectionString: process.env['PG_CONNECTION_STRING'] });
const db = drizzle(pdb, { schema });Sources: drizzle-orm/src/node-postgres/driver.ts:67-77, drizzle-orm/type-tests/pg/db-rel.ts:1-10
Note
MySQL2 database instances require config.mode to be explicitly set to either 'planetscale' or 'default' when a schema is provided, otherwise an error is thrown during initialization.
Filtering, Pagination, and Column Selection
The relational query builder accepts where, limit, orderBy, columns, and with options to filter records and shape nested tree structures. The columns configuration object supports boolean values or undefined / false flags to include or exclude specific fields.
const result = await db.query.users.findMany({
where: (users, { sql }) => sql`char_length(${users.name} > 1)`,
limit: sql.placeholder('l'),
orderBy: (users, { asc, desc }) => [asc(users.name), desc(users.id)],
with: {
posts: {
columns: {
id: false,
title: undefined,
},
extras: {
lower: sql<string>`lower(${schema.posts.title})`.as('lower_name'),
},
},
},
});Sources: drizzle-orm/type-tests/pg/db-rel.ts:13-24, drizzle-orm/type-tests/pg/db-rel.ts:92-107
Tip
The extras property allows appending computed SQL expressions and custom aliases to query selections without modifying the underlying table schema definition.
Sources: drizzle-orm/type-tests/pg/db-rel.ts:99-104, drizzle-orm/type-tests/mysql/db-rel.ts:99-101
Applying Migrations with Kit Entrypoints
Configuration and DefineConfig
The drizzle-kit entrypoint provides the defineConfig utility to validate and structure options for schema management, migrations, and database introspection Configuration and Validations. The configuration object governs dialect selection, schema paths, migration output directories, and driver-specific credentials across multiple engines.
import { defineConfig } from 'drizzle-kit';
export default defineConfig({
dialect: 'postgresql',
schema: './src/schema.ts',
out: './drizzle',
breakpoints: true,
strict: true,
verbose: true,
});Sources: drizzle-kit/src/index.ts:109-132, drizzle-kit/src/index.ts:347-349
Dialect Support and Driver Options
Drizzle-kit supports distinct database dialects alongside specialized driver integrations. The configuration schema enforces valid option combinations for connection parameters, SSL settings, and migration metadata tables.
| Dialect | Required Credentials / URL | Supported Drivers / Options |
|---|---|---|
postgresql | url or host, database, optional ssl | aws-data-api, pglite |
mysql | url or host, database, optional ssl | — |
sqlite | url | d1-http, expo, durable-sqlite |
turso | url, optional authToken | — |
singlestore | url or host, database, optional ssl | — |
gel | optional url or host fields, tlsSecurity | — |
Sources: drizzle-kit/src/index.ts:133-255
Note
The breakpoints option defaults to true and inserts SQL statement breakpoints into generated migrations. This is required for engines like MySQL, SQLite, and SingleStore that do not support multiple DDL alteration statements within a single transaction.
Sources: drizzle-kit/src/index.ts:65-71, drizzle-kit/src/index.ts:113
Related
On This Page
OverviewDeclaring Tables Across DialectsDialect Table APIsCustom Types and Schema ScopingInitializing PostgreSQL and MySQL ClientsPostgreSQL Client InitializationMySQL Client InitializationConfiguring Embedded and Alternative EnginesSQLite and Serverless DriversAlternative Database DialectsExecuting Queries with Selection OperatorsQuery Construction and JoinsRow Locking and ModifiersQuerying Relational Schemas with RelationsRelational Queries and Schema DefinitionFiltering, Pagination, and Column SelectionApplying Migrations with Kit EntrypointsConfiguration and DefineConfigDialect Support and Driver OptionsRelated