API Reference

Core Types

• SqlQuery<T> - Represents a SQL query that returns a result of type T
• SqlExpression<T> - Represents a SQL expression of type T
• SqlAction<T, R> - Represents a SQL action (INSERT, UPDATE, DELETE) that operates on type T and returns R
• SqlCompiledQuery<T> - A compiled query ready to be executed
• SqlCompiledAction<T, R> - A compiled action ready to be executed

Main Functions

• sql { ... } - Create a SQL query or action
• sql.select { ... } - Create a SQL select query with joins and grouping
• sql.expression { ... } - Create a SQL expression
• Table<T>() - Create a table reference
• param(value) - Pass a runtime parameter to a query
• params(values) - Pass multiple runtime parameters (for IN clauses)

Query Operations

Transformation

• .map { ... } - Project columns (SELECT)
• .flatMap { ... } - Monadic composition for complex queries (see Basic Operations)

Filtering

• .filter { ... } - Filter rows (WHERE)
• .where { ... } - Filter rows (WHERE), alternative syntax

Aggregate Functions

Aggregate functions are used inside sql.select blocks, typically with groupBy.

Function	Input Type	Return Type	SQL
count()	-	Int	COUNT(*)
count(field)	T	Int	COUNT(field)
countDistinct(field)	T	Int	COUNT(DISTINCT field)
sum(field)	Int	Long	SUM(field)
sum(field)	Long	Long	SUM(field)
sum(field)	Double	Double	SUM(field)
avg(field)	Numeric	Double	AVG(field)
min(field)	T	T	MIN(field)
max(field)	T	T	MAX(field)

Important: When defining result data classes for aggregations, match these types exactly:

// CORRECT
@Serializable
data class Stats(
    val totalCount: Int,           // count() returns Int
    val distinctUsers: Int,        // countDistinct() returns Int
    val totalRevenue: Long,        // sum(Long) returns Long
    val avgOrderValue: Double      // avg() returns Double
)

// WRONG - will cause compile errors
@Serializable
data class Stats(
    val totalCount: Long,          // ❌ count() returns Int, not Long
    val distinctUsers: Long,       // ❌ countDistinct() returns Int
    ...
)

Sorting

• .sortedBy { ... } - Sort results ascending (ORDER BY ASC)
• .sortedByDescending { ... } - Sort results descending (ORDER BY DESC)

Pagination

• .limit(n) - Limit number of results (LIMIT)
• .take(n) - Alias for .limit(n)
• .offset(n) - Skip rows (OFFSET)
• .drop(n) - Alias for .offset(n)
• .distinct() - Remove duplicate rows (SELECT DISTINCT)

Set Operations

• .union(query) - Combine queries, remove duplicates (UNION)
• .unionAll(query) - Combine queries, keep duplicates (UNION ALL)

Subquery Helpers

• .nested() - Force subquery boundary, prevent automatic flattening
• .value() - Convert single-row SqlQuery<T> to scalar T for use in expressions
• .isEmpty() - Check if subquery returns no rows (NOT EXISTS)
• .isNotEmpty() - Check if subquery returns rows (EXISTS)

Conditional Expressions

• if (cond) a else b - Translates to CASE WHEN ... THEN ... ELSE ... END
• when { ... } - Translates to multi-branch CASE expression
• a ?: b - Translates to COALESCE(a, b)

See Basic Operations for examples.

Build and Execute

• .buildFor.Postgres() - Build query for PostgreSQL
• .buildFor.Sqlite() - Build query for SQLite
• .buildFor.SqlServer() - Build query for SQL Server
• .buildFor.MySql() - Build query for MySQL
• .runOn(controller) - Execute the query on a database controller