I've poked around a lot in the database/sql package while writing modl and sqlx, and I try to answer as many sql related questions as I can in #go-nuts. Here are some questions I've seen about the Go's sql package:

How can I scan into a struct?

You can't.

Quite a few third party libraries do, however, provide this. The tradeoff inherent in these libraries is that they all must use reflect and rows.Columns() to figure out a way to match up struct fields with database columns. Reflect only has access to visible struct fields, so your fields must all be Capitalized. Here is how you would do such a thing with sqlx:

import (
    _ "driver"
    "database/sql"
    "github.com/jmoiron/sqlx"
)

type User struct {
    Id int
    Name string
}

func main() {
    db := sqlx.Connect(...)
    user := User{}
    rows, _ := db.Queryx("SELECT id, name FROM users;")
    for rows.Next() {
        rows.StructScan(&user)
    }
}

The nature of Scan (and sql's convertAssign method) means that reflect is already being used, but there is a small amount of extra work that must be done per iteration to build a slice of field addresses for the underlying Scan method. In practice, the difference in speed is minimal.

How can I use named query parameters?

Once again, you can't out of the box, but sqlx provides this. Because normal parameter bindvars are driver specific, and sql.DB hides the driverName after your call to db.Open(), this is much more difficult to achieve and is much less commonly implemented. Sqlx currently supports this for MySQL, SQLite, and PostgreSQL. Contributions are welcome.

// db is an *sqlx.DB
rene := User{1, "Rene Ranger"}
rows, _ := db.NamedQuery("SELECT * FROM user WHERE name=:name;", rene)

There is also NamedExec and variants which will work with maps rather than structs.

Why am I running out of database connections?

Note Issue #4805 hass been accepted and merged into Go 1.2 since this writing, and you can now limit the size of sql's connection pool with db.SetMaxOpenConns. The discussion on the ways that connections are created is still valid and preserved below.

The connection pooling implemented by database/sql does not prevent it from making an infinite number of connections to your database. There is an open issue #4805 discussing possible solutions to this, but it didn't make 1.1 and there are no proposed patches which look like being accepted.

The only direct control you have over the number of connections in the pool is db.SetMaxIdleConns(). The default number of idle connections is 2. If a connection has been created and is then released while the size of the pool is at maxIdleConns, it is closed.

Most "too many connections" problems stem from misunderstanding the way the connection pool is used and how connection ownership is passed around, so lets focus on that first. Functions that require talking to the database (Ping, Exec, Query,Begin, etc) all start by asking your DB object for a connection. If there is an idle connection in the pool, that is returned; otherwise, a new connection is created. When that connection is no longer needed by that operation, it is returned to the pool.

Ping and Exec will release the connection right before returning, but the others will pass ownership of the connection to the result, whether that's an sql.Row, sql.Rows, or sql.Tx.

This code will create two connections:

r1, _ := db.Query("SELECT * FROM foo")
r2, _ := db.Query("SELECT * FROM bar")

Since the second Query is called before the first connection is released by r1, a new connection is made. An sql.Row returns the connection when Scan() is called, sql.Rows returns either when Close() is called or all rows have been iterated over with Next(), and sql.Tx will return when Commit or Rollback() are called. If you forget to completely iterate an sql.Rows and you forget to Close it, that connection will never go back to the pool.

It's worth noting here that, were a max conns implemented in the database's pool and set to 1, there are two likely behaviors for the second query: db.Query could block (forever), waiting for open connections, or an error could be returned immediately. Neither solution would ultimately prevent deadlocks, so it's best to avoid this type of code if possible.

Transactions, as explained in the documentation, are bound to a single connection. Ensure you do things serially within a transaction. The second query here will fail:

tx, _ := db.Begin()
r1, _ = tx.Query("SELECT * FROM foo")
// Second query on the same open connection
r2, _ = tx.Query("SELECT * FROM bar")

If you forget to Close() a successful query in a Transaction and then Rollback or Commit it, its connection is returned to the pool in a potentially dirty state. Subsequent queries on that connection might fail. You should always close anything with a .Close() method as early as possible, and if you want to have N queries running at once you'd better expect to be using N connections.

Now that you have some idea how connections are created, you probably already know more or less how to limit them.

The best practice currently is to be fastidious in releasing connections back to the pool in each goroutine and to limit access to db objects by using the classic techniques of resource access control described countless times elsewhere. The following demonstrates the basic idea:

var sem chan int

type Pool struct {
    sql.DB
}

func NewPool(db *sql.DB, cap int) *Pool {
    sem = make(chan int, cap)
    for i:=0; i<cap; i++ {
        sem <- 1
    }
    return &Pool{*db}
}

func (p *Pool) Query(q string, args ...interface{}) (*sql.Rows, error) {
    <-sem
    defer func() {
        sem<-1
    }()
    return p.DB.Query(q, args...)
}

I found all these ORMs on Github, which should I use?

You should use gorp.

If you are used to ActiveRecord style ORMs, one of these doesn't exist yet for Go, and limitations in the language make developing one very difficult. The R in ORM is generally the difficult part. Notably, automatically handling joins as fields in a contained struct can't be done without a great deal of effort, and lazy loading them on access is impossible to do in a generic way.

Gorp is well tested, reasonably well designed, and does about as much as it can do without becoming very complex. I wish I could recommend modl, which is my own fork of gorp that is built atop sqlx, but it is not ready yet.

Jun 21 2013