Ecto Migrations with Function Defaults

There is no time like the present.

ORMs tend to have limited if any support for setting the default value of a column to be a function. However, using a function as the default for a column is a powerful feature of PostgreSQL. Such a function would be evaluated for that column at the time of insertion if no other value had been specified. This is extremely handy for timestamp fields because we can omit them from the insert statement and trust that PostgreSQL will set them to whatever now() evaluates.

To take advantage of functional defaults for columns, I often have to execute raw SQL in migrations. That can look something like the following code:

def up do  
  execute """
    alter table posts
      alter column inserted_at set default now(),
      alter column updated_at set default now();
  """
end  

Fortunately, with Ecto, there is a fragment/1 function that allows me to set functional defaults for columns. The above SQL migrations can be transformed into the following block of Ecto DSL code:

def up do  
  alter table(:posts) do
    modify :inserted_at, :timestamptz, default: fragment("now()")
    modify :updated_at, :timestamptz, default: fragment("now()")
  end
end  

Another place you may want to use this strategy is with uuid columns that need a default value of uuid_generate_v4().