Drizzle ORM Adapter
Relevant source files
The following files were used as context for generating this wiki page:
The Drizzle ORM Adapter is a specialized database adapter designed to integrate Drizzle ORM with the Better Tables framework. It provides end-to-end type safety, automatic relationship detection, and complex query generation for SQL databases including Postgres, MySQL, and SQLite. Sources: packages/adapters/drizzle/src/index.ts, README.md:167-172
The adapter functions by introspecting Drizzle schemas to automatically map one-to-one, one-to-many, and many-to-many relationships. This enables developers to reference related data using dot-notation (e.g., profile.location) in column definitions while the adapter handles the underlying JOIN queries and filter translations. Sources: README.md:213-225
Core Architecture
The Drizzle adapter is built upon a modular architecture that separates schema introspection, relationship management, and dialect-specific SQL generation.
The diagram shows the primary internal components of the Drizzle Adapter and their relationship to the Drizzle ORM instance.
Sources: packages/adapters/drizzle/src/index.ts:25-33
Key Components
| Component | Description |
|---|---|
DrizzleAdapter | The main class implementing the TableAdapter contract. |
RelationshipDetector | Automatically identifies relationships (1:1, 1:N, N:M) from Drizzle schemas. |
RelationshipManager | Manages path resolution and JOIN plan generation for related data. |
Query Builders | Dialect-specific builders (Postgres, MySQL, SQLite) for generating SQL queries. |
DataTransformer | Re-exported from the toolkit; transforms flat SQL result sets into nested objects. |
FilterHandler | Maps framework filter operators to Drizzle-specific SQL conditions. |
Sources: packages/adapters/drizzle/src/index.ts:10-33, README.md:211-230
Query Execution Flow
When a data request is made, the adapter processes parameters through a multi-stage pipeline to produce the final result set.
The sequence diagram illustrates the lifecycle of a fetchData call within the Drizzle adapter.
Sources: packages/adapters/drizzle/src/drizzle-adapter.ts:418-460, packages/adapters/drizzle/src/query-builders/base-query-builder.ts:786-846
Relationship Detection and JOINS
The adapter uses sophisticated introspection to detect foreign keys and relationship definitions in the Drizzle schema. When a column path like posts.title is encountered, the RelationshipManager identifies the foreign key, generates the appropriate JOIN, and applies filters across the related tables. Sources: README.md:215-225, packages/adapters/drizzle/src/index.ts:55-56
Derived and Aggregate Columns
The adapter supports server-side derived columns, such as counts of related records (e.g., "number of posts per user"). These are defined using declarative specs that the adapter lowers into correlated subqueries. Sources: plans/060-derived-aggregate-columns.md:46-55
Aggregation Mechanism
- Spec Identification: The adapter receives a
DerivedColumnSpec(e.g.,{ fn: 'count', relation: 'posts' }). - SQL Lowering: The adapter generates a correlated subquery:
(SELECT count(*) FROM posts WHERE posts.user_id = users.id) AS "postsCount". - Filtering/Sorting: These derived values are integrated into the
computedFieldspipeline, allowing them to be used inWHEREandORDER BYclauses.
Sources: plans/060-derived-aggregate-columns.md:144-165, README.md:132-135
Configuration and Usage
The adapter is typically instantiated through a factory function that automatically detects the database schema and driver. Sources: packages/adapters/drizzle/src/factory.ts:89-92
Factory Configuration Options
| Option | Type | Default | Description |
|---|---|---|---|
defaultPrimaryTable | string | N/A | The default table to query if none is specified. |
cache.enabled | boolean | false | Enables LRU caching for query results. |
cache.ttl | number | 300000 | Time-to-live for cached results in milliseconds. |
logging.enabled | boolean | false | Enables adapter-level logging. |
performance.trackTiming | boolean | false | Tracks query execution time. |
Sources: packages/adapters/drizzle/examples/basic-usage.ts:246-253, packages/adapters/drizzle/src/types.ts
Example Implementation
import { drizzleAdapter } from '@better-tables/adapters-drizzle';
import { drizzle } from 'drizzle-orm/better-sqlite3';
// 1. Initialize Drizzle with schema
const db = drizzle(sqlite, { schema: { users, posts, usersRelations } });
// 2. Create the adapter
const adapter = drizzleAdapter(db);
// 3. Fetch data with automatic relationships
const result = await adapter.fetchData({
columns: ['name', 'posts.title'],
filters: [
{ columnId: 'posts.published', type: 'boolean', operator: 'isTrue', values: [] }
]
});
Sources: README.md:160-180, packages/adapters/drizzle/examples/basic-usage.ts:240-265
Advanced Features
Faceted Values and Options
The adapter provides methods for generating UI metadata based on the current data state:
getFilterOptions: Returns distinct values for a column (e.g., all unique categories).getFacetedValues: Returns counts of rows for each option value, supporting self-exclusion (filtering the counts based on other active filters).getMinMaxValues: Retrieves the range of numeric or date values for a column.
Sources: packages/adapters/drizzle/src/drizzle-adapter.ts, packages/adapters/drizzle/examples/basic-usage.ts:285-287
Write Operations
The adapter supports type-safe write operations including createRecord, updateRecord, and deleteRecord. In multi-table schemas, it employs a resolveMutationTable heuristic to determine the target table or requires an explicit defaultMutationTable configuration. Sources: packages/adapters/drizzle/src/drizzle-adapter.ts:907-982, packages/adapters/drizzle/examples/basic-usage.ts:289-290
Summary
The Drizzle ORM Adapter serves as the high-performance link between Better Tables and SQL databases. By automating relationship handling and SQL generation, it eliminates the need for manual JOIN construction while preserving full TypeScript type safety across the entire data lifecycle. It is a core component of the framework's "define once, use everywhere" philosophy. Sources: README.md:10-15, packages/adapters/drizzle/src/index.ts:35-40