QueryBuilder

Introduction
Creating a new query
Running a query
Where Conditions
Order By
Limit
Offset
Or
Union / Merging two queries
Shortcuts
projectId |> fetch

Introduction

The QueryBuilder module allows you to compose database queries in a type-safe way. Below you can find a short reference to all the commonly-used functions.

Creating a new query

To query the database for some records, you first need to build a query. You can just use the query function for that.

let myQueryBuilder = query

You can optionally specify the model you want to query:

let myProjectQueryBuilder = query @Project

Running a query

You can run a query using fetch, fetchOneOrNothing or fetchOne:

many rows: `fetch`

To run a query which will return many rows use fetch:

example :: IO [Project]
example = do
    projects <- query @Project |> fetch
    -- Query: `SELECT * FROM projects`
    pure projects

maybe single row: `fetchOneOrNothing`

To run a query which will maybe return a single row use fetchOneOrNothing:

example :: IO (Maybe Project)
example = do
    project <- query @Project |> fetchOneOrNothing
    -- Query: `SELECT * FROM projects LIMIT 1`
    pure project

single row: `fetchOne`

To run a query which will return a single row and throw an error if no record is found use fetchOne:

example :: IO Project
example = do
    project <- query @Project |> fetchOne
    -- Query: `SELECT * FROM projects LIMIT 1`
    pure project

Where Conditions

To specify WHERE conditions, you can use filterWhere:

projectsByUser :: UserId -> IO [Project]
projectsByUser userId = do
    projects <- query @Project
            |> filterWhere (#userId, userId)
            |> filterWhere (#deleted, False)
            |> fetch
    -- Query: `SELECT * FROM projects WHERE user_id = <userId> AND deleted = false`
    pure projects

Use filterWhereNot to negate a condition:

projectsByUser :: UserId -> IO [Project]
projectsByUser userId = do
    otherProjects <- query @Project
            |> filterWhereNot (#userId, userId)
            |> fetch
    -- Query: `SELECT * FROM projects WHERE user_id != <userId>`
    pure otherProjects

There’s a case insensitive variant of filterWhere called filterWhereCaseInsensitive:

userByEmail :: Text -> IO (Maybe User)
userByEmail email = do
    user <- query @User
            |> filterWhereCaseInsensitive (#email, email)
            |> fetchOneOrNothing
    -- Query: `SELECT * FROM users WHERE LOWER(email) = <email>`
    pure user

You can also use the more general filterWhereSql:

retiredEmployees :: IO [Employee]
retiredEmployees = do
    employees <- query @Employee
             |> filterWhereSql (#retireddate, "IS NOT NULL")
             |> fetch
    -- Query: `SELECT * FROM employee WHERE retireddate IS NOT NULL`
    pure employees

Several other filter-functions for generating WHERE clauses exist, such as filterWhereIn and filterWhereNotIn which take lists of items. Read more about these in the API docs on QueryBuilder

Order By

You can just use orderBy #field:

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

Nested orderBys work as expected:

projects <- query @Employee
        |> orderBy #lastname
        |> orderBy #firstname
        |> fetch
-- Query: `SELECT * FROM employees ORDER BY lastname, firstname`

Limit

To limit the number of rows returned:

projects <- query @Project
        |> limit 10
        |> fetch
-- Query: `SELECT * FROM projects LIMIT 10`

Offset

To skip multiple rows:

projects <- query @Project
        |> offset 10
        |> fetch
-- Query: `SELECT * FROM projects OFFSET 10`

Offset is most often used together with a limit to implement pagination.

Or

projects <- query @Project
         |> queryOr
            (filterWhere (#userId, userId)) (filterWhere (#teamId, teamId))
        |> fetch
-- Query: `SELECT * FROM projects WHERE (user_id = ?) OR (team_id = ?)`

Union / Merging two queries

Two query builders of the same type can be merged like this:

-- SELECT * FROM projects WHERE team_id = ?
let teamProjects :: QueryBuilder Project = query @Project |> filterWhere (#teamId, teamId)

-- SELECT * FROM projects WHERE team_id IS NULL AND created_by = ?
let personalProjects :: QueryBuilder Project = query @Project |> filterWhere (#teamId, Nothing) |> filterWhere (#createdBy, currentUserId)

-- SELECT * FROM projects WHERE (team_id = ?) OR (team_id IS NULL AND created_by = ?)
let projects :: QueryBuilder Project = queryUnion teamProjects personalProjects

Shortcuts

`findBy #field value`

Just a shortcut for filterWhere (#field, value) |> fetchOne

-- Long version
project <- query @Project |> filterWhere (#userId, userId) |> fetchOne
-- Shorter version
project <- query @Project |> findBy #userId userId

`findMaybeBy #field value`

Just a shortcut for filterWhere (#field, value) |> fetchOneOrNothing

-- Long version
project <- query @Project |> filterWhere (#userId, userId) |> fetchOneOrNothing
-- Shorter version
project <- query @Project |> findMaybeBy #userId userId

`findManyBy #field value`

Just a shortcut for filterWhere (#field, value) |> fetch

-- Long version
projects <- query @Project |> filterWhere (#userId, userId) |> fetch
-- Shorter version
projects <- query @Project |> findManyBy #userId userId

`projectId |> fetch`

Ids also have fetch implementations, that way you can just run:

let projectId :: ProjectId = ...
project <- projectId |> fetch

For convenience there is also a fetch implementation for Maybe SomeId:

let assignedUserId :: Maybe UserId = project.assignedUserId
assignedUser <- assignedUserId |> fetchOneOrNothing