Elixir: Ecto

Runs an Ecto migration (schema_migrations, locks), inserts through a schema and counts with an Ecto query. The code is the same against the original database and against MachSQL; only the address changes.

The only change

DATABASE_URL=ecto://app:${DB_PASSWORD}@old-postgres:5432/app
DATABASE_URL=ecto://app:${DB_PASSWORD}@machsql-host:5432/app

Nothing else changes: not the code, not the driver, not its version, not the SQL.

Versions

• Elixir 1.20 on Erlang/OTP 29
• ecto_sql 3.14, postgrex 0.22

Run

mix deps.get
export DATABASE_URL=ecto://app:${DB_PASSWORD}@machsql-host:5432/app
mix ecto.migrate
mix run priv/demo.exs

Expected output: inserted todo <id>, <n> open.

Source

The program as it is kept and tested in the Mach Database for PostgreSQL repository; highlighted lines read the connection setting.

config/runtime.exs

import Config

# The only value that changes is the host inside DATABASE_URL.
config :todo_example, TodoExample.Repo, url: System.fetch_env!("DATABASE_URL")

config/config.exs

import Config

config :todo_example, ecto_repos: [TodoExample.Repo]

lib/todo_example/application.ex

defmodule TodoExample.Application do
  use Application

  @impl true
  def start(_type, _args) do
    Supervisor.start_link([TodoExample.Repo], strategy: :one_for_one, name: TodoExample.Supervisor)
  end
end

lib/todo_example/repo.ex

defmodule TodoExample.Repo do
  use Ecto.Repo, otp_app: :todo_example, adapter: Ecto.Adapters.Postgres
end

lib/todo_example/todo.ex

defmodule TodoExample.Todo do
  use Ecto.Schema

  schema "ecto_todos" do
    field :title, :string
    field :done, :boolean, default: false
  end
end

mix.exs

defmodule TodoExample.MixProject do
  use Mix.Project

  def project do
    [app: :todo_example, version: "0.1.0", elixir: "~> 1.20", deps: deps()]
  end

  def application do
    [mod: {TodoExample.Application, []}, extra_applications: [:logger]]
  end

  defp deps do
    [{:ecto_sql, "~> 3.14"}, {:postgrex, "~> 0.22"}]
  end
end

priv/demo.exs

import Ecto.Query

alias TodoExample.{Repo, Todo}

todo = Repo.insert!(%Todo{title: "write the examples"})
open = Repo.aggregate(from(t in Todo, where: not t.done), :count)
IO.puts("inserted todo #{todo.id}, #{open} open")

priv/repo/migrations/20261030000000_create_todos.exs

defmodule TodoExample.Repo.Migrations.CreateTodos do
  use Ecto.Migration

  def change do
    create table(:ecto_todos) do
      add :title, :string, null: false
      add :done, :boolean, null: false, default: false
    end
  end
end