I’m especially interested using sqlite3 in my apps. I have tested it on myself:
ecto_example.exs
Mix.install([:ecto_sql, {:exqlite, github: "warmwaffles/exqlite"}])
defmodule Repo do
use Ecto.Repo, adapter: Ecto.Adapters.Exqlite, otp_app: :my_app
end
defmodule Migration do
use Ecto.Migration
def change do
create table("todo_lists") do
add(:title, :string)
timestamps()
end
create table("todo_items") do
add(:description, :string)
add(:type, :string)
timestamps()
end
create table("todo_list_items") do
add(:todo_item_id, references(:todo_items))
add(:todo_list_id, references(:todo_lists))
end
end
end
defmodule TodoList do
use Ecto.Schema
schema "todo_lists" do
field(:title)
many_to_many(:todo_items, TodoItem, join_through: TodoListItem)
timestamps()
end
end
defmodule TodoListItem do
use Ecto.Schema
schema "todo_list_items" do
belongs_to(:todo_list, TodoList)
belongs_to(:todo_item, TodoItem)
end
end
defmodule TodoItem do
use Ecto.Schema
schema "todo_items" do
field(:description, :string)
field(:type, :string)
timestamps()
end
end
defmodule Example do
alias Ecto.Query
require Query
def cleanup do
Repo.stop()
end
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")
)
Application.ensure_all_started(:ecto_sql)
Application.ensure_all_started(:postgrex)
Repo.__adapter__().storage_down(Repo.config())
Repo.__adapter__().storage_up(Repo.config())
Repo.start_link()
Ecto.Migrator.up(Repo, 1, Migration)
end
def sample do
TodoList
|> 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()
end
def seed do
Repo.insert(%TodoList{
title: "Hobby example",
todo_items: [%{description: "hobby1", type: "hobby"}]
})
Repo.insert(%TodoList{
title: "Job example",
todo_items: [%{description: "job1", type: "job"}, %{description: "job2", type: "job"}]
})
end
end
Example.prepare()
Example.seed()
Example.sample()
Example.cleanup()
elixir ecto_example.exs
* Getting exqlite (https://github.com/warmwaffles/exqlite.git)
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:
New:
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,libsqlite3.so.0 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/sqlite3_nif.so
Compiling 13 files (.ex)
warning: unused import String
lib/ecto/adapters/exqlite.ex:5
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.id, t0.title, t0.inserted_at, t0.updated_at, t1.id, 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 = t0.id INNER JOIN todo_items AS t1 ON t2.todo_item_id = t1.id WHERE (t1.type != 'hobby') []
[
%TodoList{
__meta__: #Ecto.Schema.Metadata<:loaded, "todo_lists">,
id: 2,
inserted_at: ~N[2021-03-01 03:38:54],
title: "Job example",
todo_items: [
%TodoItem{
__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]
},
%TodoItem{
__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! 
