skills/golang-bob/query-builder.md

# Bob Query Builder (Layer 1)

The query builder is a fluent, **dialect-specific** SQL builder with no knowledge of your schema
(and therefore no type safety on column names — that comes from generated models, Layer 2). Its
strength: because each dialect is hand-crafted, it can build *any* query that dialect supports.

Everything here uses `psql`; substitute `mysql` or `sqlite` and the matching mod packages. Differences
are listed at the bottom.

## Imports

```go
import (
    "github.com/stephenafamo/bob/dialect/psql"     // Select/Insert/Update/Delete, Raw/RawQuery, and starters
    "github.com/stephenafamo/bob/dialect/psql/sm"  // SELECT mods
    "github.com/stephenafamo/bob/dialect/psql/im"  // INSERT mods
    "github.com/stephenafamo/bob/dialect/psql/um"  // UPDATE mods
    "github.com/stephenafamo/bob/dialect/psql/dm"  // DELETE mods
    "github.com/stephenafamo/bob/dialect/psql/fm"  // function mods (window: fm.Over(...))
    "github.com/stephenafamo/bob/dialect/psql/wm"  // window mods (wm.PartitionBy, wm.OrderBy, wm.BasedOn)
)
```

Paths are **flat** (`dialect/psql/im`, not `dialect/psql/insert/im`).

## Building and running a query

A built query satisfies the `bob.Query` interface (one method, `WriteQuery`). To get the string + args:

```go
type Query interface {
    WriteQuery(ctx context.Context, w io.StringWriter, start int) (args []any, err error)
}
```

Use these on any query object:

- `Build(ctx) (query string, args []any, err error)`
- `BuildN(ctx, start int) (...)` — start arg numbering at `start` (for embedding as a subquery)
- `MustBuild(ctx) (query, args)` / `MustBuildN(ctx, start)` — panic on error (good for one-time init)

```go
ctx := context.Background()
q, args, err := psql.Select(
    sm.Columns("id", "name"),
    sm.From("users"),
    sm.Where(psql.Quote("id").In(psql.Arg(100, 200, 300))),
).Build(ctx)
// q:    SELECT id, name FROM users WHERE (id IN ($1, $2, $3))
// args: [100 200 300]

rows, err := db.QueryContext(ctx, q, args...)   // plain database/sql
```

Or skip the manual step and use the executor (`execution.md`): `bob.All(ctx, exec, query, mapper)`.

## Query mods are the core idea

Each `psql.Select/Insert/Update/Delete` takes a variadic list of **mods**. The mod packages are
distinct per query type, so an INSERT can't take a `FROM` and a SELECT can't take an `INTO` — the
compiler enforces it.

**Conditional building** — `Apply()` adds mods to an existing query (it **mutates in place**):

```go
q := psql.Select(sm.From("projects"))   // SELECT * FROM projects
if !user.IsAdmin {
    q.Apply(sm.Where(psql.Quote("user_id").EQ(psql.Arg(user.ID))))
}
// To reuse a base without mutating it, call q.Clone() first.
```

## Starters (build expressions)

Starter functions live on the dialect package and return a chainable `Expression`. Shared by all dialects:

| Starter | Produces | Example → SQL |
|---------|----------|---------------|
| `Arg(...any)` | bound placeholder(s) | `psql.Arg("a","b")` → `$1, $2` (args a,b) |
| `ArgGroup(...any)` | parenthesized args (tuples) | `psql.ArgGroup("a","b")` → `($1, $2)` |
| `Placeholder(n uint)` | n empty placeholders | `psql.Placeholder(3)` → `$1, $2, $3` (nil args) |
| `Quote(...string)` | quoted identifier | `psql.Quote("t","col")` → `"t"."col"` |
| `S(string)` | single-quoted string literal | `psql.S("hi")` → `'hi'` |
| `F(name, args...)` | function call | `psql.F("count", "*")` → `count(*)` |
| `And(...Expression)` / `Or(...)` | joined with AND/OR | `psql.Or("a","b")` → `a OR b` |
| `Not(Expression)` | `NOT expr` | |
| `Group(...Expression)` | parenthesized, comma-separated | `psql.Group("a","b")` → `(a, b)` |
| `Raw(clause, args...)` | raw SQL fragment, `?` placeholders | `psql.Raw("a = ?", 1)` → `a = $1` |

PSQL adds: `Cast(expr, type)`, `Case()`, `Exists(expr)`, `Concat(...)`, `Any(expr)`, `All(expr)`,
`Minus(expr)`, `TableFunctions(...)`.

## Operators (methods on an Expression)

Chain operators off any expression. Shared across dialects:

`IsNull()` · `IsNotNull()` · `IsDistinctFrom(y)` · `IsNotDistinctFrom(y)` · `EQ(y)` · `NE(y)` ·
`LT(y)` · `LTE(y)` · `GT(y)` · `GTE(y)` · `In(...y)` · `NotIn(...y)` · `Between(y,z)` ·
`NotBetween(y,z)` · `And(y)` · `Or(y)` · `Concat(y)` · `Minus(y)` · `OP(op string, y)` (custom op) ·
`As(alias)` (terminal, for aliasing).

```go
// ($1 >= 50) AND ("name" IS NOT NULL)
psql.Arg("Stephen").GTE(psql.Raw(50)).And(psql.Quote("name").IsNotNull())
// equivalently:
psql.And(psql.Arg("Stephen").GTE(psql.Raw(50)), psql.Quote("name").IsNotNull())
```

## Parameters & quoting — the two footguns

1. **Only `Arg`/`ArgGroup` produce bound parameters.** Anything else (a raw string, a Go int passed
   as a literal) is written into the SQL text. Always wrap user values in `Arg`.

   ```go
   sm.Where(psql.Quote("name").EQ(psql.Arg(userInput)))   // safe: -> "name" = $1
   ```

2. **Bare strings passed to `any`-typed args are emitted verbatim (unquoted).** `sm.From("users")`
   yields `FROM users`. To quote, pass `psql.Quote("users")`. However, several params that are
   *known* to be identifiers auto-quote their `string` arguments:

   | API | Auto-quotes |
   |-----|-------------|
   | `sm/im/um/dm.With(name, cols...)` | CTE name + columns |
   | `um/im.SetCol("c")` | the `SET c =` column |
   | `im.SetExcluded("c")` | `"c" = EXCLUDED."c"` |
   | `im.Into(table, "c1","c2")` | the column list (table is `any` — quote it yourself) |
   | `JoinChain.Using("c1")` | USING columns |
   | `FromChain.As(alias, cols...)` | alias + renamed columns |

   When the left side of a SET is itself an expression (e.g. a qualified column), use `SetExpr`:
   `um.SetExpr(psql.Quote("employees", "id")).ToArg(1)`.

## Raw escape hatches

```go
// Whole query:
psql.RawQuery(`SELECT * FROM users WHERE id = ? AND name = ?`, 100, "Stephen")
// Fragment inside a mod (? placeholders, dialect-rewritten):
sm.Where(psql.Raw("id = ? and name = ?", 100, "Stephen"))
```

## SELECT examples (PSQL)

```go
// DISTINCT ON (PSQL-only)
psql.Select(sm.Columns("id","name"), sm.Distinct("id"), sm.From("users"))
// SELECT DISTINCT ON(id) id, name FROM users

// JOIN ... USING + CTE
psql.Select(
    sm.With("c", "id", "data").As(psql.Select(
        sm.Columns("id"), sm.From("test1"), sm.LeftJoin("test2").Using("id"),
    )),
    sm.From("c"),
)
// WITH c(id, data) AS (SELECT id FROM test1 LEFT JOIN test2 USING (id)) SELECT * FROM c

// CASE (use .Else(...).As(...), or .End().As(...) when there is no ELSE)
sm.Columns(psql.Case().
    When(psql.Quote("id").EQ(psql.S("1")), psql.S("A")).
    Else(psql.S("B")).As("C"))

// Window function: F(...) returns a value you CALL with fm/wm mods
sm.Columns(
    psql.F("LEAD", "created_date", 1, psql.F("NOW"))(
        fm.Over(wm.PartitionBy("presale_id"), wm.OrderBy("created_date")),
    ).Minus(psql.Quote("created_date")).As("difference"),
)
// Named window:
psql.Select(
    sm.Columns(psql.F("avg", "salary")(fm.Over(wm.BasedOn("w")))),
    sm.From("c"),
    sm.Window("w", wm.PartitionBy("depname"), wm.OrderBy("salary")),
)

// Tuple IN
sm.Where(psql.Group(psql.Quote("id"), psql.Quote("employee_id")).
    In(psql.ArgGroup(100, 200), psql.ArgGroup(300, 400)))
// WHERE (id, employee_id) IN (($1, $2), ($3, $4))

// LIMIT/OFFSET as args, FOR UPDATE, subquery FROM, UNION
sm.Limit(psql.Arg(10)); sm.Offset(psql.Arg(15))
sm.ForUpdate("users").SkipLocked()
sm.From(psql.Select(sm.From("clients"), sm.Where(...))).As("c")
sm.Union(psql.Select(sm.Columns("id"), sm.From("admins")))
// For ORDER BY / LIMIT on the whole UNION: sm.OrderCombined("id"), sm.LimitCombined(1000)
```

## INSERT / UPDATE / DELETE examples (PSQL)

```go
// INSERT VALUES (RETURNING is added automatically by the ORM Insert, not here)
psql.Insert(im.Into("films"), im.Values(psql.Arg("UA502","Bananas",105)))
// Multiple rows: repeat im.Values(...). INSERT ... SELECT: im.Query(psql.Select(...)).

// UPSERT
psql.Insert(
    im.IntoAs("distributors", "d", "did", "dname"),
    im.Values(psql.Arg(8, "Anvil")),
    im.OnConflict("did").DoUpdate(
        im.SetExcluded("dname"),
        im.Where(psql.Quote("d","zipcode").NE(psql.S("21201"))),
    ),
)
// im.OnConflict().DoNothing() and im.OnConflictOnConstraint("name").DoUpdate(...) also exist.

// UPDATE
psql.Update(
    um.Table("films"),
    um.SetCol("kind").ToArg("Dramatic"),
    um.Where(psql.Quote("kind").EQ(psql.Arg("Drama"))),
)
// um.From("accounts") (PSQL UPDATE...FROM), um.Set(expr1, expr2) for multiple set clauses.

// DELETE
psql.Delete(dm.From("films"), dm.Where(psql.Quote("kind").EQ(psql.Arg("Drama"))))
// dm.Using("accounts") for PSQL DELETE ... USING.
```

## Cross-dialect differences

**Placeholders** (Bob writes the right one; in `Raw` you always type `?`):

| Dialect | Placeholder |
|---------|-------------|
| PostgreSQL | `$1, $2, …` |
| SQLite | `?1, ?2, …` |
| MySQL | `?` (positional) |

**Identifier quoting:** PSQL & SQLite use `"double quotes"`; MySQL uses `` `backticks` ``. String
literals via `.S()` are `'single-quoted'` everywhere.

**Feature availability:**

| Feature | PSQL | MySQL | SQLite |
|---------|------|-------|--------|
| `RETURNING` | ✅ | ❌ | ✅ |
| `DISTINCT ON(col)` (`sm.Distinct("col")`) | ✅ | ❌ | ❌ |
| `ON CONFLICT DO UPDATE` (`im.OnConflict`) | ✅ | ❌ (`ON DUPLICATE KEY`) | ✅ |
| `FOR UPDATE ... SKIP LOCKED` | ✅ | ✅ | ❌ |
| `UPDATE ... FROM` (`um.From`) / `DELETE ... USING` (`dm.Using`) | ✅ | ❌ | ❌ |
| `MERGE` (`mm` package) | ✅ | ❌ | ❌ |
| `ROWS FROM (...)` (`sm.FromFunction`) | ✅ | ❌ | ❌ |
| CTEs, window functions, `CROSS JOIN` subquery | ✅ | ✅ (MySQL 8+) | ✅ |

- `sm.Distinct()` (no args) = `SELECT DISTINCT` in all dialects; only PSQL accepts column args.
- `psql.Concat(...)` is PSQL-only (→ `a || b`); for MySQL/SQLite use `F("CONCAT", ...)`.
- MySQL upsert: `im.OnDuplicateKeyUpdate(im.UpdateWithValues("col"))`.
- Switching dialects is usually just swapping the imports — mods are named to match across dialects;
  unsupported combinations fail at compile time rather than producing invalid SQL.