Skip to content

Query Builder

David Ciulla edited this page Nov 7, 2026 · 2 revisions

Query Builder

Laravex.Database.Query is a fluent, pipeline-style query builder that compiles to parameterized SQL executed against SQLite. It mirrors the Laravel Query Builder API closely.

Getting Started

Start a query with Query.table/1 (or DB.table/1):

alias Laravex.Database.Query

users = Query.table("users") |> Query.get()

Every builder function returns a new %Query{} struct, making queries fully composable and side-effect free until a terminal function is called.

Retrieving Results

# All rows
Query.table("users") |> Query.get()
# => [%{"id" => 1, "name" => "Alice"}, ...]

# First row (adds LIMIT 1)
Query.table("users") |> Query.where("active", 1) |> Query.first()
# => %{"id" => 1, ...} | nil

# Single column value
Query.table("settings") |> Query.where("key", "app_name") |> Query.value("value")

# List of values
Query.table("users") |> Query.pluck("email")
# => ["alice@example.com", "bob@example.com"]

Selecting Columns

Query.table("users")
|> Query.select(["id", "name", "email"])
|> Query.get()

# Raw expression
Query.table("orders")
|> Query.select_raw("SUM(total) AS revenue")
|> Query.get()

# Append columns without replacing the existing selection (Laravel `addSelect`)
q
|> Query.select(["id", "name"])
|> Query.add_select("email")           # single column
|> Query.add_select(["role", "bio"])   # list of columns

# DISTINCT rows
Query.table("users") |> Query.distinct() |> Query.pluck("country")

WHERE Clauses

# Equality
Query.where(q, "status", "active")

# Comparison operator
Query.where(q, "age", ">", 18)

# OR WHERE
Query.or_where(q, "role", "admin")

# NULL checks
Query.where_null(q, "deleted_at")
Query.where_not_null(q, "email_verified_at")
Query.or_where_null(q, "deleted_at")
Query.or_where_not_null(q, "email_verified_at")

# IN / NOT IN
Query.where_in(q, "id", [1, 2, 3])
Query.where_not_in(q, "status", ["banned", "deleted"])
Query.or_where_in(q, "role", ["admin", "moderator"])
Query.or_where_not_in(q, "status", ["banned"])

# BETWEEN / NOT BETWEEN
Query.where_between(q, "price", 10, 100)
Query.where_not_between(q, "price", 10, 100)
Query.or_where_between(q, "price", 10, 100)
Query.or_where_not_between(q, "price", 10, 100)

# LIKE
Query.where_like(q, "name", "%alice%")
Query.or_where_like(q, "email", "%@example.com")

# Column comparison
Query.where_column(q, "updated_at", ">", "created_at")
Query.where_column(q, "email", "confirm_email")  # defaults to =
Query.or_where_column(q, "updated_at", "created_at")

# Date / time parts (SQLite strftime-based)
Query.where_date(q, "created_at", ~D[2024-01-01])
Query.where_year(q, "created_at", 2024)
Query.where_month(q, "created_at", 1)
Query.where_day(q, "created_at", 15)
Query.where_time(q, "created_at", ~T[12:00:00])

Query.or_where_date(q, "created_at", ~D[2024-06-01])
Query.or_where_year(q, "created_at", 2025)
Query.or_where_month(q, "created_at", 6)
Query.or_where_day(q, "created_at", 1)
Query.or_where_time(q, "created_at", ~T[00:00:00])

# EXISTS subquery (Laravel `whereExists`)
Query.where_exists(q, fn sub ->
  sub
  |> Query.select(["1"])
  |> Query.where("orders.user_id", "users.id")
end)

Query.or_where_exists(q, fn sub -> ... end)

# Raw fragment
Query.where_raw(q, "LENGTH(name) > ?", [5])
Query.or_where_raw(q, "role = 'admin'")

Joins

# INNER JOIN
Query.table("posts")
|> Query.join("users", "posts.user_id", "=", "users.id")
|> Query.select(["posts.id", "users.name"])
|> Query.get()

# LEFT JOIN
Query.table("users")
|> Query.left_join("orders", "users.id", "=", "orders.user_id")
|> Query.get()

# RIGHT JOIN
Query.table("orders")
|> Query.right_join("users", "users.id", "=", "orders.user_id")
|> Query.get()

# Custom operator
Query.left_join(q, "logs", "logs.user_id", "!=", "users.id")

Ordering, Limiting, and Offsetting

q
|> Query.order_by("created_at", "desc")
|> Query.order_by_desc("updated_at")  # shorthand
|> Query.limit(10)
|> Query.offset(20)

# Newest/oldest shortcuts (default column: created_at)
Query.table("posts") |> Query.latest() |> Query.get()
Query.table("posts") |> Query.latest("published_at") |> Query.get()
Query.table("posts") |> Query.oldest() |> Query.get()

# Paginate by page number (1-based, sets LIMIT + OFFSET)
Query.table("posts") |> Query.for_page(2, 15) |> Query.get()

# Random order
Query.table("quotes") |> Query.in_random_order() |> Query.first()

Grouping and Aggregates

# GROUP BY / HAVING
Query.table("orders")
|> Query.select(["user_id"])
|> Query.select_raw("SUM(total) AS total")
|> Query.group_by(["user_id"])
|> Query.having("total", ">", 100)
|> Query.get()

# or_having / having_raw
Query.or_having(q, "total", ">", 50)
Query.having_raw(q, "SUM(total) > ? AND COUNT(*) > ?", [100, 2])
Query.or_having_raw(q, "AVG(rating) > 4")

# Aggregate shortcuts
Query.table("orders") |> Query.count()
Query.table("orders") |> Query.sum("total")
Query.table("orders") |> Query.avg("total")
Query.table("orders") |> Query.max("total")
Query.table("orders") |> Query.min("total")

# EXISTS
Query.table("orders") |> Query.where("user_id", 42) |> Query.exists?()

Conditional Queries

Apply a clause only when a condition is truthy (Laravel when / unless):

# when_cond — apply the function when condition is truthy
q
|> Query.when_cond(params["search"], fn q ->
  Query.where_like(q, "name", "%#{params["search"]}%")
end)

# unless_cond — apply when condition is falsy
q
|> Query.unless_cond(show_all, fn q ->
  Query.where(q, "published", 1)
end)

Inserting Records

# Single row — returns the new row's ID
id = Query.insert("users", %{"name" => "Alice", "email" => "alice@example.com"})

# Multiple rows
Query.insert_all("tags", [
  %{"name" => "elixir"},
  %{"name" => "laravex"}
])

# Insert, skip on UNIQUE / PRIMARY KEY conflict (SQLite INSERT OR IGNORE)
Query.insert_or_ignore("users", %{"email" => "alice@example.com", "name" => "Alice"})
Query.insert_or_ignore("users", [
  %{"email" => "alice@example.com"},
  %{"email" => "bob@example.com"}
])

# Upsert — INSERT … ON CONFLICT … DO UPDATE SET
Query.upsert("users",
  [%{"email" => "alice@example.com", "name" => "Alice"}],
  ["email"],       # unique_by columns
  ["name"]         # columns to update on conflict
)
# Pass nil as the last arg to DO NOTHING on conflict (instead of updating)

Updating Records

Query.table("users")
|> Query.where("id", 1)
|> Query.update(%{"name" => "Bob"})

Deleting Records

Query.table("users")
|> Query.where("active", 0)
|> Query.delete()

Chunking Results

For large tables, process rows in chunks to avoid loading everything into memory:

# Offset-based chunks
Query.table("users")
|> Query.chunk(100, fn rows ->
  Enum.each(rows, &send_email/1)
end)

# Keyset-based chunks (more efficient for large tables)
Query.table("users")
|> Query.chunk_by_id(100, fn rows ->
  Enum.each(rows, &send_email/1)
end)

chunk_by_id/3 uses a cursor on the id column (or a custom :column override) and avoids the offset penalty of chunk/3.

The DB Facade

Laravex.Database.DB delegates to Query and Connection for concise top-level access:

alias Laravex.Database.DB

DB.table("users") |> Query.where("id", 1) |> Query.first()
DB.select("SELECT NOW()")
DB.statement("PRAGMA journal_mode=WAL")
DB.transaction(fn -> ... end)

See Also

  • Database — Connection configuration and raw SQL
  • Migrations — Schema creation and Blueprint
  • Eloquex — Model-based queries built on top of Query
  • Pagination — paginate/3 and simple_paginate/3

Clone this wiki locally