README
¶
Query 
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 projects 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 ¶
- Variables
- func CloseDatabase() error
- func Exec(sql string, args ...interface{}) (sql.Result, error)
- func ExecSQL(query string, args ...interface{}) (sql.Result, error)
- func OpenDatabase(opts map[string]string, mu *sync.RWMutex) error
- func QuerySQL(query string, args ...interface{}) (*sql.Rows, error)
- func Rows(sql string, args ...interface{}) (*sql.Rows, error)
- func SetMaxOpenConns(max int)
- func TimeString(t time.Time) string
- func ToCamel(text string, private ...bool) string
- func ToPlural(text string) (plural string)
- func ToSingular(word string) (singular string)
- func ToSnake(text string) string
- func Truncate(s string, length int) string
- func TruncateWithEllipsis(s string, length int, ellipsis string) string
- type Func
- type Query
- func (q *Query) Apply(f Func) *Query
- func (q *Query) Conditions(funcs ...Func) *Query
- func (q *Query) Copy() *Query
- func (q *Query) Count() (int64, error)
- func (q *Query) DebugString() string
- func (q *Query) Delete() error
- func (q *Query) DeleteAll() error
- func (q *Query) FirstResult() (Result, error)
- func (q *Query) Group(sql string) *Query
- func (q *Query) Having(sql string) *Query
- func (q *Query) Insert(params map[string]string) (int64, error)
- func (q *Query) InsertJoin(a int64, b int64) error
- func (q *Query) InsertJoins(a []int64, b []int64) error
- func (q *Query) Join(otherModel string) *Query
- func (q *Query) Limit(limit int) *Query
- func (q *Query) Offset(offset int) *Query
- func (q *Query) OrWhere(sql string, args ...interface{}) *Query
- func (q *Query) Order(sql string) *Query
- func (q *Query) QueryString() string
- func (q *Query) Result() (sql.Result, error)
- func (q *Query) ResultFloat64(c string) (float64, error)
- func (q *Query) ResultIDSets(a, b string) map[int64][]int64
- func (q *Query) ResultIDs() []int64
- func (q *Query) ResultInt64(c string) (int64, error)
- func (q *Query) Results() ([]Result, error)
- func (q *Query) Rows() (*sql.Rows, error)
- func (q *Query) SQL(sql string) *Query
- func (q *Query) Select(sql string) *Query
- func (q *Query) Update(params map[string]string) error
- func (q *Query) UpdateAll(params map[string]string) error
- func (q *Query) UpdateJoins(id int64, a []int64, b []int64) error
- func (q *Query) Where(sql string, args ...interface{}) *Query
- func (q *Query) WhereIn(col string, IDs []int64) *Query
- type Result
Constants ¶
This section is empty.
Variables ¶
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 ¶
Exec the given sql and args against the database directly Returning sql.Result (NB not rows)
func ExecSQL ¶
ExecSQL executes the given sql against our database with arbitrary args NB returns sql.Result - not to be used when rows expected
func OpenDatabase ¶
OpenDatabase opens the database with the given options
func SetMaxOpenConns ¶
func SetMaxOpenConns(max int)
SetMaxOpenConns sets the maximum number of open connections
func TimeString ¶
TimeString returns a string formatted as a time for this db if the database is nil, an empty string is returned.
func ToCamel ¶
ToCamel converts a string from database column names to corresponding struct field names (e.g. field_name to FieldName).
func ToPlural ¶
ToPlural returns the plural version of an English word using some simple rules and a table of exceptions.
func ToSingular ¶
ToSingular converts a word to singular. NB reversal from plurals may fail
func ToSnake ¶
ToSnake converts a string from struct field names to corresponding database column names (e.g. FieldName to field_name).
Types ¶
type Query ¶
type Query struct {
// contains filtered or unexported fields
}
Query provides all the chainable relational query builder methods
func (*Query) Apply ¶
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 ¶
Conditions applies a series of query funcs to a query
func (*Query) Copy ¶
Copy returns a new copy of this query which can be mutated without affecting the original
func (*Query) DebugString ¶
DebugString returns a query representation string useful for debugging
func (*Query) FirstResult ¶
FirstResult executes the SQL and returrns the first result
func (*Query) InsertJoin ¶
InsertJoin inserts a join clause on the query
func (*Query) InsertJoins ¶
InsertJoins using an array of ids (more general version of above) This inserts joins for every possible relation between the ids
func (*Query) OrWhere ¶
OrWhere defines a where clause on SQL - Additional calls add WHERE () OR () clauses
func (*Query) QueryString ¶
QueryString builds a query string to use for results
func (*Query) Result ¶
Result executes the query against the database, returning sql.Result, and error (no rows) (Executes SQL)
func (*Query) ResultFloat64 ¶
ResultFloat64 returns the first result from a query stored in the column named col as a float64.
func (*Query) ResultIDSets ¶
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 ¶
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 ¶
ResultInt64 returns the first result from a query stored in the column named col as an int64.
func (*Query) Rows ¶
Rows executes the query against the database, and return the sql rows result for this query (Executes SQL)
func (*Query) SQL ¶
SQL defines sql manually and overrides all other setters Completely replaces all stored sql
func (*Query) Update ¶
Update one model specified in this query - the column names MUST be verified in the model
func (*Query) UpdateJoins ¶
UpdateJoins updates the given joins, using the given id to clear joins first
Source Files
Directories