Writing Database Queries

info

This guide covers common query patterns using Drizzle ORM. For comprehensive documentation, see the Drizzle ORM Query Guide.

Basic Queries

Queries are centralized in src/server/db/queries.ts:

import { db } from ".";
import { eq, and, like } from "drizzle-orm";
import { users } from "./schema";

// Find by ID
export const getUserById = async (id: string) => {
    return await db.query.users.findFirst({
        where: eq(users.id, id),
    });
};

// Find with conditions
export const findUsers = async (query: string) => {
    return await db.query.users.findMany({
        where: like(users.name, `%${query}%`),
        columns: {
            id: true,
            name: true,
            email: true,
        },
    });
};

Inserting Data

// Single insert
export const createUser = async (data: NewUser) => {
    return await db.insert(users)
        .values(data)
        .returning();
};

// Batch insert
export const createManyUsers = async (data: NewUser[]) => {
    return await db.insert(users)
        .values(data)
        .returning();
};

Updating Records

// Update by ID
export const updateUser = async (id: string, data: Partial<User>) => {
    return await db.update(users)
        .set(data)
        .where(eq(users.id, id))
        .returning();
};

// Conditional update
export const verifyEmail = async (email: string) => {
    return await db.update(users)
        .set({ emailVerified: true })
        .where(eq(users.email, email))
        .returning();
};

Deleting Records

// Delete by ID
export const deleteUser = async (id: string) => {
    return await db.delete(users)
        .where(eq(users.id, id))
        .returning();
};

// Conditional delete
export const deleteInactiveUsers = async () => {
    return await db.delete(users)
        .where(eq(users.emailVerified, false))
        .returning();
};

Relations and Joins

// Get user with related data
export const getUserWithSessions = async (id: string) => {
    return await db.query.users.findFirst({
        where: eq(users.id, id),
        with: {
            sessions: true,
            accounts: true,
        },
    });
};

Complex Queries

// Advanced filtering
export const findActiveUsers = async () => {
    return await db.query.users.findMany({
        where: and(
            eq(users.emailVerified, true),
            eq(users.twoFactorEnabled, false)
        ),
        orderBy: (users, { desc }) => [desc(users.createdAt)],
        limit: 10,
    });
};

Best Practices

1. Centralize Queries

   • Keep queries in queries.ts
   • Group related queries together
   • Export typed results

2. Type Safety

   • Use Drizzle's type inference
   • Define return types for complex queries
   • Validate input data

3. Performance

   • Select only needed columns
   • Use appropriate indexes
   • Limit result sets when possible

Query Organization

For larger applications, consider organizing queries by domain:

// src/server/db/queries/users.ts
export const userQueries = {
    getById: async (id: string) => {...},
    create: async (data: NewUser) => {...},
    update: async (id: string, data: Partial<User>) => {...},
};

warning

Remember to:

• Handle database errors appropriately
• Validate input data before queries
• Use transactions for related operations