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
Unfortunately, SQLite does not support
using functions in
UNIQUE constraints. It does, however, support
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 Ecto1,
so it seems that the best way to do this in Ecto is with an
ALTER TABLE in an
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)2.
None of the definitions in the SQLite driver seem to allow anything other than in a form Ecto already understands. ↩︎
As far as I’m aware, you can just omit it in your