v3.5.6, A bugfix release

Fixes #474, an error in fetchRows() was ignored

v3.5.5

Fixes a problem with InsertReturning() and tables with varchar/string primary keys.

v3.5.4

minor release, fixes a deadlock

v3.5.0

Changelog for v3.5.0

Support for comparison operations

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.

Thanks

Thank you for using upper-db!

v3.4.0

Changelog for 3.4.0

Allow escaping ? marks inside queries

You can now escape ? marks inside queries by using ??:

// ...WHERE someColumn ? "Some Value"
col.Find().Where(`someColumn ?? ?`, "Some Value")

PostgreSQL: jsonb tag and JSONB values

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:

Structs

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{}

Maps

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"`
}

Interfaces

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.

Arrays

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 {
  ...
}

Other types

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

Thanks for using upper-db. Feel free to open a ticket if you need help.

v3.3.0

Changelog

Pagination

The new pagination API lets you split the results of a query into chunks containing a fixed number of items.

Simple pagination for db.Result

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)

Simple pagination for SQL builder

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)

Cursor based pagination (both for db.Result and SQL Builder)

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

Other commonly used pagination tools

res = res.Paginate(23)

totalNumberOfEntries, err = res.TotalEntries()

totalNumberOfPages, err = res.TotalPages()

Support for binary and text mode (PostgreSQL)

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!

v3.2.1

Changelog

This is a maintenance release that takes care of these issues:

• #383
• #381
• #380

v3.2.0

Changelog

• Multiple Where() calls append conditions instead of overwriting previous ones. See: #357
• Support for UUID in InsertReturning and UpdateReturning. See: #370
• Added 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.

v3.1.0

Changelog

The most important feature in this release is support for SQL Server via https://github.com/denisenkom/go-mssqldb.

v3.0.0

Changelog

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

• Takes advantage of go1.8+'s new 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).

• Provides per-database settings and logging (optional).
• The query builder on 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.

Migration notes

For users who want to migrate from db.v2 to db.v3:

• Remember to change all import paths to upper.io/db.v3
• Use the 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

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.