sqlz

package module
v0.0.0-...-487b8fa Latest Latest
Warning

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

Go to latest
Published: Mar 8, 2020 License: Apache-2.0 Imports: 5 Imported by: 0

README

sqlz

sqlz is an SQL query builder for Go.


sqlz is an un-opinionated, un-obtrusive SQL query builder for Go projects, based on sqlx.

As opposed to other query builders, sqlz does not mean to bridge the gap between different SQL servers and implementations by providing a unified interface. Instead, it aims to support an extended SQL syntax that may be implementation-specific. For example, if you wish to use PostgreSQL-specific features such as JSON operators and upsert statements, sqlz means to support these without caring if the underlying database backend really is PostgreSQL. In other words, sqlz builds whatever queries you want it to build.

sqlz is easy to integrate into existing code, as it does not require you to create your database connections through the sqlz API; in fact, it doesn't supply one. You can either use your existing *sql.DB connection or an *sqlx.DB connection, so you can start writing new queries with sqlz without having to modify any existing code.

sqlz leverages sqlx for easy loading of query results. Please make sure you are familiar with how sqlx works in order to understand how row scanning is performed. You may need to add db struct tags to your Go structures.

sqlz provides a comfortable API for running queries in a transaction, and will automatically commit or rollback the transaction as necessary.

NOTE 1: "sqlz" is currently a working name, and may change soon.

NOTE 2: sqlz is in an early stage, currently mostly targeting PostgreSQL. There's much more work to do, but it's definitely usable. API may change, though I'm not currently planning on doing so.

Install

To install sqlz globally:

go get -u github.com/ido50/sqlz

Alternatively, use your preferred Go depedency manager to vendor sqlz into your projects.

Usage

Once installed, you can import sqlz into your Go packages. To build and execute queries with sqlz, you need to pass the underlying *sql.DB or *sqlx.DB objects. If using database/sql, you'll need to tell sqlz the name of the driver (so that it knows which placeholders to use when building queries); if using jmoiron/sqlx, this is not necessary.

package main

import (
    "fmt"
    "database/sql"
    "github.com/ido50/sqlz"
)

func main() {
    driver := "postgres"

    db, err := sql.Open(driver, "dsn")
    if err != nil {
        panic(err)
    }

    // find one row in the database and load it
    // into a struct variable
    var row someStruct
    err = sqlz.New(db, driver).  // if using sqlx: sqlz.Newx(dbx)
        Select("*").
        From("some-table").
        Where(sqlz.Eq("id", 1)).
        GetRow(&row)
    if err != nil {
        panic(err)
    }

    fmt.Printf("%+v\n", row)
}

Examples

Load one row from a table

var row someStruct
err = sqlz.New(db, driver).
    Select("*").
    From("some-table").
    Where(Sqlz.Eq("id", 1)).
    GetRow(&row)

Generated SQL (disregarding placeholders):

   SELECT *
     FROM some-table
    WHERE id = 1

Complex load of many rows with pagination

var rows []struct{
    maxVal int64
    sumCount uint64
}

err = sqlz.New(db, driver).
     Select("MAX(t.col1) maxVal", "SUM(t.col2) sumCount").
     From("some-table t").
     LeftJoin("other-table o", sqlz.Eq("o.id", sqlz.Indirect("t.id"))). // there's also RightJoin, InnerJoin, FullJoin
     GroupBy("t.col3", "t.col4").
     Having(sqlz.Gte("maxVal", 3)).
     OrderBy(sqlz.Desc("maxVal"), sqlz.Asc("sumCount")).
     Limit(5).
     Offset(10).
     Where(sqlz.Or(sqlz.Eq("t.col3", 5), sqlz.IsNotNull("t.col4"))).
     GetAll(&rows)

Generated SQL (disregarding placeholders):

        SELECT MAX(t.col1) maxVal, SUM(t.col2) sumCount
        FROM some-table t
   LEFT JOIN other-table o ON o.id = t.id
       WHERE t.col3 = 5 OR t.col4 IS NOT NULL
    GROUP BY t.col3, t.col4
      HAVING maxVal > 3
    ORDER BY maxVal DESC, sumCount ASC
       LIMIT 5
      OFFSET 10, 20

When paginating results, sqlz provides a nice feature to also calculate the total number of results matching the query, regardless of limiting and offsets:

var rows []struct{
    maxVal int64
    sumCount uint64
}

query := sqlz.New(db, driver).
     Select("MAX(t.col1) maxVal", "SUM(t.col2) sumCount").
     // rest of the query as before
count, err := query.GetCount() // returns total number of results available, regardless of limits and offsets
err = query.GetAll(&rows)      // returns actual results according to limits and offsets

Simple inserts

res, err := sqlz.New(db, driver).
    InsertInto("table").
    Columns("id", "name").
    Values(1, "My Name").
    Exec()

// res is sql.Result

Generated SQL:

INSERT INTO table (id, name) VALUES (?, ?)

Inserts with a value map

res, err := sqlz.New(db, driver).
    InsertInto("table").
    ValueMap(map[string]interface{}{
        "id": 1,
        "name": "My Name",
    }).
    Exec()

Generates the same SQL as for simple inserts.

Inserts returning values

var id int64
err := sqlz.New(db, driver).
    InsertInto("table").
    Columns("name").
    Values("My Name").
    Returning("id").
    GetRow(&id)

Update rows

res, err := sqlz.New(db, driver).
    Update("table").
    Set("col1", "some-string").
    SetMap(map[string]interface{}{
        "col2": true,
        "col3": 5,
    }).
    Where(sqlz.Eq("id", 3)).
    Exec()

Generated SQL:

   UPDATE table
      SET col1 = ?, col2 = ?, col3 = ?
    WHERE id = ?

Updates support the RETURNING clause just like inserts.

Delete rows

res, err := sqlz.New(db, driver).
    DeleteFrom("table").
    Where(sqlz.Eq("id", 3)).
    Exec()

Generated SQL:

   DELETE FROM table
         WHERE id = ?

Easy transactions

sqlz makes it easy to run multiple queries in a transaction, and will automatically rollback or commit as necessary:

sqlz.
    New(db, driver).
    Transactional(func(tx *sqlz.Tx) error {
        var id int64
        err := tx.InsertInto("table").Columns("name").Values("some guy").GetRow(&id)
        if err != nil {
            return err
        }

        _, err = tx.Update("other-table").Set("some-col", 4).Exec()
        if err != nil {
            return err
        }

        return nil
    })

If the function provided to the Transactional method returns an error, the transaction will be rolled back. Otherwise, it will be committed.

Using strings as-is in queries

If you need to compare columns, call database functions, modify columns based on their (or other's) existing values, and any place you need strings to be used as-is and not replaced with placeholders, use the Indirect function:

  • To compare two columns in a WHERE clause, use sqlz.Eq("column-one", sqlz.Indirect("column-two"))
  • To increase a column in a SET clause, use sqlz.Set("int-column", sqlz.Indirect("int-column + 1"))
  • To set a columm using a database function (e.g. LOCALTIMESTAMP), use sqlz.Set("datetime", sqlz.Indirect("LOCALTIMESTAMP"))

Dependencies

The only non-standard library package used is jmoiron/sqlx. The test suite, however, uses DATA-DOG/sqlmock.

Acknowledgments

sqlz was inspired by gocraft/dbr.

Documentation

Overview

Package sqlz implements an SQL query builder based on github.com/jmoiron/sqlx.

Index

Constants

This section is empty.

Variables

This section is empty.

Functions

This section is empty.

Types

type AndOrCondition

type AndOrCondition struct {
	Or         bool
	Conditions []WhereCondition
}

AndOrCondition represents a group of AND or OR conditions.

func And

func And(conds ...WhereCondition) AndOrCondition

And joins multiple where conditions as an AndOrCondition (representing AND conditions). You will use this a lot less than Or as passing multiple conditions to functions like Where or Having are all AND conditions.

func Or

func Or(conds ...WhereCondition) AndOrCondition

Or joins multiple where conditions as an AndOrCondition (representing OR conditions).

func (AndOrCondition) Parse

func (andOr AndOrCondition) Parse() (asSQL string, bindings []interface{})

Parse implements the WhereCondition interface, generating SQL from the condition

type ArrayCondition

type ArrayCondition struct {
	Left     interface{}
	Operator string
	Type     string
	Right    interface{}
}

ArrayCondition represents an array comparison condition

func EqAll

func EqAll(arr interface{}, value interface{}) ArrayCondition

EqAll creates an "= ALL" condition on an array

func EqAny

func EqAny(arr interface{}, value interface{}) ArrayCondition

EqAny creates an "= ANY" condition on an array column

func GtAll

func GtAll(arr interface{}, value interface{}) ArrayCondition

GtAll creates an "> ALL" condition on an array

func GtAny

func GtAny(arr interface{}, value interface{}) ArrayCondition

GtAny creates an "> ANY" condition on an array

func GteAll

func GteAll(arr interface{}, value interface{}) ArrayCondition

GteAll creates an ">= ALL" condition on an array

func GteAny

func GteAny(arr interface{}, value interface{}) ArrayCondition

GteAny creates an ">= ANY" condition on an array

func LikeAny

func LikeAny(arr interface{}, value interface{}) ArrayCondition

LikeAny creates an "Like ANY" condition on an array

func LtAll

func LtAll(arr interface{}, value interface{}) ArrayCondition

LtAll creates an "< ALL" condition on an array

func LtAny

func LtAny(arr interface{}, value interface{}) ArrayCondition

LtAny creates an "< ANY" condition on an array

func LteAll

func LteAll(arr interface{}, value interface{}) ArrayCondition

LteAll creates an "<= ALL" condition on an array

func LteAny

func LteAny(arr interface{}, value interface{}) ArrayCondition

LteAny creates an "<= ANY" condition on an array

func NeAll

func NeAll(arr interface{}, value interface{}) ArrayCondition

NeAll creates an "<> ALL" condition on an array

func NeAny

func NeAny(arr interface{}, value interface{}) ArrayCondition

NeAny creates an "<> ANY" condition on an array

func (ArrayCondition) Parse

func (array ArrayCondition) Parse() (asSQL string, bindings []interface{})

Parse implements the WhereCondition interface, generating SQL from the condition

type AuxStmt

type AuxStmt struct {
	Stmt SQLStmt
	As   string
}

AuxStmt represents an auxiliary statement that is part of a WITH query. It includes the statement itself, and the name used for referencing it in other queries

type ConflictAction

type ConflictAction string

ConflictAction represents an action to perform on an INSERT conflict

const (
	DoNothing ConflictAction = "nothing"
	DoUpdate  ConflictAction = "update"
)

type ConflictClause

type ConflictClause struct {
	Targets []string
	Action  ConflictAction
	SetCols []string
	SetVals []interface{}
	Updates map[string]interface{}
}

ConflictClause represents an ON CONFLICT clause in an INSERT statement

func OnConflict

func OnConflict(targets ...string) *ConflictClause

OnConflict gets a list of targets and creates a new ConflictClause object

func (*ConflictClause) DoNothing

func (conflict *ConflictClause) DoNothing() *ConflictClause

DoNothing sets the conflict clause's action as DO NOTHING

func (*ConflictClause) DoUpdate

func (conflict *ConflictClause) DoUpdate() *ConflictClause

DoUpdate sets the conflict clause's action as DO UPDATE. Caller is expected to set columns to update using Set or SetMap after calling this method.

func (*ConflictClause) Set

func (conflict *ConflictClause) Set(col string, val interface{}) *ConflictClause

Set adds a column to update as part of the conflict resolution

func (*ConflictClause) SetIf

func (conflict *ConflictClause) SetIf(col string, val interface{}, b bool) *ConflictClause

SetIf is the same as Set, but also accepts a boolean value and only does anything if that value is true. This is a convenience method so that conditional updates can be made without having to save the ConflictClause into a variable and using if statements

func (*ConflictClause) SetMap

func (conflict *ConflictClause) SetMap(vals map[string]interface{}) *ConflictClause

SetMap adds a mapping between columns to values to update as part of the conflict resolution

func (*ConflictClause) ToSQL

func (conflict *ConflictClause) ToSQL() (asSQL string, bindings []interface{})

ToSQL generates the SQL code for the conflict clause

type DB

type DB struct {
	*sqlx.DB
	ErrHandlers []func(err error)
}

DB is a wrapper around sqlx.DB (which is a wrapper around sql.DB)

func New

func New(db *sql.DB, driverName string, errHandlersFuncs ...func(err error)) *DB

New creates a new DB instance from an underlying sql.DB object. It requires the name of the SQL driver in order to use the correct placeholders when generating SQL

func Newx

func Newx(db *sqlx.DB) *DB

Newx creates a new DB instance from an underlying sqlx.DB object

func (*DB) DeleteFrom

func (db *DB) DeleteFrom(table string) *DeleteStmt

DeleteFrom creates a new DeleteStmt object for the provided table

func (*DB) InsertInto

func (db *DB) InsertInto(table string) *InsertStmt

InsertInto creates a new InsertStmt object for the provided table

func (*DB) Select

func (db *DB) Select(cols ...string) *SelectStmt

Select creates a new SelectStmt object, selecting the provided columns. You can use any SQL syntax supported by your database system, e.g. Select("*"), Select("one", "two t", "MAX(three) maxThree")

func (*DB) Transactional

func (db *DB) Transactional(f func(tx *Tx) error) error

Transactional runs the provided function inside a transaction. The function must receive an sqlz Tx object, and return an error. If the function returns an error, the transaction is automatically rolled back. Otherwise, the transaction is committed.

func (*DB) TransactionalContext

func (db *DB) TransactionalContext(ctx context.Context, opts *sql.TxOptions, f func(tx *Tx) error) error

TransactionalContext runs the provided function inside a transaction. The function must receive an sqlz Tx object, and return an error. If the function returns an error, the transaction is automatically rolled back. Otherwise, the transaction is committed.

func (*DB) Update

func (db *DB) Update(table string) *UpdateStmt

Update creates a new UpdateStmt object for the specified table

func (*DB) With

func (db *DB) With(stmt SQLStmt, as string) *WithStmt

With creates a new WithStmt object including the provided auxiliary statements

type DeleteStmt

type DeleteStmt struct {
	*Statment
	Table       string
	Conditions  []WhereCondition
	UsingTables []string
	Return      []string
	// contains filtered or unexported fields
}

DeleteStmt represents a DELETE statement

func (*DeleteStmt) Exec

func (stmt *DeleteStmt) Exec() (res sql.Result, err error)

Exec executes the DELETE statement, returning the standard sql.Result struct and an error if the query failed.

func (*DeleteStmt) ExecContext

func (stmt *DeleteStmt) ExecContext(ctx context.Context) (res sql.Result, err error)

ExecContext executes the DELETE statement, returning the standard sql.Result struct and an error if the query failed.

func (*DeleteStmt) GetAll

func (stmt *DeleteStmt) GetAll(into interface{}) error

GetAll executes a DELETE statement with a RETURNING clause expected to return multiple rows, and loads the result into the provided slice variable

func (*DeleteStmt) GetAllContext

func (stmt *DeleteStmt) GetAllContext(ctx context.Context, into interface{}) error

GetAllContext executes a DELETE statement with a RETURNING clause expected to return multiple rows, and loads the result into the provided slice variable

func (*DeleteStmt) GetRow

func (stmt *DeleteStmt) GetRow(into interface{}) error

GetRow executes a DELETE statement with a RETURNING clause expected to return one row, and loads the result into the provided variable (which may be a simple variable if only one column is returned, or a struct if multiple columns are returned)

func (*DeleteStmt) GetRowContext

func (stmt *DeleteStmt) GetRowContext(ctx context.Context, into interface{}) error

GetRowContext executes a DELETE statement with a RETURNING clause expected to return one row, and loads the result into the provided variable (which may be a simple variable if only one column is returned, or a struct if multiple columns are returned)

func (*DeleteStmt) Returning

func (stmt *DeleteStmt) Returning(cols ...string) *DeleteStmt

Returning sets a RETURNING clause to receive values back from the database once executing the DELETE statement. Note that GetRow or GetAll must be used to execute the query rather than Exec to get back the values.

func (*DeleteStmt) ToSQL

func (stmt *DeleteStmt) ToSQL(rebind bool) (asSQL string, bindings []interface{})

ToSQL generates the DELETE statement's SQL and returns a list of bindings. It is used internally by Exec, but is exported if you wish to use it directly.

func (*DeleteStmt) Using

func (stmt *DeleteStmt) Using(tables ...string) *DeleteStmt

Using adds a USING clause for joining in a delete statement

func (*DeleteStmt) Where

func (stmt *DeleteStmt) Where(conds ...WhereCondition) *DeleteStmt

Where creates one or more WHERE conditions for the DELETE statement. If multiple conditions are passed, they are considered AND conditions.

type Ext

Ext is a union interface which can bind, query, and exec, with or without contexts, used by NamedQuery and NamedExec

type InCondition

type InCondition struct {
	NotIn bool
	Left  string
	Right []interface{}
}

InCondition is a struct representing IN and NOT IN conditions

func In

func In(col string, values ...interface{}) InCondition

In creates an IN condition for matching the value of a column against an array of possible values

func NotIn

func NotIn(col string, values ...interface{}) InCondition

NotIn creates a NOT IN condition for checking that the value of a column is not one of the defined values

func (InCondition) Parse

func (in InCondition) Parse() (asSQL string, bindings []interface{})

Parse implements the WhereCondition interface, generating SQL from the condition

type IndirectValue

type IndirectValue struct {
	Reference string
	Bindings  []interface{}
}

IndirectValue represents a reference to a database name (e.g. column, function) that should be used as-is in a query rather than replaced with a placeholder.

func Indirect

func Indirect(value string, bindings ...interface{}) IndirectValue

Indirect receives a string and injects it into a query as-is rather than with a placeholder. Use this when comparing columns, modifying columns based on their (or others') existing values, using database functions, etc. Never use this with user-supplied input, as this may open the door for SQL injections!

func (IndirectValue) ToSQL

func (i IndirectValue) ToSQL(_ bool) (string, []interface{})

type InsertStmt

type InsertStmt struct {
	*Statment
	InsCols         []string
	InsVals         []interface{}
	InsMultipleVals [][]interface{}
	SelectStmt      *SelectStmt
	Table           string
	Return          []string
	Conflicts       []*ConflictClause
	// contains filtered or unexported fields
}

InsertStmt represents an INSERT statement

func (*InsertStmt) Columns

func (stmt *InsertStmt) Columns(cols ...string) *InsertStmt

Columns defines the columns to insert. It can be safely used alongside ValueMap in the same query, provided Values is used immediately after Columns

func (*InsertStmt) Exec

func (stmt *InsertStmt) Exec() (res sql.Result, err error)

Exec executes the INSERT statement, returning the standard sql.Result struct and an error if the query failed.

func (*InsertStmt) ExecContext

func (stmt *InsertStmt) ExecContext(ctx context.Context) (res sql.Result, err error)

ExecContext executes the INSERT statement, returning the standard sql.Result struct and an error if the query failed.

func (*InsertStmt) FromSelect

func (stmt *InsertStmt) FromSelect(selStmt *SelectStmt) *InsertStmt

Select sets a SELECT statements that will supply the rows to be inserted.

func (*InsertStmt) GetAll

func (stmt *InsertStmt) GetAll(into interface{}) error

GetAll executes an INSERT statement with a RETURNING clause expected to return multiple rows, and loads the result into the provided slice variable

func (*InsertStmt) GetAllContext

func (stmt *InsertStmt) GetAllContext(ctx context.Context, into interface{}) error

GetAllContext executes an INSERT statement with a RETURNING clause expected to return multiple rows, and loads the result into the provided slice variable

func (*InsertStmt) GetRow

func (stmt *InsertStmt) GetRow(into interface{}) error

GetRow executes an INSERT statement with a RETURNING clause expected to return one row, and loads the result into the provided variable (which may be a simple variable if only one column is returned, or a struct if multiple columns are returned)

func (*InsertStmt) GetRowContext

func (stmt *InsertStmt) GetRowContext(ctx context.Context, into interface{}) error

GetRowContext executes an INSERT statement with a RETURNING clause expected to return one row, and loads the result into the provided variable (which may be a simple variable if only one column is returned, or a struct if multiple columns are returned)

func (*InsertStmt) OnConflict

func (stmt *InsertStmt) OnConflict(clause *ConflictClause) *InsertStmt

OnConflict adds an ON CONFLICT clause to the statement

func (*InsertStmt) OnConflictDoNothing

func (stmt *InsertStmt) OnConflictDoNothing() *InsertStmt

OnConflictDoNothing sets an ON CONFLICT clause on the statement. This method is deprecated in favor of OnConflict.

func (*InsertStmt) OrAbort

func (stmt *InsertStmt) OrAbort() *InsertStmt

OrAbort enables the "OR ABORT" conflict resolution for SQLIte inserts

func (*InsertStmt) OrFail

func (stmt *InsertStmt) OrFail() *InsertStmt

OrFail enables the "OR FAIL" conflict resolution for SQLIte inserts

func (*InsertStmt) OrIgnore

func (stmt *InsertStmt) OrIgnore() *InsertStmt

OrIgnore enables the "OR IGNORE" conflict resolution for SQLIte inserts

func (*InsertStmt) OrReplace

func (stmt *InsertStmt) OrReplace() *InsertStmt

OrReplace enables the "OR REPLACE" conflict resolution for SQLIte inserts

func (*InsertStmt) OrRollback

func (stmt *InsertStmt) OrRollback() *InsertStmt

OrRollback enables the "OR ROLLBACK" conflict resolution for SQLIte inserts

func (*InsertStmt) Returning

func (stmt *InsertStmt) Returning(cols ...string) *InsertStmt

Returning sets a RETURNING clause to receive values back from the database once executing the INSERT statement. Note that GetRow or GetAll must be used to execute the query rather than Exec to get back the values.

func (*InsertStmt) ToSQL

func (stmt *InsertStmt) ToSQL(rebind bool) (asSQL string, bindings []interface{})

ToSQL generates the INSERT statement's SQL and returns a list of bindings. It is used internally by Exec, GetRow and GetAll, but is exported if you wish to use it directly.

func (*InsertStmt) ValueMap

func (stmt *InsertStmt) ValueMap(vals map[string]interface{}) *InsertStmt

ValueMap receives a map of columns and values to insert

func (*InsertStmt) ValueMultiple

func (stmt *InsertStmt) ValueMultiple(vals [][]interface{}) *InsertStmt

ValueMultiple receives an array of interfaces in order to insert multiple records using the same insert statement

func (*InsertStmt) Values

func (stmt *InsertStmt) Values(vals ...interface{}) *InsertStmt

Values sets the values to insert to the table (based on the columns provided via Columns)

type JSONBBuilder

type JSONBBuilder struct {
	Array    bool
	Bindings []interface{}
}

func BuildJSONBArray

func BuildJSONBArray(in ...interface{}) (out JSONBBuilder)

func BuildJSONBObject

func BuildJSONBObject(in map[string]interface{}) (out JSONBBuilder)

func (JSONBBuilder) Parse

func (b JSONBBuilder) Parse() (asSQL string, bindings []interface{})

type JSONBObject

type JSONBObject struct {
	Bindings []interface{}
}

type JoinClause

type JoinClause struct {
	Type       JoinType
	Table      string
	ResultSet  *SelectStmt
	Conditions []WhereCondition
}

JoinClause represents a JOIN clause in a SELECT statement

type JoinType

type JoinType int

JoinType is an enumerated type representing the type of a JOIN clause (INNER, LEFT, RIGHT or FULL)

const (
	InnerJoin JoinType = iota
	LeftJoin
	RightJoin
	FullJoin
	InnerLateralJoin
	LeftLateralJoin
	RightLateralJoin
)

InnerJoin represents an inner join LeftJoin represents a left join RightJoin represents a right join FullJoin represents a full join InnerLateralJoin represents an inner lateral join LeftLateralJoin represents a left lateral join RightLateralJoin represents a right lateral join

func (JoinType) IsLateral

func (j JoinType) IsLateral() bool

func (JoinType) String

func (j JoinType) String() string

String returns the string representation of the join type (e.g. "FULL JOIN")

type LockClause

type LockClause struct {
	Strength LockStrength
	Tables   []string
	Wait     LockWait
}

LockClause represents a row or table level locking for a SELECT statement

func ForKeyShare

func ForKeyShare() *LockClause

ForKeyShare adds a "FOR KEY SHARE" lock clause on the statement

func ForNoKeyUpdate

func ForNoKeyUpdate() *LockClause

ForNoKeyUpdate adds a "FOR NO KEY UPDATE" lock clause on the statement

func ForShare

func ForShare() *LockClause

ForShare adds a "FOR SHARE" lock clause on the statement

func ForUpdate

func ForUpdate() *LockClause

ForUpdate adds a "FOR UPDATE" lock clause on the statement

func (*LockClause) NoWait

func (lock *LockClause) NoWait() *LockClause

func (*LockClause) OfTables

func (lock *LockClause) OfTables(tables ...string) *LockClause

func (*LockClause) SkipLocked

func (lock *LockClause) SkipLocked() *LockClause

type LockStrength

type LockStrength int8

LockStrength represents the strength of a LockClause

const (
	LockForUpdate LockStrength = iota
	LockForNoKeyUpdate
	LockForShare
	LockForKeyShare
)

type LockWait

type LockWait int8

LockWait represents the behavior of the database when a lock cannot be acquired

const (
	LockDefault LockWait = iota
	LockNoWait
	LockSkipLocked
)

type OrderColumn

type OrderColumn struct {
	Column string
	Desc   bool
}

OrderColumn represents a column in an ORDER BY clause (with direction)

func Asc

func Asc(col string) OrderColumn

Asc creates an OrderColumn for the provided column in ascending order

func Desc

func Desc(col string) OrderColumn

Desc creates an OrderColumn for the provided column in descending order

func (OrderColumn) ToSQL

func (o OrderColumn) ToSQL(_ bool) (string, []interface{})

ToSQL generates SQL for an OrderColumn

type PreCondition

type PreCondition struct {
	Pre       string
	Condition WhereCondition
}

PreCondition represents pre-condition operator

func Not

func Not(cond WhereCondition) PreCondition

Not represents a pre condition ("NOT" operator)

func (PreCondition) Parse

func (pre PreCondition) Parse() (asSQL string, bindings []interface{})

Parse implements the WhereCondition interface, generating SQL from the condition

type Queryer

type Queryer interface {
	sqlx.Queryer
	sqlx.QueryerContext
}

Queryer is an interface used by Get and Select, with or without context

type SQLCondition

type SQLCondition struct {
	Condition string
	Binds     []interface{}
}

SQLCondition represents a condition written directly in SQL, allows using complex SQL conditions not yet supported by sqlz

func SQLCond

func SQLCond(condition string, binds ...interface{}) SQLCondition

SQLCond creates an SQL condition, allowing to use complex SQL conditions that are not yet supported by sqlz. Question marks must be used for placeholders in the condition regardless of the database driver.

func (SQLCondition) Parse

func (cond SQLCondition) Parse() (asSQL string, bindings []interface{})

Parse implements the WhereCondition interface, generating SQL from the condition

type SQLStmt

type SQLStmt interface {
	ToSQL(bool) (string, []interface{})
}

SQLStmt is an interface representing a general SQL statement. All specific statement types (e.g. SelectStmt, UpdateStmt, etc.) implement this interface

type SelectStmt

type SelectStmt struct {
	*Statment
	IsDistinct      bool
	IsUnionAll      bool
	DistinctColumns []string
	Columns         []string
	Table           string
	Joins           []JoinClause
	Conditions      []WhereCondition
	Ordering        []SQLStmt
	Grouping        []string
	GroupConditions []WhereCondition
	Unions          []*SelectStmt
	Locks           []*LockClause
	LimitTo         int64
	OffsetFrom      int64
	OffsetRows      int64
	// contains filtered or unexported fields
}

SelectStmt represents a SELECT statement

func (*SelectStmt) Distinct

func (stmt *SelectStmt) Distinct(cols ...string) *SelectStmt

Distinct marks the statements as a SELECT DISTINCT statement

func (*SelectStmt) From

func (stmt *SelectStmt) From(table string) *SelectStmt

From sets the table to select from

func (*SelectStmt) FullJoin

func (stmt *SelectStmt) FullJoin(table string, conds ...WhereCondition) *SelectStmt

FullJoin is a wrapper of Join for creating a FULL JOIN on a table with the provided conditions

func (*SelectStmt) FullJoinRS

func (stmt *SelectStmt) FullJoinRS(rs *SelectStmt, as string, conds ...WhereCondition) *SelectStmt

FullJoinRS is a wrapper of Join for creating a FULL JOIN on the results of a sub-query

func (*SelectStmt) GetAll

func (stmt *SelectStmt) GetAll(into interface{}) error

GetAll executes the SELECT statement and loads all the results into the provided slice variable.

func (*SelectStmt) GetAllAsMaps

func (stmt *SelectStmt) GetAllAsMaps() (maps []map[string]interface{}, err error)

GetAllAsMaps executes the SELECT statement and returns all results as a slice of maps from string to empty interfaces. This is useful for intermediary query where creating a struct type would be redundant

func (*SelectStmt) GetAllAsRows

func (stmt *SelectStmt) GetAllAsRows() (rows *sqlx.Rows, err error)

GetAllAsRows executes the SELECT statement and returns an sqlx.Rows object to use for iteration. It is the caller's responsibility to close the cursor with Close().

func (*SelectStmt) GetAllAsRowsContext

func (stmt *SelectStmt) GetAllAsRowsContext(ctx context.Context) (rows *sqlx.Rows, err error)

GetAllAsRowsContext executes the SELECT statement and returns an sqlx.Rows object to use for iteration. It is the caller's responsibility to close the cursor with Close().

func (*SelectStmt) GetAllContext

func (stmt *SelectStmt) GetAllContext(ctx context.Context, into interface{}) error

GetAllContext executes the SELECT statement and loads all the results into the provided slice variable.

func (*SelectStmt) GetCount

func (stmt *SelectStmt) GetCount() (count int64, err error)

GetCount executes the SELECT statement disregarding limits, offsets, selected columns and ordering; and returns the total number of matching results. This is useful when paginating results.

func (*SelectStmt) GetCountContext

func (stmt *SelectStmt) GetCountContext(ctx context.Context) (count int64, err error)

GetCountContext executes the SELECT statement disregarding limits, offsets, selected columns and ordering; and returns the total number of matching results. This is useful when paginating results.

func (*SelectStmt) GetRow

func (stmt *SelectStmt) GetRow(into interface{}) error

GetRow executes the SELECT statement and loads the first result into the provided variable (which may be a simple variable if only one column was selected, or a struct if multiple columns were selected).

func (*SelectStmt) GetRowAsMap

func (stmt *SelectStmt) GetRowAsMap() (results map[string]interface{}, err error)

GetRowAsMap executes the SELECT statement and returns the first result as a map from string to empty interfaces. This is useful for intermediary query where creating a struct type would be redundant

func (*SelectStmt) GetRowContext

func (stmt *SelectStmt) GetRowContext(ctx context.Context, into interface{}) error

GetRowContext executes the SELECT statement and loads the first result into the provided variable (which may be a simple variable if only one column was selected, or a struct if multiple columns were selected).

func (*SelectStmt) GroupBy

func (stmt *SelectStmt) GroupBy(cols ...string) *SelectStmt

GroupBy sets a GROUP BY clause with the provided columns.

func (*SelectStmt) Having

func (stmt *SelectStmt) Having(conditions ...WhereCondition) *SelectStmt

Having sets HAVING conditions for aggregated values. Usage is the same as Where.

func (*SelectStmt) InnerJoin

func (stmt *SelectStmt) InnerJoin(table string, conds ...WhereCondition) *SelectStmt

InnerJoin is a wrapper of Join for creating a INNER JOIN on a table with the provided conditions

func (*SelectStmt) InnerJoinRS

func (stmt *SelectStmt) InnerJoinRS(rs *SelectStmt, as string, conds ...WhereCondition) *SelectStmt

InnerJoinRS is a wrapper of Join for creating a INNER JOIN on the results of a sub-query

func (*SelectStmt) InnerLateralJoin

func (stmt *SelectStmt) InnerLateralJoin(rs *SelectStmt, as string, conds ...WhereCondition) *SelectStmt

func (*SelectStmt) Join

func (stmt *SelectStmt) Join(joinType JoinType, table string, resultSet *SelectStmt, conds ...WhereCondition) *SelectStmt

Join creates a new join with the supplied type, on the supplied table or result set (a sub-select statement), using the provided conditions. Since conditions in a JOIN clause usually compare two columns, use sqlz.Indirect in your conditions.

func (*SelectStmt) LeftJoin

func (stmt *SelectStmt) LeftJoin(table string, conds ...WhereCondition) *SelectStmt

LeftJoin is a wrapper of Join for creating a LEFT JOIN on a table with the provided conditions

func (*SelectStmt) LeftJoinRS

func (stmt *SelectStmt) LeftJoinRS(rs *SelectStmt, as string, conds ...WhereCondition) *SelectStmt

LeftJoinRS is a wrapper of Join for creating a LEFT JOIN on the results of a sub-query

func (*SelectStmt) LeftLateralJoin

func (stmt *SelectStmt) LeftLateralJoin(rs *SelectStmt, as string, conds ...WhereCondition) *SelectStmt

func (*SelectStmt) Limit

func (stmt *SelectStmt) Limit(limit int64) *SelectStmt

Limit limits the amount of results returned to the provided value (this is a LIMIT clause). In some database systems, Offset with two values should be used instead.

func (*SelectStmt) Lock

func (stmt *SelectStmt) Lock(lock *LockClause) *SelectStmt

func (*SelectStmt) Offset

func (stmt *SelectStmt) Offset(start int64, rows ...int64) *SelectStmt

Offset skips the provided number of results. In supporting database systems, you can provide a limit on the number of the returned results as the second parameter

func (*SelectStmt) OrderBy

func (stmt *SelectStmt) OrderBy(cols ...SQLStmt) *SelectStmt

OrderBy sets an ORDER BY clause for the query. Pass OrderColumn objects using the Asc and Desc functions.

func (*SelectStmt) RightJoin

func (stmt *SelectStmt) RightJoin(table string, conds ...WhereCondition) *SelectStmt

RightJoin is a wrapper of Join for creating a RIGHT JOIN on a table with the provided conditions

func (*SelectStmt) RightJoinRS

func (stmt *SelectStmt) RightJoinRS(rs *SelectStmt, as string, conds ...WhereCondition) *SelectStmt

RightJoinRS is a wrapper of Join for creating a RIGHT JOIN on the results of a sub-query

func (*SelectStmt) RightLateralJoin

func (stmt *SelectStmt) RightLateralJoin(rs *SelectStmt, as string, conds ...WhereCondition) *SelectStmt

func (*SelectStmt) ToSQL

func (stmt *SelectStmt) ToSQL(rebind bool) (asSQL string, bindings []interface{})

ToSQL generates the SELECT statement's SQL and returns a list of bindings. It is used internally by GetRow and GetAll, but is exported if you wish to use it directly.

func (*SelectStmt) Union

func (stmt *SelectStmt) Union(statements ...*SelectStmt) *SelectStmt

Union adds the 'UNION' command between two SELECT statements or more

func (*SelectStmt) UnionAll

func (stmt *SelectStmt) UnionAll(statements ...*SelectStmt) *SelectStmt

Union adds the 'UNION ALL' command between two SELECT statements or more

func (*SelectStmt) Where

func (stmt *SelectStmt) Where(conditions ...WhereCondition) *SelectStmt

Where creates one or more WHERE conditions for the SELECT statement. If multiple conditions are passed, they are considered AND conditions.

func (*SelectStmt) WithNullsFirst

func (stmt *SelectStmt) WithNullsFirst() *SelectStmt

OrderBy with null values first

func (*SelectStmt) WithNullsLast

func (stmt *SelectStmt) WithNullsLast() *SelectStmt

OrderBy with null values last

type SimpleCondition

type SimpleCondition struct {
	Left     string
	Right    interface{}
	Operator string
}

SimpleCondition represents the most basic WHERE condition, where one left-value (usually a column) is compared with a right-value using an operator (e.g. "=", "<>", ">=", ...)

func Eq

func Eq(col string, value interface{}) SimpleCondition

Eq represents a simple equality condition ("=" operator)

func Gt

func Gt(col string, value interface{}) SimpleCondition

Gt represents a simple greater-than condition (">" operator)

func Gte

func Gte(col string, value interface{}) SimpleCondition

Gte represents a simple greater-than-or-equals condition (">=" operator)

func ILike

func ILike(col string, value interface{}) SimpleCondition

ILike represents a wildcard equality condition ("ILIKE" operator)

func IsNotNull

func IsNotNull(col string) SimpleCondition

IsNotNull represents a simple non-nullity condition ("IS NOT NULL" operator)

func IsNull

func IsNull(col string) SimpleCondition

IsNull represents a simple nullity condition ("IS NULL" operator)

func JSONBOp

func JSONBOp(op string, left string, value interface{}) SimpleCondition

JSONBOp creates simple conditions with JSONB operators for PostgreSQL databases (supported operators are "@>", "<@", "?", "?!", "?&", "||", "-" and "#-")

func Like

func Like(col string, value interface{}) SimpleCondition

Like represents a wildcard equality condition ("LIKE" operator)

func Lt

func Lt(col string, value interface{}) SimpleCondition

Lt represents a simple less-than condition ("<" operator)

func Lte

func Lte(col string, value interface{}) SimpleCondition

Lte represents a simple less-than-or-equals condition ("<=" operator)

func Ne

func Ne(col string, value interface{}) SimpleCondition

Ne represents a simple non-equality condition ("<>" operator)

func NotLike

func NotLike(col string, value interface{}) SimpleCondition

NotLike represents a wildcard non-equality condition ("NOT LIKE" operator)

func (SimpleCondition) Parse

func (simple SimpleCondition) Parse() (asSQL string, bindings []interface{})

Parse implements the WhereCondition interface, generating SQL from the condition

type Statment

type Statment struct {
	ErrHandlers []func(err error)
}

func (*Statment) HandlerError

func (stmt *Statment) HandlerError(err error)

type SubqueryCondition

type SubqueryCondition struct {
	Stmt     *SelectStmt
	Operator string
}

SubqueryCondition is a WHERE condition on the results of a sub-query.

func Exists

func Exists(stmt *SelectStmt) SubqueryCondition

Exists creates a sub-query condition checking the sub-query returns results ("EXISTS" operator)

func NotExists

func NotExists(stmt *SelectStmt) SubqueryCondition

NotExists creates a sub-query condition checking the sub-query does not return results ("NOT EXISTS" operator)

func (SubqueryCondition) Parse

func (subCond SubqueryCondition) Parse() (asSQL string, bindings []interface{})

Parse implements the WhereCondition interface, generating SQL from the condition

type Tx

type Tx struct {
	*sqlx.Tx
	ErrHandlers []func(err error)
}

Tx is a wrapper around sqlx.Tx (which is a wrapper around sql.Tx)

func (*Tx) DeleteFrom

func (tx *Tx) DeleteFrom(table string) *DeleteStmt

DeleteFrom creates a new DeleteStmt object for the provided table

func (*Tx) InsertInto

func (tx *Tx) InsertInto(table string) *InsertStmt

InsertInto creates a new InsertStmt object for the provided table

func (*Tx) Select

func (tx *Tx) Select(cols ...string) *SelectStmt

Select creates a new SelectStmt object, selecting the provided columns. You can use any SQL syntax supported by your database system, e.g. Select("*"), Select("one", "two t", "MAX(three) maxThree")

func (*Tx) Update

func (tx *Tx) Update(table string) *UpdateStmt

Update creates a new UpdateStmt object for the specified table

func (*Tx) With

func (tx *Tx) With(stmt SQLStmt, as string) *WithStmt

With creates a new WithStmt object including the provided auxiliary statements

type UpdateFunction

type UpdateFunction struct {
	Name      string
	Arguments []interface{}
}

UpdateFunction represents a function call in the context of updating a column's value. For example, PostgreSQL provides functions to append, prepend or remove items from array columns.

func ArrayAppend

func ArrayAppend(name string, value interface{}) UpdateFunction

ArrayAppend is an UpdateFunction for calling PostgreSQL's array_append function during an update.

func ArrayPrepend

func ArrayPrepend(name string, value interface{}) UpdateFunction

ArrayPrepend is an UpdateFunction for calling PostgreSQL's array_prepend function during an update.

func ArrayRemove

func ArrayRemove(name string, value interface{}) UpdateFunction

ArrayRemove is an UpdateFunction for calling PostgreSQL's array_remove function during an update.

type UpdateStmt

type UpdateStmt struct {
	*Statment
	Table      string
	Updates    map[string]interface{}
	Conditions []WhereCondition
	Return     []string

	SelectStmt      *SelectStmt
	SelectStmtAlias string
	// contains filtered or unexported fields
}

UpdateStmt represents an UPDATE statement

func (*UpdateStmt) Exec

func (stmt *UpdateStmt) Exec() (res sql.Result, err error)

Exec executes the UPDATE statement, returning the standard sql.Result struct and an error if the query failed.

func (*UpdateStmt) ExecContext

func (stmt *UpdateStmt) ExecContext(ctx context.Context) (res sql.Result, err error)

ExecContext executes the UPDATE statement, returning the standard sql.Result struct and an error if the query failed.

func (*UpdateStmt) FromSelect

func (stmt *UpdateStmt) FromSelect(selStmt *SelectStmt, alias string) *UpdateStmt

func (*UpdateStmt) GetAll

func (stmt *UpdateStmt) GetAll(into interface{}) error

GetAll executes an UPDATE statement with a RETURNING clause expected to return multiple rows, and loads the result into the provided slice variable

func (*UpdateStmt) GetAllContext

func (stmt *UpdateStmt) GetAllContext(ctx context.Context, into interface{}) error

GetAllContext executes an UPDATE statement with a RETURNING clause expected to return multiple rows, and loads the result into the provided slice variable

func (*UpdateStmt) GetRow

func (stmt *UpdateStmt) GetRow(into interface{}) error

GetRow executes an UPDATE statement with a RETURNING clause expected to return one row, and loads the result into the provided variable (which may be a simple variable if only one column is returned, or a struct if multiple columns are returned)

func (*UpdateStmt) GetRowContext

func (stmt *UpdateStmt) GetRowContext(ctx context.Context, into interface{}) error

GetRowContext executes an UPDATE statement with a RETURNING clause expected to return one row, and loads the result into the provided variable (which may be a simple variable if only one column is returned, or a struct if multiple columns are returned)

func (*UpdateStmt) Returning

func (stmt *UpdateStmt) Returning(cols ...string) *UpdateStmt

Returning sets a RETURNING clause to receive values back from the database once executing the UPDATE statement. Note that GetRow or GetAll must be used to execute the query rather than Exec to get back the values.

func (*UpdateStmt) Set

func (stmt *UpdateStmt) Set(col string, value interface{}) *UpdateStmt

Set receives the name of a column and a new value. Multiple calls to Set can be chained together to modify multiple columns. Set can also be chained with calls to SetMap

func (*UpdateStmt) SetIf

func (stmt *UpdateStmt) SetIf(col string, value interface{}, b bool) *UpdateStmt

SetIf is the same as Set, but also accepts a boolean value and only does anything if that value is true. This is a convenience method so that conditional updates can be made without having to save the UpdateStmt into a variable and using if statements

func (*UpdateStmt) SetMap

func (stmt *UpdateStmt) SetMap(updates map[string]interface{}) *UpdateStmt

SetMap receives a map of columns and values. Multiple calls to both Set and SetMap can be chained to modify multiple columns.

func (*UpdateStmt) ToSQL

func (stmt *UpdateStmt) ToSQL(rebind bool) (asSQL string, bindings []interface{})

ToSQL generates the UPDATE statement's SQL and returns a list of bindings. It is used internally by Exec, GetRow and GetAll, but is exported if you wish to use it directly.

func (*UpdateStmt) Where

func (stmt *UpdateStmt) Where(conditions ...WhereCondition) *UpdateStmt

Where creates one or more WHERE conditions for the UPDATE statement. If multiple conditions are passed, they are considered AND conditions.

type WhereCondition

type WhereCondition interface {
	Parse() (asSQL string, bindings []interface{})
}

WhereCondition is an interface describing conditions that can be used inside an SQL WHERE clause. It defines the Parse function that generates SQL (with placeholders) from the condition(s) and returns a list of data bindings for the placeholders (if any)

type WithStmt

type WithStmt struct {
	// AuxStmts is the list of auxiliary statements that are
	// part of the WITH query
	AuxStmts []AuxStmt
	// MainStmt is the query's main statement in which the
	// auxiliary statements can be referenced
	MainStmt SQLStmt
	// contains filtered or unexported fields
}

WithStmt represents a WITH statement

func (*WithStmt) And

func (stmt *WithStmt) And(auxStmt SQLStmt, as string) *WithStmt

And adds another auxiliary statement to the query

func (*WithStmt) Exec

func (stmt *WithStmt) Exec() (res sql.Result, err error)

Exec executes the WITH statement, returning the standard sql.Result struct and an error if the query failed.

func (*WithStmt) ExecContext

func (stmt *WithStmt) ExecContext(ctx context.Context) (res sql.Result, err error)

ExecContext executes the WITH statement, returning the standard sql.Result struct and an error if the query failed.

func (*WithStmt) GetAll

func (stmt *WithStmt) GetAll(into interface{}) error

GetAll executes a WITH statement whose main statement has a RETURNING clause expected to return multiple rows, and loads the result into the provided slice variable

func (*WithStmt) GetAllAsRows

func (stmt *WithStmt) GetAllAsRows() (rows *sqlx.Rows, err error)

GetAllAsRows executes the WITH statement and returns an sqlx.Rows object to use for iteration. It is the caller's responsibility to close the cursor with Close().

func (*WithStmt) GetAllContext

func (stmt *WithStmt) GetAllContext(ctx context.Context, into interface{}) error

GetAllContext executes a WITH statement whose main statement has a RETURNING clause expected to return multiple rows, and loads the result into the provided slice variable

func (*WithStmt) GetRow

func (stmt *WithStmt) GetRow(into interface{}) error

GetRow executes a WITH statement whose main statement has a RETURNING clause expected to return one row, and loads the result into the provided variable (which may be a simple variable if only one column is returned, or a struct if multiple columns are returned)

func (*WithStmt) GetRowContext

func (stmt *WithStmt) GetRowContext(ctx context.Context, into interface{}) error

GetRowContext executes a WITH statement whose main statement has a RETURNING clause expected to return one row, and loads the result into the provided variable (which may be a simple variable if only one column is returned, or a struct if multiple columns are returned)

func (*WithStmt) Then

func (stmt *WithStmt) Then(mainStmt SQLStmt) *WithStmt

Then sets the main statement of the WITH query

func (*WithStmt) ToSQL

func (stmt *WithStmt) ToSQL(rebind bool) (asSQL string, bindings []interface{})

ToSQL generates the WITH statement's SQL and returns a list of bindings. It is used internally by Exec, GetRow and GetAll, but is exported if you wish to use it directly.

Jump to

Keyboard shortcuts

? : This menu
/ : Search site
f or F : Jump to
y or Y : Canonical URL