squirrel

package module
v0.6.0 Latest Latest
Warning

This package is not in the latest version of its module.

 Go to latest Published: Apr 7, 2026 License: MIT Imports: 13 Imported by: 0

README

Squirrel - fluent SQL generator for Go

Go Reference

Squirrel is not an ORM.

Squirrel helps you build SQL queries from composable parts:

import sq "github.com/alexZaicev/squirrel"

users := sq.Select("*").From("users").Join("emails USING (email_id)")

active := users.Where(sq.Eq{"deleted_at": nil})

sql, args, err := active.ToSql()

sql == "SELECT * FROM users JOIN emails USING (email_id) WHERE deleted_at IS NULL"

sql, args, err := sq.
Insert("users").Columns("name", "age").
Values("moe", 13).Values("larry", sq.Expr("? + 5", 12)).
ToSql()

sql == "INSERT INTO users (name,age) VALUES (?,?),(?,? + 5)"

Squirrel can also execute queries directly:

stooges := users.Where(sq.Eq{"username": []string{"moe", "larry", "curly", "shemp"}})
three_stooges := stooges.Limit(3)
rows, err := three_stooges.RunWith(db).Query()

// Behaves like:
rows, err := db.Query("SELECT * FROM users WHERE username IN (?,?,?,?) LIMIT ?",
"moe", "larry", "curly", "shemp", 3)

Squirrel makes conditional query building a breeze:

if len(q) > 0 {
users = users.Where("name LIKE ?", fmt.Sprint("%", q, "%"))
}

Squirrel wants to make your life easier:

// StmtCache caches Prepared Stmts for you
dbCache := sq.NewStmtCache(db)

// StatementBuilder keeps your syntax neat
mydb := sq.StatementBuilder.RunWith(dbCache)
select_users := mydb.Select("*").From("users")

Squirrel loves PostgreSQL:

psql := sq.StatementBuilder.PlaceholderFormat(sq.Dollar)

// You use question marks for placeholders...
sql, _, _ := psql.Select("*").From("elephants").Where("name IN (?,?)", "Dumbo", "Verna").ToSql()

/// ...squirrel replaces them using PlaceholderFormat.
sql == "SELECT * FROM elephants WHERE name IN ($1,$2)"


/// You can retrieve id ...
query := sq.Insert("nodes").
Columns("uuid", "type", "data").
Values(node.Uuid, node.Type, node.Data).
Suffix("RETURNING \"id\"").
RunWith(m.db).
PlaceholderFormat(sq.Dollar)

query.QueryRow().Scan(&node.id)

You can escape question marks by inserting two question marks:

SELECT * FROM nodes WHERE meta->'format' ??| array[?,?]

will generate with the Dollar Placeholder:

SELECT * FROM nodes WHERE meta->'format' ?| array[$1,$2]

FAQ

  • How can I build an IN query on composite keys / tuples, e.g. WHERE (col1, col2) IN ((1,2),(3,4))? (#104)

    Squirrel does not explicitly support tuples, but you can get the same effect with e.g.:

    sq.Or{
  sq.Eq{"col1": 1, "col2": 2},
  sq.Eq{"col1": 3, "col2": 4}}

    (col1 = ? AND col2 = ? OR col1 = ? AND col2 = ?)

    with args [1 2 3 4]

    (which should produce the same query plan as the tuple version, since AND has higher precedence than OR in SQL)

  • Why doesn't Eq{"mynumber": []uint8{1,2,3}} turn into an IN query? (#114)

    Values of type []byte are handled specially by database/sql. In Go, byte is just an alias of uint8, so there is no way to distinguish []uint8 from []byte.

  • Some features are poorly documented!

    Hopefully not anymore! See the Feature Reference below. The tests can also be considered a part of the documentation; take a look at those for ideas on how to express more complex queries.

Feature Reference

Statement Builders

Squirrel provides builders for the four main SQL statement types plus CASE expressions and MySQL's REPLACE:

// UPDATE
sql, args, err := sq.Update("users").Set("name", "moe").Set("age", 13).
Where(sq.Eq{"id": 1}).ToSql()
// UPDATE users SET name = ?, age = ? WHERE id = ?

// DELETE
sql, args, err := sq.Delete("users").Where(sq.Eq{"id": 1}).ToSql()
// DELETE FROM users WHERE id = ?

// CASE expression (usable inside SELECT columns, etc.)
sql, args, err := sq.Case("status").
When("1", "'active'").
When("2", "'inactive'").
Else("'unknown'").ToSql()
// CASE status WHEN 1 THEN 'active' WHEN 2 THEN 'inactive' ELSE 'unknown' END

// REPLACE (MySQL-specific; same interface as Insert)
sql, args, err := sq.Replace("users").Columns("name", "age").
Values("moe", 13).ToSql()
// REPLACE INTO users (name,age) VALUES (?,?)
WHERE Expressions

Beyond Eq, Squirrel provides a rich set of expression helpers:

sq.NotEq{"id": 1}          // id <> ?
sq.Lt{"age": 18}            // age < ?
sq.LtOrEq{"age": 18}        // age <= ?
sq.Gt{"age": 18}            // age > ?
sq.GtOrEq{"age": 18}        // age >= ?
sq.Like{"name": "%moe%"}    // name LIKE ?
sq.NotLike{"name": "%moe%"} // name NOT LIKE ?
sq.ILike{"name": "sq%"}     // name ILIKE ?  (PostgreSQL)
sq.NotILike{"name": "sq%"}  // name NOT ILIKE ?

sq.Between{"age": [2]interface{}{18, 65}}       // age BETWEEN ? AND ?
sq.NotBetween{"age": [2]interface{}{18, 65}}    // age NOT BETWEEN ? AND ?

Combine expressions with And / Or / Not:

sq.And{sq.Gt{"age": 18}, sq.Eq{"active": true}}
// (age > ? AND active = ?)

sq.Or{sq.Eq{"col": 1}, sq.Eq{"col": 2}}
// (col = ? OR col = ?)

sq.Not{Cond: sq.Eq{"deleted": true}}
// NOT (deleted = ?)

// Compose Not with And/Or:
sq.And{sq.Eq{"active": true}, sq.Not{Cond: sq.Eq{"banned": true}}}
// (active = ? AND NOT (banned = ?))

Use Exists / NotExists for subquery existence checks:

sub := sq.Select("1").From("orders").Where("orders.user_id = users.id")

sq.Select("*").From("users").Where(sq.Exists(sub))
// SELECT * FROM users WHERE EXISTS (SELECT 1 FROM orders WHERE orders.user_id = users.id)

sq.Select("*").From("users").Where(sq.NotExists(sub))
// SELECT * FROM users WHERE NOT EXISTS (SELECT 1 FROM orders WHERE orders.user_id = users.id)

Use Expr for arbitrary SQL fragments:

sq.Expr("FROM_UNIXTIME(?)", ts)

Use ConcatExpr to build expressions by concatenating strings and other Sqlizer values:

name_expr := sq.Expr("CONCAT(?, ' ', ?)", firstName, lastName)
sq.ConcatExpr("COALESCE(full_name,", name_expr, ")")
Placeholder Formats

Four placeholder formats are built in:

sq.Question // ?           (default — MySQL, SQLite)
sq.Dollar   // $1, $2, ... (PostgreSQL)
sq.Colon    // :1, :2, ... (Oracle)
sq.AtP      // @p1, @p2, ... (SQL Server)

Set a format on any builder or on StatementBuilder:

psql := sq.StatementBuilder.PlaceholderFormat(sq.Dollar)

The Placeholders helper generates a comma-separated list of ? markers:

sq.Placeholders(3) // "?,?,?"
SELECT Clauses

SelectBuilder supports the full range of SELECT clauses:

sq.Select("department", "COUNT(*) as cnt").
Distinct().                          // SELECT DISTINCT ...
From("users").
Join("emails USING (email_id)").     // also LeftJoin, RightJoin, InnerJoin, CrossJoin, FullJoin
Where(sq.Gt{"age": 18}).
GroupBy("department").
Having("COUNT(*) > ?", 5).
OrderBy("cnt DESC").
Limit(10).
Offset(20).
ToSql()
// SELECT DISTINCT department, COUNT(*) as cnt FROM users
//   JOIN emails USING (email_id) WHERE age > ?
//   GROUP BY department HAVING COUNT(*) > ?
//   ORDER BY cnt DESC LIMIT ? OFFSET ?
// args: [18, 5, 10, 20]

Limit and Offset use parameterized placeholders (LIMIT ? / OFFSET ?) rather than formatting values directly into the SQL string. This means the query string is identical regardless of the limit/offset values, enabling prepared-statement caching and reuse:

// Both produce the same SQL: "SELECT * FROM users LIMIT ? OFFSET ?"
// Only the bound args differ.
page1 := sq.Select("*").From("users").Limit(10).Offset(0)
page2 := sq.Select("*").From("users").Limit(10).Offset(10)

FullJoin adds a FULL OUTER JOIN clause:

sq.Select("*").From("a").FullJoin("b ON a.id = b.a_id")
// SELECT * FROM a FULL OUTER JOIN b ON a.id = b.a_id

Use the *JoinUsing convenience methods for the common case where the join condition is a simple column equality (USING clause):

sq.Select("*").From("orders").JoinUsing("customers", "customer_id")
// SELECT * FROM orders JOIN customers USING (customer_id)

sq.Select("*").From("orders").LeftJoinUsing("customers", "customer_id", "region")
// SELECT * FROM orders LEFT JOIN customers USING (customer_id, region)

// All join types have a *JoinUsing variant:
// JoinUsing, LeftJoinUsing, RightJoinUsing, InnerJoinUsing, CrossJoinUsing, FullJoinUsing
Structured Joins with JoinExpr

For more complex joins, JoinExpr provides a structured builder that avoids raw SQL strings. Pass the result to JoinClause:

// Basic ON condition — no raw string concatenation needed
sq.Select("items.name", "users.username").
From("items").
JoinClause(
sq.JoinExpr("users").On("items.fk_user_key = users.key"),
)
// SELECT items.name, users.username FROM items JOIN users ON items.fk_user_key = users.key

Chain multiple .On() calls — they are ANDed together:

sq.Select("items.name", "users.username").
From("items").
JoinClause(
sq.JoinExpr("users").
On("items.fk_user_key = users.key").
On("users.username = ?", "alice"),
)
// ... JOIN users ON items.fk_user_key = users.key AND users.username = ?

Use .OnExpr() to compose with expression helpers like Eq, Gt, Between:

sq.Select("*").From("items").JoinClause(
sq.JoinExpr("prices").
On("items.id = prices.item_id").
OnExpr(sq.Gt{"prices.amount": 100}),
)
// ... JOIN prices ON items.id = prices.item_id AND prices.amount > ?

Set the join type with .Type(), add an alias with .As():

sq.Select("i.name", "u.username").
From("items i").
JoinClause(
sq.JoinExpr("users").Type(sq.JoinLeft).As("u").
On("i.fk_user_key = u.key"),
)
// SELECT i.name, u.username FROM items i LEFT JOIN users u ON i.fk_user_key = u.key

Available join types: sq.JoinInner (default), sq.JoinLeft, sq.JoinRight, sq.JoinFull, sq.JoinCross.

Use .SubQuery() to join against a subquery:

sub := sq.Select("id", "name").From("users").Where(sq.Eq{"active": true})
sq.Select("items.name", "u.name").
From("items").
JoinClause(
sq.JoinExpr("").SubQuery(sub).As("u").
On("items.fk_user_key = u.id"),
)
// ... JOIN (SELECT id, name FROM users WHERE active = ?) u ON items.fk_user_key = u.id

Use .Using() for USING clauses:

sq.Select("*").From("orders").JoinClause(
sq.JoinExpr("customers").Using("customer_id"),
)
// SELECT * FROM orders JOIN customers USING (customer_id)

JoinExpr is fully compatible with the existing string-based join methods — you can mix both styles in the same query.

Remove clauses that were previously set:

base := sq.Select("*").From("users").Limit(10).Offset(20)

// Remove limit and offset for a count query.
// RemoveLimit/RemoveOffset remove the parameterized LIMIT/OFFSET clauses entirely.
countQuery := base.RemoveColumns().RemoveLimit().RemoveOffset().
Column("COUNT(*)")
Subqueries

Use FromSelect to nest a SELECT in the FROM clause:

sub := sq.Select("id").From("other_table").Where(sq.Gt{"age": 18})
sql, args, err := sq.Select("*").FromSelect(sub, "subquery").ToSql()
// SELECT * FROM (SELECT id FROM other_table WHERE age > ?) AS subquery
SELECT ... FROM (VALUES ...) (PostgreSQL)

Use FromValues on a SelectBuilder to select from an inline VALUES list. This is useful for constructing data on-the-fly without a real table:

sq.Select("v.id", "v.name").
    FromValues(
        [][]interface{}{{1, "Alice"}, {2, "Bob"}},
        "v", "id", "name",
    ).
    PlaceholderFormat(sq.Dollar).
    ToSql()
// SELECT v.id, v.name FROM (VALUES ($1, $2), ($3, $4)) AS v(id, name)

This composes naturally with InsertBuilder.Select() for INSERT ... SELECT FROM (VALUES ...) — a powerful PostgreSQL pattern for bulk-inserting with filtering or deduplication:

sq.Insert("employees").
    Columns("id", "name").
    Select(
        sq.Select("v.id", "v.name").
            FromValues(
                [][]interface{}{{1, "Alice"}, {2, "Bob"}},
                "v", "id", "name",
            ).
            Where(sq.NotExists(
                sq.Select("1").From("employees e").Where("e.id = v.id"),
            )),
    ).
    PlaceholderFormat(sq.Dollar).
    ToSql()
// INSERT INTO employees (id,name)
//   SELECT v.id, v.name FROM (VALUES ($1, $2), ($3, $4)) AS v(id, name)
//   WHERE NOT EXISTS (SELECT 1 FROM employees e WHERE e.id = v.id)

Use a SelectBuilder as a value in Eq / NotEq for WHERE ... IN (SELECT ...):

sub := sq.Select("id").From("departments").Where(sq.Eq{"name": "Engineering"})
sql, args, err := sq.Select("*").From("employees").
Where(sq.Eq{"department_id": sub}).ToSql()
// SELECT * FROM employees WHERE department_id IN (SELECT id FROM departments WHERE name = ?)

NotEq produces NOT IN:

blocked := sq.Select("user_id").From("bans")
sq.Select("*").From("users").Where(sq.NotEq{"id": blocked})
// SELECT * FROM users WHERE id NOT IN (SELECT user_id FROM bans)

Comparison operators (Lt, Gt, LtOrEq, GtOrEq) also accept subqueries for scalar comparisons:

avgPrice := sq.Select("AVG(price)").From("products")
sq.Select("name").From("products").Where(sq.Gt{"price": avgPrice})
// SELECT name FROM products WHERE price > (SELECT AVG(price) FROM products)

Subqueries work correctly with all placeholder formats, including Dollar for PostgreSQL — placeholders are numbered sequentially across outer and inner queries.

UNION / INTERSECT / EXCEPT

Combine multiple SELECT queries with set operations:

q1 := sq.Select("name").From("employees")
q2 := sq.Select("name").From("contractors")

sql, args, err := sq.Union(q1, q2).ToSql()
// SELECT name FROM employees UNION SELECT name FROM contractors

sql, args, err = sq.UnionAll(q1, q2).ToSql()
// SELECT name FROM employees UNION ALL SELECT name FROM contractors

sql, args, err = sq.Intersect(q1, q2).ToSql()
// SELECT name FROM employees INTERSECT SELECT name FROM contractors

sql, args, err = sq.Except(q1, q2).ToSql()
// SELECT name FROM employees EXCEPT SELECT name FROM contractors

Chain additional set operations and add ORDER BY, LIMIT, OFFSET (all parameterized):

q3 := sq.Select("name").From("interns")
sql, args, err := sq.Union(q1, q2).Union(q3).OrderBy("name").Limit(10).ToSql()
// ... ORDER BY name LIMIT ?
// args: [..., 10]
Common Table Expressions (CTEs)

Build WITH / WITH RECURSIVE clauses using CteBuilder:

activeSub := sq.Select("id", "name").From("users").Where(sq.Eq{"active": true})
sql, args, err := sq.With("active_users", activeSub).
Statement(sq.Select("*").From("active_users")).ToSql()
// WITH active_users AS (SELECT id, name FROM users WHERE active = ?)
//   SELECT * FROM active_users

Recursive CTEs:

base := sq.Select("id", "parent_id").From("categories").Where(sq.Eq{"parent_id": nil})
recursive := sq.Select("c.id", "c.parent_id").From("categories c").
Join("tree t ON c.parent_id = t.id")

sql, args, err := sq.WithRecursive("tree", sq.Union(base, recursive)).
Statement(sq.Select("*").From("tree")).ToSql()
// WITH RECURSIVE tree AS (
//   SELECT id, parent_id FROM categories WHERE parent_id IS NULL
//   UNION SELECT c.id, c.parent_id FROM categories c JOIN tree t ON c.parent_id = t.id
// ) SELECT * FROM tree

CTEs with explicit column lists:

sq.WithColumns("cte", []string{"x", "y"}, sq.Select("a", "b").From("t1")).
Statement(sq.Select("x", "y").From("cte"))
// WITH cte (x, y) AS (SELECT a, b FROM t1) SELECT x, y FROM cte

The main .Statement() accepts any Sqlizer — SELECT, INSERT, UPDATE, DELETE, UNION, or even another CTE.

Upsert — ON CONFLICT (PostgreSQL) / ON DUPLICATE KEY UPDATE (MySQL)

PostgreSQL — use OnConflictColumns (or OnConflictOnConstraint) with OnConflictDoNothing or OnConflictDoUpdate:

// DO NOTHING
sq.Insert("users").Columns("id", "name").Values(1, "John").
OnConflictColumns("id").OnConflictDoNothing().ToSql()
// INSERT INTO users (id,name) VALUES (?,?) ON CONFLICT (id) DO NOTHING

// DO UPDATE SET
sq.Insert("users").Columns("id", "name").Values(1, "John").
OnConflictColumns("id").
OnConflictDoUpdate("name", sq.Expr("EXCLUDED.name")).ToSql()
// INSERT INTO users (id,name) VALUES (?,?)
//   ON CONFLICT (id) DO UPDATE SET name = EXCLUDED.name

// DO UPDATE with WHERE clause
sq.Insert("users").Columns("id", "name").Values(1, "John").
OnConflictColumns("id").
OnConflictDoUpdate("name", sq.Expr("EXCLUDED.name")).
OnConflictWhere(sq.Eq{"users.active": true}).ToSql()

// Named constraint
sq.Insert("users").Columns("id", "name").Values(1, "John").
OnConflictOnConstraint("users_pkey").OnConflictDoNothing().ToSql()

// Map convenience
sq.Insert("users").Columns("id", "name", "email").Values(1, "John", "j@x.com").
OnConflictColumns("id").
OnConflictDoUpdateMap(map[string]interface{}{
"name":  sq.Expr("EXCLUDED.name"),
"email": sq.Expr("EXCLUDED.email"),
}).ToSql()

MySQL — use OnDuplicateKeyUpdate:

sq.Insert("users").Columns("id", "name").Values(1, "John").
OnDuplicateKeyUpdate("name", sq.Expr("VALUES(name)")).ToSql()
// INSERT INTO users (id,name) VALUES (?,?) ON DUPLICATE KEY UPDATE name = VALUES(name)
RETURNING Clause

PostgreSQL, SQLite (3.35+), and MariaDB support RETURNING. Use the first-class Returning method on InsertBuilder, UpdateBuilder, and DeleteBuilder:

sq.Insert("users").Columns("name").Values("moe").Returning("id").ToSql()
// INSERT INTO users (name) VALUES (?) RETURNING id

sq.Update("users").Set("name", "moe").Where(sq.Eq{"id": 1}).
Returning("id", "name").ToSql()
// UPDATE users SET name = ? WHERE id = ? RETURNING id, name

sq.Delete("users").Where(sq.Eq{"id": 1}).Returning("*").ToSql()
// DELETE FROM users WHERE id = ? RETURNING *

Returning works correctly with ON CONFLICT — the RETURNING clause is emitted after the conflict action:

sq.Insert("users").Columns("id", "name").Values(1, "John").
OnConflictColumns("id").
OnConflictDoUpdate("name", sq.Expr("EXCLUDED.name")).
Returning("id", "name").ToSql()
// INSERT INTO users (id,name) VALUES (?,?)
//   ON CONFLICT (id) DO UPDATE SET name = EXCLUDED.name
//   RETURNING id, name
INSERT ... SELECT

Insert rows from a SELECT query instead of literal values:

sub := sq.Select("name", "age").From("other_users").Where(sq.Gt{"age": 18})
sql, args, err := sq.Insert("users").Columns("name", "age").Select(sub).ToSql()
// INSERT INTO users (name,age) SELECT name, age FROM other_users WHERE age > ?
SetMap

Set columns and values from a map (available on both InsertBuilder and UpdateBuilder):

// Insert
sq.Insert("users").SetMap(map[string]interface{}{
"name": "moe",
"age":  13,
}).ToSql()
// INSERT INTO users (age,name) VALUES (?,?)   -- columns sorted alphabetically

// Update
sq.Update("users").SetMap(map[string]interface{}{
"name": "moe",
"age":  13,
}).Where(sq.Eq{"id": 1}).ToSql()
// UPDATE users SET age = ?, name = ? WHERE id = ?
INSERT Options

Add keywords before the INTO clause (e.g. MySQL's INSERT IGNORE):

sq.Insert("users").Options("IGNORE").Columns("name").Values("moe").ToSql()
// INSERT IGNORE INTO users (name) VALUES (?)
UPDATE ... FROM (PostgreSQL)

Use From or FromSelect on an UpdateBuilder for PostgreSQL-style joins:

sq.Update("users").Set("name", "moe").
From("accounts").
Where("users.account_id = accounts.id").
ToSql()
// UPDATE users SET name = ? FROM accounts WHERE users.account_id = accounts.id
UPDATE ... FROM (VALUES ...) — Bulk Updates (PostgreSQL)

Use FromValues on an UpdateBuilder for PostgreSQL-style bulk updates using inline VALUES lists. This is efficient for updating multiple rows with different values in a single statement:

sq.Update("employees").
    Set("name", sq.Expr("v.name")).
    Set("salary", sq.Expr("v.salary")).
    FromValues(
        [][]interface{}{{1, "Alice", 50000}, {2, "Bob", 60000}},
        "v", "id", "name", "salary",
    ).
    Where("employees.id = v.id").
    PlaceholderFormat(sq.Dollar).
    ToSql()
// UPDATE employees SET name = v.name, salary = v.salary
//   FROM (VALUES ($1, $2, $3), ($4, $5, $6)) AS v(id, name, salary)
//   WHERE employees.id = v.id
// args: [1, "Alice", 50000, 2, "Bob", 60000]

You can also mix regular Set values with expressions referencing the VALUES alias:

sq.Update("t").
    Set("flag", "yes").                   // regular parameterized value
    Set("name", sq.Expr("v.name")).       // reference the VALUES alias
    FromValues(
        [][]interface{}{{1, "Alice"}, {2, "Bob"}},
        "v", "id", "name",
    ).
    Where("t.id = v.id").
    ToSql()
// UPDATE t SET flag = ?, name = v.name
//   FROM (VALUES (?, ?), (?, ?)) AS v(id, name)
//   WHERE t.id = v.id
UPDATE ... JOIN (MySQL)

Use Join, LeftJoin, InnerJoin, etc. on an UpdateBuilder for MySQL-style joins. The join clause is emitted between the table name and SET:

sq.Update("orders").
    Join("customers ON orders.customer_id = customers.id").
    Set("orders.status", "verified").
    Where("customers.verified = ?", true).
    ToSql()
// UPDATE orders JOIN customers ON orders.customer_id = customers.id
//   SET orders.status = ? WHERE customers.verified = ?

// Multiple joins
sq.Update("t1").
    Join("t2 ON t1.id = t2.t1_id").
    LeftJoin("t3 ON t2.id = t3.t2_id AND t3.active = ?", true).
    Set("t1.name", "updated").
    Where("t1.id = ?", 1).
    ToSql()

// Structured JoinExpr works too
sq.Update("orders").
    JoinClause(
        sq.JoinExpr("customers").
            On("orders.customer_id = customers.id").
            On("customers.active = ?", true),
    ).
    Set("orders.status", "verified").
    ToSql()

// JoinUsing convenience
sq.Update("t1").JoinUsing("t2", "id").Set("t1.name", "updated").ToSql()
// UPDATE t1 JOIN t2 USING (id) SET t1.name = ?

All join types are available: Join, LeftJoin, RightJoin, InnerJoin, CrossJoin, FullJoin, and their *JoinUsing variants.

DELETE ... JOIN (MySQL) / DELETE ... USING (PostgreSQL)

MySQL — use Join, LeftJoin, etc. on a DeleteBuilder:

sq.Delete("orders").
    Join("customers ON orders.customer_id = customers.id").
    Where("customers.active = ?", false).
    ToSql()
// DELETE orders FROM orders JOIN customers ON orders.customer_id = customers.id
//   WHERE customers.active = ?

// Structured JoinExpr works too
sq.Delete("orders").
    JoinClause(
        sq.JoinExpr("customers").
            On("orders.customer_id = customers.id").
            On("customers.active = ?", false),
    ).
    ToSql()

PostgreSQL — use Using for DELETE ... USING syntax:

sq.Delete("orders").
    Using("customers").
    Where("orders.customer_id = customers.id AND customers.active = ?", false).
    PlaceholderFormat(sq.Dollar).
    ToSql()
// DELETE FROM orders USING customers
//   WHERE orders.customer_id = customers.id AND customers.active = $1

Multiple USING tables:

sq.Delete("t1").
    Using("t2", "t3").
    Where("t1.id = t2.t1_id AND t2.t3_id = t3.id AND t3.active = ?", true).
    ToSql()
// DELETE FROM t1 USING t2, t3 WHERE ...

All join types are available on DeleteBuilder: Join, LeftJoin, RightJoin, InnerJoin, CrossJoin, FullJoin, their *JoinUsing variants, and JoinClause for structured JoinExpr builders.

Prefix and Suffix

Add arbitrary SQL before or after the main statement:

sq.Select("*").From("users").
Prefix("/* admin query */").
Suffix("FOR UPDATE").
Where(sq.Eq{"id": 1}).ToSql()
// /* admin query */ SELECT * FROM users WHERE id = ? FOR UPDATE
Column Aliasing

Use Alias to wrap complex expressions with an AS alias:

caseExpr := sq.Case().When(sq.Eq{"active": true}, "1").Else("0")
sq.Select("name").Column(sq.Alias(caseExpr, "is_active")).From("users")
MustSql

All builders provide MustSql() which panics on error instead of returning it — useful in tests:

sql, args := sq.Select("*").From("users").MustSql()
Context-Aware Execution

All builders support context-aware variants for query execution:

rows, err := sq.Select("*").From("users").
RunWith(db).
QueryContext(ctx)

result, err := sq.Update("users").Set("name", "moe").
Where(sq.Eq{"id": 1}).
RunWith(db).
ExecContext(ctx)
Debugging

DebugSqlizer inlines arguments into the SQL string for display purposes. Never execute the output — it is not safe against SQL injection:

fmt.Println(sq.DebugSqlizer(
sq.Select("*").From("users").Where(sq.Eq{"name": "moe"}),
))
// SELECT * FROM users WHERE name = 'moe'
Safe Identifiers — Preventing SQL Injection in Table & Column Names

Methods like From(), Into(), Table(), Columns(), Set(), Join(), OrderBy(), and GroupBy() interpolate strings directly into SQL without sanitization. If any of these strings come from user input (e.g., a dynamic sort column from an API query parameter), your application is vulnerable to SQL injection.

Squirrel provides the Ident type and two helper functions to safely handle dynamic identifiers:

QuoteIdent — ANSI SQL double-quote escaping (maximum flexibility):

// Safely quote any user-supplied identifier — even malicious input.
table, err := sq.QuoteIdent(userInput) // e.g. "users" → `"users"`
if err != nil { /* handle error */ }

sql, args, err := sq.Select("*").SafeFrom(table).ToSQL()
// SELECT * FROM "users"

// Injection attempt is safely neutralized:
table, _ := sq.QuoteIdent("users; DROP TABLE users; --")
sq.Select("*").SafeFrom(table).ToSQL()
// SELECT * FROM "users; DROP TABLE users; --"   ← treated as a single identifier

ValidateIdent — strict pattern validation (maximum strictness):

// Only allows letters, digits, underscores, and dots.
// Rejects anything that doesn't look like a simple identifier.
col, err := sq.ValidateIdent(userSortColumn)
if err != nil {
// Reject the request — input contains invalid characters.
return err
}

sql, args, err := sq.Select("*").From("users").SafeOrderByDir(col, sq.Desc).ToSQL()
// SELECT * FROM users ORDER BY user_name DESC

Safe builder methods accept Ident values instead of raw strings:

// SELECT
table, _ := sq.QuoteIdent("users")
cols, _ := sq.QuoteIdents("id", "name", "email")
orderCol, _ := sq.QuoteIdent("name")
groupCol, _ := sq.QuoteIdent("department")

sq.Select().SafeColumns(cols...).SafeFrom(table).
SafeGroupBy(groupCol).
SafeOrderByDir(orderCol, sq.Desc).
ToSQL()
// SELECT "id", "name", "email" FROM "users" GROUP BY "department" ORDER BY "name" DESC

// INSERT
table, _ := sq.QuoteIdent("users")
cols, _ := sq.QuoteIdents("id", "name")
sq.Insert("").SafeInto(table).SafeColumns(cols...).Values(1, "moe").ToSQL()
// INSERT INTO "users" ("id","name") VALUES (?,?)

// UPDATE
table, _ := sq.QuoteIdent("users")
col, _ := sq.QuoteIdent("name")
sq.Update("").SafeTable(table).SafeSet(col, "moe").Where("id = ?", 1).ToSQL()
// UPDATE "users" SET "name" = ? WHERE id = ?

// DELETE
table, _ := sq.QuoteIdent("users")
sq.Delete("").SafeFrom(table).Where("id = ?", 1).ToSQL()
// DELETE FROM "users" WHERE id = ?

Batch helpers quote or validate multiple identifiers at once:

ids, err := sq.QuoteIdents("id", "name", "email")   // quote all
ids, err := sq.ValidateIdents("id", "name", "email") // validate all

Panic variants for use with known-safe literals in application code:

table := sq.MustQuoteIdent("users")       // panics on error
col := sq.MustValidateIdent("created_at") // panics on error

The Ident type also implements Sqlizer, so it can be used anywhere a Sqlizer is accepted.

Summary of Safe methods:

Builder Safe Method Replaces
SelectBuilder SafeFrom(Ident) From(string)
SelectBuilder SafeColumns(...Ident) Columns(...string)
SelectBuilder SafeGroupBy(...Ident) GroupBy(...string)
SelectBuilder SafeOrderBy(...Ident) OrderBy(...string)
SelectBuilder SafeOrderByDir(Ident, OrderDir) OrderBy("col DESC")
InsertBuilder SafeInto(Ident) Into(string)
InsertBuilder SafeColumns(...Ident) Columns(...string)
UpdateBuilder SafeTable(Ident) Table(string)
UpdateBuilder SafeSet(Ident, any) Set(string, any)
DeleteBuilder SafeFrom(Ident) From(string)

License

Squirrel is released under the MIT License.

Documentation

Overview

Package squirrel provides a fluent SQL generator.

See https://github.com/alexZaicev/squirrel for examples.

Index

Examples

Constants

This section is empty.

Variables

View Source 
var (
	// Question is a PlaceholderFormat instance that leaves placeholders as
	// question marks.
	Question = questionFormat{}

	// Dollar is a PlaceholderFormat instance that replaces placeholders with
	// dollar-prefixed positional placeholders (e.g. $1, $2, $3).
	Dollar = dollarFormat{}

	// Colon is a PlaceholderFormat instance that replaces placeholders with
	// colon-prefixed positional placeholders (e.g. :1, :2, :3).
	Colon = colonFormat{}

	// AtP is a PlaceholderFormat instance that replaces placeholders with
	// "@p"-prefixed positional placeholders (e.g. @p1, @p2, @p3).
	AtP = atpFormat{}
)
View Source 
var ErrInvalidIdentifier = fmt.Errorf("invalid SQL identifier")

ErrInvalidIdentifier is returned when a string cannot be used as a safe SQL identifier.

View Source 
var ErrNoContextSupport = errors.New("DB does not support Context")

ErrNoContextSupport is returned if a db doesn't support Context.

View Source 
var ErrRunnerNotQueryRunner = fmt.Errorf("cannot QueryRow; Runner is not a QueryRower")

ErrRunnerNotQueryRunner is returned by QueryRow if the RunWith value doesn't implement QueryRower.

View Source 
var ErrRunnerNotSet = fmt.Errorf("cannot run; no Runner set (RunWith)")

ErrRunnerNotSet is returned by methods that need a Runner if it isn't set.

View Source 
var StatementBuilder = StatementBuilderType(builder.EmptyBuilder).PlaceholderFormat(Question)

StatementBuilder is a parent builder for other builders, e.g. SelectBuilder.

Functions

func DebugSqlizer 

func DebugSqlizer(s Sqlizer) string

DebugSqlizer calls ToSQL on s and shows the approximate SQL to be executed

If ToSQL returns an error, the result of this method will look like: "[ToSQL error: %s]" or "[DebugSqlizer error: %s]"

IMPORTANT: As its name suggests, this function should only be used for debugging. While the string result *might* be valid SQL, this function does not try very hard to ensure it. Additionally, executing the output of this function with any untrusted user input is certainly insecure.

Example 
package main

import (
	"fmt"

	sq "github.com/alexZaicev/squirrel"
)

func main() {
	query := sq.Select("id", "name").
		From("users").
		Where("id = ?", 42)
	fmt.Println(sq.DebugSqlizer(query))
}

Output:
SELECT id, name FROM users WHERE id = '42'

func ExecContextWith 

func ExecContextWith(ctx context.Context, db ExecerContext, s Sqlizer) (res sql.Result, err error)

ExecContextWith ExecContexts the SQL returned by s with db.

func ExecWith 

func ExecWith(db Execer, s Sqlizer) (res sql.Result, err error)

ExecWith Execs the SQL returned by s with db.

func Placeholders 

func Placeholders(count int) string

Placeholders returns a string with count ? placeholders joined with commas.

Example 
package main

import (
	"fmt"

	sq "github.com/alexZaicev/squirrel"
)

func main() {
	p := sq.Placeholders(3)
	fmt.Println(p)
}

Output:
?,?,?

func QueryContextWith 

func QueryContextWith(ctx context.Context, db QueryerContext, s Sqlizer) (rows *sql.Rows, err error)

QueryContextWith QueryContexts the SQL returned by s with db.

func QueryWith 

func QueryWith(db Queryer, s Sqlizer) (rows *sql.Rows, err error)

QueryWith Querys the SQL returned by s with db.

Types

type And 

type And conj

And conjunction Sqlizers

Example 
package main

import (
	"fmt"

	sq "github.com/alexZaicev/squirrel"
)

func main() {
	sql, args, _ := sq.And{
		sq.Eq{"company": 20},
		sq.Gt{"age": 18},
	}.ToSQL()
	fmt.Println(sql)
	fmt.Println(args)
}

Output:
(company = ? AND age > ?)
[20 18]

func (And) ToSQL added in v0.0.2 

func (a And) ToSQL() (string, []any, error)

type BaseRunner 

type BaseRunner interface {
	Execer
	Queryer
}

BaseRunner groups the Execer and Queryer interfaces.

type Between added in v0.1.0 

type Between map[string]any

Between is syntactic sugar for use with Where/Having methods. Values must be two-element arrays: [2]interface{}{lo, hi}.

Ex: 

.Where(Between{"age": [2]interface{}{18, 65}})  // age BETWEEN ? AND ?
Example 
package main

import (
	"fmt"

	sq "github.com/alexZaicev/squirrel"
)

func main() {
	sql, args, _ := sq.Between{"age": [2]interface{}{18, 65}}.ToSQL()
	fmt.Println(sql)
	fmt.Println(args)
}

Output:
age BETWEEN ? AND ?
[18 65]

func (Between) ToSQL added in v0.1.0 

func (b Between) ToSQL() (sql string, args []any, err error)

type CaseBuilder 

type CaseBuilder builder.Builder

CaseBuilder builds SQL CASE construct which could be used as parts of queries.

func Case 

func Case(what ...any) CaseBuilder

Case returns a new CaseBuilder "what" represents case value

Example 
package main

import (
	"fmt"

	sq "github.com/alexZaicev/squirrel"
)

func main() {
	caseStmt := sq.Case("status").
		When("1", "'active'").
		When("2", "'inactive'").
		Else(sq.Expr("?", "unknown"))

	sql, args, _ := sq.Select().
		Column(caseStmt).
		From("users").
		ToSQL()
	fmt.Println(sql)
	fmt.Println(args)
}

Output:
SELECT CASE status WHEN 1 THEN 'active' WHEN 2 THEN 'inactive' ELSE ? END FROM users
[unknown]
Example (Alias) 
package main

import (
	"fmt"

	sq "github.com/alexZaicev/squirrel"
)

func main() {
	caseStmt := sq.Case("status").
		When("1", "'active'").
		When("2", "'inactive'")

	sql, _, _ := sq.Select().
		Column(sq.Alias(caseStmt, "status_text")).
		From("users").
		ToSQL()
	fmt.Println(sql)
}

Output:
SELECT (CASE status WHEN 1 THEN 'active' WHEN 2 THEN 'inactive' END) AS status_text FROM users
Example (Searched) 
package main

import (
	"fmt"

	sq "github.com/alexZaicev/squirrel"
)

func main() {
	// Searched CASE (no value after CASE)
	caseStmt := sq.Case().
		When("age < 18", "'minor'").
		When("age >= 18", "'adult'")

	sql, _, _ := sq.Select().
		Column(caseStmt).
		From("users").
		ToSQL()
	fmt.Println(sql)
}

Output:
SELECT CASE WHEN age < 18 THEN 'minor' WHEN age >= 18 THEN 'adult' END FROM users

func (CaseBuilder) Else 

func (b CaseBuilder) Else(expr any) CaseBuilder

Else sets optional "ELSE ..." part for CASE construct

func (CaseBuilder) MustSQL added in v0.0.2 

func (b CaseBuilder) MustSQL() (string, []any)

MustSQL builds the query into a SQL string and bound args. It panics if there are any errors.

func (CaseBuilder) ToSQL added in v0.0.2 

func (b CaseBuilder) ToSQL() (string, []any, error)

ToSQL builds the query into a SQL string and bound args.

func (CaseBuilder) When 

func (b CaseBuilder) When(when any, then any) CaseBuilder

When adds "WHEN ... THEN ..." part to CASE construct

type CteBuilder added in v0.0.7 

type CteBuilder builder.Builder

CteBuilder builds SQL WITH (Common Table Expression) statements.

func With added in v0.0.7 

func With(name string, as Sqlizer) CteBuilder

With creates a new CteBuilder with a single CTE definition.

Ex: 

With("active_users", Select("id", "name").From("users").Where(Eq{"active": true})).
	Statement(Select("*").From("active_users"))
// WITH active_users AS (SELECT id, name FROM users WHERE active = ?) SELECT * FROM active_users
Example 
package main

import (
	"fmt"

	sq "github.com/alexZaicev/squirrel"
)

func main() {
	sql, args, _ := sq.With("active_users",
		sq.Select("id", "name").From("users").Where(sq.Eq{"active": true}),
	).Statement(
		sq.Select("*").From("active_users"),
	).ToSQL()
	fmt.Println(sql)
	fmt.Println(args)
}

Output:
WITH active_users AS (SELECT id, name FROM users WHERE active = ?) SELECT * FROM active_users
[true]
Example (MultipleCTEs) 
package main

import (
	"fmt"

	sq "github.com/alexZaicev/squirrel"
)

func main() {
	sql, args, _ := sq.With("cte1",
		sq.Select("id").From("t1").Where(sq.Eq{"a": 1}),
	).With("cte2",
		sq.Select("name").From("t2").Where(sq.Eq{"b": 2}),
	).Statement(
		sq.Select("*").From("cte1").Join("cte2 ON cte1.id = cte2.id"),
	).ToSQL()
	fmt.Println(sql)
	fmt.Println(args)
}

Output:
WITH cte1 AS (SELECT id FROM t1 WHERE a = ?), cte2 AS (SELECT name FROM t2 WHERE b = ?) SELECT * FROM cte1 JOIN cte2 ON cte1.id = cte2.id
[1 2]

func WithColumns added in v0.0.7 

func WithColumns(name string, columns []string, as Sqlizer) CteBuilder

WithColumns creates a new CteBuilder with a single CTE definition that has explicit column names.

Ex: 

WithColumns("cte", []string{"x", "y"}, Select("a", "b").From("t1")).
	Statement(Select("x", "y").From("cte"))
// WITH cte (x, y) AS (SELECT a, b FROM t1) SELECT x, y FROM cte
Example 
package main

import (
	"fmt"

	sq "github.com/alexZaicev/squirrel"
)

func main() {
	sql, _, _ := sq.WithColumns("cte", []string{"x", "y"},
		sq.Select("a", "b").From("t1"),
	).Statement(
		sq.Select("x", "y").From("cte"),
	).ToSQL()
	fmt.Println(sql)
}

Output:
WITH cte (x, y) AS (SELECT a, b FROM t1) SELECT x, y FROM cte

func WithRecursive added in v0.0.7 

func WithRecursive(name string, as Sqlizer) CteBuilder

WithRecursive creates a new CteBuilder with a single recursive CTE definition.

Ex: 

WithRecursive("tree",
	Union(
		Select("id", "parent_id").From("categories").Where(Eq{"parent_id": nil}),
		Select("c.id", "c.parent_id").From("categories c").Join("tree t ON c.parent_id = t.id"),
	),
).Statement(Select("*").From("tree"))
// WITH RECURSIVE tree AS (...) SELECT * FROM tree
Example 
package main

import (
	"fmt"

	sq "github.com/alexZaicev/squirrel"
)

func main() {
	sql, args, _ := sq.WithRecursive("numbers",
		sq.Union(
			sq.Select("1 as n"),
			sq.Select("n + 1").From("numbers").Where("n < ?", 10),
		),
	).Statement(
		sq.Select("n").From("numbers"),
	).ToSQL()
	fmt.Println(sql)
	fmt.Println(args)
}

Output:
WITH RECURSIVE numbers AS (SELECT 1 as n UNION SELECT n + 1 FROM numbers WHERE n < ?) SELECT n FROM numbers
[10]

func WithRecursiveColumns added in v0.0.7 

func WithRecursiveColumns(name string, columns []string, as Sqlizer) CteBuilder

WithRecursiveColumns creates a new CteBuilder with a single recursive CTE definition that has explicit column names.

Ex: 

WithRecursiveColumns("cnt", []string{"x"},
	Union(Select("1"), Select("x + 1").From("cnt").Where("x < ?", 100)),
).Statement(Select("x").From("cnt"))
// WITH RECURSIVE cnt (x) AS (...) SELECT x FROM cnt
Example 
package main

import (
	"fmt"

	sq "github.com/alexZaicev/squirrel"
)

func main() {
	sql, args, _ := sq.WithRecursiveColumns("cnt", []string{"x"},
		sq.Union(
			sq.Select("1"),
			sq.Select("x + 1").From("cnt").Where("x < ?", 100),
		),
	).Statement(
		sq.Select("x").From("cnt"),
	).ToSQL()
	fmt.Println(sql)
	fmt.Println(args)
}

Output:
WITH RECURSIVE cnt (x) AS (SELECT 1 UNION SELECT x + 1 FROM cnt WHERE x < ?) SELECT x FROM cnt
[100]

func (CteBuilder) Exec added in v0.0.7 

func (b CteBuilder) Exec() (sql.Result, error)

Exec builds and Execs the query with the Runner set by RunWith.

func (CteBuilder) ExecContext added in v0.0.7 

func (b CteBuilder) ExecContext(ctx context.Context) (sql.Result, error)

ExecContext builds and ExecContexts the query with the Runner set by RunWith.

func (CteBuilder) MustSQL added in v0.0.7 

func (b CteBuilder) MustSQL() (string, []any)

MustSQL builds the query into a SQL string and bound args. It panics if there are any errors.

func (CteBuilder) PlaceholderFormat added in v0.0.7 

func (b CteBuilder) PlaceholderFormat(f PlaceholderFormat) CteBuilder

PlaceholderFormat sets PlaceholderFormat (e.g. Question or Dollar) for the query.

func (CteBuilder) Query added in v0.0.7 

func (b CteBuilder) Query() (*sql.Rows, error)

Query builds and Querys the query with the Runner set by RunWith.

func (CteBuilder) QueryContext added in v0.0.7 

func (b CteBuilder) QueryContext(ctx context.Context) (*sql.Rows, error)

QueryContext builds and QueryContexts the query with the Runner set by RunWith.

func (CteBuilder) QueryRow added in v0.0.7 

func (b CteBuilder) QueryRow() RowScanner

QueryRow builds and QueryRows the query with the Runner set by RunWith.

func (CteBuilder) QueryRowContext added in v0.0.7 

func (b CteBuilder) QueryRowContext(ctx context.Context) RowScanner

QueryRowContext builds and QueryRowContexts the query with the Runner set by RunWith.

func (CteBuilder) RunWith added in v0.0.7 

func (b CteBuilder) RunWith(runner BaseRunner) CteBuilder

RunWith sets a Runner (like database/sql.DB) to be used with e.g. Exec. For most cases runner will be a database connection.

func (CteBuilder) Scan added in v0.0.7 

func (b CteBuilder) Scan(dest ...any) error

Scan is a shortcut for QueryRow().Scan.

func (CteBuilder) ScanContext added in v0.0.7 

func (b CteBuilder) ScanContext(ctx context.Context, dest ...any) error

ScanContext is a shortcut for QueryRowContext().Scan.

func (CteBuilder) Statement added in v0.0.7 

func (b CteBuilder) Statement(stmt Sqlizer) CteBuilder

Statement sets the main SQL statement that follows the WITH clause. The statement can be any Sqlizer (SelectBuilder, InsertBuilder, UpdateBuilder, DeleteBuilder, UnionBuilder, etc.).

Ex: 

With("active", Select("id").From("users").Where(Eq{"active": true})).
	Statement(Select("*").From("active"))

func (CteBuilder) Suffix added in v0.0.7 

func (b CteBuilder) Suffix(sql string, args ...any) CteBuilder

Suffix adds an expression to the end of the query.

func (CteBuilder) SuffixExpr added in v0.0.7 

func (b CteBuilder) SuffixExpr(expr Sqlizer) CteBuilder

SuffixExpr adds an expression to the end of the query.

func (CteBuilder) ToSQL added in v0.0.7 

func (b CteBuilder) ToSQL() (string, []any, error)

ToSQL builds the query into a SQL string and bound args.

func (CteBuilder) With added in v0.0.7 

func (b CteBuilder) With(name string, as Sqlizer) CteBuilder

With adds a CTE definition to the builder.

Ex: 

With("cte", Select("id").From("t1")).
	With("cte2", Select("name").From("t2")).
	Statement(Select("*").From("cte").Join("cte2 ON cte.id = cte2.id"))

func (CteBuilder) WithColumns added in v0.0.7 

func (b CteBuilder) WithColumns(name string, columns []string, as Sqlizer) CteBuilder

WithColumns adds a CTE definition with explicit column names.

Ex: 

WithColumns("cte", []string{"x", "y"}, Select("a", "b").From("t1")).
	Statement(Select("x", "y").From("cte"))
// WITH cte (x, y) AS (SELECT a, b FROM t1) SELECT x, y FROM cte

func (CteBuilder) WithRecursive added in v0.0.7 

func (b CteBuilder) WithRecursive(name string, as Sqlizer) CteBuilder

WithRecursive adds a CTE definition and marks the WITH clause as RECURSIVE. In standard SQL, RECURSIVE is a clause-level keyword — if any CTE is recursive, the entire WITH clause uses WITH RECURSIVE.

Ex: 

WithRecursive("tree",
	Union(
		Select("id", "parent_id").From("categories").Where(Eq{"parent_id": nil}),
		Select("c.id", "c.parent_id").From("categories c").Join("tree t ON c.parent_id = t.id"),
	),
).Statement(Select("*").From("tree"))

func (CteBuilder) WithRecursiveColumns added in v0.0.7 

func (b CteBuilder) WithRecursiveColumns(name string, columns []string, as Sqlizer) CteBuilder

WithRecursiveColumns adds a CTE definition with explicit column names and marks the WITH clause as RECURSIVE.

Ex: 

WithRecursiveColumns("cnt", []string{"x"},
	Union(Select("1"), Select("x + 1").From("cnt").Where("x < ?", 100)),
).Statement(Select("x").From("cnt"))
// WITH RECURSIVE cnt (x) AS (SELECT 1 UNION SELECT x + 1 FROM cnt WHERE x < ?) SELECT x FROM cnt

type DBProxy 

type DBProxy interface {
	Execer
	Queryer
	QueryRower
	Preparer
}

DBProxy groups the Execer, Queryer, QueryRower, and Preparer interfaces.

type DBProxyBeginner 

type DBProxyBeginner interface {
	DBProxy
	Begin() (*sql.Tx, error)
}

func NewStmtCacheProxy 

func NewStmtCacheProxy(db *sql.DB) DBProxyBeginner

type DBProxyContext 

type DBProxyContext interface {
	Execer
	Queryer
	QueryRower
	PreparerContext
}

DBProxyContext groups the Execer, Queryer, QueryRower and PreparerContext interfaces.

func NewStmtCacher 

func NewStmtCacher(prep PreparerContext) DBProxyContext

NewStmtCacher is deprecated

Use NewStmtCache instead

type DeleteBuilder 

type DeleteBuilder builder.Builder

DeleteBuilder builds SQL DELETE statements.

func Delete 

func Delete(from string) DeleteBuilder

Delete returns a new DeleteBuilder with the given table name.

See DeleteBuilder.Table.

Example 
package main

import (
	"fmt"

	sq "github.com/alexZaicev/squirrel"
)

func main() {
	sql, args, _ := sq.Delete("users").
		Where("id = ?", 1).
		ToSQL()
	fmt.Println(sql)
	fmt.Println(args)
}

Output:
DELETE FROM users WHERE id = ?
[1]

func (DeleteBuilder) CrossJoin added in v0.5.0 

func (b DeleteBuilder) CrossJoin(join string, rest ...any) DeleteBuilder

CrossJoin adds a CROSS JOIN clause to the query.

WARNING: The join clause is interpolated directly into the SQL string. NEVER pass unsanitized user input to this method.

func (DeleteBuilder) CrossJoinUsing added in v0.5.0 

func (b DeleteBuilder) CrossJoinUsing(table string, columns ...string) DeleteBuilder

CrossJoinUsing adds a CROSS JOIN ... USING clause to the query.

func (DeleteBuilder) Exec 

func (b DeleteBuilder) Exec() (sql.Result, error)

Exec builds and Execs the query with the Runner set by RunWith.

func (DeleteBuilder) ExecContext 

func (b DeleteBuilder) ExecContext(ctx context.Context) (sql.Result, error)

ExecContext builds and ExecContexts the query with the Runner set by RunWith.

func (DeleteBuilder) From 

func (b DeleteBuilder) From(from string) DeleteBuilder

From sets the table to be deleted from.

WARNING: The table name is interpolated directly into the SQL string without sanitization. NEVER pass unsanitized user input to this method. For dynamic table names from user input, use SafeFrom instead.

func (DeleteBuilder) FullJoin added in v0.5.0 

func (b DeleteBuilder) FullJoin(join string, rest ...any) DeleteBuilder

FullJoin adds a FULL OUTER JOIN clause to the query.

WARNING: The join clause is interpolated directly into the SQL string. NEVER pass unsanitized user input to this method.

func (DeleteBuilder) FullJoinUsing added in v0.5.0 

func (b DeleteBuilder) FullJoinUsing(table string, columns ...string) DeleteBuilder

FullJoinUsing adds a FULL OUTER JOIN ... USING clause to the query.

func (DeleteBuilder) InnerJoin added in v0.5.0 

func (b DeleteBuilder) InnerJoin(join string, rest ...any) DeleteBuilder

InnerJoin adds an INNER JOIN clause to the query.

WARNING: The join clause is interpolated directly into the SQL string. NEVER pass unsanitized user input to this method.

func (DeleteBuilder) InnerJoinUsing added in v0.5.0 

func (b DeleteBuilder) InnerJoinUsing(table string, columns ...string) DeleteBuilder

InnerJoinUsing adds an INNER JOIN ... USING clause to the query.

func (DeleteBuilder) Join added in v0.5.0 

func (b DeleteBuilder) Join(join string, rest ...any) DeleteBuilder

Join adds a JOIN clause to the query.

WARNING: The join clause is interpolated directly into the SQL string. NEVER pass unsanitized user input to this method.

Example 
package main

import (
	"fmt"

	sq "github.com/alexZaicev/squirrel"
)

func main() {
	sql, args, _ := sq.Delete("orders").
		Join("customers ON orders.customer_id = customers.id").
		Where("customers.active = ?", false).
		ToSQL()
	fmt.Println(sql)
	fmt.Println(args)
}

Output:
DELETE orders FROM orders JOIN customers ON orders.customer_id = customers.id WHERE customers.active = ?
[false]

func (DeleteBuilder) JoinClause added in v0.5.0 

func (b DeleteBuilder) JoinClause(pred any, args ...any) DeleteBuilder

JoinClause adds a join clause to the query.

Example 
package main

import (
	"fmt"

	sq "github.com/alexZaicev/squirrel"
)

func main() {
	sql, args, _ := sq.Delete("orders").
		JoinClause(
			sq.JoinExpr("customers").
				On("orders.customer_id = customers.id").
				On("customers.active = ?", false),
		).
		ToSQL()
	fmt.Println(sql)
	fmt.Println(args)
}

Output:
DELETE orders FROM orders JOIN customers ON orders.customer_id = customers.id AND customers.active = ?
[false]

func (DeleteBuilder) JoinUsing added in v0.5.0 

func (b DeleteBuilder) JoinUsing(table string, columns ...string) DeleteBuilder

JoinUsing adds a JOIN ... USING clause to the query.

func (DeleteBuilder) LeftJoin added in v0.5.0 

func (b DeleteBuilder) LeftJoin(join string, rest ...any) DeleteBuilder

LeftJoin adds a LEFT JOIN clause to the query.

WARNING: The join clause is interpolated directly into the SQL string. NEVER pass unsanitized user input to this method.

func (DeleteBuilder) LeftJoinUsing added in v0.5.0 

func (b DeleteBuilder) LeftJoinUsing(table string, columns ...string) DeleteBuilder

LeftJoinUsing adds a LEFT JOIN ... USING clause to the query.

func (DeleteBuilder) Limit 

func (b DeleteBuilder) Limit(limit uint64) DeleteBuilder

Limit sets a LIMIT clause on the query.

func (DeleteBuilder) MustSQL added in v0.0.2 

func (b DeleteBuilder) MustSQL() (string, []any)

MustSQL builds the query into a SQL string and bound args. It panics if there are any errors.

func (DeleteBuilder) Offset 

func (b DeleteBuilder) Offset(offset uint64) DeleteBuilder

Offset sets a OFFSET clause on the query.

func (DeleteBuilder) OrderBy 

func (b DeleteBuilder) OrderBy(orderBys ...string) DeleteBuilder

OrderBy adds ORDER BY expressions to the query.

WARNING: Order-by expressions are interpolated directly into the SQL string. NEVER pass unsanitized user input to this method.

Example 
package main

import (
	"fmt"

	sq "github.com/alexZaicev/squirrel"
)

func main() {
	sql, args, _ := sq.Delete("logs").
		Where("created < ?", "2024-01-01").
		OrderBy("created").
		Limit(1000).
		ToSQL()
	fmt.Println(sql)
	fmt.Println(args)
}

Output:
DELETE FROM logs WHERE created < ? ORDER BY created LIMIT ?
[2024-01-01 1000]

func (DeleteBuilder) PlaceholderFormat 

func (b DeleteBuilder) PlaceholderFormat(f PlaceholderFormat) DeleteBuilder

PlaceholderFormat sets PlaceholderFormat (e.g. Question or Dollar) for the query.

func (DeleteBuilder) Prefix 

func (b DeleteBuilder) Prefix(sql string, args ...any) DeleteBuilder

Prefix adds an expression to the beginning of the query

func (DeleteBuilder) PrefixExpr 

func (b DeleteBuilder) PrefixExpr(expr Sqlizer) DeleteBuilder

PrefixExpr adds an expression to the very beginning of the query

func (DeleteBuilder) Query 

func (b DeleteBuilder) Query() (*sql.Rows, error)

func (DeleteBuilder) QueryContext 

func (b DeleteBuilder) QueryContext(ctx context.Context) (*sql.Rows, error)

QueryContext builds and QueryContexts the query with the Runner set by RunWith.

func (DeleteBuilder) QueryRowContext 

func (b DeleteBuilder) QueryRowContext(ctx context.Context) RowScanner

QueryRowContext builds and QueryRowContexts the query with the Runner set by RunWith.

func (DeleteBuilder) Returning added in v0.0.6 

func (b DeleteBuilder) Returning(columns ...string) DeleteBuilder

Returning adds RETURNING expressions to the query.

Ex: 

Delete("users").Where("id = ?", 1).
	Returning("id", "name")
// DELETE FROM users WHERE id = ? RETURNING id, name
Example 
package main

import (
	"fmt"

	sq "github.com/alexZaicev/squirrel"
)

func main() {
	sql, args, _ := sq.Delete("users").
		Where("active = ?", false).
		Returning("id", "name").
		PlaceholderFormat(sq.Dollar).
		ToSQL()
	fmt.Println(sql)
	fmt.Println(args)
}

Output:
DELETE FROM users WHERE active = $1 RETURNING id, name
[false]

func (DeleteBuilder) RightJoin added in v0.5.0 

func (b DeleteBuilder) RightJoin(join string, rest ...any) DeleteBuilder

RightJoin adds a RIGHT JOIN clause to the query.

WARNING: The join clause is interpolated directly into the SQL string. NEVER pass unsanitized user input to this method.

func (DeleteBuilder) RightJoinUsing added in v0.5.0 

func (b DeleteBuilder) RightJoinUsing(table string, columns ...string) DeleteBuilder

RightJoinUsing adds a RIGHT JOIN ... USING clause to the query.

func (DeleteBuilder) RunWith 

func (b DeleteBuilder) RunWith(runner BaseRunner) DeleteBuilder

RunWith sets a Runner (like database/sql.DB) to be used with e.g. Exec.

func (DeleteBuilder) SafeFrom added in v0.4.0 

func (b DeleteBuilder) SafeFrom(from Ident) DeleteBuilder

SafeFrom sets the table to be deleted from using a safe Ident.

Ex: 

id, _ := sq.QuoteIdent(userInput)
sq.Delete("").SafeFrom(id).Where("id = ?", 1)
Example 
package main

import (
	"fmt"

	sq "github.com/alexZaicev/squirrel"
)

func main() {
	table, _ := sq.QuoteIdent("users")
	sql, args, _ := sq.Delete("").SafeFrom(table).Where("id = ?", 1).ToSQL()
	fmt.Println(sql)
	fmt.Println(args)
}

Output:
DELETE FROM "users" WHERE id = ?
[1]

func (DeleteBuilder) ScanContext 

func (b DeleteBuilder) ScanContext(ctx context.Context, dest ...any) error

ScanContext is a shortcut for QueryRowContext().Scan.

func (DeleteBuilder) Suffix 

func (b DeleteBuilder) Suffix(sql string, args ...any) DeleteBuilder

Suffix adds an expression to the end of the query

func (DeleteBuilder) SuffixExpr 

func (b DeleteBuilder) SuffixExpr(expr Sqlizer) DeleteBuilder

SuffixExpr adds an expression to the end of the query

func (DeleteBuilder) ToSQL added in v0.0.2 

func (b DeleteBuilder) ToSQL() (string, []any, error)

ToSQL builds the query into a SQL string and bound args.

func (DeleteBuilder) Using added in v0.5.0 

func (b DeleteBuilder) Using(tables ...string) DeleteBuilder

Using adds a USING clause to the query (PostgreSQL).

PostgreSQL DELETE ... USING allows referencing additional tables in the WHERE clause: 

Delete("t1").Using("t2").Where("t1.id = t2.t1_id AND t2.active = ?", false)
// DELETE FROM t1 USING t2 WHERE t1.id = t2.t1_id AND t2.active = ?

WARNING: The table name is interpolated directly into the SQL string. NEVER pass unsanitized user input to this method.

Example 
package main

import (
	"fmt"

	sq "github.com/alexZaicev/squirrel"
)

func main() {
	sql, args, _ := sq.Delete("orders").
		Using("customers").
		Where("orders.customer_id = customers.id AND customers.active = ?", false).
		PlaceholderFormat(sq.Dollar).
		ToSQL()
	fmt.Println(sql)
	fmt.Println(args)
}

Output:
DELETE FROM orders USING customers WHERE orders.customer_id = customers.id AND customers.active = $1
[false]

func (DeleteBuilder) Where 

func (b DeleteBuilder) Where(pred any, args ...any) DeleteBuilder

Where adds WHERE expressions to the query.

See SelectBuilder.Where for more information.

type Eq 

type Eq map[string]any

Eq is syntactic sugar for use with Where/Having/Set methods.

Example 
package main

import (
	"fmt"

	sq "github.com/alexZaicev/squirrel"
)

func main() {
	sql, args, _ := sq.Eq{"company": 20}.ToSQL()
	fmt.Println(sql)
	fmt.Println(args)
}

Output:
company = ?
[20]
Example (In) 
package main

import (
	"fmt"

	sq "github.com/alexZaicev/squirrel"
)

func main() {
	sql, args, _ := sq.Eq{"status": []string{"active", "pending"}}.ToSQL()
	fmt.Println(sql)
	fmt.Println(args)
}

Output:
status IN (?,?)
[active pending]
Example (Null) 
package main

import (
	"fmt"

	sq "github.com/alexZaicev/squirrel"
)

func main() {
	sql, _, _ := sq.Eq{"deleted_at": nil}.ToSQL()
	fmt.Println(sql)
}

Output:
deleted_at IS NULL
Example (Subquery) 
package main

import (
	"fmt"

	sq "github.com/alexZaicev/squirrel"
)

func main() {
	subQ := sq.Select("id").From("other_table").Where(sq.Eq{"active": true})
	sql, args, _ := sq.Eq{"id": subQ}.ToSQL()
	fmt.Println(sql)
	fmt.Println(args)
}

Output:
id IN (SELECT id FROM other_table WHERE active = ?)
[true]

func (Eq) ToSQL added in v0.0.2 

func (eq Eq) ToSQL() (sql string, args []any, err error)

type Execer 

type Execer interface {
	Exec(query string, args ...any) (sql.Result, error)
}

Execer is the interface that wraps the Exec method.

Exec executes the given query as implemented by database/sql.Exec.

type ExecerContext 

type ExecerContext interface {
	ExecContext(ctx context.Context, query string, args ...any) (sql.Result, error)
}

ExecerContext is the interface that wraps the ExecContext method.

Exec executes the given query as implemented by database/sql.ExecContext.

type Gt 

type Gt Lt

Gt is syntactic sugar for use with Where/Having/Set methods. Ex: 

.Where(Gt{"id": 1}) == "id > 1"
Example 
package main

import (
	"fmt"

	sq "github.com/alexZaicev/squirrel"
)

func main() {
	sql, args, _ := sq.Gt{"age": 65}.ToSQL()
	fmt.Println(sql)
	fmt.Println(args)
}

Output:
age > ?
[65]

func (Gt) ToSQL added in v0.0.2 

func (gt Gt) ToSQL() (sql string, args []any, err error)

type GtOrEq 

type GtOrEq Lt

GtOrEq is syntactic sugar for use with Where/Having/Set methods. Ex: 

.Where(GtOrEq{"id": 1}) == "id >= 1"
Example 
package main

import (
	"fmt"

	sq "github.com/alexZaicev/squirrel"
)

func main() {
	sql, args, _ := sq.GtOrEq{"age": 18}.ToSQL()
	fmt.Println(sql)
	fmt.Println(args)
}

Output:
age >= ?
[18]

func (GtOrEq) ToSQL added in v0.0.2 

func (gtOrEq GtOrEq) ToSQL() (sql string, args []any, err error)

type ILike 

type ILike Like

ILike is syntactic sugar for use with ILIKE conditions. Ex: 

.Where(ILike{"name": "sq%"})
Example 
package main

import (
	"fmt"

	sq "github.com/alexZaicev/squirrel"
)

func main() {
	sql, args, _ := sq.ILike{"name": "sq%"}.ToSQL()
	fmt.Println(sql)
	fmt.Println(args)
}

Output:
name ILIKE ?
[sq%]

func (ILike) ToSQL added in v0.0.2 

func (ilk ILike) ToSQL() (sql string, args []any, err error)

type Ident added in v0.4.0 

type Ident struct {
	// contains filtered or unexported fields
}

Ident represents a validated SQL identifier (table name, column name, etc.) that is safe for interpolation into SQL strings.

An Ident is created by QuoteIdent or ValidateIdent, which ensure the value is safe to use in SQL. This type is used by the Safe* builder methods to prevent SQL injection via dynamic identifiers.

Ident implements the Sqlizer interface, so it can be used anywhere a Sqlizer is accepted.

func MustQuoteIdent added in v0.4.0 

func MustQuoteIdent(name string) Ident

MustQuoteIdent is like QuoteIdent but panics on error.

func MustValidateIdent added in v0.4.0 

func MustValidateIdent(name string) Ident

MustValidateIdent is like ValidateIdent but panics on error.

func QuoteIdent added in v0.4.0 

func QuoteIdent(name string) (Ident, error)

QuoteIdent produces a safely-quoted SQL identifier using ANSI SQL double-quoting. Any embedded double-quote characters are escaped by doubling them. This is the most permissive way to produce safe identifiers — it allows any string, including those with spaces, reserved words, and special characters, by wrapping the entire identifier in double quotes.

For schema-qualified identifiers (e.g. "public.users"), each part is quoted separately: "public"."users".

WARNING: Empty strings are rejected and will cause QuoteIdent to return an error.

Ex: 

id, err := sq.QuoteIdent("users")        // "users"
id, err := sq.QuoteIdent("my table")     // "my table"
id, err := sq.QuoteIdent("public.users") // "public"."users"
id, err := sq.QuoteIdent(`Robert"; DROP TABLE users; --`)
  // "Robert""; DROP TABLE users; --"
Example 
package main

import (
	"fmt"

	sq "github.com/alexZaicev/squirrel"
)

func main() {
	id, _ := sq.QuoteIdent("users")
	fmt.Println(id.String())
	fmt.Println(id.Raw())
}

Output:
"users"
users
Example (InjectionSafe) 
package main

import (
	"fmt"

	sq "github.com/alexZaicev/squirrel"
)

func main() {
	// Even malicious input is safely wrapped in quotes.
	id, _ := sq.QuoteIdent("users; DROP TABLE users; --")
	fmt.Println(id.String())
}

Output:
"users; DROP TABLE users; --"
Example (SchemaQualified) 
package main

import (
	"fmt"

	sq "github.com/alexZaicev/squirrel"
)

func main() {
	id, _ := sq.QuoteIdent("public.users")
	fmt.Println(id.String())
}

Output:
"public"."users"

func QuoteIdents added in v0.4.0 

func QuoteIdents(names ...string) ([]Ident, error)

QuoteIdents quotes each name with QuoteIdent and returns all results. It returns an error on the first invalid identifier.

func ValidateIdent added in v0.4.0 

func ValidateIdent(name string) (Ident, error)

ValidateIdent checks that name matches a strict identifier pattern (letters, digits, underscores; optionally dot-separated for schema-qualified names) and returns an Ident WITHOUT adding double quotes. This is useful when double-quoting is undesirable (e.g. case-sensitive databases where quoting changes behaviour) but you still want to reject obviously dangerous input.

The validation pattern is: ^[A-Za-z_][A-Za-z0-9_]*(\.[A-Za-z_][A-Za-z0-9_]*)*$

Ex: 

id, err := sq.ValidateIdent("users")         // OK → users
id, err := sq.ValidateIdent("public.users")  // OK → public.users
id, err := sq.ValidateIdent("users; DROP")   // ERROR — invalid
id, err := sq.ValidateIdent("")               // ERROR — empty
Example 
package main

import (
	"fmt"

	sq "github.com/alexZaicev/squirrel"
)

func main() {
	id, err := sq.ValidateIdent("users")
	if err != nil {
		panic(err)
	}
	fmt.Println(id.String())
}

Output:
users
Example (Rejected) 
package main

import (
	"fmt"

	sq "github.com/alexZaicev/squirrel"
)

func main() {
	_, err := sq.ValidateIdent("users; DROP TABLE users; --")
	fmt.Println(err)
}

Output:
invalid SQL identifier: "users; DROP TABLE users; --" contains invalid characters

func ValidateIdents added in v0.4.0 

func ValidateIdents(names ...string) ([]Ident, error)

ValidateIdents validates each name with ValidateIdent and returns all results. It returns an error on the first invalid identifier.

func (Ident) Raw added in v0.4.0 

func (id Ident) Raw() string

Raw returns the original unquoted identifier string.

func (Ident) String added in v0.4.0 

func (id Ident) String() string

String returns the quoted form of the identifier.

func (Ident) ToSQL added in v0.4.0 

func (id Ident) ToSQL() (string, []any, error)

ToSQL implements the Sqlizer interface. It returns the quoted identifier.

type InsertBuilder 

type InsertBuilder builder.Builder

InsertBuilder builds SQL INSERT statements.

func Insert 

func Insert(into string) InsertBuilder

Insert returns a new InsertBuilder with the given table name.

See InsertBuilder.Into.

Example 
package main

import (
	"fmt"

	sq "github.com/alexZaicev/squirrel"
)

func main() {
	sql, args, _ := sq.Insert("users").
		Columns("name", "age").
		Values("Alice", 30).
		ToSQL()
	fmt.Println(sql)
	fmt.Println(args)
}

Output:
INSERT INTO users (name,age) VALUES (?,?)
[Alice 30]

func Replace 

func Replace(into string) InsertBuilder

Replace returns a new InsertBuilder with the statement keyword set to "REPLACE" and with the given table name.

See InsertBuilder.Into.

Example 
package main

import (
	"fmt"

	sq "github.com/alexZaicev/squirrel"
)

func main() {
	sql, args, _ := sq.Replace("users").
		Columns("name").
		Values("Alice").
		ToSQL()
	fmt.Println(sql)
	fmt.Println(args)
}

Output:
REPLACE INTO users (name) VALUES (?)
[Alice]

func (InsertBuilder) Columns 

func (b InsertBuilder) Columns(columns ...string) InsertBuilder

Columns adds insert columns to the query.

WARNING: Column names are interpolated directly into the SQL string without sanitization. NEVER pass unsanitized user input to this method. For dynamic column names from user input, use SafeColumns instead.

func (InsertBuilder) Exec 

func (b InsertBuilder) Exec() (sql.Result, error)

Exec builds and Execs the query with the Runner set by RunWith.

func (InsertBuilder) ExecContext 

func (b InsertBuilder) ExecContext(ctx context.Context) (sql.Result, error)

ExecContext builds and ExecContexts the query with the Runner set by RunWith.

func (InsertBuilder) Into 

func (b InsertBuilder) Into(into string) InsertBuilder

Into sets the INTO clause of the query.

WARNING: The table name is interpolated directly into the SQL string without sanitization. NEVER pass unsanitized user input to this method. For dynamic table names from user input, use SafeInto instead.

func (InsertBuilder) MustSQL added in v0.0.2 

func (b InsertBuilder) MustSQL() (string, []any)

MustSQL builds the query into a SQL string and bound args. It panics if there are any errors.

func (InsertBuilder) OnConflictColumns added in v0.0.5 

func (b InsertBuilder) OnConflictColumns(columns ...string) InsertBuilder

OnConflictColumns sets the conflict target columns for a PostgreSQL ON CONFLICT clause. Use with OnConflictDoNothing or OnConflictDoUpdate.

Ex: 

Insert("users").Columns("id", "name").Values(1, "John").
	OnConflictColumns("id").OnConflictDoNothing()
// INSERT INTO users (id,name) VALUES (?,?) ON CONFLICT (id) DO NOTHING

func (InsertBuilder) OnConflictDoNothing added in v0.0.5 

func (b InsertBuilder) OnConflictDoNothing() InsertBuilder

OnConflictDoNothing sets the conflict action to DO NOTHING for a PostgreSQL ON CONFLICT clause.

Ex: 

Insert("users").Columns("id", "name").Values(1, "John").
	OnConflictColumns("id").OnConflictDoNothing()
// INSERT INTO users (id,name) VALUES (?,?) ON CONFLICT (id) DO NOTHING
Example 
package main

import (
	"fmt"

	sq "github.com/alexZaicev/squirrel"
)

func main() {
	sql, args, _ := sq.Insert("users").
		Columns("id", "name").
		Values(1, "Alice").
		OnConflictColumns("id").
		OnConflictDoNothing().
		PlaceholderFormat(sq.Dollar).
		ToSQL()
	fmt.Println(sql)
	fmt.Println(args)
}

Output:
INSERT INTO users (id,name) VALUES ($1,$2) ON CONFLICT (id) DO NOTHING
[1 Alice]

func (InsertBuilder) OnConflictDoUpdate added in v0.0.5 

func (b InsertBuilder) OnConflictDoUpdate(column string, value any) InsertBuilder

OnConflictDoUpdate adds a column = value SET clause to the DO UPDATE action for a PostgreSQL ON CONFLICT clause. The value can be a Sqlizer (e.g. Expr) for expressions like EXCLUDED.column.

Ex: 

Insert("users").Columns("id", "name").Values(1, "John").
	OnConflictColumns("id").
	OnConflictDoUpdate("name", sq.Expr("EXCLUDED.name"))
// INSERT INTO users (id,name) VALUES (?,?) ON CONFLICT (id) DO UPDATE SET name = EXCLUDED.name
Example 
package main

import (
	"fmt"

	sq "github.com/alexZaicev/squirrel"
)

func main() {
	sql, args, _ := sq.Insert("users").
		Columns("id", "name").
		Values(1, "Alice").
		OnConflictColumns("id").
		OnConflictDoUpdate("name", sq.Expr("EXCLUDED.name")).
		PlaceholderFormat(sq.Dollar).
		ToSQL()
	fmt.Println(sql)
	fmt.Println(args)
}

Output:
INSERT INTO users (id,name) VALUES ($1,$2) ON CONFLICT (id) DO UPDATE SET name = EXCLUDED.name
[1 Alice]

func (InsertBuilder) OnConflictDoUpdateMap added in v0.0.5 

func (b InsertBuilder) OnConflictDoUpdateMap(clauses map[string]any) InsertBuilder

OnConflictDoUpdateMap is a convenience method that calls OnConflictDoUpdate for each key/value pair in clauses.

func (InsertBuilder) OnConflictOnConstraint added in v0.0.5 

func (b InsertBuilder) OnConflictOnConstraint(name string) InsertBuilder

OnConflictOnConstraint sets the conflict target to a named constraint for a PostgreSQL ON CONFLICT ON CONSTRAINT clause.

Ex: 

Insert("users").Columns("id", "name").Values(1, "John").
	OnConflictOnConstraint("users_pkey").OnConflictDoNothing()
// INSERT INTO users (id,name) VALUES (?,?) ON CONFLICT ON CONSTRAINT users_pkey DO NOTHING
Example 
package main

import (
	"fmt"

	sq "github.com/alexZaicev/squirrel"
)

func main() {
	sql, args, _ := sq.Insert("users").
		Columns("id", "name").
		Values(1, "Alice").
		OnConflictOnConstraint("users_pkey").
		OnConflictDoNothing().
		PlaceholderFormat(sq.Dollar).
		ToSQL()
	fmt.Println(sql)
	fmt.Println(args)
}

Output:
INSERT INTO users (id,name) VALUES ($1,$2) ON CONFLICT ON CONSTRAINT users_pkey DO NOTHING
[1 Alice]

func (InsertBuilder) OnConflictWhere added in v0.0.5 

func (b InsertBuilder) OnConflictWhere(pred any, args ...any) InsertBuilder

OnConflictWhere adds a WHERE clause to the DO UPDATE action of a PostgreSQL ON CONFLICT clause.

Ex: 

Insert("users").Columns("id", "name").Values(1, "John").
	OnConflictColumns("id").
	OnConflictDoUpdate("name", sq.Expr("EXCLUDED.name")).
	OnConflictWhere(sq.Eq{"users.active": true})
Example 
package main

import (
	"fmt"

	sq "github.com/alexZaicev/squirrel"
)

func main() {
	sql, args, _ := sq.Insert("users").
		Columns("id", "name").
		Values(1, "Alice").
		OnConflictColumns("id").
		OnConflictDoUpdate("name", sq.Expr("EXCLUDED.name")).
		OnConflictWhere(sq.Eq{"users.active": true}).
		PlaceholderFormat(sq.Dollar).
		ToSQL()
	fmt.Println(sql)
	fmt.Println(args)
}

Output:
INSERT INTO users (id,name) VALUES ($1,$2) ON CONFLICT (id) DO UPDATE SET name = EXCLUDED.name WHERE users.active = $3
[1 Alice true]

func (InsertBuilder) OnDuplicateKeyUpdate added in v0.0.5 

func (b InsertBuilder) OnDuplicateKeyUpdate(column string, value any) InsertBuilder

OnDuplicateKeyUpdate adds a column = value clause to a MySQL ON DUPLICATE KEY UPDATE clause.

Ex: 

Insert("users").Columns("id", "name").Values(1, "John").
	OnDuplicateKeyUpdate("name", "John")
// INSERT INTO users (id,name) VALUES (?,?) ON DUPLICATE KEY UPDATE name = ?
Example 
package main

import (
	"fmt"

	sq "github.com/alexZaicev/squirrel"
)

func main() {
	sql, args, _ := sq.Insert("users").
		Columns("id", "name").
		Values(1, "Alice").
		OnDuplicateKeyUpdate("name", "Alice").
		ToSQL()
	fmt.Println(sql)
	fmt.Println(args)
}

Output:
INSERT INTO users (id,name) VALUES (?,?) ON DUPLICATE KEY UPDATE name = ?
[1 Alice Alice]

func (InsertBuilder) OnDuplicateKeyUpdateMap added in v0.0.5 

func (b InsertBuilder) OnDuplicateKeyUpdateMap(clauses map[string]any) InsertBuilder

OnDuplicateKeyUpdateMap is a convenience method that calls OnDuplicateKeyUpdate for each key/value pair in clauses.

func (InsertBuilder) Options 

func (b InsertBuilder) Options(options ...string) InsertBuilder

Options adds keyword options before the INTO clause of the query.

WARNING: Options are interpolated directly into the SQL string without sanitization. NEVER pass unsanitized user input to this method.

Example 
package main

import (
	"fmt"

	sq "github.com/alexZaicev/squirrel"
)

func main() {
	sql, args, _ := sq.Insert("users").
		Options("IGNORE").
		Columns("name").
		Values("Alice").
		ToSQL()
	fmt.Println(sql)
	fmt.Println(args)
}

Output:
INSERT IGNORE INTO users (name) VALUES (?)
[Alice]

func (InsertBuilder) PlaceholderFormat 

func (b InsertBuilder) PlaceholderFormat(f PlaceholderFormat) InsertBuilder

PlaceholderFormat sets PlaceholderFormat (e.g. Question or Dollar) for the query.

func (InsertBuilder) Prefix 

func (b InsertBuilder) Prefix(sql string, args ...any) InsertBuilder

Prefix adds an expression to the beginning of the query

func (InsertBuilder) PrefixExpr 

func (b InsertBuilder) PrefixExpr(expr Sqlizer) InsertBuilder

PrefixExpr adds an expression to the very beginning of the query

func (InsertBuilder) Query 

func (b InsertBuilder) Query() (*sql.Rows, error)

Query builds and Querys the query with the Runner set by RunWith.

func (InsertBuilder) QueryContext 

func (b InsertBuilder) QueryContext(ctx context.Context) (*sql.Rows, error)

QueryContext builds and QueryContexts the query with the Runner set by RunWith.

func (InsertBuilder) QueryRow 

func (b InsertBuilder) QueryRow() RowScanner

QueryRow builds and QueryRows the query with the Runner set by RunWith.

func (InsertBuilder) QueryRowContext 

func (b InsertBuilder) QueryRowContext(ctx context.Context) RowScanner

QueryRowContext builds and QueryRowContexts the query with the Runner set by RunWith.

func (InsertBuilder) Returning added in v0.0.6 

func (b InsertBuilder) Returning(columns ...string) InsertBuilder

Returning adds RETURNING expressions to the query.

Ex: 

Insert("users").Columns("name").Values("John").
	Returning("id", "created_at")
// INSERT INTO users (name) VALUES (?) RETURNING id, created_at
Example 
package main

import (
	"fmt"

	sq "github.com/alexZaicev/squirrel"
)

func main() {
	sql, args, _ := sq.Insert("users").
		Columns("name").
		Values("Alice").
		Returning("id", "created_at").
		ToSQL()
	fmt.Println(sql)
	fmt.Println(args)
}

Output:
INSERT INTO users (name) VALUES (?) RETURNING id, created_at
[Alice]

func (InsertBuilder) RunWith 

func (b InsertBuilder) RunWith(runner BaseRunner) InsertBuilder

RunWith sets a Runner (like database/sql.DB) to be used with e.g. Exec.

func (InsertBuilder) SafeColumns added in v0.4.0 

func (b InsertBuilder) SafeColumns(columns ...Ident) InsertBuilder

SafeColumns adds insert columns to the query using safe Ident values.

Ex: 

cols, _ := sq.QuoteIdents("id", "name")
sq.Insert("users").SafeColumns(cols...).Values(1, "John")
Example 
package main

import (
	"fmt"

	sq "github.com/alexZaicev/squirrel"
)

func main() {
	cols, _ := sq.QuoteIdents("id", "name")
	sql, args, _ := sq.Insert("users").SafeColumns(cols...).Values(1, "moe").ToSQL()
	fmt.Println(sql)
	fmt.Println(args)
}

Output:
INSERT INTO users ("id","name") VALUES (?,?)
[1 moe]

func (InsertBuilder) SafeInto added in v0.4.0 

func (b InsertBuilder) SafeInto(into Ident) InsertBuilder

SafeInto sets the INTO clause of the query using a safe Ident.

Ex: 

id, _ := sq.QuoteIdent(userInput)
sq.Insert("").SafeInto(id).Columns("name").Values("John")
Example 
package main

import (
	"fmt"

	sq "github.com/alexZaicev/squirrel"
)

func main() {
	table, _ := sq.QuoteIdent("users")
	sql, args, _ := sq.Insert("").SafeInto(table).Columns("name").Values("moe").ToSQL()
	fmt.Println(sql)
	fmt.Println(args)
}

Output:
INSERT INTO "users" (name) VALUES (?)
[moe]

func (InsertBuilder) SafeSetColumn added in v0.4.1 

func (b InsertBuilder) SafeSetColumn(column Ident, value interface{}) InsertBuilder

SafeSetColumn adds a single column name (as a safe Ident) and appends the corresponding value to every existing row. If no rows exist yet, a new single-value row is created. This is the safe counterpart of SetColumn for dynamic column names from user input.

Ex: 

col, _ := sq.QuoteIdent(userInput)
q = q.SafeSetColumn(col, someValue)

func (InsertBuilder) Scan 

func (b InsertBuilder) Scan(dest ...any) error

Scan is a shortcut for QueryRow().Scan.

func (InsertBuilder) ScanContext 

func (b InsertBuilder) ScanContext(ctx context.Context, dest ...any) error

ScanContext is a shortcut for QueryRowContext().Scan.

func (InsertBuilder) Select 

func (b InsertBuilder) Select(sb SelectBuilder) InsertBuilder

Select set Select clause for insert query If Values and Select are used, then Select has higher priority

Example 
package main

import (
	"fmt"

	sq "github.com/alexZaicev/squirrel"
)

func main() {
	sql, args, _ := sq.Insert("user_archive").
		Columns("id", "name").
		Select(
			sq.Select("id", "name").From("users").Where(sq.Eq{"active": false}),
		).
		ToSQL()
	fmt.Println(sql)
	fmt.Println(args)
}

Output:
INSERT INTO user_archive (id,name) SELECT id, name FROM users WHERE active = ?
[false]
Example (FromValues) 
package main

import (
	"fmt"

	sq "github.com/alexZaicev/squirrel"
)

func main() {
	sql, args, _ := sq.Insert("employees").
		Columns("id", "name").
		Select(
			sq.Select("v.id", "v.name").
				FromValues(
					[][]interface{}{{1, "Alice"}, {2, "Bob"}},
					"v", "id", "name",
				),
		).
		PlaceholderFormat(sq.Dollar).
		ToSQL()
	fmt.Println(sql)
	fmt.Println(args)
}

Output:
INSERT INTO employees (id,name) SELECT v.id, v.name FROM (VALUES ($1::bigint, $2::text), ($3, $4)) AS v(id, name)
[1 Alice 2 Bob]

func (InsertBuilder) SetColumn added in v0.4.1 

func (b InsertBuilder) SetColumn(column string, value interface{}) InsertBuilder

SetColumn adds a single column name and appends the corresponding value to every existing row. If no rows exist yet, a new single-value row is created. This enables conditional, incremental column/value building without producing invalid multi-row VALUES clauses.

Ex: 

q := sq.Insert("test").SetColumn("a", 1)
if needB {
    q = q.SetColumn("b", 2)
}
// INSERT INTO test (a,b) VALUES (?,?)

WARNING: The column name is interpolated directly into the SQL string without sanitization. NEVER pass unsanitized user input to this method. For dynamic column names from user input, use SafeSetColumn instead.

func (InsertBuilder) SetMap 

func (b InsertBuilder) SetMap(clauses map[string]any) InsertBuilder

SetMap set columns and values for insert builder from a map of column name and value note that it will reset all previous columns and values was set if any

Example 
package main

import (
	"fmt"

	sq "github.com/alexZaicev/squirrel"
)

func main() {
	sql, args, _ := sq.Insert("users").
		SetMap(map[string]interface{}{
			"age":  30,
			"name": "Alice",
		}).
		ToSQL()
	fmt.Println(sql)
	fmt.Println(args)
}

Output:
INSERT INTO users (age,name) VALUES (?,?)
[30 Alice]

func (InsertBuilder) Suffix 

func (b InsertBuilder) Suffix(sql string, args ...any) InsertBuilder

Suffix adds an expression to the end of the query

func (InsertBuilder) SuffixExpr 

func (b InsertBuilder) SuffixExpr(expr Sqlizer) InsertBuilder

SuffixExpr adds an expression to the end of the query

func (InsertBuilder) ToSQL added in v0.0.2 

func (b InsertBuilder) ToSQL() (string, []any, error)

ToSQL builds the query into a SQL string and bound args.

func (InsertBuilder) Values 

func (b InsertBuilder) Values(values ...any) InsertBuilder

Values adds a single row's values to the query.

Example (Multiple) 
package main

import (
	"fmt"

	sq "github.com/alexZaicev/squirrel"
)

func main() {
	sql, args, _ := sq.Insert("users").
		Columns("name", "age").
		Values("Alice", 30).
		Values("Bob", 25).
		ToSQL()
	fmt.Println(sql)
	fmt.Println(args)
}

Output:
INSERT INTO users (name,age) VALUES (?,?),(?,?)
[Alice 30 Bob 25]

type JoinBuilder added in v0.2.0 

type JoinBuilder interface {
	Sqlizer
	// Type sets the join type (JoinInner, JoinLeft, JoinRight, JoinFull, JoinCross).
	Type(JoinType) JoinBuilder
	// As sets an alias for the joined table.
	As(string) JoinBuilder
	// SubQuery sets a subquery as the join target instead of a plain table name.
	SubQuery(Sqlizer) JoinBuilder
	// On adds a raw ON condition. Multiple calls are ANDed together.
	On(pred string, args ...any) JoinBuilder
	// OnExpr adds a Sqlizer-based ON condition. Multiple calls are ANDed together.
	OnExpr(Sqlizer) JoinBuilder
	// Using sets the USING columns. Mutually exclusive with On/OnExpr.
	Using(columns ...string) JoinBuilder
}

JoinBuilder builds a structured join clause. It implements Sqlizer so it can be passed directly to SelectBuilder.JoinClause. Use JoinExpr to create one.

func JoinExpr added in v0.2.0 

func JoinExpr(table string) JoinBuilder

JoinExpr starts building a structured join clause for the given table.

Use On / OnExpr to add ON conditions, or Using to add USING columns. Pass the result to SelectBuilder.JoinClause: 

sq.Select("*").From("items").JoinClause(
    sq.JoinExpr("users").On("items.fk_user_key = users.key"),
)
// SELECT * FROM items JOIN users ON items.fk_user_key = users.key
Example 
package main

import (
	"fmt"

	sq "github.com/alexZaicev/squirrel"
)

func main() {
	sql, _, _ := sq.Select("items.name", "users.username").
		From("items").
		JoinClause(
			sq.JoinExpr("users").On("items.fk_user_key = users.key"),
		).
		ToSQL()
	fmt.Println(sql)
}

Output:
SELECT items.name, users.username FROM items JOIN users ON items.fk_user_key = users.key
Example (LeftJoinAlias) 
package main

import (
	"fmt"

	sq "github.com/alexZaicev/squirrel"
)

func main() {
	sql, _, _ := sq.Select("i.name", "u.username").
		From("items i").
		JoinClause(
			sq.JoinExpr("users").Type(sq.JoinLeft).As("u").
				On("i.fk_user_key = u.key"),
		).
		ToSQL()
	fmt.Println(sql)
}

Output:
SELECT i.name, u.username FROM items i LEFT JOIN users u ON i.fk_user_key = u.key
Example (MultipleConditions) 
package main

import (
	"fmt"

	sq "github.com/alexZaicev/squirrel"
)

func main() {
	sql, args, _ := sq.Select("items.name", "users.username").
		From("items").
		JoinClause(
			sq.JoinExpr("users").
				On("items.fk_user_key = users.key").
				On("users.username = ?", "alice"),
		).
		ToSQL()
	fmt.Println(sql)
	fmt.Println(args)
}

Output:
SELECT items.name, users.username FROM items JOIN users ON items.fk_user_key = users.key AND users.username = ?
[alice]
Example (SubQuery) 
package main

import (
	"fmt"

	sq "github.com/alexZaicev/squirrel"
)

func main() {
	sub := sq.Select("id", "name").From("users").Where(sq.Eq{"active": true})
	sql, args, _ := sq.Select("items.name", "u.name").
		From("items").
		JoinClause(
			sq.JoinExpr("").SubQuery(sub).As("u").
				On("items.fk_user_key = u.id"),
		).
		ToSQL()
	fmt.Println(sql)
	fmt.Println(args)
}

Output:
SELECT items.name, u.name FROM items JOIN (SELECT id, name FROM users WHERE active = ?) u ON items.fk_user_key = u.id
[true]
Example (Using) 
package main

import (
	"fmt"

	sq "github.com/alexZaicev/squirrel"
)

func main() {
	sql, _, _ := sq.Select("*").
		From("orders").
		JoinClause(sq.JoinExpr("customers").Using("customer_id")).
		ToSQL()
	fmt.Println(sql)
}

Output:
SELECT * FROM orders JOIN customers USING (customer_id)

type JoinType added in v0.2.0 

type JoinType string

JoinType represents the type of SQL JOIN. 

const (
	// JoinInner represents a JOIN / INNER JOIN.
	JoinInner JoinType = "JOIN"
	// JoinLeft represents a LEFT JOIN.
	JoinLeft JoinType = "LEFT JOIN"
	// JoinRight represents a RIGHT JOIN.
	JoinRight JoinType = "RIGHT JOIN"
	// JoinFull represents a FULL OUTER JOIN.
	JoinFull JoinType = "FULL OUTER JOIN"
	// JoinCross represents a CROSS JOIN.
	JoinCross JoinType = "CROSS JOIN"
)

type Like 

type Like map[string]any

Like is syntactic sugar for use with LIKE conditions. Ex: 

.Where(Like{"name": "%irrel"})
Example 
package main

import (
	"fmt"

	sq "github.com/alexZaicev/squirrel"
)

func main() {
	sql, args, _ := sq.Like{"name": "%irrel"}.ToSQL()
	fmt.Println(sql)
	fmt.Println(args)
}

Output:
name LIKE ?
[%irrel]

func (Like) ToSQL added in v0.0.2 

func (lk Like) ToSQL() (sql string, args []any, err error)

type Lt 

type Lt map[string]any

Lt is syntactic sugar for use with Where/Having/Set methods. Ex: 

.Where(Lt{"id": 1})
Example 
package main

import (
	"fmt"

	sq "github.com/alexZaicev/squirrel"
)

func main() {
	sql, args, _ := sq.Lt{"age": 18}.ToSQL()
	fmt.Println(sql)
	fmt.Println(args)
}

Output:
age < ?
[18]

func (Lt) ToSQL added in v0.0.2 

func (lt Lt) ToSQL() (sql string, args []any, err error)

type LtOrEq 

type LtOrEq Lt

LtOrEq is syntactic sugar for use with Where/Having/Set methods. Ex: 

.Where(LtOrEq{"id": 1}) == "id <= 1"
Example 
package main

import (
	"fmt"

	sq "github.com/alexZaicev/squirrel"
)

func main() {
	sql, args, _ := sq.LtOrEq{"age": 65}.ToSQL()
	fmt.Println(sql)
	fmt.Println(args)
}

Output:
age <= ?
[65]

func (LtOrEq) ToSQL added in v0.0.2 

func (ltOrEq LtOrEq) ToSQL() (sql string, args []any, err error)

type Not added in v0.1.0 

type Not struct {
	Cond Sqlizer
}

Not negates the given Sqlizer condition.

Ex: 

sq.Not{sq.Eq{"active": true}}    → NOT (active = ?)
sq.Not{sq.Or{sq.Eq{"a": 1}, sq.Eq{"b": 2}}} → NOT ((a = ? OR b = ?))
Example 
package main

import (
	"fmt"

	sq "github.com/alexZaicev/squirrel"
)

func main() {
	sql, args, _ := sq.Not{Cond: sq.Eq{"active": true}}.ToSQL()
	fmt.Println(sql)
	fmt.Println(args)
}

Output:
NOT (active = ?)
[true]

func (Not) ToSQL added in v0.1.0 

func (n Not) ToSQL() (sql string, args []any, err error)

type NotBetween added in v0.1.0 

type NotBetween Between

NotBetween is syntactic sugar for use with Where/Having methods. Values must be two-element arrays: [2]interface{}{lo, hi}.

Ex: 

.Where(NotBetween{"age": [2]interface{}{18, 65}})  // age NOT BETWEEN ? AND ?
Example 
package main

import (
	"fmt"

	sq "github.com/alexZaicev/squirrel"
)

func main() {
	sql, args, _ := sq.NotBetween{"age": [2]interface{}{18, 65}}.ToSQL()
	fmt.Println(sql)
	fmt.Println(args)
}

Output:
age NOT BETWEEN ? AND ?
[18 65]

func (NotBetween) ToSQL added in v0.1.0 

func (nb NotBetween) ToSQL() (sql string, args []any, err error)

type NotEq 

type NotEq Eq

NotEq is syntactic sugar for use with Where/Having/Set methods. Ex: 

.Where(NotEq{"id": 1}) == "id <> 1"
Example 
package main

import (
	"fmt"

	sq "github.com/alexZaicev/squirrel"
)

func main() {
	sql, args, _ := sq.NotEq{"status": "deleted"}.ToSQL()
	fmt.Println(sql)
	fmt.Println(args)
}

Output:
status <> ?
[deleted]

func (NotEq) ToSQL added in v0.0.2 

func (neq NotEq) ToSQL() (sql string, args []any, err error)

type NotILike 

type NotILike Like

NotILike is syntactic sugar for use with ILIKE conditions. Ex: 

.Where(NotILike{"name": "sq%"})
Example 
package main

import (
	"fmt"

	sq "github.com/alexZaicev/squirrel"
)

func main() {
	sql, args, _ := sq.NotILike{"name": "sq%"}.ToSQL()
	fmt.Println(sql)
	fmt.Println(args)
}

Output:
name NOT ILIKE ?
[sq%]

func (NotILike) ToSQL added in v0.0.2 

func (nilk NotILike) ToSQL() (sql string, args []any, err error)

type NotLike 

type NotLike Like

NotLike is syntactic sugar for use with LIKE conditions. Ex: 

.Where(NotLike{"name": "%irrel"})
Example 
package main

import (
	"fmt"

	sq "github.com/alexZaicev/squirrel"
)

func main() {
	sql, args, _ := sq.NotLike{"name": "%test%"}.ToSQL()
	fmt.Println(sql)
	fmt.Println(args)
}

Output:
name NOT LIKE ?
[%test%]

func (NotLike) ToSQL added in v0.0.2 

func (nlk NotLike) ToSQL() (sql string, args []any, err error)

type Or 

type Or conj

Or conjunction Sqlizers

Example 
package main

import (
	"fmt"

	sq "github.com/alexZaicev/squirrel"
)

func main() {
	sql, args, _ := sq.Or{
		sq.Eq{"status": "active"},
		sq.Eq{"status": "pending"},
	}.ToSQL()
	fmt.Println(sql)
	fmt.Println(args)
}

Output:
(status = ? OR status = ?)
[active pending]

func (Or) ToSQL added in v0.0.2 

func (o Or) ToSQL() (string, []any, error)

type OrderDir added in v0.4.0 

type OrderDir string

OrderDir represents an ORDER BY sort direction. 

const (
	// Asc sorts in ascending order.
	Asc OrderDir = "ASC"
	// Desc sorts in descending order.
	Desc OrderDir = "DESC"
)

type PlaceholderFormat 

type PlaceholderFormat interface {
	ReplacePlaceholders(sql string) (string, error)
}

PlaceholderFormat is the interface that wraps the ReplacePlaceholders method.

ReplacePlaceholders takes a SQL statement and replaces each question mark placeholder with a (possibly different) SQL placeholder.

type Preparer 

type Preparer interface {
	Prepare(query string) (*sql.Stmt, error)
}

Prepareer is the interface that wraps the Prepare method.

Prepare executes the given query as implemented by database/sql.Prepare.

type PreparerContext 

type PreparerContext interface {
	Preparer
	PrepareContext(ctx context.Context, query string) (*sql.Stmt, error)
}

PrepareerContext is the interface that wraps the Prepare and PrepareContext methods.

Prepare executes the given query as implemented by database/sql.Prepare. PrepareContext executes the given query as implemented by database/sql.PrepareContext.

type QueryRower 

type QueryRower interface {
	QueryRow(query string, args ...any) RowScanner
}

QueryRower is the interface that wraps the QueryRow method.

QueryRow executes the given query as implemented by database/sql.QueryRow.

type QueryRowerContext 

type QueryRowerContext interface {
	QueryRowContext(ctx context.Context, query string, args ...any) RowScanner
}

QueryRowerContext is the interface that wraps the QueryRowContext method.

QueryRowContext executes the given query as implemented by database/sql.QueryRowContext.

type Queryer 

type Queryer interface {
	Query(query string, args ...any) (*sql.Rows, error)
}

Queryer is the interface that wraps the Query method.

Query executes the given query as implemented by database/sql.Query.

type QueryerContext 

type QueryerContext interface {
	QueryContext(ctx context.Context, query string, args ...any) (*sql.Rows, error)
}

QueryerContext is the interface that wraps the QueryContext method.

QueryContext executes the given query as implemented by database/sql.QueryContext.

type Row 

type Row struct {
	RowScanner
	// contains filtered or unexported fields
}

Row wraps database/sql.Row to let squirrel return new errors on Scan.

func (*Row) Scan 

func (r *Row) Scan(dest ...any) error

Scan returns Row.err or calls RowScanner.Scan.

type RowScanner 

type RowScanner interface {
	Scan(...any) error
}

RowScanner is the interface that wraps the Scan method.

Scan behaves like database/sql.Row.Scan.

func QueryRowContextWith 

func QueryRowContextWith(ctx context.Context, db QueryRowerContext, s Sqlizer) RowScanner

QueryRowContextWith QueryRowContexts the SQL returned by s with db.

func QueryRowWith 

func QueryRowWith(db QueryRower, s Sqlizer) RowScanner

QueryRowWith QueryRows the SQL returned by s with db.

type Runner 

type Runner interface {
	Execer
	Queryer
	QueryRower
}

Runner groups the Execer, Queryer, and QueryRower interfaces.

func WrapStdSQL added in v0.0.2 

func WrapStdSQL(stdSQL StdSQL) Runner

WrapStdSQL wraps a type implementing the standard SQL interface with methods that squirrel expects.

type RunnerContext 

type RunnerContext interface {
	Runner
	QueryerContext
	QueryRowerContext
	ExecerContext
}

RunnerContext groups the Runner interface, along with the Context versions of each of its methods

func WrapStdSQLCtx added in v0.0.2 

func WrapStdSQLCtx(stdSQLCtx StdSQLCtx) RunnerContext

WrapStdSQLCtx wraps a type implementing the standard SQL interface plus the context versions of the methods with methods that squirrel expects.

type SelectBuilder 

type SelectBuilder builder.Builder

SelectBuilder builds SQL SELECT statements.

func Select 

func Select(columns ...string) SelectBuilder

Select returns a new SelectBuilder, optionally setting some result columns.

See SelectBuilder.Columns.

Example 
package main

import (
	"fmt"

	sq "github.com/alexZaicev/squirrel"
)

func main() {
	sql, args, err := sq.Select("id", "created", "first_name").From("users").ToSQL()
	if err != nil {
		panic(err)
	}
	fmt.Println(sql)
	fmt.Println(args)
}

Output:
SELECT id, created, first_name FROM users
[]

func (SelectBuilder) Column 

func (b SelectBuilder) Column(column any, args ...any) SelectBuilder

Column adds a result column to the query. Unlike Columns, Column accepts args which will be bound to placeholders in the columns string, for example: 

Column("IF(col IN ("+squirrel.Placeholders(3)+"), 1, 0) as col", 1, 2, 3)

func (SelectBuilder) Columns 

func (b SelectBuilder) Columns(columns ...string) SelectBuilder

Columns adds result columns to the query.

WARNING: Column names are interpolated directly into the SQL string without sanitization. NEVER pass unsanitized user input to this method. For dynamic column names from user input, use SafeColumns instead.

Example 
package main

import (
	"fmt"

	sq "github.com/alexZaicev/squirrel"
)

func main() {
	query := sq.Select("id").Columns("created", "first_name").From("users")
	sql, _, _ := query.ToSQL()
	fmt.Println(sql)
}

Output:
SELECT id, created, first_name FROM users
Example (Order) 
package main

import (
	"fmt"

	sq "github.com/alexZaicev/squirrel"
)

func main() {
	// out of order is ok too
	query := sq.Select("id").Columns("created").From("users").Columns("first_name")
	sql, _, _ := query.ToSQL()
	fmt.Println(sql)
}

Output:
SELECT id, created, first_name FROM users

func (SelectBuilder) CrossJoin 

func (b SelectBuilder) CrossJoin(join string, rest ...any) SelectBuilder

CrossJoin adds a CROSS JOIN clause to the query.

WARNING: The join clause is interpolated directly into the SQL string. NEVER pass unsanitized user input to this method.

func (SelectBuilder) CrossJoinUsing added in v0.2.0 

func (b SelectBuilder) CrossJoinUsing(table string, columns ...string) SelectBuilder

CrossJoinUsing adds a CROSS JOIN ... USING clause to the query.

Example 
package main

import (
	"fmt"

	sq "github.com/alexZaicev/squirrel"
)

func main() {
	sql, _, _ := sq.Select("a.x", "b.y").
		From("a").
		CrossJoinUsing("b", "id").
		ToSQL()
	fmt.Println(sql)
}

Output:
SELECT a.x, b.y FROM a CROSS JOIN b USING (id)

func (SelectBuilder) Distinct 

func (b SelectBuilder) Distinct() SelectBuilder

Distinct adds a DISTINCT clause to the query. Multiple calls are idempotent — calling Distinct() more than once still produces a single DISTINCT keyword in the generated SQL.

Example 
package main

import (
	"fmt"

	sq "github.com/alexZaicev/squirrel"
)

func main() {
	sql, _, _ := sq.Select("country").Distinct().From("users").ToSQL()
	fmt.Println(sql)
}

Output:
SELECT DISTINCT country FROM users
Example (Idempotent) 
package main

import (
	"fmt"

	sq "github.com/alexZaicev/squirrel"
)

func main() {
	sql, _, _ := sq.Select("country").Distinct().Distinct().From("users").ToSQL()
	fmt.Println(sql)
}

Output:
SELECT DISTINCT country FROM users

func (SelectBuilder) Exec 

func (b SelectBuilder) Exec() (sql.Result, error)

Exec builds and Execs the query with the Runner set by RunWith.

func (SelectBuilder) ExecContext 

func (b SelectBuilder) ExecContext(ctx context.Context) (sql.Result, error)

ExecContext builds and ExecContexts the query with the Runner set by RunWith.

func (SelectBuilder) From 

func (b SelectBuilder) From(from string) SelectBuilder

From sets the FROM clause of the query.

WARNING: The table name is interpolated directly into the SQL string without sanitization. NEVER pass unsanitized user input to this method. For dynamic table names from user input, use SafeFrom instead.

Example 
package main

import (
	"fmt"

	sq "github.com/alexZaicev/squirrel"
)

func main() {
	sql, _, _ := sq.Select("id", "created", "first_name").From("users").ToSQL()
	fmt.Println(sql)
}

Output:
SELECT id, created, first_name FROM users

func (SelectBuilder) FromSelect 

func (b SelectBuilder) FromSelect(from SelectBuilder, alias string) SelectBuilder

FromSelect sets a subquery into the FROM clause of the query.

Example 
package main

import (
	"fmt"

	sq "github.com/alexZaicev/squirrel"
)

func main() {
	usersByCompany := sq.Select("company", "count(*) as n_users").From("users").GroupBy("company")
	query := sq.Select("company.id", "company.name", "users_by_company.n_users").
		FromSelect(usersByCompany, "users_by_company").
		Join("company on company.id = users_by_company.company")

	sql, _, _ := query.ToSQL()
	fmt.Println(sql)
}

Output:
SELECT company.id, company.name, users_by_company.n_users FROM (SELECT company, count(*) as n_users FROM users GROUP BY company) AS users_by_company JOIN company on company.id = users_by_company.company

func (SelectBuilder) FromValues added in v0.6.0 

func (b SelectBuilder) FromValues(values [][]interface{}, alias string, columns ...string) SelectBuilder

FromValues sets a VALUES list into the FROM clause of the query. This enables SELECT ... FROM (VALUES ...) patterns, commonly used in PostgreSQL for inline data or as part of INSERT ... SELECT FROM (VALUES ...).

Ex: 

sq.Select("v.id", "v.name").
	FromValues(
		[][]interface{}{{1, "Alice"}, {2, "Bob"}},
		"v", "id", "name",
	)
// SELECT v.id, v.name FROM (VALUES (?, ?), (?, ?)) AS v(id, name)
Example 
package main

import (
	"fmt"

	sq "github.com/alexZaicev/squirrel"
)

func main() {
	sql, args, _ := sq.Select("v.id", "v.name").
		FromValues(
			[][]interface{}{{1, "Alice"}, {2, "Bob"}},
			"v", "id", "name",
		).
		PlaceholderFormat(sq.Dollar).
		ToSQL()
	fmt.Println(sql)
	fmt.Println(args)
}

Output:
SELECT v.id, v.name FROM (VALUES ($1::bigint, $2::text), ($3, $4)) AS v(id, name)
[1 Alice 2 Bob]

func (SelectBuilder) FullJoin added in v0.2.0 

func (b SelectBuilder) FullJoin(join string, rest ...any) SelectBuilder

FullJoin adds a FULL OUTER JOIN clause to the query.

WARNING: The join clause is interpolated directly into the SQL string. NEVER pass unsanitized user input to this method.

Example 
package main

import (
	"fmt"

	sq "github.com/alexZaicev/squirrel"
)

func main() {
	sql, _, _ := sq.Select("u.id", "d.name").
		From("users u").
		FullJoin("departments d ON u.dept_id = d.id").
		ToSQL()
	fmt.Println(sql)
}

Output:
SELECT u.id, d.name FROM users u FULL OUTER JOIN departments d ON u.dept_id = d.id

func (SelectBuilder) FullJoinUsing added in v0.2.0 

func (b SelectBuilder) FullJoinUsing(table string, columns ...string) SelectBuilder

FullJoinUsing adds a FULL OUTER JOIN ... USING clause to the query.

Example 
package main

import (
	"fmt"

	sq "github.com/alexZaicev/squirrel"
)

func main() {
	sql, _, _ := sq.Select("o.id", "r.region_name").
		From("orders o").
		FullJoinUsing("regions r", "region_id").
		ToSQL()
	fmt.Println(sql)
}

Output:
SELECT o.id, r.region_name FROM orders o FULL OUTER JOIN regions r USING (region_id)

func (SelectBuilder) GroupBy 

func (b SelectBuilder) GroupBy(groupBys ...string) SelectBuilder

GroupBy adds GROUP BY expressions to the query.

WARNING: Group-by expressions are interpolated directly into the SQL string. NEVER pass unsanitized user input to this method. For dynamic group-by columns from user input, use SafeGroupBy instead.

Example 
package main

import (
	"fmt"

	sq "github.com/alexZaicev/squirrel"
)

func main() {
	sql, _, _ := sq.Select("department", "count(*) as cnt").
		From("employees").
		GroupBy("department").
		Having("count(*) > 5").
		ToSQL()
	fmt.Println(sql)
}

Output:
SELECT department, count(*) as cnt FROM employees GROUP BY department HAVING count(*) > 5

func (SelectBuilder) Having 

func (b SelectBuilder) Having(pred any, rest ...any) SelectBuilder

Having adds an expression to the HAVING clause of the query.

See Where.

func (SelectBuilder) InnerJoin 

func (b SelectBuilder) InnerJoin(join string, rest ...any) SelectBuilder

InnerJoin adds a INNER JOIN clause to the query.

WARNING: The join clause is interpolated directly into the SQL string. NEVER pass unsanitized user input to this method.

func (SelectBuilder) InnerJoinUsing added in v0.2.0 

func (b SelectBuilder) InnerJoinUsing(table string, columns ...string) SelectBuilder

InnerJoinUsing adds an INNER JOIN ... USING clause to the query.

Example 
package main

import (
	"fmt"

	sq "github.com/alexZaicev/squirrel"
)

func main() {
	sql, _, _ := sq.Select("e.name", "d.name").
		From("employees e").
		InnerJoinUsing("departments d", "dept_id").
		ToSQL()
	fmt.Println(sql)
}

Output:
SELECT e.name, d.name FROM employees e INNER JOIN departments d USING (dept_id)

func (SelectBuilder) Join 

func (b SelectBuilder) Join(join string, rest ...any) SelectBuilder

Join adds a JOIN clause to the query.

WARNING: The join clause is interpolated directly into the SQL string. NEVER pass unsanitized user input to this method.

Example 
package main

import (
	"fmt"

	sq "github.com/alexZaicev/squirrel"
)

func main() {
	sql, _, _ := sq.Select("u.id", "u.name", "o.total").
		From("users u").
		Join("orders o ON o.user_id = u.id").
		ToSQL()
	fmt.Println(sql)
}

Output:
SELECT u.id, u.name, o.total FROM users u JOIN orders o ON o.user_id = u.id

func (SelectBuilder) JoinClause 

func (b SelectBuilder) JoinClause(pred any, args ...any) SelectBuilder

JoinClause adds a join clause to the query.

func (SelectBuilder) JoinUsing added in v0.2.0 

func (b SelectBuilder) JoinUsing(table string, columns ...string) SelectBuilder

JoinUsing adds a JOIN ... USING clause to the query. It is a convenience for the common case where the join condition is a simple column equality: JOIN table USING (col1, col2, ...).

Example 
package main

import (
	"fmt"

	sq "github.com/alexZaicev/squirrel"
)

func main() {
	sql, _, _ := sq.Select("orders.id", "customers.name").
		From("orders").
		JoinUsing("customers", "customer_id").
		ToSQL()
	fmt.Println(sql)
}

Output:
SELECT orders.id, customers.name FROM orders JOIN customers USING (customer_id)
Example (MultipleColumns) 
package main

import (
	"fmt"

	sq "github.com/alexZaicev/squirrel"
)

func main() {
	sql, _, _ := sq.Select("*").
		From("orders").
		JoinUsing("shipments", "region_id", "order_id").
		ToSQL()
	fmt.Println(sql)
}

Output:
SELECT * FROM orders JOIN shipments USING (region_id, order_id)

func (SelectBuilder) LeftJoin 

func (b SelectBuilder) LeftJoin(join string, rest ...any) SelectBuilder

LeftJoin adds a LEFT JOIN clause to the query.

WARNING: The join clause is interpolated directly into the SQL string. NEVER pass unsanitized user input to this method.

Example 
package main

import (
	"fmt"

	sq "github.com/alexZaicev/squirrel"
)

func main() {
	sql, _, _ := sq.Select("u.id", "u.name", "o.total").
		From("users u").
		LeftJoin("orders o ON o.user_id = u.id").
		ToSQL()
	fmt.Println(sql)
}

Output:
SELECT u.id, u.name, o.total FROM users u LEFT JOIN orders o ON o.user_id = u.id

func (SelectBuilder) LeftJoinUsing added in v0.2.0 

func (b SelectBuilder) LeftJoinUsing(table string, columns ...string) SelectBuilder

LeftJoinUsing adds a LEFT JOIN ... USING clause to the query.

Example 
package main

import (
	"fmt"

	sq "github.com/alexZaicev/squirrel"
)

func main() {
	sql, _, _ := sq.Select("orders.id", "returns.reason").
		From("orders").
		LeftJoinUsing("returns", "order_id").
		ToSQL()
	fmt.Println(sql)
}

Output:
SELECT orders.id, returns.reason FROM orders LEFT JOIN returns USING (order_id)

func (SelectBuilder) Limit 

func (b SelectBuilder) Limit(limit uint64) SelectBuilder

Limit sets a LIMIT clause on the query.

Example 
package main

import (
	"fmt"

	sq "github.com/alexZaicev/squirrel"
)

func main() {
	sql, args, _ := sq.Select("id", "name").
		From("users").
		Limit(10).
		Offset(20).
		ToSQL()
	fmt.Println(sql)
	fmt.Println(args)
}

Output:
SELECT id, name FROM users LIMIT ? OFFSET ?
[10 20]
Example (Dollar) 
package main

import (
	"fmt"

	sq "github.com/alexZaicev/squirrel"
)

func main() {
	// Parameterized LIMIT/OFFSET works with all placeholder formats,
	// enabling prepared-statement reuse across different page sizes.
	sql, args, _ := sq.Select("id", "name").
		From("users").
		Where("active = ?", true).
		Limit(10).
		Offset(20).
		PlaceholderFormat(sq.Dollar).
		ToSQL()
	fmt.Println(sql)
	fmt.Println(args)
}

Output:
SELECT id, name FROM users WHERE active = $1 LIMIT $2 OFFSET $3
[true 10 20]

func (SelectBuilder) MustSQL added in v0.0.2 

func (b SelectBuilder) MustSQL() (string, []any)

MustSQL builds the query into a SQL string and bound args. It panics if there are any errors.

func (SelectBuilder) Offset 

func (b SelectBuilder) Offset(offset uint64) SelectBuilder

Offset sets a OFFSET clause on the query.

func (SelectBuilder) Options 

func (b SelectBuilder) Options(options ...string) SelectBuilder

Options adds select option to the query.

WARNING: Options are interpolated directly into the SQL string without sanitization. NEVER pass unsanitized user input to this method.

func (SelectBuilder) OrderBy 

func (b SelectBuilder) OrderBy(orderBys ...string) SelectBuilder

OrderBy adds ORDER BY expressions to the query.

WARNING: Order-by expressions are interpolated directly into the SQL string. NEVER pass unsanitized user input to this method. For dynamic order-by columns from user input, use SafeOrderBy instead.

Example 
package main

import (
	"fmt"

	sq "github.com/alexZaicev/squirrel"
)

func main() {
	sql, _, _ := sq.Select("id", "name").
		From("users").
		OrderBy("name ASC", "id DESC").
		ToSQL()
	fmt.Println(sql)
}

Output:
SELECT id, name FROM users ORDER BY name ASC, id DESC

func (SelectBuilder) OrderByClause 

func (b SelectBuilder) OrderByClause(pred any, args ...any) SelectBuilder

OrderByClause adds ORDER BY clause to the query.

func (SelectBuilder) PlaceholderFormat 

func (b SelectBuilder) PlaceholderFormat(f PlaceholderFormat) SelectBuilder

PlaceholderFormat sets PlaceholderFormat (e.g. Question or Dollar) for the query.

Example 
package main

import (
	"fmt"

	sq "github.com/alexZaicev/squirrel"
)

func main() {
	sql, _, _ := sq.Select("id", "name").
		From("users").
		Where("id = ?", 1).
		PlaceholderFormat(sq.Dollar).
		ToSQL()
	fmt.Println(sql)
}

Output:
SELECT id, name FROM users WHERE id = $1

func (SelectBuilder) Prefix 

func (b SelectBuilder) Prefix(sql string, args ...any) SelectBuilder

Prefix adds an expression to the beginning of the query

Example 
package main

import (
	"fmt"

	sq "github.com/alexZaicev/squirrel"
)

func main() {
	sql, args, _ := sq.Select("*").
		Prefix("WITH cte AS (?)", 0).
		From("cte").
		ToSQL()
	fmt.Println(sql)
	fmt.Println(args)
}

Output:
WITH cte AS (?) SELECT * FROM cte
[0]

func (SelectBuilder) PrefixExpr 

func (b SelectBuilder) PrefixExpr(expr Sqlizer) SelectBuilder

PrefixExpr adds an expression to the very beginning of the query

func (SelectBuilder) Query 

func (b SelectBuilder) Query() (*sql.Rows, error)

Query builds and Querys the query with the Runner set by RunWith.

func (SelectBuilder) QueryContext 

func (b SelectBuilder) QueryContext(ctx context.Context) (*sql.Rows, error)

QueryContext builds and QueryContexts the query with the Runner set by RunWith.

func (SelectBuilder) QueryRow 

func (b SelectBuilder) QueryRow() RowScanner

QueryRow builds and QueryRows the query with the Runner set by RunWith.

func (SelectBuilder) QueryRowContext 

func (b SelectBuilder) QueryRowContext(ctx context.Context) RowScanner

QueryRowContext builds and QueryRowContexts the query with the Runner set by RunWith.

func (SelectBuilder) RemoveColumns 

func (b SelectBuilder) RemoveColumns() SelectBuilder

RemoveColumns remove all columns from query. Must add a new column with Column or Columns methods, otherwise return a error.

Example 
package main

import (
	"fmt"

	sq "github.com/alexZaicev/squirrel"
)

func main() {
	query := sq.Select("id").
		From("users").
		RemoveColumns().
		Columns("name")
	sql, _, _ := query.ToSQL()
	fmt.Println(sql)
}

Output:
SELECT name FROM users

func (SelectBuilder) RemoveLimit 

func (b SelectBuilder) RemoveLimit() SelectBuilder

Limit ALL allows to access all records with limit

func (SelectBuilder) RemoveOffset 

func (b SelectBuilder) RemoveOffset() SelectBuilder

RemoveOffset removes OFFSET clause.

func (SelectBuilder) RightJoin 

func (b SelectBuilder) RightJoin(join string, rest ...any) SelectBuilder

RightJoin adds a RIGHT JOIN clause to the query.

WARNING: The join clause is interpolated directly into the SQL string. NEVER pass unsanitized user input to this method.

func (SelectBuilder) RightJoinUsing added in v0.2.0 

func (b SelectBuilder) RightJoinUsing(table string, columns ...string) SelectBuilder

RightJoinUsing adds a RIGHT JOIN ... USING clause to the query.

Example 
package main

import (
	"fmt"

	sq "github.com/alexZaicev/squirrel"
)

func main() {
	sql, _, _ := sq.Select("orders.id", "products.name").
		From("orders").
		RightJoinUsing("products", "product_id").
		ToSQL()
	fmt.Println(sql)
}

Output:
SELECT orders.id, products.name FROM orders RIGHT JOIN products USING (product_id)

func (SelectBuilder) RunWith 

func (b SelectBuilder) RunWith(runner BaseRunner) SelectBuilder

RunWith sets a Runner (like database/sql.DB) to be used with e.g. Exec. For most cases runner will be a database connection.

Internally we use this to mock out the database connection for testing.

func (SelectBuilder) SafeColumns added in v0.4.0 

func (b SelectBuilder) SafeColumns(columns ...Ident) SelectBuilder

SafeColumns adds result columns to the query using safe Ident values.

Ex: 

cols, _ := sq.QuoteIdents("id", "name")
sq.Select().SafeColumns(cols...)
Example 
package main

import (
	"fmt"

	sq "github.com/alexZaicev/squirrel"
)

func main() {
	cols, _ := sq.QuoteIdents("id", "name", "email")
	sql, _, _ := sq.Select().SafeColumns(cols...).From("users").ToSQL()
	fmt.Println(sql)
}

Output:
SELECT "id", "name", "email" FROM users

func (SelectBuilder) SafeFrom added in v0.4.0 

func (b SelectBuilder) SafeFrom(from Ident) SelectBuilder

SafeFrom sets the FROM clause of the query using a safe Ident.

Ex: 

id, _ := sq.QuoteIdent(userInput)
sq.Select("*").SafeFrom(id)
Example 
package main

import (
	"fmt"

	sq "github.com/alexZaicev/squirrel"
)

func main() {
	table, _ := sq.QuoteIdent("users")
	sql, _, _ := sq.Select("*").SafeFrom(table).ToSQL()
	fmt.Println(sql)
}

Output:
SELECT * FROM "users"

func (SelectBuilder) SafeGroupBy added in v0.4.0 

func (b SelectBuilder) SafeGroupBy(groupBys ...Ident) SelectBuilder

SafeGroupBy adds GROUP BY expressions using safe Ident values.

Ex: 

id, _ := sq.QuoteIdent(userInput)
sq.Select("count(*)").SafeFrom(tableId).SafeGroupBy(id)
Example 
package main

import (
	"fmt"

	sq "github.com/alexZaicev/squirrel"
)

func main() {
	col, _ := sq.QuoteIdent("category")
	sql, _, _ := sq.Select("category", "count(*)").From("items").SafeGroupBy(col).ToSQL()
	fmt.Println(sql)
}

Output:
SELECT category, count(*) FROM items GROUP BY "category"

func (SelectBuilder) SafeOrderBy added in v0.4.0 

func (b SelectBuilder) SafeOrderBy(orderBys ...Ident) SelectBuilder

SafeOrderBy adds ORDER BY expressions using safe Ident values. Each Ident is used as a column name; to specify direction, use SafeOrderByDir.

Ex: 

id, _ := sq.QuoteIdent("name")
sq.Select("*").From("users").SafeOrderBy(id)

func (SelectBuilder) SafeOrderByDir added in v0.4.0 

func (b SelectBuilder) SafeOrderByDir(column Ident, dir OrderDir) SelectBuilder

SafeOrderByDir adds a single ORDER BY expression with a safe Ident column and an explicit sort direction.

Ex: 

col, _ := sq.QuoteIdent(userSortColumn)
sq.Select("*").From("users").SafeOrderByDir(col, sq.Desc)
Example 
package main

import (
	"fmt"

	sq "github.com/alexZaicev/squirrel"
)

func main() {
	col, _ := sq.QuoteIdent("name")
	sql, _, _ := sq.Select("*").From("users").SafeOrderByDir(col, sq.Desc).ToSQL()
	fmt.Println(sql)
}

Output:
SELECT * FROM users ORDER BY "name" DESC

func (SelectBuilder) Scan 

func (b SelectBuilder) Scan(dest ...any) error

Scan is a shortcut for QueryRow().Scan.

func (SelectBuilder) ScanContext 

func (b SelectBuilder) ScanContext(ctx context.Context, dest ...any) error

ScanContext is a shortcut for QueryRowContext().Scan.

func (SelectBuilder) Suffix 

func (b SelectBuilder) Suffix(sql string, args ...any) SelectBuilder

Suffix adds an expression to the end of the query

Example 
package main

import (
	"fmt"

	sq "github.com/alexZaicev/squirrel"
)

func main() {
	sql, _, _ := sq.Select("id").
		From("users").
		Suffix("FOR UPDATE").
		ToSQL()
	fmt.Println(sql)
}

Output:
SELECT id FROM users FOR UPDATE

func (SelectBuilder) SuffixExpr 

func (b SelectBuilder) SuffixExpr(expr Sqlizer) SelectBuilder

SuffixExpr adds an expression to the end of the query

func (SelectBuilder) ToSQL added in v0.0.2 

func (b SelectBuilder) ToSQL() (string, []any, error)

ToSQL builds the query into a SQL string and bound args.

func (SelectBuilder) Where 

func (b SelectBuilder) Where(pred any, args ...any) SelectBuilder

Where adds an expression to the WHERE clause of the query.

Expressions are ANDed together in the generated SQL.

Where accepts several types for its pred argument:

nil OR "" - ignored.

string - SQL expression. If the expression has SQL placeholders then a set of arguments must be passed as well, one for each placeholder.

map[string]any OR Eq - map of SQL expressions to values. Each key is transformed into an expression like "<key> = ?", with the corresponding value bound to the placeholder. If the value is nil, the expression will be "<key> IS NULL". If the value is an array or slice, the expression will be "<key> IN (?,?,...)", with one placeholder for each item in the value. These expressions are ANDed together.

Where will panic if pred isn't any of the above types.

Example 
package main

import (
	"fmt"

	sq "github.com/alexZaicev/squirrel"
)

func main() {
	sql, args, _ := sq.Select("id", "created", "first_name").
		From("users").
		Where("company = ?", 20).
		ToSQL()
	fmt.Println(sql)
	fmt.Println(args)
}

Output:
SELECT id, created, first_name FROM users WHERE company = ?
[20]
Example (Helpers) 
package main

import (
	"fmt"

	sq "github.com/alexZaicev/squirrel"
)

func main() {
	sql, args, _ := sq.Select("id", "created", "first_name").
		From("users").
		Where(sq.Eq{"company": 20}).
		ToSQL()
	fmt.Println(sql)
	fmt.Println(args)
}

Output:
SELECT id, created, first_name FROM users WHERE company = ?
[20]
Example (Multiple) 
package main

import (
	"fmt"

	sq "github.com/alexZaicev/squirrel"
)

func main() {
	sql, args, _ := sq.Select("id", "created", "first_name").
		From("users").
		Where("company = ?", 20).
		Where(sq.Gt{"created": 0}).
		ToSQL()
	fmt.Println(sql)
	fmt.Println(args)
}

Output:
SELECT id, created, first_name FROM users WHERE company = ? AND created > ?
[20 0]

type Sqlizer 

type Sqlizer interface {
	ToSQL() (string, []any, error)
}

Sqlizer is the interface that wraps the ToSQL method.

ToSQL returns a SQL representation of the Sqlizer, along with a slice of args as passed to e.g. database/sql.Exec. It can also return an error.

func Alias 

func Alias(expr Sqlizer, alias string) Sqlizer

Alias allows to define alias for column in SelectBuilder. Useful when column is defined as complex expression like IF or CASE Ex: 

.Column(Alias(caseStmt, "case_column"))
Example 
package main

import (
	"fmt"

	sq "github.com/alexZaicev/squirrel"
)

func main() {
	caseStmt := sq.Case("status").
		When("1", "'active'").
		When("2", "'inactive'")
	sql, _, _ := sq.Select().
		Column(sq.Alias(caseStmt, "status_text")).
		From("users").
		ToSQL()
	fmt.Println(sql)
}

Output:
SELECT (CASE status WHEN 1 THEN 'active' WHEN 2 THEN 'inactive' END) AS status_text FROM users

func ConcatExpr 

func ConcatExpr(parts ...any) Sqlizer

ConcatExpr builds an expression by concatenating strings and other expressions.

Ex: 

name_expr := Expr("CONCAT(?, ' ', ?)", firstName, lastName)
ConcatExpr("COALESCE(full_name,", name_expr, ")")
Example 
package main

import (
	"fmt"

	sq "github.com/alexZaicev/squirrel"
)

func main() {
	nameExpr := sq.Expr("CONCAT(?, ' ', ?)", "first", "last")
	sql, args, _ := sq.ConcatExpr("COALESCE(full_name, ", nameExpr, ")").ToSQL()
	fmt.Println(sql)
	fmt.Println(args)
}

Output:
COALESCE(full_name, CONCAT(?, ' ', ?))
[first last]

func Exists added in v0.1.0 

func Exists(subquery Sqlizer) Sqlizer

Exists builds an EXISTS (subquery) expression for use with Where/Having methods.

Ex: 

sub := sq.Select("1").From("orders").Where("orders.user_id = users.id")
sq.Select("*").From("users").Where(sq.Exists(sub))
// SELECT * FROM users WHERE EXISTS (SELECT 1 FROM orders WHERE orders.user_id = users.id)
Example 
package main

import (
	"fmt"

	sq "github.com/alexZaicev/squirrel"
)

func main() {
	sub := sq.Select("1").From("orders").Where("orders.user_id = users.id")
	sql, _, _ := sq.Select("*").
		From("users").
		Where(sq.Exists(sub)).
		ToSQL()
	fmt.Println(sql)
}

Output:
SELECT * FROM users WHERE EXISTS (SELECT 1 FROM orders WHERE orders.user_id = users.id)

func Expr 

func Expr(sql string, args ...any) Sqlizer

Expr builds an expression from a SQL fragment and arguments.

Ex: 

Expr("FROM_UNIXTIME(?)", t)
Example 
package main

import (
	"fmt"

	sq "github.com/alexZaicev/squirrel"
)

func main() {
	sql, args, _ := sq.Expr("COUNT(*) > ?", 5).ToSQL()
	fmt.Println(sql)
	fmt.Println(args)
}

Output:
COUNT(*) > ?
[5]

func NotExists added in v0.1.0 

func NotExists(subquery Sqlizer) Sqlizer

NotExists builds a NOT EXISTS (subquery) expression for use with Where/Having methods.

Ex: 

sub := sq.Select("1").From("orders").Where("orders.user_id = users.id")
sq.Select("*").From("users").Where(sq.NotExists(sub))
// SELECT * FROM users WHERE NOT EXISTS (SELECT 1 FROM orders WHERE orders.user_id = users.id)
Example 
package main

import (
	"fmt"

	sq "github.com/alexZaicev/squirrel"
)

func main() {
	sub := sq.Select("1").From("orders").Where("orders.user_id = users.id")
	sql, _, _ := sq.Select("*").
		From("users").
		Where(sq.NotExists(sub)).
		ToSQL()
	fmt.Println(sql)
}

Output:
SELECT * FROM users WHERE NOT EXISTS (SELECT 1 FROM orders WHERE orders.user_id = users.id)

type StatementBuilderType 

type StatementBuilderType builder.Builder

StatementBuilderType is the type of StatementBuilder.

func (StatementBuilderType) Delete 

func (b StatementBuilderType) Delete(from string) DeleteBuilder

Delete returns a DeleteBuilder for this StatementBuilderType.

func (StatementBuilderType) Insert 

func (b StatementBuilderType) Insert(into string) InsertBuilder

Insert returns a InsertBuilder for this StatementBuilderType.

func (StatementBuilderType) PlaceholderFormat 

func (b StatementBuilderType) PlaceholderFormat(f PlaceholderFormat) StatementBuilderType

PlaceholderFormat sets the PlaceholderFormat field for any child builders.

func (StatementBuilderType) Replace 

func (b StatementBuilderType) Replace(into string) InsertBuilder

Replace returns a InsertBuilder for this StatementBuilderType with the statement keyword set to "REPLACE".

func (StatementBuilderType) RunWith 

func (b StatementBuilderType) RunWith(runner BaseRunner) StatementBuilderType

RunWith sets the RunWith field for any child builders.

func (StatementBuilderType) Select 

func (b StatementBuilderType) Select(columns ...string) SelectBuilder

Select returns a SelectBuilder for this StatementBuilderType.

func (StatementBuilderType) Update 

func (b StatementBuilderType) Update(table string) UpdateBuilder

Update returns a UpdateBuilder for this StatementBuilderType.

func (StatementBuilderType) Where 

func (b StatementBuilderType) Where(pred any, args ...any) StatementBuilderType

Where adds WHERE expressions to the query.

See SelectBuilder.Where for more information.

type StdSQL added in v0.0.2 

type StdSQL interface {
	Query(string, ...any) (*sql.Rows, error)
	QueryRow(string, ...any) *sql.Row
	Exec(string, ...any) (sql.Result, error)
}

StdSQL encompasses the standard methods of the *sql.DB type, and other types that wrap these methods.

type StdSQLCtx added in v0.0.2 

type StdSQLCtx interface {
	StdSQL
	QueryContext(context.Context, string, ...any) (*sql.Rows, error)
	QueryRowContext(context.Context, string, ...any) *sql.Row
	ExecContext(context.Context, string, ...any) (sql.Result, error)
}

StdSQLCtx encompasses the standard methods of the *sql.DB type, along with the Context versions of those methods, and other types that wrap these methods.

type StmtCache 

type StmtCache struct {
	// contains filtered or unexported fields
}

StmtCache wraps and delegates down to a Preparer type

It also automatically prepares all statements sent to the underlying Preparer calls for Exec, Query and QueryRow and caches the returns *sql.Stmt using the provided query as the key. So that it can be automatically re-used.

func NewStmtCache 

func NewStmtCache(prep PreparerContext) *StmtCache

NewStmtCache returns a *StmtCache wrapping a PreparerContext that caches Prepared Stmts.

Stmts are cached based on the string value of their queries.

func (*StmtCache) Clear 

func (sc *StmtCache) Clear() (err error)

Clear removes and closes all the currently cached prepared statements

func (*StmtCache) Exec 

func (sc *StmtCache) Exec(query string, args ...any) (res sql.Result, err error)

Exec delegates down to the underlying Preparer using a prepared statement

func (*StmtCache) ExecContext 

func (sc *StmtCache) ExecContext(ctx context.Context, query string, args ...any) (res sql.Result, err error)

ExecContext delegates down to the underlying PreparerContext using a prepared statement

func (*StmtCache) Prepare 

func (sc *StmtCache) Prepare(query string) (*sql.Stmt, error)

Prepare delegates down to the underlying Preparer and caches the result using the provided query as a key

func (*StmtCache) PrepareContext 

func (sc *StmtCache) PrepareContext(ctx context.Context, query string) (*sql.Stmt, error)

PrepareContext delegates down to the underlying PreparerContext and caches the result using the provided query as a key

func (*StmtCache) Query 

func (sc *StmtCache) Query(query string, args ...any) (rows *sql.Rows, err error)

Query delegates down to the underlying Preparer using a prepared statement

func (*StmtCache) QueryContext 

func (sc *StmtCache) QueryContext(ctx context.Context, query string, args ...any) (rows *sql.Rows, err error)

QueryContext delegates down to the underlying PreparerContext using a prepared statement

func (*StmtCache) QueryRow 

func (sc *StmtCache) QueryRow(query string, args ...any) RowScanner

QueryRow delegates down to the underlying Preparer using a prepared statement

func (*StmtCache) QueryRowContext 

func (sc *StmtCache) QueryRowContext(ctx context.Context, query string, args ...any) RowScanner

QueryRowContext delegates down to the underlying PreparerContext using a prepared statement

type UnionBuilder added in v0.0.4 

type UnionBuilder builder.Builder

UnionBuilder builds SQL UNION / UNION ALL / INTERSECT / EXCEPT statements.

func Except added in v0.0.4 

func Except(selects ...SelectBuilder) UnionBuilder

Except returns a new UnionBuilder combining the given SELECTs with EXCEPT.

Example 
package main

import (
	"fmt"

	sq "github.com/alexZaicev/squirrel"
)

func main() {
	q1 := sq.Select("id").From("t1")
	q2 := sq.Select("id").From("t2")

	sql, _, _ := sq.Except(q1, q2).ToSQL()
	fmt.Println(sql)
}

Output:
SELECT id FROM t1 EXCEPT SELECT id FROM t2

func Intersect added in v0.0.4 

func Intersect(selects ...SelectBuilder) UnionBuilder

Intersect returns a new UnionBuilder combining the given SELECTs with INTERSECT.

Example 
package main

import (
	"fmt"

	sq "github.com/alexZaicev/squirrel"
)

func main() {
	q1 := sq.Select("id").From("t1")
	q2 := sq.Select("id").From("t2")

	sql, _, _ := sq.Intersect(q1, q2).ToSQL()
	fmt.Println(sql)
}

Output:
SELECT id FROM t1 INTERSECT SELECT id FROM t2

func Union added in v0.0.4 

func Union(selects ...SelectBuilder) UnionBuilder

Union returns a new UnionBuilder combining the given SELECTs with UNION.

Example 
package main

import (
	"fmt"

	sq "github.com/alexZaicev/squirrel"
)

func main() {
	q1 := sq.Select("id", "name").From("users").Where(sq.Eq{"active": true})
	q2 := sq.Select("id", "name").From("admins").Where(sq.Eq{"active": true})

	sql, args, _ := sq.Union(q1, q2).ToSQL()
	fmt.Println(sql)
	fmt.Println(args)
}

Output:
SELECT id, name FROM users WHERE active = ? UNION SELECT id, name FROM admins WHERE active = ?
[true true]

func UnionAll added in v0.0.4 

func UnionAll(selects ...SelectBuilder) UnionBuilder

UnionAll returns a new UnionBuilder combining the given SELECTs with UNION ALL.

Example 
package main

import (
	"fmt"

	sq "github.com/alexZaicev/squirrel"
)

func main() {
	q1 := sq.Select("id").From("t1")
	q2 := sq.Select("id").From("t2")

	sql, _, _ := sq.UnionAll(q1, q2).ToSQL()
	fmt.Println(sql)
}

Output:
SELECT id FROM t1 UNION ALL SELECT id FROM t2

func (UnionBuilder) Except added in v0.0.4 

func (b UnionBuilder) Except(selects ...SelectBuilder) UnionBuilder

Except adds one or more SELECT queries joined by EXCEPT.

func (UnionBuilder) Exec added in v0.0.4 

func (b UnionBuilder) Exec() (sql.Result, error)

Exec builds and Execs the query with the Runner set by RunWith.

func (UnionBuilder) ExecContext added in v0.0.4 

func (b UnionBuilder) ExecContext(ctx context.Context) (sql.Result, error)

ExecContext builds and ExecContexts the query with the Runner set by RunWith.

func (UnionBuilder) Intersect added in v0.0.4 

func (b UnionBuilder) Intersect(selects ...SelectBuilder) UnionBuilder

Intersect adds one or more SELECT queries joined by INTERSECT.

func (UnionBuilder) Limit added in v0.0.4 

func (b UnionBuilder) Limit(limit uint64) UnionBuilder

Limit sets a LIMIT clause on the combined result.

func (UnionBuilder) MustSQL added in v0.0.4 

func (b UnionBuilder) MustSQL() (string, []any)

MustSQL builds the query into a SQL string and bound args. It panics if there are any errors.

func (UnionBuilder) Offset added in v0.0.4 

func (b UnionBuilder) Offset(offset uint64) UnionBuilder

Offset sets an OFFSET clause on the combined result.

func (UnionBuilder) OrderBy added in v0.0.4 

func (b UnionBuilder) OrderBy(orderBys ...string) UnionBuilder

OrderBy adds ORDER BY expressions to the combined result.

Example 
package main

import (
	"fmt"

	sq "github.com/alexZaicev/squirrel"
)

func main() {
	q1 := sq.Select("id", "name").From("t1")
	q2 := sq.Select("id", "name").From("t2")

	sql, args, _ := sq.Union(q1, q2).
		OrderBy("name").
		Limit(10).
		Offset(5).
		ToSQL()
	fmt.Println(sql)
	fmt.Println(args)
}

Output:
SELECT id, name FROM t1 UNION SELECT id, name FROM t2 ORDER BY name LIMIT ? OFFSET ?
[10 5]

func (UnionBuilder) OrderByClause added in v0.0.4 

func (b UnionBuilder) OrderByClause(pred any, args ...any) UnionBuilder

OrderByClause adds an ORDER BY clause to the combined result.

func (UnionBuilder) PlaceholderFormat added in v0.0.4 

func (b UnionBuilder) PlaceholderFormat(f PlaceholderFormat) UnionBuilder

PlaceholderFormat sets PlaceholderFormat (e.g. Question or Dollar) for the query.

func (UnionBuilder) Prefix added in v0.0.4 

func (b UnionBuilder) Prefix(sql string, args ...any) UnionBuilder

Prefix adds an expression to the beginning of the query.

func (UnionBuilder) PrefixExpr added in v0.0.4 

func (b UnionBuilder) PrefixExpr(expr Sqlizer) UnionBuilder

PrefixExpr adds an expression to the very beginning of the query.

func (UnionBuilder) Query added in v0.0.4 

func (b UnionBuilder) Query() (*sql.Rows, error)

Query builds and Querys the query with the Runner set by RunWith.

func (UnionBuilder) QueryContext added in v0.0.4 

func (b UnionBuilder) QueryContext(ctx context.Context) (*sql.Rows, error)

QueryContext builds and QueryContexts the query with the Runner set by RunWith.

func (UnionBuilder) QueryRow added in v0.0.4 

func (b UnionBuilder) QueryRow() RowScanner

QueryRow builds and QueryRows the query with the Runner set by RunWith.

func (UnionBuilder) QueryRowContext added in v0.0.4 

func (b UnionBuilder) QueryRowContext(ctx context.Context) RowScanner

QueryRowContext builds and QueryRowContexts the query with the Runner set by RunWith.

func (UnionBuilder) RemoveLimit added in v0.0.4 

func (b UnionBuilder) RemoveLimit() UnionBuilder

RemoveLimit removes the LIMIT clause.

func (UnionBuilder) RemoveOffset added in v0.0.4 

func (b UnionBuilder) RemoveOffset() UnionBuilder

RemoveOffset removes the OFFSET clause.

func (UnionBuilder) RunWith added in v0.0.4 

func (b UnionBuilder) RunWith(runner BaseRunner) UnionBuilder

RunWith sets a Runner (like database/sql.DB) to be used with e.g. Exec.

func (UnionBuilder) Scan added in v0.0.4 

func (b UnionBuilder) Scan(dest ...any) error

Scan is a shortcut for QueryRow().Scan.

func (UnionBuilder) ScanContext added in v0.0.4 

func (b UnionBuilder) ScanContext(ctx context.Context, dest ...any) error

ScanContext is a shortcut for QueryRowContext().Scan.

func (UnionBuilder) Suffix added in v0.0.4 

func (b UnionBuilder) Suffix(sql string, args ...any) UnionBuilder

Suffix adds an expression to the end of the query.

func (UnionBuilder) SuffixExpr added in v0.0.4 

func (b UnionBuilder) SuffixExpr(expr Sqlizer) UnionBuilder

SuffixExpr adds an expression to the end of the query.

func (UnionBuilder) ToSQL added in v0.0.4 

func (b UnionBuilder) ToSQL() (string, []any, error)

ToSQL builds the query into a SQL string and bound args.

func (UnionBuilder) Union added in v0.0.4 

func (b UnionBuilder) Union(selects ...SelectBuilder) UnionBuilder

Union adds one or more SELECT queries joined by UNION.

func (UnionBuilder) UnionAll added in v0.0.4 

func (b UnionBuilder) UnionAll(selects ...SelectBuilder) UnionBuilder

UnionAll adds one or more SELECT queries joined by UNION ALL.

type UpdateBuilder 

type UpdateBuilder builder.Builder

UpdateBuilder builds SQL UPDATE statements.

func Update 

func Update(table string) UpdateBuilder

Update returns a new UpdateBuilder with the given table name.

See UpdateBuilder.Table.

Example 
package main

import (
	"fmt"

	sq "github.com/alexZaicev/squirrel"
)

func main() {
	sql, args, _ := sq.Update("users").
		Set("name", "Alice").
		Set("age", 30).
		Where("id = ?", 1).
		ToSQL()
	fmt.Println(sql)
	fmt.Println(args)
}

Output:
UPDATE users SET name = ?, age = ? WHERE id = ?
[Alice 30 1]

func (UpdateBuilder) CrossJoin added in v0.5.0 

func (b UpdateBuilder) CrossJoin(join string, rest ...any) UpdateBuilder

CrossJoin adds a CROSS JOIN clause to the query.

WARNING: The join clause is interpolated directly into the SQL string. NEVER pass unsanitized user input to this method.

func (UpdateBuilder) CrossJoinUsing added in v0.5.0 

func (b UpdateBuilder) CrossJoinUsing(table string, columns ...string) UpdateBuilder

CrossJoinUsing adds a CROSS JOIN ... USING clause to the query.

func (UpdateBuilder) Exec 

func (b UpdateBuilder) Exec() (sql.Result, error)

Exec builds and Execs the query with the Runner set by RunWith.

func (UpdateBuilder) ExecContext 

func (b UpdateBuilder) ExecContext(ctx context.Context) (sql.Result, error)

ExecContext builds and ExecContexts the query with the Runner set by RunWith.

func (UpdateBuilder) From 

func (b UpdateBuilder) From(from string) UpdateBuilder

From adds FROM clause to the query FROM is valid construct in postgresql only.

WARNING: The table name is interpolated directly into the SQL string without sanitization. NEVER pass unsanitized user input to this method.

Example 
package main

import (
	"fmt"

	sq "github.com/alexZaicev/squirrel"
)

func main() {
	sql, args, _ := sq.Update("users").
		Set("status", "active").
		From("accounts").
		Where("users.account_id = accounts.id AND accounts.verified = ?", true).
		PlaceholderFormat(sq.Dollar).
		ToSQL()
	fmt.Println(sql)
	fmt.Println(args)
}

Output:
UPDATE users SET status = $1 FROM accounts WHERE users.account_id = accounts.id AND accounts.verified = $2
[active true]

func (UpdateBuilder) FromSelect 

func (b UpdateBuilder) FromSelect(from SelectBuilder, alias string) UpdateBuilder

FromSelect sets a subquery into the FROM clause of the query.

func (UpdateBuilder) FromValues added in v0.6.0 

func (b UpdateBuilder) FromValues(values [][]interface{}, alias string, columns ...string) UpdateBuilder

FromValues sets a VALUES list into the FROM clause of the query. This enables PostgreSQL-style UPDATE ... FROM (VALUES ...) for bulk updates.

Ex: 

sq.Update("employees").
	Set("name", Expr("v.name")).
	FromValues(
		[][]interface{}{{1, "Alice"}, {2, "Bob"}},
		"v", "id", "name",
	).
	Where("employees.id = v.id")
// UPDATE employees SET name = v.name
//   FROM (VALUES (?, ?), (?, ?)) AS v(id, name)
//   WHERE employees.id = v.id
Example 
package main

import (
	"fmt"

	sq "github.com/alexZaicev/squirrel"
)

func main() {
	sql, args, _ := sq.Update("t").
		Set("name", sq.Expr("v.name")).
		Set("salary", sq.Expr("v.salary")).
		FromValues(
			[][]interface{}{{1, "Alice", 50000}, {2, "Bob", 60000}},
			"v", "id", "name", "salary",
		).
		Where("t.id = v.id").
		PlaceholderFormat(sq.Dollar).
		ToSQL()
	fmt.Println(sql)
	fmt.Println(args)
}

Output:
UPDATE t SET name = v.name, salary = v.salary FROM (VALUES ($1::bigint, $2::text, $3::bigint), ($4, $5, $6)) AS v(id, name, salary) WHERE t.id = v.id
[1 Alice 50000 2 Bob 60000]

func (UpdateBuilder) FullJoin added in v0.5.0 

func (b UpdateBuilder) FullJoin(join string, rest ...any) UpdateBuilder

FullJoin adds a FULL OUTER JOIN clause to the query.

WARNING: The join clause is interpolated directly into the SQL string. NEVER pass unsanitized user input to this method.

func (UpdateBuilder) FullJoinUsing added in v0.5.0 

func (b UpdateBuilder) FullJoinUsing(table string, columns ...string) UpdateBuilder

FullJoinUsing adds a FULL OUTER JOIN ... USING clause to the query.

func (UpdateBuilder) InnerJoin added in v0.5.0 

func (b UpdateBuilder) InnerJoin(join string, rest ...any) UpdateBuilder

InnerJoin adds an INNER JOIN clause to the query.

WARNING: The join clause is interpolated directly into the SQL string. NEVER pass unsanitized user input to this method.

func (UpdateBuilder) InnerJoinUsing added in v0.5.0 

func (b UpdateBuilder) InnerJoinUsing(table string, columns ...string) UpdateBuilder

InnerJoinUsing adds an INNER JOIN ... USING clause to the query.

func (UpdateBuilder) Join added in v0.5.0 

func (b UpdateBuilder) Join(join string, rest ...any) UpdateBuilder

Join adds a JOIN clause to the query.

WARNING: The join clause is interpolated directly into the SQL string. NEVER pass unsanitized user input to this method.

Example 
package main

import (
	"fmt"

	sq "github.com/alexZaicev/squirrel"
)

func main() {
	sql, args, _ := sq.Update("orders").
		Join("customers ON orders.customer_id = customers.id").
		Set("orders.status", "verified").
		Where("customers.verified = ?", true).
		ToSQL()
	fmt.Println(sql)
	fmt.Println(args)
}

Output:
UPDATE orders JOIN customers ON orders.customer_id = customers.id SET orders.status = ? WHERE customers.verified = ?
[verified true]

func (UpdateBuilder) JoinClause added in v0.5.0 

func (b UpdateBuilder) JoinClause(pred any, args ...any) UpdateBuilder

JoinClause adds a join clause to the query.

Example 
package main

import (
	"fmt"

	sq "github.com/alexZaicev/squirrel"
)

func main() {
	sql, args, _ := sq.Update("orders").
		JoinClause(
			sq.JoinExpr("customers").
				Type(sq.JoinInner).
				On("orders.customer_id = customers.id").
				On("customers.active = ?", true),
		).
		Set("orders.status", "verified").
		ToSQL()
	fmt.Println(sql)
	fmt.Println(args)
}

Output:
UPDATE orders JOIN customers ON orders.customer_id = customers.id AND customers.active = ? SET orders.status = ?
[true verified]

func (UpdateBuilder) JoinUsing added in v0.5.0 

func (b UpdateBuilder) JoinUsing(table string, columns ...string) UpdateBuilder

JoinUsing adds a JOIN ... USING clause to the query.

func (UpdateBuilder) LeftJoin added in v0.5.0 

func (b UpdateBuilder) LeftJoin(join string, rest ...any) UpdateBuilder

LeftJoin adds a LEFT JOIN clause to the query.

WARNING: The join clause is interpolated directly into the SQL string. NEVER pass unsanitized user input to this method.

Example 
package main

import (
	"fmt"

	sq "github.com/alexZaicev/squirrel"
)

func main() {
	sql, args, _ := sq.Update("items").
		LeftJoin("inventory ON items.id = inventory.item_id").
		Set("items.in_stock", false).
		Where("inventory.item_id IS NULL").
		ToSQL()
	fmt.Println(sql)
	fmt.Println(args)
}

Output:
UPDATE items LEFT JOIN inventory ON items.id = inventory.item_id SET items.in_stock = ? WHERE inventory.item_id IS NULL
[false]

func (UpdateBuilder) LeftJoinUsing added in v0.5.0 

func (b UpdateBuilder) LeftJoinUsing(table string, columns ...string) UpdateBuilder

LeftJoinUsing adds a LEFT JOIN ... USING clause to the query.

func (UpdateBuilder) Limit 

func (b UpdateBuilder) Limit(limit uint64) UpdateBuilder

Limit sets a LIMIT clause on the query.

func (UpdateBuilder) MustSQL added in v0.0.2 

func (b UpdateBuilder) MustSQL() (string, []any)

MustSQL builds the query into a SQL string and bound args. It panics if there are any errors.

func (UpdateBuilder) Offset 

func (b UpdateBuilder) Offset(offset uint64) UpdateBuilder

Offset sets a OFFSET clause on the query.

func (UpdateBuilder) OrderBy 

func (b UpdateBuilder) OrderBy(orderBys ...string) UpdateBuilder

OrderBy adds ORDER BY expressions to the query.

WARNING: Order-by expressions are interpolated directly into the SQL string. NEVER pass unsanitized user input to this method.

Example 
package main

import (
	"fmt"

	sq "github.com/alexZaicev/squirrel"
)

func main() {
	sql, args, _ := sq.Update("users").
		Set("name", "Alice").
		OrderBy("id").
		Limit(10).
		ToSQL()
	fmt.Println(sql)
	fmt.Println(args)
}

Output:
UPDATE users SET name = ? ORDER BY id LIMIT ?
[Alice 10]

func (UpdateBuilder) PlaceholderFormat 

func (b UpdateBuilder) PlaceholderFormat(f PlaceholderFormat) UpdateBuilder

PlaceholderFormat sets PlaceholderFormat (e.g. Question or Dollar) for the query.

func (UpdateBuilder) Prefix 

func (b UpdateBuilder) Prefix(sql string, args ...any) UpdateBuilder

Prefix adds an expression to the beginning of the query

func (UpdateBuilder) PrefixExpr 

func (b UpdateBuilder) PrefixExpr(expr Sqlizer) UpdateBuilder

PrefixExpr adds an expression to the very beginning of the query

func (UpdateBuilder) Query 

func (b UpdateBuilder) Query() (*sql.Rows, error)

func (UpdateBuilder) QueryContext 

func (b UpdateBuilder) QueryContext(ctx context.Context) (*sql.Rows, error)

QueryContext builds and QueryContexts the query with the Runner set by RunWith.

func (UpdateBuilder) QueryRow 

func (b UpdateBuilder) QueryRow() RowScanner

func (UpdateBuilder) QueryRowContext 

func (b UpdateBuilder) QueryRowContext(ctx context.Context) RowScanner

QueryRowContext builds and QueryRowContexts the query with the Runner set by RunWith.

func (UpdateBuilder) Returning added in v0.0.6 

func (b UpdateBuilder) Returning(columns ...string) UpdateBuilder

Returning adds RETURNING expressions to the query.

Ex: 

Update("users").Set("name", "John").Where("id = ?", 1).
	Returning("id", "name")
// UPDATE users SET name = ? WHERE id = ? RETURNING id, name
Example 
package main

import (
	"fmt"

	sq "github.com/alexZaicev/squirrel"
)

func main() {
	sql, args, _ := sq.Update("users").
		Set("name", "Alice").
		Where("id = ?", 1).
		Returning("id", "name").
		PlaceholderFormat(sq.Dollar).
		ToSQL()
	fmt.Println(sql)
	fmt.Println(args)
}

Output:
UPDATE users SET name = $1 WHERE id = $2 RETURNING id, name
[Alice 1]

func (UpdateBuilder) RightJoin added in v0.5.0 

func (b UpdateBuilder) RightJoin(join string, rest ...any) UpdateBuilder

RightJoin adds a RIGHT JOIN clause to the query.

WARNING: The join clause is interpolated directly into the SQL string. NEVER pass unsanitized user input to this method.

func (UpdateBuilder) RightJoinUsing added in v0.5.0 

func (b UpdateBuilder) RightJoinUsing(table string, columns ...string) UpdateBuilder

RightJoinUsing adds a RIGHT JOIN ... USING clause to the query.

func (UpdateBuilder) RunWith 

func (b UpdateBuilder) RunWith(runner BaseRunner) UpdateBuilder

RunWith sets a Runner (like database/sql.DB) to be used with e.g. Exec.

func (UpdateBuilder) SafeSet added in v0.4.0 

func (b UpdateBuilder) SafeSet(column Ident, value any) UpdateBuilder

SafeSet adds a SET clause with a safe Ident column name.

Ex: 

col, _ := sq.QuoteIdent(userInput)
sq.Update("users").SafeSet(col, "value").Where("id = ?", 1)
Example 
package main

import (
	"fmt"

	sq "github.com/alexZaicev/squirrel"
)

func main() {
	col, _ := sq.QuoteIdent("name")
	sql, args, _ := sq.Update("users").SafeSet(col, "moe").Where("id = ?", 1).ToSQL()
	fmt.Println(sql)
	fmt.Println(args)
}

Output:
UPDATE users SET "name" = ? WHERE id = ?
[moe 1]

func (UpdateBuilder) SafeTable added in v0.4.0 

func (b UpdateBuilder) SafeTable(table Ident) UpdateBuilder

SafeTable sets the table to be updated using a safe Ident.

Ex: 

id, _ := sq.QuoteIdent(userInput)
sq.Update("").SafeTable(id).Set("name", "John").Where("id = ?", 1)
Example 
package main

import (
	"fmt"

	sq "github.com/alexZaicev/squirrel"
)

func main() {
	table, _ := sq.QuoteIdent("users")
	sql, args, _ := sq.Update("").SafeTable(table).Set("name", "moe").Where("id = ?", 1).ToSQL()
	fmt.Println(sql)
	fmt.Println(args)
}

Output:
UPDATE "users" SET name = ? WHERE id = ?
[moe 1]

func (UpdateBuilder) Scan 

func (b UpdateBuilder) Scan(dest ...any) error

func (UpdateBuilder) ScanContext 

func (b UpdateBuilder) ScanContext(ctx context.Context, dest ...any) error

ScanContext is a shortcut for QueryRowContext().Scan.

func (UpdateBuilder) Set 

func (b UpdateBuilder) Set(column string, value any) UpdateBuilder

Set adds SET clauses to the query.

WARNING: The column name is interpolated directly into the SQL string without sanitization. NEVER pass unsanitized user input as the column argument. For dynamic column names from user input, use SafeSet instead.

func (UpdateBuilder) SetMap 

func (b UpdateBuilder) SetMap(clauses map[string]any) UpdateBuilder

SetMap is a convenience method which calls .Set for each key/value pair in clauses.

Example 
package main

import (
	"fmt"

	sq "github.com/alexZaicev/squirrel"
)

func main() {
	sql, args, _ := sq.Update("users").
		SetMap(map[string]interface{}{
			"age":  30,
			"name": "Alice",
		}).
		Where("id = ?", 1).
		ToSQL()
	fmt.Println(sql)
	fmt.Println(args)
}

Output:
UPDATE users SET age = ?, name = ? WHERE id = ?
[30 Alice 1]

func (UpdateBuilder) Suffix 

func (b UpdateBuilder) Suffix(sql string, args ...any) UpdateBuilder

Suffix adds an expression to the end of the query

func (UpdateBuilder) SuffixExpr 

func (b UpdateBuilder) SuffixExpr(expr Sqlizer) UpdateBuilder

SuffixExpr adds an expression to the end of the query

func (UpdateBuilder) Table 

func (b UpdateBuilder) Table(table string) UpdateBuilder

Table sets the table to be updated.

WARNING: The table name is interpolated directly into the SQL string without sanitization. NEVER pass unsanitized user input to this method. For dynamic table names from user input, use SafeTable instead.

func (UpdateBuilder) ToSQL added in v0.0.2 

func (b UpdateBuilder) ToSQL() (string, []any, error)

ToSQL builds the query into a SQL string and bound args.

func (UpdateBuilder) Where 

func (b UpdateBuilder) Where(pred any, args ...any) UpdateBuilder

Where adds WHERE expressions to the query.

See SelectBuilder.Where for more information.

Source Files

View all Source files

Jump to

Keyboard shortcuts

? : This menu
/ : Search site
f or F : Jump to
y or Y : Canonical URL
go.dev uses cookies from Google to deliver and enhance the quality of its services and to analyze traffic. Learn more.