xsql

package
v0.7.0 Latest Latest
Warning

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

 Go to latest Published: Nov 21, 2023 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. 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 
Output:

Index

Examples

Constants

This section is empty.

Variables

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

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

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() []any
	Bind(data any) Builder
	Clause(expr string, args ...any) Builder
	Clone() Builder
	Close()
	DeleteFrom(tableName string) Builder
	Dest() []any
	Exec(ctx context.Context, db Executor) (sql.Result, error)
	ExecAndClose(ctx context.Context, db Executor) (sql.Result, error)
	Expr(expr string, args ...any) Builder
	From(expr string, args ...any) Builder
	FullJoin(table string, on string) Builder
	GroupBy(expr string) Builder
	Having(expr string, args ...any) Builder
	In(args ...any) Builder
	InsertInto(tableName string) Builder
	Invalidate()
	Join(table string, on string) Builder
	LeftJoin(table string, on string) Builder
	Limit(limit any) Builder
	NewRow() Row
	Offset(offset any) Builder
	OrderBy(expr ...string) Builder
	Paginate(page int, pageSize int) Builder
	Query(ctx context.Context, db Executor, handler func(rows *sql.Rows)) error
	QueryAndClose(ctx context.Context, db Executor, handler func(rows *sql.Rows)) error
	QueryRow(ctx context.Context, db Executor) error
	QueryRowAndClose(ctx context.Context, db Executor) error
	Returning(expr string) Builder
	RightJoin(table string, on string) Builder
	Select(expr string, args ...any) Builder
	Set(field string, value any) Builder
	SetExpr(field string, expr string, args ...any) Builder
	String() string
	SubQuery(prefix string, suffix string, query Builder) Builder
	To(dest ...any) Builder
	Union(all bool, query Builder) Builder
	Update(tableName string) Builder
	Where(expr string, args ...any) Builder
	With(queryName string, query Builder) Builder

	Name() string
	SetName(name string) 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) ClearCache 

func (d *Dialect) ClearCache()

ClearCache clears the statement cache.

In most cases you don't need to care about it. It's there to let caller free memory when a caller executes zillions of unique SQL statements.

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) 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) 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

	ClearCache()
	GetCachedQuery(name string) (string, bool)
	PutCachedQuery(name, query 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.

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 
Output:

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 
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 
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 
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 
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 
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 
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 
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 
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 
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 
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 
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 
Output:

func (*Stmt) Returning 

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

Returning adds a RETURNING clause to a statement

Example 
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 
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 Stmt passed as query parameter. Do not reuse it afterwards.

Example 
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 
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 
Output:

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

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