Skip to main content

​
CaseBuilder

CaseBuilder provides a type-safe way to build SQL CASE expressions and statements. It’s created by calling eb.case() or eb.case(value) on an ExpressionBuilder.

​
Type Parameters

​
DB
object
required
The database schema type
​
TB
keyof DB
required
The table references available in the current query context
​
W
any
default:"unknown"
The type being compared in when clauses (when using case(value) form)
​
O
any
default:"never"
The accumulated output type from all then clauses

​
Methods

​
when

Adds a when clause to the case statement. 
when<
  RE extends ReferenceExpression<DB, TB>,
  VE extends OperandValueExpressionOrList<DB, TB, RE>,
>(
  lhs: RE,
  op: ComparisonOperatorExpression,
  rhs: VE,
): CaseThenBuilder<DB, TB, W, O>

when(expression: Expression<W>): CaseThenBuilder<DB, TB, W, O>

when(value: W): CaseThenBuilder<DB, TB, W, O>
​
lhs
ReferenceExpression
Left-hand side column reference (only for case() without value)
​
op
ComparisonOperatorExpression
Comparison operator (only for case() without value)
​
rhs
OperandValueExpressionOrList
Right-hand side value or expression (only for case() without value)
​
expression
Expression
Expression to evaluate
​
value
any
Value to compare against (only for case(value) form)
A when call must be followed by a then call. Example with case(): 
eb.case()
  .when('gender', '=', 'male')
  .then('Mr.')
  .when('gender', '=', 'female')
  .then('Ms.')
  .end()
Example with case(value): 
eb.case('marital_status')
  .when('single')
  .then('Ms.')
  .when('married')
  .then('Mrs.')
  .else('Mrs.')
  .end()

​
CaseThenBuilder

Returned by when() calls. Provides the then() method to specify the result for the when condition.

​
then

Adds a then clause to the case statement. 
then<E extends Expression<unknown>>(
  expression: E,
): CaseWhenBuilder<DB, TB, W, O | ExtractTypeFromValueExpression<E>>

then<V>(value: V): CaseWhenBuilder<DB, TB, W, O | V>
​
expression
Expression
Expression to return when the when condition matches
​
value
any
Value to return when the when condition matches
A then call can be followed by when, else, end, or endCase calls.

​
CaseWhenBuilder

Returned by then() calls. Allows chaining additional when clauses or finishing with else/end.

​
when

Adds another when clause to the case statement. 
when<
  RE extends ReferenceExpression<DB, TB>,
  VE extends OperandValueExpressionOrList<DB, TB, RE>,
>(
  lhs: RE,
  op: ComparisonOperatorExpression,
  rhs: VE,
): CaseThenBuilder<DB, TB, W, O>

when(expression: Expression<W>): CaseThenBuilder<DB, TB, W, O>

when(value: W): CaseThenBuilder<DB, TB, W, O>

​
else

Adds an else clause to the case statement. 
else<E extends Expression<unknown>>(
  expression: E,
): CaseEndBuilder<DB, TB, O | ExtractTypeFromValueExpression<E>>

else<V>(value: V): CaseEndBuilder<DB, TB, O | V>
​
expression
Expression
Expression to return when no when conditions match
​
value
any
Value to return when no when conditions match
An else call must be followed by an end or endCase call. Example: 
eb.case()
  .when('gender', '=', 'male')
  .then('Mr.')
  .when('gender', '=', 'female')
  .then('Ms.')
  .else('Other')
  .end()

​
end

Adds an end keyword to the case operator. 
end(): ExpressionWrapper<DB, TB, O | null>
case operators can only be used as part of a query. For a case statement used as part of a stored program, use endCase instead. If no else clause is provided, the result type will be nullable (O | null). Example: 
const result = await db
  .selectFrom('person')
  .select((eb) => [
    eb.case()
      .when('age', '<', 18)
      .then('minor')
      .when('age', '>=', 18)
      .then('adult')
      .end()
      .as('age_group')
  ])
  .execute()

​
endCase

Adds end case keywords to the case statement. 
endCase(): ExpressionWrapper<DB, TB, O | null>
case statements can only be used for flow control in stored programs. For a case operator used as part of a query, use end instead.

​
CaseEndBuilder

Returned by else() calls. Only provides end and endCase methods.

​
end

Adds an end keyword to the case operator. 
end(): ExpressionWrapper<DB, TB, O>
Example: 
eb.case()
  .when('gender', '=', 'male')
  .then('Mr.')
  .else('Ms.')
  .end()

​
endCase

Adds end case keywords to the case statement. 
endCase(): ExpressionWrapper<DB, TB, O>

​
Complete Example

Kitchen sink example with 2 flavors of case operator: 
const { title, name } = await db
  .selectFrom('person')
  .where('id', '=', 123)
  .select((eb) => [
    eb.fn.coalesce('last_name', 'first_name').as('name'),
    eb
      .case()
      .when('gender', '=', 'male')
      .then('Mr.')
      .when('gender', '=', 'female')
      .then(
        eb
          .case('marital_status')
          .when('single')
          .then('Ms.')
          .else('Mrs.')
          .end()
      )
      .end()
      .as('title'),
  ])
  .executeTakeFirstOrThrow()
The generated SQL (PostgreSQL): 
select
  coalesce("last_name", "first_name") as "name",
  case
    when "gender" = $1 then $2
    when "gender" = $3 then
      case "marital_status"
        when $4 then $5
        else $6
      end
  end as "title"
from "person"
where "id" = $7

​
Type Safety

The CaseBuilder maintains type safety throughout the chain:
  • When using case(value), the when clauses must match the value type
  • The output type O accumulates all possible return types from then clauses
  • Without an else clause, the final type is O | null
  • With an else clause, the final type includes the else value type
// Type: ExpressionWrapper<DB, TB, 'minor' | 'adult' | null>
const ageGroup = eb.case()
  .when('age', '<', 18)
  .then('minor')
  .when('age', '>=', 18)
  .then('adult')
  .end()

// Type: ExpressionWrapper<DB, TB, 'minor' | 'adult' | 'unknown'>
const ageGroupWithElse = eb.case()
  .when('age', '<', 18)
  .then('minor')
  .when('age', '>=', 18)
  .then('adult')
  .else('unknown')
  .end()

Build docs developers (and LLMs) love

Get started for freeTalk to us