sqlf

README

sqlf

A fast SQL query builder for Go.

What sqlf 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 - sqlf 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.
• sqlf.Stmt has methods to execute a query using any database/sql compatible driver.

What sqlf doesn't?

• sqlf 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.
• sqlf 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, sqlf reuses memory allocated for query building. The heavier load is, the faster sqlf works.

Usage

Build complex statements:

var (
    region       string
    product      string
    productUnits int
    productSales float64
)

sqlf.SetDialect(sqlf.PostgreSQL)

err := sqlf.From("orders").
    With("regional_sales",
        sqlf.From("orders").
            Select("region, SUM(amount) AS total_sales").
            GroupBy("region")).
    With("top_regions",
        sqlf.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 := sqlf.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 := sqlf.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 sqlf.Stmt to construct a basic query and extend it for a case:

func (o *Offer) Select() *sqlf.Stmt {
    return sqlf.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 := sqlf.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 := sqlf.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 := sqlf.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 := sqlf.From("orders o").
		Select("date, region").
		SubQuery("(", ") AS prev_order_date",
			sqlf.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 := sqlf.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 := sqlf.Select("").
		From("").
		SubQuery(
			"(", ") counted_news",
			sqlf.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 := sqlf.From("tasks").
		Select("id, status").
		Where("status = ?", "new").
		Union(true, sqlf.PostgreSQL.From("tasks").
			Select("id, status").
            Where("status = ?", "wip"))
    // ...
	q.Close()

INSERT

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

var userId int64

err := sqlf.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 := sqlf.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 := sqlf.Update("users").
    Set("email", "new@email.com").
    ExecAndClose(ctx, db)

DELETE

_, err := sqlf.DeleteFrom("products").
    Where("id = ?", 42)
    ExecAndClose(ctx, db)

Documentation

Overview

Package sqlf is an SQL statement builder and executor.

SQL Statement Builder

sqlf 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/leporo/sqlf"
)

type dummyDB int

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

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

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

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

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

	sqlf.SetDialect(sqlf.PostgreSQL)

	err := sqlf.From("orders").
		With("regional_sales",
			sqlf.From("orders").
				Select("region, SUM(amount) AS total_sales").
				GroupBy("region")).
		With("top_regions",
			sqlf.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

• func SetDialect(newDefaultDialect *Dialect)
• type Dialect
  • func (d *Dialect) ClearCache()
  • func (b *Dialect) DeleteFrom(tableName string) *Stmt
  • func (b *Dialect) From(expr string, args ...interface{}) *Stmt
  • func (b *Dialect) InsertInto(tableName string) *Stmt
  • func (b *Dialect) New(verb string, args ...interface{}) *Stmt
  • func (b *Dialect) Select(expr string, args ...interface{}) *Stmt
  • func (b *Dialect) Update(tableName string) *Stmt
  • func (b *Dialect) With(queryName string, query *Stmt) *Stmt
• type Executor
• type Stmt
  • func DeleteFrom(tableName string) *Stmt
  • func From(expr string, args ...interface{}) *Stmt
  • func InsertInto(tableName string) *Stmt
  • func New(verb string, args ...interface{}) *Stmt
  • func Select(expr string, args ...interface{}) *Stmt
  • func Update(tableName string) *Stmt
  • func With(queryName string, query *Stmt) *Stmt
  • func (q *Stmt) Args() []interface{}
  • func (q *Stmt) Bind(data interface{}) *Stmt
  • func (q *Stmt) Clause(expr string, args ...interface{}) *Stmt
  • func (q *Stmt) Clone() *Stmt
  • func (q *Stmt) Close()
  • func (q *Stmt) DeleteFrom(tableName string) *Stmt
  • func (q *Stmt) Dest() []interface{}
  • 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 ...interface{}) *Stmt
  • func (q *Stmt) From(expr string, args ...interface{}) *Stmt
  • func (q *Stmt) FullJoin(table, on string) *Stmt
  • func (q *Stmt) GroupBy(expr string) *Stmt
  • func (q *Stmt) Having(expr string, args ...interface{}) *Stmt
  • func (q *Stmt) In(args ...interface{}) *Stmt
  • func (q *Stmt) InsertInto(tableName string) *Stmt
  • func (q *Stmt) Invalidate()
  • func (q *Stmt) Join(table, on string) *Stmt
  • func (q *Stmt) LeftJoin(table, on string) *Stmt
  • func (q *Stmt) Limit(limit interface{}) *Stmt
  • func (q *Stmt) NewRow() newRow
  • func (q *Stmt) Offset(offset interface{}) *Stmt
  • func (q *Stmt) OrderBy(expr ...string) *Stmt
  • func (q *Stmt) Paginate(page, pageSize int) *Stmt
  • 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) *Stmt
  • func (q *Stmt) RightJoin(table, on string) *Stmt
  • func (q *Stmt) Select(expr string, args ...interface{}) *Stmt
  • func (q *Stmt) Set(field string, value interface{}) *Stmt
  • func (q *Stmt) SetExpr(field, expr string, args ...interface{}) *Stmt
  • func (q *Stmt) String() string
  • func (q *Stmt) SubQuery(prefix, suffix string, query *Stmt) *Stmt
  • func (q *Stmt) To(dest ...interface{}) *Stmt
  • func (q *Stmt) Union(all bool, query *Stmt) *Stmt
  • func (q *Stmt) Update(tableName string) *Stmt
  • func (q *Stmt) Where(expr string, args ...interface{}) *Stmt
  • func (q *Stmt) With(queryName string, query *Stmt) *Stmt

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

Functions

func SetDialect

func SetDialect(newDefaultDialect *Dialect)

SetDialect selects a Dialect to be used by default.

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

sqlf.SetDialect(sqlf.PostgreSQL)

Types

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 := sqlf.PostgreSQL.From("table").Select("field")
	...
q.Close()

or as default mode:

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

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

var (
	// NoDialect is a default statement builder mode.
	NoDialect *Dialect = &Dialect{}
	// PostgreSQL mode is to be used to automatically replace ? placeholders with $1, $2...
	PostgreSQL *Dialect = &Dialect{}
)

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

DeleteFrom starts a DELETE statement.

func (*Dialect) From

func (b *Dialect) From(expr string, args ...interface{}) *Stmt

From starts a SELECT statement.

func (*Dialect) InsertInto

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

InsertInto starts an INSERT statement.

func (*Dialect) New

func (b *Dialect) New(verb string, args ...interface{}) *Stmt

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

func (b *Dialect) Select(expr string, args ...interface{}) *Stmt

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

Update starts an UPDATE statement.

func (*Dialect) With

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

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 ...interface{}) (sql.Result, error)
	QueryContext(ctx context.Context, query string, args ...interface{}) (*sql.Rows, error)
	QueryRowContext(ctx context.Context, query string, args ...interface{}) *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 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:

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

For other SQL statements use New:

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

func DeleteFrom

func DeleteFrom(tableName string) *Stmt

DeleteFrom starts a DELETE statement.

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

func From

func From(expr string, args ...interface{}) *Stmt

From starts a SELECT statement.

var cnt int64

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

func InsertInto

func InsertInto(tableName string) *Stmt

InsertInto starts an INSERT statement.

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

func New

func New(verb string, args ...interface{}) *Stmt

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

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

Use New for special cases like this:

q := sqlf.New("TRANCATE")
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 ...interface{}) *Stmt

Select starts a SELECT statement.

var cnt int64

err := sqlf.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) *Stmt

Update starts an UPDATE statement.

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

func With

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

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

func (*Stmt) Args

func (q *Stmt) Args() []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 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 interface{}) *Stmt

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/leporo/sqlf"
)

type dummyDB int

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

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

func (db *dummyDB) QueryRowContext(ctx context.Context, query string, args ...interface{}) *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 := sqlf.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 ...interface{}) *Stmt

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/leporo/sqlf"
)

func main() {
	q := sqlf.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() *Stmt

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

DeleteFrom adds DELETE clause to a statement.

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

Example

package main

import (
	"fmt"

	"github.com/leporo/sqlf"
)

func main() {
	q := sqlf.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() []interface{}

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 ...interface{}) *Stmt

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 ...interface{}) *Stmt

From adds a FROM clause to statement.

Example

package main

import (
	"fmt"

	"github.com/leporo/sqlf"
)

func main() {
	q := sqlf.Select("*").
		From("").
		SubQuery(
			"(", ") counted_news",
			sqlf.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) *Stmt

FullJoin adds a FULL OUTER JOIN clause to SELECT statement

func (*Stmt) GroupBy

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

GroupBy adds the GROUP BY clause to SELECT statement

Example

package main

import (
	"fmt"

	"github.com/leporo/sqlf"
)

func main() {
	q := sqlf.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 ...interface{}) *Stmt

Having adds the HAVING clause to SELECT statement

Example

package main

import (
	"fmt"

	"github.com/leporo/sqlf"
)

func main() {
	q := sqlf.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 ...interface{}) *Stmt

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/leporo/sqlf"
)

func main() {
	q := sqlf.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) *Stmt

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/leporo/sqlf"
)

func main() {
	q := sqlf.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) *Stmt

Join adds an INNERT JOIN clause to SELECT statement

func (*Stmt) LeftJoin

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

LeftJoin adds a LEFT OUTER JOIN clause to SELECT statement

func (*Stmt) Limit

func (q *Stmt) Limit(limit interface{}) *Stmt

Limit adds a limit on number of returned rows

Example

package main

import (
	"fmt"

	"github.com/leporo/sqlf"
)

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

Output:

SELECT id FROM table LIMIT ?

func (*Stmt) NewRow

func (q *Stmt) NewRow() newRow

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 interface{}) *Stmt

Offset adds a limit on number of returned rows

Example

package main

import (
	"fmt"

	"github.com/leporo/sqlf"
)

func main() {
	q := sqlf.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) *Stmt

OrderBy adds the ORDER BY clause to SELECT statement

Example

package main

import (
	"fmt"

	"github.com/leporo/sqlf"
)

func main() {
	q := sqlf.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) *Stmt

Paginate provides an easy way to set both offset and limit

Example

package main

import (
	"fmt"

	"github.com/leporo/sqlf"
)

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

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

	// Zero and negative values are replaced with 1
	q = sqlf.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/leporo/sqlf"
)

type dummyDB int

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

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

func (db *dummyDB) QueryRowContext(ctx context.Context, query string, args ...interface{}) *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 := sqlf.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) *Stmt

Returning adds a RETURNING clause to a statement

Example

package main

import (
	"fmt"

	"github.com/leporo/sqlf"
)

func main() {
	var newId int
	q := sqlf.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) *Stmt

RightJoin adds a RIGHT OUTER JOIN clause to SELECT statement

func (*Stmt) Select

func (q *Stmt) Select(expr string, args ...interface{}) *Stmt

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

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

Select can be called multiple times to add more columns:

q := sqlf.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 := sqlf.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 interface{}) *Stmt

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 := sqlf.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 ...interface{}) *Stmt

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/leporo/sqlf"
)

func main() {
	q := sqlf.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) String

func (q *Stmt) String() string

String method builds and returns an SQL statement.

func (*Stmt) SubQuery

func (q *Stmt) SubQuery(prefix, suffix string, query *Stmt) *Stmt

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

package main

import (
	"fmt"

	"github.com/leporo/sqlf"
)

func main() {
	q := sqlf.From("orders o").
		Select("date, region").
		SubQuery("(", ") AS prev_order_date",
			sqlf.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 ...interface{}) *Stmt

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 := sqlf.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, query *Stmt) *Stmt

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/leporo/sqlf"
)

func main() {
	q := sqlf.From("tasks").
		Select("id, status").
		Where("status = ?", "new").
		Union(true, sqlf.From("tasks").
			Select("id, status").
			Where("status = ?", "pending")).
		Union(true, sqlf.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) *Stmt

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/leporo/sqlf"
)

func main() {
	q := sqlf.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) Where

func (q *Stmt) Where(expr string, args ...interface{}) *Stmt

Where adds a filter:

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

func (*Stmt) With

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

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/leporo/sqlf"
)

func main() {
	q := sqlf.From("orders").
		With("regional_sales",
			sqlf.From("orders").
				Select("region, SUM(amount) AS total_sales").
				GroupBy("region")).
		With("top_regions",
			sqlf.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