Would you be interested in a pool implementation?

[michaellenaghan]

I have a pool implementation that I use when I'm using the SQLite API directly. Would you be interested in adding it to the package?

What I'd propose is that I give you the code here; you could then take it and make it your own, so to speak. (If you'd rather have a pull request we can do that too. I thought this approach might be more efficient, but maybe not.)

The API is about what you'd expect:

func NewPool[T any](min, max int, idleTime time.Duration, newFunc func() (T, error), destroyFunc func(T)) (*Pool[T], error)
func (p *Pool[T]) Start() error
func (p *Pool[T]) Stop() 
func (p *Pool[T]) Get(ctx context.Context) (T, error)
func (p *Pool[T]) Put(object T)
func (p *Pool[T]) Stats() Stats

Here's a somewhat stripped down example of its use, also about what you'd expect:

	pool, err := NewPool(
		db.MinConnections,
		db.MaxConnections,
		db.MaxConnectionIdleTime,
		func() (*Conn, error) {
			conn, err := sqlite3.Open("file:" + filepath.Join(db.Dir, db.Filename) + "?" +
				"_pragma=busy_timeout(2500)&" + // default: 0

				"_pragma=journal_mode(wal)&" + // default: delete
				"_pragma=synchronous(normal)" + // default: full
			)
			if err != nil {
				return nil, err
			}

			return &Conn{Conn: conn}, nil
		}, func(conn *Conn) {
			for stmt := range conn.Stmts() {
				stmt.Close()
			}
			conn.Close()
		},
	)

        ...

	err := db.Pool.Start()
	if err != nil {
		return err
	}

	defer db.Pool.Stop()

        ...

	conn, err := db.Pool.Get(ctx)
	if err != nil {
		return err
	}
	defer db.Pool.Put(conn)

        ...

[ncruces]

My stance, so far, has been that if you want a pool, prepared statement cache, etc, you should use database/sql.

I'm not opposed to connection pooling, but it's one less thing for me to maintain.

And I fully expect that a prepared statement cache is the follow-up request (not necessarily from you, but the next person that decides to use the pool).

I've also tried hard to avoid inventing new APIs: the low level ones I copy from SQLite as closely as possible, database/sql mostly follows other drivers. The fewer innovative things I come up with, the less likely I am to mess things up. Even the extensions, I mostly try to offer compatibility with well known extensions, or PostgreSQL, etc.

There aren't many battle tested, low level, connection pool APIs for SQLite; crawshaw is one, so if I was to build one, I'd mostly likely copy from there.

[michaellenaghan]

Understood!