Dealing with Legacy Databases

Ergast.com is a webservice which provides a database of Historical F1 Data, starting from the 1950 season until today.

They also kindly provide a mysql database image for download. This database is the source of this toy project I’ve been working on: the idea of which is provide a {json:api} compliant API as a way for me to practice working with Elixir and Phoenix.

Challenges

Unfortunately, the mysql database image uses a non standard primary key for each table.

Here’s a sample of the races table.

Notice that the primary key for this table is raceId, and the association to the seasons table is year.

Setting Custom Primary Keys

Using the model generator in Phoenix, you’ll end up with something like this:

//web/models/race.ex

schema "races" do
  field :round, :integer
  field :name, :string
  field :url, :string
  field :date, Ecto.Date
  field :time, Ecto.Time
  timestamps()

  belongs_to :circuit, PhoenixF1JsonApi.Circuit
  belongs_to :season, PhoenixF1JsonApi.Season
end

However, this will not work, because this assumes the primary key for the race table is id, and the associations are more sane, such as season_id and circuit_id.

In order to set it up correct, I had to do the following:

//web/models/race.ex

@primary_key {:raceId, :integer, []}
@derive {Phoenix.Param, key: :raceId}
schema "races" do
  field :round, :integer
  field :name, :string
  field :date, Ecto.Date
  field :time, Ecto.Time
  field :url, :string

  belongs_to :circuit, PhoenixF1JsonApi.Circuit, foreign_key: :circuitId
  belongs_to :season, PhoenixF1JsonApi.Season, foreign_key: :year
end

Let’s break down each step.

@primary_key {:raceId, :integer, []}

Here I’m defining the primary key for this schema as raceId, and it’s an intger.

@derive {Phoenix.Param, key: :raceId}

Next, I set the @derive to use raceID as the param key. This is the ID that’s going to be used in the routes.

belongs_to :circuit, PhoenixF1JsonApi.Circuit, foreign_key: :circuitId
belongs_to :season, PhoenixF1JsonApi.Season, foreign_key: :year

Next, I had to asscoiate the foreign_keys for the circuit and season tables to those table’s primary key.

timestamps()

Finally I had to remove the timestamps() line because the data doesn’t have updated_at/created_at.

Conclusion

While it’s not difficult to use non standard keys in Phoenix, It’s really not preferred. If you have the ability to reformat the data, I highly recommend it. If not, there are ways to deal with this problem.

The main takaway for me is that the phoenix framework guides are very good. I followed the instructions here: Ecto Custom Primary Keys

Open Source

See my project on Github.

Tags:

• elixir
• ecto
• phoenix
• learning