gocraft/dbr (database records)

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

$ go get -u github.com/gocraft/dbr/v2

import"github.com/gocraft/dbr/v2"

Driver support

• MySQL
• PostgreSQL
• SQLite3
• MsSQL

Examples

See godoc for more examples.

Open connections

// create a connection (e.g. "postgres", "mysql", or "sqlite3")conn, _:=Open("postgres", "...", nil)
conn.SetMaxOpenConns(10)
// create a session for each business unit of execution (e.g. a web request or goworkers job)sess:=conn.NewSession(nil)
// create a tx from sessionssess.Begin()

Create and use Tx

sess:=mysqlSessiontx, err:=sess.Begin()
iferr!=nil {
return
}
defertx.RollbackUnlessCommitted()
// do stuff...tx.Commit()

SelectStmt loads data into structs

// columns are mapped by tag then by fieldtypeSuggestionstruct {
IDint64// id, will be autoloaded by last insert idTitleNullString`db:"subject"`// subjects are called titles nowUrlstring`db:"-"`// ignoredsecretstring// ignored
}
// By default gocraft/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.varsuggestions []Suggestionsess:=mysqlSessionsess.Select("*").From("suggestions").Load(&suggestions)

SelectStmt with where-value interpolation

// database/sql uses prepared statements, which means each argument// in an IN clause needs its own question mark.// gocraft/dbr, on the other hand, handles interpolation itself// so that you can easily use a single question mark paired with a// dynamically sized slice.sess:=mysqlSessionids:= []int64{1, 2, 3, 4, 5}
sess.Select("*").From("suggestions").Where("id IN ?", ids)

SelectStmt with joins

sess:=mysqlSessionsess.Select("*").From("suggestions").
Join("subdomains", "suggestions.subdomain_id = subdomains.id")
sess.Select("*").From("suggestions").
LeftJoin("subdomains", "suggestions.subdomain_id = subdomains.id")
// join multiple tablessess.Select("*").From("suggestions").
Join("subdomains", "suggestions.subdomain_id = subdomains.id").
Join("accounts", "subdomains.accounts_id = accounts.id")

SelectStmt with raw SQL

SelectBySql("SELECT `title`, `body` FROM `suggestions` ORDER BY `id` ASC LIMIT 10")

InsertStmt adds data from struct

typeSuggestionstruct {
IDint64TitleNullStringCreatedAt	time.Time
}
sugg:=&Suggestion{
Title: NewNullString("Gopher"),
CreatedAt:	time.Now(),
}
sess:=mysqlSessionsess.InsertInto("suggestions").
Columns("title").
Record(sugg).
Exec()
// id is set automaticallyfmt.Println(sugg.ID)

InsertStmt adds data from value

sess:=mysqlSessionsess.InsertInto("suggestions").
Pair("title", "Gopher").
Pair("body", "I love go.")

Benchmark (2018-05-11)

BenchmarkLoadValues/sqlx_10-8 5000 407318 ns/op 3913 B/op 164 allocs/op
BenchmarkLoadValues/dbr_10-8 5000 372940 ns/op 3874 B/op 123 allocs/op
BenchmarkLoadValues/sqlx_100-8 2000 584197 ns/op 30195 B/op 1428 allocs/op
BenchmarkLoadValues/dbr_100-8 3000 558852 ns/op 22965 B/op 937 allocs/op
BenchmarkLoadValues/sqlx_1000-8 1000 2319101 ns/op 289339 B/op 14031 allocs/op
BenchmarkLoadValues/dbr_1000-8 1000 2310441 ns/op 210092 B/op 9040 allocs/op
BenchmarkLoadValues/sqlx_10000-8 100 17004716 ns/op 3193997 B/op 140043 allocs/op
BenchmarkLoadValues/dbr_10000-8 100 16150062 ns/op 2394698 B/op 90051 allocs/op
BenchmarkLoadValues/sqlx_100000-8 10 170068209 ns/op	31679944 B/op 1400053 allocs/op
BenchmarkLoadValues/dbr_100000-8 10 147202536 ns/op	23680625 B/op 900061 allocs/op

Thanks & Authors

Inspiration from these excellent libraries:

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

Authors:

• Jonathan Novak -- https://github.com/cypriss
• Tai-Lin Chu -- https://github.com/taylorchu
• Sponsored by UserVoice

Contributors:

• Paul Bergeron -- https://github.com/dinedal - SQLite dialect

License