Drizzle ORM - Goodies

Type API

To retrieve a type from your table schema for select and insert queries, you can make use of our type helpers.

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<typeof users>;
type InsertUser = InferInsertModel<typeof users>;

Logging

To enable default query logging, just pass { logger: true } to the drizzle initialization function:

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, 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:

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 customize table names.
It’s very useful when you need to keep schemas of different projects in one database.

import { int, text, singlestoreTableCreator } from 'drizzle-orm/singlestore-core';

const singlestoreTable = singlestoreTableCreator((name) => `project1_${name}`);

const users = singlestoreTable('users', {
  id: int('id').primaryKey(),
  name: text('name').notNull(),
});

import { defineConfig } from "drizzle-kit";

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

You can apply multiple or filters:

tablesFilter: ["project1_*", "project2_*"]

Printing SQL query

You can print SQL queries with db instance or by using standalone query builder.

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.

import { ..., SingleStoreRawQueryResult } 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.

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 columns

You can get a typed columns map, very useful when you need to omit certain columns upon selection.

IMPORTANT

getColumns available starting from drizzle-orm@1.0.0-beta.2(read more here)

If you are on pre-1 version(like 0.45.1) then use getTableColumns

index.ts

schema.ts

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

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

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

import { int, text, singlestoreTable } 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

import { getTableConfig, singlestoreTable } 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.

IMPORTANT

You should always use is() instead of instanceof

Few examples

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

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

const db = drizzle.mock();

you can provide schema if necessary for types

import { drizzle } from "drizzle-orm/...";
import * as schema from "./schema"

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