There was some great feedback from people regarding my post on getting Ecto in your non-Phoenix application (From Zero to Ecto in 10 Minutes). One of the questions I got was about using Ecto with existing databases. While using Ecto with a new database is a great way to start a new project, there are plenty of existing projects that could stand to have a little Elixir love thrown their way.

So let’s explore creating a new Elixir application that interacts with an existing database. Furthermore, while most tutorials show you how to use Ecto with Postgres, there are plenty of existing projects that use MySQL. Let’s make this more difficult and set up the application to access a MySQL database. Not difficult enough you say? Fine. Let’s also deal with an unusual primary key column name.

But really, all that isn’t hard at all with Elixir and Ecto. It only took me 6 minutes to get my basic application up and running.

Set up your project

For this sample project, I’m going to continue with data from one of my favorite sports: Formula 1 racing. There is a fantastic database online that contains the entire history of the sport and we are going to use that. If you’d like to follow along, I’m going to be working with the Ergast Motor Racing Developer API database dump.

MySQL

After getting the dump, create a database (I’m calling mine f1) and import that into your local MySQL database. I’m assuming, of course, that you have a local MySQL server running and can import the SQL dump. If not, there are plenty of tutorials out there to get you started.

Let’s take a quick peek at what the races table looks like:

mysql> use f1
mysql> describe races;
+-----------+--------------+------+-----+------------+----------------+
| Field     | Type         | Null | Key | Default    | Extra          |
+-----------+--------------+------+-----+------------+----------------+
| raceId    | int(11)      | NO   | PRI | NULL       | auto_increment |
| year      | int(11)      | NO   |     | 0          |                |
| round     | int(11)      | NO   |     | 0          |                |
| circuitId | int(11)      | NO   |     | 0          |                |
| name      | varchar(255) | NO   |     |            |                |
| date      | date         | NO   |     | 0000-00-00 |                |
| time      | time         | YES  |     | NULL       |                |
| url       | varchar(255) | YES  | UNI | NULL       |                |
+-----------+--------------+------+-----+------------+----------------+
8 rows in set (0.00 sec)

We can see here an output of the fields and types we are going to have to connect to in our application – and notice our primary key is named raceId. We’ll have to deal with that later.

Elixir

Now that we have that out of the way, we can finally get to some Elixir. The first thing we’ll need to do, of course, is create a new application with ecto and a supervisor. I’m going to name mine F1History. I wrote a detailed post about how to quickly get going in From Zero To Ecto In 10 Minutes. If you need some pointers on getting started, go there.

HOWEVER, we need to make a couple changes from that tutorial since we will be using MySQL instead of the default—Postgres.

In our mix.exs file, instead of indicating :postgrex as a dependency, we’ll need to use :mariaex. mariaex is the Ecto recommended MySQL driver.

mix.exs

# ...
def application do
  [applications: [:logger, :ecto, :mariaex],
    mod: {F1History, []}]
end

defp deps do
  [
    {:ecto, "~> 2.0"},
    {:mariaex, "~> 0.7"}
  ]
end
# ...

In the config/config.exs file, we’ll also need to tell Ecto that we’re going to be talking to a MySQL database:

config/config.exs

# ...
config :f1_history, F1History.Repo,
  adapter: Ecto.Adapters.MySQL,
  database: "f1",
  username: "root",
  password: "",
  hostname: "localhost"
  
config :f1_history, ecto_repos: [F1History.Repo]
# ...

Defining your schema

Now that we have told Elixir and Ecto how to start and connect to the databse, we can now define our schema. This is the file that we will use to define what data we’d like to retrieve from the database and in what format. If you remember from above, here is how our existing table is defined:

+-----------+--------------+------+-----+------------+----------------+
| Field     | Type         | Null | Key | Default    | Extra          |
+-----------+--------------+------+-----+------------+----------------+
| raceId    | int(11)      | NO   | PRI | NULL       | auto_increment |
| year      | int(11)      | NO   |     | 0          |                |
| round     | int(11)      | NO   |     | 0          |                |
| circuitId | int(11)      | NO   |     | 0          |                |
| name      | varchar(255) | NO   |     |            |                |
| date      | date         | NO   |     | 0000-00-00 |                |
| time      | time         | YES  |     | NULL       |                |
| url       | varchar(255) | YES  | UNI | NULL       |                |
+-----------+--------------+------+-----+------------+----------------+

So with that in mind, you will need to match field names and types. Here’s how I defined my file:

lib/race.ex

defmodule F1History.Race do
  use Ecto.Schema

  @primary_key {:raceId, :id, autogenerate: true}
  
  schema "races" do
    field :year, :integer
    field :round, :integer
    field :circuitId, :integer
    field :name, :string
    field :date, Ecto.Date
    field :time, Ecto.Time
    field :url, :string
  end
end

It looks suspiciously like every other Ecto schema you’ve set up in the past, right? There may be a couple new things in here that you haven’t seen before or aren’t real familiar with, so I’d like to point them out.

Funky primary key

This table (and all of them in the Ergast database) have what I’d consider to be a funky primary key column name. Instead of a normal id, we have raceId. If you remember from your past work in Ecto or a previous post of mine, Ecto will automatically assume that the primary key name is id. We can override that default with the @primary_key attribute. Here’s what the Ecto documents say about @primary_key:

configures the schema primary key. It expects a tuple {field_name, type, options} with the primary key field name, type (typically :id or :binary_id, but can be any type) and options. Defaults to {:id, :id, autogenerate: true}. When set to false, does not define a primary key in the schema;

So this is pretty straightforward (now that you know about it). If you’d like to read more about the @primary_key attribute, you can in the Ecto.Schema docs.

Funky field types

You’ll also notice that there are a couple of unusual field types: Ecto.Date and Ecto.Time. Dates and times have been somewhat tricky things to deal with in Elixir but now that Elixir v1.3 has been released, we have new Calendar data types. That’s beyond the scope of this post, but know that Ecto defined it’s own special types for dealing with Dates and Times (as well as DateTimes and UUIDs). Again, you can read about these in detail in the Ecto.Schema docs online.

Testing it out in iex

Now comes the point of truth. Did all this work? Let’s see if we can query our existing database table in an iex session:

> iex -S mix

iex(1)> import Ecto.Query
nil
iex(2)> F1History.Repo.one from race in F1History.Race, limit: 1

%F1History.Race{__meta__: #Ecto.Schema.Metadata<:loaded, "races">,
 circuitId: 1, date: #Ecto.Date<2009-03-29>, name: "Australian Grand Prix",
 raceId: 1, round: 1, time: #Ecto.Time<06:00:00>,
 url: "http://en.wikipedia.org/wiki/2009_Australian_Grand_Prix", year: 2009}

That’s it!

And that’s all there is to it! You can now use Ecto’s powerful composable queries to get information from your database, and, of course, write to it and update it as well. The powerful nature of Ecto comes through as connecting and wiring up Ecto to an existing database is just as easy as using Ecto to create one from scratch. Don’t let old databases stop you from connecting with Elixir any longer!

If you have any questions regarding this post, Elixir, Ecto, or anything else, please don’t hesitate to contact me. I love hearing from readers and get some great ideas for new posts (like this one!) based on your questions and comments. I’m @geolessel on Twitter and @geo in the Elixir Slack group.

And finally, please sign up for my mailing list below. I have a few cool things rattling around in my mind that, if they ever come into existance, I’d love to let you know about.