Drizzle ORM - CharleOS

CharleOS uses Drizzle ORM for type-safe database queries. This page covers query syntax, common patterns, and best practices.

Why Drizzle?

Type Safety

Full TypeScript inference - compile-time error checking

Zero Overhead

No runtime proxy, maps directly to SQL

SQL-Like API

Familiar syntax for SQL developers

Excellent DX

Auto-complete, inline docs, visual studio

Basic Queries

Select

import { db } from "@/lib/db";
import { task } from "@/lib/db/schema";

// Select all
const allTasks = await db.select().from(task);

// Select specific columns
const taskTitles = await db
  .select({
    id: task.id,
    title: task.title,
  })
  .from(task);

Insert

// Insert single record
const [newTask] = await db
  .insert(task)
  .values({
    displayId: 123,
    title: "New task",
    clientId: "client-123",
    pmId: "user-456",
    priority: 3,
  })
  .returning();

// Insert multiple records
const newTasks = await db
  .insert(task)
  .values([
    { displayId: 124, title: "Task 1", ... },
    { displayId: 125, title: "Task 2", ... },
  ])
  .returning();

Update

import { eq } from "drizzle-orm";

// Update single record
await db
  .update(task)
  .set({
    status: "complete",
    updatedAt: new Date(),
  })
  .where(eq(task.id, taskId));

// Update multiple records
await db
  .update(task)
  .set({ priority: 1 })
  .where(eq(task.clientId, clientId));

Delete

// Delete single record
await db
  .delete(task)
  .where(eq(task.id, taskId));

// Delete with condition
await db
  .delete(task)
  .where(
    and(
      eq(task.status, "cancelled"),
      lt(task.createdAt, oneYearAgo)
    )
  );

Filtering

Basic Conditions

import { eq, ne, gt, gte, lt, lte } from "drizzle-orm";

// Equal
const task = await db
  .select()
  .from(task)
  .where(eq(task.id, "task-123"));

// Not equal
const activeTasks = await db
  .select()
  .from(task)
  .where(ne(task.status, "cancelled"));

// Greater than
const recentTasks = await db
  .select()
  .from(task)
  .where(gt(task.createdAt, lastWeek));

Combining Conditions

import { and, or } from "drizzle-orm";

// AND - all conditions must be true
const filteredTasks = await db
  .select()
  .from(task)
  .where(
    and(
      eq(task.clientId, clientId),
      eq(task.status, "in_progress"),
      gte(task.priority, 3)
    )
  );

// OR - any condition can be true
const tasks = await db
  .select()
  .from(task)
  .where(
    or(
      eq(task.status, "complete"),
      eq(task.status, "cancelled")
    )
  );

Pattern Matching

import { like, ilike } from "drizzle-orm";

// Case-sensitive LIKE
const tasks = await db
  .select()
  .from(task)
  .where(like(task.title, "%website%"));

// Case-insensitive ILIKE (PostgreSQL)
const tasks = await db
  .select()
  .from(task)
  .where(ilike(task.title, "%website%"));

IN / NOT IN

import { inArray, notInArray } from "drizzle-orm";

// IN - match any value
const tasks = await db
  .select()
  .from(task)
  .where(inArray(task.status, ["in_progress", "awaiting_review"]));

// NOT IN - exclude values
const tasks = await db
  .select()
  .from(task)
  .where(notInArray(task.status, ["cancelled", "complete"]));

NULL Checks

import { isNull, isNotNull } from "drizzle-orm";

// Is NULL
const unassignedTasks = await db
  .select()
  .from(task)
  .where(isNull(task.pmId));

// Is NOT NULL
const assignedTasks = await db
  .select()
  .from(task)
  .where(isNotNull(task.pmId));

Joins

Left Join

import { client } from "@/lib/db/schema";

// Task with client info
const tasksWithClients = await db
  .select({
    taskId: task.id,
    taskTitle: task.title,
    clientName: client.name,
    clientSlug: client.slug,
  })
  .from(task)
  .leftJoin(client, eq(task.clientId, client.id));

// Returns: { taskId, taskTitle, clientName, clientSlug | null }

Inner Join

// Only tasks that have a client
const tasksWithClients = await db
  .select()
  .from(task)
  .innerJoin(client, eq(task.clientId, client.id));

Multiple Joins

import { user, subtask } from "@/lib/db/schema";

// Task with client, PM, and subtasks
const taskDetails = await db
  .select({
    task,
    clientName: client.name,
    pmName: user.name,
    subtaskCount: count(subtask.id),
  })
  .from(task)
  .leftJoin(client, eq(task.clientId, client.id))
  .leftJoin(user, eq(task.pmId, user.id))
  .leftJoin(subtask, eq(task.id, subtask.taskId))
  .groupBy(task.id, client.name, user.name);

Aggregation

Count

import { count } from "drizzle-orm";

// Count all tasks
const [{ totalTasks }] = await db
  .select({
    totalTasks: count(task.id),
  })
  .from(task);

// Count with filter
const [{ activeTasks }] = await db
  .select({
    activeTasks: count(task.id),
  })
  .from(task)
  .where(eq(task.status, "in_progress"));

Sum, Average, Min, Max

import { sum, avg, min, max } from "drizzle-orm";

// Aggregate time entries
const [stats] = await db
  .select({
    totalMinutes: sum(timeEntry.duration),
    avgMinutes: avg(timeEntry.duration),
    minMinutes: min(timeEntry.duration),
    maxMinutes: max(timeEntry.duration),
  })
  .from(timeEntry)
  .where(eq(timeEntry.clientId, clientId));

Group By

// Tasks per client
const clientStats = await db
  .select({
    clientId: task.clientId,
    clientName: client.name,
    taskCount: count(task.id),
    totalMinutes: sum(task.estimatedMinutes),
  })
  .from(task)
  .leftJoin(client, eq(task.clientId, client.id))
  .groupBy(task.clientId, client.name);

Sorting

Order By

import { asc, desc } from "drizzle-orm";

// Sort ascending
const tasks = await db
  .select()
  .from(task)
  .orderBy(asc(task.priority));

// Sort descending
const tasks = await db
  .select()
  .from(task)
  .orderBy(desc(task.createdAt));

// Multiple sort columns
const tasks = await db
  .select()
  .from(task)
  .orderBy(
    asc(task.priority),
    desc(task.createdAt)
  );

Pagination

Limit & Offset

const PAGE_SIZE = 20;
const page = 1; // 0-indexed

const tasks = await db
  .select()
  .from(task)
  .orderBy(desc(task.createdAt))
  .limit(PAGE_SIZE)
  .offset(page * PAGE_SIZE);

// Get total count for pagination
const [{ total }] = await db
  .select({ total: count(task.id) })
  .from(task)
  .where(eq(task.status, "in_progress"));

Transactions

Basic Transaction

await db.transaction(async (tx) => {
  // Create task
  const [newTask] = await tx
    .insert(task)
    .values({ ... })
    .returning();

  // Create subtasks
  await tx
    .insert(subtask)
    .values([
      { taskId: newTask.id, ... },
      { taskId: newTask.id, ... },
    ]);

  // If any operation fails, entire transaction rolls back
});

Rollback on Error

try {
  await db.transaction(async (tx) => {
    await tx.insert(task).values({ ... });
    
    // If this throws, everything rolls back
    if (someCondition) {
      throw new Error("Validation failed");
    }
    
    await tx.insert(subtask).values({ ... });
  });
} catch (error) {
  console.error("Transaction failed:", error);
}

Advanced Patterns

Conditional Queries

Build queries dynamically based on filters:

import { SQL, and } from "drizzle-orm";

function buildTaskQuery(filters: {
  clientId?: string;
  status?: string;
  searchTerm?: string;
}) {
  const conditions: SQL[] = [];

  if (filters.clientId) {
    conditions.push(eq(task.clientId, filters.clientId));
  }

  if (filters.status) {
    conditions.push(eq(task.status, filters.status));
  }

  if (filters.searchTerm) {
    conditions.push(ilike(task.title, `%${filters.searchTerm}%`));
  }

  return db
    .select()
    .from(task)
    .where(conditions.length > 0 ? and(...conditions) : undefined);
}

// Usage
const tasks = await buildTaskQuery({
  clientId: "client-123",
  status: "in_progress",
});

Subqueries

import { sql } from "drizzle-orm";

// Count subtasks per task
const tasksWithSubtaskCount = await db
  .select({
    task,
    subtaskCount: sql<number>`(
      SELECT COUNT(*) 
      FROM ${subtask} 
      WHERE ${subtask.taskId} = ${task.id}
    )`,
  })
  .from(task);

Upsert (Insert or Update)

// PostgreSQL ON CONFLICT
await db
  .insert(userPreference)
  .values({
    userId: "user-123",
    key: "theme",
    value: "dark",
  })
  .onConflictDoUpdate({
    target: [userPreference.userId, userPreference.key],
    set: { value: "dark", updatedAt: new Date() },
  });

Batch Operations

// Insert in batches (more efficient for large datasets)
const batchSize = 100;
const records = [...]; // 1000s of records

for (let i = 0; i < records.length; i += batchSize) {
  const batch = records.slice(i, i + batchSize);
  await db.insert(timeEntry).values(batch);
}

Common CharleOS Patterns

Get Task with Full Details

export async function getTaskWithDetails(taskId: string) {
  const [result] = await db
    .select({
      task,
      client: client,
      pm: user,
      subtasks: sql`
        json_agg(json_build_object(
          'id', ${subtask.id},
          'type', ${subtask.type},
          'status', ${subtask.status}
        ))
      `,
    })
    .from(task)
    .leftJoin(client, eq(task.clientId, client.id))
    .leftJoin(user, eq(task.pmId, user.id))
    .leftJoin(subtask, eq(task.id, subtask.taskId))
    .where(eq(task.id, taskId))
    .groupBy(task.id, client.id, user.id);

  return result;
}

Calculate Client Utilization

export async function getClientUtilization(
  clientId: string,
  startDate: Date,
  endDate: Date
) {
  const [result] = await db
    .select({
      allocatedMinutes: client.monthlyHours * 60, // Convert to minutes
      usedMinutes: sum(timeEntry.duration),
      utilization: sql<number>`
        ROUND(
          (${sum(timeEntry.duration)} * 100.0) / (${client.monthlyHours} * 60)
        )
      `,
    })
    .from(client)
    .leftJoin(timeEntry, eq(client.id, timeEntry.clientId))
    .where(
      and(
        eq(client.id, clientId),
        gte(timeEntry.date, startDate),
        lte(timeEntry.date, endDate)
      )
    )
    .groupBy(client.id);

  return result;
}

Performance Tips

Use Indexes for Frequently Queried Columns

Drizzle supports index definitions in schema:

export const task = pgTable("task", {
  // ... columns
}, (table) => ({
  clientIdIdx: index("task_client_id_idx").on(table.clientId),
  statusIdx: index("task_status_idx").on(table.status),
}));

Select Only Needed Columns

Don’t select all columns if you only need a few:

// ✅ Good: Select only what you need
const tasks = await db
  .select({ id: task.id, title: task.title })
  .from(task);

// ❌ Bad: Selecting everything
const tasks = await db.select().from(task);

Use Prepared Statements for Repeated Queries

const getTaskById = db
  .select()
  .from(task)
  .where(eq(task.id, sql.placeholder("id")))
  .prepare("get_task_by_id");

// Reuse prepared statement
const task1 = await getTaskById.execute({ id: "task-123" });
const task2 = await getTaskById.execute({ id: "task-456" });

Limit Large Result Sets

Always use .limit() when querying potentially large datasets:

const recentTasks = await db
  .select()
  .from(task)
  .orderBy(desc(task.createdAt))
  .limit(100); // Prevent loading 10,000+ records

Type Safety

Drizzle provides full type inference:

// Types are automatically inferred
const tasks = await db.select().from(task);
// tasks: Array<{
//   id: string;
//   title: string;
//   clientId: string | null;
//   ... all columns
// }>

// Custom selections
const taskTitles = await db
  .select({
    id: task.id,
    title: task.title,
  })
  .from(task);
// taskTitles: Array<{ id: string; title: string }>

// Joins
const tasksWithClients = await db
  .select({
    taskId: task.id,
    clientName: client.name,
  })
  .from(task)
  .leftJoin(client, eq(task.clientId, client.id));
// tasksWithClients: Array<{ taskId: string; clientName: string | null }>

Error Handling

import { PostgresError } from "postgres";

try {
  await db.insert(task).values({ ... });
} catch (error) {
  if (error instanceof PostgresError) {
    // Handle specific PostgreSQL errors
    if (error.code === "23505") {
      throw new Error("Duplicate record");
    }
    if (error.code === "23503") {
      throw new Error("Foreign key violation");
    }
  }
  throw error;
}

Debugging

Log Queries

// Enable query logging in development
const db = drizzle(pool, {
  logger: process.env.NODE_ENV === "development",
});

Inspect Generated SQL

// Get SQL without executing
const query = db
  .select()
  .from(task)
  .where(eq(task.clientId, clientId))
  .toSQL();

console.log(query.sql);      // SQL string
console.log(query.params);   // Parameter values

Schema

Database table definitions

Migrations

Managing schema changes

Drizzle Docs

Official Drizzle documentation

Getting Started

Database

Authentication

Testing

Deployment

​Why Drizzle?

Type Safety

Zero Overhead

SQL-Like API

Excellent DX

​Basic Queries

​Select

​Insert

​Update

​Delete

​Filtering

​Basic Conditions

​Combining Conditions

​Pattern Matching

​IN / NOT IN

​NULL Checks

​Joins

​Left Join

​Inner Join

​Multiple Joins

​Aggregation

​Count

​Sum, Average, Min, Max

​Group By

​Sorting

​Order By

​Pagination

​Limit & Offset

​Transactions

​Basic Transaction

​Rollback on Error

​Advanced Patterns

​Conditional Queries

​Subqueries

​Upsert (Insert or Update)

​Batch Operations

​Common CharleOS Patterns

​Get Task with Full Details

​Calculate Client Utilization

​Performance Tips

​Type Safety

​Error Handling

​Debugging

​Log Queries

​Inspect Generated SQL

​Related Documentation

Schema

Migrations

Drizzle Docs

Why Drizzle?

Basic Queries

Select

Insert

Update

Delete

Filtering

Basic Conditions

Combining Conditions

Pattern Matching

IN / NOT IN

NULL Checks

Joins

Left Join

Inner Join

Multiple Joins

Aggregation

Count

Sum, Average, Min, Max

Group By

Sorting

Order By

Pagination

Limit & Offset

Transactions

Basic Transaction

Rollback on Error

Advanced Patterns

Conditional Queries

Subqueries

Upsert (Insert or Update)

Batch Operations

Common CharleOS Patterns

Get Task with Full Details

Calculate Client Utilization

Performance Tips

Type Safety

Error Handling

Debugging

Log Queries

Inspect Generated SQL

Related Documentation