pgq

package module
v0.0.3 Latest Latest
Warning

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

 Go to latest Published: Sep 23, 2024 License: MIT Imports: 8 Imported by: 1

README

pgq

GoDoc Build Status Coverage Status

pgq is a query builder for PostgreSQL written in Go.

It is a fork of Squirrel more suitable for working with the PostgreSQL database when you are able to use the native PostgreSQL protocol directly, rather than the slower textual protocol used by database/sql.

You can use it with the pgx driver.

Usage example

Something like this:

sql, args, err := pgq.Update("employees").
	Set("salary_bonus", pgq.Expr("salary_bonus + 1000")).
	From("accounts").
	Where("accounts.team = ?", "engineering").
	Returning("id", "name", "salary").SQL()

if err != nil {
	panic(err) // bug: this should never happen.
}

type employee struct {
	ID string
	Name string
	Salary int
}
var data []employee
rows, err := pool.Query(context.Background(), sql, args...)
if err == nil {
	defer rows.Close()
	data, err = pgx.CollectRows(rows, pgx.RowTo[employee])
}

Main benefits

  • API is crafted with only PostgreSQL compatibility so it has a somewhat lean API.
  • It uses ANY and ALL operators for slices by default, which means it supports slices out of the box and you get to reuse your prepared statements.
  • It's throughly tested (including integration tests to check for invalid queries being generated).
  • If you already use pgx with Squirrel and the native PostgreSQL protocol, switching is very straightforward with just a few breaking changes (example: Alias is a type rather than a function).

Main drawback

  • It's still a query builder. You can go a long way writing pure SQL queries. Consider doing so.

FAQ

Why forking a query builder then? Whatever it takes to make people ditch using an ORM, I guess.

See also

Documentation

Overview

package pgq provides a fluent SQL generator.

See https://github.com/Masterminds/pgq for examples.

Index

Examples

Constants

This section is empty.

Variables

This section is empty.

Functions

func ConcatSQL 

func ConcatSQL(ce ...any) (sql string, args []any, err error)

ConcatSQL builds a SQL of an expression by concatenating strings and other expressions.

Ex: 

name_expr := Expr("CONCAT(?, ' ', ?)", firstName, lastName)
ConcatSQL("COALESCE(full_name,", name_expr, ")")

func Debug 

func Debug(s SQLizer) string

Debug calls SQL on s and shows the approximate SQL to be executed

If SQL returns an error, the result of this method will look like: "[SQL error: %s]" or "[DebugSQLizer error: %s]"

IMPORTANT: As its name suggests, this function should only be used for debugging. While the string result *might* be valid SQL, this function does not try very hard to ensure it. Additionally, executing the output of this function with any untrusted user input is certainly insecure.

func Placeholders 

func Placeholders(count int) string

Placeholders returns a string with count ? placeholders joined with commas.

Types

type Alias 

type Alias struct {
	Expr SQLizer
	As   string
}

Alias allows to define alias for column in SelectBuilder. Useful when column is defined as complex expression like IF or CASE Ex: 

.Column(Alias{Expr: caseStmt, Alias: "case_column"})

func (Alias) SQL 

func (a Alias) SQL() (sql string, args []any, err error)

AliasExprSQL returns a SQL query based on the alias.

type And 

type And []SQLizer

And conjunction SQLizers

func (And) SQL 

func (a And) SQL() (string, []any, error)

type CaseBuilder 

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

CaseBuilder builds SQL CASE construct which could be used as parts of queries.

func Case 

func Case(what ...any) CaseBuilder

Case returns a new CaseBuilder "what" represents case value

func (CaseBuilder) Else 

func (b CaseBuilder) Else(expr any) CaseBuilder

What sets optional "ELSE ..." part for CASE construct

func (CaseBuilder) MustSQL 

func (b CaseBuilder) MustSQL() (string, []any)

MustSQL builds the query into a SQL string and bound args. It panics if there are any errors.

func (CaseBuilder) SQL 

func (b CaseBuilder) SQL() (sqlStr string, args []any, err error)

SQL builds the query into a SQL string and bound args.

func (CaseBuilder) When 

func (b CaseBuilder) When(when any, then any) CaseBuilder

When adds "WHEN ... THEN ..." part to CASE construct

type DeleteBuilder 

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

DeleteBuilder builds SQL DELETE statements.

func Delete 

func Delete(from string) DeleteBuilder

Delete returns a new DeleteBuilder with the given table name.

See DeleteBuilder.Table.

func (DeleteBuilder) From 

func (b DeleteBuilder) From(from string) DeleteBuilder

From sets the table to be deleted from.

func (DeleteBuilder) MustSQL 

func (b DeleteBuilder) MustSQL() (string, []any)

MustSQL builds the query into a SQL string and bound args. It panics if there are any errors.

func (DeleteBuilder) OrderBy 

func (b DeleteBuilder) OrderBy(orderBys ...string) DeleteBuilder

OrderBy adds ORDER BY expressions to the query.

func (DeleteBuilder) Prefix 

func (b DeleteBuilder) Prefix(sql string, args ...any) DeleteBuilder

Prefix adds an expression to the beginning of the query

func (DeleteBuilder) PrefixExpr 

func (b DeleteBuilder) PrefixExpr(expr SQLizer) DeleteBuilder

PrefixExpr adds an expression to the very beginning of the query

func (DeleteBuilder) Returning 

func (b DeleteBuilder) Returning(columns ...string) DeleteBuilder

Returning adds RETURNING expressions to the query.

func (DeleteBuilder) ReturningSelect 

func (b DeleteBuilder) ReturningSelect(from SelectBuilder, alias string) DeleteBuilder

ReturningSelect adds a RETURNING expressions to the query similar to Using, but takes a Select statement.

func (DeleteBuilder) SQL 

func (b DeleteBuilder) SQL() (sqlStr string, args []any, err error)

SQL builds the query into a SQL string and bound args.

func (DeleteBuilder) Suffix 

func (b DeleteBuilder) Suffix(sql string, args ...any) DeleteBuilder

Suffix adds an expression to the end of the query

func (DeleteBuilder) SuffixExpr 

func (b DeleteBuilder) SuffixExpr(expr SQLizer) DeleteBuilder

SuffixExpr adds an expression to the end of the query

func (DeleteBuilder) Using 

func (b DeleteBuilder) Using(items ...string) DeleteBuilder

Using adds USING expressions to the query.

A table expression allowing columns from other tables to appear in the WHERE condition. This uses the same syntax as the FROM clause of a SELECT statement. Do not repeat the target table unless you intend a self-join (in which case, you must use an alias).

func (DeleteBuilder) UsingSelect 

func (b DeleteBuilder) UsingSelect(from SelectBuilder, alias string) DeleteBuilder

UsingSelect adds USING expressions to the query similar to Using, but takes a Select statement.

func (DeleteBuilder) Where 

func (b DeleteBuilder) Where(pred any, args ...any) DeleteBuilder

Where adds WHERE expressions to the query.

See SelectBuilder.Where for more information.

type Eq 

type Eq map[string]any

Eq is syntactic sugar for use with Where/Having/Set methods.

Example 
Output:

func (Eq) SQL 

func (eq Eq) SQL() (sql string, args []any, err error)

type Gt 

type Gt Lt

Gt is syntactic sugar for use with Where/Having/Set methods. Ex: 

.Where(Gt{"id": 1}) == "id > 1"

func (Gt) SQL 

func (gt Gt) SQL() (sql string, args []any, err error)

type GtOrEq 

type GtOrEq Lt

GtOrEq is syntactic sugar for use with Where/Having/Set methods. Ex: 

.Where(GtOrEq{"id": 1}) == "id >= 1"

func (GtOrEq) SQL 

func (gtOrEq GtOrEq) SQL() (sql string, args []any, err error)

type ILike 

type ILike Like

ILike is syntactic sugar for use with ILIKE conditions. Ex: 

.Where(ILike{"name": "sq%"})

func (ILike) SQL 

func (ilk ILike) SQL() (sql string, args []any, err error)

type InsertBuilder 

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

InsertBuilder builds SQL INSERT statements.

func Insert 

func Insert(into string) InsertBuilder

Insert returns a new InsertBuilder with the given table name.

See InsertBuilder.Into.

func Replace 

func Replace(into string) InsertBuilder

Replace returns a new InsertBuilder with the statement keyword set to "REPLACE" and with the given table name.

See InsertBuilder.Into.

func (InsertBuilder) Columns 

func (b InsertBuilder) Columns(columns ...string) InsertBuilder

Columns adds insert columns to the query.

func (InsertBuilder) Into 

func (b InsertBuilder) Into(from string) InsertBuilder

Into sets the INTO clause of the query.

func (InsertBuilder) MustSQL 

func (b InsertBuilder) MustSQL() (string, []any)

MustSQL builds the query into a SQL string and bound args. It panics if there are any errors.

func (InsertBuilder) Prefix 

func (b InsertBuilder) Prefix(sql string, args ...any) InsertBuilder

Prefix adds an expression to the beginning of the query

func (InsertBuilder) PrefixExpr 

func (b InsertBuilder) PrefixExpr(expr SQLizer) InsertBuilder

PrefixExpr adds an expression to the very beginning of the query

func (InsertBuilder) Returning 

func (b InsertBuilder) Returning(columns ...string) InsertBuilder

Returning adds RETURNING expressions to the query.

func (InsertBuilder) ReturningSelect 

func (b InsertBuilder) ReturningSelect(from SelectBuilder, alias string) InsertBuilder

ReturningSelect adds a RETURNING expressions to the query similar to Using, but takes a Select statement.

func (InsertBuilder) SQL 

func (b InsertBuilder) SQL() (sqlStr string, args []any, err error)

SQL builds the query into a SQL string and bound args.

func (InsertBuilder) Select 

func (b InsertBuilder) Select(sb SelectBuilder) InsertBuilder

Select set Select clause for insert query If Values and Select are used, then Select has higher priority

func (InsertBuilder) SetMap 

func (b InsertBuilder) SetMap(clauses map[string]any) InsertBuilder

SetMap set columns and values for insert builder from a map of column name and value note that it will reset all previous columns and values was set if any

func (InsertBuilder) Suffix 

func (b InsertBuilder) Suffix(sql string, args ...any) InsertBuilder

Suffix adds an expression to the end of the query

func (InsertBuilder) SuffixExpr 

func (b InsertBuilder) SuffixExpr(expr SQLizer) InsertBuilder

SuffixExpr adds an expression to the end of the query

func (InsertBuilder) Values 

func (b InsertBuilder) Values(values ...any) InsertBuilder

Values adds a single row's values to the query.

func (InsertBuilder) Verb added in v0.0.2 

func (b InsertBuilder) Verb(v string) InsertBuilder

Verb to be used for the operation (default: INSERT).

type Like 

type Like map[string]any

Like is syntactic sugar for use with LIKE conditions. Ex: 

.Where(Like{"name": "%irrel"})

func (Like) SQL 

func (lk Like) SQL() (sql string, args []any, err error)

type Lt 

type Lt map[string]any

Lt is syntactic sugar for use with Where/Having/Set methods. Ex: 

.Where(Lt{"id": 1})

func (Lt) SQL 

func (lt Lt) SQL() (sql string, args []any, err error)

type LtOrEq 

type LtOrEq Lt

LtOrEq is syntactic sugar for use with Where/Having/Set methods. Ex: 

.Where(LtOrEq{"id": 1}) == "id <= 1"

func (LtOrEq) SQL 

func (ltOrEq LtOrEq) SQL() (sql string, args []any, err error)

type NotEq 

type NotEq Eq

NotEq is syntactic sugar for use with Where/Having/Set methods. Ex: 

.Where(NotEq{"id": 1}) == "id <> 1"

func (NotEq) SQL 

func (neq NotEq) SQL() (sql string, args []any, err error)

type NotILike 

type NotILike Like

NotILike is syntactic sugar for use with ILIKE conditions. Ex: 

.Where(NotILike{"name": "sq%"})

func (NotILike) SQL 

func (nilk NotILike) SQL() (sql string, args []any, err error)

type NotLike 

type NotLike Like

NotLike is syntactic sugar for use with LIKE conditions. Ex: 

.Where(NotLike{"name": "%irrel"})

func (NotLike) SQL 

func (nlk NotLike) SQL() (sql string, args []any, err error)

type Or 

type Or []SQLizer

Or conjunction SQLizers

func (Or) SQL 

func (o Or) SQL() (string, []any, error)

type SQLizer 

type SQLizer interface {
	SQL() (string, []any, error)
}

SQLizer is the interface that wraps the SQL method.

SQL() returns a SQL representation of the SQLizer, along with a slice of arguments. If the query cannot be created, it returns an error.

func Expr 

func Expr(sql string, args ...any) SQLizer

Expr builds an expression from a SQL fragment and arguments.

Ex: 

Expr("FROM_UNIXTIME(?)", t)

type SelectBuilder 

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

SelectBuilder builds SQL SELECT statements.

func Select 

func Select(columns ...string) SelectBuilder

Select returns a new SelectBuilder, optionally setting some result columns.

See SelectBuilder.Columns.

Example 
Output:

func (SelectBuilder) Column 

func (b SelectBuilder) Column(column any, args ...any) SelectBuilder

Column adds a result column to the query. Unlike Columns, Column accepts args which will be bound to placeholders in the columns string, for example: 

Column("IF(col IN ("+pgq.Placeholders(3)+"), 1, 0) as col", 1, 2, 3)

func (SelectBuilder) Columns 

func (b SelectBuilder) Columns(columns ...string) SelectBuilder

Columns adds result columns to the query.

Example 
Output:

SELECT id, created, first_name FROM users
Example (Order) 
Output:

SELECT id, created, first_name FROM users

func (SelectBuilder) CrossJoin 

func (b SelectBuilder) CrossJoin(join string, rest ...any) SelectBuilder

CrossJoin adds a CROSS JOIN clause to the query.

func (SelectBuilder) Distinct 

func (b SelectBuilder) Distinct() SelectBuilder

Distinct adds a DISTINCT clause to the query.

func (SelectBuilder) From 

func (b SelectBuilder) From(from string) SelectBuilder

From sets the FROM clause of the query.

Example 
Output:

func (SelectBuilder) FromSelect 

func (b SelectBuilder) FromSelect(from SelectBuilder, alias string) SelectBuilder

FromSelect sets a subquery into the FROM clause of the query.

Example 
Output:

SELECT company.id, company.name, users_by_company.n_users FROM (SELECT company, count(*) as n_users FROM users GROUP BY company) AS users_by_company JOIN company on company.id = users_by_company.company

func (SelectBuilder) GroupBy 

func (b SelectBuilder) GroupBy(groupBys ...string) SelectBuilder

GroupBy adds GROUP BY expressions to the query.

func (SelectBuilder) Having 

func (b SelectBuilder) Having(pred any, rest ...any) SelectBuilder

Having adds an expression to the HAVING clause of the query.

See Where.

func (SelectBuilder) InnerJoin 

func (b SelectBuilder) InnerJoin(join string, rest ...any) SelectBuilder

InnerJoin adds a INNER JOIN clause to the query.

func (SelectBuilder) Join 

func (b SelectBuilder) Join(join string, rest ...any) SelectBuilder

Join adds a JOIN clause to the query.

func (SelectBuilder) JoinClause 

func (b SelectBuilder) JoinClause(pred any, args ...any) SelectBuilder

JoinClause adds a join clause to the query.

func (SelectBuilder) LeftJoin 

func (b SelectBuilder) LeftJoin(join string, rest ...any) SelectBuilder

LeftJoin adds a LEFT JOIN clause to the query.

func (SelectBuilder) Limit 

func (b SelectBuilder) Limit(limit uint64) SelectBuilder

Limit sets a LIMIT clause on the query.

func (SelectBuilder) MustSQL 

func (b SelectBuilder) MustSQL() (string, []any)

MustSQL builds the query into a SQL string and bound args. It panics if there are any errors.

func (SelectBuilder) Offset 

func (b SelectBuilder) Offset(offset uint64) SelectBuilder

Offset sets a OFFSET clause on the query.

func (SelectBuilder) Options 

func (b SelectBuilder) Options(options ...string) SelectBuilder

Options adds select option to the query

func (SelectBuilder) OrderBy 

func (b SelectBuilder) OrderBy(orderBys ...string) SelectBuilder

OrderBy adds ORDER BY expressions to the query.

func (SelectBuilder) OrderByClause 

func (b SelectBuilder) OrderByClause(pred any, args ...any) SelectBuilder

OrderByClause adds ORDER BY clause to the query.

func (SelectBuilder) Prefix 

func (b SelectBuilder) Prefix(sql string, args ...any) SelectBuilder

Prefix adds an expression to the beginning of the query

func (SelectBuilder) PrefixExpr 

func (b SelectBuilder) PrefixExpr(expr SQLizer) SelectBuilder

PrefixExpr adds an expression to the very beginning of the query

func (SelectBuilder) RemoveColumns 

func (b SelectBuilder) RemoveColumns() SelectBuilder

RemoveColumns remove all columns from query. Must add a new column with Column or Columns methods, otherwise return a error.

func (SelectBuilder) RemoveLimit 

func (b SelectBuilder) RemoveLimit() SelectBuilder

Limit ALL allows to access all records with limit

func (SelectBuilder) RemoveOffset 

func (b SelectBuilder) RemoveOffset() SelectBuilder

RemoveOffset removes OFFSET clause.

func (SelectBuilder) RightJoin 

func (b SelectBuilder) RightJoin(join string, rest ...any) SelectBuilder

RightJoin adds a RIGHT JOIN clause to the query.

func (SelectBuilder) SQL 

func (b SelectBuilder) SQL() (sqlStr string, args []any, err error)

SQL builds the query into a SQL string and bound args.

func (SelectBuilder) Suffix 

func (b SelectBuilder) Suffix(sql string, args ...any) SelectBuilder

Suffix adds an expression to the end of the query

func (SelectBuilder) SuffixExpr 

func (b SelectBuilder) SuffixExpr(expr SQLizer) SelectBuilder

SuffixExpr adds an expression to the end of the query

func (SelectBuilder) Where 

func (b SelectBuilder) Where(pred any, args ...any) SelectBuilder

Where adds an expression to the WHERE clause of the query.

Expressions are ANDed together in the generated SQL.

Where accepts several types for its pred argument:

nil OR "" - ignored.

string - SQL expression. If the expression has SQL placeholders then a set of arguments must be passed as well, one for each placeholder.

map[string]any OR Eq - map of SQL expressions to values. Each key is transformed into an expression like "<key> = ?", with the corresponding value bound to the placeholder. If the value is nil, the expression will be "<key> IS NULL". If the value is an array or slice, the expression will be "<key> = ANY (?)". These expressions are ANDed together.

Where will panic if pred isn't any of the above types.

Example 
Output:
Example (Helpers) 
Output:
Example (Multiple) 
Output:

type StatementBuilder 

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

StatementBuilder for WHERE parts.

func Statement 

func Statement() StatementBuilder

Statement returns a new StatementBuilder, which can be used to create SQL WHERE parts.

func (StatementBuilder) Delete 

func (b StatementBuilder) Delete(from string) DeleteBuilder

Delete returns a DeleteBuilder for this StatementBuilder.

func (StatementBuilder) Select 

func (b StatementBuilder) Select(columns ...string) SelectBuilder

Select returns a SelectBuilder for this StatementBuilder.

func (StatementBuilder) Update 

func (b StatementBuilder) Update(table string) UpdateBuilder

Update returns a UpdateBuilder for this StatementBuilder.

func (StatementBuilder) Where 

func (b StatementBuilder) Where(pred any, args ...any) StatementBuilder

Where adds WHERE expressions to the query.

See SelectBuilder.Where for more information.

type UpdateBuilder 

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

UpdateBuilder builds SQL UPDATE statements.

func Update 

func Update(table string) UpdateBuilder

Update returns a new UpdateBuilder with the given table name.

See UpdateBuilder.Table.

func (UpdateBuilder) From 

func (b UpdateBuilder) From(items ...string) UpdateBuilder

From adds FROM expressions to the query.

A table expression allowing columns from other tables to appear in the WHERE condition and update expressions. This uses the same syntax as the FROM clause of a SELECT statement. Do not repeat the target table unless you intend a self-join (in which case, you must use an alias).

func (UpdateBuilder) FromSelect 

func (b UpdateBuilder) FromSelect(from SelectBuilder, alias string) UpdateBuilder

FromSelect adds FROM expressions to the query similar to From, but takes a Select statement.

func (UpdateBuilder) MustSQL 

func (b UpdateBuilder) MustSQL() (string, []any)

MustSQL builds the query into a SQL string and bound args. It panics if there are any errors.

func (UpdateBuilder) OrderBy 

func (b UpdateBuilder) OrderBy(orderBys ...string) UpdateBuilder

OrderBy adds ORDER BY expressions to the query.

func (UpdateBuilder) Prefix 

func (b UpdateBuilder) Prefix(sql string, args ...any) UpdateBuilder

Prefix adds an expression to the beginning of the query

func (UpdateBuilder) PrefixExpr 

func (b UpdateBuilder) PrefixExpr(expr SQLizer) UpdateBuilder

PrefixExpr adds an expression to the very beginning of the query

func (UpdateBuilder) Returning 

func (b UpdateBuilder) Returning(columns ...string) UpdateBuilder

Returning adds RETURNING expressions to the query.

func (UpdateBuilder) ReturningSelect 

func (b UpdateBuilder) ReturningSelect(from SelectBuilder, alias string) UpdateBuilder

ReturningSelect adds a RETURNING expressions to the query similar to Using, but takes a Select statement.

func (UpdateBuilder) SQL 

func (b UpdateBuilder) SQL() (sqlStr string, args []any, err error)

func (UpdateBuilder) Set 

func (b UpdateBuilder) Set(column string, value any) UpdateBuilder

Set adds SET clauses to the query.

func (UpdateBuilder) SetMap 

func (b UpdateBuilder) SetMap(clauses map[string]any) UpdateBuilder

SetMap is a convenience method which calls .Set for each key/value pair in clauses.

func (UpdateBuilder) Suffix 

func (b UpdateBuilder) Suffix(sql string, args ...any) UpdateBuilder

Suffix adds an expression to the end of the query

func (UpdateBuilder) SuffixExpr 

func (b UpdateBuilder) SuffixExpr(expr SQLizer) UpdateBuilder

SuffixExpr adds an expression to the end of the query

func (UpdateBuilder) Table 

func (b UpdateBuilder) Table(table string) UpdateBuilder

Table sets the table to be updated.

func (UpdateBuilder) Where 

func (b UpdateBuilder) Where(pred any, args ...any) UpdateBuilder

Where adds WHERE expressions to the query.

See SelectBuilder.Where for more information.

type Valuer 

type Valuer interface {
	// Value returns a driver Value.
	// Value must not panic.
	Value() (any, error)
}

Valuer is the interface providing the Value method.

Types implementing Valuer interface are able to convert themselves to a driver Value.

Similar to database/sql/driver.Value, but returns any instead of driver.Value.

Source Files

View all Source files

Directories

Path Synopsis
integration module

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.