import Tab from '@mdx/Tab.astro'; import Tabs from '@mdx/Tabs.astro'; import Callout from '@mdx/Callout.astro'; import Section from '@mdx/Section.astro'; import CodeTabs from '@mdx/CodeTabs.astro'; ## Type API To retrieve a type from your table schema for `select` and `insert` queries, you can make use of our type helpers. ```ts import { serial, text, pgTable } from 'drizzle-orm/pg-core'; import { type InferSelectModel, type InferInsertModel } from 'drizzle-orm' const users = pgTable('users', { id: serial('id').primaryKey(), name: text('name').notNull(), }); type SelectUser = typeof users.$inferSelect; type InsertUser = typeof users.$inferInsert; // or type SelectUser = typeof users._.$inferSelect; type InsertUser = typeof users._.$inferInsert; // or type SelectUser = InferSelectModel; type InsertUser = InferInsertModel; ``` ```ts import { int, text, mysqlTable } from 'drizzle-orm/mysql-core'; import { type InferSelectModel, type InferInsertModel } from 'drizzle-orm' const users = mysqlTable('users', { id: int('id').primaryKey(), name: text('name').notNull(), }); type SelectUser = typeof users.$inferSelect; type InsertUser = typeof users.$inferInsert; // or type SelectUser = typeof users._.$inferSelect; type InsertUser = typeof users._.$inferInsert; // or type SelectUser = InferSelectModel; type InsertUser = InferInsertModel; ``` ```ts import { int, text, sqliteTable } from 'drizzle-orm/sqlite-core'; import { type InferSelectModel, type InferInsertModel } from 'drizzle-orm' const users = sqliteTable('users', { id: int('id').primaryKey(), name: text('name').notNull(), }); type SelectUser = typeof users.$inferSelect; type InsertUser = typeof users.$inferInsert; // or type SelectUser = typeof users._.$inferSelect; type InsertUser = typeof users._.$inferInsert; // or type SelectUser = InferSelectModel; type InsertUser = InferInsertModel; ``` ```ts import { int, text, singlestoreTable } from 'drizzle-orm/singlestore-core'; import { type InferSelectModel, type InferInsertModel } from 'drizzle-orm' const users = singlestoreTable('users', { id: int('id').primaryKey(), name: text('name').notNull(), }); type SelectUser = typeof users.$inferSelect; type InsertUser = typeof users.$inferInsert; // or type SelectUser = typeof users._.$inferSelect; type InsertUser = typeof users._.$inferInsert; // or type SelectUser = InferSelectModel; type InsertUser = InferInsertModel; ``` ## Logging To enable default query logging, just pass `{ logger: true }` to the `drizzle` initialization function: ```typescript copy import { drizzle } from 'drizzle-orm/...'; // driver specific const db = drizzle({ logger: true }); ``` You can change the logs destination by creating a `DefaultLogger` instance and providing a custom `writer` to it: ```typescript copy import { DefaultLogger, LogWriter } from 'drizzle-orm/logger'; import { drizzle } from 'drizzle-orm/...'; // driver specific class MyLogWriter implements LogWriter { write(message: string) { // Write to file, stdout, etc. } } const logger = new DefaultLogger({ writer: new MyLogWriter() }); const db = drizzle({ logger }); ``` You can also create a custom logger: ```typescript copy import { Logger } from 'drizzle-orm/logger'; import { drizzle } from 'drizzle-orm/...'; // driver specific class MyLogger implements Logger { logQuery(query: string, params: unknown[]): void { console.log({ query, params }); } } const db = drizzle({ logger: new MyLogger() }); ``` ## Multi-project schema **Table creator** API lets you define customise table names. It's very useful when you need to keep schemas of different projects in one database. ```ts {3} import { serial, text, pgTableCreator } from 'drizzle-orm/pg-core'; const pgTable = pgTableCreator((name) => `project1_${name}`); const users = pgTable('users', { id: serial('id').primaryKey(), name: text('name').notNull(), }); ``` ```ts {3} import { int, text, mysqlTableCreator } from 'drizzle-orm/mysql-core'; const mysqlTable = mysqlTableCreator((name) => `project1_${name}`); const users = mysqlTable('users', { id: int('id').primaryKey(), name: text('name').notNull(), }); ``` ```ts {3} import { int, text, sqliteTableCreator } from 'drizzle-orm/sqlite-core'; const sqliteTable = sqliteTableCreator((name) => `project1_${name}`); const users = sqliteTable('users', { id: int('id').primaryKey(), name: text('name').notNull(), }); ``` ```ts {3} import { int, text, singlestoreTableCreator } from 'drizzle-orm/singlestore-core'; const mysqlTable = singlestoreTableCreator((name) => `project1_${name}`); const users = singlestoreTable('users', { id: int('id').primaryKey(), name: text('name').notNull(), }); ``` ```ts {10} import { defineConfig } from "drizzle-kit"; export default defineConfig({ schema: "./src/schema/*", out: "./drizzle", dialect: "mysql", dbCredentials: { url: process.env.DATABASE_URL, } tablesFilter: ["project1_*"], }); ``` You can apply multiple `or` filters: ```ts tablesFilter: ["project1_*", "project2_*"] ``` ## Printing SQL query You can print SQL queries with `db` instance or by using **[`standalone query builder`](#standalone-query-builder)**. ```typescript copy const query = db .select({ id: users.id, name: users.name }) .from(users) .groupBy(users.id) .toSQL(); // query: { sql: 'select 'id', 'name' from 'users' group by 'users'.'id'', params: [], } ``` ## Raw SQL queries execution If you have some complex queries to execute and `drizzle-orm` can't handle them yet, you can use the `db.execute` method to execute raw `parametrized` queries. ```ts const statement = sql`select * from ${users} where ${users.id} = ${userId}`; const res: postgres.RowList[]> = await db.execute(statement) ``` ```typescript copy import { ..., MySqlQueryResult } from "drizzle-orm/mysql2"; const statement = sql`select * from ${users} where ${users.id} = ${userId}`; const res: MySqlRawQueryResult = await db.execute(statement); ``` ```ts const statement = sql`select * from ${users} where ${users.id} = ${userId}`; const res: unknown[] = db.all(statement) const res: unknown = db.get(statement) const res: unknown[][] = db.values(statement) const res: Database.RunResult = db.run(statement) ``` ```typescript copy import { ..., SingleStoreQueryResult } from "drizzle-orm/singlestore"; const statement = sql`select * from ${users} where ${users.id} = ${userId}`; const res: SingleStoreRawQueryResult = await db.execute(statement); ``` ## Standalone query builder Drizzle ORM provides a standalone query builder that allows you to build queries without creating a database instance and get generated SQL. ```typescript copy import { QueryBuilder } from 'drizzle-orm/pg-core'; const qb = new QueryBuilder(); const query = qb.select().from(users).where(eq(users.name, 'Dan')); const { sql, params } = query.toSQL(); ``` ```typescript copy import { QueryBuilder } from 'drizzle-orm/mysql-core'; const qb = new QueryBuilder(); const query = qb.select().from(users).where(eq(users.name, 'Dan')); const { sql, params } = query.toSQL(); ``` ```typescript copy import { QueryBuilder } from 'drizzle-orm/sqlite-core'; const qb = new QueryBuilder(); const query = qb.select().from(users).where(eq(users.name, 'Dan')); const { sql, params } = query.toSQL(); ``` ```typescript copy import { QueryBuilder } from 'drizzle-orm/singlestore-core'; const qb = new QueryBuilder(); const query = qb.select().from(users).where(eq(users.name, 'Dan')); const { sql, params } = query.toSQL(); ``` ## Get typed table columns You can get a typed table columns map, very useful when you need to omit certain columns upon selection. ```ts import { getTableColumns } from "drizzle-orm"; import { user } from "./schema"; const { password, role, ...rest } = getTableColumns(user); await db.select({ ...rest }).from(users); ``` ```ts import { serial, text, pgTable } from "drizzle-orm/pg-core"; export const user = pgTable("user", { id: serial("id").primaryKey(), name: text("name"), email: text("email"), password: text("password"), role: text("role").$type<"admin" | "customer">(), }); ``` ```ts import { getTableColumns } from "drizzle-orm"; import { user } from "./schema"; const { password, role, ...rest } = getTableColumns(user); await db.select({ ...rest }).from(users); ``` ```ts import { int, text, mysqlTable } from "drizzle-orm/mysql-core"; export const user = mysqlTable("user", { id: int("id").primaryKey().autoincrement(), name: text("name"), email: text("email"), password: text("password"), role: text("role").$type<"admin" | "customer">(), }); ``` ```ts import { getTableColumns } from "drizzle-orm"; import { user } from "./schema"; const { password, role, ...rest } = getTableColumns(user); await db.select({ ...rest }).from(users); ``` ```ts import { integer, text, sqliteTable } from "drizzle-orm/sqlite-core"; export const user = sqliteTable("user", { id: integer("id").primaryKey({ autoIncrement: true }), name: text("name"), email: text("email"), password: text("password"), role: text("role").$type<"admin" | "customer">(), }); ``` ```ts import { getTableColumns } from "drizzle-orm"; import { user } from "./schema"; const { password, role, ...rest } = getTableColumns(user); await db.select({ ...rest }).from(users); ``` ```ts import { int, text, mysqlTable } from "drizzle-orm/singlestore-core"; export const user = singlestoreTable("user", { id: int("id").primaryKey().autoincrement(), name: text("name"), email: text("email"), password: text("password"), role: text("role").$type<"admin" | "customer">(), }); ``` ## Get table information ```ts copy import { getTableConfig, pgTable } from 'drizzle-orm/pg-core'; export const table = pgTable(...); const { columns, indexes, foreignKeys, checks, primaryKeys, name, schema, } = getTableConfig(table); ``` ```ts copy import { getTableConfig, mysqlTable } from 'drizzle-orm/mysql-core'; export const table = mysqlTable(...); const { columns, indexes, foreignKeys, checks, primaryKeys, name, schema, } = getTableConfig(table); ``` ```ts copy import { getTableConfig, sqliteTable } from 'drizzle-orm/sqlite-core'; export const table = sqliteTable(...); const { columns, indexes, foreignKeys, checks, primaryKeys, name, schema, } = getTableConfig(table); ``` ```ts copy import { getTableConfig, mysqlTable } from 'drizzle-orm/singlestore-core'; export const table = singlestoreTable(...); const { columns, indexes, checks, primaryKeys, name, schema, } = getTableConfig(table); ``` ## Compare objects types (instanceof alternative) You can check if an object is of a specific Drizzle type using the `is()` function. You can use it with any available type in Drizzle. You should always use `is()` instead of `instanceof` **Few examples** ```ts import { Column, is } from 'drizzle-orm'; if (is(value, Column)) { // value's type is narrowed to Column } ``` ### Mock Driver This API is a successor to an undefined `drizzle({} as any)` API which we've used internally in Drizzle tests and rarely recommended to external developers. We decided to build and expose a proper API, every `drizzle` driver now has `drizzle.mock()`: ```ts import { drizzle } from "drizzle-orm/node-postgres"; const db = drizzle.mock(); ``` you can provide schema if necessary for types ```ts import { drizzle } from "drizzle-orm/node-postgres"; import * as schema from "./schema" const db = drizzle.mock({ schema }); ```

type-helpers-and-utilities