Ecto_sqlite different read and write pools

Questions / Help
,
1

Context: I want to apply some of the recommendations specified in this article https://kerkour.com/sqlite-for-servers

Specifically: 1 Write connection, multiple Read connections

My thought on how to solve this was to use 2 different ecto repos, one for writes, one for reads and give the read repo a pool_size of 1 and the read a different, bigger pool_size.

That seems to work ok, but I run into a problem with testing and the ecto sandbox.
Because ofcourse there are now 2 sandboxes and the read repo can’t read what the write repo wrote.

Here are my thoughts on how to solve this:

  1. don’t use 2 different repo’s and accept that I can’t do what the article suggests
  2. maybe there is an other way with 1 repo that allows me to set different read and write pools
  3. fix the tests by removing the sandbox and cleaning up the db manually after each test - ecto_sqlite3 doesn’t allow async tests anyway, so this would not be to much of an issue
  4. …?

Does anyone have any suggestion?

2
  1. Wait for Replace DirtyNIF execution model with a different mechanism · Issue #192 · elixir-sqlite/exqlite · GitHub to be resolved
2 Likes
3

I would do number 3

4

Either 3 or what @LostKobrakai’s idea.

5

Thanks all, going to read into that issue, but it looks like it might be out a while, so will look into 3 as well.

Does anyone see downsides for 3?
Or downsides for using 2 repos in general?

6

No issues besides the potential error prone behavior of doing this manually, well at least as long as you don’t try to write from both repos.

1 Like