Using Generated Columns in Ecto

I was playing around with Ecto and SQLite, and one thing I noticed is that there seemed to be no native support for generated columns, which I needed for a project I was working on. I only found some vague references on how to do this online, so I figured I’d immortalize my solution in a blog post.

Let’s consider a table with the following schema:

CREATE TABLE taken_log(
	id INTEGER PRIMARY KEY,
	pillminder VARCHAR,
	taken_at TEXT_DATETIME NOT NULL
);

This table will track entries based on when different users have taken their pills (identified by their pillminder name). Translating this to an Ecto migration is quite simple.

defmodule Pillminder.Stats.TakenLog.Repo.Migrations.CreateTakenLog do
  use Ecto.Migration

  def change do
    create table(:taken_log) do
      add(:pillminder, :varchar)
      add(:taken_at, :utc_datetime, null: false)
    end
  end
end

However, for this application, we only want to allow one entry per day, so it would not be valid to, for instance, have two taken_at entries on the same day with different times.

Unfortunately, SQLite does not support using functions in UNIQUE constraints. It does, however, support GENERATED columns which can be constrained like any other column. Ideally, we’d want something like this:

CREATE TABLE taken_log(
	id INTEGER PRIMARY KEY,
	pillminder VARCHAR,
	taken_at TEXT_DATETIME NOT NULL,
	taken_on DATE GENERATED ALWAYS AS (DATE(taken_at)),
	UNIQUE(taken_on)
);

I don’t believe it is possible to give custom column specifications in Ecto¹, so it seems that the best way to do this in Ecto is with an ALTER TABLE in an execute.

defmodule Pillminder.Stats.TakenLog.Repo.Migrations.CreateTakenLog do
  use Ecto.Migration

  def change do
    create table(:taken_log) do
      add(:pillminder, :varchar)
      add(:taken_at, :utc_datetime, null: false)
    end

    execute(
      "ALTER TABLE taken_log ADD COLUMN taken_on GENERATED ALWAYS AS (date(taken_at))",
      "ALTER TABLE taken_log DROP COLUMN taken_on"
    )

    create unique_index(:taken_log, [:pillminder, :taken_on])
  end
end

Depending on your application, you may or may not want to add this to the schema. In my case, because this is effectively used as a glorified constraint, I chose not to. If you did, though, you may wish to build a custom changeset that omits this field when writing to the database (otherwise, you’ll get a nasty error telling you that you can’t write to generated fields)².