A few months ago, I started learning Elixir by reading and doing the exercises from Dave Thomas’ book ‘Programming Elixir’. I was having a lot of fun on it, but I was missing a real project to put my hands on.

At that time, José Valim was in Brazil and I had the chance of working with him for a whole day. In the first hour, he told me that there were some low hanging fruits on Elixir’s codebase and then he gave me an introduction to where I should focus. I was able to send two Pull Requests to Elixir’s codebase to add timeouts to tests and set timeout to infinity in trace mode. The result was great and I learned a lot. By the end of that day, he mentioned that Ecto was needing a MySQL adapter because it only had support for PostgreSQL. I didn’t think twice and told him I was going to work on it.

In this blog post, I will share what I learned while making the MySQL adapter for Ecto. Let’s start from the beginning.

Ecto is a database wrapper and language integrated query for Elixir. It supports many features, like migrations, associations and more.

Migrations on Ecto look like this:

defmodule MyApp.Repo.Migrations.CreateLists do
  use Ecto.Migration

  def change do
    create table(:lists) do
      add :name, :string

      timestamps
    end
  end
end

and associations:

defmodule MyApp.List do
  use Ecto.Model

  schema "lists" do
    has_many :tasks, Task

    field :name, :string
    timestamps
  end
end

defmodule MyApp.Task do
  use Ecto.Model

  schema "tasks" do
    belongs_to :list, List

    field :description, :string
    timestamps
  end
end

this is how queries work:

select = from l in MyApp.List,
          where: l.id = 10,
          select: l.name

update = from(l in MyApp.List, where: l.id == 1)

After you define your models and your queries, you can run them against a repository:

# The examples here are using the query defined above

MyApp.Repo.all(select)
MyApp.Repo.update_all(update)

Cool, right? But how to make all these features run on MySQL too?

Well, I didn’t start from nothing. I started from an open Pull Request that had some initial work. It was setting up a database connection using the MySQL driver and creating/dropping MySQL databases from the command line.

We had to make the connection work and support the basic DDL (Data Definition Language) statements, such as CREATE tables and indexes, DROP and so on. It was quite easy since Ecto has a well-defined interface that must be implemented by the adapters.

After the initial setup, I got the integration tests to run, but I got stuck with prepared statements. It took some time until I found that Mariaex (the MySQL driver) didn’t have support for it, so I focused on porting the queries to MySQL module and its unit tests until prepared statements got supported.

The unit tests were all passing, but they didn’t cover complex situations, which were the responsibility of integration tests. When the basic support for prepared statements was added, I went back to the integration tests and that’s where things started getting complicated.

First problem found was that MySQL doesn’t support transactions on DDL operations and since Ecto runs the migrations inside a transaction, it was not working. To solve this particular problem, it was needed to check if the current adapter supports DDL transactions and only run migrations inside a transaction if the current adapter supports it.

All done with the DDL check, it was time to get back to the other failing cases when I got stuck with another hard problem. The migration tests were still failing and I couldn’t find where the problem was. After some time looking into it (pairing with José) we discovered that the problem was the driver that was not encoding the parameters.

The prepared statements only had support for basic types (Integer, String and Binary), so we had to add support for Date, Datetime, Timestamp, Decimal, Float, Double and Boolean. It was really hard to make it work, since I never had worked with binary protocols before and the MySQL documentation for the binary protocol isn’t complete.

I was making progress but the Ecto development didn’t stop. I had to port some features while trying to make the integration tests pass and one of those changes required more work on Mariaex, which was the change to add support for microseconds.

Today the MySQL adapter is working and you can use it on Ecto, but, as you can see, I spent more time adding features to the MySQL driver than working on Ecto itself. It was a great experience and I could learn a lot about how MySQL actually works.

If you want more detail about the implementation itself, you can check my Pull Request and you will see a detailed todo list with all the steps that were required. I also would like to thanks all the people involved in this work: José Valim, Joe Quadrino and Dmitry Aleksandrov.