Ecto Schemas On Rails

I recently migrated a Rails JSONAPI server to Phoenix, and in the process I learned a lot about using Ecto with a database that conforms to Rails naming conventions. It turned out that I was able to share an existing Rails database between the Rails and Phoenix implementations of the server during development, which allowed me to use a phased approach in transitioning to the new Phoenix version.

In his ElixirConf 2016 keynote, Chris McCord announced that Phoenix models will likely be replaced with service modules in Phoenix 1.3. Because of this I’ll stick to describing the parts of Ecto used in Phoenix models while skipping the concept of models in general.

The Ecto library has four core modules that you will need to understand. These are Ecto.Schema, Ecto.Changeset, Ecto.Query, and Ecto.Repo. To get our feet wet and provide a basis for future articles, we’ll focus on schemas here.

The responsibility of Ecto.Schema is to define a struct to hold a table’s record data, and to provide type information to facilitate casting columns and referencing associations. This definition is done using Ecto’s field and association macros. In ActiveRecord terms: Ecto schema fields are like explicit versions of the Ruby magic that turns table columns into model attributes, while Ecto schema associations are very similar to AR associations.

The Ecto documentation is extremely clear and well written, but it took some digging to figure out how Ecto conventions can be over-ridden to work with Rails databases. I wrote this article to share what I’ve learned.

We’ll be using these versions of Rails and Phoenix/Ecto.

Rails 5.0
Phoenix 1.2.1
Ecto 2.1.0-rc.2

NOTE: Ecto stable is currently 2.0.5, but it has a bug in certain has_many through scenarios. This is fixed in the 2.1.0 release candidates. We’ll see how to update this dependency when we set up our new Phoenix application.

Generating the Rails database

We’ll start by generating Rails migrations and models to build an example database. The models have a few basic attributes, but we won’t be using them yet. The models do, however, include examples of all the basic associations supported by ActiveRecord. Here are the relations we will be modeling in terms of Rails associations.

User has_and_belongs_to_many Projects
Project has_and_belongs_to_many Users

Project has_many Lists
List belongs_to Project

List has_many Items
Item belongs_to List

List has_one Status
Status belong_to List

We will also look at writing these through associations.

Project has_many Items through Lists
User has_many Lists through Projects
User has_many Items through Lists
  (a through association based on another through association)
Item has_one Status through List

Assuming we have a new Rails app backed by a PostgreSQL database, we can generate the models and migrations to express these fields and associations.

$ bin/rails g model User email:uniq encrypted_password
$ bin/rails g model Project name slug:uniq
$ bin/rails g migration CreateJoinTableProjectsUsers project user
$ bin/rails g model List name slug project:references
$ bin/rails g model Item description list:references
$ bin/rails g model Status description list:references

Now we need to make a few final adjustments. First, edit the XXX_create_join_table_projects_users.rb migration and uncomment both indexes. Next, change the XXX_create_lists.rb migration to force the List slug to be unique with respect to project_id. We can do this by adding a compound unique index.

add_index :lists, [:project_id, :slug], unique: true

With all this set up we are ready to migrate the database.

$ bin/rake db:migrate

I’ll finish by adding the remaining associations to the new models. You can take a look at the generated migrations, the models including all associations, and the schema.rb file in the sample project at:

https://github.com/HaughtCodeworks/playground-rails/tree/ecto-schemas

Before we start looking at the Ecto schemas we’ll use to access this new database, let’s create some sample data in the Rails console. We just need enough data to test our association definitions later.

user = User.create!(email: "[email protected]")
project = Project.create!(name: "Project", slug: "project", users: [user])
list = List.create!(name: "List", slug: "list", project: project)
Item.create!(description: "Item", list: list)
Status.create!(description: "Status", list: list)

Connecting Ecto to the Rails database

We’ll start by creating a new app named PhoenixApp:

$ mix phoenix.new phoenix_app --no-brunch
$ cd PhoenixApp

As mentioned in the introduction, Ecto 2.0.5 has a bug that has been fixed in the 2.1.0 release candidates. To use the latest rc version of Ecto you’ll need to change your project dependencies and then run mix deps.get to fetch the new versions.

These lines:

defp deps do
  [. . .
   {:phoenix_ecto, "~> 3.0"},
   {:postgrex, ">= 0.0.0"},
   . . .
  ]
end

Become:

defp deps do
  [. . .
   {:ecto, "~> 2.1.0-rc.0"},
   {:phoenix_ecto, "~> 3.1.0-rc.0"},
   {:postgrex, "~> 0.12.0"},
   . . .
  ]
end

Now update config/dev.exs to connect to the Rails development database. Typically this just requires changing the username, password, and database settings.

# Configure your database
config :phoenix_app, PhoenixApp.Repo,
  adapter: Ecto.Adapters.Postgres,
  username: "username",
  password: "password",
  database: "rails_app_development",
  hostname: "localhost",
  pool_size: 10

Finally we can test the connection by starting an iex session. To exit the session type Ctrl-C Ctrl-C.

$ iex -S mix

If iex starts without errors, your connection is configured correctly.

A bad username or password configuration will cause iex to emit errors looking like this:

[error] Postgrex.Protocol (#PID<0.NNN.0>) failed to connect: ** (Postgrex.Error) FATAL (invalid_password): password authentication failed for user "xxx"

A bad database name will cause iex to emit errors looking like this:

[error] Postgrex.Protocol (#PID<0.NNN.0>) failed to connect: ** (Postgrex.Error) FATAL (invalid_catalog_name): database "xxx" does not exist

Generating the Ecto Schemas

Just like in Rails, we’ll use generators to build our Ecto schemas and add the remaining associations by hand. As we generate Phoenix models and Ecto migrations, the mix command output will ask you to run mix ecto.migrate. Since our development database already has a complete database structure built, this will fail with duplicate table errors. For now we’ll just skip running Ecto migrations. For similar reasons we will skip generating a migration for the projects_users join table.

If you want to refer to the final version of the Ecto schemas as we go along, check out the source in web/models at:

https://github.com/HaughtCodeworks/playground-phoenix/tree/ecto-schemas

$ mix phoenix.gen.model User users email:unique encrypted_password
$ mix phoenix.gen.model Project projects name slug:unique
$ mix phoenix.gen.model List lists name slug project_id:references:projects
$ mix phoenix.gen.model Item items description list_id:references:lists
$ mix phoenix.gen.model Status statuses description list_id:references:lists

Let’s test out our new models, start up iex and see if we can load all Users. We won’t get too advanced with our usage of Ecto.Repo, for now it’s enough to understand that Repo.all returns all records, and Repo.one returns a single record. All of our Phoenix modules live under the PhoenixApp namespace, so our Ecto User is PhoenixApp.User and our application Repo is PhoenixApp.Repo. This command fetches all of our application’s Users from our application’s Repo:

$ iex -S mix
iex(1)> PhoenixApp.Repo.all(PhoenixApp.User)
SELECT u0."id", u0."email", u0."encrypted_password", u0."inserted_at", u0."updated_at" FROM "users" AS u0 []
** (Postgrex.Error) ERROR (undefined_column): column u0.inserted_at does not exist
    (ecto) lib/ecto/adapters/sql.ex:415: Ecto.Adapters.SQL.execute_and_cache/7
    (ecto) lib/ecto/repo/queryable.ex:121: Ecto.Repo.Queryable.execute/5
    (ecto) lib/ecto/repo/queryable.ex:35: Ecto.Repo.Queryable.all/4

We’ve hit our first naming convention issue. Ecto uses an inserted_at column for tracking when a record is first inserted into the database, Rails uses created_at. We’ll get that fixed in the next section.

Over-riding the inserted_at column name

Ecto supports over-riding the default timestamps column names both per model schema and globally across all model schemas.

In an Ecto schema definition, timestamps accepts options specifying alternative column names for inserted_at and updated_at. Let’s try configuring this in User.

Change the timestamps definition in web/models/user.ex from:

schema "users" do
  field :login, :string

  timestamps()
end

To:

schema "users" do
  field :login, :string

  timestamps inserted_at: :created_at
end

We can see if it worked in iex. The result is a list of PhoenixApp.User structs. For the rest of the article I’ll be hiding the contents of these structs for clarity, but it’s worth looking them over if you run these commands yourself in a real iex session.

$ iex -S mix
iex(1)> PhoenixApp.Repo.all(PhoenixApp.User)
SELECT u0."id", u0."email", u0."encrypted_password", u0."created_at", u0."updated_at" FROM "users" AS u0 []
[%PhoenixApp.User{__meta__: #Ecto.Schema.Metadata<:loaded, "users">,
  created_at: ~N[2022-10-24 20:42:17.914653], email: "[email protected]",
  encrypted_password: nil, id: 1, updated_at: ~N[2022-10-24 20:42:17.914653]}]

Obviously this configuration will be required by all of our models, so let’s undo the User timestamps change and set the configuration globally instead. The documentation for these global Ecto schema configurations can be found at https://hexdocs.pm/ecto/Ecto.Schema.html#module-schema-attributes.

In web/web.ex add @timestamp_opts to the model function after use Ecto.Schema:

def model do
  quote do
    use Ecto.Schema
    @timestamps_opts inserted_at: :created_at

    import Ecto
    import Ecto.Changeset
    import Ecto.Query
  end
end

We can check our work:

$ iex -S mix
iex(1)> PhoenixApp.Repo.all(PhoenixApp.User)
SELECT u0."id", u0."email", u0."encrypted_password", u0."created_at", u0."updated_at" FROM "users" AS u0 []
[%PhoenixApp.User{ . . . }]

Defining our Ecto schemas

Now we can start defining the fields and associations for our Ecto schemas. We’ll begin by covering the field declarations, then we can add the associations. We’ll cover all of the basic association types and then see how we can re-use those in through associations.

Fields

The generators have done most of the field mapping work for us. Each field declaration consists of a field name, a type, and options. Types can be either primitive or custom, so you are free to add types if your application needs them. Interestingly, both list (array) and map are included in the primitive types.

The field options you’ll most commonly use are default and virtual. The default option is used to provide a static default value for the field when it has not been set. The virtual option flags a field as non-persisting, a common use would be to temporarily hold a plaintext password before encrypting it for writing to the database. You can refer to the Ecto.Schema field documentation for all of the types and options.

Basic associations

We’ll start by looking at defining and accessing the basic associations belongs_to, has_many, many_to_many (has_and_belongs_to_many), and has_one.

belongs_to

Our Ecto model generator commands explicitly named the model table and the foreign keys referring to other tables. By choosing table names and foreign key names that match Rails conventions (which are pretty hard to argue with), we automatically get working generated belongs_to associations in our Ecto schemas.

Let’s test our generated belongs_to definition for Item. To fetch our item, we use Repo.one which is piped to Repo.preload(:list). Repo.preload loads the data for the named association and combines it with the Item returned by Repo.one. We can access the fields of the resulting struct using dot notation.

$ iex -S mix
iex(1)> item = PhoenixApp.Repo.one(PhoenixApp.Item) |> PhoenixApp.Repo.preload(:list)
SELECT i0."id", i0."description", i0."list_id", i0."created_at", i0."updated_at" FROM "items" AS i0 []
SELECT l0."id", l0."name", l0."slug", l0."project_id", l0."created_at", l0."updated_at", l0."id" FROM "lists" AS l0 WHERE (l0."id" = $1) [1]
%PhoenixApp.Item{ . . . }

iex(2)> item.list.name
"List"

has_many

Just like we did for the Rails models, we need to add has_many definitions to our Ecto model schemas by hand. In Project we add a has_many explicitly naming the relationship and specifying the model to use.

schema "projects" do
  . . .
  has_many :lists, PhoenixApp.List
  . . .
end

List has_many Items works the same:

schema "lists" do
  . . .
  has_many :items, PhoenixApp.Item
  . . .
end

And we can test Project has_many Lists out in iex. After we fetch our project we can map over the project.lists list and return the name of each one.

$ iex -S mix
iex(1)> project = PhoenixApp.Repo.one(PhoenixApp.Project) |> PhoenixApp.Repo.preload(:lists)
SELECT p0."id", p0."name", p0."slug", p0."created_at", p0."updated_at" FROM "projects" AS p0 []
SELECT l0."id", l0."name", l0."slug", l0."project_id", l0."created_at", l0."updated_at", l0."project_id" FROM "lists" AS l0 WHERE (l0."project_id" = $1) ORDER BY l0."project_id" [1]
%PhoenixApp.Project{ . . . }

iex(2)> project.lists |> Enum.map(fn l -> l.name end)
["List"]

has_and_belongs_to_many/many_to_many

The Rails has_and_belongs_to_many association is referred to as many_to_many in Ecto. Ecto 2.0 supports many_to_many using either a simple join table (like projects_users in our example) or joining through another Ecto schema definition.

When adding the many_to_many declaration we specify the association name, the association model, and the name of the join table. Here are the many_to_many lines from User and Project.

schema "users" do
  . . .
  many_to_many :projects, PhoenixApp.Project, join_through: "projects_users"
  . . .
end

schema "projects" do
  . . .
  many_to_many :users, PhoenixApp.User, join_through: "projects_users"
  . . .
end

And in iex User many_to_many Projects:

$ iex -S mix
iex(1)> user = PhoenixApp.Repo.one(PhoenixApp.User) |> PhoenixApp.Repo.preload(:projects)
SELECT u0."id", u0."email", u0."encrypted_password", u0."created_at", u0."updated_at" FROM "users" AS u0 []
SELECT p0."id", p0."name", p0."slug", p0."created_at", p0."updated_at", u1."id" FROM "projects" AS p0 INNER JOIN "users" AS u1 ON u1."id" = ANY($1) INNER JOIN "projects_users" AS p2 ON p2."user_id" = u1."id" WHERE (p2."project_id" = p0."id") ORDER BY u1."id" [[1]]
%PhoenixApp.User{ . . . }

iex(2)> user.projects |> Enum.map(fn p -> p.name end)
["Project"]

has_one

As in Rails, Ecto has_one definitions look just like has_many. We can add List has_one Status like this:

schema "lists" do
  . . .
  has_one :status, PhoenixApp.Status
  . . .
end

And it works as expected:

$ iex -S mix
iex(1)> list = PhoenixApp.Repo.one(PhoenixApp.List) |> PhoenixApp.Repo.preload(:status)
SELECT l0."id", l0."name", l0."slug", l0."project_id", l0."created_at", l0."updated_at" FROM "lists" AS l0 []
SELECT s0."id", s0."description", s0."list_id", s0."created_at", s0."updated_at", s0."list_id" FROM "statuses" AS s0 WHERE (s0."list_id" = $1) [1]
%PhoenixApp.List{ . . . }

iex(2)> list.status.description
"Status"

Through associations

Associations can be defined through other associations in Ecto just like in ActiveRecord. Ecto’s through definitions use a list of association names to resolve when fetching the target association.

has_many through

As an example, Project has_many Items through List and User has_many Lists through Project can be expressed as:

schema "projects" do
  . . .
  has_many :items, through: [:lists, :items]
  . . .
end

schema "users" do
  . . .
  has_many :lists, through: [:projects, :lists]
  . . .
end

These through definitions describe the association chain used to fetch the final association and can be arbitrarily deep. You can see them working in iex.

$ iex -S mix
iex(1)> project = PhoenixApp.Repo.one(PhoenixApp.Project) |> PhoenixApp.Repo.preload(:items)
SELECT p0."id", p0."name", p0."slug", p0."created_at", p0."updated_at" FROM "projects" AS p0 []
SELECT l0."id", l0."name", l0."slug", l0."project_id", l0."created_at", l0."updated_at", l0."project_id" FROM "lists" AS l0 WHERE (l0."project_id" = $1) ORDER BY l0."project_id" [1]
SELECT i0."id", i0."description", i0."list_id", i0."created_at", i0."updated_at", i0."list_id" FROM "items" AS i0 WHERE (i0."list_id" = $1) ORDER BY i0."list_id" [1]
%PhoenixApp.Project{ . . . }

iex(2)> project.items |> Enum.map(fn i -> i.description end)
["Item"]

For our last has_many through example, let’s try User has_many Items through Lists. In this case the lists association on User is also a has_many through, and we can chain them together without any problems.

schema "users" do
  . . .
  has_many :items, through: [:lists, :items]
  . . .
end

With our updated version of Ecto we can access this chained through association. One thing to notice is that pre-loading a through relationship like items automatically pre-loads any intermediate associations like projects and lists.

i$ iex -S mix
ex(1)> user = PhoenixApp.Repo.one(PhoenixApp.User) |> PhoenixApp.Repo.preload(:items)
SELECT u0."id", u0."email", u0."encrypted_password", u0."created_at", u0."updated_at" FROM "users" AS u0 []
SELECT p0."id", p0."name", p0."slug", p0."created_at", p0."updated_at", u1."id" FROM "projects" AS p0 INNER JOIN "users" AS u1 ON u1."id" = ANY($1) INNER JOIN "projects_users" AS p2 ON p2."user_id" = u1."id" WHERE (p2."project_id" = p0."id") ORDER BY u1."id" [[1]]
SELECT l0."id", l0."name", l0."slug", l0."project_id", l0."created_at", l0."updated_at", l0."project_id" FROM "lists" AS l0 WHERE (l0."project_id" = $1) ORDER BY l0."project_id" [1]
SELECT i0."id", i0."description", i0."list_id", i0."created_at", i0."updated_at", i0."list_id" FROM "items" AS i0 WHERE (i0."list_id" = $1) ORDER BY i0."list_id" [1]
%PhoenixApp.User{ . . . }

iex(2)> user.items |> Enum.map(fn i -> i.description end)
["Item"]

has_one through

The other through variation we have available is has_one. Let’s add a definition for Item has_one Status through List.

schema "items" do
  . . .
  has_one :status, through: [:list, :status]
  . . .
end

And in iex it just works:

$ iex -S mix
iex(1)> item = PhoenixApp.Repo.one(PhoenixApp.Item) |> PhoenixApp.Repo.preload(:status)
SELECT i0."id", i0."description", i0."list_id", i0."created_at", i0."updated_at" FROM "items" AS i0 []
SELECT l0."id", l0."name", l0."slug", l0."project_id", l0."created_at", l0."updated_at", l0."id" FROM "lists" AS l0 WHERE (l0."id" = $1) [1]
SELECT s0."id", s0."description", s0."list_id", s0."created_at", s0."updated_at", s0."list_id" FROM "statuses" AS s0 WHERE (s0."list_id" = $1) [1]
%PhoenixApp.Item{ . . . }

iex(2)> item.status.description
"Status"

Other Association Types

There are two other varieties of associations that I have skipped, polymorphic associations and self-referential associations. I have not needed to use either of these on a project yet.

The polymorphic association is not really supported by Ecto. From a database theory perspective, polymorphic associations are dangerous because they break enforcement of referential integrity. Ecto prefers that you use a distinct table and foreign key for each “type”, which allows for enforcement of referential integrity constraints.

The other problem with supporting polymorphic associations is that Ecto uses structs to hold record data, but there is no notion of struct inheritance in Elixir. Regardless, there is community interest in supporting some type of polymorphic association in Ecto but any implementation will likely be an extension and not part of Ecto core.

The other association we skipped is self-referential associations, which are used to model tree like relationships. Setting up the association definitions for has_many :children and belongs_to :parent should be straight forward, and functional languages make it easy to use recursion to fetch data to any depth. This may be an interesting topic to explore in the future.

Next Steps

So far our use of Ecto has been straightforward, we were able to create Ecto schemas and define fields and associations compatible with Rails database conventions. At this point we have the read side of interacting with a Rails database covered.

Obviously reading records out of the database is only a small part of the story. If we want to replicate the behavior of ActiveRecord models, we’re still missing a lot of pieces. We need to handle record inserts and updates with validation and error reporting. We need callback behaviors that generate slugs or encrypt passwords. We’ll want to catch and handle violations of database constraints. Most of these behaviors will be implemented in the form of Ecto changesets.

To really use our new Ecto schemas in an application, we also need to understand Ecto’s query interface. We’ll want to join on our schema associations and access those joined tables in composed queries. We’ll want to write Ecto migrations that can duplicate the Rails database structure. And at some point we may want to use migrations to move some of our Rails naming conventions toward the Ecto ones.

That’s a lot to cover, and Ecto is definitely a different beast than Rails ActiveRecord. Fortunately our familiarity with ActiveRecord allows us to draw parallels and emphasize the differences between the two. Before long you’ll be just as good at thinking in Ecto as you are at working with ActiveRecord.