query

package module
Version: v1.5.3 Latest Latest
Warning

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

Go to latest
Published: Mar 31, 2017 License: MIT Imports: 8 Imported by: 61

README

Query GoDoc Go Report Card

Query lets you build SQL queries with chainable methods, and defer execution of SQL until you wish to extract a count or array of models. It will probably remain limited in scope - it is not intended to be a full ORM with strict mapping between db tables and structs, but a tool for querying the database with minimum friction, and performing CRUD operations linked to models; simplifying your use of SQL to store model data without getting in the way. Full or partial SQL queries are of course also available, and full control over sql. Model creation and column are delegated to the model, to avoid dictating any particular model structure or interface, however a suggested interface is given (see below and in tests), which makes usage painless in your handlers without any boilerplate.

Supported databases: PostgreSQL, SQLite, MySQL. Bug fixes, suggestions and contributions welcome.

Usage


// In your app - open a database with options
options := map[string]string{"adapter":"postgres","db":"query_test"}
err := query.OpenDatabase(options)
defer query.CloseDatabase()

...

// In your model
type Page struct {
	ID			int64
	CreatedAt   time.Time
	UpdatedAt   time.Time
	MyField	    myStruct
	...
	// Models can have any structure, any PK, here an int is used
}

// Normally you'd define helpers on your model class to load rows from the database
// Query does not attempt to read data into columns with reflection or tags - 
// that is left to your model so you can read as little or as much as you want from queries

func Find(ID int64) (*Page, error) {
	result, err := PagesQuery().Where("id=?", ID).FirstResult()
	if err != nil {
		return nil, err
	}
	return NewWithColumns(result), nil
}

func FindAll(q *Query) ([]*Page, error) {
	results, err := q.Results()
	if err != nil {
		return nil, err
	}

	var models []*Page
	for _, r := range results {
		m := NewWithColumns(r)
		models = append(models, m)
	}

	return models, nil
}

...

// In your handlers, construct queries and ask your models for the data

// Find a simple model by id
page, err := pages.Find(1)

// Start querying the database using chained finders
q := page.Query().Where("id IN (?,?)",4,5).Order("id desc").Limit(30)

// Build up chains depending on other app logic, still no db requests
if shouldRestrict {
	q.Where("id > ?",3).OrWhere("keywords ~* ?","Page")
}

// Pass the relation around, until you are ready to retrieve models from the db
results, err := pages.FindAll(q)

What it does

  • Builds chainable queries including where, orwhere,group,having,order,limit,offset or plain sql
  • Allows any Primary Key/Table name or model fields (query.New lets you define this)
  • Allows Delete and Update operations on queried records, without creating objects
  • Defers SQL requests until full query is built and results requested
  • Provide helpers and return results for join ids, counts, single rows, or multiple rows

What it doesn't do

  • Attempt to read your models with reflection or struct tags
  • Require changes to your structs like tagging fields or specific fields
  • Cause problems with untagged fields, embedding, and fields not in the database
  • Provide hooks after/before update etc - your models are fully in charge of queries and their lifecycle

Tests

All 3 databases supported have a test suite - to run the tests, create a database called query_test in mysql and psql then run go test at the root of the package. The sqlite tests are disabled by default because enabling them prohibits cross compilation, which is useful if you don't want to install go on your server but just upload a binary compiled locally.

go test

Versions

  • 1.0 - First version with interfaces and chainable finders
  • 1.0.1 - Updated to quote table names and fields, for use of reserved words, bug fix for mysql concurrency
  • 1.3 - updated API, now shifted instantiation to models instead, to avoid use of reflection
  • 1.3.1 - Fixed bugs in Mysql import, updated tests

Documentation

Index

Constants

This section is empty.

Variables

View Source
var Debug bool

Debug sets whether we output debug statements for SQL

Functions

func CloseDatabase

func CloseDatabase() error

CloseDatabase closes the database opened by OpenDatabase

func Exec

func Exec(sql string, args ...interface{}) (sql.Result, error)

Exec the given sql and args against the database directly Returning sql.Result (NB not rows)

func ExecSQL

func ExecSQL(query string, args ...interface{}) (sql.Result, error)

ExecSQL executes the given sql against our database with arbitrary args NB returns sql.Result - not to be used when rows expected

func OpenDatabase

func OpenDatabase(opts map[string]string) error

OpenDatabase opens the database with the given options

func QuerySQL

func QuerySQL(query string, args ...interface{}) (*sql.Rows, error)

QuerySQL executes the given sql Query against our database, with arbitrary args

func Rows

func Rows(sql string, args ...interface{}) (*sql.Rows, error)

Rows executes the given sql and args against the database directly Returning sql.Rows

func SetMaxOpenConns added in v1.3.4

func SetMaxOpenConns(max int)

SetMaxOpenConns sets the maximum number of open connections

func TimeString

func TimeString(t time.Time) string

TimeString returns a string formatted as a time for this db if the database is nil, an empty string is returned.

func ToCamel

func ToCamel(text string, private ...bool) string

ToCamel converts a string from database column names to corresponding struct field names (e.g. field_name to FieldName).

func ToPlural

func ToPlural(text string) (plural string)

ToPlural returns the plural version of an English word using some simple rules and a table of exceptions.

func ToSingular

func ToSingular(word string) (singular string)

ToSingular converts a word to singular. NB reversal from plurals may fail

func ToSnake

func ToSnake(text string) string

ToSnake converts a string from struct field names to corresponding database column names (e.g. FieldName to field_name).

func Truncate

func Truncate(s string, length int) string

Truncate the given string to length using … as ellipsis.

func TruncateWithEllipsis

func TruncateWithEllipsis(s string, length int, ellipsis string) string

TruncateWithEllipsis truncates the given string to length using provided ellipsis.

Types

type Func

type Func func(q *Query) *Query

Func is a function which applies effects to queries

type Query

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

Query provides all the chainable relational query builder methods

func New

func New(t string, pk string) *Query

New builds a new Query, given the table and primary key

func (*Query) Apply

func (q *Query) Apply(f Func) *Query

Apply the Func to this query, and return the modified Query This allows chainable finders from other packages e.g. q.Apply(status.Published) where status.Published is a Func

func (*Query) Conditions

func (q *Query) Conditions(funcs ...Func) *Query

Conditions applies a series of query funcs to a query

func (*Query) Copy added in v1.3.5

func (q *Query) Copy() *Query

Copy returns a new copy of this query which can be mutated without affecting the original

func (*Query) Count

func (q *Query) Count() (int64, error)

Count fetches a count of model objects (executes SQL).

func (*Query) DebugString

func (q *Query) DebugString() string

DebugString returns a query representation string useful for debugging

func (*Query) Delete

func (q *Query) Delete() error

Delete one model specified in this relation

func (*Query) DeleteAll

func (q *Query) DeleteAll() error

DeleteAll delets *all* models specified in this relation

func (*Query) FirstResult

func (q *Query) FirstResult() (Result, error)

FirstResult executes the SQL and returrns the first result

func (*Query) Group

func (q *Query) Group(sql string) *Query

Group defines GROUP BY sql

func (*Query) Having

func (q *Query) Having(sql string) *Query

Having defines HAVING sql

func (*Query) Insert

func (q *Query) Insert(params map[string]string) (int64, error)

Insert inserts a record in the database

func (*Query) InsertJoin

func (q *Query) InsertJoin(a int64, b int64) error

InsertJoin inserts a join clause on the query

func (*Query) InsertJoins

func (q *Query) InsertJoins(a []int64, b []int64) error

InsertJoins using an array of ids (more general version of above) This inserts joins for every possible relation between the ids

func (*Query) Join

func (q *Query) Join(otherModel string) *Query

Join adds an inner join to the query

func (*Query) Limit

func (q *Query) Limit(limit int) *Query

Limit sets the sql LIMIT with an int

func (*Query) Offset

func (q *Query) Offset(offset int) *Query

Offset sets the sql OFFSET with an int

func (*Query) OrWhere

func (q *Query) OrWhere(sql string, args ...interface{}) *Query

OrWhere defines a where clause on SQL - Additional calls add WHERE () OR () clauses

func (*Query) Order

func (q *Query) Order(sql string) *Query

Order defines ORDER BY sql

func (*Query) QueryString

func (q *Query) QueryString() string

QueryString builds a query string to use for results

func (*Query) Result

func (q *Query) Result() (sql.Result, error)

Result executes the query against the database, returning sql.Result, and error (no rows) (Executes SQL)

func (*Query) ResultFloat64 added in v1.5.1

func (q *Query) ResultFloat64(c string) (float64, error)

ResultFloat64 returns the first result from a query stored in the column named col as a float64.

func (*Query) ResultIDSets

func (q *Query) ResultIDSets(a, b string) map[int64][]int64

ResultIDSets returns a map from a values to arrays of b values, the order of a,b is respected not the table key order

func (*Query) ResultIDs

func (q *Query) ResultIDs() []int64

ResultIDs returns an array of ids as the result of a query FIXME - this should really use the query primary key, not "id" hardcoded

func (*Query) ResultInt64 added in v1.3.5

func (q *Query) ResultInt64(c string) (int64, error)

ResultInt64 returns the first result from a query stored in the column named col as an int64.

func (*Query) Results

func (q *Query) Results() ([]Result, error)

Results returns an array of results

func (*Query) Rows

func (q *Query) Rows() (*sql.Rows, error)

Rows executes the query against the database, and return the sql rows result for this query (Executes SQL)

func (*Query) SQL

func (q *Query) SQL(sql string) *Query

SQL defines sql manually and overrides all other setters Completely replaces all stored sql

func (*Query) Select

func (q *Query) Select(sql string) *Query

Select defines SELECT sql

func (*Query) Update

func (q *Query) Update(params map[string]string) error

Update one model specified in this query - the column names MUST be verified in the model

func (*Query) UpdateAll

func (q *Query) UpdateAll(params map[string]string) error

UpdateAll updates all models specified in this relation

func (*Query) UpdateJoins

func (q *Query) UpdateJoins(id int64, a []int64, b []int64) error

UpdateJoins updates the given joins, using the given id to clear joins first

func (*Query) Where

func (q *Query) Where(sql string, args ...interface{}) *Query

Where defines a WHERE clause on SQL - Additional calls add WHERE () AND () clauses

func (*Query) WhereIn

func (q *Query) WhereIn(col string, IDs []int64) *Query

WhereIn adds a Where clause which selects records IN() the given array If IDs is an empty array, the query limit is set to 0

type Result

type Result map[string]interface{}

Result holds the results of a query as map[string]interface{}

Directories

Path Synopsis
Package adapters offers adapters for pouplar databases
Package adapters offers adapters for pouplar databases

Jump to

Keyboard shortcuts

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