Add a specialized pool for SQLite that has a reader / writer split

[mehcode]

pub struct SqlitePool {
  writer: Pool<Sqlite>,
  reader: Pool<Sqlite>,
}

impl SqlitePool {
  pub fn read(&self) -> &Pool<Sqlite> { &self.reader }

  pub fn write(&self) -> &Pool<Sqlite> { &self.writer }
}

• SqlitePool::new
• SqlitePool::new_with
• SqlitePool::builder
• SqlitePoolBuilder::max_readers
• SqlitePoolBuilder::max_writers

let db = SqlitePool::new(" ... ").await?;

// [...]

query!("SELECT 10").fetch_one(db.read()).await?

[Cocalus]

I suspect you know this, but just as a reminder

This only avoids busy locks when the database is using the wal journal. While very often the best mode, it's not the default because it does have some limits.
https://sqlite.org/wal.html

In the SqlitePool documentation I would suggest adding a link to the wal docs so the user is reminded that they probably want a wal journal.

Also you probably should use
SQLITE_OPEN_READONLY
With the read connections, to catch client misuse of the read connections.

[link2xt]

Also you probably should use SQLITE_OPEN_READONLY

In our experience at chatmail/core#2089 opening reader connections with read only mode while writer connections are open with read-write mode caused some tests to break. Didn't look further, but probably it's better to open all connections with the same settings and limit reader connections with query_only PRAGMA.

[D1plo1d]

Any update on this? I'm experiencing intermittent database is locked errors similar to this that render my IOT application unresponsive until reboot. Obviously I can't go to production with this issue so I'm wondering whether to build my own locking mechanism or hold off for a sqlx solution.

[mehcode]

@D1plo1d For an IOT application, you should be fine doing exactly as that blog article. Either don't use the pool and put a Mutex around a single SqliteConnection or use a max_connections of 1 with the pool. This reduces some throughput but will prevent all potential locking issues.

[D1plo1d]

Thank, I'll give that a shot. max_connections is an easy enough change for me. I'm guessing that is effectively turning the pool into a mutex around one Sqlite connection?

[aalowlevel]

Any news?