Additions to Go's database/sql for super fast performance and convenience. (fork of gocraft/dbr)
Branch: master
Clone or download
Latest commit bee39f3 Aug 26, 2018
Permalink
Type Name Latest commit message Commit time
Failed to load latest commit information.
dialect FEATURE: added clickhouse driver Jun 26, 2017
.gitignore Combine args Aug 24, 2018
.travis.yml
CHANGELOG.md CLEANUP: Add a changelog with 2.0 changes. Oct 27, 2015
LICENSE FEATURE: 2.0 Sep 19, 2015
README.md FEATURE: added clickhouse driver Jun 26, 2017
buffer.go TWEAK: removed from public API NewBuffer and MapKeys Jun 1, 2017
buffer_test.go TWEAK: removed from public API NewBuffer and MapKeys Jun 1, 2017
builder.go DOC: added comments for public API Jun 1, 2017
condition.go Combine args Aug 24, 2018
condition_test.go TWEAK: removed from public API NewBuffer and MapKeys Jun 1, 2017
dbr.go Add method to fork session Sep 12, 2017
dbr_go18.go FEATURE: add context support May 11, 2017
dbr_go18_test.go - sort imports Feb 25, 2018
dbr_test.go Add method to fork session Sep 12, 2017
delete.go TWEAK: replace Condition with Builder Aug 3, 2016
delete_builder.go DOC: added comments for public API Jun 1, 2017
delete_test.go TWEAK: removed from public API NewBuffer and MapKeys Jun 1, 2017
dialect.go TWEAK: moved limits statement into driver level Jun 5, 2017
errors.go BUGFIX: Handle NullTimes from MySQL without parseTime=true. Nov 11, 2015
event.go FEATURE: 2.0 Sep 19, 2015
expr.go DOC: added comments for public API Jun 1, 2017
ident.go DOC: added comments for public API Jun 1, 2017
insert.go FEATURE: added 'on conflict update' statement May 12, 2017
insert_builder.go DOC: added comments for public API Jun 1, 2017
insert_test.go TWEAK: removed from public API NewBuffer and MapKeys Jun 1, 2017
interpolate.go
interpolate_test.go FEATURE: added clickhouse driver Jun 26, 2017
join.go Combine args Aug 24, 2018
load.go [*] fix kvScanner.Scan and test on bug Apr 5, 2018
load_test.go [*] fix kvScanner.Scan and test on bug Apr 5, 2018
now.go TWEAK: make time format const Aug 2, 2016
order.go TWEAK: un-export uncommon helpers Jul 7, 2016
select.go
select_builder.go support SELECT ... FOR UPDATE Dec 7, 2017
select_return.go FEATURE: 2.0 Sep 19, 2015
select_test.go support SELECT ... FOR UPDATE Dec 7, 2017
transaction.go FEATURE: add context support May 11, 2017
transaction_test.go FEATURE: added clickhouse driver Jun 26, 2017
types.go - unwrap (not needed) Feb 25, 2018
types_test.go FEATURE: added clickhouse driver Jun 26, 2017
union.go DOC: added comments for public API Jun 1, 2017
update.go TWEAK: replace Condition with Builder Aug 3, 2016
update_builder.go
update_test.go TWEAK: removed from public API NewBuffer and MapKeys Jun 1, 2017
util.go TWEAK: refactored Load Apr 13, 2017
util_test.go TWEAK: refactored Load Apr 13, 2017

README.md

dbr (fork of gocraft/dbr) provides additions to Go's database/sql for super fast performance and convenience.

Build Status Go Report Card Coverage Status

Getting Started

// create a connection (e.g. "postgres", "mysql", or "sqlite3")
conn, _ := dbr.Open("postgres", "...")

// create a session for each business unit of execution (e.g. a web request or goworkers job)
sess := conn.NewSession(nil)

// get a record
var suggestion Suggestion
sess.Select("id", "title").From("suggestions").Where("id = ?", 1).Load(&suggestion)

// JSON-ready, with dbr.Null* types serialized like you want
json.Marshal(&suggestion)

Feature highlights

Use a Sweet Query Builder or use Plain SQL

mailru/dbr supports both.

Sweet Query Builder:

stmt := dbr.Select("title", "body").
	From("suggestions").
	OrderBy("id").
	Limit(10)

Plain SQL:

builder := dbr.SelectBySql("SELECT `title`, `body` FROM `suggestions` ORDER BY `id` ASC LIMIT 10")

Amazing instrumentation with session

All queries in mailru/dbr are made in the context of a session. This is because when instrumenting your app, it's important to understand which business action the query took place in.

Writing instrumented code is a first-class concern for mailru/dbr. We instrument each query to emit to a EventReceiver interface.

Faster performance than using database/sql directly

Every time you call database/sql's db.Query("SELECT ...") method, under the hood, the mysql driver will create a prepared statement, execute it, and then throw it away. This has a big performance cost.

mailru/dbr doesn't use prepared statements. We ported mysql's query escape functionality directly into our package, which means we interpolate all of those question marks with their arguments before they get to MySQL. The result of this is that it's way faster, and just as secure.

Check out these benchmarks.

IN queries that aren't horrible

Traditionally, database/sql uses prepared statements, which means each argument in an IN clause needs its own question mark. mailru/dbr, on the other hand, handles interpolation itself so that you can easily use a single question mark paired with a dynamically sized slice.

ids := []int64{1, 2, 3, 4, 5}
builder.Where("id IN ?", ids) // `id` IN ?

map object can be used for IN queries as well. Note: interpolation map is slower than slice and it is preferable to use slice when it is possible.

ids := map[int64]string{1: "one", 2: "two"}
builder.Where("id IN ?", ids)  // `id` IN ?

JSON Friendly

Every try to JSON-encode a sql.NullString? You get:

{
	"str1": {
		"Valid": true,
		"String": "Hi!"
	},
	"str2": {
		"Valid": false,
		"String": ""
  }
}

Not quite what you want. mailru/dbr has dbr.NullString (and the rest of the Null* types) that encode correctly, giving you:

{
	"str1": "Hi!",
	"str2": null
}

Inserting multiple records

sess.InsertInto("suggestions").Columns("title", "body").
  Record(suggestion1).
  Record(suggestion2)

Updating records on conflict

stmt := sess.InsertInto("suggestions").Columns("title", "body").Record(suggestion1)
stmt.OnConflict("suggestions_pkey").Action("body", dbr.Proposed("body"))

Updating records

sess.Update("suggestions").
	Set("title", "Gopher").
	Set("body", "I love go.").
	Where("id = ?", 1)

Transactions

tx, err := sess.Begin()
if err != nil {
  return err
}
defer tx.RollbackUnlessCommitted()

// do stuff...

return tx.Commit()

Load database values to variables

Querying is the heart of mailru/dbr.

  • Load(&any): load everything!
  • LoadStruct(&oneStruct): load struct
  • LoadStructs(&manyStructs): load a slice of structs
  • LoadValue(&oneValue): load basic type
  • LoadValues(&manyValues): load a slice of basic types
// columns are mapped by tag then by field
type Suggestion struct {
	ID int64  // id, will be autoloaded by last insert id
	Title string // title
	Url string `db:"-"` // ignored
	secret string // ignored
	Body dbr.NullString `db:"content"` // content
	User User
}

// By default dbr converts CamelCase property names to snake_case column_names
// You can override this with struct tags, just like with JSON tags
// This is especially helpful while migrating from legacy systems
type Suggestion struct {
	Id        int64
	Title     dbr.NullString `db:"subject"` // subjects are called titles now
	CreatedAt dbr.NullTime
}

var suggestions []Suggestion
sess.Select("*").From("suggestions").Load(&suggestions)

Join multiple tables

dbr supports many join types:

sess.Select("*").From("suggestions").
  Join("subdomains", "suggestions.subdomain_id = subdomains.id")

sess.Select("*").From("suggestions").
  LeftJoin("subdomains", "suggestions.subdomain_id = subdomains.id")

sess.Select("*").From("suggestions").
  RightJoin("subdomains", "suggestions.subdomain_id = subdomains.id")

sess.Select("*").From("suggestions").
  FullJoin("subdomains", "suggestions.subdomain_id = subdomains.id")

You can join on multiple tables:

sess.Select("*").From("suggestions").
  Join("subdomains", "suggestions.subdomain_id = subdomains.id").
  Join("accounts", "subdomains.accounts_id = accounts.id")

Quoting/escaping identifiers (e.g. table and column names)

dbr.I("suggestions.id") // `suggestions`.`id`

Subquery

sess.Select("count(id)").From(
  dbr.Select("*").From("suggestions").As("count"),
)

Union

dbr.Union(
  dbr.Select("*"),
  dbr.Select("*"),
)

dbr.UnionAll(
  dbr.Select("*"),
  dbr.Select("*"),
)

Union can be used in subquery.

Alias/AS

  • SelectStmt
dbr.Select("*").From("suggestions").As("count")
  • Identity
dbr.I("suggestions").As("s")
  • Union
dbr.Union(
  dbr.Select("*"),
  dbr.Select("*"),
).As("u1")

dbr.UnionAll(
  dbr.Select("*"),
  dbr.Select("*"),
).As("u2")

Building arbitrary condition

One common reason to use this is to prevent string concatenation in a loop.

  • And
  • Or
  • Eq
  • Neq
  • Gt
  • Gte
  • Lt
  • Lte
dbr.And(
  dbr.Or(
    dbr.Gt("created_at", "2015-09-10"),
    dbr.Lte("created_at", "2015-09-11"),
  ),
  dbr.Eq("title", "hello world"),
)

Built with extensibility

The core of dbr is interpolation, which can expand ? with arbitrary SQL. If you need a feature that is not currently supported, you can build it on your own (or use dbr.Expr).

To do that, the value that you wish to be expaned with ? needs to implement dbr.Builder.

type Builder interface {
	Build(Dialect, Buffer) error
}

Driver support

  • MySQL
  • PostgreSQL
  • SQLite3
  • ClickHouse

These packages were developed by the engineering team at UserVoice and currently power much of its infrastructure and tech stack.

Thanks & Authors

Inspiration from these excellent libraries:

  • sqlx - various useful tools and utils for interacting with database/sql.
  • Squirrel - simple fluent query builder.

Authors:

Contributors: