Ecto is awesome. It makes dealing with SQL databases in Elixir particularly fun. Is it simple to use with legacy databases though? The answer is, of course, yes. But you might want to be aware of some quirks. This is all in the (excellent) Ecto documentation, but I might save you some searching.

For my first phoenix app, I received a database from a .Net application running in production, with its own quirks and conventions for column naming that were quite different from Ecto assumptions.

Comparison

The first thing that I encountered is that all column names were written in CamelCase. If you look at common Ecto examples, you will find that columns are written in snake_case. Also, primary keys are automatically generated as migrations as simply id, but in this database all primary keys had the form TableId. Ecto timestamps also have default names. Fortunately all of this can be overridden. Let’s look at a schema definition using Ecto defaults:

Simple enough. With very little code we have a table with a column, plus a primary key and timestamps. Now let’s see a second version of the table with different conventions:

Looks different enough. I tried to bring to your attention the following 4 points:

1- The primary key can be specified to whatever name was chosen. It’s a three element tuple with the column name, the type and the options. The :id type with the option [autogenerate: true] makes it equivalent to an id table as created by Ecto migrations.

2- The table name can be specified and it’s independent of the model name. In this example it breaks the convention of using plural table names (¯\_(ツ)_/¯).

3- Field names are declared as you would expect. But there are some gotchas to be aware of that will be mentioned soon.

4- Timestamps work just fine with non-default names. Write it once, then forget about it.

What about the column names issue that I mentioned? This is a very simple issue but it caused me more than a couple of headaches. Ecto models can be used as structs, which means that you can access their fields by name. In the case of our first, conventional example, it means that you can do something like this:

But in the case of our second example, we can’t quite do the same thing.

Are we doomed? Using pattern matching or Map.get every time we access a record field seems cumbersome but doable (it does add noise to the code, let me tell you). What about queries? There’s a field/2 function, which let’s you query by a dynamically defined column name without much code.

Smart readers should notice by this point that there’s a simpler way. In fact, the elixir compiler would tell you (at least by version 1.3) the right syntax for accessing a struct field with that starts with an uppercase letter. But alas, it seems that I’m not that smart since I discovered it painfully late:

That can save you a lot of typing. Believe me.

Relations

Last, but not least, are definitions of model relationships. While simple, you do have to specify the names of the foreign keys in the table and the target key in the other table that it references. Of course, you are not required to add relationships to your schemas, but it servers as documentation and gives you access to a lot of niceties, such as preload/3 and assoc , just to mention a couple.

Conclusion

Dealing with legacy databases in Ecto is surprisingly simple. You just add a few lines here and there. The engineering implications of having a (possible) second source of truth for your business logic are beyond the scope of this article.

If you are not dealing with a legacy database I hope this gave you some insight on the role that Ecto defaults might play in our designs. It’s also worth of praise to be able to skip the defaults without much problem.

If you find errors, or want to share some experiences or insights on this topic, please contribute to the discussion in the comments.