Ecto

Mix.install([
  {:ecto, "~> 3.12"},
  {:ecto_sql, "~> 3.12"},
  {:postgrex, github: "elixir-ecto/postgrex", override: true},
  {:kino, "~> 0.11.0"},
  {:kino_db, "~> 0.2.4"},
  {:sqlcommenter, path: "/home/kuku/Projects/sqlcommenter"}
])

Setup

# docker run --rm -it -e POSTGRES_USER=postgres -e POSTGRES_PASSWORD=postgres -p 5432:5432 postgres:13.2

Repo

defmodule Test.Repo do
  use Sqlcommenter.Repo,
    otp_app: :my_app,
    adapter: Ecto.Adapters.Postgres,
    sqlcommenter: [app: "test_app", owner: "team_c"]
end

defmodule Test.MigrationRepo do
  use Ecto.Repo,
    otp_app: :my_app,
    adapter: Ecto.Adapters.Postgres,
    priv: "priv/repo"
  end

Kino.start_child({Test.Repo, url: "postgres://postgres:postgres@localhost/postgres"})

Kino.start_child({Test.MigrationRepo, url: "postgres://postgres:postgres@localhost/postgres"})

Queries

Ecto.Adapters.SQL.query!(Test.Repo, "create table if not exists users (id int)", [], comment: "123")

Ecto.Adapters.SQL.query!(Test.Repo, "insert into users values (1)")

Ecto.Adapters.SQL.query!(Test.Repo, "select * from users")

Ecto migration

defmodule Migrations.AddWeatherTable do
  use Ecto.Migration

  def up do
    create table("weather") do
      add(:city, :string, size: 40)
      add(:temp_lo, :integer)
      add(:temp_hi, :integer)
      add(:prcp, :float)

      timestamps()
    end
  end

  def down do
    drop(table("weather"))
  end
end

Ecto.Migrator.up(Test.MigrationRepo, 1, Migrations.AddWeatherTable)

Schema

defmodule Weather do
  use Ecto.Schema

  schema "weather" do
    field(:city, :string)
    field(:temp_lo, :integer)
    field(:temp_hi, :integer)
    field(:prcp, :float, default: 0.0)

    timestamps()
  end
end

defmodule W do
  require Test.Repo
  alias Test.Repo
  import Ecto.Query

  def insert do
    weather1 = %Weather{temp_lo: 0, temp_hi: 23}
    weather2 = %Weather{temp_lo: 2, temp_hi: 23}
    Repo.insert!(weather1)
    Repo.insert!(weather2)
  end

  def get_query do
    values = [%{id: 1, name: "Zabrze"}, %{id: 2, name: "Dudley"}]
    Weather
    |> join(:inner, [w], w1 in values(values, %{id: :integer, name: :string}), on: w.id == w1.id)
    |> select([w, c], %Weather{id: w.id, city: c.name})
    |> Repo.all()
  end

  def get_schema do
    Weather
    |> select([w], %Weather{id: w.id})
    |> where([w], not is_nil(w.id))
    |> Repo.all()
  end
end

W.insert()

W.get_query()

W.get_schema()