Ecto SQL v3.2.0 Ecto.Adapters.SQL View Source
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 PostgresEcto.Adapters.MyXQL
for MySQL
Migrations
Ecto 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 programatically, 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 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
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
:migration_lock
- the lock to use on migration locks. For example: "FOR UPDATE". It may also benil
(for no lock). The user can still override this by setting:migration_lock
in the repository configuration
Link to this section Summary
Link to this section Functions
query(repo, sql, params \\ [], opts \\ [])
View Source
query(
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()}
query( 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 adelete
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}}
query!(repo, sql, params \\ [], opts \\ [])
View Source
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()
}
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.
stream(repo, sql, params \\ [], opts \\ [])
View Source
stream(Ecto.Repo.t(), String.t(), [term()], Keyword.t()) :: Enum.t()
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
: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}]
table_exists?(repo, table)
View Source
table_exists?(Ecto.Repo.t(), table :: String.t()) :: boolean()
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.
to_sql(kind, repo, queryable)
View Source
to_sql(:all | :update_all | :delete_all, Ecto.Repo.t(), Ecto.Queryable.t()) ::
{String.t(), [term()]}
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", []}