Ecto.Adapters.SQL — Ecto SQL v3.12.1 (original) (raw)

This application provides functionality for working with SQL databases in Ecto.

Built-in adapters

By default, we support the following adapters:

Ecto.Adapters.Postgres for Postgres
Ecto.Adapters.MyXQL for MySQL
Ecto.Adapters.Tds for SQLServer

Additional functions

If your Ecto.Repo is backed by any of the SQL adapters above, this module will inject additional functions into your repository:

disconnect_all(interval, options \\ []) - shortcut for Ecto.Adapters.SQL.disconnect_all/3
explain(type, query, options \\ []) - shortcut for Ecto.Adapters.SQL.explain/4
query(sql, params, options \\ []) - shortcut for Ecto.Adapters.SQL.query/4
query!(sql, params, options \\ []) - shortcut for Ecto.Adapters.SQL.query!/4
query_many(sql, params, options \\ []) - shortcut for Ecto.Adapters.SQL.query_many/4
query_many!(sql, params, options \\ []) - shortcut for Ecto.Adapters.SQL.query_many!/4
to_sql(type, query) - shortcut for Ecto.Adapters.SQL.to_sql/3

Generally speaking, you must invoke those functions directly from your repository, for example: MyApp.Repo.query("SELECT true"). You can also invoke them directly from Ecto.Adapters.SQL, but keep in mind that in such cases features such as "dynamic repositories" won't be available.

Migrations

ecto_sql supports database migrations. You can generate a migration with:

$ mix ecto.gen.migration create_posts

This will create a new file inside priv/repo/migrations with thechange function. Check Ecto.Migration for more information.

To interface with migrations, developers typically use mix tasks:

mix ecto.migrations - lists all available migrations and their status
mix ecto.migrate - runs a migration
mix ecto.rollback - rolls back a previously run migration

If you want to run migrations programmatically, see Ecto.Migrator.

SQL sandbox

ecto_sql provides a sandbox for testing. The sandbox wraps each test in a transaction, making sure the tests are isolated and can run concurrently. See Ecto.Adapters.SQL.Sandbox for more information.

Structure load and dumping

If you have an existing database, you may want to dump its existing structure and make it reproducible from within Ecto. This can be achieved with two Mix tasks:

mix ecto.load - loads an existing structure into the database
mix ecto.dump - dumps the existing database structure to the filesystem

For creating and dropping databases, see mix ecto.createand mix ecto.drop that are included as part of Ecto.

Custom adapters

Developers can implement their own SQL adapters by usingEcto.Adapters.SQL and by implementing the callbacks required by Ecto.Adapters.SQL.Connection for handling connections and performing queries. The connection handling and pooling for SQL adapters should be built using the DBConnection library.

When using Ecto.Adapters.SQL, the following options are required:

:driver (required) - the database driver library. For example: :postgrex

Summary

Functions

Forces all connections in the repo pool to disconnect within the given interval.

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

Runs a custom SQL query on the given repo.

Same as query/4 but raises on invalid queries.

Runs a custom SQL query that returns multiple results on the given repo.

Same as query_many/4 but raises on invalid queries.

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

Checks if the given table exists.

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

Types

Functions

Forces all connections in the repo pool to disconnect within the given interval.

Once this function is called, the pool will disconnect all of its connections as they are checked in or as they are pinged. Checked in connections will be randomly disconnected within the given time interval. Pinged connections are immediately disconnected - as they are idle (according to :idle_interval).

If the connection has a backoff configured (which is the case by default), disconnecting means an attempt at a new connection will be done immediately after, without starting a new process for each connection. However, if backoff has been disabled, the connection process will terminate. In such cases, disconnecting all connections may cause the pool supervisor to restart depending on the max_restarts/max_seconds configuration of the pool, so you will want to set those carefully.

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

iex> MyRepo.disconnect_all(60_000)
:ok

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 committed:

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:

Adapter	Supported opts
Postgrex	analyze, verbose, costs, settings, buffers, timing, summary, format, plan
MyXQL	format

All options except format are boolean valued and default to false.

The allowed format values are :map, :yaml, and :text:

:map is the deserialized JSON encoding.
:yaml and :text return the result as a string.

The built-in adapters support the following formats:

Postgrex: :map, :yaml and :text
MyXQL: :map and :text

The :plan option in Postgrex can take the values :custom or :fallback_generic. When :customis specified, the explain plan generated will consider the specific values of the query parameters that are supplied. When using :fallback_generic, the specific values of the query parameters will be ignored. :fallback_generic does not use PostgreSQL's built-in support for a generic explain plan (available as of PostgreSQL 16), but instead uses a special implementation that works for PostgreSQL versions 12 and above. Defaults to :custom.

Any other value passed to opts will be forwarded to the underlying adapter query function, including shared Repo options such as :timeout. Non built-in adapters may have specific behaviour and you should consult their documentation for more details.

For version compatibility, please check your database's documentation:

Postgrex: PostgreSQL doc.
MyXQL: MySQL doc.

Runs a custom SQL query on the 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
:timeout - Execute request timeout, accepts: :infinity (default: 15000);

Examples

iex> Ecto.Adapters.SQL.query(MyRepo, "SELECT <span class="katex"><span class="katex-mathml"><math xmlns="http://www.w3.org/1998/Math/MathML"><semantics><mrow><mn>1</mn><mo>:</mo><mo>:</mo><mi>i</mi><mi>n</mi><mi>t</mi><mi>e</mi><mi>g</mi><mi>e</mi><mi>r</mi><mo>+</mo></mrow><annotation encoding="application/x-tex">1::integer + </annotation></semantics></math></span><span class="katex-html" aria-hidden="true"><span class="base"><span class="strut" style="height:0.6444em;"></span><span class="mord">1</span><span class="mspace" style="margin-right:0.2778em;"></span><span class="mrel">::</span><span class="mspace" style="margin-right:0.2778em;"></span></span><span class="base"><span class="strut" style="height:0.854em;vertical-align:-0.1944em;"></span><span class="mord mathnormal">in</span><span class="mord mathnormal">t</span><span class="mord mathnormal">e</span><span class="mord mathnormal" style="margin-right:0.03588em;">g</span><span class="mord mathnormal" style="margin-right:0.02778em;">er</span><span class="mord">+</span></span></span></span>2", [40, 2])
{:ok, %{rows: [[42]], num_rows: 1}}

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

iex> MyRepo.query("SELECT <span class="katex"><span class="katex-mathml"><math xmlns="http://www.w3.org/1998/Math/MathML"><semantics><mrow><mn>1</mn><mo>:</mo><mo>:</mo><mi>i</mi><mi>n</mi><mi>t</mi><mi>e</mi><mi>g</mi><mi>e</mi><mi>r</mi><mo>+</mo></mrow><annotation encoding="application/x-tex">1::integer + </annotation></semantics></math></span><span class="katex-html" aria-hidden="true"><span class="base"><span class="strut" style="height:0.6444em;"></span><span class="mord">1</span><span class="mspace" style="margin-right:0.2778em;"></span><span class="mrel">::</span><span class="mspace" style="margin-right:0.2778em;"></span></span><span class="base"><span class="strut" style="height:0.854em;vertical-align:-0.1944em;"></span><span class="mord mathnormal">in</span><span class="mord mathnormal">t</span><span class="mord mathnormal">e</span><span class="mord mathnormal" style="margin-right:0.03588em;">g</span><span class="mord mathnormal" style="margin-right:0.02778em;">er</span><span class="mord">+</span></span></span></span>2", [40, 2])
{:ok, %{rows: [[42]], num_rows: 1}}

Same as query/4 but raises on invalid queries.

Runs a custom SQL query that returns multiple results on the given repo.

In case of success, it must return an :ok tuple containing a list of 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)

Options

:log - When false, does not log the query
:timeout - Execute request timeout, accepts: :infinity (default: 15000);

Examples

iex> Ecto.Adapters.SQL.query_many(MyRepo, "SELECT <span class="katex"><span class="katex-mathml"><math xmlns="http://www.w3.org/1998/Math/MathML"><semantics><mrow><mn>1</mn><mo separator="true">;</mo><mi>S</mi><mi>E</mi><mi>L</mi><mi>E</mi><mi>C</mi><mi>T</mi></mrow><annotation encoding="application/x-tex">1; SELECT </annotation></semantics></math></span><span class="katex-html" aria-hidden="true"><span class="base"><span class="strut" style="height:0.8778em;vertical-align:-0.1944em;"></span><span class="mord">1</span><span class="mpunct">;</span><span class="mspace" style="margin-right:0.1667em;"></span><span class="mord mathnormal" style="margin-right:0.05764em;">SE</span><span class="mord mathnormal">L</span><span class="mord mathnormal" style="margin-right:0.13889em;">ECT</span></span></span></span>2;", [40, 2])
{:ok, [%{rows: [[40]], num_rows: 1}, %{rows: [[2]], num_rows: 1}]}

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

iex> MyRepo.query_many("SELECT <span class="katex"><span class="katex-mathml"><math xmlns="http://www.w3.org/1998/Math/MathML"><semantics><mrow><mn>1</mn><mo separator="true">;</mo><mi>S</mi><mi>E</mi><mi>L</mi><mi>E</mi><mi>C</mi><mi>T</mi></mrow><annotation encoding="application/x-tex">1; SELECT </annotation></semantics></math></span><span class="katex-html" aria-hidden="true"><span class="base"><span class="strut" style="height:0.8778em;vertical-align:-0.1944em;"></span><span class="mord">1</span><span class="mpunct">;</span><span class="mspace" style="margin-right:0.1667em;"></span><span class="mord mathnormal" style="margin-right:0.05764em;">SE</span><span class="mord mathnormal">L</span><span class="mord mathnormal" style="margin-right:0.13889em;">ECT</span></span></span></span>2;", [40, 2])
{:ok, [%{rows: [[40]], num_rows: 1}, %{rows: [[2]], num_rows: 1}]}

Same as query_many/4 but raises on invalid queries.

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 <span class="katex"><span class="katex-mathml"><math xmlns="http://www.w3.org/1998/Math/MathML"><semantics><mrow><mn>1</mn><mo>:</mo><mo>:</mo><mi>i</mi><mi>n</mi><mi>t</mi><mi>e</mi><mi>g</mi><mi>e</mi><mi>r</mi><mo>+</mo></mrow><annotation encoding="application/x-tex">1::integer + </annotation></semantics></math></span><span class="katex-html" aria-hidden="true"><span class="base"><span class="strut" style="height:0.6444em;"></span><span class="mord">1</span><span class="mspace" style="margin-right:0.2778em;"></span><span class="mrel">::</span><span class="mspace" style="margin-right:0.2778em;"></span></span><span class="base"><span class="strut" style="height:0.854em;vertical-align:-0.1944em;"></span><span class="mord mathnormal">in</span><span class="mord mathnormal">t</span><span class="mord mathnormal">e</span><span class="mord mathnormal" style="margin-right:0.03588em;">g</span><span class="mord mathnormal" style="margin-right:0.02778em;">er</span><span class="mord">+</span></span></span></span>2", [40, 2]) |> Enum.to_list()
[%{rows: [[42]], num_rows: 1}]

Checks 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.

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", []}