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 

type Ext interface {
	sqlx.Queryer
	sqlx.QueryerContext
	sqlx.Execer
	sqlx.ExecerContext
}

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.

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.