Drizzle supports both relational and SQL-like query APIs
Drizzle is the only ORM with both relational (Queries API) and SQL-like query APIs, providing the best of both worlds for accessing relational data.
Drizzle · PostgreSQL · all subjects
drizzle-core
66 notes in this subject, read out of this brain and free to use. This is page 1 of 2.
Drizzle is a headless TypeScript ORM
Drizzle ORM is a headless TypeScript ORM that lets developers build projects the way they want without interfering with project structure. It is a library and collection of complementary opt-in tools, not a data framework that requires building projects around it.
Drizzle supports major database drivers
Drizzle operates natively through industry-standard database drivers and supports all major PostgreSQL, MySQL, SQLite, SingleStore, MSSQL, and CockroachDB drivers.
Drizzle is SQL-like at its core
Drizzle embraces SQL and is built to be SQL-like at its core, allowing developers who know SQL to use Drizzle with zero to no learning curve. It provides SQL schema declaration, SQL-like queries, and automatic migrations.
Drizzle always outputs exactly 1 SQL query
Drizzle always outputs exactly one SQL query, regardless of the complexity of the relational query, making it suitable for serverless databases without worrying about performance or roundtrip costs.
Drizzle has zero dependencies
Drizzle ORM has exactly 0 dependencies, making it lightweight and serverless-ready by design.
pgSchema example with enum and table
Example using pgSchema: Import serial, text, and pgSchema from drizzle-orm/pg-core. Create a schema with `export const mySchema = pgSchema('my_schema')`. Define an enum with `export const colors = mySchema.enum('colors', ['red', 'green', 'blue'])`. Define a table with `export const mySchemaUsers = mySchema.table('users', { id: serial('id').primaryKey(), name: text('name'), color: colors('color').default('red') })`. This generates SQL: `CREATE SCHEMA "my_schema"; CREATE TYPE "my_schema"."colors" AS ENUM ('red', 'green', 'blue'); CREATE TABLE "my_schema"."users" ("id" serial PRIMARY KEY, "name" text, "color" "my_schema"."colors" DEFAULT 'red');`
Tables with same names in different schemas cause type errors
If multiple tables with the same name exist in different schemas, Drizzle will respond with a `never[]` error in result types and a corresponding error from the database. This can be resolved using alias syntax for joins.
pgSchema creates namespaced tables and enums in PostgreSQL
The pgSchema function from drizzle-orm/pg-core creates a PostgreSQL schema namespace. Tables and enums declared within a pgSchema are automatically namespaced, and the query builder prepends the schema name in SQL queries. For example, tables declared in a schema named 'my_schema' will be queried as `select * from "my_schema"."users"`.
pgSchema for PostgreSQL schema organization
Use pgSchema('schemaName') from drizzle-orm/pg-core to define a PostgreSQL schema, then place tables inside it using schemaObject.table('tableName', {...}). This creates the PostgreSQL schema structure for organizing tables.
TypeScript key equals database key by default
By default in Drizzle, the TypeScript property name for a column becomes the database column name unless explicitly aliased. Example: { id: integer(), first_name: varchar() } creates database columns 'id' and 'first_name'.
Schema model types supported by Drizzle
Drizzle supports defining tables with columns and constraints, schemas, enums, sequences, views, materialized views, and other PostgreSQL model types.
Column aliases for TypeScript vs database naming
When a TypeScript property name differs from the desired database column name, pass the database name as a string argument to the column type function. Example: firstName: varchar('first_name') creates a TypeScript property 'firstName' that maps to database column 'first_name'.
pgTable basic syntax with column types
A PostgreSQL table in Drizzle is defined using pgTable() with a table name and an object of columns. Each column is defined with a type method like integer(), varchar(), timestamp(), etc., optionally chained with modifiers like primaryKey(), notNull(), unique(), etc. Example: pgTable('users', { id: integer().primaryKey().generatedAlwaysAsIdentity(), name: varchar().notNull(), email: varchar().notNull().unique() })
Automatic camelCase to snake_case conversion with snakeCase builder
Import snakeCase or camelCase builder from drizzle-orm/pg-core and use snakeCase.table() instead of pgTable() to automatically map camelCase property names to snake_case database column names. Available on table, view, materializedView, and schema builders. Example: snakeCase.table('users', { fullName: text() }) creates a database column 'full_name'.
Reuse column definitions across multiple tables
Define commonly used columns (like timestamps: { updated_at: timestamp(), created_at: timestamp().defaultNow().notNull(), deleted_at: timestamp() }) in a separate file, then spread them into table definitions using the spread operator (...timestamps).
Three import styles for pgTable definitions
Drizzle supports three import patterns for defining tables: (1) Direct imports of individual functions like pgTable, integer, varchar, then using them directly; (2) Using callback syntax with pgTable() accepting a callback function where column types are accessed via the parameter (t.integer(), t.varchar(), etc.); (3) Namespace import using 'import * as p' then accessing all functions as p.pgTable, p.integer, etc.
createSelectSchema typebox function
createSelectSchema generates a typebox schema for selecting data from a Drizzle ORM table. It can be used to validate API responses.
Refine typebox schema fields
When calling createInsertSchema or related functions, pass a second argument where field values are callback functions to refine schemas before they become nullable or optional. For example: createInsertSchema(users, { id: (schema) => Type.Number({ ...schema, minimum: 0 }) }) modifies the id field schema.
Validate data with typebox schema from Drizzle
Use Value.Check from typebox to validate data against generated schemas. For example: Value.Check(insertUserSchema, { name: 'John Doe', email: 'johndoe@test.com', role: 'admin' }) returns a boolean indicating validity.
typebox schema generation example
Example showing complete workflow: define a pgTable with columns, import createInsertSchema/createUpdateSchema/createSelectSchema and Type from typebox, generate schemas, and use Value.Check to validate. The code shows overriding the role field to Type.String() and refining the id field with a minimum constraint.
createUpdateSchema typebox function
createUpdateSchema generates a typebox schema for updating data in a Drizzle ORM table. It can be used to validate API requests.
createInsertSchema typebox function
createInsertSchema generates a typebox schema for inserting data into a Drizzle ORM table. It can be used to validate API requests. Call it with the table as the only argument, or pass a second argument object to override or refine field schemas.
typebox schema generation features
The typebox integration provides three main features: create select schemas for tables, views and enums; create insert schemas for tables; create update schemas for tables.
typebox integration for Drizzle ORM
The typebox integration allows you to generate typebox schemas from Drizzle ORM schemas. Install with: drizzle-orm@rc typebox
Override typebox schema fields
When calling createInsertSchema or related functions, pass a second argument object to override field schemas entirely. For example: createInsertSchema(users, { role: Type.String() }) replaces the role field schema.
Relational Queries upgrade path v1 to v2
If using Relational Queries, upgrading to v1 requires further migration to v2, which involves updating both the relations schema definition and the queries themselves.
createUpdateSchema example with users table
Example showing createUpdateSchema usage: ```ts import { pgTable, text, integer } from 'drizzle-orm/pg-core'; import { createUpdateSchema } from 'drizzle-orm/typebox-legacy'; import { Value } from '@sinclair/typebox/value'; import { eq } from "drizzle-orm"; const users = pgTable('users', { id: integer().generatedAlwaysAsIdentity().primaryKey(), name: text().notNull(), age: integer().notNull() }); const userUpdateSchema = createUpdateSchema(users); const user = { age: 35 }; const parsed: { name?: string | undefined, age?: number | undefined } = Value.Parse(userUpdateSchema, user); // Will parse successfully await db.update(users).set(parsed).where(eq(users.name, 'Jane')); ```
createSelectSchema refinements example
Example showing schema refinements with callbacks and Typebox schemas: ```ts import { pgTable, text, integer, json } from 'drizzle-orm/pg-core'; import { createSelectSchema } from 'drizzle-orm/typebox-legacy'; import { Type } from '@sinclair/typebox'; import { Value } from '@sinclair/typebox/value'; const users = pgTable('users', { id: integer().generatedAlwaysAsIdentity().primaryKey(), name: text().notNull(), bio: text(), preferences: json() }); const userSelectSchema = createSelectSchema(users, { name: (schema) => Type.String({ ...schema, maxLength: 20 }), // Extends schema bio: (schema) => Type.String({ ...schema, maxLength: 1000 }), // Extends schema before becoming nullable/optional preferences: Type.Object({ theme: Type.String() }) // Overwrites the field, including its nullability }); const parsed: { id: number; name: string, bio: string | null; preferences: { theme: string; }; } = Value.Parse(userSelectSchema, ...); ```
createSchemaFactory with extended Typebox instance example
Example showing createSchemaFactory usage with extended Typebox instance: ```ts import { pgTable, text, integer } from 'drizzle-orm/pg-core'; import { createSchemaFactory } from 'drizzle-orm/typebox'; import { t } from 'elysia'; // Extended Typebox instance const users = pgTable('users', { id: integer().generatedAlwaysAsIdentity().primaryKey(), name: text().notNull(), age: integer().notNull() }); const { createInsertSchema } = createSchemaFactory({ typeboxInstance: t }); const userInsertSchema = createInsertSchema(users, { // We can now use the extended instance name: (schema) => t.Number({ ...schema, error: "`name` must be a string" }), }); ```
typebox-legacy bit type mapping
PostgreSQL pg.bit({ dimensions: ... }) maps to Typebox t.RegExp(/^[01]+$/, { maxLength: dimensions }).
typebox-legacy text enum mapping
PostgreSQL pg.text({ enum: ... }), pg.char({ enum: ... }), and pg.varchar({ enum: ... }) map to Typebox Type.Enum(enum).
typebox-legacy smallint type mapping
PostgreSQL pg.smallint() and pg.smallserial() map to Typebox Type.Integer({ minimum: -32_768, maximum: 32_767 }).
typebox-legacy real type mapping
PostgreSQL pg.real() maps to Typebox Type.Number().min(-8_388_608).max(8_388_607).
typebox-legacy integer type mapping
PostgreSQL pg.integer() and pg.serial() map to Typebox Type.Integer({ minimum: -2_147_483_648, maximum: 2_147_483_647 }).
typebox-legacy doublePrecision type mapping
PostgreSQL pg.doublePrecision() maps to Typebox Type.Number({ minimum: -140_737_488_355_328, maximum: 140_737_488_355_327 }).
typebox-legacy install
Install drizzle-orm@rc and @sinclair/typebox to use typebox-legacy integration for schema validation.
createSelectSchema generates Typebox schema from table
The createSelectSchema function from drizzle-orm/typebox-legacy generates a Typebox schema that defines the shape of data queried from the database. It can validate API responses. Views and enums are also supported. The generated schema will match the columns actually selected in the query.
createInsertSchema generates Typebox schema for insertions
The createInsertSchema function from drizzle-orm/typebox-legacy generates a Typebox schema that defines the shape of data to be inserted into the database. It can validate API requests. All non-default columns must be provided when parsing.
createUpdateSchema generates Typebox schema for updates
The createUpdateSchema function from drizzle-orm/typebox-legacy generates a Typebox schema that defines the shape of data to be updated in the database. It can validate API requests. All fields are optional in the generated schema.
Schema refinements with callback or Typebox schema
Each create schema function (createSelectSchema, createInsertSchema, createUpdateSchema) accepts an optional second parameter for refinements. Providing a callback function will extend or modify a field's schema. Providing a Typebox schema will overwrite it completely, including its nullability.
createSchemaFactory for extended Typebox instances
The createSchemaFactory function from drizzle-orm/typebox allows creating schema functions that use a custom or extended Typebox instance by passing { typeboxInstance: t } as a parameter. This is useful when integrating with frameworks like Elysia that provide extended Typebox instances.
typebox-legacy boolean type mapping
PostgreSQL pg.boolean() maps to Typebox Type.Boolean().
typebox-legacy enum type mapping
PostgreSQL pgEnum('name', ['val1', 'val2']) maps to Typebox Type.Enum({'val1': 'val1', 'val2': 'val2'}).
typebox-legacy date and timestamp type mapping
PostgreSQL pg.date({ mode: 'date' }) and pg.timestamp({ mode: 'date' }) map to Typebox Type.Date().
typebox-legacy string-mode temporal and numeric type mapping
PostgreSQL pg.date({ mode: 'string' }), pg.timestamp({ mode: 'string' }), pg.cidr(), pg.inet(), pg.interval(), pg.macaddr(), pg.macaddr8(), pg.numeric(), pg.text(), pg.sparsevec(), and pg.time() all map to Typebox Type.String().
typebox-legacy varchar type mapping
PostgreSQL pg.varchar({ length: ... }) maps to Typebox Type.String({ maxLength: length }).
typebox-legacy uuid type mapping
PostgreSQL pg.uuid() maps to Typebox Type.String({ format: 'uuid' }).
typebox-legacy bigint bigint mode type mapping
PostgreSQL pg.bigint({ mode: 'bigint' }) and pg.bigserial({ mode: 'bigint' }) map to Typebox Type.BigInt({ minimum: -9_223_372_036_854_775_808n, maximum: 9_223_372_036_854_775_807n }).
typebox-legacy point xy mode type mapping
PostgreSQL pg.geometry({ type: 'point', mode: 'xy' }) and pg.point({ mode: 'xy' }) map to Typebox Type.Object({ x: Type.Number(), y: Type.Number() }).
typebox-legacy halfvec and vector type mapping
PostgreSQL pg.halfvec({ dimensions: ... }) and pg.vector({ dimensions: ... }) map to Typebox Type.Array(Type.Number(), { minItems: dimensions, maxItems: dimensions }).
typebox-legacy line abc mode type mapping
PostgreSQL pg.line({ mode: 'abc' }) maps to Typebox Type.Object({ a: Type.Number(), b: Type.Number(), c: Type.Number() }).
typebox-legacy line tuple mode type mapping
PostgreSQL pg.line({ mode: 'tuple' }) maps to Typebox Type.Tuple([Type.Number(), Type.Number(), Type.Number()]).
typebox-legacy json and jsonb type mapping
PostgreSQL pg.json() and pg.jsonb() map to Typebox Type.Recursive((self) => Type.Union([Type.Union([Type.String(), Type.Number(), Type.Boolean(), Type.Null()]), Type.Array(self), Type.Record(Type.String(), self)])).
typebox-legacy array type mapping
PostgreSQL pg.dataType().array(...) maps to Typebox Type.Array(baseDataTypeSchema, { minItems: size, maxItems: size }).
typebox-legacy bytea type mapping
PostgreSQL pg.bytea() maps to a custom Typebox schema created with TypeRegistry.Set('Buffer', (_, value) => value instanceof Buffer) and { [Kind]: 'Buffer', type: 'buffer' }.
createInsertSchema example with users table
Example showing createInsertSchema usage: ```ts import { pgTable, text, integer } from 'drizzle-orm/pg-core'; import { createInsertSchema } from 'drizzle-orm/typebox-legacy'; import { Value } from '@sinclair/typebox/value'; const users = pgTable('users', { id: integer().generatedAlwaysAsIdentity().primaryKey(), name: text().notNull(), age: integer().notNull() }); const userInsertSchema = createInsertSchema(users); const user = { name: 'John' }; const parsed: { name: string, age: number } = Value.Parse(userInsertSchema, user); // Error: `age` is not defined const user = { name: 'Jane', age: 30 }; const parsed: { name: string, age: number } = Value.Parse(userInsertSchema, user); // Will parse successfully await db.insert(users).values(parsed); ```
Relational Queries v1 removed in v1.0
Relational Queries v1 (RQBv1) has been completely removed from Drizzle ORM v1.0. Users must migrate to Relational Queries v2 using the new `defineRelations()` API. The new API uses a pattern like `r.many.posts()` and `r.one.profiles()` for defining one-to-many and one-to-one relationships between entities.
New casing API in v1.0 replaces legacy casing option
The legacy `drizzle({ casing: 'camelCase' })` configuration has been replaced with a table/view/schema-level API. Users must now use `snakeCase.table()`, `camelCase.table()`, and similar methods on all entity types (table, view, materializedView, schema). For example: `snakeCase.table('users', { id: serial().primaryKey(), fullName: text() })` automatically converts `fullName` to `full_name` in the database.
Validator packages consolidated into drizzle-orm v1.0
In v1.0, separate validator packages have been consolidated: drizzle-zod is now drizzle-orm/zod, drizzle-valibot is now drizzle-orm/valibot, drizzle-typebox is now drizzle-orm/typebox-legacy (using @sinclair/typebox) or drizzle-orm/typebox (using typebox), drizzle-arktype is now drizzle-orm/arktype, and a new drizzle-orm/effect-schema package has been added. The old packages can still be used but will not receive new updates.