Ecto SQL v3.0.0-rc.1 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:

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 status
mix ecto.migrate - runs a migration
mix 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 database
mix ecto.rollback - 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 implementing the callbacks required by this module and the ones from Ecto.Adapters.SQL.Connection for handling connections and performing queries. The connection handling and pooling for SQL adapter should be built using the DBConnection library.

Link to this section Summary

Functions

query!(repo, sql, params \\ [], opts \\ [])

Same as query/4 but raises on invalid queries

query(repo, sql, params \\ [], opts \\ [])

Runs custom SQL query on given repo

stream(repo, sql, params \\ [], opts \\ [])

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

to_sql(kind, repo, queryable)

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

Link to this section Functions

query!(repo, sql, params \\ [], opts \\ [])

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.

query(repo, sql, params \\ [], opts \\ [])

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 a delete command without returning would)

Options

:timeout - The time in milliseconds to wait for a query to finish, :infinity will wait indefinitely. (default: 15_000)
:pool_timeout - The time in milliseconds to wait for a call to the pool to finish, :infinity will wait indefinitely. (default: 5_000)
: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}}

stream(repo, sql, params \\ [], opts \\ [])

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

:timeout - The time in milliseconds to wait for a query to finish, :infinity will wait indefinitely (default: 15_000)
:pool_timeout - The time in milliseconds to wait for a call to the pool to finish, :infinity will wait indefinitely (default: 5_000)
: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}]

to_sql(kind, repo, queryable)

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