Ecto v1.1.0 Ecto.Adapters.SQL

Behaviour and implementation for SQL adapters.

The implementation for SQL adapter provides a pooled based implementation of SQL and also expose a query function to developers.

Developers that use Ecto.Adapters.SQL should implement a connection module with specifics on how to connect to the database and also how to translate the queries to SQL.

See Ecto.Adapters.Connection for connection processes and Ecto.Adapters.SQL.Query for the query semantics.

Summary

Functions

Starts a transaction for test

Runs custom SQL query on given repo

Same as query/4 but raises on invalid queries

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

Functions

begin_test_transaction(repo, opts \\ [])

Specs

begin_test_transaction(Ecto.Repo.t, Keyword.t) :: :ok

Starts a transaction for test.

This function work by starting a transaction and storing the connection back in the pool with an open transaction. On every test, we restart the test transaction rolling back to the appropriate savepoint.

IMPORTANT: Test transactions only work if the connection pool is Ecto.Adapters.SQL.Sandbox

Example

The first step is to configure your database to use the Ecto.Adapters.SQL.Sandbox pool. You set those options in your config/config.exs:

config :my_app, Repo,
  pool: Ecto.Adapters.SQL.Sandbox

Since you don’t want those options in your production database, we typically recommend to create a config/test.exs and add the following to the bottom of your config/config.exs file:

import_config "config/#{Mix.env}.exs"

Now with the test database properly configured, you can write transactional tests:

# At the end of your test_helper.exs
# From now, all tests happen inside a transaction
Ecto.Adapters.SQL.begin_test_transaction(TestRepo)

defmodule PostTest do
  # Tests that use the shared repository cannot be async
  use ExUnit.Case

  setup do
    # Go back to a clean slate at the beginning of every test
    Ecto.Adapters.SQL.restart_test_transaction(TestRepo)
    :ok
  end

  test "create comment" do
    assert %Post{} = TestRepo.insert!(%Post{})
  end
end

In some cases, you may want to start the test transaction only for specific tests and then roll it back. You can do it as:

defmodule PostTest do
  # Tests that use the shared repository cannot be async
  use ExUnit.Case

  setup_all do
    # Wrap this case in a transaction
    Ecto.Adapters.SQL.begin_test_transaction(TestRepo)

    # Roll it back once we are done
    on_exit fn ->
      Ecto.Adapters.SQL.rollback_test_transaction(TestRepo)
    end

    :ok
  end

  setup do
    # Go back to a clean slate at the beginning of every test
    Ecto.Adapters.SQL.restart_test_transaction(TestRepo)
    :ok
  end

  test "create comment" do
    assert %Post{} = TestRepo.insert!(%Post{})
  end
end
query(repo, sql, params, opts \\ [])

Specs

query(Ecto.Repo.t, String.t, [term], Keyword.t) ::
  {:ok, %{rows: nil | [tuple], num_rows: non_neg_integer}} |
  {: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}}
query!(repo, sql, params, opts \\ [])

Specs

query!(Ecto.Repo.t, String.t, [term], Keyword.t) ::
  %{rows: nil | [tuple], num_rows: non_neg_integer} |
  no_return

Same as query/4 but raises on invalid queries.

restart_test_transaction(repo, opts \\ [])

Specs

restart_test_transaction(Ecto.Repo.t, Keyword.t) :: :ok

Restarts a test transaction, see begin_test_transaction/2.

rollback_test_transaction(repo, opts \\ [])

Specs

rollback_test_transaction(Ecto.Repo.t, Keyword.t) :: :ok
to_sql(kind, repo, queryable)

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:

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

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