drizzle-orm Documentation

const customSerial = customType<{ data: number; notNull: true; default: true }>(
  {
    dataType() {
      return 'serial';
    },
  },
);

const customText = customType<{ data: string }>({
  dataType() {
    return 'text';
  },
});

const customBoolean = customType<{ data: boolean }>({
  dataType() {
    return 'boolean';
  },
});

const customJsonb = (name: string) =>
  customType<{ data: TData; driverData: string }>({
    dataType() {
      return 'jsonb';
    },
    toDriver(value: TData): string {
      return JSON.stringify(value);
    },
  })(name);

const customTimestamp = customType<
  {
    data: Date;
    driverData: string;
    config: { withTimezone: boolean; precision?: number };
  }
>({
  dataType(config) {
    const precision = typeof config.precision !== 'undefined'
      ? ` (${config.precision})`
      : '';
    return `timestamp${precision}${
      config.withTimezone ? ' with time zone' : ''
    }`;
  },
  fromDriver(value: string): Date {
    return new Date(value);
  },
});

const usersTable = pgTable('users', {
  id: customSerial('id').primaryKey(),
  name: customText('name').notNull(),
  verified: customBoolean('verified').notNull().default(false),
  jsonb: customJsonb<string[]>('jsonb'),
  createdAt: customTimestamp('created_at', { withTimezone: true }).notNull()
    .default(sql`now()`),
});

const customSerial = customType<{ data: number; notNull: true; default: true }>(
  {
    dataType() {
      return 'serial';
    },
  },
);

const customText = customType<{ data: string }>({
  dataType() {
    return 'text';
  },
});

const customBoolean = customType<{ data: boolean }>({
  dataType() {
    return 'boolean';
  },
  fromDriver(value) {
    if (typeof value === 'boolean') {
      return value;
    }
    return value === 1;
  },
});

const customJson = (name: string) =>
  customType<{ data: TData; driverData: string }>({
    dataType() {
      return 'json';
    },
    toDriver(value: TData): string {
      return JSON.stringify(value);
    },
  })(name);

const customTimestamp = customType<
  { data: Date; driverData: string; config: { fsp: number } }
>({
  dataType(config) {
    const precision = typeof config.fsp !== 'undefined'
      ? ` (${config.fsp})`
      : '';
    return `timestamp${precision}`;
  },
  fromDriver(value: string): Date {
    return new Date(value);
  },
});

const usersTable = mysqlTable('userstest', {
  id: customSerial('id').primaryKey(),
  name: customText('name').notNull(),
  verified: customBoolean('verified').notNull().default(false),
  jsonb: customJson<string[]>('jsonb'),
  createdAt: customTimestamp('created_at', { fsp: 2 }).notNull().default(
    sql`now()`,
  ),
});

export type CustomTypeValues = {
  /**
   * Required type for custom column, that will infer proper type model
   *
   * Examples:
   *
   * If you want your column to be `string` type after selecting/or on inserting - use `data: string`. Like `text`, `varchar`
   *
   * If you want your column to be `number` type after selecting/or on inserting - use `data: number`. Like `integer`
   */
  data: unknown;

  /**
   * Type helper, that represents what type database driver is accepting for specific database data type
   */
  driverData?: unknown;

  /**
   * What config type should be used for {@link CustomTypeParams} `dataType` generation
   */
  config?: unknown;

  /**
   * Whether the config argument should be required or not
   * @default false
   */
  configRequired?: boolean;

  /**
   * If your custom data type should be notNull by default you can use `notNull: true`
   *
   * @example
   * const customSerial = customType<{ data: number, notNull: true, default: true }>({
   *    dataType() {
   *      return 'serial';
   *    },
   * });
   */
  notNull?: boolean;

  /**
   * If your custom data type has default you can use `default: true`
   *
   * @example
   * const customSerial = customType<{ data: number, notNull: true, default: true }>({
   *    dataType() {
   *      return 'serial';
   *    },
   * });
   */
  default?: boolean;
};

export interface CustomTypeParams<T extends Partial<CustomTypeValues>> {
  /**
   * Database data type string representation, that is used for migrations
   * @example
   * ```
   * `jsonb`, `text`
   * ```
   *
   * If database data type needs additional params you can use them from `config` param
   * @example
   * ```
   * `varchar(256)`, `numeric(2,3)`
   * ```
   *
   * To make `config` be of specific type please use config generic in {@link CustomTypeValues}
   *
   * @example
   * Usage example
   * ```
   *   dataType() {
   *     return 'boolean';
   *   },
   * ```
   * Or
   * ```
   *   dataType(config) {
   *     return typeof config.length !== 'undefined' ? `varchar(${config.length})` : `varchar`;
   *   }
   * ```
   */
  dataType: (config: T['config'] | (Equal<T['configRequired'], true> extends true ? never : undefined)) => string;

  /**
   * Optional mapping function, between user input and driver
   * @example
   * For example, when using jsonb we need to map JS/TS object to string before writing to database
   * ```
   * toDriver(value: TData): string {
   *   return JSON.stringify(value);
   * }
   * ```
   */
  toDriver?: (value: T['data']) => T['driverData'] | SQL;

  /**
   * Optional mapping function, that is responsible for data mapping from database to JS/TS code
   * @example
   * For example, when using timestamp we need to map string Date representation to JS Date
   * ```
   * fromDriver(value: string): Date {
   *  return new Date(value);
   * },
   * ```
   */
  fromDriver?: (value: T['driverData']) => T['data'];
}

export class PgTextBuilder
  extends PgColumnBuilder<
    ColumnBuilderConfig<{ data: TData; driverParam: string }>
  >
{
  

  build<TTableName extends string>(
    table: AnyPgTable<{ name: TTableName }>,
  ): PgText<TTableName, TData> {
    return new PgText(table, this.config);
  }
}

override mapToDriverValue(value: TData): string {
  return JSON.stringify(value);
}

override mapFromDriverValue(value: number | string): number {
  if (typeof value === 'string') {
    return parseInt(value);
  }
  return value;
}

export class PgText<TTableName extends string, TData extends string>
  extends PgColumn<ColumnConfig<{ tableName: TTableName; data: TData; driverParam: string }>> {
  

  constructor(table: AnyPgTable<{ name: TTableName }>, builder: PgTextBuilder['config']) {
    super(table, builder);
  }

  getSQLType(): string {
    return 'text';
  }

  override mapFromDriverValue(value: string): TData {
    return value as TData
  }

  override mapToDriverValue(value: TData): string {
    return value
  }
}

import { ColumnConfig, ColumnBuilderConfig } from 'drizzle-orm';
import { AnyPgTable } from 'drizzle-orm/pg-core';

import { PgColumn, PgColumnBuilder } from './common';

export class PgTextBuilder
  extends PgColumnBuilder<
    ColumnBuilderConfig<{ data: TData; driverParam: string }>
  >
{
  

  build<TTableName extends string>(
    table: AnyPgTable<{ name: TTableName }>,
  ): PgText<TTableName, TData> {
    return new PgText(table, this.config);
  }
}

export class PgText<TTableName extends string, TData extends string>
  extends PgColumn<
    ColumnConfig<{ tableName: TTableName; data: TData; driverParam: string }>
  >
{
  

  constructor(
    table: AnyPgTable<{ name: TTableName }>,
    builder: PgTextBuilder['config'],
  ) {
    super(table, builder);
  }

  getSQLType(): string {
    return 'text';
  }
}

export function text<T extends string = string>(
  name: string,
): PgTextBuilder<T> {
  return new PgTextBuilder(name);
}

export class PgCITextBuilder extends PgColumnBuilder<
  PgColumnBuilderHKT,
  ColumnBuilderConfig<{ data: TData; driverParam: string }>
> {
  protected $pgColumnBuilderBrand: string = 'PgCITextBuilder';
  
  build<TTableName extends string>(table: AnyPgTable<{ name: TTableName }>): PgCIText<TTableName, TData> {
    return new PgCIText(table, this.config);
  }
}

export class PgCIText<TTableName extends string, TData extends string>
  extends PgColumn>
{
  

  constructor(table: AnyPgTable<{ name: TTableName }>, builder: PgCITextBuilder['config']) {
    super(table, builder);
  }

  getSQLType(): string {
    return 'citext';
  }
}

export function citext<T extends string = string>(name: string): PgCITextBuilder<T> {
  return new PgCITextBuilder(name);
}

const table = pgTable('table', {
  id: integer('id').primaryKey(),
  ciname: citext('ciname')
})

const users = pgTable('users', {
  id: serial('id').primaryKey(),
  firstName: text('first_name').notNull(),
  lastName: text('last_name'),
  cityId: int('city_id').references(() => cities.id),
});

const cities = pgTable('cities', {
  id: serial('id').primaryKey(),
  name: text('name').notNull(),
});

select
  cities.id as city_id,
  cities.name as city_name,
  users.id as user_id,
  users.first_name,
  users.last_name
from cities
left join users on users.city_id = cities.id

const rows = await db
  .select({
      cityId: cities.id,
      cityName: cities.name,
      userId: users.id,
      firstName: users.firstName,
      lastName: users.lastName,
    })
  .from(cities)
  .leftJoin(users, eq(users.cityId, cities.id));

{
  cityId: number;
  cityName: string;
  userId: number | null;
  firstName: string | null;
  lastName: string | null;
}[]

const rows = await db
  .select({
    cityId: cities.id,
    cityName: cities.name,
    user: {
      id: users.id,
      firstName: users.firstName,
      lastName: users.lastName,
    },
  })
  .from(cities)
  .leftJoin(users, eq(users.cityId, cities.id));

{
  cityId: number;
  cityName: string;
  user: {
    id: number;
    firstName: string;
    lastName: string | null;
  } | null;
}

.select({
  city: {
    id: cities.id,
    name: cities.name,
  },
  user: {
    id: users.id,
    firstName: users.firstName,
    lastName: users.lastName,
  },
})

{
  city: {
    id: number;
    name: string;
  };
  user: {
    id: number;
    firstName: string;
    lastName: string | null;
  } | null;
}

const rows = await db.select().from(cities).leftJoin(users, eq(users.cityId, cities.id));

{
  cities: {
    id: number;
    name: string;
  };
  users: {
    id: number;
    firstName: string;
    lastName: string | null;
    cityId: number | null;
  } | null;
}[]

.select({
  cities, // shorthand for "cities: cities", the key can be anything
  user: {
    firstName: users.firstName,
  },
})

{
  cities: {
    id: number;
    name: string;
  };
  user: {
    firstName: string;
  } | null;
}

.select({
  id: cities.id,
  cityAndUser: {
    cityName: cities.name,
    userId: users.id,
    firstName: users.firstName,
    lastName: users.lastName,
  }
})

{
  id: number;
  cityAndUser: {
    cityName: string;
    userId: number | null;
    firstName: string | null;
    lastName: string | null;
  };
}

import { InferModel } from 'drizzle-orm';

type User = InferModel<typeof users>;
type City = InferModel<typeof cities>;

const rows = await db
  .select({
    city: cities,
    user: users,
  })
  .from(cities)
  .leftJoin(users, eq(users.cityId, cities.id));

const result = rows.reduce<Record<number, { city: City; users: User[] }>>(
  (acc, row) => {
    const city = row.city;
    const user = row.user;

    if (!acc[city.id]) {
      acc[city.id] = { city, users: [] };
    }

    if (user) {
      acc[city.id].users.push(user);
    }

    return acc;
  },
  {},
);

import { pgTable, getTableConfig } from 'drizzle-orm/pg-core';

const table = pgTable(...);

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

import { pgTable, getTableColumns } from 'drizzle-orm/pg-core';

const table = pgTable('table', {
  id: integer('id').primaryKey(),
  name: text('name'),
});

const columns/*: { id: ..., name: ... } */ = getTableColumns(table);

import { pgEnum, pgTable, serial, text, timestamp } from 'drizzle-orm/pg-core';
import { createInsertSchema, createSelectSchema } from 'drizzle-arktype';
import { type } from 'arktype';

const users = pgTable('users', {
	id: serial('id').primaryKey(),
	name: text('name').notNull(),
	email: text('email').notNull(),
	role: text('role', { enum: ['admin', 'user'] }).notNull(),
	createdAt: timestamp('created_at').notNull().defaultNow(),
});

// Schema for inserting a user - can be used to validate API requests
const insertUserSchema = createInsertSchema(users);

// Schema for updating a user - can be used to validate API requests
const updateUserSchema = createUpdateSchema(users);

// Schema for selecting a user - can be used to validate API responses
const selectUserSchema = createSelectSchema(users);

// Overriding the fields
const insertUserSchema = createInsertSchema(users, {
	role: type('string'),
});

// Refining the fields - useful if you want to change the fields before they become nullable/optional in the final schema
const insertUserSchema = createInsertSchema(users, {
	id: (schema) => schema.atLeast(1),
	role: type('string'),
});

// Usage

const isUserValid = parse(insertUserSchema, {
	name: 'John Doe',
	email: '[email protected]',
	role: 'admin',
});

// src/db/schema.ts

import { integer, pgTable, serial, text, varchar } from "drizzle-orm/pg-core";

const users = pgTable("users", {
    id: serial("id").primaryKey(),
    fullName: varchar("full_name", { length: 256 }),
  }, (table) => ({
    nameIdx: index("name_idx", table.fullName),
  })
);

export const authOtp = pgTable("auth_otp", {
  id: serial("id").primaryKey(),
  phone: varchar("phone", { length: 256 }),
  userId: integer("user_id").references(() => users.id),
});

CREATE TABLE IF NOT EXISTS auth_otp (
 "id" SERIAL PRIMARY KEY,
 "phone" character varying(256),
 "user_id" INT
);

CREATE TABLE IF NOT EXISTS users (
 "id" SERIAL PRIMARY KEY,
 "full_name" character varying(256)
);

DO $$ BEGIN
 ALTER TABLE auth_otp ADD CONSTRAINT auth_otp_user_id_fkey FOREIGN KEY ("user_id") REFERENCES users(id);
EXCEPTION
 WHEN duplicate_object THEN null;
END $$;

CREATE INDEX IF NOT EXISTS users_full_name_index ON users (full_name);

npm install -D drizzle-kit

// package.json
{
 "scripts": {
  "generate": "drizzle-kit generate --out migrations-folder --schema src/db/schema.ts"
 }
}

npm run generate

import Knex from 'knex';
import { pgTable, serial, text } from 'drizzle-orm/pg-core';
// This line is important - it allows you to use the Knexify type
import 'drizzle-orm/knex';

const test = pgTable('test', {
  id: serial('id').primaryKey(),
  name: text('name').notNull(),
});

declare module 'knex/types/tables' {
  interface Tables {
    test: Knexify<typeof test>;
  }
}

const db = Knex({});

const result/*: { id: number, name: string }[] */ = db('test').select();

import { Kysely, PostgresDialect } from 'kysely';
import { Pool } from 'pg';
import { Kyselify } from 'drizzle-orm/kysely';
import { pgTable, serial, text } from 'drizzle-orm/pg-core';

const test = pgTable('test', {
  id: serial('id').primaryKey(),
  name: text('name').notNull(),
});

interface Database {
  test: Kyselify<typeof test>;
}

const db = new Kysely<Database>({
  dialect: new PostgresDialect({
    pool: new Pool(),
  }),
});

const result/*: { id: number, name: string }[] */ = db.selectFrom('test').selectAll().execute();

├── src/
│   ├── content/
│   │   └── docs

├── src/
│   ├──data/
│   │   └── announcements

├── src/
│   ├──data/
│   │   └── roadmap.md

├── src/
│   ├──data/
│   │   └── shipping.yaml

progress: number
weeks:
  - date:
      start: "YYYY-MM-DD"
    details:
      - string

import { int4, cockroachTable, text } from 'drizzle-orm/cockroach-core';
import { createSelectSchema } from 'drizzle-orm/arktype';
import type { ArkErrors } from 'arktype';

const users = cockroachTable('users', {
  id: int4().primaryKey().generatedAlwaysAsIdentity(),
  name: text().notNull(),
  age: int4().notNull()
});

const userSelectSchema = createSelectSchema(users);

const rows = await db.select({ id: users.id, name: users.name }).from(users).limit(1);
const parsed: ArkErrors | { id: number; name: string; age: number } = userSelectSchema(rows[0]); // Error: `age` is not returned in the above query

const rows = await db.select().from(users).limit(1);
const parsed: ArkErrors | { id: number; name: string; age: number } = userSelectSchema(rows[0]); // Will parse successfully

import { cockroachEnum, cockroachView } from 'drizzle-orm/cockroach-core';
import { gt } from 'drizzle-orm';
import { createSelectSchema } from 'drizzle-orm/arktype';
import type { ArkErrors } from 'arktype';

const roles = cockroachEnum('roles', ['admin', 'basic']);
const rolesSchema = createSelectSchema(roles);
const parsed: 'admin' | 'basic' = rolesSchema(...);

const usersView = cockroachView('users_view').as((qb) => qb.select().from(users).where(gt(users.age, 18)));
const usersViewSchema = createSelectSchema(usersView);
const parsed: ArkErrors | { id: number; name: string; age: number } = usersViewSchema(...);

import { int4, cockroachTable, text } from 'drizzle-orm/cockroach-core';
import { createInsertSchema } from 'drizzle-orm/arktype';
import { ArkErrors } from "arktype";

const users = cockroachTable('users', {
  id: int4().primaryKey().generatedAlwaysAsIdentity(),
  name: text().notNull(),
  age: int4().notNull()
});

const userInsertSchema = createInsertSchema(users);

const user = { name: 'John' };
const parsed: ArkErrors | { name: string, age: number } = userInsertSchema(user); // Error: `age` is not defined

const user = { name: 'Jane', age: 30 };
const parsed: ArkErrors | { name: string, age: number } = userInsertSchema(user); // Will parse successfully

if (parsed instanceof ArkErrors) {
  console.error(parsed.summary);
  process.exit(1);
}

await db.insert(users).values(parsed);

import { int4, cockroachTable, text } from 'drizzle-orm/cockroach-core';
import { createUpdateSchema } from 'drizzle-orm/arktype';
import { eq } from "drizzle-orm";
import { ArkErrors } from 'arktype';

const users = cockroachTable('users', {
  id: int4().primaryKey().generatedAlwaysAsIdentity(),
  name: text().notNull(),
  age: int4().notNull()
});

const userUpdateSchema = createUpdateSchema(users);

const user = { age: 35 };
const parsed: ArkErrors | { name?: string | undefined, age?: number | undefined } = userUpdateSchema(user); // Will parse successfully

if (parsed instanceof ArkErrors) {
  console.error(parsed.summary);
  process.exit(1);
}

await db.update(users).set(parsed).where(eq(users.name, 'Jane'));

import { int4, jsonb, cockroachTable, text } from 'drizzle-orm/cockroach-core';
import { createSelectSchema } from 'drizzle-orm/arktype';
import { ArkErrors, type } from 'arktype';

const users = cockroachTable('users', {
  id: int4().primaryKey().generatedAlwaysAsIdentity(),
  name: text().notNull(),
  bio: text(),
  preferences: jsonb()
});

const userSelectSchema = createSelectSchema(users, {
  name: (schema) => schema.atMostLength(20), // Extends schema
  bio: () => type.string.atMostLength(2000), // Extends schema before becoming nullable/optional
  preferences: type({ theme: 'string' }) // Overwrites the field, including its nullability
});

const parsed: ArkErrors | {
  id: number;
  name: string,
  bio: string | null;
  preferences: {
    theme: string;
  };
} = userSelectSchema(...);

import { upstashCache } from "drizzle-orm/cache/upstash";
import { drizzle } from "drizzle-orm/...";

const db = drizzle(process.env.DB_URL!, {
  cache: upstashCache({ token: process.env.UPSTASH_TOKEN, url: process.env.UPSTASH_URL }),
});

import { upstashCache } from "drizzle-orm/cache/upstash";
import { drizzle } from "drizzle-orm/...";

const db = drizzle(process.env.DB_URL!, {
  cache: upstashCache({
    // 👇 Redis credentials (optional — can also be pulled from env vars)
    url: '',
    token: '',

    // 👇 Enable caching for all queries by default (optional)
    global: true,

    // 👇 Default cache behavior (optional)
    config: { ex: 60 }
  })
});

export type CacheConfig = {
  /**
   * Expiration in seconds (positive integer)
   */
  ex?: number;
  /**
   * Set an expiration (TTL or time to live) on one or more fields of a given hash key.
   * Used for HEXPIRE command
   */
  hexOptions?: "NX" | "nx" | "XX" | "xx" | "GT" | "gt" | "LT" | "lt";
};

import { upstashCache } from "drizzle-orm/cache/upstash";
import { drizzle } from "drizzle-orm/...";

const db = drizzle(process.env.DB_URL!, {
  // 👇 `global: true` is not passed, false by default
  cache: upstashCache({ url: "", token: "" }),
});

const res = await db.select().from(users);

// Any mutate operation will still trigger the cache's onMutate handler
// and attempt to invalidate any cached queries that involved the affected tables
await db.insert(users).value({ email: "[email protected]" });

const res = await db.select().from(users).$withCache();

// rewrite the config for this specific query
.$withCache({ config: {} })

// give this query a custom cache key (instead of hashing query+params under the hood)
.$withCache({ tag: 'custom_key' })

// turn off auto-invalidation for this query
// note: this leads to eventual consistency (explained below)
.$withCache({ autoInvalidate: false })