View Source Ecto.Adapters.SQL (Ecto SQL v3.8.1)
This application provides functionality for working with
SQL databases in Ecto
.
built-in-adapters
Built-in adapters
By default, we support the following adapters:
Ecto.Adapters.Postgres
for PostgresEcto.Adapters.MyXQL
for MySQLEcto.Adapters.Tds
for SQLServer
additional-functions
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 forEcto.Adapters.SQL.disconnect_all/3
explain(type, query, options \\ [])
- shortcut forEcto.Adapters.SQL.explain/4
query(sql, params, options \\ [])
- shortcut forEcto.Adapters.SQL.query/4
query!(sql, params, options \\ [])
- shortcut forEcto.Adapters.SQL.query!/4
query_many(sql, params, options \\ [])
- shortcut forEcto.Adapters.SQL.query_many/4
query_many!(sql, params, options \\ [])
- shortcut forEcto.Adapters.SQL.query_many!/4
to_sql(type, query)
- shortcut forEcto.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 direcltly from Ecto.Adapters.SQL
, but
keep in mind that in such cases features such as "dynamic repositories"
won't be available.
migrations
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 the
change
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 statusmix ecto.migrate
- runs a migrationmix ecto.rollback
- rolls back a previously run migration
If you want to run migrations programmatically, see Ecto.Migrator
.
sql-sandbox
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
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 databasemix ecto.dump
- dumps the existing database structure to the filesystem
For creating and dropping databases, see mix ecto.create
and mix ecto.drop
that are included as part of Ecto.
custom-adapters
Custom adapters
Developers can implement their own SQL adapters by using
Ecto.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
Link to this section 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.
Same as query/4
but raises on invalid queries.
Runs a custom SQL query on the given repo.
Same as query_many/4
but raises on invalid queries.
Runs a custom SQL query that returns multiple results on the given repo.
Returns a stream that runs a custom SQL query on given repo when reduced.
Check if the given table
exists.
Converts the given query to SQL according to its kind and the adapter in the given repository.
Link to this section Functions
@spec disconnect_all( pid() | Ecto.Repo.t() | Ecto.Adapter.adapter_meta(), non_neg_integer(), opts :: Keyword.t() ) :: :ok
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
@spec 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
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
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 |
MyXQL | None |
Postgrex: Check PostgreSQL doc for version compatibility.
MyXQL: EXTENDED
and PARTITIONS
opts were deprecated
and are enabled by default.
Also note that:
Currently
:map
,:yaml
, and:text
format options are supported for PostgreSQL.:map
is the deserialized JSON encoding. The last two options return the result as a string;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.
@spec query!( Ecto.Repo.t() | Ecto.Adapter.adapter_meta(), iodata(), [term()], Keyword.t() ) :: %{ :rows => nil | [[term()] | binary()], :num_rows => non_neg_integer(), optional(atom()) => any() }
Same as query/4
but raises on invalid queries.
@spec query( pid() | Ecto.Repo.t() | Ecto.Adapter.adapter_meta(), iodata(), [term()], Keyword.t() ) :: {:ok, %{ :rows => nil | [[term()] | binary()], :num_rows => non_neg_integer(), optional(atom()) => any() }} | {:error, Exception.t()}
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 adelete
command without returning would)
options
Options
:log
- When false, does not log the query
examples
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}}
@spec query_many!( Ecto.Repo.t() | Ecto.Adapter.adapter_meta(), iodata(), [term()], Keyword.t() ) :: [ %{ :rows => nil | [[term()] | binary()], :num_rows => non_neg_integer(), optional(atom()) => any() } ]
Same as query_many/4
but raises on invalid queries.
@spec query_many( pid() | Ecto.Repo.t() | Ecto.Adapter.adapter_meta(), iodata(), [term()], Keyword.t() ) :: {:ok, [ %{ :rows => nil | [[term()] | binary()], :num_rows => non_neg_integer(), optional(atom()) => any() } ]} | {:error, Exception.t()}
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 adelete
command without returning would)
options
Options
:log
- When false, does not log the query
examples
Examples
iex> Ecto.Adapters.SQL.query_many(MyRepo, "SELECT $1; SELECT $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 $1; SELECT $2;", [40, 2])
{:ok, [%{rows: [[40]], num_rows: 1}, %{rows: [[2]], num_rows: 1}]}
@spec 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 adelete
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
Options
:log
- When false, does not log the query:max_rows
- The number of rows to load from the database as we stream
examples
Examples
iex> Ecto.Adapters.SQL.stream(MyRepo, "SELECT $1::integer + $2", [40, 2]) |> Enum.to_list()
[%{rows: [[42]], num_rows: 1}]
@spec 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.
@spec 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
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", []}