IHP Typed SQL API

Overview

IHP Typed SQL provides compile-time type checking for SQL queries. It connects to your development database during compilation to infer types, ensuring SQL queries are correct before runtime. Key Features:

Compile-time type inference from SQL
Automatic decoder generation
Type-safe parameters with ${expr} syntax
Support for complex queries (JOINs, CTEs, window functions)
Nullability tracking for JOINs and computed expressions

Installation

Add ihp-typed-sql to your project’s default.nix:

haskellDeps = p: with p; [
    ihp-typed-sql
];

Import the module:

import IHP.TypedSql

Requirements

Development database must be running during compilation. The typedSql quasiquoter connects to DATABASE_URL to describe queries.

Core Types

TypedQuery

A prepared query with type-safe result decoder:

data TypedQuery result = TypedQuery
    { tqSnippet :: !Snippet.Snippet
    , tqResultDecoder :: !(HasqlDecoders.Row result)
    }

This is the runtime value produced by [typedSql| ... |]. You don’t construct it manually.

Quasiquoter

typedSql

Parse SQL at compile time and generate type-safe query:

typedSql :: QuasiQuoter

Example:

users <- sqlQueryTyped [typedSql|
    SELECT id, email FROM users WHERE created_at > ${since}
|]
-- Type inferred: [(Id' "users", Text)]

Execution Functions

sqlQueryTyped

Execute a SELECT query or DML with RETURNING:

sqlQueryTyped :: (?modelContext :: ModelContext) => TypedQuery result -> IO [result]

query

TypedQuery result

Typed query from [typedSql| ... |]

results

[result]

List of result rows with inferred type

Example:

action UsersAction = do
    let minAge = 18 :: Int
    
    users <- sqlQueryTyped [typedSql|
        SELECT id, email, age FROM users 
        WHERE age >= ${minAge}
        ORDER BY created_at DESC
    |]
    -- users :: [(Id' "users", Text, Int)]
    
    render IndexView { users }

sqlExecTyped

Execute INSERT/UPDATE/DELETE without RETURNING:

sqlExecTyped :: (?modelContext :: ModelContext) => TypedQuery result -> IO Int64

query

TypedQuery result

Typed query from [typedSql| ... |]

rowsAffected

Int64

Number of rows affected

Example:

action DeleteOldItemsAction = do
    let cutoffDate = currentTime - (30 * days)
    
    rowsDeleted <- sqlExecTyped [typedSql|
        DELETE FROM items 
        WHERE created_at < ${cutoffDate}
    |]
    
    setSuccessMessage $ show rowsDeleted <> " items deleted"
    redirectTo ItemsAction

Parameters

Use ${expr} to splice Haskell expressions as typed parameters:

Basic Parameters

let name = "Alice" :: Text
let minAge = 25 :: Int

results <- sqlQueryTyped [typedSql|
    SELECT id FROM users 
    WHERE name = ${name} AND age >= ${minAge}
|]

Parameter types are inferred from the columns they’re compared against.

Foreign Key Parameters

Foreign keys are automatically typed as Id' "table":

let userId = currentUserId  -- Id' "users"

tasks <- sqlQueryTyped [typedSql|
    SELECT id, title FROM tasks 
    WHERE user_id = ${userId}
|]
-- Type: [(Id' "tasks", Text)]

Array Parameters

Use with IN or ANY:

let taskIds = [taskId1, taskId2, taskId3] :: [Id' "tasks"]

-- With IN
tasks <- sqlQueryTyped [typedSql|
    SELECT title FROM tasks 
    WHERE id IN (${taskIds})
|]

-- With ANY
tasks <- sqlQueryTyped [typedSql|
    SELECT title FROM tasks 
    WHERE id = ANY(${taskIds})
|]

Selecting Columns

Single Column

names <- sqlQueryTyped [typedSql| SELECT name FROM users |]
-- names :: [Text]

Multiple Columns

Returns tuples:

users <- sqlQueryTyped [typedSql|
    SELECT id, email, created_at FROM users
|]
-- users :: [(Id' "users", Text, UTCTime)]

Table Wildcard (`table.*`)

Returns the model type:

users <- sqlQueryTyped [typedSql|
    SELECT users.* FROM users 
    WHERE email LIKE '%@example.com'
|]
-- users :: [User]

Requires a FromRowHasql instance (automatically generated by IHP).

Nullability

NOT NULL Columns

Non-nullable columns map directly:

-- email is NOT NULL
emails <- sqlQueryTyped [typedSql| SELECT email FROM users |]
-- emails :: [Text]

Nullable Columns

Nullable columns are wrapped in Maybe:

-- phone is nullable
phones <- sqlQueryTyped [typedSql| SELECT phone FROM users |]
-- phones :: [Maybe Text]

Computed Expressions

Always Maybe (PostgreSQL cannot guarantee non-null):

counts <- sqlQueryTyped [typedSql| SELECT COUNT(*) FROM users |]
-- counts :: [Maybe Integer]

sums <- sqlQueryTyped [typedSql| SELECT SUM(amount) FROM orders |]
-- sums :: [Maybe Scientific]

literals <- sqlQueryTyped [typedSql| SELECT 1, 'hello' |]
-- literals :: [(Maybe Int, Maybe Text)]

JOINs

INNER JOIN

Both sides are non-nullable:

results <- sqlQueryTyped [typedSql|
    SELECT u.name, o.total
    FROM users u
    INNER JOIN orders o ON o.user_id = u.id
|]
-- results :: [(Text, Scientific)]

LEFT JOIN

Right side is nullable:

results <- sqlQueryTyped [typedSql|
    SELECT u.name, o.total
    FROM users u
    LEFT JOIN orders o ON o.user_id = u.id
|]
-- results :: [(Text, Maybe Scientific)]
-- o.total is Maybe because some users may have no orders

RIGHT JOIN

Left side is nullable:

results <- sqlQueryTyped [typedSql|
    SELECT u.name, o.total
    FROM users u
    RIGHT JOIN orders o ON o.user_id = u.id
|]
-- results :: [(Maybe Text, Scientific)]

Complex JOIN Example

results <- sqlQueryTyped [typedSql|
    SELECT 
        u.id,
        u.email,
        p.title,
        t.name
    FROM users u
    LEFT JOIN projects p ON p.user_id = u.id
    LEFT JOIN tasks t ON t.project_id = p.id
|]
-- results :: [(Id' "users", Text, Maybe Text, Maybe Text)]
-- Both p.title and t.name are Maybe due to LEFT JOINs

Type Mapping

PostgreSQL Type	Haskell Type
`int2`, `int4`	`Int`
`int8`	`Integer`
`text`, `varchar`, `bpchar`, `citext`	`Text`
`bool`	`Bool`
`uuid` (primary/foreign key)	`Id' "table"`
`uuid` (other)	`UUID`
`timestamptz`	`UTCTime`
`timestamp`	`LocalTime`
`date`	`Day`
`time`	`TimeOfDay`
`json`, `jsonb`	`Aeson.Value`
`bytea`	`ByteString`
`float4`	`Float`
`float8`	`Double`
`numeric`	`Scientific`
`point`	`Point`
`inet`	`Inet`
`tsvector`	`Tsvector`
`interval`	`Interval`
`anytype[]`	`[ElementType]`
Custom enums	Corresponding Haskell type

Advanced Queries

CTEs (Common Table Expressions)

results <- sqlQueryTyped [typedSql|
    WITH active_users AS (
        SELECT id, email FROM users WHERE active = true
    )
    SELECT au.email, COUNT(t.id)
    FROM active_users au
    LEFT JOIN tasks t ON t.user_id = au.id
    GROUP BY au.email
|]
-- results :: [(Text, Maybe Integer)]

Window Functions

results <- sqlQueryTyped [typedSql|
    SELECT 
        name,
        salary,
        AVG(salary) OVER (PARTITION BY department_id) AS dept_avg
    FROM employees
|]
-- results :: [(Text, Scientific, Maybe Scientific)]

Subqueries

results <- sqlQueryTyped [typedSql|
    SELECT 
        (SELECT COUNT(*) FROM tasks WHERE user_id = u.id) AS task_count,
        u.email
    FROM users u
|]
-- results :: [(Maybe Integer, Text)]

UNION

results <- sqlQueryTyped [typedSql|
    SELECT name FROM users
    UNION
    SELECT name FROM archived_users
|]
-- results :: [Maybe Text]  -- UNION results are computed columns

INSERT with RETURNING

let email = "[email protected]" :: Text
let name = "New User" :: Text

newUsers <- sqlQueryTyped [typedSql|
    INSERT INTO users (email, name)
    VALUES (${email}, ${name})
    RETURNING id, created_at
|]
-- newUsers :: [(Id' "users", UTCTime)]

case headMay newUsers of
    Just (userId, createdAt) -> 
        setSuccessMessage "User created"
    Nothing ->
        error "Insert failed"

UPDATE with RETURNING

let userId = currentUserId
let newEmail = "[email protected]" :: Text

updated <- sqlQueryTyped [typedSql|
    UPDATE users 
    SET email = ${newEmail}, updated_at = NOW()
    WHERE id = ${userId}
    RETURNING email, updated_at
|]
-- updated :: [(Text, UTCTime)]

DELETE with RETURNING

let minDate = currentTime - (90 * days)

deleted <- sqlQueryTyped [typedSql|
    DELETE FROM sessions
    WHERE created_at < ${minDate}
    RETURNING id
|]
-- deleted :: [Id' "sessions"]

setSuccessMessage $ show (length deleted) <> " sessions deleted"

Error Messages

Placeholder Count Mismatch

[typedSql| SELECT * FROM users WHERE id = ${userId} AND email = ${email} |]
-- But only provide userId parameter
-- Compile error: "placeholder count mismatch. SQL expects 2 parameters but found 1"

Type Mismatch

let age = "25" :: Text  -- Wrong type
[typedSql| SELECT * FROM users WHERE age > ${age} |]
-- Compile error: Couldn't match type 'Text' with 'Int'

RLS Not Enabled

[typedSql| SELECT * FROM users |]
-- Runtime error if RLS not enabled: "Row level security is required"

Composite Columns

[typedSql| SELECT users FROM users |]
-- Compile error: "composite columns must be expanded (use SELECT users.* or list columns explicitly)"

Best Practices

Use for complex queries: Standard query builder is better for simple CRUD

-- Simple: use query builder
users <- query @User |> fetch

-- Complex: use typedSql
results <- sqlQueryTyped [typedSql|
    SELECT u.*, COUNT(t.id) as task_count
    FROM users u
    LEFT JOIN tasks t ON t.user_id = u.id
    GROUP BY u.id
    HAVING COUNT(t.id) > 5
|]

Prefer table.* for full records: Avoids manual tuple deconstruction

users <- sqlQueryTyped [typedSql| SELECT users.* FROM users |]
-- users :: [User]  -- Better than tuples

Handle computed column nullability: Always expect Maybe for aggregates

counts <- sqlQueryTyped [typedSql| SELECT COUNT(*) FROM users |]
let count = fromMaybe 0 (headMay counts >>= id)

Keep development DB in sync: Type inference depends on current schema

Use type annotations for parameters: Makes intent clear

let userId = currentUserId :: Id' "users"
let minAge = 18 :: Int

Comparison with sqlQuery

Feature	`sqlQuery`	`typedSql`
Type checking	Runtime	Compile time
Result types	Manual `FromRow`	Inferred automatically
Parameter syntax	`?` placeholders	`${expr}` inline
Database required	No	Yes (at compile time)
Best for	Simple queries	Complex queries

Migration example:

-- Before: sqlQuery
import Database.PostgreSQL.Simple
import Database.PostgreSQL.Simple.FromRow

data UserWithCount = UserWithCount 
    { userId :: UUID
    , email :: Text
    , taskCount :: Int
    }

instance FromRow UserWithCount where
    fromRow = UserWithCount <$> field <*> field <*> field

users <- sqlQuery 
    "SELECT u.id, u.email, COUNT(t.id) FROM users u LEFT JOIN tasks t ON t.user_id = u.id GROUP BY u.id" 
    ()

-- After: typedSql
users <- sqlQueryTyped [typedSql|
    SELECT u.id, u.email, COUNT(t.id)
    FROM users u
    LEFT JOIN tasks t ON t.user_id = u.id
    GROUP BY u.id
|]
-- Type inferred: [(Id' "users", Text, Maybe Integer)]
-- No manual FromRow instance needed!

Core Modules

Extensions

​Overview

​Installation

​Requirements

​Core Types

​TypedQuery

​Quasiquoter

​typedSql

​Execution Functions

​sqlQueryTyped

​sqlExecTyped

​Parameters

​Basic Parameters

​Foreign Key Parameters

​Array Parameters

​Selecting Columns

​Single Column

​Multiple Columns

​Table Wildcard (table.*)

​Nullability

​NOT NULL Columns

​Nullable Columns

​Computed Expressions

​JOINs

​INNER JOIN

​LEFT JOIN

​RIGHT JOIN

​Complex JOIN Example

​Type Mapping

​Advanced Queries

​CTEs (Common Table Expressions)

​Window Functions

​Subqueries

​UNION

​INSERT with RETURNING

​UPDATE with RETURNING

​DELETE with RETURNING

​Error Messages

​Placeholder Count Mismatch

​Type Mismatch

​RLS Not Enabled

​Composite Columns

​Best Practices

​Comparison with sqlQuery

​See Also

Build docs developers (and LLMs) love

Overview

Installation

Requirements

Core Types

TypedQuery

Quasiquoter

typedSql

Execution Functions

sqlQueryTyped

sqlExecTyped

Parameters

Basic Parameters

Foreign Key Parameters

Array Parameters

Selecting Columns

Single Column

Multiple Columns

Table Wildcard (`table.*`)

Nullability

NOT NULL Columns

Nullable Columns

Computed Expressions

JOINs

INNER JOIN

LEFT JOIN

RIGHT JOIN

Complex JOIN Example

Type Mapping

Advanced Queries

CTEs (Common Table Expressions)

Window Functions

Subqueries

UNION

INSERT with RETURNING

UPDATE with RETURNING

DELETE with RETURNING

Error Messages

Placeholder Count Mismatch

Type Mismatch

RLS Not Enabled

Composite Columns

Best Practices

Comparison with sqlQuery

See Also