Documentation
Documentation
Introduction

Getting Started

Getting StartedInstallationQuick StartProject Structure

Architecture

Architecture OverviewTech StacktRPC MiddlewareDesign Principles

Patterns

Code Patterns & ConventionsFeature ModulesError HandlingType Safety

Database

DatabaseSchema DefinitionDatabase OperationsMigrationsCaching

API

tRPCProceduresRouterstRPC Proxy Setup
APIsOpenAPIREST Endpoints

Auth & Access

AuthenticationConfigurationOAuth ProvidersRolesSession Management
AuthorizationUser RolesPermissions

Routing & i18n

RoutingDeclarative RoutingNavigation
InternationalizationTranslationsLocale Routing

Components & UI

ComponentsButtonsFormsNavigationDialogs
StylesTailwind CSSThemingTypography

Storage

StorageConfigurationUsageBuckets

Configuration

ConfigurationEnvironment VariablesFeature Flags

Templates

Template GuidesCreate New FeatureCreate New PageCreate Database TableCreate tRPC RouterAdd Translations

Development

DevelopmentCommandsAI AgentsBest Practices

Database Operations

CRUD operations with createDrizzleOperations abstraction

The createDrizzleOperations helper provides a standardized abstraction for database CRUD operations, caching, and pagination.

Why Use Operations Abstraction?

Instead of writing repetitive Drizzle queries, the operations abstraction provides:

  • Consistent API - Same methods across all tables
  • Built-in caching - Automatic cache management with tags
  • Type safety - Full TypeScript inference
  • Pagination support - Built-in table pagination
  • Cache invalidation - Automatic revalidation
  • Less boilerplate - Focus on business logic

Available Methods

Located in src/db/drizzle-operations.ts, the abstraction provides:

MethodDescriptionReturns
listDocuments(where?)Get all records, optionally filteredPromise<T[]>
getDocument(id)Get single record by IDPromise<T | undefined>
createDocument(data)Insert new recordPromise<T>
updateDocument(id, data)Update existing recordPromise<T>
removeDocument(id)Delete recordPromise<void>
countDocuments(where?)Count recordsPromise<number>
searchDocuments(term, fields)Full-text search across fieldsPromise<T[]>
listTable(params)Paginated listing with filtersPromise<TableResult<T>>

Creating Operations

Basic Setup

Create operations in your feature's functions.ts:

import { createDrizzleOperations } from "@/db/drizzle-operations";
import { tasks } from "@/db/tables/tasks";
import type { Task } from "@/features/tasks/schema";

const operations = createDrizzleOperations<typeof tasks, Task>({
  table: tasks,
});

Type Parameters

  • First param: Table schema (typeof myTable)
  • Second param: TypeScript type for the data

Usage Examples

List All Documents

export async function list() {
  return operations.listDocuments();
}

With filtering:

import { eq } from "drizzle-orm";

export async function listByUser(userId: string) {
  return operations.listDocuments(eq(tasks.userId, userId));
}

Get by ID

export async function get(id: string) {
  return operations.getDocument(id);
}

Create Document

import { CommonTableData } from "@/db/enums";

type DataCore = Omit<Task, keyof CommonTableData>;

export async function create(data: DataCore) {
  return operations.createDocument(data);
}

The CommonTableData type includes id, createdAt, and updatedAt, which are auto-generated.

Update Document

export async function update(id: string, data: Partial<DataCore>) {
  return operations.updateDocument(id, data);
}

Remove Document

export async function remove(id: string) {
  return operations.removeDocument(id);
}

Count Documents

export async function count() {
  return operations.countDocuments();
}

With filter:

export async function countActive() {
  return operations.countDocuments(eq(tasks.status, "active"));
}

Search Documents

export async function search(searchTerm: string) {
  return operations.searchDocuments(searchTerm, ["title", "description"]);
}

Table Pagination

For paginated tables with sorting and filtering:

import type { TablePagination } from "@/forms/table-list/types";

export async function listTable(params: TablePagination) {
  return operations.listTable(params);
}

The listTable method handles:

  • Pagination - page, limit (pageSize)
  • Sorting - orderBy, direction
  • Filtering - Field filters with operators
  • Total count - For pagination UI

Real-World Example

From src/features/api-keys/functions.ts:

import { createDrizzleOperations } from "@/db/drizzle-operations";
import { CommonTableData } from "@/db/enums";
import { apiKeys } from "@/db/tables";
import { ApiKey } from "@/features/api-keys/schema";
import type { TablePagination } from "@/forms/table-list/types";
import { and, eq, type SQL } from "drizzle-orm";

type DataCore = Omit<ApiKey, keyof CommonTableData>;

const operations = createDrizzleOperations<typeof apiKeys, ApiKey>({
  table: apiKeys,
});

// List all API keys
export async function list() {
  return operations.listDocuments();
}

// Paginated table
export async function listTable(params: TablePagination) {
  return operations.listTable(params);
}

// Get by ID
export async function get(id: string) {
  return operations.getDocument(id);
}

// Create new API key
export async function create(data: DataCore) {
  return operations.createDocument(data);
}

// Update API key
export async function update(id: string, data: Partial<DataCore>) {
  return operations.updateDocument(id, data);
}

// Remove API key
export async function remove(id: string) {
  return operations.removeDocument(id);
}

// Custom: Get by user ID
export async function getByUserId(userId: string) {
  return operations.listDocuments(eq(apiKeys.userId, userId));
}

// Custom: Get with ownership verification
export async function getByUserIdWithVerification(id: string, userId: string) {
  return operations.listDocuments(
    and(eq(apiKeys.id, id), eq(apiKeys.userId, userId))
  );
}

Custom Operations

When createDrizzleOperations isn't sufficient, use raw Drizzle queries:

Complex Queries

import { dbDrizzle } from "@/db";
import { eq, and, gte, sql } from "drizzle-orm";
import { posts, users } from "@/db/tables";

export async function getPostsWithAuthors(startDate: Date) {
  return await dbDrizzle
    .select({
      id: posts.id,
      title: posts.title,
      authorName: users.name,
      authorEmail: users.email,
    })
    .from(posts)
    .leftJoin(users, eq(posts.userId, users.id))
    .where(
      and(
        eq(posts.published, true),
        gte(posts.createdAt, startDate)
      )
    )
    .orderBy(posts.createdAt)
    .limit(10);
}

Aggregations

export async function getPostStats(userId: string) {
  const result = await dbDrizzle
    .select({
      count: sql<number>`count(*)`,
      avgViews: sql<number>`avg(${posts.views})`,
      totalViews: sql<number>`sum(${posts.views})`,
    })
    .from(posts)
    .where(eq(posts.userId, userId));

  return result[0];
}

Transactions

For operations that need atomicity:

import { dbDrizzle } from "@/db";

export async function createPostWithTag(
  postData: NewPost,
  tagData: NewTag
) {
  return await dbDrizzle.transaction(async (tx) => {
    const [post] = await tx.insert(posts).values(postData).returning();
    const [tag] = await tx.insert(tags).values({
      ...tagData,
      postId: post.id,
    }).returning();
    
    return { post, tag };
  });
}

Database Facade Pattern

The database facade (src/db/facade.ts) provides a unified interface:

import * as users from "@/features/users/functions";
import * as organizations from "@/features/organizations/functions";
import * as apiKeys from "@/features/api-keys/functions";

export const db = {
  users,
  organizations,
  apiKeys,
};

Usage:

import { db } from "@/db/facade";

// Instead of importing individual functions
const users = await db.users.list();
const org = await db.organizations.get(orgId);
const keys = await db.apiKeys.getByUserId(userId);

Adding to Facade

When creating a new feature:

// Add import
import * as myFeature from "@/features/my-feature/functions";

// Add to facade
export const db = {
  // ...existing
  myFeature,
};

Caching Behavior

Operations automatically handle caching:

Read Operations

  • listDocuments() - Cached with table tag
  • getDocument() - Cached with table tag
  • countDocuments() - Cached with table tag
  • listTable() - Cached with table tag

Write Operations

Write operations automatically revalidate cache:

  • createDocument() - Revalidates table tag
  • updateDocument() - Revalidates table tag
  • removeDocument() - Revalidates table tag

See Caching for more details.

When to Use Each Method

Choose the right method for your use case.

Use CaseMethod
Get all recordslistDocuments()
Get filtered recordslistDocuments(where)
Get single recordgetDocument(id)
Paginated tablelistTable(params)
Create recordcreateDocument(data)
Update recordupdateDocument(id, data)
Delete recordremoveDocument(id)
Count recordscountDocuments()
Search textsearchDocuments(term, fields)
Complex joinsUse raw Drizzle queries
AggregationsUse raw Drizzle queries
TransactionsUse dbDrizzle.transaction()

Best Practices

Follow these guidelines for efficient database operations.

  1. Use operations abstraction for simple CRUD - Saves time and ensures consistency
  2. Use raw queries for complex operations - Joins, aggregations, subqueries
  3. Omit auto-generated fields - Use Omit<Type, keyof CommonTableData> for create
  4. Use transactions for atomicity - When multiple operations must succeed together
  5. Add custom functions as needed - Extend operations with feature-specific logic
  6. Keep operations in functions.ts - Follow the 5-file pattern
  7. Leverage TypeScript - Let types guide your implementation

Error Handling

Operations throw errors that can be caught:

try {
  const user = await operations.getDocument(id);
  if (!user) {
    throw new Error("User not found");
  }
  return user;
} catch (error) {
  console.error("Failed to get user:", error);
  throw error;
}

Next Steps

  • Caching - Cache strategies and invalidation
  • Schema Definition - Define tables
  • Feature Modules - Organize features with 5-file pattern
  • New Table Template - Create new tables

On this page

Why Use Operations Abstraction?
Available Methods
Creating Operations
Basic Setup
Type Parameters
Usage Examples
List All Documents
Get by ID
Create Document
Update Document
Remove Document
Count Documents
Search Documents
Table Pagination
Real-World Example
Custom Operations
Complex Queries
Aggregations
Transactions
Database Facade Pattern
Adding to Facade
Caching Behavior
Read Operations
Write Operations
When to Use Each Method
Best Practices
Error Handling
Next Steps