drizzle-orm

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

PostgreSQL full-text search with to_tsvector

The to_tsvector function parses a textual document into tokens, reduces the tokens to lexemes, and returns a tsvector which lists the lexemes together with their positions in the document. Example: `sql`select to_tsvector('english', 'Guide to PostgreSQL full-text search with Drizzle ORM')`` returns `"'drizzl':9 'full':5 'full-text':4 'guid':1 'orm':10 'postgresql':3 'search':7 'text':6"`.

Supported databases for parent-child queries

Selecting parent rows with at least one related child row is supported on PostgreSQL, MySQL, SQLite, MSSQL, and Cockroach databases.

Toggle boolean supported databases

The SQL toggle value pattern is supported in PostgreSQL, MySQL, SQLite, MSSQL, and CockroachDB.

Boolean type representation in different databases

There is no native boolean type in MySQL and SQLite. MySQL uses tinyint(1) to represent boolean values. SQLite uses integers where 0 represents false and 1 represents true.

SQLite unique case-insensitive email with uniqueIndex

To implement unique and case-insensitive email handling in SQLite, create a unique index on the lowercased email column using uniqueIndex('emailUniqueIndex').on(lower(table.email)). Define a custom lower function that returns sql`lower(${email})` where email is of type AnySQLiteColumn. This ensures the email is unique regardless of case. The generated migration creates a unique index on lower(`email`).

JIT mappers performance benefit

Regular Drizzle ORM mappers loop through column metadata for every single row, performing metadata lookups, null checks, codec checks, and object construction repeatedly. JIT mappers eliminate this interpretive overhead by generating a function where all metadata lookups, null checks, and nested object construction are hardcoded into the function body at generation time. This reduces overhead significantly when processing thousands of rows.

What are JIT mappers

JIT (Just-In-Time) mappers are dynamically generated JavaScript functions that transform database rows into JavaScript objects. Instead of interpreting column metadata at runtime for every row, JIT mappers compile a specialized function once and reuse it for all rows in the result set.

Drizzle version requirements for vector support

Vector similarity search requires drizzle-orm version 0.31.0 or higher and drizzle-kit version 0.22.0 or higher.

SQLite current timestamp default example

Example of setting current timestamp as default on a text column in SQLite: ```ts import { sql } from 'drizzle-orm'; import { integer, sqliteTable, text } from 'drizzle-orm/sqlite-core'; export const users = sqliteTable('users', { id: integer('id').primaryKey(), timestamp: text('timestamp') .notNull() .default(sql`(current_timestamp)`), }); ```

SQLite integer mode option for timestamps

The `mode` option on integer columns in SQLite defines how timestamp values are handled in the application. 'timestamp' mode treats values as Date objects representing seconds; 'timestamp_ms' mode treats values as Date objects representing milliseconds; 'number' mode treats values as numbers. All are stored as integers in the database.

SQLite unix timestamp default with unixepoch()

To set unix timestamp as default on an integer column in SQLite, use `.default(sql`(unixepoch())`)`. This returns the number of seconds since 1970-01-01 00:00:00 UTC. For milliseconds, use `.default(sql`(unixepoch() * 1000)`)`.

SQLite current timestamp default with current_timestamp

To set current timestamp as default on a text column in SQLite, use `.default(sql`(current_timestamp)`)`. This returns text representation of the current UTC date and time in YYYY-MM-DD HH:MM:SS format.

SQLite unix timestamp default example

Example of setting unix timestamp defaults in SQLite: ```ts import { sql } from 'drizzle-orm'; import { integer, sqliteTable } from 'drizzle-orm/sqlite-core'; export const users = sqliteTable('users', { id: integer('id').primaryKey(), timestamp1: integer('timestamp1', { mode: 'timestamp' }) .notNull() .default(sql`(unixepoch())`), timestamp2: integer('timestamp2', { mode: 'timestamp_ms' }) .notNull() .default(sql`(unixepoch() * 1000)`), timestamp3: integer('timestamp3', { mode: 'number' }) .notNull() .default(sql`(unixepoch())`), }); ```

Use is() for Drizzle object type checking

Import is from drizzle-orm and use is(value, ColumnType) instead of instanceof to check Drizzle object types. This properly narrows the type of the value.

Supported Drizzle ORM databases

Drizzle ORM supports PostgreSQL, MySQL, SQLite, SingleStore, MSSQL, and CockroachDB. It operates natively through industry-standard database drivers for each database type.

Drizzle design characteristics

Drizzle is lightweight, performant, typesafe, flexible, and serverless-ready by design. It has exactly zero dependencies and is 31kb in size.

Why Drizzle uses SQL-like syntax

Drizzle embraces SQL and is built to be SQL-like at its core to minimize learning curve. If you know SQL, you know Drizzle. This avoids the double learning curve of other ORMs that abstract away from SQL.

Import filter operators from drizzle-orm

All filter and conditional operators are imported from the 'drizzle-orm' package. Example: import { eq, ne, gt, gte, ... } from "drizzle-orm";

sql in WHERE example with simple filter

This example filters by id using sql template: const id = 77 await db.select() .from(usersTable) .where(sql`${usersTable.id} = ${id}`) Generates: select * from [users] where [users].[id] = @par0 with params: [77]

sql.raw() for unescaped raw SQL

The sql.raw() function allows you to include raw SQL statements within your queries without any additional processing, escaping, or parameterization. It can be used standalone or nested within a sql template to incorporate unescaped raw strings directly into queries. This is useful for pre-constructed SQL statements or complex dynamic SQL code that should remain untouched.

sql.append() for dynamically adding SQL chunks

The .append() method allows you to dynamically add additional SQL chunks to an existing SQL statement generated from the sql template. This enables you to incrementally construct SQL queries by appending new chunks, effectively concatenating them together with custom logic or business rules.

Converting sql template to string and params with dialect

To obtain the query string and corresponding parameters generated from an sql template, you must specify the database dialect. Different databases have varying syntax for parameterization and escaping. Use the dialect's sqlToQuery() method to convert the SQL template into the desired query string and parameter format for compatibility with your specific database system.

Using sql in partial SELECT queries

The sql template can be used in partial select queries to retrieve specific fields or columns. You can use sql<T> for type definition, sql.mapWith() for runtime mapping, and sql.as() for aliasing custom fields selected from a table.

Using sql in WHERE clause

The sql template can be used directly in the WHERE clause to write SQL expressions not natively supported by Drizzle. This allows you to leverage the full power of SQL and incorporate any expressions or functionalities specific to your target database while maintaining type safety and parameterization.

Using sql in ORDER BY clause

The sql template can be used in the ORDER BY clause when you need specific ordering functionality not available in Drizzle's built-in methods. This allows you to write custom ordering expressions while maintaining parameterization and escaping.

Using sql in HAVING and GROUP BY clauses

The sql template can be used in the HAVING and GROUP BY clauses when you need specific functionality for grouping and filtering aggregates that is not available in Drizzle. This allows you to write custom expressions while maintaining type safety and parameterization.

sql template example with parameterized select query

This example demonstrates basic parameterized query usage: await db.execute(sql`select * from ${usersTable} where ${usersTable.id} = ${id}`) with id = 69 generates: select * from [users] where [users].[id] = @par0 with params: [69]

sql<T> example with type definition in partial select

This example shows typing custom fields in partial selects: await db.select({ lowerName: sql<string>`lower(${usersTable.id})` }).from(usersTable); with sql<T> type defined, response type is { lowerName: string }[] instead of { lowerName: unknown }[]

sql.mapWith() example with column mapping

This example demonstrates column-based mapping: sql`...`.mapWith(usersTable.name); where values at runtime are mapped the same way the text column type is mapped in Drizzle.

sql.raw() example comparing parameterized vs raw

Comparison example: sql.raw(`select * from users where id = ${12}`) generates: select * from users where id = 12 vs sql`select * from users where id = ${12}` generates: select * from users where id = @par0 with params: [12]

sql.fromList() example combining SQL chunks

This example builds a query from multiple SQL chunks: 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); Generates: select * from users where id = @par0 or id = @par1 or id = @par2 or id = @par3 or id = @par4 with params: [0, 1, 2, 3, 4]

sql.join() example with custom separator

This example joins SQL chunks with space separators: const finalSql: SQL = sql.join(sqlChunks, sql.raw(' ')); where sqlChunks contains: sql`select * from users`, sql`where`, sql`id = ${i}` chunks Generates: select * from users where id = @par0 or id = @par1 or id = @par2 or id = @par3 or id = @par4 with params: [0, 1, 2, 3, 4]

sql.append() example dynamically building query

This example builds a query by appending SQL chunks: 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 `); } Generates: select * from users where id = @par0 or id = @par1 or id = @par2 or id = @par3 or id = @par4 with params: [0, 1, 2, 3, 4]

sql.empty() example incrementally constructing query

This example builds a query from scratch using sql.empty(): 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 `); } Generates: select * from users where id = @par0 or id = @par1 or id = @par2 or id = @par3 or id = @par4 with params: [0, 1, 2, 3, 4]

MsSqlDialect example converting sql to string and params

This example shows how to convert an sql template to string and params: import { MsSqlDialect } from 'drizzle-orm/mssql-core'; const mssqlDialect = new MsSqlDialect(); mssqlDialect.sqlToQuery(sql`select * from ${usersTable} where ${usersTable.id} = ${12}`); Generates: select * from [users] where [users].[id] = @par0 with params: [12]

sql in SELECT example with type, mapWith, and alias

This example shows comprehensive sql usage in partial select: 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); Generates: select [id], lower([name]), lower([name]) as [aliased_column], count(*) from [users]

sql in WHERE example with fulltext search

This example performs advanced fulltext search: const searchParam = "%Ale%" await db.select() .from(usersTable) .where(sql`lower(${usersTable.name}) like lower(${searchPattern})`) Generates: select * from [users] where lower([users].[name]) like lower(@par0) with params: ["%Ale%"]

sql in ORDER BY example

This example uses sql template in ORDER BY clause: await db.select().from(usersTable).orderBy(sql`${usersTable.id} desc`) Generates: select * from [users] order by [users].[id] desc

Drizzle SQL-like select query example

SQL-like queries use method chaining for building database queries. Example: await db.select().from(countries).leftJoin(cities, eq(cities.countryId, countries.id)).where(eq(countries.id, 10))

Drizzle ORM core philosophy

Drizzle ORM is a headless TypeScript ORM designed to be lightweight, performant, typesafe, and serverless-ready by design. It lets developers build projects the way they want without interfering with project structure. Drizzle has exactly 0 dependencies.

Drizzle ORM SQL-like design principle

Drizzle is built to be SQL-like at its core. If you know SQL, you know Drizzle. This approach eliminates the double learning curve of learning both SQL and a framework API. Drizzle brings familiar SQL schema declaration, queries, automatic migrations, and relational query capabilities.

Edge functions do not reuse database connections

Edge functions clean up immediately after they are invoked, which leads to little to no performance benefits from connection reuse.

Declare database connection outside handler scope for reuse

To reuse database connections and prepared statements in serverless functions, declare the database connection and prepared statements outside of the handler scope so they persist across invocations.

Serverless connection reuse example

This example shows how to declare a database connection and prepared statement outside an AWS Lambda handler so they can be reused across invocations: ```ts const databaseConnection = ...; const db = drizzle({ client: databaseConnection }); const prepared = db.select().from(...).prepare(); // AWS handler export const handler = async (event: APIGatewayProxyEvent) => { return prepared.execute(); } ```

Serverless functions can reuse database connections and prepared statements

AWS Lambda and Vercel Server Functions (which are AWS Lambda based) can live up to 15 minutes and therefore can reuse both database connections and prepared statements across multiple invocations, providing immense performance benefits.

Delete rows with conditions using where()

To delete rows that match specific conditions, use db.delete(tableName).where(condition). For example, await db.delete(users).where(eq(users.name, 'Dan')) deletes all rows where the name field equals 'Dan'.

Delete all rows from a table

To delete all rows from a table in Drizzle ORM, use db.delete(tableName) without any conditions.

Delete query with limit and orderBy example

Example: await db.delete(users).where(eq(users.name, 'Dan')).orderBy(users.name).limit(2); generates SQL: delete from `users` where `users`.`name` = ? order by `name` limit ?;

Order deleted rows with orderBy()

The .orderBy() method adds an ORDER BY clause to a DELETE query, determining which rows are deleted when using limit(). It can accept single or multiple fields, and supports asc() and desc() functions for sort direction. For example, await db.delete(users).where(eq(users.name, 'Dan')).orderBy(users.name) or await db.delete(users).where(eq(users.name, 'Dan')).orderBy(desc(users.name)) or await db.delete(users).where(eq(users.name, 'Dan')).orderBy(asc(users.name), desc(users.name2)) for multiple fields.

Limit the number of rows deleted with limit()

The .limit() method adds a LIMIT clause to a DELETE query, restricting the number of rows deleted. For example, await db.delete(users).where(eq(users.name, 'Dan')).limit(2) deletes a maximum of 2 rows matching the condition.

Import asc and desc functions

The asc and desc functions for controlling sort direction in orderBy() are imported from 'drizzle-orm': import { asc, desc } from 'drizzle-orm';

count() with column parameter

To count rows where a specified column contains non-NULL values, pass the column to the count() function: await db.select({ count: count(products.discount) }).from(products);

count() function basic usage

Use the count() function from drizzle-orm to count all rows in a table. The function returns a result with a count property of type number. Example: await db.select({ count: count() }).from(products);

count() with sql operator

You can also count rows using sql`count(*)`.mapWith(Number). The mapWith() method casts the result to a number at runtime, since the count() function performs this cast under the hood.

sql<number> type parameter warning

When using sql<number>, you are telling Drizzle the expected type is number. Drizzle cannot perform runtime type casts based on the provided type generic since that information is not available at runtime. If you specify the type incorrectly (e.g. sql<string> for a field returned as number), the runtime value will not match the expected type. Use the .mapWith() method if you need to apply runtime transformations.

count() with where clause

To count rows that match a condition, use the .where() method with the count() function. Example: await db.select({ count: count() }).from(products).where(gt(products.price, 100));

count() with joins and groupBy

Use count() with joins and aggregations by combining .leftJoin(), .groupBy(), and .orderBy() methods. Example: count cities in each country with await db.select({ country: countries.name, citiesCount: count(cities.id) }).from(countries).leftJoin(cities, eq(countries.id, cities.countryId)).groupBy(countries.id).orderBy(countries.name);

SingleStore count() returns string by default

In SingleStore, the count() function may be returned as a string by the driver. Cast the result to a number when you need a numeric result using sql<number>`cast(count(*) as unsigned)` or sql<number>`cast(count(${column}) as unsigned)` to ensure proper type handling.

SingleStore FSP option not supported

The FSP (Fractional Seconds Precision) option in DATE, TIMESTAMP, and DATETIME column types is not supported by SingleStore.

SingleStore serial column limitation

SingleStore's serial column type only ensures the uniqueness of column values, and does not auto-increment like in other databases.

Give your agent this brain