Need help testing and working through an ecto3 and sqlite adapter

During the winter storm here in Texas, I was couped up without reliable electricity so I was bored and started working on an sqlite3 nif and ecto3 adapter.

I had seen esqlite and sqlite_ecto2 and thought I would try my hand at just doing it all in elixir.

My goal with the project was to make the sqlite3 adapter as configurable as possible. Including changing cache pragma directives, full text search etc…

Anyways, I’m looking for some help knocking out bugs and finding them. I am currently using them on a personal project of mine until I can get this stable.


I got this error:

==> exqlite
mkdir -p /tmp/mix_installs/elixir-1.12.0-dev-erts-12.0/d3fdf3d0ae84916ee0aaf0dc561b4c17/_build/dev/lib/exqlite/priv
make: *** Brak reguł do zrobienia obiektu 'sqlite3/sqlite3.c', wymaganego przez '/tmp/mix_installs/elixir-1.12.0-dev-erts-12.0/
d3fdf3d0ae84916ee0aaf0dc561b4c17/_build/dev/lib/exqlite/priv/'. Stop.
could not compile dependency :exqlite, "mix compile" failed. You can recompile this dependency with "mix deps.compile exqlite",
update it with "mix deps.update exqlite" or clean it with "mix deps.clean exqlite"                                             
** (Mix.Error) Could not compile with "make" (exit status: 2).
You need to have gcc and make installed. If you are using
Ubuntu or any other Debian-based system, install the packages
"build-essential". Also install "erlang-dev" package if not
included in your Erlang/OTP version. If you're on Fedora, run
"dnf group install 'Development Tools'".

(mix 1.12.0-dev) lib/mix.ex:452: Mix.raise/2
(elixir_make 0.6.2) lib/mix/tasks/compile.make.ex:143:
(mix 1.12.0-dev) lib/mix/task.ex:394: Mix.Task.run_task/3
(mix 1.12.0-dev) lib/mix/tasks/compile.all.ex:90: Mix.Tasks.Compile.All.run_compiler/2
(mix 1.12.0-dev) lib/mix/tasks/compile.all.ex:70: Mix.Tasks.Compile.All.compile/4
(mix 1.12.0-dev) lib/mix/tasks/compile.all.ex:57: Mix.Tasks.Compile.All.with_logger_app/2
(mix 1.12.0-dev) lib/mix/tasks/compile.all.ex:35:
(mix 1.12.0-dev) lib/mix/task.ex:394: Mix.Task.run_task/3

Then tried to run make manually:

$ LC_ALL=en MIX_APP_PATH=. make clean all
rm -f ./priv/
mkdir -p priv
make: *** No rule to make target 'sqlite3/sqlite3.c', needed by 'priv/'.  Stop.

After that I have removed sqlite3/sqlite3.c from Makefile:

# SRC = sqlite3/sqlite3.c c_src/sqlite3_nif.c
SRC = c_src/sqlite3_nif.c

# …

Finally I have this error:

==> exqlite
mkdir -p /tmp/mix_installs/elixir-1.12.0-dev-erts-12.0/d3fdf3d0ae84916ee0aaf0dc561b4c17/_build/dev/lib/exqlite/priv
cc -g -O3 -Wall -I"/home/eiji/.asdf/installs/erlang/24.0-rc1/erts-12.0/include" -Isqlite3 -Ic_src -DSQLITE_THREADSAFE=1 -DSQLITE_USE_URI -DSQLITE_ENABLE_FTS4 -DSQLITE_ENABLE_FTS5 -DSQLITE_ENABLE_JSON1 -DSQLITE_ENABLE_GEOPOLY -DSQLITE_OMIT_DEPRECATED -DSQLITE_ENABLE_RTREE -DSQLITE_ENABLE_RBU -DNDEBUG=1 -shared -fPIC -fvisibility=hidden -Wl,-soname, c_src/sqlite3_nif.c -o /tmp/mix_installs/elixir-1.12.0-dev-erts-12.0/d3fdf3d0ae84916ee0aaf0dc561b4c17/_build/dev/lib/exqlite/priv/
Compiling 5 files (.ex)

03:04:35.140 [warn]  The on_load function for module Elixir.Exqlite.Sqlite3NIF returned:
  'Failed to load NIF library: \'/tmp/mix_installs/elixir-1.12.0-dev-erts-12.0/d3fdf3d0ae84916ee0aaf0dc561b4c17/_build/dev/lib/exqlite/priv/ undefined symbol: sqlite3_bind_int64\''}}

Generated exqlite app
** (ArgumentError) adapter Ecto.Adapters.Exqlite was not compiled, ensure it is correct and it is included as a project dependency
    (ecto 3.5.8) lib/ecto/repo/supervisor.ex:60: Ecto.Repo.Supervisor.compile_config/2
    ecto_example.exs:4: (module)
    ecto_example.exs:3: (file)

So I have checked it by ldd:

$ ldd (0x00007ffe94eb1000) => /usr/lib/ (0x00007f3220a46000)
        /usr/lib64/ (0x00007f3220c41000)

Please notice that I had no errors on cc compilation.

@Eiji was this installing with mix or cloning the repository and running mix compile?

I have used: Mix.install([:ecto_sql, :exqlite]) from elixir's master branch. It’s like mix deps.get, but for single file scripts.

Interesting … This is error from cloned repo:

$ LC_ALL=en MIX_APP_PATH=. make clean all
rm -f ./priv/
mkdir -p priv
/bin/sh: warning: setlocale: LC_ALL: cannot change locale (en)
c_src/sqlite3_nif.c:5:10: fatal error: erl_nif.h: No such file or directory
    5 | #include <erl_nif.h>
      |          ^~~~~~~~~~~
compilation terminated.
make: *** [Makefile:55: priv/] Error 1

Edit: mix compile on cloned repository compiled without any error.

ok, I found it - it’s simple sqlite3 directory is not included in hex

Odd indeed. Once we knock out some of the outstanding issues in the repo, I’ll cut a release.

Can you submit a PR for the fix?

sure :slight_smile:

1 Like

I appreciate the help.

I’m especially interested using sqlite3 in my apps. I have tested it on myself:

Mix.install([:ecto_sql, {:exqlite, github: "warmwaffles/exqlite"}])

defmodule Repo do
  use Ecto.Repo, adapter: Ecto.Adapters.Exqlite, otp_app: :my_app

defmodule Migration do
  use Ecto.Migration

  def change do
    create table("todo_lists") do
      add(:title, :string)

    create table("todo_items") do
      add(:description, :string)
      add(:type, :string)

    create table("todo_list_items") do
      add(:todo_item_id, references(:todo_items))
      add(:todo_list_id, references(:todo_lists))

defmodule TodoList do
  use Ecto.Schema

  schema "todo_lists" do
    many_to_many(:todo_items, TodoItem, join_through: TodoListItem)

defmodule TodoListItem do
  use Ecto.Schema

  schema "todo_list_items" do
    belongs_to(:todo_list, TodoList)
    belongs_to(:todo_item, TodoItem)

defmodule TodoItem do
  use Ecto.Schema

  schema "todo_items" do
    field(:description, :string)
    field(:type, :string)

defmodule Example do
  alias Ecto.Query
  require Query

  def cleanup do

  def prepare do
    Application.put_env(:my_app, Repo,
      database: "example",
      pool_size: 10,
      show_sensitive_data_on_connection_error: true,
      username: System.get_env("USER")

    Ecto.Migrator.up(Repo, 1, Migration)

  def sample do
    |> Query.from(as: :todo_list)
    |> Query.join(:inner, [todo_list: todo_list], assoc(todo_list, :todo_items), as: :todo_items)
    |> Query.where([todo_items: todo_items], todo_items.type != "hobby")
    |> Query.preload([todo_list: todo_list, todo_items: todo_items], todo_items: todo_items)
    |> Repo.all()
    |> IO.inspect()

  def seed do
      title: "Hobby example",
      todo_items: [%{description: "hobby1", type: "hobby"}]

      title: "Job example",
      todo_items: [%{description: "job1", type: "job"}, %{description: "job2", type: "job"}]

elixir ecto_example.exs
* Getting exqlite (
remote: Enumerating objects: 77, done.        
remote: Counting objects: 100% (77/77), done.        
remote: Compressing objects: 100% (62/62), done.        
remote: Total 802 (delta 23), reused 55 (delta 12), pack-reused 725        
origin/HEAD set to main
Resolving Hex dependencies...
Dependency resolution completed:
  connection 1.1.0
  db_connection 2.3.1
  decimal 2.0.0
  ecto 3.5.8
  ecto_sql 3.5.4
  elixir_make 0.6.2
  telemetry 0.4.2
* Getting ecto_sql (Hex package)
* Getting db_connection (Hex package)
* Getting decimal (Hex package)
* Getting ecto (Hex package)
* Getting elixir_make (Hex package)
* Getting telemetry (Hex package)
* Getting connection (Hex package)
==> connection
Compiling 1 file (.ex)
Generated connection app
===> Compiling telemetry
==> decimal
Compiling 4 files (.ex)
Generated decimal app
==> elixir_make
Compiling 1 file (.ex)
Generated elixir_make app
==> db_connection
Compiling 14 files (.ex)
Generated db_connection app
==> ecto
Compiling 56 files (.ex)
warning: Decimal.cmp/2 is deprecated. Use compare/2 instead
  lib/ecto/changeset.ex:2160: Ecto.Changeset.validate_number/6

Generated ecto app
==> ecto_sql
Compiling 26 files (.ex)
Generated ecto_sql app
==> exqlite
mkdir -p /tmp/mix_installs/elixir-1.12.0-dev-erts-12.0/5f38105e26a7022da3e48e89d9b11bd6/_build/dev/lib/exqlite/priv
cc -g -O3 -Wall -I"/home/…/.asdf/installs/erlang/24.0-rc1/erts-12.0/include" -Isqlite3 -Ic_src -DSQLITE_THREADSAFE=1 -DSQLITE_USE_URI -DSQLITE_ENABLE_FTS4 -DSQLITE_ENABLE_FTS5 -DSQLITE_ENABLE_JSON1 -DSQLITE_ENABLE_GEOPOLY -DSQLITE_OMIT_DEPRECATED -DSQLITE_ENABLE_RTREE -DSQLITE_ENABLE_RBU -DNDEBUG=1 -shared -fPIC -fvisibility=hidden -Wl,-soname, sqlite3/sqlite3.c c_src/sqlite3_nif.c -o /tmp/mix_installs/elixir-1.12.0-dev-erts-12.0/5f38105e26a7022da3e48e89d9b11bd6/_build/dev/lib/exqlite/priv/
Compiling 13 files (.ex)
warning: unused import String

Generated exqlite app

04:38:54.636 [info]  == Running 1 Migration.change/0 forward

04:38:54.645 [info]  create table todo_lists

04:38:54.648 [info]  create table todo_items

04:38:54.650 [info]  create table todo_list_items

04:38:54.654 [info]  == Migrated 1 in 0.0s

04:38:54.792 [debug] QUERY OK db=0.0ms idle=182.7ms
begin []

04:38:54.795 [debug] QUERY OK db=0.8ms
INSERT INTO todo_lists (title,inserted_at,updated_at) VALUES (?,?,?) ["Hobby example", "2021-03-01T03:38:54", "2021-03-01T03:38:54"]

04:38:54.795 [debug] QUERY OK db=0.6ms
INSERT INTO todo_items (description,type,inserted_at,updated_at) VALUES (?,?,?,?) ["hobby1", "hobby", "2021-03-01T03:38:54", "2021-03-01T03:38:54"]

04:38:54.796 [debug] QUERY OK db=0.1ms
INSERT INTO todo_list_items (todo_item_id,todo_list_id) VALUES (?,?) [1, 1]

04:38:54.798 [debug] QUERY OK db=1.9ms
commit []

04:38:54.798 [debug] QUERY OK db=0.0ms idle=194.0ms
begin []

04:38:54.799 [debug] QUERY OK db=0.4ms
INSERT INTO todo_lists (title,inserted_at,updated_at) VALUES (?,?,?) ["Job example", "2021-03-01T03:38:54", "2021-03-01T03:38:54"]

04:38:54.799 [debug] QUERY OK db=0.1ms
INSERT INTO todo_items (description,type,inserted_at,updated_at) VALUES (?,?,?,?) ["job1", "job", "2021-03-01T03:38:54", "2021-03-01T03:38:54"]

04:38:54.799 [debug] QUERY OK db=0.1ms
INSERT INTO todo_list_items (todo_item_id,todo_list_id) VALUES (?,?) [2, 2]

04:38:54.799 [debug] QUERY OK db=0.0ms
INSERT INTO todo_items (description,type,inserted_at,updated_at) VALUES (?,?,?,?) ["job2", "job", "2021-03-01T03:38:54", "2021-03-01T03:38:54"]

04:38:54.799 [debug] QUERY OK db=0.0ms
INSERT INTO todo_list_items (todo_item_id,todo_list_id) VALUES (?,?) [3, 2]

04:38:54.801 [debug] QUERY OK db=1.7ms
commit []

04:38:54.804 [debug] QUERY OK source="todo_lists" db=0.1ms queue=0.3ms idle=199.6ms
SELECT, t0.title, t0.inserted_at, t0.updated_at,, t1.description, t1.type, t1.inserted_at, t1.updated_at FROM todo_lists AS t0 INNER JOIN todo_list_items AS t2 ON t2.todo_list_id = INNER JOIN todo_items AS t1 ON t2.todo_item_id = WHERE (t1.type != 'hobby') []
    __meta__: #Ecto.Schema.Metadata<:loaded, "todo_lists">,
    id: 2,
    inserted_at: ~N[2021-03-01 03:38:54],
    title: "Job example",
    todo_items: [
        __meta__: #Ecto.Schema.Metadata<:loaded, "todo_items">,
        description: "job1",
        id: 2,
        inserted_at: ~N[2021-03-01 03:38:54],
        type: "job",
        updated_at: ~N[2021-03-01 03:38:54]
        __meta__: #Ecto.Schema.Metadata<:loaded, "todo_items">,
        description: "job2",
        id: 3,
        inserted_at: ~N[2021-03-01 03:38:54],
        type: "job",
        updated_at: ~N[2021-03-01 03:38:54]
    updated_at: ~N[2021-03-01 03:38:54]

Many thanks! :heart:


No problem man. I have the same desire too. I wanted to try a quick poor mans map reduce with multiple sqlite databases. Thought it would be something cool to demonstrate and write about.

If you run into ANY bugs. Please report them in the issues. I am really trying to push for stability and then possibly push to get the adapter portion pulled into ecto_sql.


Thanks for working on exqlite :heart: (and to all the contributors/testers)! I just used it to integrate a simple album art banning feature on my Pandora gui that run on a Raspberry Pi, via nerves so exqlite was a great fit. Didn’t run into any issues using it yet (besides a weird telemetry error, but that’s probably not related to exqlite). I’m quite excited to have a workable ecto3-compatible sqlite library :tada:


Just keep an eye out on updates, I am hunting down bugs and trying to clean it up.

Also note, I am publishing updates to hex so you don’t have to depend on github to build.


I’m not sure if possible, but it seems Ecto.Sandbox is not (yet) supported. Any plans to do so? There are no errors, but concurrent tests can see writes of other tests.

1 Like

amazing work :tada: - will give it a spin straight away…

Were you hitting “busy” error? We just fixed an issue related to that, which should now be out with 0.3.5. Using the Sandbox should be more stable now and is definitely something we aim to improve support of.

If you hit any issues, feel free to file an issue in the repo.

1 Like

If anyone here has access to a Windows dev environment, I would like to see if this library at least compiles and functions there.

1 Like

The core driver and its adapter are coming along nicely. Only a couple more tests left to onboard of the entire ecto and ecto_sql integration test suite.

I ran some benchmarks for exqlite comparing it to postgrex and myxql and found that it does much better than I expect, having up to 10x speed increase for inserts, for instance. This is far more than I expected, to the point where I think I may have ran something wrong, but it looks like everything is set up correctly.