named parameters in database/sql and database/sql/driver

Question

I'm trying to figure out what the pattern is for using named parameters in go's built-in database/sql package. I looked at the oracle driver, but it seems like just a wrapper for the C library. Have people solved this in an elegant way? So far I've just worked around the problem by putting {0}, {1} as the parameters in the unit tests, but it sure would be nice to be able to use them normally as a map[string]interface{} or something. Does anyone have an idea or an implementation that seems idiomatic?

For reference, here is a test:

db := testConn()
stmt, err := db.Prepare("return {0} as int1, {1} as int2")
if err != nil {
   t.Fatal(err)
}
rows, err := stmt.Query(123, 456)
if err != nil {
   t.Fatal(err)
}
rows.Next()

var test int
var test2 int
err = rows.Scan(&test, &test2)
if err != nil {
   t.Fatal(err)
}
if test != 123 {
   t.Fatal("test != 123;", test)
}
if test2 != 456 {
   t.Fatal("test2 != 456;", test2)
}

And what I'm doing in Query is:

func (stmt *cypherStmt) Query(args []driver.Value) (driver.Rows, error) {
   cyphReq := cypherRequest{
      Query: stmt.query,
   }
   if len(args) > 0 {
      cyphReq.Params = make(map[string]interface{})
   }
   for idx, e := range args {
      cyphReq.Params[strconv.Itoa(idx)] = e
   }
...

Answer 1

I'm using wrapper on top of database/sql called sqlx https://github.com/jmoiron/sqlx You can check here how he did it.

Example on how to select into a tuple

type Person struct {
    FirstName string `db:"first_name"`
    LastName  string `db:"last_name"`
    Email     string
}
jason = Person{}
err = db.Get(&jason, "SELECT * FROM person WHERE first_name=$1", "Jason")
fmt.Printf("%#v\n", jason)
// Person{FirstName:"Jason", LastName:"Moiron", Email:"[email protected]"}

Example on how to insert a tuple

dude := Person{
    FirstName:"Jason", 
    LastName:"Moiron", 
    Email:"[email protected]"
}
 _, err = db.NamedExec(`INSERT INTO person (first_name,last_name,email) VALUES (:first,:last,:email)`, dude)

Answer 2

As far as I know, no driver natively provides for named parameters. I personally use gorp which allows you to bind queries from structs or maps:

_, err = dbm.Select(&users,
    "select * from PersistentUser where mykey = :Key",
    map[string]interface{}{
        "Key": 43,
    }
)

or

_, err = dbm.Select(&users,
    "select * from PersistentUser where mykey = :Key",
    User{Key: 43},
)

Answer 3

It would be possible to create a map[string]interface{} type that implements driver.Valuer{} to serialize it as a []byte, and then convert it back in the driver.

But that would be inefficient and unidiomatic. Since your driver would then be used in a nonstandard way anyway, it would probably be better to just forget about database/sql and write a package with a totally custom interface.