Specs

explain(
  pid() | Ecto.Repo.t() | Ecto.Adapter.adapter_meta(),
  :all | :update_all | :delete_all,
  Ecto.Queryable.t(),
  opts :: Keyword.t()
) :: String.t() | Exception.t()

Executes an EXPLAIN statement or similar for the given query according to its kind and the adapter in the given repository.

Examples

# Postgres
iex> Ecto.Adapters.SQL.explain(Repo, :all, Post)
"Seq Scan on posts p0  (cost=0.00..12.12 rows=1 width=443)"

# MySQL
iex> Ecto.Adapters.SQL.explain(Repo, :all, from(p in Post, where: p.title == "title")) |> IO.puts()
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | p0    | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    1 |    100.0 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+

# Shared opts
iex> Ecto.Adapters.SQL.explain(Repo, :all, Post, analyze: true, timeout: 20_000)
"Seq Scan on posts p0  (cost=0.00..11.70 rows=170 width=443) (actual time=0.013..0.013 rows=0 loops=1)\nPlanning Time: 0.031 ms\nExecution Time: 0.021 ms"

It's safe to execute it for updates and deletes, no data change will be commited:

iex> Ecto.Adapters.SQL.explain(Repo, :update_all, from(p in Post, update: [set: [title: "new title"]]))
"Update on posts p0  (cost=0.00..11.70 rows=170 width=449)\n  ->  Seq Scan on posts p0  (cost=0.00..11.70 rows=170 width=449)"

This function is also available under the repository with name explain:

iex> Repo.explain(:all, from(p in Post, where: p.title == "title"))
"Seq Scan on posts p0  (cost=0.00..12.12 rows=1 width=443)\n  Filter: ((title)::text = 'title'::text)"

Options

Built-in adapters support passing opts to the EXPLAIN statement according to the following:

Postgrex: Check PostgreSQL doc for version compatibility.

MyXQL: EXTENDED and PARTITIONS opts were deprecated and are enabled by default.

Also note that:

• FORMAT isn't supported at the moment and the only possible output is a textual format, so you may want to call IO.puts/1 to display it;
• Any other value passed to opts will be forwarded to the underlying adapter query function, including Repo shared options such as :timeout;
• Non built-in adapters may have specific behavior and you should consult their own documentation.

Specs

format_table(%{columns: [String.t()] | nil, rows: [term()] | nil}) :: String.t()

Returns a formatted table for a given query result.

Examples

iex> Ecto.Adapters.SQL.format_table(query) |> IO.puts()
+---------------+---------+--------+
| title         | counter | public |
+---------------+---------+--------+
| My Post Title |       1 | NULL   |
+---------------+---------+--------+

Specs

query(
  pid() | Ecto.Repo.t() | Ecto.Adapter.adapter_meta(),
  String.t(),
  [term()],
  Keyword.t()
) ::
  {:ok,
   %{
     :rows => nil | [[term()] | binary()],
     :num_rows => non_neg_integer(),
     optional(atom()) => any()
   }}
  | {:error, Exception.t()}

Runs custom SQL query on given repo.

In case of success, it must return an :ok tuple containing a map with at least two keys:

• :num_rows - the number of rows affected

• :rows - the result set as a list. nil may be returned instead of the list if the command does not yield any row as result (but still yields the number of affected rows, like a delete command without returning would)

Options

• :log - When false, does not log the query

Examples

iex> Ecto.Adapters.SQL.query(MyRepo, "SELECT $1::integer + $2", [40, 2])
{:ok, %{rows: [[42]], num_rows: 1}}

For convenience, this function is also available under the repository:

iex> MyRepo.query("SELECT $1::integer + $2", [40, 2])
{:ok, %{rows: [[42]], num_rows: 1}}

Specs

query!(
  Ecto.Repo.t() | Ecto.Adapter.adapter_meta(),
  String.t(),
  [term()],
  Keyword.t()
) :: %{
  :rows => nil | [[term()] | binary()],
  :num_rows => non_neg_integer(),
  optional(atom()) => any()
}

Same as query/4 but raises on invalid queries.

Specs

stream(Ecto.Repo.t(), String.t(), [term()], Keyword.t()) :: Enum.t()

Returns a stream that runs a custom SQL query on given repo when reduced.

In case of success it is a enumerable containing maps with at least two keys:

• :num_rows - the number of rows affected

• :rows - the result set as a list. nil may be returned instead of the list if the command does not yield any row as result (but still yields the number of affected rows, like a delete command without returning would)

In case of failure it raises an exception.

If the adapter supports a collectable stream, the stream may also be used as the collectable in Enum.into/3. Behaviour depends on the adapter.

Options

• :log - When false, does not log the query
• :max_rows - The number of rows to load from the database as we stream

Examples

iex> Ecto.Adapters.SQL.stream(MyRepo, "SELECT $1::integer + $2", [40, 2]) |> Enum.to_list()
[%{rows: [[42]], num_rows: 1}]

Specs

table_exists?(Ecto.Repo.t(), table :: String.t()) :: boolean()

Check if the given table exists.

Returns true if the table exists in the repo, otherwise false. The table is checked against the current database/schema in the connection.

Specs

to_sql(:all | :update_all | :delete_all, Ecto.Repo.t(), Ecto.Queryable.t()) ::
  {String.t(), [term()]}

Converts the given query to SQL according to its kind and the adapter in the given repository.

Examples

The examples below are meant for reference. Each adapter will return a different result:

iex> Ecto.Adapters.SQL.to_sql(:all, Repo, Post)
{"SELECT p.id, p.title, p.inserted_at, p.created_at FROM posts as p", []}

iex> Ecto.Adapters.SQL.to_sql(:update_all, Repo,
                              from(p in Post, update: [set: [title: ^"hello"]]))
{"UPDATE posts AS p SET title = $1", ["hello"]}

This function is also available under the repository with name to_sql:

iex> Repo.to_sql(:all, Post)
{"SELECT p.id, p.title, p.inserted_at, p.created_at FROM posts as p", []}