Building a new MySQL adapter for Ecto Part IV: Ecto Integration

• Wojtek Mach
• January 4th, 2019
• ecto, elixir, myxql

Welcome to the “Building a new MySQL adapter for Ecto” series:

• Part I: Hello World
• Part II: Encoding/Decoding
• Part III: DBConnection Integration
• Part IV: Ecto Integration (you’re here!)

After DBConnection integration we have a driver that should be usable on its own. The next step is to integrate it with Ecto so that we can:

• leverage Ecto (doh!) meaning, among other things, using changesets to cast and validate data before inserting it into the DB, composing queries instead of concatenating SQL strings, defining schemas that map DB data into Elixir structs, being able to run Mix tasks like mix ecto.create and mix ecto.migrate, and finally using Ecto SQL Sandbox to manage clean slate between tests
• tap into greater Ecto ecosystem: integration with the Phoenix Web framework, various pagination libraries, custom types, admin builders etc

Ecto Adapter

If you ever worked with Ecto, you’ve seen code like:

defmodule MyApp.Repo do
  use Ecto.Repo,
    adapter: Ecto.Adapters.MySQL,
    otp_app: :my_app
end

The adapter is a module that implements Ecto Adapter specifications:

• Ecto.Adapter - minimal API required from adapters
• Ecto.Adapter.Queryable - plan, prepare, and execute queries leveraging query cache
• Ecto.Adapter.Schema - insert, update, and delete structs as well as autogenerate IDs
• Ecto.Adapter.Storage - storage API used by e.g. mix ecto.create and mix ecto.drop
• Ecto.Adapter.Transaction - transactions API Adapters are required to implement at least Ecto.Adapter behaviour. The remaining behaviours are optional as some data stores don’t support transactions or creating/dropping the storage (e.g. some cloud services).

There’s also a separate Ecto SQL project which ships with its own set of adapter specifications on top of the ones from Ecto. Conveniently, it also includes a Ecto.Adapters.SQL module that we can use, which implements most of the callbacks and lets us worry mostly about generating appropriate SQL.

Ecto SQL Adapter

Let’s try using the Ecto.Adapters.SQL module:

defmodule MyXQL.EctoAdapter do
  use Ecto.Adapters.SQL,
    driver: :myxql,
    migration_lock: "FOR UPDATE"
end

When we compile it, we’ll get a bunch of warnings as we haven’t implemented any of the callbacks yet.

warning: function supports_ddl_transaction?/0 required by behaviour Ecto.Adapter.Migration is not implemented (in module MyXQL.EctoAdapter)
  lib/a.ex:1
warning: function MyXQL.EctoAdapter.Connection.all/1 is undefined (module MyXQL.EctoAdapter.Connection is not available)
  lib/a.ex:2
warning: function MyXQL.EctoAdapter.Connection.delete/4 is undefined (module MyXQL.EctoAdapter.Connection is not available)
  lib/a.ex:2
(...)

Notably, we get a module MyXQL.EctoAdapter.Connection is not available warning. The SQL adapter specification requires us to implement a separate connection module (see Ecto.Adapters.SQL.Connection behaviour) which will leverage, you guessed it, DBConnection. Let’s try that now and implement a couple of callbacks:

defmodule MyXQL.EctoAdapter.Connection do
  @moduledoc false
  @behaviour Ecto.Adapters.SQL.Connection

  @impl true
  def child_spec(opts) do
    MyXQL.child_spec(opts)
  end

  @impl true
  def prepare_execute(conn, name, sql, params, opts) do
    MyXQL.prepare_execute(conn, name, sql, params, opts)
  end
end

Since we’ve leveraged DBConnection in the MyXQL driver, these functions are simply delegating to driver. Let’s implement something a little bit more interesting.

Did you ever wonder how Ecto.Changeset.unique_constraint/3 is able to transform a SQL constraint violation failure into a changeset error? Turns out that unique_constriant/3 keeps a mapping between unique key constraint name and fields these errors should be reported on. The code that makes it work is executed in the repo and the adapter when the structs are persisted. In particular, the adapter should implement the Ecto.Adapters.SQL.Connection.to_constraints/1 callback. Let’s take a look:

iex> b Ecto.Adapters.SQL.Connection.to_constraints
@callback to_constraints(exception :: Exception.t()) :: Keyword.t()
Receives the exception returned by c:query/4.
The constraints are in the keyword list and must return the constraint type,
like :unique, and the constraint name as a string, for example:
    [unique: "posts_title_index"]
Must return an empty list if the error does not come from any constraint.

Let’s see how the constraint violation error looks exactly:

$ mysql -u root myxql_test
mysql> CREATE TABLE uniques (x INTEGER UNIQUE);
Query OK, 0 rows affected (0.17 sec)
mysql> INSERT INTO uniques VALUES (1);
Query OK, 1 row affected (0.08 sec)
mysql> INSERT INTO uniques VALUES (1);
ERROR 1062 (23000): Duplicate entry '1' for key 'x'

MySQL responds with error code 1062. We can further look into the error by using perror command-line utility that ships with MySQL installation:

$ perror 1062
MySQL error code 1062 (ER_DUP_ENTRY): Duplicate entry '%-.192s' for key %d

Ok, let’s finally implement the callback:

defmodule MyXQL.EctoAdapter.Connection do
  # ...

  @impl true
  def to_constraints(%MyXQL.Error{mysql: %{code: 1062}, message: message}) do
    case :binary.split(message, " for key ") do
      [_, quoted] -> [unique: strip_quotes(quoted)]
      _ -> []
    end
  end
end

Let’s break this down. We expect that the driver raises an exception struct on constraint violation, we then match on the particular error code, extract the field name from the error message, and return that as keywords list.

(To make this more understandable, in the MyXQL project we’ve added error code/name mapping so we pattern match like this instead: mysql: %{code: :ER_DUP_ENTRY}.)

To get a feeling of what other subtle changes we may have between data stores, let’s implement one more callback, back in the MyXQL.EctoAdapter module.

While MySQL has a BOOLEAN type, turns out it’s simply an alias to TINYINT and its possible values are 1 and 0. These sort of discrepancies are handled by the dumpers/2 and loaders/2 callbacks, let’s implement the latter:

defmodule MyXQL.EctoAdapter do
  # ...

  @impl true
  def loaders(:boolean, type), do: [&bool_decode/1, type]
  # ...
  def loaders(_, type),        do: [type]

  defp bool_decode(<<0>>), do: {:ok, false}
  defp bool_decode(<<1>>), do: {:ok, true}
  defp bool_decode(0), do: {:ok, false}
  defp bool_decode(1), do: {:ok, true}
  defp bool_decode(other), do: {:ok, other}
end

Integration Tests

As you can see there might be quite a bit of discrepancies between adapters and data stores. For this reason, besides providing adapter specifications, Ecto ships with integration tests that can be re-used by adapter libraries.

Here’s a set of basic integration test cases and support files in Ecto, see: ./integration_test/ directory.

And here’s an example how a separate package might leverage these. Turns out that ecto_sql uses ecto integration tests:

# ecto_sql/integration_test/mysql/all_test.exs
ecto = Mix.Project.deps_paths[:ecto]
Code.require_file "#{ecto}/integration_test/cases/assoc.exs", __DIR__
Code.require_file "#{ecto}/integration_test/cases/interval.exs", __DIR__
# ...

and has a few of its own.

When implementing a 3rd-party SQL adapter for Ecto we already have a lot of integration tests to run against!

Conclusion

In this article we have briefly looked at integrating our driver with Ecto and Ecto SQL.

Ecto helps with the integration by providing:

• adapter specifications
• a Ecto.Adapters.SQL module that we can use to build adapters for relational databases even faster
• integration tests

We’re also concluding our adapter series. Some of the overarching themes were:

• separation of concerns: we’ve built our protocol packet encoding/decoding layer stateless and separate from a process model which in turn made DBConnection integration more straight-forward and resulting codebase easier to understand. Ecto also exhibits a separation of concerns: not only we have separate changeset, repo, adapter etc, within adapter we have different aspects of talking to data stores like storage, transactions, connection etc.
• behaviours, behaviours, behaviours! Not only behaviours provide a thought-through way of organizing the code as contracts, as long as we adhere to those contracts, features like e.g. DBConnection resilience and access to Ecto tooling and greater ecosystem becomes avaialble. As this article is being published, we’re getting closer to shipping MyXQL’s first release as well as making it the default MySQL adapter in upcoming Ecto v3.1. You can see the progress on elixir-ecto/ecto_sql#66.

Happy coding!

P.S.: This post was originally published on Plataformatec’s blog.