gosql

README

godoc pkg.go.dev

WORK IN PROGRESS

gosql

The gosql package defines types that can be used together with the cmd/gosql tool to generate static SQL queries from Go structs.

Documentation

Index

• Variables
• func InValueList(num, pos int) string
• type AfterScanner
• type All
• type Column
• type Conn
• type Constraint
• type CrossJoin
• type Default
• type ErrorHandler
• type ErrorInfo
• type ErrorInfoHandler
• type Filter
• type FilterConstructor
• type Force
• type FullJoin
• type Ignore
• type Index
• type InnerJoin
• type LeftJoin
• type Limit
• type Offset
• type Optional
• type OrderBy
• type Override
• type Query
• type Relation
• type Return
• type RightJoin
• type TextSearch
• type Update

Variables

var OrdinalParameters = func() (a [65535]string) {
	for i := 0; i < len(a); i++ {
		a[i] = "$" + strconv.Itoa(i+1)
	}
	return a
}()

OrdinalParameters is a pre-generated array of PostgreSQL specific ordinal parameters ($N). The array size is 65535 which is the max number of parameters that postgres allows.

Functions

func InValueList

func InValueList(num, pos int) string

InValueList generates a string that contains a comma separated list of ordinal parameters starting from the given pos and ending at pos+num, i.e. "$pos, ..., $pos+num". The returned string is intended to be used in an "IN ( <string> )" clause.

Types

type AfterScanner

type AfterScanner interface {
	// The method to invoke after scanning an *sql.Row | *sql.Rows.
	AfterScan()
}

An implementation of the AfterScanner interface can be used to do some trivial post-processing of fields after a row was scanned successfully.

The cmd/gosql tool checks the target "relation types" if they have an AfterScan() method in their method sets, if a type does have the AfterScan() method the tool will generate code that calls AfterScan() right after code that invokes the (*sql.Row|*sql.Rows).Scan method.

type All

type All directive

The All directive is inteded to be used with the UpdateXxx and DeleteXxx query types to explicitly indicate that, if a "where struct" is missing, the query should be executed against all the rows of the relation.

This acts as a safeguard against unintentionally omitting the "where struct" and then generating a query that would delete/update every single rows in a table.

The All directive accepts no tags.

type Column

type Column directive

The Column directive has two use cases:

(1) It can be used within a "where struct" to produce a column specific predicate for a WHERE search condition. The type of the predicate that can be produced depends on the `sql` tag value supplied to the directive. The expected format for the directive's tag value is:

`sql:"{ column_ident [ predicate-type [ quantifier ] { column_ident | literal } ] }"`

(2) It can be used in an "on_conflict struct" to specify the resulting ON CONFLICT clause's conflict_target as a list of column names that have an index associated with them. The list of columns should be provided in the directive's `sql` tag. The expected format for the directive's tag value is:

`sql:"{ column_ident [ , column_ident ] }"`

type Conn

type Conn interface {
	// Exec executes the given query and returns an sql.Result object
	// that holds information about the result of that query.
	Exec(query string, args ...interface{}) (sql.Result, error)
	// ExecContext executes the given query and returns an sql.Result object
	// that holds information about the result of that query.
	ExecContext(ctx context.Context, query string, args ...interface{}) (sql.Result, error)
	// Query executes the given query and returns an *sql.Rows object
	// that represents the result of that query.
	Query(query string, args ...interface{}) (*sql.Rows, error)
	// QueryContext executes the given query and returns an *sql.Rows object
	// that represents the result of that query.
	QueryContext(ctx context.Context, query string, args ...interface{}) (*sql.Rows, error)
	// QueryRow executes the given query and returns an *sql.Row object
	// that represents the result of that query.
	QueryRow(query string, args ...interface{}) *sql.Row
	// QueryRowContext executes the given query and returns an *sql.Row object
	// that represents the result of that query.
	QueryRowContext(ctx context.Context, query string, args ...interface{}) *sql.Row
}

Conn is implemented by *sql.DB and *sql.Tx, and it is the main type used by a Query's implementation to execute its SQL.

type Constraint

type Constraint directive

The Constraint directive can be used in an "on_conflict struct" to specify the resulting ON CONFLICT clause's conflict_target using the identifier of a table constraint. The constraint's identifier should be provided in the directive's "sql" tag. The expected format for the directive's tag value is:

`sql:"{ constraint_ident }"`

type CrossJoin

type CrossJoin directive

The CrossJoin directive can be used to produce the CROSS JOIN clause. This directive can only be used within a "join struct", a "using struct", or a "from struct". The `sql` struct tag supplied with the directive will be used to produce the join_condition.

The expected format for the directive's tag value is:

`sql:"{ relation_ident [ , column_comparison_expr [ { ',' | ';' } column_comparison_expr ] ] }"`

type Default

type Default directive

The Default directive can be used to produce the DEFAULT marker instead of an ordinal parameter for the specified column. The Default directive can be used in InsertXxx and UpdateXxx query types. The expected format for the directive's tag value is:

`sql:"{ '*' | column_ident [ , column_ident ] }"`

type ErrorHandler

type ErrorHandler interface {
	// HandleError can be used to return a custom error from the Exec method
	// of a Query type. If, during the execution of a Query, an error occurs,
	// it will be passed in to the method as the inErr parameter and, in its
	// stead, the outErr result value will be returned from Exec.
	//
	// NOTE that HandleError could be invoked regardless of whether an error
	// occured or not which means that in some cases the passed in error value
	// will be nil, therefore the implementation needs to mindful of that.
	HandleError(inErr error) (outErr error)
}

An implementation of the ErrorHandler interface can be used to transform database specific query errors into custom, application specific errors.

The cmd/gosql tool checks the target "query types" if they have a field whose type implements the ErrorHandler interface and, if a query type does have such a field, the tool will generate code that invokes the HandleError method on that field passing it the error value as its argument.

type ErrorInfo

type ErrorInfo struct {
	// The error object.
	Error error
	// The SQL query string that was executed.
	QueryString string
	// The kind of the query. One of: "Insert", "Select", "Update", or "Delete".
	QueryKind string
	// The name of the Query type.
	QueryName string
	// The actual Query value.
	QueryValue interface{}
}

ErrorInfo holds information on an error that occurred during the execution of a Query.

type ErrorInfoHandler

type ErrorInfoHandler interface {
	// HandleErrorInfo can be used to return a custom error from the Exec
	// method of a Query type. If, during the execution of a Query, an error
	// occurs, it will be passed in to the method, together with some additional
	// information, as the *ErrorInfo parameter and, in its stead, the outErr
	// result value will be returned from Exec.
	HandleErrorInfo(info *ErrorInfo) (outErr error)
}

An implementation of the ErrorInfoHandler interface can be used to transform database specific query errors into custom, application specific errors.

The cmd/gosql tool checks the target "query types" if they have a field whose type implements the ErrorInfoHandler interface and, if a query type does have such a field, the tool will generate code that invokes the HandleErrorInfo method on that field passing it an instance of *ErrorInfo as its argument.

type Filter

type Filter interface {
	// ToSQL should return a string containing SQL clauses for filtering
	// the SQL query that was generated for the QueryXxx type to which the
	// specific instance of Filter belongs.
	//
	// The pos argument represents the current position of the ordinal
	// parameters in the query to which the Filter is applied, therefore,
	// the pos value should be used as the starting point to produce the
	// correct positional parameters ($N) in the returned string.
	//
	// The returned params slice should contain the arguments that go
	// along with the SQL clauses in the returned filterString.
	ToSQL(pos int) (filterString string, params []interface{})
}

Filter is an interface that can be used by QueryXxx types to dynamically produce SQL for filtering the results of a generated, static query.

type FilterConstructor

type FilterConstructor interface {
	// Init should retain the given arguments. The colmap argument is a map
	// of column names as generated by the tool. The tscol argument, if present,
	// will match the ts_vector column specified by the gosql.TextSearch directive.
	//
	// NOTE: The given colmap SHOULD NOT be modified, it is intended to be read-only.
	//
	// The tool will generate an Init() method on the target FilterXxx type,
	// with no in or out parameters, that will invoke this Init and explicitly
	// pass in those arguments.
	Init(colmap map[string]string, tscol string)
	// Col should, based on the given parameters, produce a column-specific
	// SQL comparison predicate.
	//
	// The tool will generate a list of predicate methods that will parallel
	// the fields of the "rel type" of the target FilterXxx type. The predicate
	// methods will delegate to Col, passing it the name of the column that's
	// associated with the corresponding field.
	//
	//	func (f *FilterXxx) <FieldName>(op string, value interface{}) *FilterXxx {
	//		f.FilterConstructor.Col("<column_name>", op, value)
	//		return f
	//	}
	Col(column string, op string, value interface{})
	// And should produce the "AND" boolean SQL operator. The nest argument
	// is optional and when provided the implementation should surround the
	// code produced by its invocation in parentheses.
	//
	// The tool will generate a wrapper around this method that will be
	// specific for the FilterXxx type for which it is declared.
	//
	//	func (f *FilterXxx) And(nest func(*FilterXxx)) *FilterXxx {
	//		// ...
	//	}
	And(nest func())
	// Or should produce the "OR" boolean SQL operator. The nest argument
	// is optional and when provided the implementation should surround the
	// code produced by its invocation in parentheses.
	//
	// The tool will generate a wrapper around this method that will be
	// specific for the FilterXxx type for which it is declared.
	//
	//	func (f *FilterXxx) Or(nest func(*FilterXxx)) *FilterXxx {
	//		// ...
	//	}
	Or(nest func())
}

A type that implements the FilterConstructor interface should be used as a member field of a FilterXxx type.

The cmd/gosql tool checks the target FilterXxx types if they have a field whose type implements the FilterConstructor interface and, if a FilterXxx type does have such a field, the tool will generate methods for the target FilterXxx type that will delegate to the FilterConstructor's methods.

type Force

type Force directive

The Force directive can be used to override column specific settings like "ro" (ready only) and "wo" (write only).

For example a read-only column (one with the "ro" setting) will by default be excluded from INSERT/UPDATE queries, however, to override this default behaviour the user can add the Force directive to an InsertXxx or UpdateXxx query type and specify the read-only column in the directive's `sql` tag, then the tool will include the column in the generated SQL query. The expected format for the directive's tag value is:

`sql:"{ '*' | column_ident [ , column_ident ] }"`

type FullJoin

type FullJoin directive

The FullJoin directive can be used to produce the FULL JOIN clause. This directive can only be used within a "join struct", a "using struct", or a "from struct". The `sql` struct tag supplied with the directive will be used to produce the join_condition.

The expected format for the directive's tag value is:

`sql:"{ relation_ident [ , column_comparison_expr [ { ',' | ';' } column_comparison_expr ] ] }"`

type Ignore

type Ignore directive

The Ignore directive can be used in an "on_conflict struct" to produce the DO NOTHING action of the resulting ON CONFLICT clause. The Ignore directive does not accept any tags.

type Index

type Index directive

The Index directive can be used in an "on_conflict struct" to specify the resulting ON CONFLICT clause's conflict_target using the identifier of a unique index. The index name should be provided in the directive's "sql" tag. The expected format for the directive's tag value is:

`sql:"{ index_ident }"`

The tool will use the index identifier to retrive the index expression which then will be used verbatim to produce the conflict target.

type InnerJoin

type InnerJoin directive

The InnerJoin directive can be used to produce the INNER JOIN clause. This directive can only be used within a "join struct", a "using struct", or a "from struct". The `sql` struct tag supplied with the directive will be used to produce the join_condition.

The expected format for the directive's tag value is:

`sql:"{ relation_ident [ , column_comparison_expr [ { ',' | ';' } column_comparison_expr ] ] }"`

type LeftJoin

type LeftJoin directive

The LeftJoin directive can be used to produce the LEFT JOIN clause. This directive can only be used within a "join struct", a "using struct", or a "from struct". The `sql` struct tag supplied with the directive will be used to produce the join_condition.

The expected format for the directive's tag value is:

`sql:"{ relation_ident [ , column_comparison_expr [ { ',' | ';' } column_comparison_expr ] ] }"`

type Limit

type Limit directive

The Limit directive can be used inside a SelectXxx query type to produce a LIMIT clause for the SELECT query. The limit value must be specified in the directive field's `sql` tag. The expected format for the directive's tag value is:

`sql:"{ uint64 }"`

type Offset

type Offset directive

The Offset directive can be used inside a SelectXxx query type to produce an OFFSET clause for the SELECT query. The offset value must be specified in the directive field's `sql` tag. The expected format for the directive's tag value is:

`sql:"{ uint64 }"`

type Optional

type Optional directive

The Optional directive can be used to tell the tool to queries with types whose fields do not have a corresponding column in the target relation.

TODO add more details and / or example

type OrderBy

type OrderBy directive

The OrderBy directive can be used inside a SelectXxx query type to produce an ORDER BY clause for the SELECT query. The list of columns by which to order should be specified in the directive's tag. The expected format for the directive's tag value is:

`sql:"{ order_by_item [ , order_by_item ] }"`

and the expected format of the order_by_item is:

[ - ]column_ident[ :nullsfirst | :nullslast ]

The optional preceding "-" produces the DESC sort direction option, if no "-" is provided ASC is produced instead. The optional ":nullsfirst" and ":nullslast" produce the NULLS FIRST and NULLS LAST options respectively.

type Override

type Override directive

The Override directive can be used in an InsertXxx query type to produce the OVERRIDING { SYSTEM | USER } VALUE clause of an INSERT query. The expected format for the directive's tag value is:

`sql:"{ 'user' | 'system' }"`

type Query

type Query interface {
	// Exec executes the Query's implementation using the given Conn.
	Exec(c Conn) error
}

A Query represents an SQL query.

The implementation of the Query interface would, ideally, be generated by the cmd/gosql tool however, for SQL not supported by the tool one can implement the Query interface manually.

type Relation

type Relation directive

The Relation directive has two use cases:

(1) It can be used in a DeleteXxx query type as the "mount" for the `rel` tag. This can be useful for DeleteXxx types that have no Return directive, since such query types produce a DELETE query that takes no input other than the optional WHERE clause parameters, nor does it generate any output, and therefore it then becomes unnecessary to provide a proper Go struct representation of the target relation. The expected format for the directive's tag value is:

`rel:"{ relation_ident }"`

(2) It can be used as the first directive in a "using struct" or "from struct" to specify the primary target relation for the clauses produced from those structs. The expected format for the directive's tag value is:

`sql:"{ relation_ident }"`

type Return

type Return directive

The Return directive can be used to produce a postgres RETURNING clause. The list of columns to return should be specified in the `sql` struct tag of the directive. The Return directive can be used in InsertXxx, UpdateXxx, and DeleteXxx query types. The expected format for the directive's tag value is:

`sql:"{ '*' | column_ident [ , column_ident ] }"`

type RightJoin

type RightJoin directive

The RightJoin directive can be used to produce the RIGHT JOIN clause. This directive can only be used within a "join struct", a "using struct", or a "from struct". The `sql` struct tag supplied with the directive will be used to produce the join_condition.

The expected format for the directive's tag value is:

`sql:"{ relation_ident [ , column_comparison_expr [ { ',' | ';' } column_comparison_expr ] ] }"`

type TextSearch

type TextSearch directive

The TextSearch directive can be used in a FilterXxx filter type to specify the ts_vector column that can be used for full-text search. The expected format for the directive's tag value is:

`sql:"{ column_ident }"`

type Update

type Update directive

The Update directive can be used in an "on_conflict struct" to produce the DO UPDATE SET action of the resulting ON CONFLICT clause. The columns to be updated by the produced action should be listed in the directive's "sql" tag. The expected format for the directive's tag value is:

`sql:"{ '*' | column_ident [ , column_ident ] }"`