Database locked error on create+migrate

[pikeas]

# /lib/project/repo.ex
defmodule Project.Repo do
  use Ecto.Repo,
    otp_app: :project,
    adapter: Ecto.Adapters.SQLite3
end

# /priv/repo/migrations/1234_create_items.exs
defmodule Project.Repo.Migrations.CreateItems do
  use Ecto.Migration

  def change do
    create table(:items) do
    end
  end
end

# config/runtime.exs
config :project, Project.Repo, database: "tmp/project.db"

$ mix ecto.drop; mix ecto.create; mix ecto.migrate
The database for Project.Repo has been dropped
The database for Project.Repo has been created

10:50:36.792 [info] == Running 1234 Project.Repo.Migrations.CreateItems.change/0 forward
10:50:36.795 [info] create table items
10:50:36.795 [info] == Migrated 1234 in 0.0s
✅ 

$ mix ecto.drop; mix ecto.create; mix ecto.migrate
✅ [success again]

$ mix ecto.drop; mix ecto.create; mix ecto.migrate
The database for Project.Repo has been dropped
The database for Project.Repo has been created

❌ 10:50:39.983 [error] Exqlite.Connection (#PID<0.176.0>) failed to connect: ** (Exqlite.Error) database is locked
10:50:40.006 [info] == Running 1234 Project.Repo.Migrations.CreateItems.change/0 forward
10:50:40.008 [info] create table items
10:50:40.009 [info] == Migrated 1234 in 0.0s

$ mix ecto.drop; mix ecto.create; mix ecto.migrate
❌ [error again]

$  mix ecto.drop; mix ecto.create; mix ecto.migrate
✅ [success again]

This is running locally (no virtualization, no remote filesystem) on MacOS. Why does Exqlite sometimes error when drop/create/migrate is run in quick succession? It looks like ecto.create may be holding a write lock despite appearing to exit?

[warmwaffles]

It's a "race" condition I have yet to resolve. This has to do with how the connection pooling works. The process that held the sqlite connection open is shutting down at the same time a new one is spinning up and I have no way to reliably prevent that without dropping DBConnection from this.

Is this a blocker for you or just an annoyance?

[pikeas]

Thanks for the quick response!

Is this a blocker for you or just an annoyance?

Annoyance and makes for a poor new user experience - I spent a lot of time triple-checking my config and narrowing down the root cause.

Since this is a known issue, perhaps the error could be caught and a hint logged to console before re-raising?

[pikeas]

Another idea is to add a single sleep+retry to the migration function, with an opt-in repo config flag.

[warmwaffles]

That error is the process crashing. You would have to build that handler in your application.

Curious, what is your pooling configuration for dev/test?

[pikeas]

Curious, what is your pooling configuration for dev/test?

This is a fresh Elixir app, all packages are up-to-date and all config is 100% defaults. The behavior should be reproducible with the code snippets in the first post.

[warmwaffles]

Set pool_size: 1 in your dev.exs and give it a shot. It should help with the thrashing.

[warmwaffles]

I'm going to leave this issue open until we complete the removal of DBConnection from this library and lift it to ecto_sqlite3 OR do away with it entirely and implement our own pooling strategy since sqlite is not like mysql or postgres with regards to TCP connections.

[pikeas]

Set pool_size: 1 in your dev.exs and give it a shot. It should help with the thrashing.

Good idea. On ~50 runs in quick succession, this reduces the failure rate from ~30% to ~15%.

For context, I ran into this while iterating on a new schema:

• Add field to Thing.ex, add column to 123_create_things.exs migration.
• Drop/create/migrate
• Make another quick change
• Drop/create/migrate again <10 seconds later
• Crash

[foucist]

I just got this error with 0.34.0. Originally I never got this error with

database: Path.expand("../project_dev.db", __DIR__)` 

but it started happening with

database: Path.expand("../priv/db/project_dev.db", __DIR__)

I'm not sure why changing the relative path would affect it, but I found that adding busy_timeout: 5000 resolved this error.

[warmwaffles]

@foucist can you try pulling the latest version v0.35.0 and try this again?

[foucist]

@warmwaffles It didn't seem to make a difference. I tried making a repo for you to test with https://github.com/foucist/exqlite_test

This is what I get. Dev environment is linux, and Elixir 1.19.5 (compiled with Erlang/OTP 28)

exqlite_test main ❯ mix ecto.create && mix ecto.migrate
==> comeonin
Compiling 3 files (.ex)
Generated comeonin app
==> bcrypt_elixir
mkdir -p "/home/foucist/Work/exqlite_test/_build/dev/lib/bcrypt_elixir/priv"
cc -g -O3 -Wall -Wno-format-truncation -I"/home/foucist/.local/share/mise/installs/erlang/28.3.1/erts-16.2/include" -Ic_src -fPIC -shared  c_src/bcrypt_nif.c c_src/blowfish.c -o "/home/foucist/Work/exqlite_test/_build/dev/lib/bcrypt_elixir/priv/bcrypt_nif.so"
Compiling 3 files (.ex)
Generated bcrypt_elixir app
==> exqlite_test
Compiling 26 files (.ex)
Generated exqlite_test app
The database for ExqliteTest.Repo has been created

10:39:06.865 [error] Exqlite.Connection (#PID<0.195.0> ("db_conn_2")) failed to connect: ** (Exqlite.Error) database is locked

10:39:06.956 [info] == Running 20260226033300 ExqliteTest.Repo.Migrations.CreateUsersAuthTables.change/0 forward

10:39:06.958 [info] create table users

10:39:06.964 [info] create index users_email_index

10:39:06.964 [info] create table users_tokens

10:39:06.964 [info] create index users_tokens_user_id_index

10:39:06.964 [info] create index users_tokens_context_token_index

10:39:06.965 [info] == Migrated 20260226033300 in 0.0s
exqlite_test main ❯ git st .
On branch main
nothing to commit, working tree clean
exqlite_test main ❯ rm priv/db/*
exqlite_test main ❯ mix ecto.create && mix ecto.migrate
The database for ExqliteTest.Repo has been created

10:39:16.700 [error] Exqlite.Connection (#PID<0.194.0> ("db_conn_1")) failed to connect: ** (Exqlite.Error) database is locked

10:39:17.286 [info] == Running 20260226033300 ExqliteTest.Repo.Migrations.CreateUsersAuthTables.change/0 forward

10:39:17.288 [info] create table users

10:39:17.294 [info] create index users_email_index

10:39:17.294 [info] create table users_tokens

10:39:17.294 [info] create index users_tokens_user_id_index

10:39:17.295 [info] create index users_tokens_context_token_index

10:39:17.296 [info] == Migrated 20260226033300 in 0.0s