Drizzle ORM - Goodies

Type API

Use Drizzle’s type helpers to infer select and insert models from a SQLite table schema.

import { integer, text, sqliteTable } from 'drizzle-orm/sqlite-core';
import { type InferSelectModel, type InferInsertModel } from 'drizzle-orm'

const users = sqliteTable('users', {
  id: integer().primaryKey({ autoIncrement: true }),
  name: text().notNull(),
});

type SelectUser = typeof users.$inferSelect;
type InsertUser = typeof users.$inferInsert;

type SelectUserAlt = InferSelectModel<typeof users>;
type InsertUserAlt = InferInsertModel<typeof users>;

Logging

Enable default query logging by passing { logger: true } to drizzle.

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:

import { DefaultLogger, type 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(process.env.DB_URL, { logger });

You can also provide a custom logger.

import type { Logger } from 'drizzle-orm/logger';
import { drizzle } from 'drizzle-orm/...';

class MyLogger implements Logger {
  logQuery(query: string, params: unknown[]): void {
    console.log({ query, params });
  }
}

const db = drizzle(process.env.DB_URL, { logger: new MyLogger() });

Multi-project schema

Use sqliteTableCreator to customize table names when several projects share one database.

import { integer, text, sqliteTableCreator } from 'drizzle-orm/sqlite-core';

const sqliteTable = sqliteTableCreator((name) => `project1_${name}`);

const users = sqliteTable('users', {
  id: integer().primaryKey({ autoIncrement: true }),
  name: text().notNull(),
});

import { defineConfig } from "drizzle-kit";

export default defineConfig({
  schema: "./src/schema/*",
  out: "./drizzle",
  dialect: "sqlite", 
  dbCredentials: {
    url: process.env.DATABASE_URL,
  }
  tablesFilter: ["project1_*"],
});

CREATE TABLE `project1_users` (
	`id` integer PRIMARY KEY AUTOINCREMENT,
	`name` text NOT NULL
);

Printing SQL Query

Print generated SQL from a query with .toSQL().

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

If you have some complex queries to execute and drizzle-orm can’t handle them yet, you can use the db.run, db.all, db.values, db.get methods to execute raw parametrized queries.

import { sql } from 'drizzle-orm';

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)

Standalone Query Builder

Use the SQLite query builder without creating a database instance.

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();

Get Typed Columns

Use getColumns to get a typed column map, which is useful when excluding fields from a selection.

index.ts

schema.ts

import { getColumns } from "drizzle-orm";
import { users } from "./schema";

const { password, role, ...rest } = getColumns(users);

await db.select({ ...rest }).from(users);

import { integer, text, sqliteTable } from "drizzle-orm/sqlite-core";

export const users = sqliteTable("users", {
  id: integer().primaryKey({ autoIncrement: true }),
  name: text(),
  email: text(),
  password: text(),
  role: text().$type<"admin" | "customer">(),
});

Get Table Information

Use getTableConfig to inspect SQLite table metadata.

import { getTableConfig, sqliteTable } from 'drizzle-orm/sqlite-core';

export const table = sqliteTable(...);

const {
  columns,
  indexes,
  foreignKeys,
  checks,
  primaryKeys,
  name,
  uniqueConstraints,
} = getTableConfig(table);

Compare Object Types

Use is() instead of instanceof to check Drizzle object types.

import { Column, is } from 'drizzle-orm';

if (is(value, Column)) {
  // value's type is narrowed to Column
}

Mock Driver

Use drizzle.mock() when you need a typed database object without a real PostgreSQL connection.

import { drizzle } from "drizzle-orm/...";
import { relations } from "./relations";

const db = drizzle.mock({ relations });