Data access layer for PostgreSQL, CockroachDB, MySQL, SQLite and MongoDB with ORM-like features.
MIT License
Published by VojtechVitek about 6 years ago
minor release, fixes a deadlock
Published by xiam about 7 years ago
Starting on 3.5.0 you'll be able to use comparison operators that are compatible across different database engines, see some examples:
// foo = "bar"
db.Cond{
"foo": db.Eq("bar")
}
// foo <= 15
db.Cond{
"foo": db.Lte(15)
}
// foo >= 17
db.Cond{
"foo": db.Gte(17)
}
// foo BETWEEN DATEA AND DATEB
db.Cond{
"foo": db.Between(dateA, dateB)
}
// foo IS NOT NULL
db.Cond{
"foo": db.IsNotNull(),
}
// foo -> 45
db.Cond{
"foo": db.Op("->", 45),
}
This is the full list of comparison functions:
db.Eq(interface{})
db.NotEq(interface{})
db.Gte(interface{})
db.Gt(interface{})
db.Lte(interface{})
db.Lt(interface{})
db.Between(interface{}, interface{})
db.NotBetween(interface{}, interface{})
db.In(interface{})
db.NotIn(interface{})
db.After(time.Time)
db.Before(time.Time)
db.OnOrAfter(time.Time)
db.OnOrBefore(time.Time)
db.Is(interface{})
db.IsNot(interface{})
db.IsNull()
db.IsNotNull()
db.Like(string)
db.NotLike(string)
db.RegExp(string)
db.NotRegExp(string)
db.Op(string, interface{})
The old syntax (db.Cond{"column operator": value}
) will continue to be supported along to the new syntax, no code changes are required to upgrade from 3.4.x.
Thank you for using upper-db!
Published by xiam about 7 years ago
?
marks inside queriesYou can now escape ?
marks inside queries by using ??
:
// ...WHERE someColumn ? "Some Value"
col.Find().Where(`someColumn ?? ?`, "Some Value")
Fields tagged with stringarray
and int64array
will no longer emit a deprecation message, as []string
and []int64
will be converted automatically to the corresponding PostgreSQL array types like before. We recommend removing them anyways.
Fields tagged with jsonb
will still emit an error when used, no matter what their type is they all need to satisfy sql.Scanner
and driver.Valuer
(or sqlbuilder.ScannerValuer
).
We know that providing all those types with Scan(interface{}) error
and Value() (driver.Value, error)
by hand can be annoying, that's why we're providing this guide with common cases hoping that it might help you upgrading your codebase:
If your struct looks like:
type MyModel struct {
...
MyField CustomStruct `db:"my_field,jsonb"`
}
type CustomStruct struct {
Foo string `db:"foo"`
...
}
Change it into:
// 1. Import "upper.io/db.v3/postgresql"
import "upper.io/db.v3/postgresql"
type MyModel struct {
...
MyField CustomStruct `db:"my_field,jsonb"`
}
type CustomStruct struct {
Foo string `db:"foo"`
...
// 2. Embed this type.
*postgresql.JSONBConverter
}
// 3. (Optional) Import "upper.io/db.v3/lib/sqlbuilder" and add a compile-time type check
var _ sqlbuilder.ValueWrapper = &CustomStruct{}
If your struct looks like:
type MyModel struct {
...
MyField map[string]interface{} `db:"my_field,jsonb"`
}
Change it into:
// 1. Import "upper.io/db.v3/postgresql"
import "upper.io/db.v3/postgresql"
type MyModel struct {
...
// 2. Just remove "jsonb"
MyField map[string]interface{} `db:"my_field"`
}
If your struct looks like:
type MyModel struct {
...
MyField interface{} `db:"my_field,jsonb"`
}
Change it into:
// 1. Import "upper.io/db.v3/postgresql"
import "upper.io/db.v3/postgresql"
type MyModel struct {
...
// 2. Change interface{} into postgresql.JSONB
MyField postgresql.JSONB `db:"my_field,jsonb"`
}
You'll probably have to update more code and use myModel.MyField.V
(the actual interface) instead of myModel.MyField
.
If your struct looks like:
type MyModel struct {
...
MyField CustomStructArray `db:"my_field,jsonb"`
}
type CustomStructArray []CustomStruct
Change it into:
// 1. Import "upper.io/db.v3/postgresql"
import "upper.io/db.v3/postgresql"
type MyModel struct {
...
MyField CustomStructArray `db:"my_field,jsonb"`
}
type CustomStructArray []CustomStruct
// 2. Add a WrapValue method to satisfy sqlbuilder.ValueWrapper
func (cs CustomStructArray) WrapValue(v interface{}) interface{} {
return postgresql.Array(v)
}
// 3. Add Scan method to CustomStruct, if needed
func (c *CustomStruct) Scan(in interface{}) error {
...
}
Some types are going to be converted automatically to a proper type:
Go type | PostgreSQL type |
---|---|
[]string |
TEXT[] |
[]int64 |
INTEGER[] |
[]bool |
BOOLEAN[] |
[]float64 |
DOUBLE PRECISION[] |
map[string]interface{} |
JSONB |
Chances are you're using a different type, in that case you'll have to provide a proper driver.Valuer
and sql.Scanner
for them. If you're using a struct and embedding *postgresql.JSONBConverter
does not solve your case, you can try with postgresql.JSONBValue
and postgresql.ScanJSONB
:
import (
"database/sql/driver"
"upper.io/db.v3/postgresql"
)
type Settings struct {
Name string `json:"name"`
Num int64 `json:"num"`
}
func (s *Settings) Scan(src interface{}) error {
return postgresql.ScanJSONB(s, src)
}
func (s Settings) Value() (driver.Value, error) {
return postgresql.JSONBValue(s)
}
// var _ sqlbuilder.ScannerValuer = &Settings{} // Optional type check
Thanks for using upper-db. Feel free to open a ticket if you need help.
Published by xiam over 7 years ago
The new pagination API lets you split the results of a query into chunks containing a fixed number of items.
res = sess.Collection("posts").Paginate(20) // 20 results per page
err = res.All(&posts) // First 20 results of the query
err = res.Page(2).All(&posts) // Results from page 2 (limit 20, offset 40)
q = sess.SelectFrom("posts").Paginate(20)
err = res.All(&posts) // First 20 results of the query
err = res.Page(2).All(&posts) // Results from page 2 (limit 20, offset 40)
res = sess.Collection("posts").
Paginate(20). // 20 results per page
Cursor("id") // using id as cursor
err = res.All(&posts) // First 20 results of the query
// Get the next 20 results starting from the last item of the previous query.
res = res.NextPage(posts[len(posts)-1].ID)
err = res.All(&posts) // Results from page 1, limit 20, offset 20
res = res.Paginate(23)
totalNumberOfEntries, err = res.TotalEntries()
totalNumberOfPages, err = res.TotalPages()
pgbouncer requires binary mode (binary_parameters="yes"
) to be enabled, but all of the auto-fields that were working with text mode started failing with binary mode.
Starting with v3.3.0 upper-db supports binary mode. This is how you can enable binary mode in your PostgreSQL session:
settings = postgresql.ConnectionURL {
...
Options: map[string]string{
"binary_parameters": "yes",
},
}
Unfortunately, for this to work we had to push a breaking change: any fields that used stringarray
, int64array
and jsonb
options need to be changed into an special type.
// Before v3.3.0
type Demo struct {
MyIntegers []int64 `db:"my_integers,int64array"`
MyStrings []string `db:"my_strings,stringarray"`
MyWhatevs map[string]interface{}`db:"field,jsonb"`
}
// v3.3.0+
type Demo struct {
MyIntegers postgresql.Int64Array `db:"my_integers"`
MyStrings postgresql.StringArray `db:"my_strings"`
MyWhatevs postgresql.JSONBMap`db:"field"`
}
To make sure the user is aware of this, the mapping methods will now throw an error when stringarray
, int64array
or jsonb
field tags are detected.
You can use any other custom struct as long as it satisfies driver.Valuer
and sql.Scanner
. The postgresql
package providers some handy functions for when you want to encode something into JSONB format:
import (
...
"database/sql/driver"
"database/sql"
"upper.io/db.v3/postgresql"
)
type Demo struct {
MyWhatevs customJSONB `db:"field"`
}
type customJSONB struct {
N string `json:"name"`
V float64 `json:"value"`
}
func (c customJSONB) Value() (driver.Value, error) {
return postgresql.EncodeJSONB(c)
}
func (c *customJSONB) Scan(src interface{}) error {
return postgresql.DecodeJSONB(c, src)
}
var (
_ driver.Valuer = &customJSONB{}
_ sql.Scanner = &customJSONB{}
)
Here are some other types provided by the postgresql
package:
postgresql.Float64Array // []float64
postgresql.GenericArray // []interface{}
postgresql.BoolArray // []bool
postgresql.JSONB // interface{}
postgresql.JSONBMap // map[string]interface{}
postgresql.JSONBArray // []interface{}
Feel free to open a ticket if you find any bug or need help!
Published by xiam over 7 years ago
This is a maintenance release that takes care of these issues:
Published by xiam over 7 years ago
InsertReturning
and UpdateReturning
. See: https://github.com/upper/db/issues/370
postgresql.JSONB
, postgresql.StringArray
and postgresql.Int64Array
custom types for PostgreSQL, which will deprecate optional tags int64array
, stringarray
and jsonb
while providing full support for their Scanners and Valuers.Published by xiam over 7 years ago
The most important feature in this release is support for SQL Server via https://github.com/denisenkom/go-mssqldb.
Published by xiam over 7 years ago
This release includes new features and overall design improvements.
db.v3
uses a new import path: upper.io/db.v3
go get -u upper.io/db.v3
database/sql
features, like context.Context
support:// A regular query
res, err = sess.QueryContext(ctx, "SELECT * FROM authors")
// A transaction, all statements within this transaction run under the same context
sess.Tx(ctx, func() (tx sqlbuilder.Tx) error {
res := tx.Find()
// ...
})
With context.Context
you'll be able to cancel queries or set a timeout. db.v3
provides you with different tools to make it easy to use context.Context
:
sess := sess.WithContext(ctx) // A copy of `sess` on the given context.
res, err = sess.Query(...) // Will use the above `ctx` by default.
res, err = sess.QueryContext(anotherCtx, ...) // Uses a different context.
db.v3
can be compiled with go1.7 too (in this case, database/sql
will ignore context
features).
db.v2
modified the internal query state everytime you used a method on it:q := sess.SelectFrom("users")
q.Where(...) // This method modifies `q`'s internal state and returns the same `q`.
In v3, we decided to make the syntax more explicit to avoid potential side-effects.
q := sess.SelectFrom("users")
q = q.Where(...) // The `q` above does not get affected by Where(), unless we reassign `q`.
row, err := q.Query() // or q.QueryContext(ctx)
The new immutable behavior applies to all query builder methods:
q := sess.DeleteFrom("users").Where(...)
q = q.And(...)
q = q.Limit(5)
res, err := q.Exec() // Or q.ExecContext(ctx)
q := sess.Update("users").Set("foo", 3)
q = q.Where(...)
res, err := q.Exec() // Or q.ExecContext(ctx)
q := sess.InsertInto("users").Values(item1)
q = q.Values(item2)
res, err := q.Exec() // Or res.ExecContext(ctx)
And it also applies to db.And
, db.Or
and db.Result
:
params := db.And(db.Cond{"foo" :1})
params = params.And(db.Cond{"bar": 1})
params := db.Or(db.Cond{"foo" :1})
params = params.Or(db.Cond{"bar": 1})
res := col.Find()
res = res.Limit(5).Offset(1)
err = res.One(&item)
This is an important difference from db.v2
that makes queries safer and more consistent.
For users who want to migrate from db.v2
to db.v3
:
upper.io/db.v3
dbcheck
tool to find statements that you need to port to the new immutable syntax.# Install dbcheck
go get -u github.com/upper/cmd/dbcheck
# Use "..." at the end to check all github.com/my/package's subpackages.
dbcheck github.com/my/package/...
Thanks to our awesome sponsor Pressly! we could not have done this without you.
Pressly makes it easy for enterprises to curate and share amazing content.
Published by xiam almost 8 years ago