Package Exports

@better-tables/adapters-drizzle

Readme

@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

Installation

# 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

1. Define Your Schema

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,
};

2. Set Up Database Connection

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. Create the Adapter (Recommended: Factory Function)

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

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' },
  },
});

4. Define Columns with Dot Notation

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

const cb = createColumnBuilder<UserWithRelations>();

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(),
];

5. Use with Better Tables

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

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

Advanced Usage

Using the Constructor (Advanced)

For more control, you can use the constructor directly:

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

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

Custom Relationship Mapping

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

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'
    }
  }
});

Performance Optimization

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
    }
  }
});

Complex Filtering

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 
});

Primary Table Specification

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

// 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

Aggregate Columns

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(),
];

Server-Side Rendering (Next.js)

// 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<Record<string, string>> }) {
  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);
  
  let filters: FilterState[] = [];
  if (params.filters) {
    filters = JSON.parse(params.filters);
  }
  
  let sorting: SortingState = [];
  if (params.sorting) {
    sorting = JSON.parse(params.sorting);
  }
  
  // Fetch data
  const result = await adapter.fetchData({
    columns: ['name', 'email', 'profile.bio'],
    pagination: { page, limit },
    filters,
    sorting,
  });
  
  return <Table data={result.data} totalCount={result.total} />;
}

API Reference

Factory Function

`drizzleAdapter<TDB>(db, factoryOptions?)`

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

function drizzleAdapter<TDB>(
  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

DrizzleAdapter Class

The main adapter class that implements the TableAdapter interface.

Constructor

new DrizzleAdapter<TSchema, TDriver>(config: DrizzleAdapterConfig<TSchema, TDriver>)

Configuration:

interface DrizzleAdapterConfig<TSchema, TDriver> {
  db: DrizzleDatabase<TDriver>;     // Drizzle database instance
  schema: TSchema;                   // Schema with tables
  driver: TDriver;                   // 'postgres' | 'mysql' | 'sqlite'
  relations?: Record<string, Relations>; // Drizzle relations for auto-detection
  autoDetectRelationships?: boolean; // Enable auto-detection (default: true)
  relationships?: RelationshipMap;   // Manual relationship mappings
  options?: DrizzleAdapterOptions;    // Adapter options
  meta?: Partial<AdapterMeta>;       // 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<FetchDataResult<TRecord>>
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

RelationshipMap

Maps column IDs to relationship paths:

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

PostgreSQL

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);

MySQL

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);

SQLite

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:

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

Use Indexes - Ensure foreign key columns are indexed
Limit Joins - Use maxJoins option to prevent excessive joins
Enable Caching - Use query result caching for repeated requests
Batch Large Queries - Enable batching for large datasets
Monitor Performance - Enable performance tracking to identify slow queries
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:

Before (Raw Drizzle)

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);

After (Drizzle Adapter)

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.

How to Contribute

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

See CONTRIBUTING.md for detailed guidelines.

License

MIT License - see LICENSE for details.

@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.