DOCSEARCHbyAlgolia
Submit Docs

drizzle-plus

repository·main·Indexed 41 days ago

https://github.com/alloc/drizzle-plus

A library of query-builder extensions and SQL helpers for Drizzle ORM v1. It enhances the relational query builder with methods like upsert, pagination, and count, and provides universal and dialect-specific SQL functions for PostgreSQL, MySQL, and SQLite. Key features include typed SQL expressions, side-effect imports for query extensions, and advanced CTE support.

Tokens
28.3K
Snippets
108
Records
150
Agent score
51%

What's inside drizzle-plus

  1. Overview of drizzle-plus features

    main

    drizzle-plus provides query-builder extensions and SQL helpers for Drizzle ORM to fill practical gaps in the relational query builder. It supports PostgreSQL, MySQL, and SQLite.

    Key feature areas include:

    Query extensions

    Extends db.query.<table_name> with methods like count(), updateMany(), upsert(), and cursorPagination().

    Database and select helpers

    Extends db, tables, or the select builder with helpers like $select, $values, $without, and support for CTEs (Recursive and Materialized).

    SQL building blocks

    Provides type-aware SQL expressions such as caseWhen(), nest(), and universal/dialect-specific functions.

    Utilities and types

    Includes helpers like orThrow() for handling empty results and type utilities for deriving types from relational query builders.

  2. How to use SQL building blocks in drizzle-plus

    main

    SQL building blocks allow you to compose complex, type-safe SQL expressions within your Drizzle queries.

    Key tools include:

    • caseWhen(): Creates conditional SQL expressions that remain type-aware.
    • nest(): Wraps a one-column query into a scalar SQL expression.
    • toSQL(): Safely binds JavaScript values as SQL input.
    • Functions: Access to both Universal SQL functions (numeric, text, date, null-handling) and Dialect-specific functions (JSON, UUID, cast, etc.).
    • Timestamps: Helpers to ensure database-generated timestamps decode correctly as JavaScript Date objects.

  3. How to use orThrow() to handle empty query results

    main

    The orThrow() utility allows you to turn an empty query result into a DrizzleError while simultaneously narrowing the TypeScript type of the awaited result to a non-null value. This eliminates the need for manual null checks after a query.

    Behavior by Query Type

    • Single Row Queries: If the query returns undefined or null, the promise rejects with a default error message (No rows returned). If successful, the result is narrowed to the non-null type.
    • Array Queries: orThrow() only rejects if the returned array is empty. It will not reject a non-empty array even if individual items within that array contain nullable fields.

    When to use orThrow() vs findUnique()

    • Use findUnique() when you want to guarantee that the filter targets a database uniqueness constraint.
    • Use orThrow() when your primary requirement is the guarantee that a result must exist for the application logic to proceed.
    // Single row example
const user = await db.query.user
  .findFirst({
    where: { id: 42 },
  })
  .orThrow();

  4. How CTE materialization works in drizzle-plus

    main

    In drizzle-plus, $withMaterialized() and $withNotMaterialized() follow the same builder pattern as the standard Drizzle $with() method. The primary difference is the SQL keyword emitted after the CTE's AS clause.

    Warning: PostgreSQL has specific restrictions on when NOT MATERIALIZED is valid (e.g., regarding volatile functions). Always verify PostgreSQL's CTE rules when applying these overrides to complex queries.

  7. Constraints and error handling for nest()

    main

    When using nest(), the input query must select exactly one column.

    • If the query selects zero columns or multiple columns, nest() will throw the error: Subquery must have exactly one column.
    • If the subquery returns no rows, the scalar result in the outer query will be null. You should handle this potential null value in your application logic.

  8. Understand package entry points in drizzle-plus

    main

    The package is structured into several entry points based on your needs:

    • drizzle-plus: Shared SQL helpers and query utilities.
    • drizzle-plus/<dialect>: Named helpers for pg, mysql, or sqlite.
    • drizzle-plus/<dialect>/<extension>: Side-effect modules that install query, database, table, or select-builder extensions.
    • drizzle-plus/types: Types derived from relational query builders and SQL expressions.
    • drizzle-plus/types/json: JSON-serializable value types.
    • drizzle-plus/utils: Advanced query, decoder, and selection utilities.

  9. How named subqueries work in drizzle-plus

    main

    Named subqueries allow you to reference the selected fields of a subquery in an outer query with full type safety.

    When you call .as(alias) on a select or relational query:

    1. The alias string becomes the table name in the generated SQL.
    2. The field decoders from the subquery are propagated to the outer query, allowing you to reference alias.fieldName in the outer .select() call.

    In addition to standard queries, the .as() method is also available for:

    • Columns generated by the $values() helper.
    • Fields returned from an upsert operation when composed into a larger statement.

  10. Use `toSQL()` for values and expressions, not identifiers

    main

    When using toSQL(), the input is treated as a bound value (a parameter), not as a database identifier like a column or table name.

    For example, if you pass a string to toSQL(), it will be treated as a literal string value in the resulting SQL query. If you need to construct dynamic identifiers (like dynamic table or column names), do not use toSQL(); instead, validate the input separately and use Drizzle's sql.identifier() method.

    import 'drizzle-plus/pg/fromSingle'
// or 'drizzle-plus/mysql/fromSingle'
// or 'drizzle-plus/sqlite/fromSingle'

const query = db
  .select({
    requested: toSQL('Ada'),
  })
  .fromSingle()

  11. Understand the drizzle-plus architecture and dialect isolation

    main

    drizzle-plus is designed to extend Drizzle ORM with advanced features like upsert, updateMany, $values, and CTE helpers. To support multiple database dialects (PostgreSQL, MySQL, and SQLite) without code duplication, the project uses a layered architecture:

    1. Feature Implementations (src/extensions): Contains the core logic for features (e.g., upsert, findMany). These are written once using a dialect-agnostic approach.
    2. Dialect Registry (src/codegen/registry.ts): The source of truth for each dialect's capabilities (e.g., returning support, upsert support) and its specific Drizzle module paths.
    3. Runtime Adapter Layer (src/internal/dialects): Handles real-time differences in how SQL is executed, such as how PostgreSQL handles limited updates via CTEs versus how MySQL/SQLite use native limit clauses.
    4. Core Type/Runtime Facade (src/internal): Provides a unified interface (QueryContext) to extract relational query information (tables, columns, schema) from Drizzle builders, shielding feature code from private Drizzle internals.
    5. Codegen Layer (src/codegen): A structured emitter that transforms feature metadata and placeholder imports into the final, dialect-specific generated files found in src/generated/.

  12. Use the #dialect placeholder for dialect-agnostic feature development

    main

    When writing new features in src/extensions, do not import directly from drizzle-orm/pg-core or other dialect-specific paths. Instead, use the #dialect placeholder module. This allows the code to remain portable across PostgreSQL, MySQL, and SQLite. The generator will automatically rewrite these placeholder imports into the correct Drizzle dialect entrypoints during the build process.

    Supported placeholder types include:

    • Database
    • Table
    • Column
    • InsertBuilder
    • InsertValue
    • RelationalQuery
    • RelationalQueryBuilder
    • Session
    • UpdateBase
    • UpdateSetSource

    Note: If you need a base Drizzle type that shares a name with a placeholder (like Table), import it using a local alias (e.g., import { Table as BaseTable } from '#dialect').

    import type {
  Column,
  Database,
  InsertBuilder,
  InsertValue,
  RelationalQuery,
  RelationalQueryBuilder,
  Session,
  Table,
  UpdateBase,
  UpdateSetSource,
} from '#dialect'