Elixir and Ecto advanced constraints

Postgres is my favourite microservice. It’s fast, resilient and feature-rich. Another tool that I’m a big fan of is Ecto's and its Changesets system. Changesets simplify validation rules which may alternate depending on the context. Separated rules for new entries and for updated. Everything without a single if statement. Using them is as plausible as creating structures from LEGO bricks.

Sometimes we can’t rely on standard database constraints to satisfy business logic needs. Today we’re going to explore triggers as a mechanism of keeping data consistency between multiple rows. Validation must be protected against race condition and prevent invalid state also for updates which bypass the app.

We will create a trigger that is executed before row insert and update. When SQL statement validation fails, our Postgres procedure must raise an exception. Then, we will instruct Ecto how to handle Postgres exception gracefully, and set user-friendly error message on a changeset field.

The case

Expense management app which allows the customer to define very own categories and category hierarchy. The app permits up to two category levels. No nesting, no recursion, thus we must forbid assignment of a parent category to a child category.

Postgres migration

Below is an example Ecto migration script that creates a trigger which we’re going to use later as a custom constraint. The trigger raises an exception when we try to assign a child category as a parent of another category.

def change do
  # Procedure that prevents assigning expense_categories to a child
  execute(
    # up
    ~S"""
    -- prevents assigning expense_categories to a child if parent_id is already a child
    -- this means to check whether new parent_id has parent_id = NULL
    CREATE FUNCTION expense_category_parent_is_top_level() RETURNS TRIGGER AS $$
      BEGIN
        IF NEW.parent_id IS NOT NULL THEN
          IF (SELECT parent_id FROM expense_categories WHERE id = NEW.parent_id) IS NOT NULL
          THEN
            RAISE check_violation USING CONSTRAINT = 'parent_is_top_level';
          END IF;
        END IF;
      RETURN NEW;
    END;
    $$ LANGUAGE plpgsql;
    """,

    # down
    "DROP FUNCTION expense_category_parent_is_top_level;"
  )

  # Table expense_categories
  execute(
    # up
    ~S"""
    CREATE TRIGGER expense_category_can_be_parent_insert
    BEFORE INSERT ON expense_categories
    FOR EACH ROW
      EXECUTE PROCEDURE expense_category_parent_is_top_level();
    """,

    # down
    "DROP TRIGGER expense_category_can_be_parent_insert ON expense_categories;"
  )

  execute(
    # up
    ~S"""
    CREATE TRIGGER expense_category_can_be_parent_update
    BEFORE UPDATE OF parent_id ON expense_categories
    FOR EACH ROW
      EXECUTE PROCEDURE expense_category_parent_is_top_level();
    """,

    # down
    "DROP TRIGGER expense_category_can_be_parent_update ON expense_categories;"
  )
end

That’s relatively long code listing, so let’s split it into easier to digest chunks:

IF (SELECT parent_id FROM expense_categories WHERE id = NEW.parent_id) IS NOT NULL

This is the query that verifies whether the new parent is not a child already. Variable NEW stores inserted row data. Alternatively, you might use the OLD variable, which keeps a previous row state. For more details, please look at the links leading to the PostgreSQL documentation.

The execute function takes two SQL clauses as arguments. The first one defines the migration actions and another how to revert it. Remember to always create reversible migrations that execute opposite clauses which can roll back changes.

When the condition evaluates to false, the procedure returns a new row: RETURN NEW;

Otherwise, if condition is satisfied, the procedure raises an exception: RAISE check_violation USING CONSTRAINT = ‘parent_is_top_level’;

There are four constraint violations handled by Ecto:

• unique_violation
• foreign_key_violation
• exclusion_violation
• check_violation

check_violation - that is a constraint type specific for our case. We’ll catch that exception during Ecto Changeset validation. Constraint name is set with: USING CONSTRAINT = ‘parent_is_top_level’

We need to run the procedure before row insert and update. These clauses determine when to run checks (pay attention to OF and ON prepositions):

BEFORE INSERT ON

BEFORE UPDATE OF

This is a small optimization that executes only when the parent_id column was changed.

Changeset

Ecto does not know what to do with our new constraint and when Postgres raises that exception, Ecto will bubble it up and break process execution. Although to fail fast is the main Erlang principle, in our case we’ll want to handle exception gracefully.

Just add check_constraint method to the pipeline:

changeset
# ...
|> check_constraint(
  :parent_id,
  message: “parent must be a top level category”,
  name: :parent_is_top_level
)
# ...

The crucial part is the option name: where we tell Ecto to look for specific exception code. This code is defined in the Postgres procedure:

USING CONSTRAINT = ‘parent_is_top_level’;

Atom :parent_id is a field in the ExpenseCategory structure. Option message: is an error message which we’re going to present to the user when the exception is raised.

The simplicity is the reason I love working with Ecto so much.

External links

Below are links with more information on technical details:

• Postgres triggers
• Postgres error codes
• PL/pgSQL language
• Ecto constraints