A few Ecto helpers that you might want to add to your .iex.exs file. Were created with PSQL in mind

ecto_iex_helpers.ex

import_if_available(Ecto.Query)
# alias MyApp.Repo

defmodule R do
  # Old habits die hard. Take a random of something. You don't care which.
  # > R.take(SomeSchema)
  def take(schema_or_query) do
    schema_or_query
    |> limit(1)
    |> Repo.one()
  end

  # Transforms an ecto query into a string that you can straight up copy paste into a psql console.
  # > R.to_sql |> IO.puts()
  def to_sql(query, opts \\ []) do
    Keyword.get(opts, :type, :all)
    |> Ecto.Adapters.SQL.to_sql(Repo, query)
    |> substitute_params()
    |> then(fn sql ->
      if Keyword.get(opts, :deobfuscate, true),
        do: deobfuscate_table_names(sql),
        else: sql
    end)
    |> then(fn sql ->
      if Keyword.get(opts, :pretty, false),
        do: prettify_sql!(sql),
        else: sql
    end)
  end

  # TODO: Add more cases?
  defp substitute_params({sql, subs}) do
    subs
    |> Enum.with_index(1)
    |> Enum.reduce(sql, fn {param, i}, acc ->
      Regex.replace(~r/\$#{i}(?!\d)/, acc, dump_param(param))
    end)
  end

  # This is might be broken for lists, as I dont think we can figure out the items type at this stage...
  # but it works for several scenarios so better this than nothing
  defp dump_param(list) when is_list(list) do
    list
    |> Enum.map(&cast_param/1)
    |> Enum.join(", ")
    |> then(&"'{#{&1}}'")
  end

  defp dump_param(param) when is_integer(param) or is_float(param) or param in [true, false] do
    param
    |> cast_param()
    |> to_string()
  end
  
  defp dump_param(quoted) do
    "'#{cast_param(quoted)}'"
  end

  defp cast_param(<<_::128>> = raw_uuid), do: Ecto.UUID.load!(raw_uuid)
  defp cast_param(param), do: param

  # Try deobfuscate table names so its easier to read.
  # from users as ss join user_roles as sss0 on ss.id = sss0.user_id
  # from users as u_0 join user_roles as ur_0 on u.id = u_r0.user_id
  # 
  # I imagine this causing name collisions sometimes, so you can disable this via options.
  #
  @alias_regex ~r/(?:FROM|JOIN) "?(?<table>[a-z_]+?)"? AS "?(?<alias>\w+?)[",\s]/
  defp deobfuscate_table_names(sql) do
    # De-obfustcate table names
    aliases = Regex.scan(@alias_regex, sql)

    table_aliases =
      aliases
      |> Enum.map(fn [_, table, _] -> table end)
      |> get_table_aliases()

    Enum.reduce(aliases, {sql, %{}}, fn [_, table, as], {sql, as_counts} ->
      {count, new_as_counts} =
        Map.get_and_update(as_counts, table, fn
          nil -> {0, 1}
          n -> {n, n + 1}
        end)

      new_as =
        Map.fetch!(table_aliases, table)
        |> Kernel.<>("_#{count}")

      new_sql = Regex.replace(~r/(?<=[\s\(])#{as}(?=[\s\.])/, sql, new_as)

      {new_sql, new_as_counts}
    end)
    |> then(&elem(&1, 0))
  end
  
  defp get_table_aliases(tables) do
    small_diff = fn s1, s2 ->
      String.myers_difference(s1, s2)
      |> Enum.reduce("", fn
        {:del, del}, diff -> diff <> del
        _, diff -> diff
      end)
      |> String.slice(-5..-1)
    end

    tables
    |> Enum.uniq()
    |> Enum.group_by(fn table ->
      table
      |> String.split("_")
      |> Enum.map(&String.first/1)
      |> Enum.join()
    end)
    |> Enum.reduce(%{}, fn
      {as, [table]}, acc ->
        Map.put(acc, table, as)

      {as, [tf | _] = tables}, acc ->
        tables
        |> Enum.map(&Regex.replace(~r/_/, &1, ""))
        |> Enum.chunk_every(2, 1)
        |> Enum.with_index()
        |> Enum.reduce(acc, fn
          {[t1, t2], idx}, acc ->
            small_diff.(t1, t2)
            |> then(&"#{as}_#{&1}_#{idx}")
            |> then(&Map.put(acc, t1, &1))

          {[tl], idx}, acc ->
            tf = Regex.replace(~r/_/, tf, "")

            small_diff.(tl, tf)
            |> then(&"#{as}_#{&1}_#{idx}")
            |> then(&Map.put(acc, tl, &1))
        end)
    end)
  end
  
  def prettify_sql!(sql_str) when is_binary(sql_str) do
    {:ok, res} = prettify_sql(sql_str)
    res
  end
  
  # NOOP, You can delete this body if you implement the one below
  def prettify_sql(sql_str) when is_binary(sql_str) do
    {:ok, sql_str}
  end

  # There's a distinct lack of elixir sql prettifiers, and I'm not telling you what to do,
  # but doing a curl here works surprisingly well if you use it responsibly.
  # I'm including a sample body, but you might need to tweak the function to your prettifier of choice
  # 
  # @pretty_url ""
  # def prettify_sql(sql_str) when is_binary(sql_str) do
  #   URI.encode_query(%{data: sql_str})
  #   |> then(&"curl -s -d '#{&1}' -X POST #{@pretty_url}")
  #   |> System.shell()
  #   |> then(fn
  #     {res, 0} -> {:ok, res}
  #     _ -> :error
  #   end)
  # end

  # Quick wrapper for Repo.explain
  # R.explain(query) |> IO.puts
  # R.explain(query, analyze: true) |> IO.puts
  def explain(query, opts \\ []) do
    Repo.explain(:all, query, opts)
  end
end