Example

Reporting Query

Reporting queries are where people usually reach for raw SQL. Quarry still fits: joins stay visible, aggregates stay explicit, and the few vendor-specific pieces can be isolated instead of spread through the codebase.

The report shape

type DailySalesRow struct {
    Day         time.Time `db:"day"`
    Orders      int64     `db:"orders"`
    PaidOrders  int64     `db:"paid_orders"`
    RevenueCents int64    `db:"revenue_cents"`
}

This report is intentionally Postgres-leaning in one place so the shape stays easy to read. The rest is normal Quarry builder code.

Build the report

func DailySalesReport(qq *quarry.Quarry, start, end time.Time) quarry.SQLer {
    return qq.Select(
        quarry.Raw("DATE_TRUNC('day', o.created_at) AS day"),
        quarry.Raw("COUNT(*) AS orders"),
        quarry.Raw("SUM(CASE WHEN o.status = ? THEN 1 ELSE 0 END) AS paid_orders", "paid"),
        quarry.Raw("SUM(o.total_cents) AS revenue_cents"),
    ).
        From("orders o").
        LeftJoin("order_items i ON i.order_id = o.id").
        Where(
            quarry.Gte("o.created_at", start),
            quarry.Lt("o.created_at", end),
            quarry.IsNull("o.deleted_at"),
        ).
        GroupBy(quarry.Raw("DATE_TRUNC('day', o.created_at)")).
        Having(quarry.Gt(quarry.Raw("COUNT(*)"), 0)).
        OrderBy("day ASC")
}

Why the raw fragments are here Quarry is not trying to outsmart a reporting query. The point is to keep the rest of the statement structured while leaving the obviously vendor-specific bits in plain view.

Wrap it in a recipe

type ReportWindow struct {
    Start time.Time
    End   time.Time
}

reportRecipe := codex.NewRecipe(func(qq *quarry.Quarry, window ReportWindow) quarry.SQLer {
    return DailySalesReport(qq, window.Start, window.End)
})

A recipe is useful when the dashboard, the CSV export, and the admin preview all need the same statement. The SQL stays in one place; the callers just pass the time window.

Run the report

rows, err := scan.All[DailySalesRow](ctx, db,
    DailySalesReport(qq, start, end),
)
if err != nil {
    return err
}

If the report should only show one row per day, the scan layer stays out of the way. It just hydrates the result set into the struct you asked for.

What the SQL looks like

SELECT DATE_TRUNC('day', o.created_at) AS day,
       COUNT(*) AS orders,
       SUM(CASE WHEN o.status = $1 THEN 1 ELSE 0 END) AS paid_orders,
       SUM(o.total_cents) AS revenue_cents
FROM orders o
LEFT JOIN order_items i ON i.order_id = o.id
WHERE o.created_at >= $2
  AND o.created_at < $3
GROUP BY DATE_TRUNC('day', o.created_at)
HAVING COUNT(*) > $4
ORDER BY day ASC