drizzle-orm

282 notes in this subject, read out of this brain and free to use. This is page 1 of 5.

Drizzle ORM is a library and collection of opt-in tools

Drizzle is first and foremost a library and a collection of complementary opt-in tools. Unlike data frameworks like Django or Spring that require building projects around them, Drizzle lets you build your project with it without requiring your project to conform to its structure.

Drizzle ORM advantages over traditional ORMs

Unlike traditional ORMs and data frameworks that abstract you away from SQL and create a double learning curve, Drizzle embraces SQL. It provides SQL-like syntax at its core so developers who know SQL have minimal to no learning curve, while maintaining access to the full power of SQL.

withReplicas example with CockroachDB

const primaryDb = drizzle('postgres://user:password@host:port/primary_db'); const read1 = drizzle('postgres://user:password@host:port/read_replica_1'); const read2 = drizzle('postgres://user:password@host:port/read_replica_2'); const db = withReplicas(primaryDb, [read1, read2]); // SELECT queries automatically use read replicas await db.select().from(usersTable); // DELETE, INSERT, UPDATE use primary await db.delete(usersTable).where(eq(usersTable.id, 1));

withReplicas basic usage with multiple replicas

Create a primary database connection and one or more read replica connections. Pass the primary connection and an array of replica connections to withReplicas(). The resulting db instance automatically routes SELECT queries to read replicas and write operations to the primary instance.

withReplicas function for CockroachDB read replicas

The withReplicas() function in Drizzle allows managing SELECT queries from read replica instances while directing create, delete, and update operations to the primary database instance. Import it from 'drizzle-orm/cockroach-core'.

Many-to-Many junction table example with SQL

Example of a many-to-many relationship using a junction table for students and courses: ```sql CREATE TABLE "students" ( "id" INT4 PRIMARY KEY, "name" VARCHAR(255) ); CREATE TABLE "courses" ( "id" INT4 PRIMARY KEY, "name" VARCHAR(255), "credits" INT4 ); CREATE TABLE "enrollments" ( "id" INT4 GENERATED ALWAYS AS IDENTITY PRIMARY KEY, "student_id" INT4, "course_id" INT4, "enrollment_date" DATE, FOREIGN KEY ("student_id") REFERENCES "students"("id"), FOREIGN KEY ("course_id") REFERENCES "courses"("id"), UNIQUE ("student_id", "course_id") ); ``` The enrollments table acts as a junction table connecting students to courses with a composite foreign key and unique constraint to prevent duplicate enrollments.

Many-to-Many relationship definition and implementation

In a many-to-many relationship, one record in table A can be related to many records in table B, and one record in table B can be related to many records in table A. Many-to-many relationships are not directly implemented with foreign keys between the two main tables. Instead, you need a junction table (also called an associative table or bridging table) that acts as an intermediary to link records from both tables. Use cases include students and courses (one student enrolls in many courses, one course has many students) and products and categories (a product can belong to multiple categories, a category contains many products).

refine count property

The count property in refine specifies the number of rows to insert into a specific table. By default it is 10. If a global count is defined in the seed() options, the count defined in refine will override it for that specific table.

reset function for database seeding

The reset function from drizzle-seed allows you to easily reset your database and seed it with new values, useful for test suites. It takes a database instance and a schema object. Example: await reset(db, schema);

Basic seed function usage

The seed function is called with a drizzle database instance and an object containing tables to seed. By default, it creates 10 entities of each table. Example: await seed(db, { users });

weightedRandom for related entity counts

The with property can use weightedRandom to determine the amount of related entities to be created with different probabilities. Example: details: [{ weight: 0.6, count: [1, 2, 3] }, { weight: 0.3, count: [5, 6, 7] }, { weight: 0.1, count: [8, 9, 10] }]

refine with property for related entities

The with property in refine defines how many referenced entities to create for each parent table when generating associated entities. The with option works for one-to-many relationships only. For example, if you have one user and many posts, you can use users with posts, but you cannot use posts with users.

refine columns property options

The columns property in refine allows you to refine default behavior of each column by specifying a required generator function, or exclude a column from seeding by specifying false. When set to false, the column will not be seeded, allowing the database to use its default value.

refine basic API structure

The refine API structure is: await seed(db, schema).refine((f) => ({ tableNameOne: { columns: {}, count: 10, with: { tableNameTwo: 10 } }, }));

CockroachDB reset strategy

For CockroachDB, the drizzle-seed package generates TRUNCATE statements with the CASCADE option to ensure all tables are empty after running the reset function. The generated SQL is: TRUNCATE tableName1, tableName2, ... CASCADE;

drizzle-seed generator functions

drizzle-seed provides generator functions including: fullName(), firstName(), lastName(), companyName(), jobTitle(), streetAddress(), city(), state(), country(), postcode(), phoneNumber(with template option), date(with minDate and maxDate options), int(with minValue, maxValue, isUnique options), number(with minValue, maxValue, precision options), loremIpsum(), valuesFromArray(with values array option), and default(with defaultValue option).

geometry generator SRID limitation in CockroachDB

Currently, if the SRID of a geometry(point) column is set to anything other than 0 (for example, 4326) in the drizzle-orm table declaration, an error will occur during the seeding process. This is a known bug in the backlog. As a workaround, declare the column with srid: 0 in the table schema and specify the desired srid value in the generator function.

phoneNumber generator with template parameter

The phoneNumber generator can produce phone numbers using a template where all '#' symbols are substituted with generated digits. Parameters: template (string, required when not using prefixes) - phone number template with '#' placeholders; arraySize (number, optional) - if specified, generates arrays of phone numbers.

phoneNumber generator with prefixes parameter

The phoneNumber generator can produce phone numbers using prefixes and generated digits. Parameters: prefixes (string[], required when not using template) - array of phone number prefixes, not compatible with template property; generatedDigitsNumbers (number | number[], default 7 if prefixes are defined) - number of digits to append to each prefix, can be a single number or array matching prefixes length; arraySize (number, optional) - if specified, generates arrays of phone numbers.

drizzle-seed generators overview

drizzle-seed provides generator functions for seeding database tables with realistic data. Generators are used within the refine() method to specify how values should be generated for each column. Most generators support an arraySize parameter to generate one-dimensional arrays of values.

arraySize parameter with isUnique warning

When specifying arraySize along with isUnique in generators that support both, unique values are generated first and then packed into arrays. This means the array itself is not unique, but the individual elements within it are unique.

geometry generator arraySize limitation in CockroachDB

Currently, if arraySize is set to a value greater than 1 or if more than one geometry point element is inserted into a geometry(point, 0)[] column in CockroachDB via drizzle-orm, an error will occur. This is a known bug in the backlog. As a workaround, use arraySize: 1 for geometry arrays.

drizzle-seed v2 interval generator change

Version 2 changed the unique interval generator. The old version could produce intervals like '1 minute 60 seconds' and '2 minutes 0 seconds' as distinct values, but CockroachDB automatically converts '1 minute 60 seconds' to '2 minutes 0 seconds', causing unique constraint violations. This affects tables with unique interval columns or seeding scripts using interval({ isUnique: true }).

drizzle-seed version parameter usage

Pass a version parameter to seed() to specify which generator versions to use. Example: await seed(db, schema, { version: '2' });

drizzle-seed downgrade generators to v1

To use v1 generators (before any changes): await seed(db, schema, { version: '1' });

drizzle-seed downgrade to v3 generators with v1 uuid

To use v3 generators while maintaining v1 uuid generator: await seed(db, schema, { version: '3' });

drizzle-seed downgrade to v2 generators with v1 hash

To use v2 string and interval generators while maintaining v1 hash generating function: await seed(db, schema, { version: '2' });

sql template example with WHERE clause

Example of using sql template in a WHERE clause: ```typescript import { sql } from 'drizzle-orm' import { usersTable } from './schema' const id = 77 await db.select() .from(usersTable) .where(sql`${usersTable.id} = ${id}`) ```

Basic sql template example with raw query execution

Example of using sql template for a raw query with parameters: ```typescript import { sql } from 'drizzle-orm' const id = 69; await db.execute(sql`select * from ${usersTable} where ${usersTable.id} = ${id}`) ``` This generates: 'select * from "users" where "users"."id" = $1; --> [69]'

sql template prevents SQL injection

The sql template in Drizzle automatically prevents SQL injection vulnerabilities by mapping dynamic parameters like ${id} to parameterized placeholders ($1, $2, etc.) and moving values to a separate array that is passed to the database.

sql template automatically escapes table and column names

When using the sql template, tables and columns provided as parameters are automatically mapped to their corresponding SQL syntax with escaped names. Escaped table names are appended to column names.

sql.mapWith() example with custom DriverValueDecoder

Example of using sql.mapWith() with a custom implementation: ```typescript sql``.mapWith({ mapFromDriverValue: (value: any) => { const mappedValue = value; // mapping you want to apply return mappedValue; }, }); // or with a built-in decoder like Number sql``.mapWith(Number); ```

sql.mapWith() example with column reference

Example of using sql.mapWith() with a column reference: ```typescript const usersTable = cockroachTable('users', { id: int4('id').primaryKey(), name: string('name').notNull(), }); sql`...`.mapWith(usersTable.name); ``` At runtime, values will be mapped the same way as the 'name' text column is mapped in Drizzle.

sql<T> provides type hints without runtime mapping

The sql<T> generic allows you to define a custom type for sql expressions purely as a TypeScript helper. It does not perform any runtime mapping. The type is useful in partial select queries to ensure consistent typing for selected fields.

CockroachDialect.sqlToQuery() converts sql template to query string and params

To obtain the query string and parameters generated from an sql template, you must instantiate the appropriate database dialect (e.g., CockroachDialect) and call its sqlToQuery() method. This ensures compatibility with the specific database system being used.

CockroachDialect sqlToQuery example

Example of converting sql template to query string and params: ```typescript import { CockroachDialect } from 'drizzle-orm/cockroach-core'; const cockroachDialect = new CockroachDialect(); cockroachDialect.sqlToQuery(sql`select * from ${usersTable} where ${usersTable.id} = ${12}`); ``` This generates: 'select * from "users" where "users"."id" = $1; --> [ 12 ]'

sql template works in WHERE clauses

The sql template can be used directly in WHERE clauses to write conditions using database-specific expressions not natively supported by Drizzle, including complex expressions like full-text search with to_tsvector and to_tsquery.

sql template works in ORDER BY clauses

The sql template can be used in ORDER BY clauses to apply database-specific ordering functionality that may not be available in Drizzle, such as 'desc nulls first'.

sql.append() example for incremental query construction

Example of using sql.append() to build a query incrementally: ```typescript const finalSql = sql`select * from users`; finalSql.append(sql` where `); for (let i = 0; i < 5; i++) { finalSql.append(sql`id = ${i}`); if (i === 4) continue; finalSql.append(sql` or `); } ```

sql.empty() example for dynamic query construction

Example of using sql.empty() to start with a blank SQL object: ```typescript const finalSql = sql.empty(); finalSql.append(sql`select * from users`); finalSql.append(sql` where `); for (let i = 0; i < 5; i++) { finalSql.append(sql`id = ${i}`); if (i === 4) continue; finalSql.append(sql` or `); } ```

sql.as() provides explicit field aliases

The .as('alias_name') method allows you to explicitly define an alias for a custom field in a select statement. This is useful when dealing with complex queries where you need to provide a clear and meaningful name for the field.

sql.fromList() example for dynamic query building

Example of using sql.fromList() to dynamically build a query: ```typescript const sqlChunks: SQL[] = []; sqlChunks.push(sql`select * from users`); sqlChunks.push(sql` where `); for (let i = 0; i < 5; i++) { sqlChunks.push(sql`id = ${i}`); if (i === 4) continue; sqlChunks.push(sql` or `); } const finalSql: SQL = sql.fromList(sqlChunks) ```

sql template example with ORDER BY

Example of using sql template in ORDER BY clause: ```typescript import { sql } from 'drizzle-orm' import { usersTable } from './schema' await db.select().from(usersTable).orderBy(sql`${usersTable.id} desc nulls first`) ```

sql template example with GROUP BY and HAVING

Example of using sql template in GROUP BY and HAVING clauses: ```typescript import { sql } from 'drizzle-orm' import { usersTable } from './schema' await db.select({ projectId: usersTable.projectId, count: sql<number>`count(${usersTable.id})`.mapWith(Number) }).from(usersTable) .groupBy(sql`${usersTable.projectId}`) .having(sql`count(${usersTable.id}) > 300`) ```

sql template example with select

Example of using sql<T>, sql.mapWith(), and sql.as() in a select query: ```typescript import { sql } from 'drizzle-orm' import { usersTable } from './schema' await db.select({ id: usersTable.id, lowerName: sql<string>`lower(${usersTable.name})`, aliasedName: sql<string>`lower(${usersTable.name})`.as('aliased_column'), count: sql<number>`count(*)`.mapWith(Number) }).from(usersTable) ```

sql template example with fulltext search

Example of advanced fulltext search in WHERE clause: ```typescript import { sql } from 'drizzle-orm' import { usersTable } from './schema' const searchParam = "Ale" await db.select() .from(usersTable) .where(sql`to_tsvector('simple', ${usersTable.name}) @@ to_tsquery('simple', ${searchParam})`) ```

sql.raw() vs sql template parameterization comparison

sql.raw() generates raw SQL without parameterization (e.g., 'select * from users where id = 12;'), while sql template creates parameterized queries (e.g., 'select * from users where id = $1; --> [12]'). Use sql template for safety unless you specifically need raw output.

sql.join() example for dynamic query building with custom separator

Example of using sql.join() with a custom separator: ```typescript const sqlChunks: SQL[] = []; sqlChunks.push(sql`select * from users`); sqlChunks.push(sql`where`); for (let i = 0; i < 5; i++) { sqlChunks.push(sql`id = ${i}`); if (i === 4) continue; sqlChunks.push(sql`or`); } const finalSql: SQL = sql.join(sqlChunks, sql.raw(' ')); ```

createSelectSchema with views and enums example

Example showing createSelectSchema with views and enums: ```ts import { cockroachEnum, cockroachView } from 'drizzle-orm/cockroach-core'; import { createSelectSchema } from 'drizzle-orm/valibot'; import { parse } from 'valibot'; const roles = cockroachEnum('roles', ['admin', 'basic']); const rolesSchema = createSelectSchema(roles); const parsed: 'admin' | 'basic' = parse(rolesSchema, ...); const usersView = cockroachView('users_view').as((qb) => qb.select().from(users).where(gt(users.age, 18))); const usersViewSchema = createSelectSchema(usersView); const parsed: { id: number; name: string; age: number } = parse(usersViewSchema, ...); ```

createInsertSchema generates validation schema for INSERT operations

Import createInsertSchema from 'drizzle-orm/valibot' and call it with a table to generate a validation schema. The schema validates data to be inserted into the database and can be used to validate API requests. The schema enforces that all non-null fields without defaults are present in the data being validated.

Install valibot validation package

To use valibot validation with Drizzle ORM, install the packages: drizzle-orm@rc valibot

createSelectSchema generates validation schema for SELECT queries

Import createSelectSchema from 'drizzle-orm/valibot' and call it with a table to generate a validation schema. The schema validates data queried from the database and can be used to validate API responses. The schema will enforce that all non-null fields defined in the table are present in the validated data.

createSelectSchema example with users table

Example showing createSelectSchema usage: ```ts import { int4, cockroachTable, text } from 'drizzle-orm/cockroach-core'; import { createSelectSchema } from 'drizzle-orm/valibot'; import { parse } from 'valibot'; 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: { id: number; name: string; age: number } = parse(userSelectSchema, rows[0]); // Error: `age` is not returned const rows = await db.select().from(users).limit(1); const parsed: { id: number; name: string; age: number } = parse(userSelectSchema, rows[0]); // Will parse successfully ``` This demonstrates that the schema validates that all fields exist in the data being parsed.

createInsertSchema example with users table

Example showing createInsertSchema usage: ```ts import { int4, cockroachTable, text } from 'drizzle-orm/cockroach-core'; import { createInsertSchema } from 'drizzle-orm/valibot'; import { parse } from 'valibot'; const users = cockroachTable('users', { id: int4().primaryKey().generatedAlwaysAsIdentity(), name: text().notNull(), age: int4().notNull() }); const userInsertSchema = createInsertSchema(users); const user = { name: 'John' }; const parsed: { name: string, age: number } = parse(userInsertSchema, user); // Error: `age` is not defined const user = { name: 'Jane', age: 30 }; const parsed: { name: string, age: number } = parse(userInsertSchema, user); // Will parse successfully await db.insert(users).values(parsed); ```

createSelectSchema supports views and enums

createSelectSchema can be used with CockroachDB views and enums in addition to tables. For an enum created with cockroachEnum, the schema validates that the value matches one of the enum values. For a view created with cockroachView, the schema validates the shape of data returned by the view.

createUpdateSchema example with users table

Example showing createUpdateSchema usage: ```ts import { int4, cockroachTable, text } from 'drizzle-orm/cockroach-core'; import { createUpdateSchema } from 'drizzle-orm/valibot'; import { parse } from 'valibot'; const users = cockroachTable('users', { id: int4().primaryKey().generatedAlwaysAsIdentity(), name: text().notNull(), age: int4().notNull() }); const userUpdateSchema = createUpdateSchema(users); const user = { age: 35 }; const parsed: { name?: string | undefined, age?: number | undefined } = parse(userUpdateSchema, user); // Will parse successfully await db.update(users).set(parsed).where(eq(users.name, 'Jane')); ```

Schema refinements extend or overwrite field validation

Each create schema function (createSelectSchema, createInsertSchema, createUpdateSchema) accepts an optional second parameter for refinements. Pass a callback function to extend or modify a field's schema, or pass a Valibot schema directly to overwrite it entirely. When providing a callback, you can use valibot's pipe function to add constraints like maxLength.

createUpdateSchema generates validation schema for UPDATE operations

Import createUpdateSchema from 'drizzle-orm/valibot' and call it with a table to generate a validation schema. The schema validates data to be updated in the database and can be used to validate API requests. Fields become optional since UPDATE operations may only modify some columns.

Schema refinements example with callbacks and overwrites

Example showing schema refinements: ```ts import { int4, jsonb, cockroachTable, text } from 'drizzle-orm/cockroach-core'; import { createSelectSchema } from 'drizzle-orm/valibot'; import { parse, pipe, maxLength, object, string } from 'valibot'; const users = cockroachTable('users', { id: int4().primaryKey().generatedAlwaysAsIdentity(), name: text().notNull(), bio: text(), preferences: jsonb() }); const userSelectSchema = createSelectSchema(users, { name: (schema) => pipe(schema, maxLength(20)), // Extends schema bio: (schema) => pipe(schema, maxLength(1000)), // Extends schema before becoming nullable/optional preferences: object({ theme: string() }) // Overwrites the field, including its nullability }); const parsed: { id: number; name: string, bio: string | null; preferences: { theme: string; }; } = parse(userSelectSchema, ...); ```

ESLint Drizzle Plugin installation

Install the ESLint Drizzle plugin with the command: npm install eslint-plugin-drizzle. Also install peer dependencies @typescript-eslint/eslint-plugin and @typescript-eslint/parser.

Give your agent this brain