Additions to Go's database/sql for super fast performance and convenience.

Related tags

Database tools dbr
Overview

gocraft/dbr (database records)

GoDoc FOSSA Status Go Report Card CircleCI

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 sessions
sess.Begin()

Create and use Tx

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

// do stuff...

tx.Commit()

SelectStmt loads data into structs

// columns are mapped by tag then by field
type Suggestion struct {
	ID	int64		// id, will be autoloaded by last insert id
	Title	NullString	`db:"subject"`	// subjects are called titles now
	Url	string		`db:"-"`	// ignored
	secret	string		// 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.
var suggestions []Suggestion
sess := mysqlSession
sess.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 := mysqlSession
ids := []int64{1, 2, 3, 4, 5}
sess.Select("*").From("suggestions").Where("id IN ?", ids)

SelectStmt with joins

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

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

// join multiple tables
sess.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

type Suggestion struct {
	ID		int64
	Title		NullString
	CreatedAt	time.Time
}
sugg := &Suggestion{
	Title:		NewNullString("Gopher"),
	CreatedAt:	time.Now(),
}
sess := mysqlSession
sess.InsertInto("suggestions").
	Columns("title").
	Record(&sugg).
	Exec()

// id is set automatically
fmt.Println(sugg.ID)

InsertStmt adds data from value

sess := mysqlSession
sess.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:

Contributors:

License

FOSSA Status

Issues
  • Postgres support

    Postgres support

    dbr looks very interesting, and I'd love if it supported Postgres.

    My general plan was to run the test suite with DBR_TEST_DRIVER="postgres" and add the github.com/lib/pq import to dbr_test.go and work through until the tests pass. Do you feel that's a good approach? How comprehensive do you feel the tests are?

    Right off the bat I am hitting SQL syntax problems in the installFixtures() function in dbr_test.go. Looking through the codebase it looks like it was mostly designed with a single driver in mind. How would you like to split out the driver-specific parts of the codebase?

    opened by joeshaw 27
  • Postgres last insert id

    Postgres last insert id

    There does not seem to be a way of getting the record id of the last inserted record(s).

    The correct way would be to append "RETURNING id" to the query, and then scan the ids, however, I don't see how that's possible currently.

    Also, when using InsertBuilder.Record, an id field is only selected when its type is int64, why not allow any kind of integers?

    enhancement help wanted 
    opened by noonien 12
  • Some tweaks to the package interface

    Some tweaks to the package interface

    Hello, thank you for this library. It was the first library that suited me (except some parts). I decided to fork this library and tweak it to fit my needs. I have just released it but it is a work in progress: https://github.com/mibk/ql.

    I have forked it because I wanted to break the API of the dbr package. But now, I was wondering, whether some of the changes might interest you (especially Query method and type, quoting of columns using [col_name], All and One methods, and String methods for builders). In the README there are the main differences from your package. Other changes could be read from commit log. If you like some of it, I could prepare a pull request.

    In ideal it would be nice if we had the same goal and we could just merged it all. But it whould probably have to be some dbr 2 as the BC break wouldn't be pleasant for the current users.

    opened by mibk 12
  • UNION and SELECT...AS Syntax Errors

    UNION and SELECT...AS Syntax Errors

    I am constructing a complex query that uses a WHERE col IN ? clause that uses a UNION of SELECTs. The builder is inserting an additional set of parentheses which is causing a syntax error.

    Here is the simplified code I'm using to construct the query. The Dialect is SQLite3.

    stmt := sess.Select("*").From("entries").Limit(uint64(limit)).Offset(uint64(page*limit) + 1)
    addressIDStmt := dbr.Select("id").From("addresses").Where("rcd_hash = ?", rcdHash)
    
    entriesStmt := dbr.UnionAll(
            dbr.Select("entry_id").From("address_transactions_to").Where("address_id == ?", addressIDStmt),
            dbr.Select("entry_id").From("address_transactions_from").Where("address_id == ?", addressIDStmt))
    
    stmt = stmt.Where("id IN ?", entriesStmt)
    

    The error I get is near "UNION": syntax error. The constructed query is:

    SELECT * FROM entries WHERE 
            (id IN 
                    (   --- EXTRA PARENTHESIS, SYNTAX ERROR
                            (SELECT entry_id FROM address_transactions_to WHERE (address_id == (SELECT id FROM addresses WHERE (rcd_hash = X'6a0c800d303c0260dd998e6609f89909974d9576ec72d070f9ec7aa493e84884')))) 
                            UNION ALL 
                            (SELECT entry_id FROM address_transactions_from WHERE (address_id == (SELECT id FROM addresses WHERE (rcd_hash = X'6a0c800d303c0260dd998e6609f89909974d9576ec72d070f9ec7aa493e84884'))))
                    )   --- EXTRA PARENTHESIS, SYNTAX ERROR
            )
    LIMIT 25 OFFSET 1
    

    I have indented the query to clearly show the extra set of parentheses.

    opened by AdamSLevy 10
  • Field not automatically set after insert

    Field not automatically set after insert

    The documentation says:

    type Suggestion struct {
    	ID		int64
    	Title		NullString
    	CreatedAt	time.Time
    }
    sugg := &Suggestion{
    	Title:		NewNullString("Gopher"),
    	CreatedAt:	time.Now(),
    }
    sess := mysqlSession
    sess.InsertInto("suggestions").
    	Columns("id", "title").
    	Record(&sugg).
    	Exec()
    
    // id is set automatically
    fmt.Println(sugg.ID)
    

    I am using Postgres and have this:

                                 Table "public.users"                                            
      Column  |          Type          | Collation | Nullable |      Default                     
    ----------+------------------------+-----------+----------+-------------------               
     id       | uuid                   |           | not null | gen_random_uuid()                
     username | character varying(255) |           | not null |                                  
     password | chkpass                |           | not null |                                  
    Indexes:
        "users_pkey" PRIMARY KEY, btree (id)
        "unique_username" UNIQUE CONSTRAINT, btree (username) 
    

    With code:

    type User struct {
    	Id       dbr.NullString
    	Username string
    	Password string
    }
    
    	newUser := &User{
    		Username: "testuser",
    		Password: "P4ssword!!",
    	}
    	_, err = sess.InsertInto("users").
    		Columns("username", "password").
    		Record(newUser).
    		Exec()
    
    	if err != nil {
    		log.Fatal(err)
    	}
    
    	fmt.Printf("%+v\n", newUser.Id)
    

    And I the output:

    ↳ go run *.go
    {NullString:{String: Valid:false}}
    

    I thought maybe this was because the example shows passing the address of the newUser pointer to Record(), but that doesn't work:

    ↳ go run *.go
    2018/11/13 20:35:55 pq: syntax error at end of input
    exit status 1
    
    wontfix 
    opened by GrooveStomp 10
  • dbr.InsertInto struct fields auto mapping

    dbr.InsertInto struct fields auto mapping

    some db library have auto mapping of the struct field's db table name so something like

    type A struct {
      Id int
      Name string
      LongField string
    }
    

    will simply map to Columns("id", "name", "long_field") so this way its much less verbose compared to manually adding the field name into the Columns() and changing the struct requires the function to be updated as well.

    enhancement 
    opened by badoet 10
  • refactor opentracing into optional interface

    refactor opentracing into optional interface

    Refactored opentracing support into an optional interface so that dbr doesn't have to import opentracing directly.

    There is also an embeddable implementation of this optional interface in dbr/opentracing. I can remove this (or put it in the readme as an example) if you'd rather not have a subpackage for it.

    I suspect changes will be needed before this can be merged. I'll be happy to modify as requested 🙂

    RE: #152

    opened by mccolljr 9
  • InsertStmt: automatically add insert columns when using InsertStmt.Record()

    InsertStmt: automatically add insert columns when using InsertStmt.Record()

    This change automatically adds the insert columns when using InsertStmt.Record() if they are not set by calling Columns().

    Calling Columns() afterwards still maintains the same behaviour as it overwrites the Column field.

    Since there is no API incompatibility, this change was made on the existing Record() function.

    wontfix 
    opened by brunotm 8
  • Fix ability to pass TracingEventReceiver to dbr

    Fix ability to pass TracingEventReceiver to dbr

    After trying out #153 I discovered it doesn't work — this is because composition is used to add EventReceiver to various structs, and so it loses track of the original EventReceiver implementation when passing it around, and then the type assertion for TracingEventReceiver in dbr.go does not work. This adds some failing test cases asserting that tracing works, then fixes them.

    A bigger change would be to name the EventReceiver rather than using an anonymous embedding, but I didn't want to make such a big change.

    opened by cce 8
  • Thoughts on improving the health of this repo.

    Thoughts on improving the health of this repo.

    I have, at least at this time, dropped DBR from as many application as I can. This library was great for a long time, but there have been a few incidents (suddenly adding a massive OpenTracing dependency, aggressive issue closing, an apparent lack of support) that have made me turn to other solutions.

    I think a good step to revitalizing this library would be to give more rights to the community and contributors, as it seems like you yourself are distracted with other things (such is life, it is totally understandable). Removing the Stale Bot and allowing issues to stay open is a good thing. Having issues on your repo is not a negative reflection on you. It means people are using your software in new and interesting ways. Leaving issues open is also useful for a number of reasons. They can serve as a place for contributors to discuss how to implement certain features. They can let users of the project note when they're experiencing the same issue as someone else and discuss workarounds/problems etc. I've never seen another project aside from the Go language itself (which has thousands and thousands of issues) aggressively close issues like this repo does. @AdamSLevy has also noted that this is a non-standard approach running a repo in another issue.

    Perhaps a change to only add a stale label, rather than closing the issues? I understand that this is not a paid gig for you, and I can appreciate the time commitment of OSS, but in order for dbr to be viable for me again, I would need to see a more engaged maintainer. Is there someone you can share maintainer rights with who has more time to commit to this project?

    wontfix 
    opened by mccolljr 7
  • Adding support for Postgres RETURNING statment

    Adding support for Postgres RETURNING statment

    I know this is DB specific but it is very useful. I could not figure out how to determine the dialect. Can someone help with that? I'd like to throw a error is dialect != postgres. Worse case the underlying DB will throw an error.

    Thanks!

    opened by dougEfresh 7
Releases(v2.7.3)
Parses a file and associate SQL queries to a map. Useful for separating SQL from code logic

goyesql This package is based on nleof/goyesql but is not compatible with it any more. This package introduces support for arbitrary tag types and cha

null 0 Oct 20, 2021
write APIs using direct SQL queries with no hassle, let's rethink about SQL

SQLer SQL-er is a tiny portable server enables you to write APIs using SQL query to be executed when anyone hits it, also it enables you to define val

Mohammed Al Ashaal 2k Jun 24, 2022
Go-sql-reader - Go utility to read the externalised sql with predefined tags

go-sql-reader go utility to read the externalised sql with predefined tags Usage

null 0 Jan 25, 2022
Scan database/sql rows directly to structs, slices, and primitive types

Scan Scan standard lib database rows directly to structs or slices. For the most comprehensive and up-to-date docs see the godoc Examples Multiple Row

Brett Jones 273 Jun 22, 2022
A go Library for scan database/sql rows to struct、slice、other types. And it support multiple databases connection management

ploto A go Library for scan database/sql rows to struct、slice、other types. And it support multiple databases connection management It's not an ORM. wo

solar 2 Mar 16, 2022
Lightweight SQL database written in Go for prototyping and playing with text (CSV, JSON) data

gopicosql Lightweight SQL database written in Go for prototyping and playing wit

null 2 May 17, 2022
sqlx is a library which provides a set of extensions on go's standard database/sql library

sqlx is a library which provides a set of extensions on go's standard database/sql library. The sqlx versions of sql.DB, sql.TX, sql.Stmt, et al. all leave the underlying interfaces untouched, so that their interfaces are a superset on the standard ones. This makes it relatively painless to integrate existing codebases using database/sql with sqlx.

Jason Moiron 12.2k Jun 26, 2022
Interceptors for database/sql

sqlmw sqlmw provides an absurdly simple API that allows a caller to wrap a database/sql driver with middleware. This provides an abstraction similar t

null 407 Jun 23, 2022
OpenTelemetry instrumentation for database/sql

otelsql It is an OpenTelemetry instrumentation for Golang database/sql, a port from https://github.com/open-telemetry/opentelemetry-go-contrib/pull/50

Sam Xie 92 Jun 18, 2022
Prometheus metrics for Go database/sql via VictoriaMetrics/metrics

sqlmetrics Prometheus metrics for Go database/sql via VictoriaMetrics/metrics Features Simple API. Easy to integrate. Install Go version 1.16+ go get

cristaltech 21 Jan 22, 2022
Attach hooks to any database/sql driver

sqlhooks Attach hooks to any database/sql driver. The purpose of sqlhooks is to provide a way to instrument your sql statements, making really easy to

Gustavo Chaín 551 Jun 23, 2022
Go database/sql

Go database/sql 源码阅读 基于 release-branch.go1.17 Benchmark 连接池测试 简单介绍 database/sql 库,包括结构体和主要的方法 介绍主要函数的调用逻辑 用OneNote看源码:Link 介绍最近几个版本的commit changes 结合实

x509 0 Dec 18, 2021
Sqlbench runs benchmarks on an SQL database

sqlbench runs benchmarks on an SQL database. Right now this works for PostgreSQL

Martin Tournoij 1 Dec 20, 2021
BigQuery database/sql golang driver

BigQuery SQL Driver This library is compatible with Go 1.17+ Please refer to CHA

Viant, Inc 5 Jun 7, 2022
A minimal, single-table No-SQL database.

SimpleDB SimpleDB is a very basic No-SQL database format for long-term data storage in Golang. It is WIP, has a LOT of drawbacks, and definitely is no

Konnor Klashinsky 0 Jan 16, 2022
Otelsql - OpenTelemetry SQL database driver wrapper for Go

OpenTelemetry SQL database driver wrapper for Go Add a OpenTelemetry wrapper to

Nhat 24 Jun 15, 2022
A go package to add support for data at rest encryption if you are using the database/sql.

go-lockset A go package to add support for data at rest encryption if you are using the database/sql to access your database. Installation In your Gol

Bartlomiej Mika 0 Jan 30, 2022
Package dbi implements an experimental database/sql wrapper.

dbi Package dbi implements a database/sql wrapper. This is an EXPERIMENTAL package used for experimenting. Installation The recommended way to install

null 0 Feb 8, 2022
Google Cloud Spanner driver for Go's database/sql package.

go-sql-spanner Google Cloud Spanner driver for Go's database/sql package. This support is currently in the Preview release status. import _ "github.co

Google APIs 48 May 30, 2022