Drizzle Adapter

@better-tables/adapters-drizzle turns defineTable columns + filter/sort/page state into Drizzle queries.

import { drizzleAdapter } from '@better-tables/adapters-drizzle';
import { betterTables, defineTable } from '@better-tables/core';

export const tables = betterTables({
  database: drizzleAdapter(db),
});

export const ticketsTable = defineTable<typeof tables>()('tickets', (t) => ({
  columns: [
    t.text('subject').filterable().sortable(),
    t.text('customer.company').displayName('Customer').filterable(),
    t.option('status').filterable(),
  ],
}));

Factory options (complete)

drizzleAdapter(db, factoryOptions?) auto-detects the schema, driver, and relations from your Drizzle instance; every option is an override:

Relationship paths

Dot paths in column ids (customer.company, customer.plan) drive automatic JOINs from Drizzle relations / FK metadata. Filter and sort on those paths the same way as own-table columns.

When auto-detection doesn't capture a relationship (or you need to override one), provide a manual RelationshipMap keyed by column id:

const adapter = drizzleAdapter(db, {
  relationships: {
    'preferences.theme': {
      from: 'users',
      to: 'user_preferences',
      foreignKey: 'userId',   // FK field in the target table
      localKey: 'id',         // key field in the source table
      cardinality: 'one',     // 'one' | 'many'
      nullable: true,
      joinType: 'left',       // 'left' | 'inner'
      isArray: false,         // true for array-FK relationships
    },
  },
});

PostgreSQL .array() foreign keys (with .references()) are detected automatically. MySQL json() / SQLite text-or-JSON array FKs are not auto-detected — provide a manual RelationshipMap entry with isArray: true. Deeper patterns — junction tables, aggregates, subqueries — are covered with worked examples in the package's advanced usage notes (some samples still show a removed pre-0.6 builder API; the concepts apply unchanged, the syntax doesn't).

Try it live: /examples/relationship-filtering.

Derived aggregates (t.count)

Prefer declarative columns over hand-rolled computedFields for relation counts:

t.count('posts').displayName('Posts'), // id: postsCount

The adapter validates the many-relation on the primary table, lowers the spec to a correlated subquery on SELECT / WHERE / ORDER BY (via the existing computed-fields pipeline), and supports filters inside FilterGroupNode trees. Index the FK on the related table (posts.user_id) — each row pays one subquery.

See Derived columns.

Facets

getFacetedValues / getMinMaxValues / fetchData({ faceted }) power filter option counts and range facets, with self-exclusion handled by the adapter. See the Facets guide and /examples/facets.

Invalid filters

When a filter leaf can't be translated to SQL — an operator that isn't valid for the column's type, or a supported operator with missing/incomplete values — the adapter has to decide between dropping it and failing. Dropping widens the result set: under implicit-AND, a discarded predicate reads as "no restriction", so a filter meant to scope rows silently returns everything.

options.onInvalidFilter controls this:

• 'skip' (default) — drop the leaf and keep going, so partial URL/UI filter state still fetches. An invalid operator logs a [better-tables] warning; genuinely incomplete values stay silent (normal mid-edit state).
• 'throw' — raise a QueryError instead of dropping. Use this when filters are built programmatically and every one must apply — a dropped tenant or owner guard is a data-exposure bug, not a UI convenience.

// A server-side scoping filter that must never be silently dropped:
const adapter = drizzleAdapter(db, {
  options: { defaultPrimaryTable: 'users', onInvalidFilter: 'throw' },
});

The legitimate "match null rows only" request (includeNull with empty values) is a real condition, not a drop, and is unaffected by either mode.

Databases

Postgres, MySQL, and SQLite are supported. Peer dependencies are your driver's (postgres, mysql2, better-sqlite3) plus drizzle-orm. Integration tests for MySQL/Postgres live under packages/adapters/drizzle/tests and run in CI against real Postgres and MySQL service containers (they self-skip locally when no DB URL is set; a CI guard fails the build if those URLs ever go missing so the suites can't silently skip).

Performance tuning

• Caching: options.cache memoizes query results with TTL + LRU eviction — effective for facet counts on hot tables.
• Reserved options: optimization, batching, logging, and performance appear in the type surface but are not applied at runtime yet. Large-array filters already use fixed internal batching; do not expect configuring those fields to change behavior.

Cell writes

When your app uses adapter-level updateRecord, the Drizzle adapter resolves write targets (including related rows) through resolveCellWriteTarget. Prefer monolith tables.cellEditAction when you can — see Inline editing.

Package docs

npm README: packages/adapters/drizzle/README.md. Building your own adapter instead? Start at Custom adapters.