I'm surprised the API doesn't follow the well known by now "1 write connection, n read connections" model, at least by default.

See https://kerkour.com/sqlite-for-servers for that and more ideas.

To do this, it's best if the user opens two pools themselves.

I've tried to create something that does this transparently (give you a pool that magically routes writes to a single connection), but it's hard.

It starts simple: Exec is a write, Query is a read. Unless it contains the word RETURNING, then it can be an UPDATE…RETURNING. BeginTx can be read-only otherwise assume it's a write.

The problem is that attached DBs throw a wrench and make it even more confusing.

So only the user can really know if it's a write or a read. But then this makes it harder to use an ORM or even something like SQLc. So I don't know if the “driver” should do anything about it really.

Thanks for pointing this out. I don't think it's something this little helper library should try to do. This kind of thing belongs in whatever database abstraction you're building/using, if any. It's also a little trickier than it seems at first glance (RETURNING, etc).

And for many no/low concurrency situations there's no/little benefit.

Added this example to the README.

  rwdb, err := sqlitebp.OpenReadWriteCreate("app.db")
  if err != nil {
    log.Fatal(err)
  }
  rwdb.SetMaxOpenConns(1)
  rwdb.SetMaxIdleConns(1)


  // Read-only with default adaptive pool size (2-8 based on GOMAXPROCS)
  rodb, err := sqlitebp.OpenReadOnly("app.db")
  if err != nil {
    log.Fatal(err)
  }