Notes on Ecto Migrations

I got tripped up recently when trying to write my first Ecto migration.  The style in Ecto changed from specifying migrations as DDL strings to a database-agnostic style via macros and functions.  The problem I was running into is that most tutorials/posts refer to the older string-based style.   I was using a newer version of Ecto with DDL strings and my tables weren't being created.

The goal of this post is to give a basic overview of the new style and give some examples of its usage.  All examples assume Postgres as the target database.

In the new migration syntax, tables are created with the create macro and columns are specified using the add function.  An example of an up migration:

def up do
  create table(:table_name) do
    add :column1, :string
    add :column2, :integer
    ...
  end
end

The table's name is specified as an atom.  Columns are defined by the add function:

add(column, type \\ :string, opts \\ [])

where the first argument is the name of the column (an atom), the second argument is the type of the column (also an atom), and the remaining arguments are passed as options (more on this below).  Ecto provides the following basic type atoms (the resulting Postgres column type follows as a comment):

:integer  # integer
:float    # double precision
:boolean  # boolean
:string   # character varying
:binary   # bytea
:uuid     # uuid
:decimal  # numeric
:datetime # timestamp without time zone
:time     # time without time zone
:date     # date

Arrays are supported as composite-types having a tuple of :array and type atoms:

{:array, :string}  # character varying[]
{:array, :integer} # integer[]

The default type for the add function is :string, which results in an unbounded character varying type in Postgres.  So this:

add :post_body  

is the same as this:

add :post_body, :string

The add function also accepts arbitrary atoms, allowing for database-specific types.  Instead of using :string, :text could be used for Postgres's text character type:

add :post_body, :text  # text instead of character varying

The add function's opts argument accepts the following options:

:primary_key
:default
:null
:size
:precision
:scale

Option Examples:

By default, Ecto will create a serial primary key column for your table with a name of "id".  You can override this behavior by passing "primary_key: false" in the table function and passing the  "primary_key: true" option in a column's add function:

create table(:table_name, primary_key: false)
  add :doc_id, :uuid, primary_key: true
end

Other options:

Making a column not-null (the default is to allow nulls):

add :title, :string, null: false

Giving a column a default value:

add :author, :string, null: false, default: "Anonymous"

Limiting the size of a column:

add :state_cd, :string, size: 2  # character varying(2)

Setting precision and scale:

add :latitude, :decimal,  precision: 14, scale: 11 # numeric(14, 11)

Other:

Ecto will add inserted_at and updated_at columns if you add a "timestamps" call in the body of the create macro:

create table(:table_name)
  add :title, :string
  
  timestamps
end

Note that in Postgres, these are created as "timestamp without time zone" columns that do not allow nulls and do not have a default value.   A default value can be specified in your model.

There's a lot of stuff missing from this post, but it should be enough to get you up and running with the new migration style.  Ecto is still young and changing.  When in doubt, consult the source.