xsql

package
v0.25.159 Latest Latest
Warning

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

 Go to latest Published: Sep 8, 2026 License: MIT, MIT Imports: 9 Imported by: 1

README

xsql

A fast SQL query builder for Go.

Fork from https://github.com/leporo/xsql

What xsql does?

  • It helps you efficiently build an SQL statement in run-time.
  • You may change the number of affected columns and change the number of arguments in a safe way.
  • You may use SQL expressions (like UPDATE counters SET counter = counter + 1) in your SQL statements.
  • You may dynamically apply filters by adding where conditions, change result ordering, etc.
  • You may safely use ? placeholders in your SQL fragments - xsql converts them to PostgreSQL-like $1, $2, ... placeholders if needed and does the numbering for you.
  • You may .Bind your structure to database columns like you do with other similar libraries.
  • xsql.Stmt has methods to execute a query using any database/sql compatible driver.

What xsql doesn't?

  • xsql isn't an ORM, you'll still have to use raw SQL.
  • There are no database schema migrations or any other database schema maintenance tools.
  • There are no compile-time type checks for query arguments, column and table names.
  • There is no wrapper for OR clause. It affects performance and in most cases can be avoided by using UNION expressions, WITH clause or window functions. Other option is to split a query into two.
  • xsql doesn't help a developer to pinpoint the cause of issue with SQL statement.

Is It Fast?

It is.

In order to maximize performance and minimize memory footprint, xsql reuses memory allocated for query building. Always Close() a statement (or use QueryAndClose / ExecAndClose) so buffers return to the pool. Rendered SQL is cached per dialect, with a bounded cache to keep memory in check. Use SetName or GetOrCreateQuery for hot queries.

Bind caches field mappings per struct type. Prefer Select("col").To(&field) when you need the last bit of speed.

Concurrency

Dialect values (xsql.Postgres, xsql.NoDialect, xsql.SQLServer) are safe for concurrent use. A Builder / Stmt is not. Create a new statement per goroutine, or Clone a template:

template := xsql.Postgres.From("users").Select("id, name")
defer template.Close()

q := template.Clone().Where("id = ?", id)
defer q.Close()

UseNewLines(false) returns a dialect view and does not change the process default. Prefer xsql.Postgres.WithNewLines(false) over mutating a shared dialect with UseNewLines.

Usage

Build complex statements:

var (
    region       string
    product      string
    productUnits int
    productSales float64
)

xsql.SetDialect(xsql.Postgres)

err := xsql.From("orders").
    With("regional_sales",
        xsql.From("orders").
            Select("region, SUM(amount) AS total_sales").
            GroupBy("region")).
    With("top_regions",
        xsql.From("regional_sales").
            Select("region").
            Where("total_sales > (SELECT SUM(total_sales)/10 FROM regional_sales)")).
    // Map query fields to variables
    Select("region").To(&region).
    Select("product").To(&product).
    Select("SUM(quantity)").To(&productUnits).
    Select("SUM(amount) AS product_sales").To(&productSales).
    //
    Where("region IN (SELECT region FROM top_regions)").
    GroupBy("region, product").
    OrderBy("product_sales DESC").
    // Execute the query
    QueryAndClose(ctx, db, func(row *sql.Rows){
        // Callback function is called for every returned row.
        // Row values are scanned automatically to bound variables.
        fmt.Printf("%s\t%s\t%d\t$%.2f\n", region, product, productUnits, productSales)
    })
if err != nil {
    panic(err)
}

Bind a structure:

type Offer struct {
    Id        int64   `db:"id"`
    ProductId int64   `db:"product_id"`
    Price     float64 `db:"price"`
    IsDeleted bool    `db:"is_deleted"`
}

var o Offer

err := xsql.From("offers").
    Bind(&o).
    Where("id = ?", 42).
    QueryRowAndClose(ctx, db)
if err != nil {
    panic(err)
}

Retrieve data to private fields with more granular control on retrieved fields:

type Offer struct {
    id        int64
    productId int64
    price     float64
    isDeleted bool
}

var o Offer

err := xsql.From("offers").
    Select("id").To(&o.id).
    Select("product_id").To(&o.productId).
    Select("price").To(&o.price).
    Select("is_deleted").To(&o.isDeleted).
    Where("id = ?", 42).
    QueryRowAndClose(ctx, db)
if err != nil {
    panic(err)
}

Some SQL fragments, like a list of fields to be selected or filtering condition may appear over and over. It can be annoying to repeat them or combine an SQL statement from chunks. Use xsql.Stmt to construct a basic query and extend it for a case:

func (o *Offer) Select() xsql.Builder {
    return xsql.From("products").
        Bind(o).
        // Ignore records, marked as deleted
        Where("is_deleted = false")
}

func (o Offer) Print() {
    fmt.Printf("%d\t%s\t$%.2f\n", o.id, o.name, o.price)
}

var o Offer

// Fetch offer data
err := o.Select().
    Where("id = ?", offerId).
    QueryRowAndClose(ctx, db)
if err != nil {
    panic(err)
}
o.Print()
// ...

// Select and print 5 most recently placed
// offers for a given product
err = o.Select().
    Where("product_id = ?", productId).
    OrderBy("id DESC").
    Limit(5).
    QueryAndClose(ctx, db, func(row *sql.Rows){
        o.Print()
    })
if err != nil {
    panic(err)
}
// ...

SQL Statement Construction and Execution

SELECT
Value Binding

Bind columns to values using To method:

var (
    minAmountRequested = true
    maxAmount float64
    minAmount float64
)

q := xsql.From("offers").
    Select("MAX(amount)").To(&maxAmount).
    Where("is_deleted = false")

if minAmountRequested {
    q.Select("MIN(amount)").To(&minAmount)
}

err := q.QueryRowAndClose(ctx, db)
if err != nil {
    panic(err)
}
if minAmountRequested {
    fmt.Printf("Cheapest offer: $%.2f\n", minAmount)
}
fmt.Printf("Most expensive offer: $%.2f\n", minAmount)
Joins

There are helper methods to construct a JOIN clause: Join, LeftJoin, RightJoin and FullJoin.

var (
    offerId     int64
    productName string
    price       float64
}

err := xsql.From("offers o").
    Select("o.id").To(&offerId).
    Select("price").To(&price).
    Where("is_deleted = false").
    // Join
    LeftJoin("products p", "p.id = o.product_id").
    // Bind a column from joined table to variable
    Select("p.name").To(&productName).
    // Print top 10 offers
    OrderBy("price DESC").
    Limit(10).
    QueryAndClose(ctx, db, func(row *sql.Rows){
        fmt.Printf("%d\t%s\t$%.2f\n", offerId, productName, price)
    })
if err != nil {
    panic(err)
}

Use plain SQL for more fancy cases:

var (
    num   int64
    name  string
    value string
)
err := xsql.From("t1 CROSS JOIN t2 ON t1.num = t2.num AND t2.value IN (?, ?)", "xxx", "yyy").
    Select("t1.num").To(&num).
    Select("t1.name").To(&name).
    Select("t2.value").To(&value).
    QueryAndClose(ctx, db, func(row *sql.Rows){
        fmt.Printf("%d\t%s\ts\n", num, name, value)
    })
if err != nil {
    panic(err)
}
Subqueries

Use SubQuery method to add a sub query to a statement:

	q := xsql.From("orders o").
		Select("date, region").
		SubQuery("(", ") AS prev_order_date",
			xsql.From("orders po").
				Select("date").
				Where("region = o.region").
				Where("id < o.id").
				OrderBy("id DESC").
				Clause("LIMIT 1")).
		Where("date > CURRENT_DATE - interval '1 day'").
		OrderBy("id DESC")
	fmt.Println(q.String())
	q.Close()

Note that if a subquery uses no arguments, it's more effective to add it as SQL fragment:

	q := xsql.From("orders o").
		Select("date, region").
		Where("date > CURRENT_DATE - interval '1 day'").
        Where("exists (SELECT 1 FROM orders po WHERE region = o.region AND id < o.id ORDER BY id DESC LIMIT 1)").
        OrderBy("id DESC")
    // ...
    q.Close()

To select from sub-query pass an empty string to From and immediately call a SubQuery method.

The query constructed by the following example returns top 5 news in each section:

	q := xsql.Select("").
		From("").
		SubQuery(
			"(", ") counted_news",
			xsql.From("news").
				Select("id, section, header, score").
				Select("row_number() OVER (PARTITION BY section ORDER BY score DESC) AS rating_in_section").
				OrderBy("section, rating_in_section")).
		Where("rating_in_section <= 5")
    // ...
    q.Close()
Unions

Use Union method to combine results of two queries:

	q := xsql.From("tasks").
		Select("id, status").
		Where("status = ?", "new").
		Union(true, xsql.Postgres.From("tasks").
			Select("id, status").
            Where("status = ?", "wip"))
    // ...
	q.Close()
INSERT

xsql provides a Set method to be used both for UPDATE and INSERT statements:

var userId int64

err := xsql.Postgres.InsertInto("users").
    Set("email", "new@email.com").
    Set("address", "320 Some Avenue, Somewhereville, GA, US").
    OnConflict("(email) DO UPDATE SET address = EXCLUDED.address").
    Returning("id").To(&userId).
    QueryRowAndClose(ctx, db)

The same statement execution using the database/sql standard library looks like this:

var userId int64

// database/sql
err := db.ExecContext(ctx, "INSERT INTO users (email, address) VALUES ($1, $2) RETURNING id ON CONFLICT (email) DO UPDATE SET address = users.address", "new@email.com", "320 Some Avenue, Somewhereville, GA, US").Scan(&userId)

There are just 2 fields of a new database record to be populated, and yet it takes some time to figure out what columns are being updated and what values are to be assigned to them.

In real-world cases there are tens of fields. On any update both the list of field names and the list of values, passed to ExecContext method, have to to be reviewed and updated. It's a common thing to have values misplaced.

The use of Set method to maintain a field-value map is a way to solve this issue.

Bulk Insert

To insert a multiple rows via a single query, use NewRow method:

_, err := xsql.InsertInto("users").
    NewRow().
        Set("email", "first@email.com").
        Set("address", "320 Some Avenue, Somewhereville, GA, US").
    NewRow().
        Set("email", "second@email.com").
        Set("address", "320 Some Avenue, Somewhereville, GA, US").
    ExecAndClose(ctx, db)
UPDATE
_, err := xsql.Update("users").
    Set("email", "new@email.com").
    ExecAndClose(ctx, db)
DELETE
_, err := xsql.DeleteFrom("products").
    Where("id = ?", 42).
    ExecAndClose(ctx, db)
PostgreSQL helpers
q := xsql.Postgres.From("tasks").
    Select("id").
    Where("status").NotIn("done", "canceled").
    ForUpdate("SKIP LOCKED")

q = xsql.Postgres.InsertInto("users").
    Set("email", email).
    Set("name", name).
    OnConflict("(email) DO UPDATE SET name = EXCLUDED.name").
    Returning("id")

On the Postgres dialect, every ? in a fragment is numbered ($1, $2, ...), including fragments that have no bound arguments. Write \? for a literal ? (JSON operators, ?|, and similar).

Documentation

Overview

Package xsql is a fast SQL statement builder and executor.

Overview

xsql helps you assemble SQL at runtime from fragments and bound arguments:

  • Combine raw SQL fragments with matching arguments
  • Map selected columns to variables via To or Bind
  • Convert ? placeholders to PostgreSQL $1, $2, ... when using Postgres
  • Execute the statement through any database/sql-compatible Executor

It is not an ORM. Table and column names are not validated, and there is no wrapper for OR (prefer UNION, WITH, or a second query).

Dialects

NoDialect (the default) leaves placeholders as ?. Postgres rewrites them to numbered parameters. SQLServer leaves placeholders as ? for the Microsoft driver. 

q := xsql.Postgres.From("users").Select("id").Where("email = ?", email)
// SELECT id FROM users WHERE email = $1

SetDialect changes the process-wide default used by package-level constructors (From, Select, InsertInto, ...). Shared dialect values (Postgres, NoDialect, SQLServer) are safe for concurrent constructors.

Concurrency

A Dialect is safe for concurrent use. A Builder / Stmt is not. Create a new statement per goroutine, or Clone a template: 

template := xsql.Postgres.From("users").Select("id, name")
defer template.Close()

q := template.Clone().Where("id = ?", id)
defer q.Close()

UseNewLines on the package returns a dialect view and does not mutate the default. Prefer WithNewLines on a shared dialect over UseNewLines, which writes the receiver.

Resource reuse

Statements allocate buffers from a pool. Always Close a statement (or use QueryAndClose / QueryRowAndClose / ExecAndClose) so those buffers can be reused. Do not use a statement after Close.

Query cache

Rendered SQL is cached per dialect, keyed by the raw statement buffer and, when SetName is used, also by that name. The cache is bounded (see defaultMaxCachedQueries). Use SetName or GetOrCreateQuery for hot paths that rebuild the same SQL.

Example 
package main

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

	"github.com/effective-security/xdb/xsql"
)

type dummyDB int

func (db *dummyDB) ExecContext(ctx context.Context, query string, args ...any) (sql.Result, error) {
	return nil, nil
}

func (db *dummyDB) QueryContext(ctx context.Context, query string, args ...any) (*sql.Rows, error) {
	return nil, nil
}

func (db *dummyDB) QueryRowContext(ctx context.Context, query string, args ...any) *sql.Row {
	return nil
}

var db = new(dummyDB)
var ctx = context.Background()

func main() {
	var (
		region       string
		product      string
		productUnits int
		productSales float64
	)

	pg := xsql.Postgres.WithNewLines(false)
	err := pg.From("orders").
		With("regional_sales",
			pg.From("orders").
				Select("region, SUM(amount) AS total_sales").
				GroupBy("region")).
		With("top_regions",
			pg.From("regional_sales").
				Select("region").
				Where("total_sales > (SELECT SUM(total_sales)/10 FROM regional_sales)")).
		// Map query fields to variables
		Select("region").To(&region).
		Select("product").To(&product).
		Select("SUM(quantity)").To(&productUnits).
		Select("SUM(amount) AS product_sales").To(&productSales).
		//
		Where("region IN (SELECT region FROM top_regions)").
		GroupBy("region, product").
		OrderBy("product_sales DESC").
		// Execute the query
		QueryAndClose(ctx, db, func(row *sql.Rows) {
			// Callback function is called for every returned row.
			// Row values are scanned automatically to bound variables.
			fmt.Printf("%s\t%s\t%d\t$%.2f\n", region, product, productUnits, productSales)
		})
	if err != nil {
		panic(err)
	}
}

Index

Examples

Constants

This section is empty.

Variables

View Source 
var (
	// NoDialect is a default statement builder mode.
	NoDialect = SQLDialect(newDialect("default", true))
	// Postgres mode automatically replaces ? placeholders with $1, $2...
	Postgres = SQLDialect(newDialect("postgres", true))
	// SQLServer is a statement builder for SQL Server. Placeholders stay as ?.
	SQLServer = SQLDialect(newDialect("sqlserver", true))
)

Functions

func SetDialect 

func SetDialect(newDefaultDialect SQLDialect)

SetDialect selects a Dialect to be used by default.

Dialect can be one of xsql.NoDialect, xsql.Postgres or xsql.SQLServer. 

xsql.SetDialect(xsql.Postgres)

SetDialect is safe to call concurrently with statement constructors.

Types

type Builder 

type Builder interface {
	/*
		Args returns the list of arguments to be passed to
		database driver for statement execution.

		Do not access a slice returned by this method after Builder is closed.

		An array, a returned slice points to, can be altered by any method that
		adds a clause or an expression with arguments.

		Make sure to make a copy of the returned slice if you need to preserve it.
	*/
	Args() []any

	// Bind adds structure fields to SELECT statement.
	// Structure fields have to be annotated with "db" tag.
	// Reflect-based Bind is slightly slower than `Select("field").To(&record.field)`
	// but provides an easier way to retrieve data.
	//
	// Note: this method does no type checks and returns no errors.
	Bind(data any) Builder

	/*
		Clause appends a raw SQL fragment to the statement.

		Use it to add a raw SQL fragment like ON CONFLICT, ON DUPLICATE KEY, WINDOW, etc.

		An SQL fragment added via Clause method appears after the last clause previously
		added. If called first, Clause method prepends a statement with a raw SQL.
	*/
	Clause(expr string, args ...any) Builder

	// Clone creates a copy of the statement.
	// The copy does not inherit the cache name; call SetName if needed.
	Clone() Builder

	/*
		Close puts buffers and other objects allocated to build an SQL statement
		back to pool for reuse by other Builder instances.

		Builder instance should not be used after Close method call.
		A Builder is not safe for concurrent use.
	*/
	Close()

	/*
		DeleteFrom starts a DELETE statement.

			err := xsql.DeleteFrom("table").Where("id = ?", id).ExecAndClose(ctx, db)
	*/
	DeleteFrom(tableName string) Builder

	/*
		Dest returns a list of value pointers passed via To method calls.
		The order matches the constructed SQL statement.

		Do not access a slice returned by this method after Builder is closed.

		Note that an array, a returned slice points to, can be altered by To method
		calls.

		Make sure to make a copy if you need to preserve a slice returned by this method.
	*/
	Dest() []any

	// Exec executes the statement.
	Exec(ctx context.Context, db Executor) (sql.Result, error)

	// ExecAndClose executes the statement and releases all the objects
	// and buffers allocated by statement builder back to a pool.
	//
	// Do not call any Builder methods after this call.
	ExecAndClose(ctx context.Context, db Executor) (sql.Result, error)

	/*
		Expr appends an expression to the most recently added clause.

		Expressions are separated with commas.
	*/
	Expr(expr string, args ...any) Builder

	/*
		From starts a SELECT statement.

			var cnt int64

			err := xsql.From("table").
				Select("COUNT(*)").To(&cnt)
				Where("value >= ?", 42).
				QueryRowAndClose(ctx, db)
			if err != nil {
				panic(err)
			}
	*/
	From(expr string, args ...any) Builder

	/*
		FullJoin adds a FULL OUTER JOIN clause to SELECT statement
	*/
	FullJoin(table string, on string) Builder

	// CrossJoin adds a CROSS JOIN clause to SELECT statement
	CrossJoin(table string) Builder

	/*
		ForUpdate appends FOR UPDATE to a SELECT statement.

		Optional tokens are appended as-is, for example "NOWAIT" or "SKIP LOCKED".
	*/
	ForUpdate(opts ...string) Builder

	/*
		ForShare appends FOR SHARE to a SELECT statement.

		Optional tokens are appended as-is, for example "NOWAIT" or "OF t".
	*/
	ForShare(opts ...string) Builder

	// GroupBy adds the GROUP BY clause to SELECT statement
	GroupBy(expr string) Builder

	// Having adds the HAVING clause to SELECT statement
	Having(expr string, args ...any) Builder

	In(args ...any) Builder
	NotIn(args ...any) Builder
	InsertInto(tableName string) Builder

	/*
		Invalidate forces a rebuild on next query execution.

		Most likely you don't need to call this method directly.
	*/
	Invalidate()
	Join(table string, on string) Builder
	LeftJoin(table string, on string) Builder

	// Limit adds a limit on number of returned rows
	Limit(limit any) Builder

	/*
		NewRow method helps to construct a bulk INSERT statement.

		The following code

				q := stmt.InsertInto("table")
			    for k, v := range entries {
					q.NewRow().
						Set("key", k).
						Set("value", v)
				}

		produces (assuming there were 2 key/value pairs at entries map):

			INSERT INTO table ( key, value ) VALUES ( ?, ? ), ( ?, ? )
	*/
	NewRow() Row

	// Offset adds an offset on number of skipped rows
	Offset(offset any) Builder

	/*
		OnConflict appends an ON CONFLICT clause (PostgreSQL upsert).

		target is appended after ON CONFLICT, for example "(email) DO NOTHING"
		or "(email) DO UPDATE SET name = EXCLUDED.name". An empty target
		produces a bare ON CONFLICT, typically followed by Clause("DO NOTHING").
	*/
	OnConflict(target string, args ...any) Builder

	OrderBy(expr ...string) Builder

	// Paginate provides an easy way to set both offset and limit
	Paginate(page int, pageSize int) Builder

	// Query executes the statement.
	// For every row of a returned dataset it calls a handler function.
	// If scan targets were set via To method calls, Query method
	// executes rows.Scan right before calling a handler function.
	Query(ctx context.Context, db Executor, handler func(rows *sql.Rows)) error

	// QueryAndClose executes the statement and releases all the resources that
	// can be reused to a pool. Do not call any Builder methods after this call.
	// For every row of a returned dataset QueryAndClose executes a handler function.
	// If scan targets were set via To method calls, QueryAndClose method
	// executes rows.Scan right before calling a handler function.
	QueryAndClose(ctx context.Context, db Executor, handler func(rows *sql.Rows)) error

	// QueryRow executes the statement via Executor methods
	// and scans values to variables bound via To method calls.
	QueryRow(ctx context.Context, db Executor) error

	// QueryRowAndClose executes the statement via Executor methods
	// and scans values to variables bound via To method calls.
	// All the objects allocated by query builder are moved to a pool
	// to be reused.
	//
	// Do not call any Builder methods after this call.
	QueryRowAndClose(ctx context.Context, db Executor) error

	// Returning adds a RETURNING clause to a statement
	Returning(expr string) Builder

	/*
		RightJoin adds a RIGHT OUTER JOIN clause to SELECT statement
	*/
	RightJoin(table string, on string) Builder

	/*
		Select starts a SELECT statement.

			var cnt int64

			err := xsql.Select("COUNT(*)").To(&cnt).
				From("table").
				Where("value >= ?", 42).
				QueryRowAndClose(ctx, db)
			if err != nil {
				panic(err)
			}

		Note that From method can also be used to start a SELECT statement.
	*/
	Select(expr string, args ...any) Builder

	/*
		Set method:

		- Adds a column to the list of columns and a value to VALUES clause of INSERT statement,

		A call to Set method generates both the list of columns and
		values to be inserted by INSERT statement:

			q := xsql.InsertInto("table").Set("field", 42)

		produces

			INSERT INTO table (field) VALUES (42)

		Do not use it to construct ON CONFLICT DO UPDATE SET or similar clauses.
		Use generic Clause and Expr methods instead:

			q.Clause("ON CONFLICT DO UPDATE SET").Expr("column_name = ?", value)
	*/
	Set(field string, value any) Builder

	/*
		SetExpr is an extended version of Set method.

			q.SetExpr("field", "field + 1")
			q.SetExpr("field", "? + ?", 31, 11)
	*/
	SetExpr(field string, expr string, args ...any) Builder

	// String method builds and returns an SQL statement.
	String() string

	/*
		SubQuery appends a sub query expression to a current clause.

		SubQuery method call closes the Builder passed as query parameter.
		Do not reuse it afterwards.
	*/
	SubQuery(prefix string, suffix string, query Builder) Builder

	To(dest ...any) Builder

	/*
		Union adds a UNION clause to the statement.

		all argument controls if UNION ALL or UNION clause
		is to be constructed. Use UNION ALL if possible to
		get faster queries.
	*/
	Union(all bool, query Builder) Builder

	/*
		Update starts an UPDATE statement.

			err := xsql.Update("table").
				Set("field1", "newvalue").
				Where("id = ?", 42).
				ExecAndClose(ctx, db)
			if err != nil {
				panic(err)
			}
	*/
	Update(tableName string) Builder

	/*
		Where adds a filter:

			xsql.From("users").
				Select("id, name").
				Where("email = ?", email).
				Where("is_active = 1")
	*/
	Where(expr string, args ...any) Builder

	// With prepends a statement with an WITH clause.
	// With method calls a Close method of a given query, so
	// make sure not to reuse it afterwards.
	With(queryName string, query Builder) Builder

	// Name returns the name of the statement
	Name() string

	// SetName sets the name of the statement to be cached
	SetName(name string) Builder

	// UseNewLines specifies an option to add new lines for each clause
	UseNewLines(op bool) Builder
}

Builder is an interface for SQL statement builders.

func DeleteFrom 

func DeleteFrom(tableName string) Builder

DeleteFrom starts a DELETE statement. 

err := xsql.DeleteFrom("table").Where("id = ?", id).ExecAndClose(ctx, db)

func From 

func From(expr string, args ...any) Builder

From starts a SELECT statement. 

var cnt int64

err := xsql.From("table").
	Select("COUNT(*)").To(&cnt)
	Where("value >= ?", 42).
	QueryRowAndClose(ctx, db)
if err != nil {
	panic(err)
}

func InsertInto 

func InsertInto(tableName string) Builder

InsertInto starts an INSERT statement. 

var newId int64
err := xsql.InsertInto("table").
	Set("field", value).
	Returning("id").To(&newId).
	QueryRowAndClose(ctx, db)
if err != nil {
	panic(err)
}

func New 

func New(verb string, args ...any) Builder

New initializes a SQL statement builder instance with an arbitrary verb.

Use xsql.Select(), xsql.InsertInto(), xsql.DeleteFrom() to start common SQL statements.

Use New for special cases like this: 

q := xsql.New("TRUNCATE")
for _, table := range tableNames {
	q.Expr(table)
}
q.Clause("RESTART IDENTITY")
err := q.ExecAndClose(ctx, db)
if err != nil {
	panic(err)
}

func Select 

func Select(expr string, args ...any) Builder

Select starts a SELECT statement. 

var cnt int64

err := xsql.Select("COUNT(*)").To(&cnt).
	From("table").
	Where("value >= ?", 42).
	QueryRowAndClose(ctx, db)
if err != nil {
	panic(err)
}

Note that From method can also be used to start a SELECT statement.

func Update 

func Update(tableName string) Builder

Update starts an UPDATE statement. 

err := xsql.Update("table").
	Set("field1", "newvalue").
	Where("id = ?", 42).
	ExecAndClose(ctx, db)
if err != nil {
	panic(err)
}

func With 

func With(queryName string, query Builder) Builder

With starts a statement prepended by WITH clause and closes a subquery passed as an argument.

type Dialect 

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

Dialect defines how an SQL statement is built and caches rendered SQL.

NoDialect is the default statement builder mode: SQL fragments are not rewritten. Postgres mode replaces ? placeholders with $1, $2, ... 

q := xsql.Postgres.From("table").Select("field")
// ...
q.Close()

or as the process default: 

xsql.SetDialect(xsql.Postgres)
q := xsql.From("table").Select("field")
q.Close()

Shared Dialect values are safe for concurrent constructors. Toggle newlines with WithNewLines rather than UseNewLines when the instance is used from more than one goroutine.

func (*Dialect) ClearCache 

func (d *Dialect) ClearCache()

ClearCache drops all cached SQL strings for this dialect. Entries are deleted in place so dialects created via WithNewLines keep sharing the same map.

func (*Dialect) DeleteFrom 

func (b *Dialect) DeleteFrom(tableName string) Builder

DeleteFrom starts a DELETE statement.

func (*Dialect) From 

func (b *Dialect) From(expr string, args ...any) Builder

From starts a SELECT statement.

func (*Dialect) GetCachedQuery 

func (d *Dialect) GetCachedQuery(name string) (string, bool)

func (*Dialect) GetOrCreateQuery added in v0.10.2 

func (d *Dialect) GetOrCreateQuery(name string, create func(name string) Builder) (string, string)

GetOrCreateQuery returns a cached query by name or creates a new one. The Builder returned by create is closed after the SQL is rendered and cached. create must not call Close on the Builder it returns.

func (*Dialect) InsertInto 

func (b *Dialect) InsertInto(tableName string) Builder

InsertInto starts an INSERT statement.

func (*Dialect) New 

func (b *Dialect) New(verb string, args ...any) Builder

New starts an SQL statement with an arbitrary verb.

Use From, Select, InsertInto or DeleteFrom methods to create an instance of an SQL statement builder for common statements.

func (*Dialect) Provider 

func (b *Dialect) Provider() string

Provider returns the name of the SQL dialect.

func (*Dialect) PutCachedQuery added in v0.6.0 

func (d *Dialect) PutCachedQuery(name, sql string)

func (*Dialect) Select 

func (b *Dialect) Select(expr string, args ...any) Builder

Select starts a SELECT statement.

Consider using From method to start a SELECT statement - you may find it easier to read and maintain.

func (*Dialect) Update 

func (b *Dialect) Update(tableName string) Builder

Update starts an UPDATE statement.

func (*Dialect) UseNewLines added in v0.8.0 

func (b *Dialect) UseNewLines(op bool)

UseNewLines specifies an option to add new lines for each clause

func (*Dialect) With 

func (b *Dialect) With(queryName string, query Builder) Builder

With starts a statement prepended by WITH clause and closes a subquery passed as an argument.

func (*Dialect) WithNewLines added in v0.25.159 

func (b *Dialect) WithNewLines(op bool) SQLDialect

WithNewLines returns a dialect that shares this instance's query cache but uses op as the newline policy for newly created statements.

type Executor 

type Executor interface {
	ExecContext(ctx context.Context, query string, args ...any) (sql.Result, error)
	QueryContext(ctx context.Context, query string, args ...any) (*sql.Rows, error)
	QueryRowContext(ctx context.Context, query string, args ...any) *sql.Row
}

Executor performs SQL queries. It's an interface accepted by Query, QueryRow and Exec methods. Both sql.DB, sql.Conn and sql.Tx can be passed as executor.

type Row 

type Row interface {
	/*
		Set method:

		- Adds a column to the list of columns and a value to VALUES clause of INSERT statement,

		- Adds an item to SET clause of an UPDATE statement.

			q.Set("field", 32)

		For INSERT statements a call to Set method generates
		both the list of columns and values to be inserted:

			q := xsql.InsertInto("table").Set("field", 42)

		produces

			INSERT INTO table (field) VALUES (42)

		Do not use it to construct ON CONFLICT DO UPDATE SET or similar clauses.
		Use generic Clause and Expr methods instead:

			q.Clause("ON CONFLICT DO UPDATE SET").Expr("column_name = ?", value)
	*/
	Set(field string, value any) Row
	/*
		SetExpr is an extended version of Set method.

			q.SetExpr("field", "field + 1")
			q.SetExpr("field", "? + ?", 31, 11)
	*/
	SetExpr(field string, expr string, args ...any) Row
}

Row is an interface for a single row of data.

type SQLDialect 

type SQLDialect interface {
	// Provider returns the name of the SQL dialect.
	Provider() string

	// UseNewLines sets the default newline policy for statements created
	// from this dialect instance. It mutates the receiver; prefer
	// WithNewLines when the dialect is shared across goroutines.
	UseNewLines(op bool)

	// WithNewLines returns a dialect that shares the receiver's query cache
	// but uses op as the newline policy. The original dialect is not mutated.
	WithNewLines(op bool) SQLDialect

	// GetCachedQuery returns a cached query by name.
	GetCachedQuery(name string) (string, bool)

	// PutCachedQuery stores a query in the cache.
	PutCachedQuery(name, query string)

	// GetOrCreateQuery returns a cached query by name or creates a new one.
	// The function closes the Builder returned by create.
	GetOrCreateQuery(name string, create func(name string) Builder) (query string, key string)

	// ClearCache drops all cached SQL strings for this dialect.
	ClearCache()

	// DeleteFrom starts a DELETE statement.
	DeleteFrom(tableName string) Builder

	// From starts a SELECT statement.
	From(expr string, args ...any) Builder

	// InsertInto starts an INSERT statement.
	InsertInto(tableName string) Builder

	/*
		New starts an SQL statement with an arbitrary verb.

		Use From, Select, InsertInto or DeleteFrom methods to create
		an instance of an SQL statement builder for common statements.
	*/
	New(verb string, args ...any) Builder

	/*
		Select starts a SELECT statement.

		Consider using From method to start a SELECT statement - you may find
		it easier to read and maintain.
	*/
	Select(expr string, args ...any) Builder

	// Update starts an UPDATE statement.
	Update(tableName string) Builder

	/*
		With starts a statement prepended by WITH clause
		and closes a subquery passed as an argument.
	*/
	With(queryName string, query Builder) Builder
}

SQLDialect is an interface for SQL statement builders.

A Dialect value published by this package (NoDialect, Postgres, SQLServer) is safe for concurrent use: multiple goroutines may call its constructor methods at the same time. The Builder instances they return are not.

func UseNewLines added in v0.8.0 

func UseNewLines(op bool) SQLDialect

UseNewLines returns the default dialect with the given newline policy. The process-wide default dialect is not mutated; statements created from the returned value use op. Call this before adding clauses: 

q := xsql.UseNewLines(false).Select("id").From("table")

type Stmt 

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

Stmt provides a set of helper methods for SQL statement building and execution.

A Stmt is not safe for concurrent use. Share a Dialect across goroutines and give each goroutine its own Stmt (or Clone a template per request).

Use one of the following methods to create a SQL statement builder instance: 

xsql.From("table")
xsql.Select("field")
xsql.InsertInto("table")
xsql.Update("table")
xsql.DeleteFrom("table")

For other SQL statements use New: 

q := xsql.New("TRUNCATE")
for _, table := range tablesToBeEmptied {
	q.Expr(table)
}
err := q.ExecAndClose(ctx, db)
if err != nil {
	panic(err)
}

func (*Stmt) Args 

func (q *Stmt) Args() []any

Args returns the list of arguments to be passed to database driver for statement execution.

Do not access a slice returned by this method after Stmt is closed.

An array, a returned slice points to, can be altered by any method that adds a clause or an expression with arguments.

Make sure to make a copy of the returned slice if you need to preserve it.

func (*Stmt) Bind 

func (q *Stmt) Bind(data any) Builder

Bind adds structure fields to SELECT statement. Structure fields have to be annotated with "db" tag. Reflect-based Bind is slightly slower than `Select("field").To(&record.field)` but provides an easier way to retrieve data.

Fields tagged `db:"-"` are skipped, including anonymous embedded structs. Column names may be followed by comma-separated options (`db:"id,int8,primary"`); only the first token is used as the column name.

Field mappings are cached per struct type.

Note: this method does no type checks and returns no errors.

Example 
package main

import (
	"context"
	"database/sql"

	"github.com/effective-security/xdb/xsql"
)

type dummyDB int

func (db *dummyDB) ExecContext(ctx context.Context, query string, args ...any) (sql.Result, error) {
	return nil, nil
}

func (db *dummyDB) QueryContext(ctx context.Context, query string, args ...any) (*sql.Rows, error) {
	return nil, nil
}

func (db *dummyDB) QueryRowContext(ctx context.Context, query string, args ...any) *sql.Row {
	return nil
}

var db = new(dummyDB)
var ctx = context.Background()

func main() {
	type Offer struct {
		Id        int64   `db:"id"`
		ProductId int64   `db:"product_id"`
		Price     float64 `db:"price"`
		IsDeleted bool    `db:"is_deleted"`
	}

	var o Offer

	err := xsql.From("offers").
		Bind(&o).
		Where("id = ?", 42).
		QueryRowAndClose(ctx, db)
	if err != nil {
		panic(err)
	}
}

func (*Stmt) Clause 

func (q *Stmt) Clause(expr string, args ...any) Builder

Clause appends a raw SQL fragment to the statement.

Use it to add a raw SQL fragment like ON CONFLICT, ON DUPLICATE KEY, WINDOW, etc.

An SQL fragment added via Clause method appears after the last clause previously added. If called first, Clause method prepends a statement with a raw SQL.

Example 
package main

import (
	"fmt"

	"github.com/effective-security/xdb/xsql"
)

func main() {
	q := xsql.UseNewLines(false).From("empsalary").
		Select("sum(salary) OVER w").
		Clause("WINDOW w AS (PARTITION BY depname ORDER BY salary DESC)")
	fmt.Println(q.String())
	q.Close()

}

Output:
SELECT sum(salary) OVER w FROM empsalary WINDOW w AS (PARTITION BY depname ORDER BY salary DESC)

func (*Stmt) Clone 

func (q *Stmt) Clone() Builder

Clone creates a copy of the statement. The copy is independent: later clauses added to either instance do not affect the other. Newline policy, rendered SQL, and scan targets are copied. The cache name is not: an extended clone must not overwrite the source statement's named-cache entry. Call SetName on the clone if it should be cached under its own key.

Example 
package main

import (
	"fmt"

	"github.com/effective-security/xdb/xsql"
)

func main() {
	base := xsql.UseNewLines(false).From("users").Select("id, name").Where("active = ?", true)
	q := base.Clone().Where("id = ?", 7).Limit(1)
	fmt.Println(q.String())
	fmt.Println(q.Args())
	fmt.Println(base.String())
	q.Close()
	base.Close()
}

Output:
SELECT id, name FROM users WHERE active = ? AND id = ? LIMIT ?
[true 7 1]
SELECT id, name FROM users WHERE active = ?

func (*Stmt) Close 

func (q *Stmt) Close()

Close puts buffers and other objects allocated to build an SQL statement back to pool for reuse by other Stmt instances.

Stmt instance should not be used after Close method call.

func (*Stmt) CrossJoin added in v0.25.159 

func (q *Stmt) CrossJoin(table string) Builder

CrossJoin adds a CROSS JOIN clause to SELECT statement

func (*Stmt) DeleteFrom 

func (q *Stmt) DeleteFrom(tableName string) Builder

DeleteFrom adds DELETE clause to a statement. 

q.DeleteFrom("table").Where("id = ?", id)
Example 
package main

import (
	"fmt"

	"github.com/effective-security/xdb/xsql"
)

func main() {
	q := xsql.UseNewLines(false).DeleteFrom("table").Where("id = ?", 42)
	fmt.Println(q.String())
	fmt.Println(q.Args())
	q.Close()
}

Output:
DELETE FROM table WHERE id = ?
[42]

func (*Stmt) Dest 

func (q *Stmt) Dest() []any

Dest returns a list of value pointers passed via To method calls. The order matches the constructed SQL statement.

Do not access a slice returned by this method after Stmt is closed.

Note that an array, a returned slice points to, can be altered by To method calls.

Make sure to make a copy if you need to preserve a slice returned by this method.

func (*Stmt) Exec 

func (q *Stmt) Exec(ctx context.Context, db Executor) (sql.Result, error)

Exec executes the statement.

func (*Stmt) ExecAndClose 

func (q *Stmt) ExecAndClose(ctx context.Context, db Executor) (sql.Result, error)

ExecAndClose executes the statement and releases all the objects and buffers allocated by statement builder back to a pool.

Do not call any Stmt methods after this call.

func (*Stmt) Expr 

func (q *Stmt) Expr(expr string, args ...any) Builder

Expr appends an expression to the most recently added clause.

Expressions are separated with commas.

func (*Stmt) ForShare added in v0.25.159 

func (q *Stmt) ForShare(opts ...string) Builder

ForShare appends FOR SHARE to a SELECT statement.

Optional tokens are appended as-is: 

q.ForShare("NOWAIT")

func (*Stmt) ForUpdate added in v0.25.159 

func (q *Stmt) ForUpdate(opts ...string) Builder

ForUpdate appends FOR UPDATE to a SELECT statement.

Optional tokens are appended as-is: 

q.ForUpdate("NOWAIT")
q.ForUpdate("OF t", "SKIP LOCKED")
Example 
package main

import (
	"fmt"

	"github.com/effective-security/xdb/xsql"
)

func main() {
	q := xsql.Postgres.WithNewLines(false).
		Select("id, balance").
		From("accounts").
		Where("id = ?", 42).
		ForUpdate("NOWAIT")
	fmt.Println(q.String())
	fmt.Println(q.Args())
	q.Close()
}

Output:
SELECT id, balance FROM accounts WHERE id = $1 FOR UPDATE NOWAIT
[42]

func (*Stmt) From 

func (q *Stmt) From(expr string, args ...any) Builder

From adds a FROM clause to statement.

Example 
package main

import (
	"fmt"

	"github.com/effective-security/xdb/xsql"
)

func main() {
	nl := xsql.UseNewLines(false)
	q := nl.Select("*").
		From("").
		SubQuery(
			"(", ") counted_news",
			nl.From("news").
				Select("id, section, header, score").
				Select("row_number() OVER (PARTITION BY section ORDER BY score DESC) AS rating_in_section").
				OrderBy("section, rating_in_section")).
		Where("rating_in_section <= 5")
	fmt.Println(q.String())
	q.Close()
}

Output:
SELECT * FROM (SELECT id, section, header, score, row_number() OVER (PARTITION BY section ORDER BY score DESC) AS rating_in_section FROM news ORDER BY section, rating_in_section) counted_news WHERE rating_in_section <= 5

func (*Stmt) FullJoin 

func (q *Stmt) FullJoin(table, on string) Builder

FullJoin adds a FULL OUTER JOIN clause to SELECT statement

func (*Stmt) GroupBy 

func (q *Stmt) GroupBy(expr string) Builder

GroupBy adds the GROUP BY clause to SELECT statement

Example 
package main

import (
	"fmt"

	"github.com/effective-security/xdb/xsql"
)

func main() {
	q := xsql.UseNewLines(false).From("incomes").
		Select("source, sum(amount) as s").
		Where("amount > ?", 42).
		GroupBy("source")
	fmt.Println(q.String())
	fmt.Println(q.Args())
	q.Close()
}

Output:
SELECT source, sum(amount) as s FROM incomes WHERE amount > ? GROUP BY source
[42]

func (*Stmt) Having 

func (q *Stmt) Having(expr string, args ...any) Builder

Having adds the HAVING clause to SELECT statement

Example 
package main

import (
	"fmt"

	"github.com/effective-security/xdb/xsql"
)

func main() {
	q := xsql.UseNewLines(false).From("incomes").
		Select("source, sum(amount) as s").
		Where("amount > ?", 42).
		GroupBy("source").
		Having("s > ?", 100)
	fmt.Println(q.String())
	fmt.Println(q.Args())
	q.Close()
}

Output:
SELECT source, sum(amount) as s FROM incomes WHERE amount > ? GROUP BY source HAVING s > ?
[42 100]

func (*Stmt) In 

func (q *Stmt) In(args ...any) Builder

In adds IN expression to the current filter.

In method must be called after a Where method call.

An empty argument list is rendered as `IN (SELECT NULL WHERE 1=0)` so the filter matches no rows, stays valid SQL (engines reject IN ()), and does not change the meaning of neighboring AND/OR terms.

Example 
package main

import (
	"fmt"

	"github.com/effective-security/xdb/xsql"
)

func main() {
	q := xsql.UseNewLines(false).From("tasks").
		Select("id, status").
		Where("status").In("new", "pending", "wip")
	fmt.Println(q.String())
	fmt.Println(q.Args())
	q.Close()

}

Output:
SELECT id, status FROM tasks WHERE status IN (?,?,?)
[new pending wip]

func (*Stmt) InsertInto 

func (q *Stmt) InsertInto(tableName string) Builder

InsertInto adds INSERT INTO clause to a statement. 

q.InsertInto("table")

tableName argument can be a SQL fragment: 

q.InsertInto("table AS t")
Example 
package main

import (
	"fmt"

	"github.com/effective-security/xdb/xsql"
)

func main() {
	q := xsql.UseNewLines(false).InsertInto("table").
		Set("field1", "newvalue").
		SetExpr("field2", "field2 + 1")
	fmt.Println(q.String())
	fmt.Println(q.Args())
	q.Close()
}

Output:
INSERT INTO table ( field1, field2 ) VALUES ( ?, field2 + 1 )
[newvalue]

func (*Stmt) Invalidate 

func (q *Stmt) Invalidate()

Invalidate forces a rebuild on next query execution.

Most likely you don't need to call this method directly.

func (*Stmt) Join 

func (q *Stmt) Join(table, on string) Builder

Join adds an INNER JOIN clause to SELECT statement

Example 
package main

import (
	"fmt"

	"github.com/effective-security/xdb/xsql"
)

func main() {
	q := xsql.Postgres.WithNewLines(false).
		From("orders o").
		Select("o.id, u.email").
		Join("users u", "u.id = o.user_id").
		Where("o.total > ?", 100)
	fmt.Println(q.String())
	fmt.Println(q.Args())
	q.Close()
}

Output:
SELECT o.id, u.email FROM orders o JOIN users u ON (u.id = o.user_id) WHERE o.total > $1
[100]

func (*Stmt) LeftJoin 

func (q *Stmt) LeftJoin(table, on string) Builder

LeftJoin adds a LEFT OUTER JOIN clause to SELECT statement

func (*Stmt) Limit 

func (q *Stmt) Limit(limit any) Builder

Limit adds a limit on number of returned rows

Example 
package main

import (
	"fmt"

	"github.com/effective-security/xdb/xsql"
)

func main() {
	q := xsql.UseNewLines(false).Select("id").From("table").Limit(10)
	fmt.Println(q.String())
}

Output:
SELECT id FROM table LIMIT ?

func (*Stmt) Name 

func (q *Stmt) Name() string

Name returns the name of the statement

func (*Stmt) NewRow 

func (q *Stmt) NewRow() Row

NewRow method helps to construct a bulk INSERT statement.

The following code 

	q := stmt.InsertInto("table")
    for k, v := range entries {
		q.NewRow().
			Set("key", k).
			Set("value", v)
	}

produces (assuming there were 2 key/value pairs at entries map): 

INSERT INTO table ( key, value ) VALUES ( ?, ? ), ( ?, ? )
Example 
package main

import (
	"fmt"

	"github.com/effective-security/xdb/xsql"
)

func main() {
	q := xsql.Postgres.WithNewLines(false).InsertInto("users")
	q.NewRow().Set("email", "first@ex.com").Set("name", "First")
	q.NewRow().Set("email", "second@ex.com").Set("name", "Second")
	fmt.Println(q.String())
	fmt.Println(q.Args())
	q.Close()
}

Output:
INSERT INTO users ( email, name ) VALUES ( $1, $2 ), ( $3, $4 )
[first@ex.com First second@ex.com Second]

func (*Stmt) NotIn added in v0.25.159 

func (q *Stmt) NotIn(args ...any) Builder

NotIn adds NOT IN expression to the current filter.

NotIn method must be called after a Where method call.

An empty argument list is rendered as `NOT IN (SELECT NULL WHERE 1=0)` so the filter matches all values of that column, stays valid SQL (engines reject NOT IN ()), and does not introduce an ungrouped OR.

Example 
package main

import (
	"fmt"

	"github.com/effective-security/xdb/xsql"
)

func main() {
	q := xsql.UseNewLines(false).From("tasks").
		Select("id, status").
		Where("status").NotIn("done", "canceled")
	fmt.Println(q.String())
	fmt.Println(q.Args())
	q.Close()
}

Output:
SELECT id, status FROM tasks WHERE status NOT IN (?,?)
[done canceled]

func (*Stmt) Offset 

func (q *Stmt) Offset(offset any) Builder

Offset adds an offset on number of skipped rows

Example 
package main

import (
	"fmt"

	"github.com/effective-security/xdb/xsql"
)

func main() {
	q := xsql.UseNewLines(false).Select("id").From("table").Limit(10).Offset(10)
	fmt.Println(q.String())
}

Output:
SELECT id FROM table LIMIT ? OFFSET ?

func (*Stmt) OnConflict added in v0.25.159 

func (q *Stmt) OnConflict(target string, args ...any) Builder

OnConflict appends an ON CONFLICT clause (PostgreSQL upsert). 

q.InsertInto("users").
	Set("email", email).
	Set("name", name).
	OnConflict("(email) DO UPDATE SET name = EXCLUDED.name")

q.InsertInto("users").
	Set("email", email).
	OnConflict("DO NOTHING")
Example 
package main

import (
	"fmt"

	"github.com/effective-security/xdb/xsql"
)

func main() {
	q := xsql.Postgres.WithNewLines(false).
		InsertInto("users").
		Set("email", "a@b.c").
		Set("name", "Ada").
		OnConflict("(email) DO UPDATE SET name = EXCLUDED.name").
		Returning("id")
	fmt.Println(q.String())
	fmt.Println(q.Args())
	q.Close()
}

Output:
INSERT INTO users ( email, name ) VALUES ( $1, $2 ) ON CONFLICT (email) DO UPDATE SET name = EXCLUDED.name RETURNING id
[a@b.c Ada]

func (*Stmt) OrderBy 

func (q *Stmt) OrderBy(expr ...string) Builder

OrderBy adds the ORDER BY clause to SELECT statement

Example 
package main

import (
	"fmt"

	"github.com/effective-security/xdb/xsql"
)

func main() {
	q := xsql.UseNewLines(false).Select("id").From("table").OrderBy("id", "name DESC")
	fmt.Println(q.String())
}

Output:
SELECT id FROM table ORDER BY id, name DESC

func (*Stmt) Paginate 

func (q *Stmt) Paginate(page, pageSize int) Builder

Paginate provides an easy way to set both offset and limit

Example 
package main

import (
	"fmt"

	"github.com/effective-security/xdb/xsql"
)

func main() {
	q := xsql.UseNewLines(false).Select("id").From("table").Paginate(5, 10)
	fmt.Println(q.String(), q.Args())
	q.Close()

	q = xsql.UseNewLines(false).Select("id").From("table").Paginate(1, 10)
	fmt.Println(q.String(), q.Args())
	q.Close()

	// Zero and negative values are replaced with 1
	q = xsql.UseNewLines(false).Select("id").From("table").Paginate(-1, -1)
	fmt.Println(q.String(), q.Args())
	q.Close()

}

Output:
SELECT id FROM table LIMIT ? OFFSET ? [10 40]
SELECT id FROM table LIMIT ? [10]
SELECT id FROM table LIMIT ? [1]

func (*Stmt) Query 

func (q *Stmt) Query(ctx context.Context, db Executor, handler func(rows *sql.Rows)) (err error)

Query executes the statement. For every row of a returned dataset it calls a handler function. If scan targets were set via To method calls, Query method executes rows.Scan right before calling a handler function.

func (*Stmt) QueryAndClose 

func (q *Stmt) QueryAndClose(ctx context.Context, db Executor, handler func(rows *sql.Rows)) error

QueryAndClose executes the statement and releases all the resources that can be reused to a pool. Do not call any Stmt methods after this call. For every row of a returned dataset QueryAndClose executes a handler function. If scan targets were set via To method calls, QueryAndClose method executes rows.Scan right before calling a handler function.

func (*Stmt) QueryRow 

func (q *Stmt) QueryRow(ctx context.Context, db Executor) error

QueryRow executes the statement via Executor methods and scans values to variables bound via To method calls.

func (*Stmt) QueryRowAndClose 

func (q *Stmt) QueryRowAndClose(ctx context.Context, db Executor) error

QueryRowAndClose executes the statement via Executor methods and scans values to variables bound via To method calls. All the objects allocated by query builder are moved to a pool to be reused.

Do not call any Stmt methods after this call.

Example 
package main

import (
	"context"
	"database/sql"

	"github.com/effective-security/xdb/xsql"
)

type dummyDB int

func (db *dummyDB) ExecContext(ctx context.Context, query string, args ...any) (sql.Result, error) {
	return nil, nil
}

func (db *dummyDB) QueryContext(ctx context.Context, query string, args ...any) (*sql.Rows, error) {
	return nil, nil
}

func (db *dummyDB) QueryRowContext(ctx context.Context, query string, args ...any) *sql.Row {
	return nil
}

var db = new(dummyDB)
var ctx = context.Background()

func main() {
	type Offer struct {
		id        int64
		productId int64
		price     float64
		isDeleted bool
	}

	var o Offer

	err := xsql.From("offers").
		Select("id").To(&o.id).
		Select("product_id").To(&o.productId).
		Select("price").To(&o.price).
		Select("is_deleted").To(&o.isDeleted).
		Where("id = ?", 42).
		QueryRowAndClose(ctx, db)
	if err != nil {
		panic(err)
	}
}

func (*Stmt) Returning 

func (q *Stmt) Returning(expr string) Builder

Returning adds a RETURNING clause to a statement

Example 
package main

import (
	"fmt"

	"github.com/effective-security/xdb/xsql"
)

func main() {
	var newId int
	q := xsql.UseNewLines(false).InsertInto("table").
		Set("field1", "newvalue").
		Returning("id").To(&newId)
	fmt.Println(q.String(), q.Args())
	q.Close()
}

Output:
INSERT INTO table ( field1 ) VALUES ( ? ) RETURNING id [newvalue]

func (*Stmt) RightJoin 

func (q *Stmt) RightJoin(table, on string) Builder

RightJoin adds a RIGHT OUTER JOIN clause to SELECT statement

func (*Stmt) Select 

func (q *Stmt) Select(expr string, args ...any) Builder

Select adds a SELECT clause to a statement and/or appends an expression that defines columns of a resulting data set. 

q := xsql.Select("DISTINCT field1, field2").From("table")

Select can be called multiple times to add more columns: 

q := xsql.From("table").Select("field1")
if needField2 {
	q.Select("field2")
}
// ...
q.Close()

Use To method to bind variables to selected columns: 

var (
	num  int
	name string
)

res := xsql.From("table").
	Select("num, name").To(&num, &name).
	Where("id = ?", 42).
	QueryRowAndClose(ctx, db)
if err != nil {
	panic(err)
}

Note that a SELECT statement can also be started by a From method call.

func (*Stmt) Set 

func (q *Stmt) Set(field string, value any) Builder

Set method:

- Adds a column to the list of columns and a value to VALUES clause of INSERT statement,

- Adds an item to SET clause of an UPDATE statement. 

q.Set("field", 32)

For INSERT statements a call to Set method generates both the list of columns and values to be inserted: 

q := xsql.InsertInto("table").Set("field", 42)

produces 

INSERT INTO table (field) VALUES (42)

Do not use it to construct ON CONFLICT DO UPDATE SET or similar clauses. Use generic Clause and Expr methods instead: 

q.Clause("ON CONFLICT DO UPDATE SET").Expr("column_name = ?", value)

func (*Stmt) SetExpr 

func (q *Stmt) SetExpr(field, expr string, args ...any) Builder

SetExpr is an extended version of Set method. 

q.SetExpr("field", "field + 1")
q.SetExpr("field", "? + ?", 31, 11)
Example 
package main

import (
	"fmt"

	"github.com/effective-security/xdb/xsql"
)

func main() {
	q := xsql.UseNewLines(false).Update("table").SetExpr("field1", "field2 + 1").Where("id = ?", 42)
	fmt.Println(q.String())
	fmt.Println(q.Args())
	q.Close()
}

Output:
UPDATE table SET field1=field2 + 1 WHERE id = ?
[42]

func (*Stmt) SetName 

func (q *Stmt) SetName(name string) Builder

SetName sets the name of the statement

func (*Stmt) String 

func (q *Stmt) String() string

String method builds and returns an SQL statement.

func (*Stmt) SubQuery 

func (q *Stmt) SubQuery(prefix, suffix string, b Builder) Builder

SubQuery appends a sub query expression to a current clause.

SubQuery method call closes the Builder passed as query parameter. Do not reuse it afterwards.

Example 
package main

import (
	"fmt"

	"github.com/effective-security/xdb/xsql"
)

func main() {
	nl := xsql.UseNewLines(false)
	q := nl.From("orders o").
		Select("date, region").
		SubQuery("(", ") AS prev_order_date",
			nl.From("orders po").
				Select("date").
				Where("region = o.region").
				Where("id < o.id").
				OrderBy("id DESC").
				Clause("LIMIT 1")).
		Where("date > CURRENT_DATE - interval '1 day'").
		OrderBy("id DESC")
	fmt.Println(q.String())
	q.Close()

}

Output:
SELECT date, region, (SELECT date FROM orders po WHERE region = o.region AND id < o.id ORDER BY id DESC LIMIT 1) AS prev_order_date FROM orders o WHERE date > CURRENT_DATE - interval '1 day' ORDER BY id DESC

func (*Stmt) To 

func (q *Stmt) To(dest ...any) Builder

To sets a scan target for columns to be selected.

Accepts value pointers to be passed to sql.Rows.Scan by Query and QueryRow methods. 

var (
	field1 int
	field2 string
)
q := xsql.From("table").
	Select("field1").To(&field1).
	Select("field2").To(&field2)
err := QueryRow(nil, db)
q.Close()
if err != nil {
	// ...
}

To method MUST be called immediately after Select, Returning or other method that defines data to be returned.

func (*Stmt) Union 

func (q *Stmt) Union(all bool, b Builder) Builder

Union adds a UNION clause to the statement.

all argument controls if UNION ALL or UNION clause is to be constructed. Use UNION ALL if possible to get faster queries.

Example 
package main

import (
	"fmt"

	"github.com/effective-security/xdb/xsql"
)

func main() {
	nl := xsql.UseNewLines(false)
	q := nl.From("tasks").
		Select("id, status").
		Where("status = ?", "new").
		Union(true, nl.From("tasks").
			Select("id, status").
			Where("status = ?", "pending")).
		Union(true, nl.From("tasks").
			Select("id, status").
			Where("status = ?", "wip")).
		OrderBy("id")
	fmt.Println(q.String())
	fmt.Println(q.Args())
	q.Close()

}

Output:
SELECT id, status FROM tasks WHERE status = ? UNION ALL SELECT id, status FROM tasks WHERE status = ? UNION ALL SELECT id, status FROM tasks WHERE status = ? ORDER BY id
[new pending wip]

func (*Stmt) Update 

func (q *Stmt) Update(tableName string) Builder

Update adds UPDATE clause to a statement. 

q.Update("table")

tableName argument can be a SQL fragment: 

q.Update("ONLY table AS t")
Example 
package main

import (
	"fmt"

	"github.com/effective-security/xdb/xsql"
)

func main() {
	q := xsql.UseNewLines(false).Update("table").Set("field1", "newvalue").Where("id = ?", 42)
	fmt.Println(q.String(), q.Args())
	q.Close()
}

Output:
UPDATE table SET field1=? WHERE id = ? [newvalue 42]

func (*Stmt) UseNewLines added in v0.8.0 

func (q *Stmt) UseNewLines(op bool) Builder

UseNewLines specifies an option to add new lines for each clause. It only affects clauses added after this call. Set it on the dialect (WithNewLines) before constructing the statement when possible.

func (*Stmt) Where 

func (q *Stmt) Where(expr string, args ...any) Builder

Where adds a filter: 

xsql.From("users").
	Select("id, name").
	Where("email = ?", email).
	Where("is_active = 1")

func (*Stmt) With 

func (q *Stmt) With(queryName string, query Builder) Builder

With prepends a statement with an WITH clause. With method calls a Close method of a given query, so make sure not to reuse it afterwards.

Example 
package main

import (
	"fmt"

	"github.com/effective-security/xdb/xsql"
)

func main() {
	nl := xsql.UseNewLines(false)
	q := nl.From("orders").
		With("regional_sales",
			nl.From("orders").
				Select("region, SUM(amount) AS total_sales").
				GroupBy("region")).
		With("top_regions",
			nl.From("regional_sales").
				Select("region").
				Where("total_sales > (SELECT SUM(total_sales)/10 FROM regional_sales)")).
		Select("region").
		Select("product").
		Select("SUM(quantity) AS product_units").
		Select("SUM(amount) AS product_sales").
		Where("region IN (SELECT region FROM top_regions)").
		GroupBy("region, product")
	fmt.Println(q.String())
	q.Close()
}

Output:
WITH regional_sales AS (SELECT region, SUM(amount) AS total_sales FROM orders GROUP BY region), top_regions AS (SELECT region FROM regional_sales WHERE total_sales > (SELECT SUM(total_sales)/10 FROM regional_sales)) SELECT region, product, SUM(quantity) AS product_units, SUM(amount) AS product_sales FROM orders WHERE region IN (SELECT region FROM top_regions) GROUP BY region, product

func (*Stmt) WriteString 

func (q *Stmt) WriteString(s string)

WriteString appends a string to the statement

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.