Experimenting with inherited tables in Ecto

I’ve been working on a personal project with some mates for the past few months, and I’ve been mostly responsible for building our backend with Phoenix.

It’s come to a time where we need to implement some sort of "comments & likes" functionality for the app, so I set out to start thinknig how to go about it.

Note: this is just me experimenting, not trying to say that this is the right way to do it. I’m still learning about these things, so if you think I’m missing something, by all means, let me know at oscar@swanros.com

Note 2: the example code for this post is on GitHub.

When I started digging around, most of the answers I found about how to implement such functionality pointed to using table inheritance so that I could have a ActionableEntities table, and EntityComments and EntityLikes tables. Then, Posts, Photos, Events whould inherit from ActionableEntities gaining the ability of being commented or ‘liked’.

"Simple enough," I said to myself. Then I started digging. This is what I found.

Implementing table inheritance with Ecto

I couldn’t find concrete examples of how to do this, but did read the official Ecto documentation, though, and found that you can pass an :options parameter to specify extra attributes that you want your table to have, such as WITH, INHERITS or ON COMMIT. So, the migration looks like this:

def change do
    create table(:actionable_entities) do
        timestamps()
    end
    
    create table(:entity_comments) do
        add :content, :string
        add :entity_id, references(:actionable_entities)
        
        timestamps()
    end
    
    create table(:posts, options: "INHERITS (actionable_entities)") do
        add :content, :string
    end
    
    # photos and all other tables follow the same structure as the posts one.
end

Now the modules that’re going to be using each of these tables are defined as follows:

defmodule Inh.ActionableEntity do
  use Ecto.Schema

  schema "interactive_entities" do
    has_many :comments, Inh.EntityComments.Comment

    timestamps()
  end
end

defmodule Inh.EntityComments.Comment do
  use Ecto.Schema

  schema "entity_comments" do
    field :content, :string
    belongs_to :entity, Inh.ActionableEntity

    timestamps()
  end
end

defmodule Inh.Posts.Post do
  use Ecto.Schema

  schema "posts" do
    field :content, :string

    timestamps()
  end
end

"This ought to work right here," I thought to myself. And it does for the most part: I can create a post, and get a list of posts. I can even query for a Post‘s comments eventhough there are none on the database.

However, the following error on the database arises when trying to create a comment for a given post:

ERROR: insert or update on table "entity_comments" violates foreign key constraint "entity_comments_actionable_entity_id_fkey" DETAIL: Key (entity_id)=(1) is not present in table "actionable_entities".

The code used for trying to insert a new comment looks something along the lines of

def create_for_post(id, c_params) when is_integer(id) do
    Repo.get(ActionableEntity, id)
    |> Ecto.build_assoc(:comments, c_params)
    |> Repo.insert()
end

First get the post I want to comment, build the corresponding association and then insert it into the database. Here’s the problem, though:

indexes (including unique constraints) and foreign key constraints only apply to single tables, not to their inheritance children.

So, if I add a record to posts or to photos, the information from the inherited table bubbles up to teh parent table. But then, technically, the information is a post, not an actionable_entity. 🤔

Postgres is right to be telling me that there’s no ActionableEntity with the given id.

I could get more of a sense that something is really wrong here by creating a new photos table that too inherits from actionable_entity and adding some records to it:

At this point, there’s no data consistency as the database can’t distinguish between Photos and Posts.

One way to solve this is to create a trigger on the database to check every time we try to inset a new EntityComment for a Post, that the Post indeed exists on the database. To do this, the foreign key constraint needs to be removed from the database. So first, update the entity_comments migration:

create table(:entity_comments) do
  add :content, :string
  add :entity_id, :integer

  timestamps()
end

entity_id is now just a simple :integer, there’s not an explicit reference to the actionable_entities table.

Then, create the trigger:

execute """
  CREATE OR REPLACE FUNCTION internal_post_check() RETURNS TRIGGER AS $$
  BEGIN
    IF NOT EXISTS(SELECT 1 FROM posts WHERE id = new.entity_id) THEN
      RAISE EXCEPTION 'Post does not exist:  %', new.entity_id;
    END IF;
    RETURN new;
  END;
  $$ language plpgsql;
"""

execute """
  CREATE TRIGGER CheckEntityExists BEFORE INSERT OR UPDATE ON entity_comments
  FOR EACH ROW EXECUTE PROCEDURE internal_post_check();
"""

This trigger will run every time a new record wants to be inserted on the entity_comments table and will manually check if a post with the value on entity_id as id exists.

It may seem that the problem is now solved, but then again, what would happen when I have a Post and want to retrieve its comments? If I have Posts and Photos and potentially N number of "interactive entities" on my database, how would I be able to query just for those?

I solved this by adding a new type:String column on the actionable_entity table. Posts would have the value post in that column, photos would have the value photo, and so on for every type of actionable entity I eventually add to the database.

This way, now I can query for the comments of a specific photo with a given id:

def comments_for_post(id) when is_integer(id) do
    q = from entity in ActionableEntity,
        where: entity.id == ^id and entity.type == ^"post",
        left_join: comments in assoc(entity, :comments),
        preload: [comments: comments]
        
    post = Repo.one(q)
    post.comments
end

Final notes

Although this works it does require to bypass the database’s integrity checks to handle those on my own. This is very error prone and would require me to constantly run tests to verify that I’m not missing addig a new trigger for photos, events, or any other "actionable entity" that I want to add to my system.

Also, if I decide to add another kind of action to these entities, such as "likes" or "claps," I’d have add another set of checks for those too.

This is very soon becoming a maintainability nightmare.

I asked friend whose really experienced with backend development and he just sent me this link to a post called Three Reasons Why You Shouldn’t Use Single Table Inheritance. Read it.

In the end, this was just me trying to implement a solution that I thought would make for a good one, but it seems that the compromises that need to be made here are not worth it.

What I went with was the simplest approach: add to the entity_comments table the columns of the entities that I want to enable comments for:

id | content | post_id | photo_id | event_id | inserted_at | updated_at
-----------------------------------------------------------------------
1    Hey!      1                               -----         -----
2    nice!               1                     -----         -----
3    Good!     4                               -----         -----

As stated in schema.ex#L806:

Unless you have dozens of columns, this is simpler for the developer,
more DB friendly and more efficient in all aspects.

So there’s that! Thanks for reading.

Phoenix 1.3 is pure love for API development

Phoenix 1.3-rc.0 was just released, and while it’s still not a final release, it already feels solid as h*ck.

I’ve been doing a personal project on the side for a while now, and it has a web API component, which I wrote in Phoenix 1.2. That was my first time using Phoenix on a serious project that I intend to ship.

With the release of Phoenix 1.3, and after I watched Chris McCord’s talk about the changes it brings to the table, I decided to rewrite my API from scratch with this new version.

And oh boy, is it great.

Here are my first-impressions.

New Folder Structure

It has been said a lot of times that Phoenix is "Rails for Elixir." With 1.3, the core team sure wants that notion to be as dead as the web/ folder and the model concept.

I’m not going to explain everything that Chris talked about on his keynote at Lonestar Elixir (you should seriously go watch it), but I think it’s worth saying that the change in the folder structure of a Phoenix project has bigger implications that just files moving from one place to another.

Now, there are concrete boundaries between your actual Elixir application, and the interface that Phoenix provides for it to communicate with the web.

Responsibility separation by design. I love this.

Thinking Ahead

Long gone are the mix phoenix.* commands. Say hi to mix phx.*. Less things to type. Love it.

Also, the generators will make you think in advance of what you want to do before you actually do it.

For instance, in Phoenix 1.2 you could do something like this:

$ mix phoenix.gen.json User users email:string

which then would generate the right migrations, and the model:

defmodule MyApp.User do
    use MyApp.Web, :model

    schema "users" do
        field :email, :string
    end
end

along with a (web) controller that would handle the CRUD tasks, with the logic for validation within each action. Over time, using this structure, is easy to end up with controller actions over hundred lines long.

Now, with Phoenix 1.3, you need to specify a context for each resource you create (don’t call them "models" anymore 🙄):

$ mix phx.gen.json Accounts User users email:string

In the command above, Accounts is the context module that’s going to be also generated, and is through this context that we’ll interact with our User module:

Accounts.get_user(2) #=> %User{email: oscar@swanros.com}
Accounts.create_user(params) #=> {:ok, new_user}

This is powerful, because now you can define clear boundaries for your application domains.

Say you have the concept of "contacts" in your application. Each user (User) has contacts (also User instances). This can get messy (as I’ve written before). But using this new notion of "contexts", we can have a ContactRelationship context and handle everything in a clean way:

user
|> ContactRelationships.relationship_with(another_user)
|> ContactRelationships.accept

user
|> ContactRelationships.get_contacts

user
|> ContactRelationships.request_contact(another_user)

This scenario would make our folder structure look something like this:

|- lib/
|---- my_app/
|-------- accounts/
|-------- contact_relationships/
|-------- web/

Defining concrete boundaries between responsibilities within your app makes you come up with better code over time.

action_fallback

So far, the additions to the framework are really nice and welcomed. However, I think my favorite one is the new action_fallback plug.

On Phoenix 1.2 and earlier, every controller needed to return a valid conn for every request. Otherwise, an exception would rise.

This is still true, but with the new action_fallback plug we can delegate that task to another controller whose sole purpose is to handle the cases when other controllers couldn’t provide a "successful" response to the request.

Let me explain… before, you wrote code similar to this in every controller action:

def create(conn, %{"user" => user_params}) do
  user = Repo.get_by(User, phone_number: user_params["phone_number"])

  cond do
    user && checkpw(user_params["password"], user.password_hash) ->
      case create_session(user) do
        {:ok, session} ->
          conn
            |> put_status(:created)
            |> render("show.json", %{session: session, user: user})

        _ ->
          conn
            |> put_status(:unauthorized)
            |> render("error.json")
      end

    true ->
      dummy_checkpw()
      conn
        |> put_status(:unauthorized)
        |> render("error.json")
  end
end

Is not that bad, truly. But this is still better with Phoenix 1.3:

action_fallback MyApp.Web.FallbackController

def create(conn, %{"user" => user_params}) do
  user = Accounts.get_user_by_phone(user_params["phone_number"])

  cond do
    user && checkpw(user_params["password"], user.hashed_password) ->
      with {:ok, %Session{} = session} <- Sessions.create_session(user) do
        conn
        |> put_status(:created)
        |> render("auth_success.json", user: user, session: session)
      end

    true ->
      {:error, :wrong_credentials}
  end
end

With Phoenix 1.3 our controller actions can be simplified to just care about the "happy path", and leave the rest to the fallback controller that we define using the action_fallback plug.

In this case, when the cond evaluates to true we return from the action {:error, :wrong_credentials}. Since this is not a valid connection, our fallback controller comes forward:

defmodule MyApp.Web.FallbackController do
  use MyApp.Web, :controller

  def call(conn, {:error, %Ecto.Changeset{} = changeset}) do
    conn
    |> put_status(:unprocessable_entity)
    |> render(MyApp.Web.ChangesetView, "error.json", changeset: changeset)
  end

  def call(conn, {:error, :unauthorized}) do
    conn
    |> put_status(:unauthorized)
    |> render(MyApp.Web.ErrorView, "auth_required.json")
  end

  def call(conn, {:error, :wrong_credentials}) do
    conn
    |> put_status(:unprocessable_entity)
    |> render(MyApp.Web.ErrorView, "wrong_credentials.json")
  end
end

By pattern matching, the fallback controller knows exactly what to send back as a response to the request.

This also means that we can have a centralized list of error codes that our API can expose to our clients, on our ErrorView module.

No more error definitions scattered around our codebase. ❤️

However, why stop with errors? If your API has "default" responses for actions, you can rely on the fallback controller too to display them:

def delete(conn, _params) do
  with {:ok, session} <- Authentication.get_session(conn),
       {:ok, deleted} <- Sessions.delete_session(session) do
    :success
  else _ ->
    {:error, :unauthorized}
  end
end

:success will be passed to the appropriate action on your fallback controller, and there goes your default response. 🎉

Final thoughts

Phoenix 1.3 is a great update.

Please keep in mind that the defaults 1.3 offers could be done with 1.2, too. But the fact that this is now the default, really speaks of how much attention to detail the Phoenix team is putting in.

They don’t want us to be using "The Phoenix Way™", but rather provide sensible defaults that enable us to create our own solutions in the best possible way.

Some points:

  • The new fallback_controller plug is awesome.
  • The fact that you need to think in terms of boundaries for your app, I think will eventually lead to better, more maintainable codebases.
  • Smarter code generation is 👌.
  • For APIs, the centralized list of available errors is really welcomed.

I love how productive I am with Phoenix when building APIs, and I’m really looking forward to how this already-great framework will evolve past 1.X.


edit: I also wrote about how action_fallback and contexts in Phoenix 1.3 made my controllers tiny.

Filtering has_many relationships in Ecto

This is sot-of a follow up to my last post, about self-referencing many_to_many relationships using Ecto.

I find myself in a scenario where I have a User:

# User model
defmodule MyApp.User do
  use MyApp.Web, :model

  alias MyApp.Contact

  schema "users" do
    has_many :_internal_contacts, MyApp.Contact
    has_many :contacts, through: [:_internal_contacts, :contact]

    timestamps
  end
end

and an association model Contact through which the User has many contacts (User instances):

defmodule MyApp.Contact do
  use MyApp.Web, :model

  alias MyApp.User

  schema "contacts" do
    field :status, :integer

    belongs_to :user, User, foreign_key: :user_id
    belongs_to :contact, User, foreign_key: :contact_id

    timestamps
  end

  def status_code(status) do
    case status do
      :accepted ->
        1
      :pending ->
        0
      :rejected ->
        -1
      _ ->
        0
    end
  end

I created an endpoint on my app that’s supposed to get me only the user’s contacts that have the :accepted status (1). How can this be accomplished?

The Repo module has a set of handy functions that let you preload associations on your models.

user = User |> Repo.get(1) |> Repo.preload(:contacts)

The above will preload all contacts on the user. Pay attention to the fact that it is of type has_many and it goes through the :_internal_contacts property. This is the important cue.

Turns out that the preloads parameter on Repo.preload/3 can be accompanied by a query.

import Ecto.Query

#1
query = from c in Contact, where: c.status == 1 and c.user_id == 1

u = User
    |> Repo.get(1) #2
    |> Repo.preload(_internal_contacts: query) #3
    |> Repo.preload(:contacts) #4

The code above will:

  1. Create a query that will ask for Contacts with status 1 and with user_id 1.
  2. Get a User instance.
  3. Preload the _internal_contacts on that instace using our query (these are Contact instances, not User instances)
  4. Preload the :contacts on our user. And since :contacts goes through: _internal_contacts, :contacts has only valid contacts now (User instances).

Notes:

Honestly, I don’t know if this is the right approach here (let me know!). I did find this thread on GitHub where the general issue that we’re facing here is described. It seems that there’s an interest to have the ability to filter the relationships on the declaration itself:

It would look something like this:

has_many :comments, MyApp.Comment, foreign_key: :commentable_id,  fn (query) -> 
  from c in query, where: c.commentable_type == "articles"
end

However, right now, the saner approach seems to be the use of composable queries.

Have anything to add to this article or did I miss something? Please let me know on Twitter.