{"id":4766,"date":"2015-06-10T09:00:46","date_gmt":"2015-06-10T12:00:46","guid":{"rendered":"http:\/\/blog.plataformatec.com.br\/?p=4766"},"modified":"2015-06-09T15:04:19","modified_gmt":"2015-06-09T18:04:19","slug":"adding-mysql-support-to-ecto","status":"publish","type":"post","link":"https:\/\/blog.plataformatec.com.br\/2015\/06\/adding-mysql-support-to-ecto\/","title":{"rendered":"Adding MySQL support to Ecto"},"content":{"rendered":"

A few months ago, I started learning Elixir by reading and doing the exercises from Dave Thomas\u2019 book \u2018Programming Elixir\u2019. I was having a lot of fun on it, but I was missing a real project to put my hands on.<\/p>\n

At that time, Jos\u00e9 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\u2019s codebase and then he gave me an introduction to where I should focus. I was able to send two Pull Requests to Elixir\u2019s codebase to add timeouts to tests<\/a> and set timeout to infinity in trace mode<\/a>. 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\u2019t think twice and told him I was going to work on it.<\/p>\n

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

Ecto is a database wrapper and language integrated query<\/a> for Elixir. It supports many features, like migrations, associations and more.<\/p>\n

Migrations on Ecto look like this:<\/p>\n

defmodule MyApp.Repo.Migrations.CreateLists do\n  use Ecto.Migration\n\n  def change do\n    create table(:lists) do\n      add :name, :string\n\n      timestamps\n    end\n  end\nend\n<\/code><\/pre>\n
and associations:<\/p>\n
defmodule MyApp.List do\n  use Ecto.Model\n\n  schema \"lists\" do\n    has_many :tasks, Task\n\n    field :name, :string\n    timestamps\n  end\nend\n\ndefmodule MyApp.Task do\n  use Ecto.Model\n\n  schema \"tasks\" do\n    belongs_to :list, List\n\n    field :description, :string\n    timestamps\n  end\nend\n<\/code><\/pre>\n
this is how queries work:<\/p>\n
select = from l in MyApp.List,\n          where: l.id = 10,\n          select: l.name\n\nupdate = from(l in MyApp.List, where: l.id == 1)\n<\/code><\/pre>\n
After you define your models and your queries, you can run them against a repository:<\/p>\n
# The examples here are using the query defined above\n\nMyApp.Repo.all(select)\nMyApp.Repo.update_all(update)\n<\/code><\/pre>\n
Cool, right? But how to make all these features run on MySQL too?<\/p>\n
Well, I didn\u2019t start from nothing. I started from an open Pull Request<\/a> 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.<\/p>\n
We had to make the connection work and support the basic DDL (Data Definition Language) statements, such as CREATE<\/code> tables and indexes, DROP<\/code> and so on. It was quite easy since Ecto has a well-defined interface that must be implemented by the adapters.<\/p>\n
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\u2019t have support for it<\/a>, so I focused on porting the queries to MySQL module and its unit tests until prepared statements got supported.<\/p>\n
The unit tests were all passing, but they didn\u2019t 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\u2019s where things started getting complicated.<\/p>\n
First problem found was that MySQL doesn\u2019t 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<\/a> and only run migrations inside a transaction if the current adapter supports it.<\/p>\n
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\u2019t find where the problem was. After some time looking into it (pairing with Jos\u00e9) we discovered that the problem was the driver that was not encoding the parameters.<\/p>\n
The prepared statements only had support for basic types (Integer<\/code>, String<\/code> and Binary<\/code>), so we had to add support for Date<\/code>, Datetime<\/code>, Timestamp<\/code><\/a>, Decimal<\/code>, Float<\/code>, Double<\/code><\/a> and Boolean<\/code><\/a>. 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\u2019t complete.<\/p>\n
I was making progress but the Ecto development didn\u2019t stop. I had to port some<\/a> features<\/a> while trying to make the integration tests pass and one of those changes required more<\/a> work<\/a> on Mariaex, which was the change to add support for microseconds.<\/p>\n
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.<\/p>\n
If you want more detail about the implementation itself, you can check my Pull Request<\/a> 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\u00e9 Valim<\/a>, Joe Quadrino<\/a> and Dmitry Aleksandrov<\/a>.<\/p>\n

\n  \"Subscribe
\n<\/a><\/p>\n","protected":false},"excerpt":{"rendered":"
A few months ago, I started learning Elixir by reading and doing the exercises from Dave Thomas\u2019 book \u2018Programming Elixir\u2019. 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\u00e9 Valim was in Brazil and I had the chance of … \u00bb<\/a><\/p>\n","protected":false},"author":32,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"ngg_post_thumbnail":0,"footnotes":""},"categories":[1],"tags":[238,143],"aioseo_notices":[],"jetpack_sharing_enabled":true,"jetpack_featured_media_url":"","_links":{"self":[{"href":"https:\/\/blog.plataformatec.com.br\/wp-json\/wp\/v2\/posts\/4766"}],"collection":[{"href":"https:\/\/blog.plataformatec.com.br\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/blog.plataformatec.com.br\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/blog.plataformatec.com.br\/wp-json\/wp\/v2\/users\/32"}],"replies":[{"embeddable":true,"href":"https:\/\/blog.plataformatec.com.br\/wp-json\/wp\/v2\/comments?post=4766"}],"version-history":[{"count":14,"href":"https:\/\/blog.plataformatec.com.br\/wp-json\/wp\/v2\/posts\/4766\/revisions"}],"predecessor-version":[{"id":4780,"href":"https:\/\/blog.plataformatec.com.br\/wp-json\/wp\/v2\/posts\/4766\/revisions\/4780"}],"wp:attachment":[{"href":"https:\/\/blog.plataformatec.com.br\/wp-json\/wp\/v2\/media?parent=4766"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/blog.plataformatec.com.br\/wp-json\/wp\/v2\/categories?post=4766"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/blog.plataformatec.com.br\/wp-json\/wp\/v2\/tags?post=4766"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}