Sqlite3: Generators need plural table names (was: workaround for the testing sandbox)

Questions / Help
, , ,
1

Hey fellow alchemists,

I was delighted to see that an sqlite3 adapter has indeed been developed since I tried phoenix and ecto last, so I decided to give it another try.

Sadly, with a phoenix liveview application, using the phx.gen.live generator, some generated tests always fail – specifically, the liveview tests deleting and updating items in a list always fail.

I tracked down the issue to the documented problem with the Ecto sandbox here.

I wish to just use the normal application repo with :memory: databases which can be destroyed and spun up relatively fast. I already found information regarding after_connect. I do not care enough about performance for this.

How do I define a new TestCase (like ConnCase) without the sandbox? The goal is to make the failing tests succeed and exclude them :slight_smile:

(If there was another way to make the generated tests run with sqlite3 with the sandbox enabled, I would be happy to be told how.)

The following creates a test project exhibiting the problem.

mix phx.new --database sqlite3 --live throwaway
cd throwaway
# create some entity in the db with associates liveviews
mix phx.gen.live Things Thing thing thingvalue:integer thingdescription:string
# copy routes into router.ex
mix test

Thanks for reading!

Because PostgreSQL always seems to be commented on: sqlite3 is going to be the production database, not some development crutch. I have been working with sqlite3 for almost 15 years, I am comfortable with it in this case. I know PostgreSQL well (and I love it too), but I do not wish to install, backup, and maintain another database cluster.

1 Like
2

Thanks for posting this. I ran into the same problem months ago but did not raise the issue.

1 Like
3

The simple solution is making those tests not async. Disabling the sandbox won’t really help if you want to keep tests running concurrently as you still need a system at least very similar to the ecto sandbox, which would spawn databases instead of transactions per test.

1 Like
4

I think the tests are not generated async, at least not in the project generated through the reproduction instructions I posted above? Would they not have to be explicitly do async: true? The default was false some years ago…

They still fail, and I still suspect it is the transaction the sandbox wraps around the tests (but please if it is not – enlighten me!)…

I stated before that performance and concurrency in the tests is something I would care about after the tests run correctly, but it is no use if they do not succeed at all.

Thanks for replying!

5

The documented problem you linked to is explicitly about async sandbox usage. Non async sandbox usage should work just fine.

6

Hmm, it does not though. But the issue I linked to does state that multiple writes per transaction might be problematic? I might misunderstand this though.

I do not know how to debug this further.

7

This is due to SQLite only allowing up one write transaction at a time

Emphasis mine – the at a time part is crutial here. It’s about concurrent transactions not transactions after each other.

If non-async tests fail with the ecto sandbox I’d consider that a bug.

8

I have filed an issue on the adapter GitHub here and hope that is the right place.

Thanks!

9

Not a bad idea, it will work the same as rollback transactions in theory without the limitation of sandbox.

Sadly I’ve encountered also a lot of bugs with sqlite in composition with ecto, maybe sqlite will receive more love in the future, as I think it would be ideal for products that don’t require huge databases.

10

I do to, but two problems you will face doing this.

  1. Right now exqlite is dependent on :db_connection which makes :memory: a bit difficult to work with. There is discussion to remove it as a dependency. Replace DirtyNIF execution model with a different mechanism · Issue #192 · elixir-sqlite/exqlite · GitHub
  2. Every time you check out a new connection, you will need to run the migrations from start to finish OR load the database structure every time you check it out. Just something to keep in mind if you want to use the :memory: route.

I definitely have a dream of using :memory: for testing in a massively parallel way. The first step to accomplishing this is to remove the dependency on :db_connection and implement our own pooling mechanism or just live with one genserver. The second step is to make that spin up time be as fast as possible when using :memory: and benchmark that against using the sandbox.

2 Likes
11

I think running migrations once again is a rather sane option, as even if there are hundreds of tables, it should be very fast. Now if we talk about seeds too, then this can get slow very fast.

12

Perhaps. In the current code base I am working on day to day, we have 890 migrations and rising. The quickest and best solution is to use mix ecto.dump and mix ecto.load. We aren’t using sqlite, but there is a limit to running migrations front to back every time a connection is checked out. You’ll definitely want to load the schema.

13

Alright, please people, be blunt: Is there no way to use sqlite3 with phoenix liveview and ecto that is stable and solid, and does not generate tests which automatically fail today?

I am asking this because @D4no0 explained above that there are still “a lot” of bugs.

14

I’ve got this test case where a new random directory is created for each test, I also use it for sqlite3, but the scenario is somewhat different.

To cover your use case, I would:

  1. Create a database file and migrate it before running any tests. Store somewhere.
  2. Modify the snippet below to copy the fresh database before every test.
defmodule MyApp.SandboxCase do
  use ExUnit.CaseTemplate
  @symbols ~c"023456789abcdefghijklmnopqrst"
  @n_symbols Enum.count(@symbols)

  setup do
    rand_dir = for _ <- 1..20, into: "", do: <<Enum.at(@symbols, :rand.uniform(@n_symbols))>>

    sandbox_dir = "/tmp/sandbox/#{rand_dir}/"

    pid = self()
    ref = make_ref()

    spawn_link(fn -> monitor_for_cleanup(pid, ref, sandbox_dir) end)

    receive do
      {^ref, :ready} -> :ok
    end

    on_exit(fn ->
      File.rm_rf(sandbox_dir)
    end)

    %{sandbox_dir: sandbox_dir}
  end

  defp monitor_for_cleanup(pid, ref, path) do
    Process.flag(:trap_exit, true)
    send(pid, {ref, :ready})

    receive do
      {:DOWN, ^pid, _reason} ->
        File.rm_rf(path)
    end
  end
end
**
15

Test generation is not in the purview of ecto_sqlite3 or exqlite that is something in phoenix proper. You will need to submit a PR or open an issue there about it. The ecto_sqlite3 dependency does not provide generators.

16

I don’t use generators but I’m interested as I’m developing a (small) app slated for production using sqlite.

Are there any missing steps to reproduce? I generated a new project and generated two contexts using phx.gen.live. I copied in the routes and ran tests maybe 20 times in a row and never got a failure. Maybe OS matters? I’m on latest macos.

17

This has also been my experience with it, and I’m on linux.

18

I am on linux as well. I have created the project with

Erlang/OTP 25 [erts-13.0.4] [source] [64-bit] [smp:8:8] [ds:8:8:10] [async-threads:1] [jit:ns]

Elixir 1.15.4 (compiled with Erlang/OTP 25)

versions I used:

# Type `mix help deps` for examples and options.
  defp deps do
    [
      {:bcrypt_elixir, "~> 3.0"},
      {:phoenix, "~> 1.7.7"},
      {:phoenix_ecto, "~> 4.4"},
      {:ecto_sql, "~> 3.10"},
      {:ecto_sqlite3, ">= 0.0.0"},
      {:phoenix_html, "~> 3.3"},
      {:phoenix_live_reload, "~> 1.2", only: :dev},
      {:phoenix_live_view, "~> 0.19.0"},
      {:floki, ">= 0.30.0", only: :test},
      {:phoenix_live_dashboard, "~> 0.8.0"},
      {:esbuild, "~> 0.7", runtime: Mix.env() == :dev},
      {:tailwind, "~> 0.2.0", runtime: Mix.env() == :dev},
      {:swoosh, "~> 1.3"},
      {:finch, "~> 0.13"},
      {:telemetry_metrics, "~> 0.6"},
      {:telemetry_poller, "~> 1.0"},
      {:gettext, "~> 0.20"},
      {:jason, "~> 1.2"},
      {:plug_cowboy, "~> 2.5"},
      {:credo, "~> 1.7", only: [:dev, :test], runtime: false}
    ]
  end

Hmm, I can try on another machine if it happens there as well. Surprising.

19

Can you share this project with us?

20

I have generated a fresh one, still exhibiting the problem, and put it at GitHub - lindem/sqlite3_testsetup: SQLite3 failure example application

Thanks very much for looking into this!