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()

* 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]
  }
]