gocraft/dbr provides additions to Go's database/sql for super fast performance and convenience.
package main
import (
"database/sql"
"encoding/json"
"fmt"
_ "github.com/go-sql-driver/mysql"
"github.com/gocraft/dbr"
)
// Simple data model
type Suggestion struct {
Id int64
Title string
CreatedAt dbr.NullTime
}
// Hold a single global connection (pooling provided by sql driver)
var connection *dbr.Connection
func main() {
// Create the connection during application initialization
db, _ := sql.Open("mysql", "root@unix(/tmp/mysqld.sock)/your_database")
connection = dbr.NewConnection(db, nil)
// Create a session for each business unit of execution (e.g. a web request or goworkers job)
dbrSess := connection.NewSession(nil)
// Get a record
var suggestion Suggestion
err := dbrSess.Select("id, title").From("suggestions").Where("id = ?", 13).LoadStruct(&suggestion)
if err != nil {
fmt.Println(err.Error())
} else {
fmt.Println("Title:", suggestion.Title)
}
// JSON-ready, with dbr.Null* types serialized like you want
recordJson, _ := json.Marshal(&suggestion)
fmt.Println(string(recordJson))
}
Querying is the heart of gocraft/dbr. Automatically map results to structs:
var posts []*struct {
Id int64
Title string
Body dbr.NullString
}
err := sess.Select("id, title, body").
From("posts").Where("id = ?", id).LoadStruct(&post)
Additionally, easily query a single value or a slice of values:
id, err := sess.SelectBySql("SELECT id FROM posts WHERE title=?", title).ReturnInt64()
ids, err := sess.SelectBySql("SELECT id FROM posts", title).ReturnInt64s()
See below for many more examples.
gocraft/dbr supports both.
Sweet Query Builder:
builder := sess.Select("title", "body").
From("posts").
Where("created_at > ?", someTime).
OrderBy("id ASC").
Limit(10)
var posts []*Post
n, err := builder.LoadStructs(&posts)
Plain SQL:
n, err := sess.SelectBySql(`SELECT title, body FROM posts WHERE created_at > ?
ORDER BY id ASC LIMIT 10`, someTime).LoadStructs(&post)
Traditionally, 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.
// Traditional database/sql way:
ids := []int64{1,2,3,4,5}
questionMarks := []string
for _, _ := range ids {
questionMarks = append(questionMarks, "?")
}
query := fmt.Sprintf("SELECT * FROM posts WHERE id IN (%s)",
strings.Join(questionMarks, ",") // lolwut
rows, err := db.Query(query, ids)
// gocraft/dbr way:
ids := []int64{1,2,3,4,5}
n, err := sess.SelectBySql("SELECT * FROM posts WHERE id IN ?", ids) // yay
Writing instrumented code is a first-class concern for gocraft/dbr. We instrument each query to emit to a gocraft/health-compatible EventReceiver interface. NOTE: we have not released gocraft/health yet. This allows you to instrument your app to easily connect gocraft/dbr to your metrics systems, such statsd.
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.
gocraft/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.
Every try to JSON-encode a sql.NullString? You get:
{
"str1": {
"Valid": true,
"String": "Hi!"
},
"str2": {
"Valid": false,
"String": ""
}
}
Not quite what you want. gocraft/dbr has dbr.NullString (and the rest of the Null* types) that encode correctly, giving you:
{
"str1": "Hi!",
"str2": null
}
Currently only MySQL has been tested because that is what we use. Feel free to make an issue for Postgres if you're interested in adding support and we can discuss what it would take.
All queries in gocraft/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. See gocraft/health for more detail.
Here's an example web endpoint that makes a session:
// At app startup. If you have a gocraft/health stream, pass it here instead of nil.
dbrCxn = dbr.NewConnection(db, nil)
func SuggestionsIndex(rw http.ResponseWriter, r *http.Request) {
// Make a session. If you have a gocraft/health job, pass it here instead of nil.
dbrSess := connection.NewSession(nil)
// Do queries with the session:
var sugg Suggestion
err := dbrSess.Select("id, title").From("suggestions").
Where("id = ?", suggestion.Id).LoadStruct(&sugg)
// Render stuff, etc. Nothing else needs to be done with dbr.
}
// Create a new suggestion record
suggestion := &Suggestion{Title: "My Cool Suggestion", State: "open"}
// Insert; inserting a record automatically sets an int64 Id field if present
response, err := dbrSess.InsertInto("suggestions").
Columns("title", "state").Record(suggestion).Exec()
// Update
response, err = dbrSess.Update("suggestions").
Set("title", "My New Title").Where("id = ?", suggestion.Id).Exec()
// Select
var otherSuggestion Suggestion
err = dbrSess.Select("id, title").From("suggestions").
Where("id = ?", suggestion.Id).LoadStruct(&otherSuggestion)
// Delete
response, err = dbrSess.DeleteFrom("suggestions").
Where("id = ?", otherSuggestion.Id).Limit(1).Exec()
// Load primitives into existing variables
var ids []int64
idCount, err := sess.Select("id").From("suggestions").LoadValues(&ids)
var titles []string
titleCount, err := sess.Select("title").From("suggestions").LoadValues(&titles)
// Or return them directly
ids, err = sess.Select("id").From("suggestions").ReturnInt64s()
titles, err = sess.Select("title").From("suggestions").ReturnStrings()
// 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
}
// Columns are mapped to fields breadth-first
type Suggestion struct {
Id int64
Title string
User *struct {
Id int64 `db:"user_id"`
}
}
var suggestion Suggestion
err := dbrSess.Select("id, title, user_id").From("suggestions").
Limit(1).LoadStruct(&suggestion)
// dbr.Null* types serialize to JSON like you want
suggestion := &Suggestion{Id: 1, Title: "Test Title"}
jsonBytes, err := json.Marshal(&suggestion)
fmt.Println(string(jsonBytes)) // {"id":1,"title":"Test Title","created_at":null}
// Start bulding an INSERT statement
createDevsBuilder := sess.InsertInto("developers").
Columns("name", "language", "employee_number")
// Add some new developers
for i := 0; i < 3; i++ {
createDevsBuilder.Record(&Dev{Name: "Gopher", Language: "Go", EmployeeNumber: i})
}
// Execute statment
_, err := createDevsBuilder.Exec()
if err != nil {
log.Fatalln("Error creating developers", err)
}
// Update any rubyists to gophers
response, err := sess.Update("developers").
Set("name", "Gopher").
Set("language", "Go").
Where("language = ?", "Ruby").Exec()
// Alternatively use a map of attributes to update
attrsMap := map[string]interface{}{"name": "Gopher", "language": "Go"}
response, err := sess.Update("developers").
SetMap(attrsMap).Where("language = ?", "Ruby").Exec()
// Start txn
tx, err := c.Dbr.Begin()
if err != nil {
return err
}
// Rollback unless we're successful. You can also manually call tx.Rollback() if you'd like.
defer tx.RollbackUnlessCommitted()
// Issue statements that might cause errors
res, err := tx.Update("suggestions").Set("state", "deleted").Where("deleted_at IS NOT NULL").Exec()
if err != nil {
return err
}
// Commit the transaction
if err := tx.Commit(); err != nil {
return err
}
// Create builder
builder := dbrSess.Select("*").From("suggestions").Where("subdomain_id = ?", 1)
// Get builder's SQL and arguments
sql, args := builder.ToSql()
fmt.Println(sql) // SELECT * FROM suggestions WHERE (subdomain_id = ?)
fmt.Println(args) // [1]
// Use raw database/sql for actual query
rows, err := db.Query(sql, args...)
if err != nil {
log.Fatalln(err)
}
// Alternatively you can build the full query
query, err := dbr.Interpolate(builder.ToSql())
if err != nil {
log.Fatalln(err)
}
fmt.Println(query) // SELECT * FROM suggestions WHERE (subdomain_id = 1)
gocraft offers a toolkit for building web apps. Currently these packages are available:
- gocraft/web - Go Router + Middleware. Your Contexts.
- gocraft/dbr - Additions to Go's database/sql for super fast performance and convenience.
- gocraft/health - Instrument your web apps with logging and metrics.
These packages were developed by the engineering team at UserVoice and currently power much of its infrastructure and tech stack.
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
- Tyler Smith -- https://github.com/tyler-smith
- Sponsored by UserVoice