Retroactively add timestamps to a Phoenix/Ecto project

I chose not to add timestamps to a couple of tables in the DevDecks application at first, mainly because I was moving fast to build the app and prioritized other things first. As you and I both guessed, I needed to do that later on and here’s what I had to do to get things working.

First I created the migrations for the tables I was adding the datetime fields to; cards and decks. I created and ran the migrations one at a time, this example only shows what I did for cards but I mimicked these steps with decks as well.

The migration:

I found this Stackoverflow answer to be helpful for creating the migration. I follow this migration file code snippet with explanations for what some the different steps are doing and why.

defmodule DevDecks.Repo.Migrations.AddTimestampsToCards do
  use Ecto.Migration

  def up do
    alter table(:cards) do      
      timestamps null: true
    end

    execute """
    UPDATE cards
    SET updated_at=NOW(), inserted_at=NOW()
    """

    alter table(:cards) do
      modify :inserted_at, :utc_datetime, null: false
      modify :updated_at, :utc_datetime, null: false
    end
  end

  def down do
    alter table(:cards) do
      remove :inserted_at
      remove :updated_at
    end
  end
end

The explanations

``` timestamps null: true ``` We need this because by default using the timestamps method creates a not null constraint and your code will error when trying to add timestamps against existing db rows because those rows wouldn't have values for the inserted_at and updated_at columns the timestamps method creates.

execute """
UPDATE cards
SET updated_at=NOW(), inserted_at=NOW()
"""

Update cards records to have updated_at and inserted_at values.

modify :inserted_at, :utc_datetime, null: false
modify :updated_at, :utc_datetime, null: false

Undo the original timestamps null: true call from above now that the existing records have values.

remove :inserted_at
remove :updated_at

If rolling back the migration is needed then remove the datetime columns.

After running this migration my cards table was setup properly to timestamp new records.

Adding to the schema and changeset:

In my card.ex context I updated the schema to include the updated_at and inserted_at fields and then also added them to the changeset:

schema "cards" do
  field :updated_at, :utc_datetime
  field :inserted_at, :utc_datetime
end

def changeset(params \\ %{}) do
  %DevDecks.Card{}
  |> cast(params, [:updated_at, inserted_at])  
end

The last step was to update my create method in the same card.ex file. I updated the params to include values for the dates using the NativeDateTime module and then could cast the updated params to the changeset and insert into the database:

def create(params \\ %{}) do
  params = Map.merge(params, %{"updated_at" => NaiveDateTime.utc_now, "inserted_at" => NaiveDateTime.utc_now})

  %DevDecks.Card{}
  |> cast(params, [:uuid, :answer, :question, :deck_uuid, :deck_position, :inserted_at, :updated_at])
  |> Repo.insert()
end

If you found this useful I also wrote a post about migrating to Elixir’s Earmark for markdown processing here: