database/sql/v2: what would a Go 2 version of database/sql look like?

[bradfitz]

We're wondering what the story for database/sql might be for Go 2. We agree it's clearly important to be maintained (including ongoing maintenance including but definitely not limited to Google support).

Whether the package lives in std or x/foo is out of scope for this bug. (We'll wait until package management is figured out more first.)

But given what we know now, what would a fresh database/sql look like for Go 2?

Experience reports, docs, API proposals welcome.

@kardianos, could you write up your wishlist?

[kardianos]

database/sql Go2 wishlist:

• Remove non-context methods.
• Add Context to DB.Close.
• Have a way to pass back a user error and notify the connection pool the connection is bad as well. Differentiate between a bad connection that didn't execute and a bad connection that may have executed and should pass execution back to the client and not retry. It may also be useful to note if the error was caused by a ctx cancel database/sql: if a context is cancelled, then pass back that error #25829.
• Add hooks to allow connection pool to inform clients when a connection is closed or bad and will removed from pool.
• Remove Stmt as implemented.
• Add a separate implementation of a Stmt pool that uses the above hooks.
• Retain Conn.Prepare, remove other instances of Prepare.
• Remove runtime knobs, replace with conn pool create parameters (or remove the knob altogether).
• Increase observability of connection pool (tracing / metrics).
• Remove deprecated driver interfaces.
• Have all drivers use a driver.Connector (useful for creating a dedicated connection canceler for certain systems, parse DSN only once, and other benefits). Think about making a DSN string parsing optional.
• Possibly allow the driver.Conn.Close to take a context. database/sql: no way to protect driver.Connector.Connect(), driver.Conn.Close(), driver.Stmt.Close() from blocking #38185
• Allow drivers to export a per result-set value converter that can be used by downstream ORMs in conjunction with the built-in value converter.

database/sql suggested exploration topics prior to Go2:

• Determine best way to scan rows from a cursor from value or OUT param.
• Look into a more flexible way to get data out of driver rows:
  • Support zero alloc "scans".
  • Use a more generic interface for scanning, allows user to provide method of scan. (?)
  • Standardize generic table buffer. (?)
• Investigate experiments: https://godoc.org/github.com/golang-sql/sqlexp
  • Value quoter
  • Return raw messages and notices
  • Savepoints
  • Nested transactions
• Better support for Decimal, Date, DateTime, and Time types.
  • https://godoc.org/cloud.google.com/go/civil
  • https://godoc.org/github.com/cockroachdb/apd

Experience Reports

• Go and SQLite: when database/sql chafes

[bronze1man]

• Be more efficient, the interface should make zero alloc implement possible. I found that direct use github.com/go-sql-driver/mysql (and make some modifies to make it to make it zero alloc and it is zero alloc indeed after the modify) without the database/sql interface stuff, is 20% faster with the localhost mysql server with small dataset. Here is what to be done to make it zero alloc:

  • Add A special []byte type version Db.Query/Db.Exec/Stmt.Query/Stmt.Exec/Rows.Scans method to avoid interface alloc/slice alloc on every Query/Exec method call. The database/sql should provide a way that the input and output data set do not need type conversion to avoid alloc in strconv.Itoa or time.Parse. Just copy that input []byte direct to database network connection and take part of the inner output []byte buffer to the output [][]byte slice should make zero alloc possible.
  • Add Rows.ColumnLen() int to avoid slice alloc on every Query method call.
  • Add struct type sql.Result to avoid interface alloc on every Exec method call. It should look like type Result struct {; AffectedRows int64; InsertId int64} https://github.com/go-sql-driver/mysql/blob/master/result.go#L11
  • Bind and reuse a *sql.Rows on a *sql.driverConn to avoid alloc *sql.Rows on every Query method call.

• Make database/sql.Rows easier to understand and to be called. It should more like database/sql/driver.Rows.

• database/sql.Rows can have a callback foreach method like func (rows *Rows) Foreach(cb func(rowList [][]byte)error) error to make the interface much more easier to use.

• The database/sql.Open should be deleted. How do i pass a custom net.Dialer to the driver? How do I pass a callback to the driver? How do i manually marshal dataSourceName? How do i escape some chars in the url? Should I use json in dataSourceName? How to create a db object should be managed by driver implement to make the interface more easy to use.(more struct stuff, less string/marshal/unmarshal stuff)

• Add the MaxConnectionIdleTime Option. Db.SetConnMaxLifetime is useless, SetConnMaxLifetime just set the max time of a connection from the startTime.I think the max time of a connection from last Idle, should be a good solution to gc the useless tcp connection after a huge traffic.

• Delete Db.SetConnMaxLifetime,Db.SetMaxIdleConns,Db.SetMaxOpenConns. they should be set at the begin of the database object created. I do not have any need to change the database object config after the object created. If you want another config of the database object, just close old one and create a new one.

• Add a *sql.Stmt Pool so that the caller do not need implement one again.

About sql.Stmt

• I need *sql.Stmt to avoid sql injection attack and make the input parameter escape absolute right.If other stuff can be easier, safe, correct and fast.I will choose the easier one.
• Cache *sql.Stmt can increase the performance of mysql. But store every queries as *sql.Stmt will reach the max number Statments(may be 16k) of mysql server.So I only use them with point select/replace/delete and gc the *sql.Stmt Pool if the pool is too large. The biggest problem of *sql.Stmt Pool is that I do not know the real Statments usage. I can only know the *sql.Stmt number.

[bronze1man]

"Some database servers have limits on how many prepared statements can be prepared at once."
Here is a config in mysql called max_prepared_stmt_count. it is 16382 by default. And the prepared_stmt is in connection scope.If I only prepared one statement, it may cost 200 of the max count when I use 200 connections.
https://dev.mysql.com/doc/refman/5.7/en/sql-syntax-prepared-statements.html

I need gc the *sql.Stmt Pool and prepare last query again, if mysql return an error says there is too many stmt. (Error 1461: Can't create more than max_prepared_stmt_count statements (current value: 16382))

Here is the *sql.Stmt pool code I currently use.

func IsErrorMsgTooManyPreparedStmt(errMsg string) bool{
	// Error 1461: Can't create more than max_prepared_stmt_count statements (current value: 16382)
	return strings.Contains(errMsg,"Error 1461: Can't create more than max_prepared_stmt_count statements")
}

type Ci struct{
	Query           func(query string, args ...interface{}) (*sql.Rows, error)
	Exec            func(query string, args ...interface{}) (sql.Result, error)
	Prepare         func(query string) (*sql.Stmt, error)
	db              *Db
	IsInTransaction bool

	stmtCacheMap    map[string]*stmtCacheEntry
	lastStmtCacheTime uint64
	stmtCacheLocker sync.Mutex
}

type stmtCacheEntry struct{
	stmt *sql.Stmt
	key string
	lastUseTime uint64
}

func (ci *Ci) cleanPrepareCache(){
	ci.stmtCacheLocker.Lock()
	ci.stmtCacheMap = map[string]*stmtCacheEntry{}
	ci.stmtCacheLocker.Unlock()
}

func (ci *Ci) prepareWithCache(query string) (stmt *sql.Stmt, err error){
	ci.stmtCacheLocker.Lock()
	ci.lastStmtCacheTime++
	thisStmtCacheTime:=ci.lastStmtCacheTime
	if ci.stmtCacheMap==nil{
		ci.stmtCacheMap = map[string]*stmtCacheEntry{}
	}
	entry:= ci.stmtCacheMap[query]
	// 已经有缓存，直接使用。
	if entry!=nil{
		entry.lastUseTime = thisStmtCacheTime
		ci.stmtCacheLocker.Unlock()
		return entry.stmt,nil
	}
	ci.stmtCacheLocker.Unlock()
	for i:=0;i<3;i++{
		stmt,err=ci.Prepare(query)
		if err!=nil{
			if IsErrorMsgTooManyPreparedStmt(err.Error()){
				// 可能客户端实例太多，此处遇见太多stmt的报错后，就关闭自己的一般stmt，以便后面的程序可以继续进行。
				// 由于每一个连接会把用到过的 stmt 都创建一遍。所以即使把 stmt限制设置的很小。也依然会 报这个问题。
				ci.stmtCacheLocker.Lock()
				ci.gcNotUsePrepare__NOLOCK(len(ci.stmtCacheMap)/2)
				ci.stmtCacheLocker.Unlock()
				continue
			}
			return nil,err
		}
	}
	if err!=nil{
		return nil,err
	}
	ci.stmtCacheLocker.Lock()
	oldEntry:=ci.stmtCacheMap[query]
	if oldEntry==nil{
		if len(ci.stmtCacheMap)>= ci.db.req.StmtCacheMaxQueryNum{
			ci.gcNotUsePrepare__NOLOCK(ci.db.req.StmtCacheMaxQueryNum/2)
		}
		entry = &stmtCacheEntry{
			stmt: stmt,
			key: query,
			lastUseTime: thisStmtCacheTime,
		}
		ci.stmtCacheMap[query] = entry
		ci.stmtCacheLocker.Unlock()
		return entry.stmt,nil
	}else{
		// 可能另一个线程已经初始化好了，此处直接使用。
		ci.stmtCacheLocker.Unlock()
		stmt.Close()
		entry = oldEntry
		entry.lastUseTime = thisStmtCacheTime
		return entry.stmt,nil
	}
}

func (ci *Ci) gcNotUsePrepare__NOLOCK(toNumber int){
	currentNumber:=len(ci.stmtCacheMap)
	if currentNumber==0 || currentNumber<=toNumber{
		return
	}
	stmtCacheList:=make([]*stmtCacheEntry, len(ci.stmtCacheMap))
	i:=0
	for _,entry:=range ci.stmtCacheMap{
		stmtCacheList[i] = entry
		i++
	}
	// 时间越早越放在后面。
	kmgSort.InterfaceCallbackSortWithIndexLess(stmtCacheList,func(i int,j int)bool{
		return stmtCacheList[i].lastUseTime>stmtCacheList[j].lastUseTime
	})
	for _,entry:=range stmtCacheList[toNumber:]{
		entry.stmt.Close()
		delete(ci.stmtCacheMap,entry.key)
	}
}

• The only reason I cache a *sql.Stmt is performance. It can increase performance if the query string is same. So I never use Stmt inside a transaction. It is too complex and almost no performance gain (the query inside one transaction do not repeat in my use case) for me. By the way, I do not even know why the prepare stuff exist, the mysql server should do the query parse cache itself and make query string and input parameters in the same round trip or just let me pass the sql ast to mysql server(marshal a sql string is just difficult and unsafe.).
• Looks like I just merge DB.Query/Db.Exec and Tx.Query/Tx.Exec into one interface, And write some generic logic on top of them. It will be better if database/sql just merge DB.Query/Tx.Query into one method on some struct so the caller do not need implement it again.

[gale93]

Scan query results directly to a struct

[cznic]

Scan query results directly to a struct

Doesn't work in the general case with NULL values.

[kardianos]

@cznic RE struct NULLs: Yes, we would need some type of strategy for handling that, but I could envision the actual struct scan code provided by a user settable interface, so rather then trying to cover all scanning cases, we provide one or two default ones, but then allows users to provide their own specific one as well.

[kevinburke]

It seems like database/sql has had a lot of issues over the past few years around data races and goroutine leaks, more so than the other standard library packages. I worry that there's something fundamental in the design of the package as it's currently constructed that makes it difficult to write correct code. Maybe it's just that we're asking it to do a lot and support a lot of different use cases and backends. But I am wondering if the design or API's could be altered to make it easier to write a correct implementation.

I have long wished for some type of unified, cross-database API for reporting the constraint failures that are features of most databases:

• Not null constraint failure
• Uniqueness constraint failure
• Foreign key reference failure
• Check constraint failure
• Enum not known
• Type failure (inserted a number where a UUID was expected, or whatever)

The fact that each database reports these differently makes it difficult for e.g. ORM's to properly handle constraint failures. Instead of just issuing an INSERT and waiting for the constraint failure, Rails starts a transaction, issues a SELECT, checks the number of rows, and throws if a row already exists. It would be cool if database/sql just returned you the uniqueness constraint on an INSERT failure instead of an err you had to cast to a pq.Error or whatever else. I tried to get into this a little bit with this talk. https://kev.inburke.com/slides/feral-concurrency

I tried to address this a little bit with https://github.com/shyp/go-dberror, which attempted to wrap the existing database error in a more friendly message. Presumably that could be extended to handle more backends and more classes of errors.

[cznic]

RE struct NULLs: Yes, we would need some type of strategy for handling that

Without knowing any other context about the data model, there's only one safe strategy in this case: refuse to continue scanning and panic.

[kardianos]

@kevinburke Re database/sql reliability: yes, the pool and stmt code are too interconnected with everything else, it would be great to be able to have some of that code in internal sub-packages.
Interesting idea to classify error types (constraint failures). I've done similar things to provide more useful DB error information as well.

[kardianos]

@bronze1man

By the way, I do not even know why the prepare stuff exist, the mysql server should do the query parse cache itself and make query string and input parameters in the same round trip

Indeed. MS SQL Server and Oracle (probably others) don't benefit from a separate prepare step; you send the SQL text and parameters in the same message and will reuse recent cached query plans by matching similar SQL text. Even postgresql can have an anonymous prepared query that can be prepared and executed in a single round trip. MySQL now has the protocol X that probably supports similar. This is why I never personally use *sql.Stmt; it adds a real cost without offering a benefit (for me and the systems I use).

*sql.Stmt also add significant complication to the internal database/sql code base.

[Azareal]

I tried doing it the regular way and with *sql.Stmt. When I benchmarked one of my main joins, *sql.Stmt ended up being 30% faster on MySQL. I'm curious as to what it would be like on this new protocol.