Specific Data Types with Ecto Migrations

I just want the data types that I want. Ecto understands me.

I do a lot of Rails development, so I've become used to the ActiveRecord migration DSL forcing certain SQL data types on me. I work primarily with Rails applications backed by PostgreSQL databases. PostgreSQL is a powerful database, so I like to take advantage of it as much as possible. This includes being picky about my data types.

One point of frustration I have with the ActiveRecord DSL is that it forces the timestamp without time zone data type on me. I'd much rather use timestamp with time zone. In Rails migrations, this means that I write my migrations with SQL heredocs. So, what do things look like in the land of Elixir?

Ecto migrations tend to use the :datetime atom for timestamp fields (this includes the timestamps() function). This translates into timestamp without time zone once that SQL is generated and executed.

Fortunately, Ecto makes it easy to be more specific about the kind of data type that I want for a certain field. Check out the following migration:

  def change do
    create table(:posts) do
      add :content, :string
      add :title, :string
      add :published_at, :timestamptz

      timestamps
    end
  end

Once migrated, this gives me the following table:

    Column    |            Type             |                  
--------------+-----------------------------+------------------
 id           | integer                     | not null default nextval('posts_id_seq'::regclass)
 content      | character varying(255)      |
 title        | character varying(255)      |
 published_at | timestamp with time zone    |
 inserted_at  | timestamp without time zone | not null
 updated_at   | timestamp without time zone | not null
Indexes:  
    "posts_pkey" PRIMARY KEY, btree (id)

The :timestamptz atom took effect. My published_at column is of type timestamp with time zone. As I pointed out earlier though, the default timestamp fields, inserted_at and updated_at, are both of type timestamp without time zone.

I can ditch the timestamps helper, though, and get closer to the data types I want.

  def change do
    create table(:posts) do
      add :content, :string
      add :title, :string
      add :published_at, :timestamptz
      add :inserted_at, :timestamptz
      add :updated_at, :timestamptz
    end
  end

Sure, I had to type a few more characters, but that is a small price to pay for getting the data model just how I want it.

Although, I'd be lying if I said I was satisfied with the posts table at this point.

I'm not interested in restricting both my content and title fields to 255 characters. I'd rather my title field be a plain varchar data type. As for the content field, I think its intent is better conveyed with the text data type.

With Ecto migrations, I can get there no problem. Here is the updated migration:

  def change do
    create table(:posts) do
      add :content, :text
      add :title, :varchar
      add :published_at, :timestamptz
      add :inserted_at, :timestamptz
      add :updated_at, :timestamptz
    end
  end

And here is the resulting table description:

    Column    |           Type           |                     
--------------+--------------------------+---------------------
 id           | integer                  | not null default nextval('posts_id_seq'::regclass)
 content      | text                     |
 title        | character varying        |
 published_at | timestamp with time zone |
 inserted_at  | timestamp with time zone |
 updated_at   | timestamp with time zone |
Indexes:  
    "posts_pkey" PRIMARY KEY, btree (id)

I now have exactly the data types I want for each column.

Now, Ecto migrations are certainly no replacement for SQL, but it is clear that Ecto gives me quite a bit more control over the resulting data model than I am used to having with other ORMs. That is a big plus in the Ecto/Phoenix column for me.