Refinement Methods

Overview

Refinement methods control how query results are shaped and formatted. Some operate at the SQL level (.select(), .exclude(), .with()), while others transform results programmatically (.omit(), .raw(), .safe()).

select

Specify which columns to include in the SQL SELECT clause (whitelist).

Syntax

query.select(fields) => ModelQueryResult

Parameters

fields

Record<string, boolean | SelectObject>

required

Object mapping field names to true (include) or nested select objects for relations.

Examples

Basic Select
Nested Select
Combine with Exclude

const users = await userModel
  .findMany()
  .select({ id: true, name: true });
// SELECT id, name FROM user
// Type: { id: number, name: string }[]

const users = await userModel
  .findMany()
  .with({ posts: true })
  .select({ 
    id: true, 
    name: true,
    posts: { 
      id: true, 
      title: true 
    }
  });
// Selects specific fields from user and posts

const users = await userModel
  .findMany()
  .select({ id: true, name: true, email: true })
  .exclude({ email: true });
// Final result: { id: true, name: true }

exclude

Specify which columns to exclude from the SQL SELECT clause (blacklist).

Syntax

query.exclude(fields) => ModelQueryResult

Parameters

fields

Record<string, boolean | ExcludeObject>

required

Object mapping field names to true (exclude) or nested exclude objects for relations.

Examples

Basic Exclude
Nested Exclude

const users = await userModel
  .findMany()
  .exclude({ password: true, secretField: true });
// SELECT * EXCEPT password, secretField FROM user

const users = await userModel
  .findMany()
  .with({ posts: true })
  .exclude({ 
    email: true,
    posts: { 
      internalStatus: true 
    }
  });

with

Load related entities via JOINs (eager loading).

Syntax

query.with(relations) => ModelQueryResult

Parameters

relations

Record<string, boolean | Model | WithObject>

required

Object mapping relation names to:

true - Load all fields from the relation
Model instance - Load with filters via .where()
Nested object - Load nested relations

Examples

const users = await userModel
  .findMany()
  .with({ posts: true });
// Each user includes posts array

const users = await userModel
  .findMany()
  .with({ 
    posts: true, 
    invitee: true,
    profile: true
  });

const users = await userModel
  .findMany()
  .with({
    posts: {
      comments: true
    }
  });
// Loads users → posts → comments

const users = await userModel
  .findMany()
  .with({
    posts: {
      comments: {
        author: true
      }
    }
  });
// Three levels deep

const users = await userModel
  .findMany()
  .with({
    posts: postModel.where({
      published: esc(true)
    })
  });
// Only load published posts

const users = await userModel
  .findMany()
  .with({
    posts: postModel
      .where({ published: esc(true) })
      .include({ comments: true })
  });
// Load published posts WITH their comments

include

Specify nested relations when using a model instance in .with().

Syntax

model.include(relations) => Model

Parameters

relations

Record<string, boolean | Model | WithObject>

required

Same format as .with() - specifies which nested relations to load.

Why It Exists

When you use .where() on a model and pass it to .with(), you need a way to also load nested relations. .include() enables this:

// ❌ Without .include() - can only filter, not load nested relations
const users = await userModel.findMany().with({
  posts: postModel.where({ published: esc(true) })
  // Can't specify comments here
});

// ✅ With .include() - filter AND load nested relations
const users = await userModel.findMany().with({
  posts: postModel
    .where({ published: esc(true) })
    .include({ comments: true })
});

Examples

Basic Include
Multiple Includes
Nested Include

const users = await userModel.findMany().with({
  posts: postModel
    .where({ published: esc(true) })
    .include({ comments: true })
});

const users = await userModel.findMany().with({
  posts: postModel
    .where({ published: esc(true) })
    .include({ 
      comments: true,
      tags: true
    })
});

const users = await userModel.findMany().with({
  posts: postModel
    .where({ published: esc(true) })
    .include({ 
      comments: commentModel.include({ author: true })
    })
});

These methods transform results AFTER the query executes (not in SQL).

omit

Remove fields from the result object programmatically.

Syntax

result.omit(fields) => ModelMutateResult

Parameters

fields

Record<string, boolean>

required

Object mapping field names to true (remove).

When to Use

.exclude() - SQL-level (doesn’t fetch the data)
.omit() - Programmatic (fetches data, then removes it)

Use .omit() when:

You need the data for processing but want to hide it from the final result
Working with mutations (.returnFirst().omit(...))

Examples

Basic Omit
Multiple Fields

const user = await userModel
  .where({ id: esc(1) })
  .update({ secretField: "new-value" })
  .returnFirst()
  .omit({ secretField: true, password: true });
// secretField and password are excluded from result

const users = await userModel
  .insert([...])
  .return()
  .omit({ 
    password: true, 
    secretToken: true,
    internalStatus: true
  });

raw

Skip the model’s format function and return raw database results.

Syntax

query.raw() => ModelQueryResult

When to Use

Performance-critical paths where formatting overhead matters
When you need original database types (e.g., before date parsing)
Debugging to see raw database output

Examples

Skip Formatting
Performance

const postModel = model("post", {
  format(row) {
    return {
      ...row,
      createdAt: new Date(row.createdAt), // Parse dates
    };
  },
});

const post = await postModel.findFirst().raw();
// post.createdAt is string (raw DB value), not Date

// Format function has expensive transformations
const users = await userModel
  .findMany()
  .raw(); // Skip formatting for speed

safe

Wrap the result in { data, error } instead of throwing errors.

Syntax

query.safe() => Promise<SafeResult<T>>

Return Type

type SafeResult<T> = 
  | { data: T; error: undefined }
  | { data: undefined; error: unknown };

When to Use

You prefer error-as-value patterns over try-catch
Building APIs that need consistent error responses
Functional programming style

Examples

Basic Safe
With Mutations
Error Handling

const result = await userModel.findMany().safe();

if (result.error) {
  console.error("Failed to fetch users:", result.error);
  return;
}

console.log(result.data); // User[]

const result = await userModel
  .insert({ ... })
  .returnFirst()
  .safe();

if (result.error) {
  return { success: false, error: result.error };
}

return { success: true, user: result.data };

const result = await userModel
  .where({ id: esc(999) })
  .findFirst()
  .safe();

if (result.error) {
  // Database error occurred
  logError(result.error);
} else if (!result.data) {
  // No user found (not an error)
  console.log("User not found");
} else {
  // Success
  console.log(result.data.name);
}

All refinement methods can be chained together:

const users = await userModel
  .where({ isVerified: esc(true) })
  .findMany()
  .with({ posts: true })
  .select({ id: true, name: true, posts: { title: true } })
  .exclude({ email: true })
  .raw()
  .safe();

Performance Considerations

SQL-Level (Efficient)

// ✅ Only fetches needed columns
const users = await userModel
  .findMany()
  .select({ id: true, name: true });
// SQL: SELECT id, name FROM user

Programmatic (Less Efficient)

// ⚠️ Fetches all columns, then removes them
const users = await userModel
  .findMany()
  .omit({ email: true, password: true });
// SQL: SELECT * FROM user (then omit in JavaScript)

Relation Loading

// ✅ Single query with JOIN
const users = await userModel
  .findMany()
  .with({ posts: true });
// Uses efficient JOIN strategy, not N+1 queries

Type Safety

All refinement methods update the result type:

const user = await userModel
  .findFirst()
  .select({ id: true, name: true });
// Type: { id: number, name: string } | undefined

const users = await userModel
  .findMany()
  .with({ posts: true });
// Type: { ...User, posts: Post[] }[]

const result = await userModel.findMany().safe();
// Type: { data: User[], error: undefined } | { data: undefined, error: unknown }

Notes

SQL vs Programmatic: Use .select() and .exclude() to reduce data transfer. Use .omit() only when you need the data for processing but want to hide it from the final result.

Relation Loading: .with() uses efficient JOIN-based loading, not N+1 queries.

Deep relation nesting can generate large queries. Use .select() to reduce payload size:

const users = await userModel
  .findMany()
  .with({ posts: { comments: { author: true } } })
  .select({ 
    id: true, 
    posts: { 
      id: true, 
      comments: { id: true } 
    } 
  });

Drizzle Model

Controller

Response

Logger

Schema & Validation

​Overview

​SQL-Level Refinement

​select

​Syntax

​Parameters

​Examples

​exclude

​Syntax

​Parameters

​Examples

​with

​Syntax

​Parameters

​Examples

​include

​Syntax

​Parameters

​Why It Exists

​Examples

​Programmatic Refinement

​omit

​Syntax

​Parameters

​When to Use

​Examples

​raw

​Syntax

​When to Use

​Examples

​safe

​Syntax

​Return Type

​When to Use

​Examples

​Chaining Refinements

​Performance Considerations

​SQL-Level (Efficient)

​Programmatic (Less Efficient)

​Relation Loading

​Type Safety

​Notes

Build docs developers (and LLMs) love

Overview

SQL-Level Refinement

select

Syntax

Parameters

Examples

exclude

Syntax

Parameters

Examples

with

Syntax

Parameters

Examples

include

Syntax

Parameters

Why It Exists

Examples

Programmatic Refinement

omit

Syntax

Parameters

When to Use

Examples

raw

Syntax

When to Use

Examples

safe

Syntax

Return Type

When to Use

Examples

Chaining Refinements

Performance Considerations

SQL-Level (Efficient)

Programmatic (Less Efficient)

Relation Loading

Type Safety

Notes