database/sql driver

[crawshaw]

I think this sqlite package provides enough interesting features that it is worth building a database/sql driver on top of it.

In particular it has better multi-threaded support than the existing drivers, and soon will have different build options by selecting sub-packages. (See the multipkg branch.)

It should be easy enough to put a driver in a package named something like sqlite/driver.

[bvinc]

BTW, I personally think that not having a database/sql driver is a selling point. Sure, you can add it and make it optional, but here are the reasons why I hate it.

• It implements connection pooling. By default, each sqlite query will go to a random connection in the pool, which may be in a different state.
• You lose the ability to set a lot of PRAGMA at proper times in the connection. Or really, you can do them, but they'll work only sometimes depending on the state of the connection in the pool.
• Since you lose your ability to create connections, you have to come up with your own connection string syntax to control sqlite.
• You must use their transactions. If some poor fool accidentally uses "BEGIN" and "COMMIT" for transactions, they'll be going into different connections. You must use database/sql transactions, which causes you to lose the ability to do things like "BEGIN EXCLUSIVE".
• It adds a whole new layer of locks and pools which hurt performance.
• The mattn driver already exists

Check the last question on the mattn driver's FAQ:

https://github.com/mattn/go-sqlite3

If you start using their driver normally, you'll immediately start getting problems with locked databases. They suggest that you use their custom connection string to enable shared cache mode, and then reduce the connection pool to only 1 connection. That's the suggested way of using it apparently.

I honestly don't understand how anyone is using sqlite through database/sql.

[crawshaw]

A database/sql driver certainly should live in a separate package (probably sqlite/driver). I'm not working on it right now because I wouldn't be using it day-to-day, so I wouldn't give it the workout it needs to be high quality.

I do believe connection pooling is possible with sqlite, though not in the default way database/sql does it. This package includes a shared-cache based pool object. You just have to check out a connection to use it. (Because of src-string based caching of prepared statements, it's even safe to assume all of your statements have been prepared across the the pool.)

One of the big problems with that other driver that I wanted to address was lack of support for https://www.sqlite.org/unlock_notify.html, which I believe is important for using the shared cache effectively.

[AdamSLevy]

I'm becoming increasingly interested in implementing this feature and may start taking a crack at it in the next few weeks in my spare time.