database/sql: common interface for query functions in sql.DB and sql.Tx

[KilledKenny]

Hi,

I'm proposing a interface that implements all functions in sql.DB and sql.Tx that takes a query string as one of its arguments. This could be useful when implementing "dumb" functions that retrieves information but in itself dose not need to be prepared however might be used to verify whether to commit or rollback.

The interface I'm proposing is: (The name can of course be changed)

type QueryAble interface {
    Exec(query string, args ...interface{}) (sql.Result, error)
    Prepare(query string) (*sql.Stmt, error)
    Query(query string, args ...interface{}) (*sql.Rows, error)
    QueryRow(query string, args ...interface{}) *sql.Row
}

This is an example function Get that can be used with or without transactions.

func Get (q Queryable)(int){
    var test int
    res , _ := q.Query("SELECT COUNT(*) FROM table;")
    res.Next()
    res.Scan(&test)
    res.Close()
    return test
}

func GetExample(db *sql.DB){

    //When you just want to retrieve the information, no need for a transaction
    fmt.Printf("Current len of Table %d\n", Get(db))

    //Make a change
    tx, _ :=db.Begin()
    // Add data to table
    if  Get(tx) > 2 {
        fmt.Printf("Table to large: %d robacking to: %d \n", Get(tx) , Get(db))
        tx.Rollback()
    } else {
        fmt.Printf("Table update new len %d\n", Get(tx))
        tx.Commit()
    }
}

[ianlancetaylor]

I'm sorry, I don't entirely understand this proposal. Are you suggesting that we should add the type QueryAble to database/sql? How would that make the package easier to use than it is today?

[KilledKenny]

Yes, I want to add QueryAble to database/sql

The reason is that: I want to be able to create functions that execute SQL query's and retrieve data regardless of if its inside a prepared statement (and preferably only use prepared statements when i really need to). This is not possible without a interface.

To provide a transaction and non transaction solution today you have to do something like this:

func SqlMathWrapper(db *sql.DB , a, b int)(int, error){
    tx, _ := db.Begin()
    defer tx.Commit()
    return SqlMath(tx, a,b)
}

func SqlMath(tx *sql.Tx, a, b int)(num int, err error){
    err = tx.QueryRow("SELECT ? + ?", a, b).Scan(&num)
    return
}

(This dose always use transaction)

Using the interface the code would look like this

func SqlMath(qa *sql.QueryAble, a, b int)(num int, err error){
    err = qa.QueryRow("SELECT ? + ?", a, b).Scan(&num)
    return
}

(This would not use prepared statements if called with a sql.DB object)

[ianlancetaylor]

Just to be clear, you could define the interface yourself, right?

[KilledKenny]

@ianlancetaylor Yes, and that's what I will do until its implemented.

[kardianos]

Another discission at #14468 .

[kardianos]

The API for rollback / savepoint may also factor into this conversation: #7898.

[vwochnik]

I am strongly in favor of the Queryable interface as it's proposed here. At the company I am working for I am running into the same use case where I have a few CRUD functions which don't need to care if it's a sql.DB or sql.Tx. In fact, it's counter intuitive. I want to use these functions both with and without transactions. The responsibility of handling the transactions is outside of the scope of the functions.

[kardianos]

@vwochnik I'm not convinced such an interface needs to live in the std lib.
Here is one implementation: https://godoc.org/github.com/golang-sql/sqlexp#Querier
You could also define your own.

[kPshi]

Absolutely common issue it seems and I have the same case here. Of course we can all write an interface definition on our own but that would be done again and again causing much more work in the end (when summing up each individual time spent or working around that).
Libraries are used to ease up things and to not make everyone repeat the very same work. So the question in my opinion is not whether one is able to write that but the question is if that would make things easier and for how many people.