README
¶
pika
ORM-like SQL builder inspired by kayak/pypika
Notice of Deprecation
This repository contains the original code for pika, which was developed by CIQ as a proof of concept (POC). It is intended solely as an illustrative example and is provided as-is for reference purposes only.
THE SOFTWARE IS PROVIDED “AS IS”, WITHOUT WARRANTY OF ANY KIND, EXPRESS OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY, FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL THE AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING FROM, OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN THE SOFTWARE.
By using this software, you acknowledge and agree to these terms.
Features
- Any feature supported by sqlx
- Support for AIP-160 filtering
- Utilities to help with AIP-132 for List calls
- Support for determining filters based on Protobuf messages
- Automatically selecting columns in struct
- Count, Get, All, Create, Update, Delete and more.
- Support for simple joins
Example
Simple connect and Get
package main
import (
"go.ciq.dev/pika"
"log"
)
type User struct {
PikaTableName string `pika:"users"`
ID int64 `db:"id" pika:"omitempty"`
Name string `db:"name"`
}
func main() {
psql, _ := pika.NewPostgreSQL("postgres://postgres:postgres@localhost:5432/test")
args := pika.NewArgs()
args.Set("id", 1)
qs := pika.Q[User](psql).Filter("id=:id").Args(args)
user, _ := qs.Get()
log.Println(user)
}
AIP-160
package main
import (
"go.ciq.dev/pika"
"log"
)
type Article struct {
PikaTableName string `pika:"users"`
ID int64 `db:"id" pika:"omitempty"`
CreatedAt time.Time `db:"created_at" pika:"omitempty"`
Title string `db:"title"`
Body string `db:"body"`
}
func main() {
psql, _ := pika.NewPostgreSQL("postgres://postgres:postgres@localhost:5432/test")
qs := pika.Q[Article](s.db)
// Get the following articles
// * The title MUST contain Hello and the body MUST contain World
// * If the above is not match, the article MUST be created before 2023-07-30
qs, err := qs.AIP160(`(title:"Hello" AND body:"World") OR (created_at < 2023-07-30T00:00:00Z)`, pika.AIPFilterOptions{})
if err != nil {
return nil, err
}
rows, err := qs.All()
if err != nil {
return nil, err
}
log.Println(rows)
}
Documentation
¶
Index ¶
Constants ¶
const (
// MaxPageSize represents the maximum allowed page size for pagination
MaxPageSize = 100
)
Variables ¶
var ( // PikaMetadataTableName is the field name used to specify custom table names in struct tags. PikaMetadataTableName = "PikaTableName" // PikaMetadataDefaultOrderBy is the field name used to specify default ordering in struct tags. PikaMetadataDefaultOrderBy = "PikaDefaultOrderBy" // PikaMetadataFields contains all available metadata field names for Pika configuration. PikaMetadataFields = []string{ PikaMetadataTableName, PikaMetadataDefaultOrderBy, } )
var ( ErrInvalidSuffix = errors.New("invalid suffix for identifier") ErrIdentifierNotAcceptable = errors.New("identifier is not acceptable") ErrNestedExpressionsNotSupported = errors.New("nested expressions are not supported") ErrCannotCombineMultipleValues = errors.New("cannot combine multiple values in subexpression") ErrUnknownAliasType = errors.New("unknown alias type") ErrTypeNotAccepted = errors.New("type is not accepted for identifier") ErrValueNotAccepted = errors.New("value is not accepted for identifier") ErrUnexpectedIdentifier = errors.New("unexpected identifier") ErrMissingOperator = errors.New("missing operator") ErrMissingIdentifier = errors.New("missing identifier") ErrIdentifierNotAllowed = errors.New("identifier is not allowed") )
Static errors for err113 compliance
var ( ErrPageTokenDecode = errors.New("failed to decode page token, make sure it is from a previous request") ErrPageSizeTooSmall = errors.New("page size cannot be less than 1") ErrPageSizeTooLarge = errors.New("page size cannot be greater than 100") ErrOffsetTooLarge = errors.New("offset value too large") ErrPageSizeValueTooLarge = errors.New("page size value too large") )
Static errors for err113 compliance
var ( ErrTooManyArguments = errors.New("too many arguments (count should be one pointer or none)") ErrPageSizeNegative = errors.New("page size cannot be negative") ErrCountNegative = errors.New("count cannot be negative") ErrMissingArgument = errors.New("missing argument") ErrInvalidOperator = errors.New("invalid operator") ErrInvalidKey = errors.New("invalid key") ErrBothModelsNil = errors.New("modelFirst and modelSecond are all nil, this is not allowed") )
Static errors for err113 compliance
var ( ErrInvalidFilter = errors.New("invalid filter") ErrFilterKeyContainsExclamation = errors.New("filter key contains exclamation mark") )
Static errors for err113 compliance
var ( // Operators // Equal OpEq = "=" // Not equal OpNeq = "!=" // Greater than OpGt = ">" // Greater than or equal OpGte = ">=" // Less than OpLt = "<" // Less than or equal OpLte = "<=" // Like OpLike = "LIKE" // Not like OpNotLike = "NOT LIKE" // ILike OpILike = "ILIKE" // Not ILike OpNotILike = "NOT ILIKE" // Is null OpIsNull = "IS NULL" // Is not null OpIsNotNull = "IS NOT NULL" // Empty OpEmpty = "" // Hints // Negate HintNegate = "__ne" // In HintIn = "__in" // Not in HintNotIn = "__nin" // Greater than HintGt = "__gt" // Greater than or equal HintGte = "__gte" // Less than HintLt = "__lt" // Less than or equal HintLte = "__lte" // Like HintLike = "__like" // Not like HintNotLike = "__nlike" // ILike HintILike = "__ilike" // Not ILike HintNotILike = "__nilike" // Is null HintIsNull = "__null" // Is not null HintIsNotNull = "__notnull" // Or HintOr = "__or" // And HintAnd = "__and" // Empty HintEmpty = "" )
var (
ErrIDNotFound = errors.New("id not found")
)
Static errors for err113 compliance
Functions ¶
func NewArgs ¶
func NewArgs() *orderedmap.OrderedMap[string, any]
NewArgs creates a new ordered map for storing named query arguments. This is a convenience function for creating argument maps that can be passed to QuerySet.Args().
Types ¶
type AIPFilter ¶
AIPFilter provides AIP-160 compliant filtering capabilities for database queries. It uses ANTLR-generated parsers to parse filter expressions and converts them into QuerySet operations that can be applied to database queries.
func NewAIPFilter ¶
NewAIPFilter creates a new AIPFilter instance for the given type T.
type AIPFilterIdentifier ¶
type AIPFilterIdentifier struct {
// Value aliases are used to map a value to a different value.
// Mostly useful for enums, where for example the values
// STAGE_STATUS_FAILED, FAILED, FaIlEd, etc. should all be
// mapped to the same value.
// This means that the alias value is case insensitive.
// Make sure to convert to lower case if key is a string.
ValueAliases map[any]any
// AcceptedTypes is a list of types that are accepted for this
// identifier.
// If empty, all types are accepted.
// The value should be in antlrValues
AcceptedTypes []int
// AcceptableValues is a list of values that are accepted for this
// identifier.
// If empty, all values are accepted.
AcceptedValues []any
// Column name is the name of the column in the database.
// If empty, the identifier is used as the column name.
ColumnName string
// IsRepeated is true if the identifier is a repeated field.
// This is used to determine how to apply the filter.
IsRepeated bool
}
AIPFilterIdentifier configures how a specific identifier (field) should be handled during AIP-160 filter parsing, including type validation, value aliases, and column mapping.
type AIPFilterOptions ¶
type AIPFilterOptions struct {
// Identifiers are additional configuration for specific identifiers.
Identifiers map[string]AIPFilterIdentifier
// AcceptableIdentifiers is a list of identifiers that are allowed
AcceptableIdentifiers []string
}
AIPFilterOptions provides configuration for AIP-160 filter parsing, including identifier validation, type checking, and field mapping.
func ProtoReflect ¶
func ProtoReflect(m proto.Message) AIPFilterOptions
ProtoReflect generates AIP filter options from a protobuf message using default settings. It uses reflection to analyze the message structure and create appropriate filter identifiers for each field based on their protobuf types.
func ProtoReflectWithOpts ¶
func ProtoReflectWithOpts(m proto.Message, opts ProtoReflectOptions) AIPFilterOptions
ProtoReflectWithOpts generates AIP filter options from a protobuf message using custom options. It allows for more control over the reflection process, including field exclusion and custom column name mapping.
type CreateOption ¶
type CreateOption byte
CreateOption represents options for database insert operations.
const InsertOnConflictionDoNothing CreateOption = 1 << iota
InsertOnConflictionDoNothing specifies that conflicts should be ignored during inserts.
type PageRequest ¶
type PageRequest struct {
// Filter is a filter expression that restricts the results to return.
Filter string
// OrderBy is a comma-separated list of fields to order by.
OrderBy string
// PageSize is the maximum number of results to return.
PageSize int32
// PageToken is the page token to use for the next request.
PageToken string
}
PageRequest represents a request for paginated data with filtering and ordering options.
func (*PageRequest) GetFilter ¶
func (p *PageRequest) GetFilter() string
GetFilter returns the filter expression for the page request.
func (*PageRequest) GetOrderBy ¶
func (p *PageRequest) GetOrderBy() string
GetOrderBy returns the order by expression for the page request.
func (*PageRequest) GetPageSize ¶
func (p *PageRequest) GetPageSize() int32
GetPageSize returns the maximum number of results to return for the page request.
func (*PageRequest) GetPageToken ¶
func (p *PageRequest) GetPageToken() string
GetPageToken returns the page token for the next request.
type PageToken ¶
type PageToken[T any] struct { QuerySet[T] `json:"-"` Offset uint `json:"offset"` Filter string `json:"filter"` OrderBy string `json:"order_by"` PageSize uint `json:"page_size"` }
PageToken represents a pagination token that encodes the current state of pagination including offset, filter, ordering, and page size information.
func NewPageToken ¶
NewPageToken creates a new PageToken instance for the given type T.
type Paginatable ¶
type Paginatable interface {
GetFilter() string
GetOrderBy() string
GetPageSize() int32
GetPageToken() string
}
Paginatable is an interface that defines the methods required for pagination support. Types implementing this interface can be used with pagination functionality.
type PostgreSQL ¶
type PostgreSQL struct {
// contains filtered or unexported fields
}
PostgreSQL represents a PostgreSQL database connection with transaction support. It wraps sqlx.DB for regular database operations and sqlx.Tx for transactional operations.
func NewPostgreSQL ¶
func NewPostgreSQL(connectionString string) (*PostgreSQL, error)
NewPostgreSQL returns a new PostgreSQL instance. connectionString should be sqlx compatible.
func NewPostgreSQLFromDB ¶
func NewPostgreSQLFromDB(db *sqlx.DB) *PostgreSQL
NewPostgreSQLFromDB creates a new PostgreSQL instance from an existing sqlx.DB connection.
func (*PostgreSQL) Begin ¶
func (p *PostgreSQL) Begin(ctx context.Context) error
Begin starts a new transaction.
func (*PostgreSQL) Commit ¶
func (p *PostgreSQL) Commit() error
Commit commits the current transaction.
func (*PostgreSQL) DB ¶
func (p *PostgreSQL) DB() *sqlx.DB
DB returns the underlying sqlx.DB instance.
func (*PostgreSQL) Queryable ¶
func (p *PostgreSQL) Queryable() Queryable
Queryable returns the current queryable interface (either DB or transaction).
func (*PostgreSQL) Rollback ¶
func (p *PostgreSQL) Rollback() error
Rollback rolls back the current transaction.
func (PostgreSQL) TableAlias ¶
type ProtoReflectOptions ¶
type ProtoReflectOptions struct {
// Exclude is a list of field names to exclude from the filter
// Uses proto name always, not JSON name
Exclude []string
// ColumnName is a function that returns the column name for a given field
// name. If not provided, the field name is used.
ColumnName func(string) string
}
ProtoReflectOptions configures how protobuf message reflection is performed for generating AIP filter options.
type QuerySet ¶
type QuerySet[T any] interface { // Filter returns a new QuerySet with the given filters applied. // The filters are applied in the order they are given. // Only use named parameters in the filters. // Multiple filter calls can be made, they will be combined with AND. // Will also work as AND combined // See FilterOr for OR combined. // See FilterInnerOr for inner filters combined with OR. // See FilterOrInnerOr for inner filters combined with OR. // Filter keys can also contain various hints (use as suffix to filter key): // - "__ne" to negate the filter // - "__in" to use an IN clause // - "__nin" to use a NOT IN clause // - "__gt" to use a > clause // - "__gte" to use a >= clause // - "__lt" to use a < clause // - "__lte" to use a <= clause // - "__like" to use a LIKE clause // - "__nlike" to use a NOT LIKE clause // - "__ilike" to use a ILIKE clause // - "__nilike" to use a NOT ILIKE clause // - "__null" to use a IS NULL clause // - "__notnull" to use a IS NOT NULL clause // - "__or" to prepend with OR instead of AND (in AND filter calls) // - "__and" to prepend with AND instead of OR (in OR filter calls) Filter(queries ...string) QuerySet[T] // FilterOr returns a new QuerySet with the given filters applied. // The filters are applied in the order they are given. // Only use named parameters in the filters. // Multiple filter calls can be made, they will be combined with AND. // But will work as OR combined // See Filter for AND combined. FilterOr(queries ...string) QuerySet[T] // FilterInnerOr returns a new QuerySet with the given filters applied. // Same as Filter, but inner filters are combined with OR. FilterInnerOr(queries ...string) QuerySet[T] // FilterOrInnerOr returns a new QuerySet with the given filters applied. // Same as FilterOr, but inner filters are combined with OR. FilterOrInnerOr(queries ...string) QuerySet[T] // Args sets named arguments for the filters. // The arguments are applied in the order they are given. Args(args *orderedmap.OrderedMap[string, interface{}]) QuerySet[T] // ClearArgs clear filters, args, and any previous set joins ClearAll() QuerySet[T] // Create creates a new value Create(ctx context.Context, value *T, options ...CreateOption) error // Update updates a value // All filters will be applied Update(ctx context.Context, value *T) error // Delete deletes a row // All filters will be applied Delete(ctx context.Context) error // GetOrNil returns a single value or nil // Multiple values will return an error. // Ignores Limit GetOrNil(ctx context.Context) (*T, error) // Get returns a single value // Returns error if no value is found // Returns error if multiple values are found // Ignores Limit Get(ctx context.Context) (*T, error) // All returns all values All(ctx context.Context) ([]*T, error) // Count returns the number of values Count(ctx context.Context) (int, error) // Limit sets the limit for the query Limit(limit int) QuerySet[T] // Offset sets the offset for the query Offset(offset int) QuerySet[T] // OrderBy sets the order for the query // Use - to indicate descending order // Example: // OrderBy("-id", "name") OrderBy(order ...string) QuerySet[T] // ResetOrderBy resets the order for the query ResetOrderBy() QuerySet[T] // CreateQuery returns the query and args for Create CreateQuery(value *T, options ...CreateOption) (string, []interface{}) // UpdateQuery returns the query and args for Update UpdateQuery(value *T) (string, []interface{}) // DeleteQuery returns the query and args for Delete DeleteQuery() (string, []interface{}) // GetOrNilQuery returns the query and args for GetOrNil GetOrNilQuery() (string, []interface{}) // GetQuery returns the query and args for Get GetQuery() (string, []interface{}) // AllQuery returns the query and args for All AllQuery() (string, []interface{}) // Extensions // AIP-160 filtering for gRPC/Proto // See https://google.aip.dev/160 AIP160(filter string, options AIPFilterOptions) (QuerySet[T], error) // Page token functionality for gRPC // The count is optional and returns the total number of rows for the query. // It is implemented as a variadic function to not break existing code. GetPage(ctx context.Context, paginatable Paginatable, options AIPFilterOptions, count ...*int) ([]*T, string, error) // Join table InnerJoin(modelFirst, modelSecond interface{}, keyFirst, keySecond string) QuerySet[T] LeftJoin(modelFirst, modelSecond interface{}, keyFirst, keySecond string) QuerySet[T] RightJoin(modelFirst, modelSecond interface{}, keyFirst, keySecond string) QuerySet[T] FullJoin(modelFirst, modelSecond interface{}, keyFirst, keySecond string) QuerySet[T] // Exclude fields Exclude(excludes ...string) QuerySet[T] // Include fields Include(includes ...string) QuerySet[T] // U is a shorthand for Update. ID field is used as the filter. // Other filters applied to the query set are also inherited. // Returns an error if the ID field is not set or does not exist. // Thus preventing accidental updates to all rows. U(ctx context.Context, value *T) error // F is a shorthand for Filter. It is a variadic function that accepts a list of filters. // The filters are applied in the order they are given. // Format is as follows: <KEY>, <VALUE> etc. F(keyval ...any) QuerySet[T] // D is a shorthand for Delete. ID field is used as the filter. // Other filters applied to the query set are also inherited. // Returns an error if the ID field is not set or does not exist. // Thus preventing accidental deletes to all rows. D(ctx context.Context, value *T) error // Transaction is a shorthand for wrapping a query set in a transaction. // Currently Pika transactions affects the full connection, not just the query set. // That method works if you use factories to create query sets. // This helper will re-use the internal DB instance to return a new query set with the transaction. Transaction(ctx context.Context) (QuerySet[T], error) }
QuerySet is a chainable interface for building queries. WARNING: This interface is not thread-safe and is meant to be used in a single goroutine. Do not re-use a QuerySet after calling All, Get, GetOrNil, Count, or any other method that returns a value. Creating a QuerySet is cheap and is meant to be discarded after use. It builds up a query string and arguments, and then executes the query. The query set can be passed onto another goroutine, given that it is modified only by one party. For safety, DO NOT pass a QuerySet to another goroutine and always create a new one. Use Q to create a new QuerySet.
func PSQLQuery ¶
func PSQLQuery[T any](p *PostgreSQL) QuerySet[T]
PSQLQuery creates a new QuerySet for PostgreSQL database operations. It initializes the query builder with metadata for the given type T and sets up table name resolution based on model metadata or pluralized model names.
type Queryable ¶
type Queryable interface {
sqlx.Ext
sqlx.ExecerContext
sqlx.PreparerContext
sqlx.QueryerContext
sqlx.Preparer
GetContext(ctx context.Context, dest interface{}, query string, args ...interface{}) error
MustExecContext(ctx context.Context, query string, args ...interface{}) sql.Result
NamedExecContext(ctx context.Context, query string, arg interface{}) (sql.Result, error)
PrepareNamedContext(ctx context.Context, query string) (*sqlx.NamedStmt, error)
PreparexContext(ctx context.Context, query string) (*sqlx.Stmt, error)
QueryRowContext(ctx context.Context, query string, args ...interface{}) *sql.Row
SelectContext(ctx context.Context, dest interface{}, query string, args ...interface{}) error
}
Queryable includes all methods shared by sqlx.DB and sqlx.Tx, allowing either type to be used interchangeably.
Source Files
Directories