Expressions

Expressions are the building blocks of WHERE, HAVING, ORDER BY, and SET clauses. Any value implementing the Expression interface can be used:

type Expression interface {
    Sql() string
    Args() []any
}

Raw expressions

Use dew.Raw() for arbitrary SQL:

// In WHERE
users, err := Users.From(db).
    Where(dew.Raw("age > ? AND name != ?", 18, "Admin")).
    All()

// In SET
err := Users.Update(db).
    Set(Users.Age, dew.Raw("age + ?", 1)).
    Where(Users.ID.Eq(1)).
    Exec()

// Force update/delete all
err := Users.Update(db).
    Set(Users.Name, "Bob").
    Where(dew.Raw("1=1")).
    Exec()

Boolean operators

And

dew.And(
    Users.Age.Gte(18),
    Users.Name.NotEq("Admin"),
)
// (users.age >= $1 AND users.name != $2)

Or

dew.Or(
    Users.Name.Eq("Alice"),
    Users.Name.Eq("Bob"),
)
// (users.name = $1 OR users.name = $2)

Nesting

dew.And(
    Users.Age.Gte(18),
    dew.Or(
        Users.Name.Eq("Alice"),
        Users.Name.Eq("Bob"),
    ),
)
// (users.age >= $1 AND (users.name = $2 OR users.name = $3))

Ordering

dew.Asc(Users.Name)   // users.name ASC
dew.Desc(Users.Age)   // users.age DESC

Used with .OrderBy():

Users.From(db).OrderBy(dew.Desc(Users.Age), dew.Asc(Users.Name))

Aggregate functions

dew.Count()          // COUNT(*)
dew.Count(Users.ID)  // COUNT(users.id)
dew.Sum(Users.Age)   // SUM(users.age)
dew.Avg(Users.Age)   // AVG(users.age)
dew.Max(Users.Age)   // MAX(users.age)
dew.Min(Users.Age)   // MIN(users.age)

Used with .Select():

Users.From(db).
    Select(Users.Name, dew.Count()).
    GroupBy(Users.Name).
    All()

Comparisons on aggregates

Aggregate functions support Eq, NotEq, Gt, Gte, Lt, Lte — useful in Having clauses without falling back to Raw():

Users.From(db).
    Select(Users.Name, dew.Count()).
    GroupBy(Users.Name).
    Having(
        dew.Count().Gt(5),
        dew.Sum(Users.Age).Lte(100),
    ).
    All()
// ... HAVING COUNT(*) > $1 AND SUM(users.age) <= $2

// More examples
dew.Count().Gt(5)              // COUNT(*) > ?
dew.Sum(Users.Age).Gte(100)    // SUM(users.age) >= ?
dew.Avg(Orders.Total).Lt(50.0) // AVG(orders.total) < ?
dew.Max(Users.Age).Eq(99)      // MAX(users.age) = ?
dew.Min(Users.Age).NotEq(0)    // MIN(users.age) != ?

Comparisons accept any because the database determines the aggregate return type — SUM(int) can overflow to bigint, AVG(int) returns numeric, etc.

Aliases

Use dew.As() to alias any expression:

total := dew.As(dew.Sum(Users.Age), "total_age")

Users.From(db).
    Select(Users.Name, total).
    GroupBy(Users.Name).
    All()
// SELECT users.name, SUM(users.age) AS total_age FROM users GROUP BY users.name

Placeholder handling

Dew uses ? as the universal placeholder. Each dialect replaces it automatically:

Dialect	Placeholder
PostgreSQL	`$1, $2, ...`
MySQL	`?, ?, ...`
SQLite	`?, ?, ...`
MSSQL	`@p1, @p2, ...`

Use ?? to escape a literal ? (needed for PostgreSQL JSONB operators like ?, ?|, ?&).

Expressions

On this page