Home › Tutorials › Query Builder

Query Builder

Build complex SQL queries using the fluent API. Type-safe, readable, and SQL injection-proof.

Why Use the Query Builder?

SELECT Queries

Basic Select

result := db.select_from ("users").execute
-- SELECT * FROM users

Select Specific Columns

result := db.select_from ("users")
    .columns ("id, name, email")
    .execute
-- SELECT id, name, email FROM users

With WHERE Clause

result := db.select_from ("users")
    .where ("active = ?", 1)
    .execute
-- SELECT * FROM users WHERE active = 1

-- Multiple conditions
result := db.select_from ("users")
    .where ("active = 1")
    .and_where ("created_at > ?", "2024-01-01")
    .execute

Ordering and Limiting

result := db.select_from ("users")
    .order_by ("created_at DESC")
    .limit (10)
    .offset (20)  -- Skip first 20
    .execute

DISTINCT

result := db.select_from ("orders")
    .columns ("customer_id")
    .distinct
    .execute
-- SELECT DISTINCT customer_id FROM orders

JOINs

-- INNER JOIN
result := db.select_from ("orders")
    .columns ("orders.id, orders.total, users.name")
    .join ("users", "users.id = orders.user_id")
    .execute

-- LEFT JOIN
result := db.select_from ("users")
    .columns ("users.name, COUNT(orders.id) as order_count")
    .left_join ("orders", "orders.user_id = users.id")
    .group_by ("users.id")
    .execute

GROUP BY and HAVING

result := db.select_from ("orders")
    .columns ("user_id, COUNT(*) as order_count, SUM(total) as total_spent")
    .group_by ("user_id")
    .having ("order_count >= 5")
    .order_by ("total_spent DESC")
    .execute

INSERT Queries

db.insert_into ("users")
    .value ("name", "Alice")
    .value ("email", "alice@example.com")
    .value ("age", 30)
    .execute

-- INSERT OR REPLACE (upsert)
db.insert_into ("settings")
    .or_replace
    .value ("key", "theme")
    .value ("value", "dark")
    .execute

UPDATE Queries

db.update ("users")
    .set ("name", "Alice Smith")
    .set ("updated_at", "datetime('now')")
    .where ("id = ?", 1)
    .execute

-- Increment a counter
db.update ("articles")
    .set_raw ("view_count", "view_count + 1")
    .where ("id = ?", article_id)
    .execute

DELETE Queries

db.delete_from ("users")
    .where ("id = ?", user_id)
    .execute

-- Delete old records
db.delete_from ("logs")
    .where ("created_at < datetime('now', '-30 days')")
    .execute

Debugging: View Generated SQL

local
    builder: SIMPLE_SQL_SELECT_BUILDER
do
    builder := db.select_from ("users")
        .columns ("id, name")
        .where ("active = 1")
        .order_by ("name")
        .limit (10)

    -- See the SQL without executing
    print (builder.to_sql)
    -- Output: SELECT id, name FROM users WHERE active = 1 ORDER BY name LIMIT 10

    -- Then execute
    result := builder.execute
end

Building Queries Dynamically

build_search_query (a_term: detachable STRING; a_category: detachable INTEGER; a_limit: INTEGER): SIMPLE_SQL_RESULT
    local
        builder: SIMPLE_SQL_SELECT_BUILDER
    do
        builder := db.select_from ("products")
            .columns ("id, name, price")

        if attached a_term as term then
            builder := builder.where ("name LIKE ?", "%%" + term + "%%")
        end

        if attached a_category as cat then
            if attached a_term then
                builder := builder.and_where ("category_id = ?", cat)
            else
                builder := builder.where ("category_id = ?", cat)
            end
        end

        builder := builder.order_by ("name").limit (a_limit)
        Result := builder.execute
    end

Next Steps

← Basic CRUD | Transactions →