QRB - PostgreSQL Query Builder

A comprehensive PostgreSQL query builder in Go with extensive support for PostgreSQL-specific features.

Why QRB?

Pure PostgreSQL focus - No compromises for multi-database support
JSON-first design - First-class support for json_build_object and json_agg to build hierarchical data
Complete feature set - Supports advanced PostgreSQL features like CTEs, window functions, arrays, and more
Type-safe - Uses explicit types instead of any where possible, no reflection
Immutable builders - All data structures are immutable by design
Multiple drivers - Works with both database/sql and pgx via qrbsql and qrbpgx packages

Installation

go get github.com/networkteam/qrb

Quick Start

package main

import (
    "fmt"
    . "github.com/networkteam/qrb"
	"github.com/networkteam/qrb/qrbsql"
)

func main() {
	filter := true
    q := Select(N("name"), N("email")).
        From(N("users")).
        Where(N("active").Eq(Arg(filter))).
        OrderBy(N("name"))

    sql, args, _ := qrbsql.Build(q).ToSQL()
    fmt.Println(sql) // SELECT name, email FROM users WHERE active = $1 ORDER BY name
	fmt.Println(args) // [true]
}

Core Concepts

Immutable Builders: All builders return new instances, making them safe for reuse
Expressions: Use N() for identifiers, Arg() for parameters, and String(), Int(), etc. for literals
Fluent API: Chain method calls naturally following SQL structure
Type Safety: Builders guide you through valid SQL construction with appropriate method availability

Recommended Import Pattern

For the best development experience, we recommend using a dot import for the main qrb package:

import (
    . "github.com/networkteam/qrb"
    "github.com/networkteam/qrb/fn"
)

This allows you to write clean, readable queries without the qrb. prefix:

// With dot import (recommended)
q := Select(N("name")).From(N("users")).Where(N("active").Eq(Bool(true)))

// Without dot import (more verbose)
q := qrb.Select(qrb.N("name")).From(qrb.N("users")).Where(qrb.N("active").Eq(qrb.Bool(true)))

All examples in this README use the dot import pattern for improved readability.

Examples

Basic Queries

Simple SELECT

q := Select(N("*")).From(N("users"))

SELECT * FROM users

SELECT with WHERE

q := Select(N("name"), N("email")).
    From(N("users")).
    Where(N("active").Eq(Bool(true)))

SELECT name, email FROM users WHERE active = true

SELECT with multiple conditions

q := Select(N("*")).
    From(N("employees")).
    Where(And(
        Or(
            N("firstname").ILike(Arg("John%")),
            N("lastname").ILike(Arg("John%")),
        ),
        N("active").Eq(Bool(true)),
    ))

SELECT * FROM employees 
WHERE ((firstname ILIKE $1) OR (lastname ILIKE $1)) AND (active = $2)

SELECT DISTINCT

q := Select().Distinct().
    Select(N("department")).
    From(N("employees"))

SELECT DISTINCT department FROM employees

SELECT with ORDER BY and LIMIT

q := Select(N("name"), N("salary")).
    From(N("employees")).
    OrderBy(N("salary")).Desc().NullsLast().
    Limit(Int(10)).
    Offset(Int(20))

SELECT name, salary FROM employees 
ORDER BY salary DESC NULLS LAST 
LIMIT 10 OFFSET 20

CRUD Operations

INSERT with VALUES

q := InsertInto(N("users")).
    ColumnNames("name", "email", "active").
    Values(String("John Doe"), String("[email protected]"), Bool(true))

INSERT INTO users (name, email, active) 
VALUES ('John Doe', '[email protected]', true)

INSERT multiple rows

q := InsertInto(N("products")).
    ColumnNames("name", "price", "category").
    Values(String("Laptop"), Float(999.99), String("Electronics")).
    Values(String("Book"), Float(19.99), String("Literature"))

INSERT INTO products (name, price, category) VALUES
    ('Laptop', 999.99, 'Electronics'),
    ('Book', 19.99, 'Literature')

INSERT with SELECT

q := InsertInto(N("archived_users")).
    Query(Select(N("*")).From(N("users")).Where(N("active").Eq(Bool(false))))

INSERT INTO archived_users SELECT * FROM users WHERE active = false

INSERT with RETURNING

q := InsertInto(N("users")).
    ColumnNames("name", "email").
    Values(String("Jane Doe"), String("[email protected]")).
    Returning(N("id"), N("created_at"))

INSERT INTO users (name, email) VALUES ('Jane Doe', '[email protected]')
RETURNING id, created_at

UPSERT (INSERT with ON CONFLICT)

q := InsertInto(N("users")).
    ColumnNames("email", "name").
    Values(String("[email protected]"), String("John Updated")).
    OnConflict(N("email")).DoUpdate().
    Set("name", N("EXCLUDED.name")).
    Set("updated_at", N("NOW()"))

INSERT INTO users (email, name) VALUES ('[email protected]', 'John Updated')
ON CONFLICT (email) DO UPDATE 
SET name = EXCLUDED.name, updated_at = NOW()

UPDATE

q := Update(N("users")).
    Set("name", String("Updated Name")).
    Set("updated_at", N("NOW()")).
    Where(N("id").Eq(Arg(123)))

UPDATE users SET name = 'Updated Name', updated_at = NOW() WHERE id = $1

UPDATE with FROM

q := Update(N("employees")).
    Set("department_name", N("d.name")).
    From(N("departments")).As("d").
    Where(N("employees.department_id").Eq(N("d.id")))

UPDATE employees SET department_name = d.name 
FROM departments AS d 
WHERE employees.department_id = d.id

DELETE

q := DeleteFrom(N("users")).
    Where(N("active").Eq(Bool(false)))

DELETE FROM users WHERE active = false

DELETE with USING

q := DeleteFrom(N("orders")).
    Using(N("customers")).
    Where(And(
        N("orders.customer_id").Eq(N("customers.id")),
        N("customers.status").Eq(String("inactive")),
    ))

DELETE FROM orders USING customers 
WHERE orders.customer_id = customers.id AND customers.status = 'inactive'

Joins

INNER JOIN

q := Select(N("u.name"), N("p.title")).
    From(N("users")).As("u").
    Join(N("posts")).As("p").On(N("u.id").Eq(N("p.user_id")))

SELECT u.name, p.title FROM users AS u 
JOIN posts AS p ON u.id = p.user_id

LEFT JOIN

q := Select(N("u.name"), N("p.title")).
    From(N("users")).As("u").
    LeftJoin(N("posts")).As("p").On(N("u.id").Eq(N("p.user_id")))

SELECT u.name, p.title FROM users AS u 
LEFT JOIN posts AS p ON u.id = p.user_id

JOIN with USING

q := Select(N("u.name"), N("p.title")).
    From(N("users")).As("u").
    Join(N("posts")).As("p").Using("user_id")

SELECT u.name, p.title FROM users AS u 
JOIN posts AS p USING (user_id)

Multiple JOINs

q := Select(N("u.name"), N("p.title"), N("c.name")).
    From(N("users")).As("u").
    Join(N("posts")).As("p").On(N("u.id").Eq(N("p.user_id"))).
    Join(N("categories")).As("c").On(N("p.category_id").Eq(N("c.id")))

SELECT u.name, p.title, c.name FROM users AS u 
JOIN posts AS p ON u.id = p.user_id 
JOIN categories AS c ON p.category_id = c.id

Aggregation & Grouping

GROUP BY with aggregate functions

q := Select(N("department")).
    Select(fn.Count(N("*"))).As("employee_count").
    From(N("employees")).
    GroupBy(N("department"))

SELECT department, count(*) AS employee_count 
FROM employees 
GROUP BY department

GROUP BY with HAVING

q := Select(N("department")).
    Select(fn.Avg(N("salary"))).As("avg_salary").
    From(N("employees")).
    GroupBy(N("department")).
    Having(fn.Avg(N("salary")).Gt(Int(50000)))

SELECT department, avg(salary) AS avg_salary 
FROM employees 
GROUP BY department 
HAVING avg(salary) > 50000

GROUP BY with ROLLUP

q := Select(N("department"), N("job_title"), fn.Sum(N("salary"))).
    From(N("employees")).
    GroupBy().
    Rollup(
        Exps(N("department")),
        Exps(N("job_title")),
    )

SELECT department, job_title, sum(salary) 
FROM employees 
GROUP BY ROLLUP (department, job_title)

GROUP BY with GROUPING SETS

q := Select(N("department"), N("job_title"), fn.Sum(N("salary"))).
    From(N("employees")).
    GroupBy().
    GroupingSets(
        Exps(N("department")),
        Exps(N("job_title")),
        Exps(),
    )

SELECT department, job_title, sum(salary) 
FROM employees 
GROUP BY GROUPING SETS (department, job_title, ())

Window Functions

ROW_NUMBER

q := Select(
    N("name"),
    N("salary"),
    fn.RowNumber().Over().PartitionBy(N("department")).OrderBy(N("salary")).Desc(),
).From(N("employees"))

SELECT name, salary, row_number() OVER (PARTITION BY department ORDER BY salary DESC) 
FROM employees

RANK and DENSE_RANK

q := Select(
    N("name"),
    N("salary"),
    fn.Rank().Over().PartitionBy(N("department")).OrderBy(N("salary")).Desc(),
    fn.DenseRank().Over().PartitionBy(N("department")).OrderBy(N("salary")).Desc(),
).From(N("employees"))

SELECT name, salary, 
       rank() OVER (PARTITION BY department ORDER BY salary DESC),
       dense_rank() OVER (PARTITION BY department ORDER BY salary DESC)
FROM employees

Named Windows

q := Select(
    fn.Sum(N("salary")).Over("w"),
    fn.Avg(N("salary")).Over("w"),
).From(N("employees")).
Window("w").As().PartitionBy(N("department")).OrderBy(N("salary")).Desc().
SelectBuilder

SELECT sum(salary) OVER w, avg(salary) OVER w
FROM employees
WINDOW w AS (PARTITION BY department ORDER BY salary DESC)

JSON Operations

Simple JSON object

q := Select(
    fn.JsonBuildObject().
        Prop("id", N("id")).
        Prop("name", N("name")).
        Prop("email", N("email")),
).From(N("users"))

SELECT json_build_object('id', id, 'name', name, 'email', email) 
FROM users

JSON with aggregation

q := Select(
    N("department"),
    fn.JsonAgg(
        fn.JsonBuildObject().
            Prop("name", N("name")).
            Prop("salary", N("salary")),
    ).OrderBy(N("name")),
).From(N("employees")).
GroupBy(N("department"))

SELECT department, 
       json_agg(json_build_object('name', name, 'salary', salary) ORDER BY name)
FROM employees 
GROUP BY department

Complex nested JSON with CTEs

q := With("author_json").As(
    Select(N("authors.author_id")).
        Select(
            fn.JsonBuildObject().
                Prop("id", N("authors.author_id")).
                Prop("name", N("authors.name")),
        ).As("json").
        From(N("authors")),
).
Select(
    N("posts.post_id"),
    fn.JsonBuildObject().
        Prop("title", N("posts.title")).
        Prop("author", N("author_json.json")),
).
From(N("posts")).
LeftJoin(N("author_json")).On(N("posts.author_id").Eq(N("author_json.author_id")))

WITH author_json AS (
    SELECT authors.author_id, 
           json_build_object('id', authors.author_id, 'name', authors.name) AS json
    FROM authors
)
SELECT posts.post_id, 
       json_build_object('title', posts.title, 'author', author_json.json)
FROM posts
LEFT JOIN author_json ON posts.author_id = author_json.author_id

Array Operations

Array construction

q := Select(Array(String("a"), String("b"), String("c")))

SELECT ARRAY['a', 'b', 'c']

Array functions

q := Select(
    fn.ArrayAppend(Array(Int(1), Int(2)), Int(3)),
    fn.ArrayLength(Array(Int(1), Int(2), Int(3)), Int(1)),
)

SELECT array_append(ARRAY[1, 2], 3), array_length(ARRAY[1, 2, 3], 1)

UNNEST

q := Select(N("*")).
    From(fn.Unnest(Array(String("a"), String("b"), String("c")))).
    As("t").ColumnAliases("value")

SELECT * FROM unnest(ARRAY['a', 'b', 'c']) AS t (value)

Array aggregation

q := Select(
    N("department"),
    fn.ArrayAgg(N("name")).OrderBy(N("name")),
).From(N("employees")).
GroupBy(N("department"))

SELECT department, array_agg(name ORDER BY name) 
FROM employees 
GROUP BY department

Subqueries

EXISTS

q := Select(N("name")).
    From(N("users")).
    Where(Exists(
        Select(Int(1)).
            From(N("posts")).
            Where(N("posts.user_id").Eq(N("users.id"))),
    ))

SELECT name FROM users 
WHERE EXISTS (SELECT 1 FROM posts WHERE posts.user_id = users.id)

IN with subquery

q := Select(N("name")).
    From(N("users")).
    Where(N("id").In(
        Select(N("user_id")).
            From(N("posts")).
            Where(N("published").Eq(Bool(true))),
    ))

SELECT name FROM users 
WHERE id IN (SELECT user_id FROM posts WHERE published = true)

Correlated subquery

q := Select(N("name"), N("salary")).
    From(N("employees")).As("e1").
    Where(N("salary").Gt(
        Select(fn.Avg(N("salary"))).
            From(N("employees")).As("e2").
            Where(N("e1.department").Eq(N("e2.department"))),
    ))

SELECT name, salary FROM employees AS e1 
WHERE salary > (
    SELECT avg(salary) FROM employees AS e2 
    WHERE e1.department = e2.department
)

Subquery in FROM

q := Select(N("avg_salary")).
    From(
        Select(fn.Avg(N("salary"))).As("avg_salary").
            From(N("employees")).
            GroupBy(N("department")),
    ).As("dept_averages")

SELECT avg_salary FROM (
    SELECT avg(salary) AS avg_salary FROM employees GROUP BY department
) AS dept_averages

Advanced Features

Common Table Expressions (WITH)

q := With("recent_orders").As(
    Select(N("*")).
        From(N("orders")).
        Where(N("created_at").Gt(String("2023-01-01"))),
).
Select(N("customer_name"), fn.Count(N("*"))).
From(N("recent_orders")).
GroupBy(N("customer_name"))

WITH recent_orders AS (
    SELECT * FROM orders WHERE created_at > '2023-01-01'
)
SELECT customer_name, count(*) FROM recent_orders GROUP BY customer_name

Recursive CTE

q := WithRecursive("employee_hierarchy").
    ColumnNames("employee_id", "name", "manager_id", "level").As(
    Select(N("employee_id"), N("name"), N("manager_id"), Int(1)).
        From(N("employees")).
        Where(N("manager_id").IsNull()).
        Union().All().
        Select(N("e.employee_id"), N("e.name"), N("e.manager_id"), N("eh.level").Plus(Int(1))).
        From(N("employees")).As("e").
        Join(N("employee_hierarchy")).As("eh").On(N("e.manager_id").Eq(N("eh.employee_id"))),
).
Select(N("*")).From(N("employee_hierarchy"))

WITH RECURSIVE employee_hierarchy (employee_id, name, manager_id, level) AS (
    SELECT employee_id, name, manager_id, 1 FROM employees WHERE manager_id IS NULL
    UNION ALL
    SELECT e.employee_id, e.name, e.manager_id, eh.level + 1
    FROM employees AS e
    JOIN employee_hierarchy AS eh ON e.manager_id = eh.employee_id
)
SELECT * FROM employee_hierarchy

ROWS FROM

q := Select(N("*")).
    From(RowsFrom(
        fn.JsonToRecordset(String(`[{"name":"John","age":30},{"name":"Jane","age":25}]`)).
            ColumnDefinition("name", "TEXT").
            ColumnDefinition("age", "INTEGER"),
        fn.GenerateSeries(Int(1), Int(2)),
    ).WithOrdinality()).
    As("t").ColumnAliases("name", "age", "series_value", "ordinality")

SELECT * FROM ROWS FROM (
    json_to_recordset('[{"name":"John","age":30},{"name":"Jane","age":25}]') AS (name TEXT, age INTEGER),
    generate_series(1, 2)
) WITH ORDINALITY AS t (name, age, series_value, ordinality)

Functions & Operators

String functions

q := Select(
    fn.Upper(N("name")),
    fn.Lower(N("email")),
    fn.Initcap(N("title")),
).From(N("users"))

SELECT upper(name), lower(email), initcap(title) 
FROM users

Date/time functions

q := Select(
    fn.Extract("year", N("created_at")),
    fn.Extract("month", N("created_at")),
    N("created_at").Plus(Interval("1 day")),
).From(N("orders"))

SELECT extract(year from created_at), extract(month from created_at), created_at + INTERVAL '1 day'
FROM orders

Mathematical operators

q := Select(
    N("price").Op("*", N("quantity")).As("total"),
    N("price").Op("*", Float(1.08)).As("price_with_tax"),
).From(N("order_items"))

SELECT price * quantity AS total, price * 1.08 AS price_with_tax 
FROM order_items

CASE expressions

q := Select(
    N("name"),
    Case().
        When(N("salary").Lt(Int(30000)), String("Low")).
        When(N("salary").Lt(Int(70000)), String("Medium")).
        Else(String("High")).
        As("salary_grade"),
).From(N("employees"))

SELECT name, 
       CASE 
           WHEN salary < 30000 THEN 'Low'
           WHEN salary < 70000 THEN 'Medium'
           ELSE 'High'
       END AS salary_grade
FROM employees

Placeholders & Parameters

Named parameters

q := Select(N("*")).
    From(N("users")).
    Where(And(
        N("name").Like(Bind("search_term")),
        N("active").Eq(Bind("is_active")),
    ))

sql, args, err := Build(q).
    WithNamedArgs(map[string]any{
        "search_term": "John%",
        "is_active":   true,
    }).
    ToSQL()

SELECT * FROM users WHERE name LIKE $1 AND active = $2
-- args: ["John%", true]

Positional parameters

q := Select(N("*")).
    From(N("users")).
    Where(And(
        N("name").Like(Arg("John%")),
        N("active").Eq(Arg(true)),
    ))

sql, args, err := Build(q).ToSQL()

SELECT * FROM users WHERE name LIKE $1 AND active = $2
-- args: ["John%", true]

Mixing named and positional parameters

q := Select(N("*")).
    From(N("users")).
    Where(And(
        N("name").Like(Bind("search_term")),
        N("active").Eq(Arg(true)),
    ))

sql, args, err := Build(q).
    WithNamedArgs(map[string]any{
        "search_term": "John%",
    }).
    ToSQL()

SELECT * FROM users WHERE name LIKE $1 AND active = $2
-- args: ["John%", true]

Execution

With pgx

package main

import (
	"github.com/jackc/pgx/v5/pgxpool"
	. "github.com/networkteam/qrb"
	"github.com/networkteam/qrb/qrbpgx"
)

func main() {
	pool, err := pgxpool.New(ctx, os.Getenv("DATABASE_URL"))
	if err != nil {
		log.Fatal(err)
	}

	q := Select(N("name"), N("email")).
		From(N("users")).
		Where(N("active").Eq(Bool(true)))

	rows, err := qrbpgx.Build(q).WithExecutor(pool).Query(ctx)
	if err != nil {
		log.Fatal(err)
	}
	defer rows.Close()

	for rows.Next() {
		var name, email string
		err := rows.Scan(&name, &email)
		if err != nil {
			log.Fatal(err)
		}
		fmt.Printf("Name: %s, Email: %s\n", name, email)
	}
}

With database/sql

package main

import (
	"context"
	"database/sql"
	"fmt"
	"log"
	"os"

	_ "github.com/lib/pq"
	. "github.com/networkteam/qrb"
	"github.com/networkteam/qrb/qrbsql"
)

func main() {
	ctx := context.Background()

	db, err := sql.Open("postgres", os.Getenv("DATABASE_URL"))
	if err != nil {
		log.Fatal(err)
	}

	q := Select(N("name"), N("email")).
		From(N("users")).
		Where(N("active").Eq(Bool(true)))

	rows, err := qrbsql.Build(q).WithExecutor(db).Query(ctx)
	if err != nil {
		log.Fatal(err)
	}
	defer rows.Close()

	for rows.Next() {
		var name, email string
		err := rows.Scan(&name, &email)
		if err != nil {
			log.Fatal(err)
		}
		fmt.Printf("Name: %s, Email: %s\n", name, email)
	}
}

Best Practices

1. Use Type-Safe Column References

// Good - define reusable column references
var (
    UserID    = N("users.id")
    UserName  = N("users.name")
    UserEmail = N("users.email")
)

q := Select(UserName, UserEmail).From(N("users"))

2. Leverage Immutability for Reusable Queries

// Base query that can be reused
baseQuery := Select(N("*")).From(N("users"))

// Create specific variations
activeUsers := baseQuery.Where(N("active").Eq(Bool(true)))
recentUsers := baseQuery.Where(N("created_at").Gt(String("2023-01-01")))

3. Use Named Parameters for Dynamic Queries

q := Select(N("*")).
    From(N("users")).
    Where(N("name").Like(Bind("search")))

// Easy to reuse with different parameters
sql, args, _ := Build(q).WithNamedArgs(map[string]any{
    "search": "John%",
}).ToSQL()

4. Organize Complex Queries with CTEs

// Break complex queries into readable parts
userData := Select(N("id"), N("name")).From(N("users"))
postData := Select(N("user_id"), fn.Count(N("*"))).From(N("posts")).GroupBy(N("user_id"))

q := With("user_data").As(userData).
    With("post_counts").As(postData).
    Select(N("ud.name"), N("pc.count")).
    From(N("user_data")).As("ud").
    LeftJoin(N("post_counts")).As("pc").Using("user_id")

5. Use JSON for Complex Data Structures

// Build hierarchical data efficiently
q := Select(
    fn.JsonBuildObject().
        Prop("user", fn.JsonBuildObject().
            Prop("id", N("u.id")).
            Prop("name", N("u.name"))).
        Prop("posts", fn.JsonAgg(
            fn.JsonBuildObject().
                Prop("id", N("p.id")).
                Prop("title", N("p.title")))),
).From(N("users")).As("u").
LeftJoin(N("posts")).As("p").On(N("u.id").Eq(N("p.user_id"))).
GroupBy(N("u.id"), N("u.name"))

License

MIT

Name		Name	Last commit message	Last commit date
Latest commit History 75 Commits
.github/workflows		.github/workflows
builder		builder
examples		examples
fn		fn
internal/testhelper		internal/testhelper
qrbpgx		qrbpgx
qrbsql		qrbsql
.envrc		.envrc
.gitignore		.gitignore
LICENSE		LICENSE
README.md		README.md
TODO.md		TODO.md
delete_builder_test.go		delete_builder_test.go
devbox.json		devbox.json
devbox.lock		devbox.lock
go.mod		go.mod
go.sum		go.sum
go.work		go.work
go.work.sum		go.work.sum
insert_builder_test.go		insert_builder_test.go
query_builder_test.go		query_builder_test.go
root.go		root.go
select_builder_test.go		select_builder_test.go
update_builder_test.go		update_builder_test.go
window.go		window.go

License

networkteam/qrb

Folders and files

Latest commit

History

Repository files navigation

QRB - PostgreSQL Query Builder

Why QRB?

Installation

Quick Start

Core Concepts

Recommended Import Pattern

Examples

Basic Queries

Simple SELECT

SELECT with WHERE

SELECT with multiple conditions

SELECT DISTINCT

SELECT with ORDER BY and LIMIT

CRUD Operations

INSERT with VALUES

INSERT multiple rows

INSERT with SELECT

INSERT with RETURNING

UPSERT (INSERT with ON CONFLICT)

UPDATE

UPDATE with FROM

DELETE

DELETE with USING

Joins

INNER JOIN

LEFT JOIN

JOIN with USING

Multiple JOINs

Aggregation & Grouping

GROUP BY with aggregate functions

GROUP BY with HAVING

GROUP BY with ROLLUP

GROUP BY with GROUPING SETS

Window Functions

ROW_NUMBER

RANK and DENSE_RANK

Named Windows

JSON Operations

Simple JSON object

JSON with aggregation

Complex nested JSON with CTEs

Array Operations

Array construction

Array functions

UNNEST

Array aggregation

Subqueries

EXISTS

IN with subquery

Correlated subquery

Subquery in FROM

Advanced Features

Common Table Expressions (WITH)

Recursive CTE

ROWS FROM

Functions & Operators

String functions

Date/time functions

Mathematical operators

CASE expressions

Placeholders & Parameters

Named parameters

Positional parameters

Mixing named and positional parameters

Execution

With pgx

With database/sql

Best Practices

1. Use Type-Safe Column References

2. Leverage Immutability for Reusable Queries

3. Use Named Parameters for Dynamic Queries

4. Organize Complex Queries with CTEs

5. Use JSON for Complex Data Structures

License

Packages