xsql

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. See benchmarks: https://github.com/leporo/golang-sql-builder-benchmark

In order to maximize performance and minimize memory footprint, xsql reuses memory allocated for query building. The heavier load is, the faster xsql works.

Usage

Build complex statements:

var (
    region       string
    product      string
    productUnits int
    productSales float64
)

xsql.SetDialect(xsql.PostgreSQL)

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.Stmt {
    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 DEST").
    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.PostgreSQL.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.InsertInto("users").
    Set("email", "new@email.com").
    Set("address", "320 Some Avenue, Somewhereville, GA, US").
    Returning("id").To(&userId).
    Clause("ON CONFLICT (email) DO UPDATE SET address = users.address").
    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)

Documentation

Overview

Package xsql is an SQL statement builder and executor.

SQL Statement Builder

xsql statement builder provides a way to:

- Combine SQL statements from fragments of raw SQL and arguments that match those fragments,

- Map columns to variables to be referenced by Scan,

- Convert ? placeholders into numbered ones for PostgreSQL ($1, $2, etc).

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
	)

	xsql.NoDialect.UseNewLines(false)
	xsql.Postgres.UseNewLines(false)
	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)
	}
}

Index

• Variables
• func SetDialect(newDefaultDialect SQLDialect)
• type Builder
  • func DeleteFrom(tableName string) Builder
  • func From(expr string, args ...any) Builder
  • func InsertInto(tableName string) Builder
  • func New(verb string, args ...any) Builder
  • func Select(expr string, args ...any) Builder
  • func Update(tableName string) Builder
  • func With(queryName string, query Builder) Builder
• type Dialect
  • func (b *Dialect) DeleteFrom(tableName string) Builder
  • func (b *Dialect) From(expr string, args ...any) Builder
  • func (d *Dialect) GetCachedQuery(name string) (string, bool)
  • func (d *Dialect) GetOrCreateQuery(name string, create func(name string) Builder) string
  • func (b *Dialect) InsertInto(tableName string) Builder
  • func (b *Dialect) New(verb string, args ...any) Builder
  • func (b *Dialect) Provider() string
  • func (d *Dialect) PutCachedQuery(name, sql string)
  • func (b *Dialect) Select(expr string, args ...any) Builder
  • func (b *Dialect) Update(tableName string) Builder
  • func (b *Dialect) UseNewLines(op bool)
  • func (b *Dialect) With(queryName string, query Builder) Builder
• type Executor
• type Row
• type SQLDialect
  • func UseNewLines(op bool) SQLDialect
• type Stmt
  • func (q *Stmt) Args() []any
  • func (q *Stmt) Bind(data any) Builder
  • func (q *Stmt) Clause(expr string, args ...any) Builder
  • func (q *Stmt) Clone() Builder
  • func (q *Stmt) Close()
  • func (q *Stmt) DeleteFrom(tableName string) Builder
  • func (q *Stmt) Dest() []any
  • func (q *Stmt) Exec(ctx context.Context, db Executor) (sql.Result, error)
  • func (q *Stmt) ExecAndClose(ctx context.Context, db Executor) (sql.Result, error)
  • func (q *Stmt) Expr(expr string, args ...any) Builder
  • func (q *Stmt) From(expr string, args ...any) Builder
  • func (q *Stmt) FullJoin(table, on string) Builder
  • func (q *Stmt) GroupBy(expr string) Builder
  • func (q *Stmt) Having(expr string, args ...any) Builder
  • func (q *Stmt) In(args ...any) Builder
  • func (q *Stmt) InsertInto(tableName string) Builder
  • func (q *Stmt) Invalidate()
  • func (q *Stmt) Join(table, on string) Builder
  • func (q *Stmt) LeftJoin(table, on string) Builder
  • func (q *Stmt) Limit(limit any) Builder
  • func (q *Stmt) Name() string
  • func (q *Stmt) NewRow() Row
  • func (q *Stmt) Offset(offset any) Builder
  • func (q *Stmt) OrderBy(expr ...string) Builder
  • func (q *Stmt) Paginate(page, pageSize int) Builder
  • func (q *Stmt) Query(ctx context.Context, db Executor, handler func(rows *sql.Rows)) error
  • func (q *Stmt) QueryAndClose(ctx context.Context, db Executor, handler func(rows *sql.Rows)) error
  • func (q *Stmt) QueryRow(ctx context.Context, db Executor) error
  • func (q *Stmt) QueryRowAndClose(ctx context.Context, db Executor) error
  • func (q *Stmt) Returning(expr string) Builder
  • func (q *Stmt) RightJoin(table, on string) Builder
  • func (q *Stmt) Select(expr string, args ...any) Builder
  • func (q *Stmt) Set(field string, value any) Builder
  • func (q *Stmt) SetExpr(field, expr string, args ...any) Builder
  • func (q *Stmt) SetName(name string) Builder
  • func (q *Stmt) String() string
  • func (q *Stmt) SubQuery(prefix, suffix string, b Builder) Builder
  • func (q *Stmt) To(dest ...any) Builder
  • func (q *Stmt) Union(all bool, b Builder) Builder
  • func (q *Stmt) Update(tableName string) Builder
  • func (q *Stmt) UseNewLines(op bool) Builder
  • func (q *Stmt) Where(expr string, args ...any) Builder
  • func (q *Stmt) With(queryName string, query Builder) Builder
  • func (q *Stmt) WriteString(s string)

Examples

• Package
• Stmt.Bind
• Stmt.Clause
• Stmt.DeleteFrom
• Stmt.From
• Stmt.GroupBy
• Stmt.Having
• Stmt.In
• Stmt.InsertInto
• Stmt.Limit
• Stmt.Offset
• Stmt.OrderBy
• Stmt.Paginate
• Stmt.QueryRowAndClose
• Stmt.Returning
• Stmt.SetExpr
• Stmt.SubQuery
• Stmt.Union
• Stmt.Update
• Stmt.With

Variables

var (
	// NoDialect is a default statement builder mode.
	NoDialect = SQLDialect(&Dialect{provider: "default", useNewLines: true})
	// Postgres mode is to be used to automatically replace ? placeholders with $1, $2...
	Postgres = SQLDialect(&Dialect{provider: "postgres", useNewLines: true})

	SQLServer = SQLDialect(&Dialect{provider: "sqlserver", useNewLines: true})
)

Functions

func SetDialect

func SetDialect(newDefaultDialect SQLDialect)

SetDialect selects a Dialect to be used by default.

Dialect can be one of xsql.NoDialect or xsql.PostgreSQL

xsql.SetDialect(xsql.PostgreSQL)

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.
	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.
	*/
	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

	// 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
	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 a limit on number of returned rows
	Offset(offset 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 the method SQL statement is to be built.

NoDialect is a default statement builder mode. No SQL fragments will be altered. PostgreSQL mode can be set for a statement:

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

or as default mode:

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

When PostgreSQL mode is activated, ? placeholders are replaced with numbered positional arguments like $1, $2...

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

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

GetOrCreateQuery returns a cached query by name or creates a new one.

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

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

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.

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 specifies an option to add new lines for each clause
	UseNewLines(op bool)

	// 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 will close the Builder
	GetOrCreateQuery(name string, create func(name string) Builder) string

	// 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.

func UseNewLines

func UseNewLines(op bool) SQLDialect

UseNewLines specifies an option to add new lines for each clause

type Stmt

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

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

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.

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.

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) 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) 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() {
	q := xsql.UseNewLines(false).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")
	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.

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 INNERT JOIN clause to SELECT statement

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 ( ?, ? ), ( ?, ? )

func (*Stmt) Offset

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

Offset 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).Offset(10)
	fmt.Println(q.String())
}

Output:

SELECT id FROM table LIMIT ? OFFSET ?

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)) 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() {
	q := xsql.UseNewLines(false).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()

}

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() {
	q := xsql.UseNewLines(false).From("tasks").
		Select("id, status").
		Where("status = ?", "new").
		Union(true, xsql.From("tasks").
			Select("id, status").
			Where("status = ?", "pending")).
		Union(true, xsql.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

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

UseNewLines specifies an option to add new lines for each clause

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() {
	q := xsql.UseNewLines(false).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)")).
		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