IHP.QueryBuilder

Overview

The IHP.QueryBuilder module provides a type-safe, composable way to build SQL queries. It’s the foundation for querying your database in IHP applications. Key features:

Type-safe query construction
Composable query builders using the |> operator
Support for filtering, ordering, pagination, and joins
Compile-time guarantees for query correctness

For complex SQL queries, use sqlQuery from IHP.ModelSupport.

Creating Queries

query

QueryBuilder table

Create a new query builder.

-- Without type annotation (type inferred from usage)
let q = query

-- With explicit type
let projectQuery = query @Project

Fetching Results

fetch

QueryBuilder table -> IO [table]

Fetch multiple rows.

projects <- query @Project |> fetch
-- SQL: SELECT * FROM projects

fetchOne

QueryBuilder table -> IO table

Fetch a single row. Throws an error if no record is found.

project <- query @Project 
    |> filterWhere (#id, projectId)
    |> fetchOne
-- SQL: SELECT * FROM projects WHERE id = ? LIMIT 1

fetchOneOrNothing

QueryBuilder table -> IO (Maybe table)

Fetch a single row or Nothing if not found.

maybeProject <- query @Project
    |> filterWhere (#slug, "my-project")
    |> fetchOneOrNothing
-- SQL: SELECT * FROM projects WHERE slug = ? LIMIT 1

Filtering

Basic Filters

filterWhere

(KnownSymbol field, ToField value) => (Proxy field, value) -> QueryBuilder table -> QueryBuilder table

Add a WHERE condition.

projects <- query @Project
    |> filterWhere (#userId, userId)
    |> filterWhere (#deleted, False)
    |> fetch
-- SQL: SELECT * FROM projects WHERE user_id = ? AND deleted = false

filterWhereNot

(KnownSymbol field, ToField value) => (Proxy field, value) -> QueryBuilder table -> QueryBuilder table

Add a negated WHERE condition.

otherProjects <- query @Project
    |> filterWhereNot (#userId, currentUserId)
    |> fetch
-- SQL: SELECT * FROM projects WHERE user_id != ?

filterWhereCaseInsensitive

(KnownSymbol field, ToField value) => (Proxy field, value) -> QueryBuilder table -> QueryBuilder table

Case-insensitive WHERE condition.

user <- query @User
    |> filterWhereCaseInsensitive (#email, email)
    |> fetchOneOrNothing
-- SQL: SELECT * FROM users WHERE LOWER(email) = LOWER(?)

Range Filters

filterWhereIn

(KnownSymbol field, ToField value) => (Proxy field, [value]) -> QueryBuilder table -> QueryBuilder table

Filter by a list of values (IN clause).

projects <- query @Project
    |> filterWhereIn (#status, ["active", "pending"])
    |> fetch
-- SQL: SELECT * FROM projects WHERE status IN (?, ?)

filterWhereNotIn

(KnownSymbol field, ToField value) => (Proxy field, [value]) -> QueryBuilder table -> QueryBuilder table

Filter by excluded values (NOT IN clause).

projects <- query @Project
    |> filterWhereNotIn (#status, ["deleted", "archived"])
    |> fetch
-- SQL: SELECT * FROM projects WHERE status NOT IN (?, ?)

filterWhereIdIn

(Proxy field, [Id' table]) -> QueryBuilder table -> QueryBuilder table

Filter by a list of IDs.

users <- query @User
    |> filterWhereIdIn (#id, userIds)
    |> fetch
-- SQL: SELECT * FROM users WHERE id IN (?, ?, ?)

Pattern Matching

filterWhereLike

(KnownSymbol field) => (Proxy field, Text) -> QueryBuilder table -> QueryBuilder table

SQL LIKE pattern matching (case-sensitive).

projects <- query @Project
    |> filterWhereLike (#title, "%website%")
    |> fetch
-- SQL: SELECT * FROM projects WHERE title LIKE ?

filterWhereILike

(KnownSymbol field) => (Proxy field, Text) -> QueryBuilder table -> QueryBuilder table

SQL ILIKE pattern matching (case-insensitive).

projects <- query @Project
    |> filterWhereILike (#title, "%WEBSITE%")
    |> fetch
-- SQL: SELECT * FROM projects WHERE title ILIKE ?

Comparison Filters

filterWhereGreaterThan

(KnownSymbol field, ToField value) => (Proxy field, value) -> QueryBuilder table -> QueryBuilder table

Filter by greater than.

expensiveProjects <- query @Project
    |> filterWhereGreaterThan (#budget, 10000)
    |> fetch
-- SQL: SELECT * FROM projects WHERE budget > ?

filterWhereGreaterThanOrEqualTo

(KnownSymbol field, ToField value) => (Proxy field, value) -> QueryBuilder table -> QueryBuilder table

Filter by greater than or equal to.

projects <- query @Project
    |> filterWhereGreaterThanOrEqualTo (#budget, 5000)
    |> fetch
-- SQL: SELECT * FROM projects WHERE budget >= ?

filterWhereLessThan

(KnownSymbol field, ToField value) => (Proxy field, value) -> QueryBuilder table -> QueryBuilder table

Filter by less than.

smallProjects <- query @Project
    |> filterWhereLessThan (#teamSize, 5)
    |> fetch
-- SQL: SELECT * FROM projects WHERE team_size < ?

filterWhereLessThanOrEqualTo

(KnownSymbol field, ToField value) => (Proxy field, value) -> QueryBuilder table -> QueryBuilder table

Filter by less than or equal to.

projects <- query @Project
    |> filterWhereLessThanOrEqualTo (#teamSize, 10)
    |> fetch
-- SQL: SELECT * FROM projects WHERE team_size <= ?

Time-based Filters

filterWherePast

KnownSymbol field => Proxy field -> QueryBuilder table -> QueryBuilder table

Filter for dates in the past.

pastEvents <- query @Event
    |> filterWherePast #eventDate
    |> fetch
-- SQL: SELECT * FROM events WHERE event_date < NOW()

filterWhereFuture

KnownSymbol field => Proxy field -> QueryBuilder table -> QueryBuilder table

Filter for dates in the future.

upcomingEvents <- query @Event
    |> filterWhereFuture #eventDate
    |> fetch
-- SQL: SELECT * FROM events WHERE event_date > NOW()

Custom SQL Filters

filterWhereSql

(ByteString, [Action]) -> QueryBuilder table -> QueryBuilder table

Add a custom SQL WHERE condition.

projects <- query @Project
    |> filterWhereSql ("budget * 1.1 > ?", [toField maxBudget])
    |> fetch
-- SQL: SELECT * FROM projects WHERE budget * 1.1 > ?

Ordering

orderBy

KnownSymbol field => Proxy field -> QueryBuilder table -> QueryBuilder table

Order by a field (ascending by default).

projects <- query @Project
    |> orderBy #createdAt
    |> fetch
-- SQL: SELECT * FROM projects ORDER BY created_at

orderByAsc

KnownSymbol field => Proxy field -> QueryBuilder table -> QueryBuilder table

Order by a field in ascending order.

projects <- query @Project
    |> orderByAsc #title
    |> fetch
-- SQL: SELECT * FROM projects ORDER BY title ASC

orderByDesc

KnownSymbol field => Proxy field -> QueryBuilder table -> QueryBuilder table

Order by a field in descending order.

recentProjects <- query @Project
    |> orderByDesc #createdAt
    |> fetch
-- SQL: SELECT * FROM projects ORDER BY created_at DESC

Pagination

limit

Int -> QueryBuilder table -> QueryBuilder table

Limit the number of results.

topProjects <- query @Project
    |> orderByDesc #stars
    |> limit 10
    |> fetch
-- SQL: SELECT * FROM projects ORDER BY stars DESC LIMIT 10

offset

Int -> QueryBuilder table -> QueryBuilder table

Skip a number of results.

page2Projects <- query @Project
    |> orderBy #title
    |> limit 20
    |> offset 20
    |> fetch
-- SQL: SELECT * FROM projects ORDER BY title LIMIT 20 OFFSET 20

Deduplication

distinct

QueryBuilder table -> QueryBuilder table

Remove duplicate rows.

uniqueProjects <- query @Project
    |> distinct
    |> fetch
-- SQL: SELECT DISTINCT * FROM projects

distinctOn

KnownSymbol field => Proxy field -> QueryBuilder table -> QueryBuilder table

Remove duplicates based on a specific field.

onePerUser <- query @Project
    |> distinctOn #userId
    |> orderByDesc #createdAt
    |> fetch
-- SQL: SELECT DISTINCT ON (user_id) * FROM projects ORDER BY created_at DESC

Joins

innerJoin

QueryBuilder left -> Proxy rightTable -> Proxy leftField -> Proxy rightField -> QueryBuilder (left, right)

Perform an inner join.

projectsWithUsers <- query @Project
    |> innerJoin @User #userId #id
    |> fetch
-- SQL: SELECT projects.*, users.* FROM projects 
--      INNER JOIN users ON projects.user_id = users.id

innerJoinThirdTable

QueryBuilder (a, b) -> Proxy thirdTable -> Proxy field1 -> Proxy field2 -> QueryBuilder (a, b, c)

Join a third table to an existing join.

fullData <- query @Project
    |> innerJoin @User #userId #id
    |> innerJoinThirdTable @Team #teamId #id
    |> fetch
-- Returns: [(Project, User, Team)]

Filtering on Joined Tables

filterWhereJoinedTable

Proxy table -> (Proxy field, value) -> QueryBuilder joined -> QueryBuilder joined

Filter on a joined table.

activeUserProjects <- query @Project
    |> innerJoin @User #userId #id
    |> filterWhereJoinedTable @User (#active, True)
    |> fetch

orderByJoinedTable

Proxy table -> Proxy field -> QueryBuilder joined -> QueryBuilder joined

Order by a field in a joined table.

projectsByUserName <- query @Project
    |> innerJoin @User #userId #id
    |> orderByJoinedTable @User #name
    |> fetch

Unions

queryUnion

QueryBuilder table -> QueryBuilder table -> QueryBuilder table

Combine two queries with UNION.

allProjects <- queryUnion
    (query @Project |> filterWhere (#status, "active"))
    (query @Project |> filterWhere (#status, "pending"))
    |> fetch
-- SQL: (SELECT * FROM projects WHERE status = 'active')
--      UNION
--      (SELECT * FROM projects WHERE status = 'pending')

queryOr

[QueryBuilder table] -> QueryBuilder table

Combine multiple queries with OR logic.

projects <- queryOr
    [ query @Project |> filterWhere (#priority, "high")
    , query @Project |> filterWhere (#urgent, True)
    ]
    |> fetch

SQL Compilation

buildQuery

QueryBuilder table -> (ByteString, [Action])

Compile a query to SQL and parameters.

let (sql, params) = buildQuery $ query @Project
    |> filterWhere (#userId, userId)
    |> orderByDesc #createdAt
-- sql: "SELECT * FROM projects WHERE user_id = ? ORDER BY created_at DESC"

toSQL

QueryBuilder table -> Statement

Compile to a Hasql statement.

import qualified IHP.QueryBuilder as QueryBuilder

let statement = QueryBuilder.toSQL $ query @Project
    |> filterWhere (#active, True)

Example: Complex Query

action ProjectsAction = do
    let page = paramOrDefault @Int 0 "page"
    let searchQuery = paramOrNothing @Text "q"
    let status = paramOrDefault @Text "all" "status"
    
    projects <- query @Project
        -- Filtering
        |> filterWhere (#userId, currentUserId)
        |> filterWhereNot (#deleted, True)
        |> case status of
            "active" -> filterWhere (#status, "active")
            "archived" -> filterWhere (#status, "archived")
            _ -> id
        |> case searchQuery of
            Just q -> filterWhereILike (#title, "%" <> q <> "%")
            Nothing -> id
        -- Ordering
        |> orderByDesc #createdAt
        -- Pagination
        |> limit 20
        |> offset (page * 20)
        |> fetch
    
    render IndexView { projects, page }

Core Modules

Extensions

IHP.QueryBuilder

Overview

Creating Queries

Fetching Results

Filtering

Basic Filters

Range Filters

Pattern Matching

Comparison Filters

Time-based Filters

Custom SQL Filters

Ordering

Deduplication

Joins

Filtering on Joined Tables

Unions

SQL Compilation

Example: Complex Query

See Also

Build docs developers (and LLMs) love

Core Modules

Extensions

​Overview

​Creating Queries

​Fetching Results

​Filtering

​Basic Filters

​Range Filters

​Pattern Matching

​Comparison Filters

​Time-based Filters

​Custom SQL Filters

​Ordering

​Pagination

​Deduplication

​Joins

​Filtering on Joined Tables

​Unions

​SQL Compilation

​Example: Complex Query

​See Also

Build docs developers (and LLMs) love

Overview

Creating Queries

Fetching Results

Filtering

Basic Filters

Range Filters

Pattern Matching

Comparison Filters

Time-based Filters

Custom SQL Filters

Ordering

Pagination

Deduplication

Joins

Filtering on Joined Tables

Unions

SQL Compilation

Example: Complex Query

See Also