Well we have a GenServer on top of the connection so there can be only a single task on each DB at any time, and so the best pool size would be 1 I guess.
Take your time and take care 
In the meantime I reverted back to CubDB for the data we know the shape of.
I originally thought I’d do that as well from Elixir side but it’s IMO hugely faster to have the pool stuff controlled from the Rust side (and my semi-scientific measurements back in the last summer have confirmed it).
I can only talk right now but when I get to the first release I’d recommend just blindly using the handle you’re given from the open(...) call and not worry about a thing. I designed the library that way. It automatically serializes access when the pool is only the size of 1 anyway.
Hi @kevinlang I just tried out the sqlite3 support in phx.new, very nice!
I was able to get the tests to run in async: true mode using :memory: databases initialized with a schema dump and put_dynamic_repo.
defmodule MyApp.Repo do
use Ecto.Repo, otp_app: :my_app, adapter: Ecto.Adapters.SQLite3
@doc "Create an in-memory database and make it the current dynamic repo"
def put_dynamic_memory_repo do
{:ok, pid} = start_link(Keyword.merge(config(), name: nil, database: ":memory:", pool_size: 1))
put_dynamic_repo(pid)
:my_app
|> :code.priv_dir()
|> Path.join("repo/structure.sql")
|> File.read!()
|> String.split(";\n", trim: true)
|> Enum.each(&query!/1)
end
setup tags do
if tags[:async], do: MyApp.Repo.put_dynamic_memory_repo()
pid = Ecto.Adapters.SQL.Sandbox.start_owner!(MyApp.Repo, shared: not tags[:async])
on_exit(fn -> Ecto.Adapters.SQL.Sandbox.stop_owner(pid) end)
:ok
end
defp aliases do
[
setup: ["deps.get", "ecto.setup"],
"ecto.migrate": ["ecto.migrate", "ecto.dump"],
"ecto.setup": ["ecto.create", "ecto.migrate", "run priv/repo/seeds.exs"],
"ecto.reset": ["ecto.drop", "ecto.setup"],
test: ["ecto.create --quiet", "ecto.migrate --quiet", "test"]
]
end
Are there any plans to support async tests out of the box in ecto_sqlite3 ?
Nice!
We discussed it at one point and decided against it in the ecto_sqlite3 code repository itself (at the time), primarily so we can more easily detect issues around handling of database connections and transaction errors. If we had each test in our code repository be async via this mechanism, we would likely have missed some issues when we were in the midst of ironing out some trickier edge cases around inter-connection issues (e.g., not rolling back a failed write transaction correctly, locking the database for the next test!)
However, for downstream uses, I don’t see any reason why this cannot be supported, eventually. I think at the very least having some documentation on the adapter for this approach could be very useful, if you would like to open a PR adding some
Some other thoughts:
MyApp.Repo by creating a new SQLite3 flavored sandbox, Ecto.Adapters.SQLite3.Sandbox that does this (or the de/serialization approach) under the hood, forking the connection into a memory-version. This would allow downstream users to choose their sandbox strategy by specifying either Ecto.Adapters.SQL.Sandbox or Ecto.Adapters.SQLite3.Sandbox for the transaction-isolation or connection-isolation approach, respectively.
This would be really amazing with litestream.io. We’ve been experimenting with using sqlite + litestream to run lightweight, single VM applications that persist their state somewhat safely. It works great for apps that can pause their writes for a minute or two while a new version comes up.
The biggest issue is that DBConnection defines the interface for pools and is how ecto_sql obtains connections.
I see. Well, I stumbled upon this curious problem while I was deciding pooling: what happens with anonymous databases? If you just pass :memory: for a pool then it’ll return a different DB for every connection that’s checked out. That’s why I moved the logic to the Rust code because it could handle that by internally generating names, e.g. through UUIDs.
It’ll be a bit painful to replicate that code in Elixir but when I get the free time and energy I’ll definitely do it.
I was under the impression that even in C or Rust you could not duplicate a connection into the same :memory: database. Are you talking about sharing the same pointer reference?
No, not the same pointer reference. I gave up on that, it’s too much trouble and internally sqlite will use a mutex which will serialize access anyway – it is almost a completely pointless exercise to use the so-called multi-threaded handle mode.
I was saying that if you setup the connection like this:
config :myapp, MyApp.Repo,
...,
database: ":memory:",
...
Then every new checked out connection will point to a brand new anonymous sqlite in-memory DB which is not what one would want – you expect to get a “connection” to the same DB when the connections are checked out from the same pool. Hence I resorted to generating UUIDs internally so even if you use the configuration from above, below the hood you’ll actually have the DB be named file:<UUID_GOES_HERE>?mode=memory&cache=shared".
That way, checking out a connection will produce an independent, serial (not multithreaded) sqlite DB handle that still points at the same anonymous DB. Which allows you to fully utilize sqlite’s ability to read from the same DB from multiple threads at the same time.