@better-tables/adapters-drizzle

A powerful Drizzle ORM adapter for Better Tables that provides automatic relationship detection, smart join generation, and full TypeScript type safety across PostgreSQL, MySQL, and SQLite databases.

Features

- 🚀 Automatic Relationship Detection - Automatically detects table relationships from Drizzle's relations() API
- 🔗 Smart Join Generation - Only joins tables needed for current query (filters/sorts/columns)
- 🎯 Dot Notation Support - Use profile.bio and posts.title for intuitive column access
- 🏗️ Nested Data Structures - Preserves relationship hierarchy in query results
- 📊 Aggregate Support - Built-in support for counts, sums, averages, and more
- 🔍 Cross-Table Filtering - Filter and sort across multiple tables seamlessly
- ⚡ Performance Optimized - Query caching, join optimization, and batch processing
- 🛡️ Full Type Safety - Complete TypeScript support with schema inference
- 🗄️ Multi-Database - Support for PostgreSQL, MySQL, and SQLite
- 🏭 Factory Function - Simple API with automatic schema and driver detection
- 🔢 Array Foreign Keys - Native support for array foreign key relationships (PostgreSQL arrays, MySQL/SQLite JSON arrays)

Installation

``bash


Install the adapter

npm install @better-tables/adapters-drizzle

Install your database driver (choose one)

npm install postgres # For PostgreSQL
npm install mysql2 # For MySQL
npm install better-sqlite3 # For SQLite
`

Quick Start

$3

`typescript
import { sqliteTable, text, integer } from 'drizzle-orm/sqlite-core';
import { relations } from 'drizzle-orm';

// Tables
const users = sqliteTable('users', {
id: integer('id').primaryKey(),
name: text('name').notNull(),
email: text('email').notNull(),
age: integer('age'),
});

const profiles = sqliteTable('profiles', {
id: integer('id').primaryKey(),
userId: integer('user_id').notNull().references(() => users.id),
bio: text('bio'),
avatar: text('avatar'),
});

const posts = sqliteTable('posts', {
id: integer('id').primaryKey(),
userId: integer('user_id').notNull().references(() => users.id),
title: text('title').notNull(),
content: text('content'),
published: integer('published', { mode: 'boolean' }).default(false),
});

// Relations
const usersRelations = relations(users, ({ one, many }) => ({
profile: one(profiles, {
fields: [users.id],
references: [profiles.userId],
}),
posts: many(posts),
}));

const profilesRelations = relations(profiles, ({ one }) => ({
user: one(users, {
fields: [profiles.userId],
references: [users.id],
}),
}));

const postsRelations = relations(posts, ({ one }) => ({
user: one(users, {
fields: [posts.userId],
references: [users.id],
}),
}));

const schema = { users, profiles, posts };
const relationsSchema = {
users: usersRelations,
profiles: profilesRelations,
posts: postsRelations,
};
`

$3

`typescript
import { drizzle } from 'drizzle-orm/better-sqlite3';
import Database from 'better-sqlite3';

// For SQLite
const sqlite = new Database('database.db');
const db = drizzle(sqlite, { schema: { ...schema, ...relationsSchema } });

// For PostgreSQL
// import { drizzle } from 'drizzle-orm/postgres-js';
// import postgres from 'postgres';
// const sql = postgres('postgresql://user:password@localhost:5432/database');
// const db = drizzle(sql, { schema: { ...schema, ...relationsSchema } });

// For MySQL
// import { drizzle } from 'drizzle-orm/mysql2';
// import mysql from 'mysql2/promise';
// const connection = await mysql.createConnection({
// host: 'localhost',
// user: 'user',
// password: 'password',
// database: 'database'
// });
// const db = drizzle(connection, { schema: { ...schema, ...relationsSchema } });
`

$3

The factory function automatically detects the schema and driver from your Drizzle instance:

`typescript
import { drizzleAdapter } from '@better-tables/adapters-drizzle';

// Simple usage - everything auto-detected
const adapter = drizzleAdapter(db);

// With options
const adapter = drizzleAdapter(db, {
options: {
cache: { enabled: true, ttl: 300000, maxSize: 1000 },
logging: { enabled: true, level: 'info' },
},
});
`

$3

`typescript
import { createColumnBuilder } from '@better-tables/core';

const cb = createColumnBuilder();

const columns = [
// Direct columns
cb.text().id('name').displayName('Name').accessor(user => user.name).build(),
cb.text().id('email').displayName('Email').accessor(user => user.email).build(),

// One-to-one relationship
cb.text().id('profile.bio').displayName('Bio').accessor(user => user.profile?.bio).build(),
cb.text().id('profile.avatar').displayName('Avatar').accessor(user => user.profile?.avatar).build(),

// One-to-many relationship (first post)
cb.text().id('posts.title').displayName('Latest Post').accessor(user => user.posts?.[0]?.title).build(),

// Aggregate columns
cb.number().id('posts_count').displayName('Post Count').accessor(user => user.posts?.length || 0).build(),
];
`

$3

`typescript
import { BetterTable } from '@better-tables/ui';

function UserTable() {
return (
adapter={adapter}
columns={columns}
// All Better Tables features work seamlessly:
// - Filtering across relationships
// - Sorting by related fields
// - Pagination
// - Virtual scrolling
// - Export functionality
/>
);
}
`

Advanced Usage

$3

For more control, you can use the constructor directly:

`typescript
import { DrizzleAdapter } from '@better-tables/adapters-drizzle';

// REQUIRED: Specify the driver type explicitly for proper type safety
const adapter = new DrizzleAdapter({
db,
schema,
driver: 'sqlite', // 'postgres' | 'mysql' | 'sqlite'
relations: relationsSchema, // For auto-detection
autoDetectRelationships: true,
options: {
cache: { enabled: true, ttl: 300000 },
},
});
`

$3

If you need more control over relationships, you can provide manual mappings:

`typescript
const adapter = drizzleAdapter(db, {
autoDetectRelationships: false,
relationships: {
'profile.bio': {
from: 'users',
to: 'profiles',
foreignKey: 'userId',
localKey: 'id',
cardinality: 'one',
nullable: true,
joinType: 'left'
},
'posts.title': {
from: 'users',
to: 'posts',
foreignKey: 'userId',
localKey: 'id',
cardinality: 'many',
joinType: 'left'
}
}
});
`

$3

`typescript
const adapter = drizzleAdapter(db, {
options: {
cache: {
enabled: true,
ttl: 300000, // 5 minutes
maxSize: 1000
},
optimization: {
maxJoins: 5,
enableBatching: true,
batchSize: 1000
},
logging: {
enabled: true,
level: 'info',
logQueries: false
},
performance: {
trackTiming: true,
maxQueryTime: 5000
}
}
});
`

$3

`typescript
import type { FilterState } from '@better-tables/core';

// Filter across multiple tables
const filters: FilterState[] = [
{
columnId: 'name',
type: 'text',
operator: 'contains',
values: ['John']
},
{
columnId: 'profile.bio',
type: 'text',
operator: 'isNotEmpty',
values: []
},
{
columnId: 'posts_count',
type: 'number',
operator: 'greaterThan',
values: [5]
}
];

const result = await adapter.fetchData({
columns: ['name', 'email', 'profile.bio'],
filters
});
`

$3

When working with JSONB accessor columns or ambiguous column names, you can explicitly specify the primary table:

`typescript
// Explicit primary table - recommended for clarity and to avoid ambiguity
const result = await adapter.fetchData({
primaryTable: 'surveys',
columns: ['title', 'slug', 'status'],
// 'title' may be accessed via accessor from survey.survey.title (JSONB)
// Explicit primaryTable ensures correct table selection
});

// Automatic determination - adapter infers from columns
const result = await adapter.fetchData({
columns: ['id', 'slug', 'status'], // All direct columns
// Adapter will automatically determine 'surveys' as primary table
});
`

When to use explicit primaryTable:
- When column IDs reference JSONB nested fields via accessors
- When column IDs are ambiguous across multiple tables
- When you want explicit control over table selection
- For better code clarity and maintainability

Automatic determination:
- The adapter uses improved heuristics to determine the primary table
- Prefers tables with the most matching direct columns
- Falls back to first table when truly ambiguous
- Works well when all columns are direct schema columns

$3

Computed fields allow you to add virtual columns that are calculated at runtime. These fields don't exist in the database schema but are computed from the row data, related tables, or any other source.

Basic Example:

`typescript
import { DrizzleAdapter } from '@better-tables/adapters-drizzle';
import { count, eq } from 'drizzle-orm';

const adapter = new DrizzleAdapter({
db,
schema,
driver: 'postgres',
computedFields: {
eventsTable: [
{
field: 'attendeeCount',
type: 'number',
compute: async (row, context) => {
const result = await context.db
.select({ count: count() })
.from(eventAttendeesTable)
.where(eq(eventAttendeesTable.eventId, row.id));
return result[0]?.count || 0;
},
filter: async (filter, context) => {
// Transform attendeeCount filter into id filter
const matchingIds = await getEventIdsByAttendeeCount(filter, context);
return [{
columnId: 'id',
operator: 'isAnyOf',
values: matchingIds,
type: 'text',
}];
},
// Alternative: Use filterSql for better performance (applied before pagination)
// filterSql: async (filter, context) => {
// // Return SQL condition directly - more efficient for large result sets
// return sqlEXISTS (
// SELECT 1 FROM event_attendees
// WHERE event_id = ${eventsTable.id}
// GROUP BY event_id
// HAVING COUNT(*) > ${filter.values[0]}
// );
// },
},
],
},
});

// Use computed field in queries
const result = await adapter.fetchData({
columns: ['title', 'attendeeCount'],
filters: [
{ columnId: 'attendeeCount', operator: 'greaterThan', values: [10], type: 'number' },
],
});
`

Simple Calculation Example:

`typescript
computedFields: {
usersTable: [
{
field: 'fullName',
type: 'text',
compute: (row) => ${row.firstName} ${row.lastName},
},
{
field: 'age',
type: 'number',
compute: (row) => {
const birthDate = new Date(row.birthDate);
const today = new Date();
return today.getFullYear() - birthDate.getFullYear();
},
},
],
},
`

With Filtering Support:

`typescript
import { count, eq, gt, sql } from 'drizzle-orm';

computedFields: {
eventsTable: [
{
field: 'attendeeCount',
type: 'number',
compute: async (row, context) => {
// Compute from related table
const result = await context.db
.select({ count: count() })
.from(eventAttendeesTable)
.where(eq(eventAttendeesTable.eventId, row.id));
return result[0]?.count || 0;
},
// Option 1: Use filter to return FilterState[] (queries all matching IDs first)
filter: async (filter, context) => {
// Transform computed field filter into database filter
const eventAttendeeCounts = context.db
.select({ eventId: eventAttendeesTable.eventId })
.from(eventAttendeesTable)
.groupBy(eventAttendeesTable.eventId)
.having(gt(count(eventAttendeesTable.userId), filter.values[0]))
.as('event_attendee_counts');

const matchingIds = await context.db
.select({ id: eventAttendeeCounts.eventId })
.from(eventAttendeeCounts);

return [{
columnId: 'id',
operator: 'isAnyOf',
values: matchingIds.map(r => r.id),
type: 'text',
}];
},
},
],
},
`

Using filterSql for Better Performance:

For large result sets, use filterSql instead of filter to return SQL conditions directly. This applies the filter in the WHERE clause before pagination, making it much more efficient:

`typescript
import { sql } from 'drizzle-orm';

computedFields: {
usersTable: [
{
field: 'demographics.language',
type: 'text',
compute: async (row) => {
// Extract language from JSONB
const demographics = row.demographics as { language?: Array<{ code: string }> };
return demographics?.language?.[0]?.code || null;
},
// Use filterSql for direct SQL condition (applied before pagination)
filterSql: async (filter, context) => {
const languageCode = filter.values?.[0];
const languageArrayJson = JSON.stringify([{ code: languageCode }]);
// Return SQL condition directly - more efficient than querying all IDs first
return sql(${usersTable.demographics}->'language') @> ${languageArrayJson};
},
},
],
},
`

Key Features:

- Runtime Computation: Fields are computed after data is fetched
- Database Queries: Can query related tables using context.db
- Filtering Support: Optional filter function to transform computed field filters into database queries
- Direct SQL Conditions: Optional filterSql function for better performance (applied before pagination)
- Batch Processing: context.allRows provides all rows for batch computation
- Type Safety: Full TypeScript support with proper types

When to use filter vs filterSql:
- Use filter when you need to query all matching IDs first (e.g., complex joins)
- Use filterSql when you can express the filter as a direct SQL condition (better performance)

See Advanced Usage Guide for more examples.

$3

Important: The adapter internally stores and resolves relationships using schema keys (e.g., usersTable, eventsTable) rather than raw database table names (e.g., users, events). This ensures consistency with Drizzle's schema object structure. When defining custom relationships or debugging, ensure you refer to tables by their schema keys.

The adapter automatically handles conversion from database table names to schema keys:
- If your schema keys differ from database table names (e.g., { usersTable: usersTable } where DB name is 'users'), the adapter will find the correct schema key.
- If your schema keys match database table names (e.g., { users: usersTable }), the adapter will use the database table name as the schema key (backward compatibility).

$3

The adapter automatically detects and handles array foreign key relationships. This is useful for scenarios where a column contains an array of foreign key references.

Auto-Detection: The adapter automatically detects array FK relationships from your schema definition. You don't need to manually configure them - just define your columns with .references() and .array():

`typescript
// PostgreSQL example
organizerId: uuid('organizer_id')
.references(() => usersTable.id)
.array()
.notNull()
`

The adapter will automatically:
- Detect that organizerId is an array column
- Extract the foreign key reference from .references(() => usersTable.id)
- Create a relationship named events.organizers (auto-pluralized from organizerId)
- Enable joins and filtering on the related users table

PostgreSQL Example:
`typescript
import { pgTable, uuid } from 'drizzle-orm/pg-core';

const events = pgTable('events', {
id: uuid('id').primaryKey(),
title: text('title').notNull(),
organizerId: uuid('organizer_id')
.array()
.references(() => users.id)
.notNull(),
});

// The adapter automatically detects this as an array FK relationship
// You can now query organizer data directly:
const columns = [
cb.text().id('organizers.name').displayName('Organizers')
.accessor(event => event.organizers?.map(org => org.name).join(', ') || '')
.build(),
];

// The adapter will automatically join with users table using array FK join
const result = await adapter.fetchData({
columns: ['title', 'organizers.name', 'organizers.username'],
});
// Returns: { title: 'Event', organizers: [{ name: 'John', username: 'john' }, ...] }
`

MySQL/SQLite Example:
`typescript
import { json, mysqlTable } from 'drizzle-orm/mysql-core';

const events = mysqlTable('events', {
id: varchar('id', { length: 36 }).primaryKey(),
title: varchar('title', { length: 255 }).notNull(),
organizerId: json('organizer_id'), // JSON array of user IDs
});

// Works the same way - adapter detects JSON array columns with FK references
`

The adapter uses database-specific syntax for array joins:
- PostgreSQL: target = ANY(source_array) (native array support)
- MySQL: JSON_SEARCH(source_array, 'one', target) IS NOT NULL (JSON array support)
- SQLite: EXISTS (SELECT 1 FROM json_each(source_array) WHERE value = target) (JSON array support)

$3

`typescript
const columns = [
// Count of related records
cb.number().id('posts_count').displayName('Posts').accessor(user => user.posts?.length || 0).build(),

// Sum of related values
cb.number().id('total_views').displayName('Total Views').accessor(user =>
user.posts?.reduce((sum, post) => sum + (post.views || 0), 0) || 0
).build(),

// Average of related values
cb.number().id('avg_rating').displayName('Avg Rating').accessor(user => {
const ratings = user.posts?.map(post => post.rating).filter(Boolean) || [];
return ratings.length > 0 ? ratings.reduce((sum, rating) => sum + rating, 0) / ratings.length : 0;
}).build(),
];
`

$3

`typescript
// app/page.tsx
import { drizzleAdapter } from '@better-tables/adapters-drizzle';
import type { FilterState, SortingState } from '@better-tables/core';
import { getDatabase } from '@/lib/db';

export default async function Page({ searchParams }: { searchParams: Promise> }) {
const params = await searchParams;

// Get database connection
const { db } = await getDatabase();

// Create adapter (can be cached at module level)
const adapter = drizzleAdapter(db);

// Parse URL params
const page = Number.parseInt(params.page || '1', 10);
const limit = Number.parseInt(params.limit || '10', 10);

// Deserialize filters (compressed format, prefixed with "c:")
let filters: FilterState[] = [];
if (params.filters) {
try {
const { deserializeFiltersFromURL } = await import('@better-tables/core');
filters = deserializeFiltersFromURL(params.filters);
} catch {
// Invalid or corrupted filter data, use empty array
filters = [];
}
}

// Deserialize sorting (compressed format, prefixed with "c:")
let sorting: SortingState = [];
if (params.sorting) {
try {
const { decompressAndDecode } = await import('@better-tables/core');
sorting = decompressAndDecode(params.sorting);
} catch {
// Invalid or corrupted sorting data, use empty array
sorting = [];
}
}

// Fetch data
const result = await adapter.fetchData({
columns: ['name', 'email', 'profile.bio'],
pagination: { page, limit },
filters,
sorting,
});

return

;
}
`

API Reference

$3

#### drizzleAdapter(db, factoryOptions?)

The recommended way to create an adapter. Automatically detects schema and driver.

`typescript
function drizzleAdapter(
db: TDB,
factoryOptions?: DrizzleAdapterFactoryOptions
): DrizzleAdapter
`

Parameters:
- db - The Drizzle database instance (schema and driver auto-detected)
- factoryOptions - Optional configuration:
- schema? - Override auto-detected schema
- driver? - Override auto-detected driver
- relations? - Provide relations for auto-detection
- relationships? - Manual relationship mappings
- autoDetectRelationships? - Enable/disable auto-detection (default: true)
- options? - Adapter configuration options
- meta? - Custom adapter metadata

Returns: Fully typed DrizzleAdapter instance

$3

The main adapter class that implements the TableAdapter interface.

#### Constructor

`typescript
new DrizzleAdapter(config: DrizzleAdapterConfig)
`

Configuration:
`typescript
interface DrizzleAdapterConfig {
db: DrizzleDatabase; // Drizzle database instance
schema: TSchema; // Schema with tables
driver: TDriver; // 'postgres' | 'mysql' | 'sqlite'
relations?: Record; // Drizzle relations for auto-detection
autoDetectRelationships?: boolean; // Enable auto-detection (default: true)
relationships?: RelationshipMap; // Manual relationship mappings
computedFields?: { // Computed/virtual fields
[K in keyof TSchema]?: ComputedFieldConfig[];
};
options?: DrizzleAdapterOptions; // Adapter options
hooks?: FilterHandlerHooks; // Filter handler hooks for customization
meta?: Partial; // Custom metadata
}
`

#### Methods

- fetchData(params) - Fetch data with filtering, sorting, and pagination
- params.columns - Array of column IDs to fetch
- params.primaryTable? - Explicit primary table (optional, auto-detected if not provided)
- params.filters? - Array of filter states
- params.sorting? - Array of sort states
- params.pagination? - Pagination configuration
- Returns: Promise>

- getFilterOptions(columnId) - Get available filter options for a column
- getFacetedValues(columnId) - Get faceted values for a column
- getMinMaxValues(columnId) - Get min/max values for number columns
- createRecord(data) - Create a new record
- updateRecord(id, data) - Update an existing record
- deleteRecord(id) - Delete a record
- bulkUpdate(ids, data) - Bulk update records
- bulkDelete(ids) - Bulk delete records
- exportData(params) - Export data in various formats
- subscribe(callback) - Subscribe to real-time updates

$3

Maps column IDs to relationship paths:

`typescript
interface RelationshipMap {
[columnId: string]: RelationshipPath;
}

interface RelationshipPath {
from: string; // Source table
to: string; // Target table
foreignKey: string; // Foreign key field in target table
localKey: string; // Local key field in source table
cardinality: 'one' | 'many';
nullable?: boolean;
joinType?: 'left' | 'inner';
}
`

Database-Specific Notes

$3

The Drizzle adapter supports filtering on PostgreSQL array columns (e.g., uuid[], text[], integer[]) using multiOption filter types. This enables efficient filtering on array columns that store relationships, tags, permissions, or other multi-value fields.

#### Supported Array Types

- uuid[] - UUID arrays
- text[] - Text arrays
- integer[] - Integer arrays
- bigint[] - BigInt arrays
- boolean[] - Boolean arrays
- numeric[] - Numeric arrays
- varchar[] - Varchar arrays

#### Schema Definition

Define array columns in your Drizzle schema:

`typescript
import { pgTable, uuid, text, integer } from 'drizzle-orm/pg-core';

export const eventsTable = pgTable('events', {
id: uuid('id').primaryKey(),
name: text('name').notNull(),
organizerIds: uuid('organizer_ids').array().notNull(), // uuid[]
tags: text('tags').array(), // text[]
categoryIds: integer('category_ids').array(), // integer[]
});
`

#### Supported Operators

Option Operators (for type: 'option'):
- isAnyOf - Array overlaps with any of the specified values (column && ARRAY[values]::type[])
- isNoneOf - Array does not overlap with any of the specified values (NOT (column && ARRAY[values]::type[]))
- isNull - Array column is NULL
- isNotNull - Array column is not NULL

MultiOption Operators (for type: 'multiOption'):
- includes - Array contains a specific value (column @> ARRAY[value]::type[])
- excludes - Array does not contain a specific value (NOT (column @> ARRAY[value]::type[]))
- includesAny - Array overlaps with any of the specified values (column && ARRAY[values]::type[])
- includesAll - Array contains all of the specified values (column @> ARRAY[values]::type[])
- excludesAny - Array does not overlap with any of the specified values (NOT (column && ARRAY[values]::type[]))
- excludesAll - Array does not contain all of the specified values (NOT (column @> ARRAY[values]::type[]))
- isNull - Array column is NULL
- isNotNull - Array column is not NULL

#### Usage Examples

Filter events by organizer IDs (uuid[]):

`typescript
const result = await adapter.fetchData({
primaryTable: 'events',
columns: ['id', 'name', 'organizerIds'],
filters: [
{
columnId: 'organizerIds',
operator: 'isAnyOf',
values: ['019a4f81-2758-73f9-9bc2-5832f88c056c', '019a4f81-2758-73f9-9bc2-5832f88c056d'],
type: 'option',
},
],
});
`

Filter events by tags (text[]):

`typescript
const result = await adapter.fetchData({
primaryTable: 'events',
columns: ['id', 'name', 'tags'],
filters: [
{
columnId: 'tags',
operator: 'includesAny',
values: ['typescript', 'javascript'],
type: 'multiOption',
},
],
});
`

Filter events by category IDs (integer[]):

`typescript
const result = await adapter.fetchData({
primaryTable: 'events',
columns: ['id', 'name', 'categoryIds'],
filters: [
{
columnId: 'categoryIds',
operator: 'includesAll',
values: ['1', '3'], // Note: values are strings in FilterState
type: 'multiOption',
},
],
});
`

Filter events with NULL array columns:

`typescript
const result = await adapter.fetchData({
primaryTable: 'events',
columns: ['id', 'name', 'tags'],
filters: [
{
columnId: 'tags',
operator: 'isNull',
values: [],
type: 'multiOption',
},
],
});
`

#### Performance Considerations

- GIN Indexes: For optimal performance with array columns, create GIN indexes:

`sql
CREATE INDEX idx_events_organizer_ids ON events USING GIN (organizer_ids);
CREATE INDEX idx_events_tags ON events USING GIN (tags);
`

- Array Operators: The adapter uses PostgreSQL's native array operators (&&, @>) which are optimized for array columns and can leverage GIN indexes.

#### Common Use Cases

1. Event Management Systems: Filter events by organizer IDs stored as uuid[]
2. Content Management Systems: Filter posts/articles by tag IDs or category IDs
3. Permission Systems: Filter users by role arrays or resources by permission arrays
4. Many-to-Many Relationships: Handle many-to-many relationships stored as arrays in PostgreSQL

$3

`typescript
import { drizzle } from 'drizzle-orm/postgres-js';
import postgres from 'postgres';
import { drizzleAdapter } from '@better-tables/adapters-drizzle';

const sql = postgres('postgresql://user:password@localhost:5432/database');
const db = drizzle(sql, { schema: { ...schema, ...relationsSchema } });

const adapter = drizzleAdapter(db);
`

$3

`typescript
import { drizzle } from 'drizzle-orm/mysql2';
import mysql from 'mysql2/promise';
import { drizzleAdapter } from '@better-tables/adapters-drizzle';

const connection = await mysql.createConnection({
host: 'localhost',
user: 'user',
password: 'password',
database: 'database'
});
const db = drizzle(connection, { schema: { ...schema, ...relationsSchema } });

const adapter = drizzleAdapter(db);
`

$3

`typescript
import { drizzle } from 'drizzle-orm/better-sqlite3';
import Database from 'better-sqlite3';
import { drizzleAdapter } from '@better-tables/adapters-drizzle';

const sqlite = new Database('database.db');
const db = drizzle(sqlite, { schema: { ...schema, ...relationsSchema } });

const adapter = drizzleAdapter(db);
`

Error Handling

The adapter provides specific error types for different scenarios:

`typescript
import {
DrizzleAdapterError,
RelationshipError,
QueryError,
SchemaError
} from '@better-tables/adapters-drizzle';

try {
const result = await adapter.fetchData({ filters: invalidFilters });
} catch (error) {
if (error instanceof RelationshipError) {
console.error('Relationship issue:', error.message);
} else if (error instanceof QueryError) {
console.error('Query issue:', error.message);
} else if (error instanceof SchemaError) {
console.error('Schema issue:', error.message);
}
}
`

Performance Tips

1. Use Indexes - Ensure foreign key columns are indexed
2. Limit Joins - Use maxJoins option to prevent excessive joins
3. Enable Caching - Use query result caching for repeated requests
4. Batch Large Queries - Enable batching for large datasets
5. Monitor Performance - Enable performance tracking to identify slow queries
6. Cache Adapter Instance - Reuse the adapter instance instead of creating new ones

Migration from Raw Drizzle

If you're migrating from raw Drizzle queries, the adapter provides a seamless transition:

$3

`typescript
const users = await db
.select()
.from(usersTable)
.leftJoin(profilesTable, eq(usersTable.id, profilesTable.userId))
.leftJoin(postsTable, eq(usersTable.id, postsTable.userId))
.where(and(
ilike(usersTable.name, '%John%'),
eq(postsTable.published, true)
))
.orderBy(asc(usersTable.name))
.limit(10);
`

$3

`typescript
import type { FilterState, SortingState } from '@better-tables/core';

const filters: FilterState[] = [
{ columnId: 'name', type: 'text', operator: 'contains', values: ['John'] },
{ columnId: 'posts.published', type: 'boolean', operator: 'isTrue', values: [] }
];

const sorting: SortingState = [{ columnId: 'name', direction: 'asc' }];

const result = await adapter.fetchData({
columns: ['name', 'email', 'profile.bio', 'posts.title'],
filters,
sorting,
pagination: { page: 1, limit: 10 }
});
`

Examples

See the demo app for a complete working example:

- Adapter Setup: apps/demo/lib/adapter.ts
- Column Definitions: apps/demo/lib/columns/user-columns.tsx
- Server Component: apps/demo/app/page.tsx

Documentation

For detailed documentation, see:

- Core Package README - Column builders and state management
- Getting Started Guide - Installation and setup
- Adapters Architecture - How adapters work
- Advanced Usage - Advanced patterns and examples

Contributing

Contributions are welcome! This is an open-source project, and we appreciate any help you can provide.

$3

1. Fork the repository
2. Create a feature branch (git checkout -b feature/amazing-feature)
3. Make your changes
4. Run tests (bun test)
5. Commit your changes (git commit -m 'Add some amazing feature')
6. Push to the branch (git push origin feature/amazing-feature)
7. Open a Pull Request

See CONTRIBUTING.md for detailed guidelines.

License

$3

Better Tables provides hooks for customizing filter behavior when the default implementation doesn't work for your use case:

`typescript
import { DrizzleAdapter } from '@better-tables/adapters-drizzle';
import { sql } from 'drizzle-orm';

const adapter = new DrizzleAdapter({
db,
schema,
driver: 'postgres',
hooks: {
// Modify filter before processing
beforeBuildFilterCondition: (filter, primaryTable) => {
// Custom logic to modify filter
if (filter.columnId === 'customField') {
return {
...filter,
columnId: 'actualColumn',
};
}
return filter;
},

// Modify SQL condition after building
afterBuildFilterCondition: (condition, filter) => {
// Custom logic to modify condition
if (filter.columnId === 'specialCase') {
return sql${condition} AND additional_condition = true;
}
return condition;
},

// Custom implementation for large arrays
buildLargeArrayCondition: (column, values, operator) => {
// Return custom SQL condition, or null to use default behavior
if (values.length > 10000) {
// Use temporary table for very large arrays
return sql${column} IN (SELECT id FROM temp_filter_table);
}
return null; // Use default batching behavior
},
},
});
`

$3

Configure how large arrays are batched to optimize performance:

`typescript
const adapter = new DrizzleAdapter({
db,
schema,
driver: 'postgres',
options: {
batching: {
batchSize: 50, // Batch size for large arrays (default: 50)
maxBatchesPerGroup: 200, // Max batches per group before nested grouping (default: 200)
enableNestedGrouping: true, // Enable nested OR/AND grouping (default: true)
},
},
});
`

Batching Behavior:
- Arrays with >50 values are automatically batched into chunks
- When there are >200 batches, nested grouping is used to prevent parameter binding issues
- This ensures queries work correctly even with 50,000+ values

MIT License - see LICENSE for details.

Related Packages

- @better-tables/core - Core functionality and column builders
- @better-tables/ui - React components built on top of core
- Demo App - Complete working example

Support

- GitHub Issues - Report bugs or request features
- GitHub Discussions - Ask questions and share ideas
- Documentation - Comprehensive guides in the docs/` directory

---

Built with ❤️ by the Better Tables team. This package is part of the Better Tables project.