Add timestamps to existing table in db Rails 5+

You cannot add columns with not-null constraint to a non-empty table because the existing lines in the table would have empty values right away and therefore the condition fails.

Instead, introduce the columns in three steps:

def change
  # add new column but allow null values
  add_timestamps :products, null: true 

  # backfill existing records with created_at and updated_at
  # values that make clear that the records are faked
  long_ago = DateTime.new(2000, 1, 1)
  Product.update_all(created_at: long_ago, updated_at: long_ago)

  # change to not null constraints
  change_column_null :products, :created_at, false
  change_column_null :products, :updated_at, false
end

In my opinion, it is wrong to manipulate existing data with activerecord queries or even SQL in migrations.

The correct rails 5.2+ way to do this is :

class AddTimestampsToCars < ActiveRecord::Migration[5.2]
  def change
    add_timestamps :cars, null: false, default: -> { 'NOW()' }
  end
end

It's a proc so you should be able to set a date in the past if you want to.

Source: https://github.com/rails/rails/pull/20005

I like @spickermann's approach since it takes into account the existing records and probably your migration already went all the way to production, his method ensures data perseverance.

Nevertheless, many of you guys might find yourselves in that situation, but still in development, meaning that there's no real sensitive data you might be afraid of losing... That gives you a bit more freedom on how you can perform the change in the table.

If your code and records only exist locally (if you still have no records created, just skip step 1.) and that table was created in the last migration , my suggestion is:

1.- Delete all the records from that table.

2.- Go to your migration file and edit it by adding t.timestamps so that it looks something like this:

    class CreateInstitutionalLegals < ActiveRecord::Migration[5.0]
      def change
        create_table :institutional_legals do |t|
          # Your original migration content goes here
          .
          .
          t.timestamps # This is your addition
        end
      end
    end

3.- Then go to your console and enter rails:db:redo. As explained here, that command is a shortcut for doing a rollback and then migrating back up again.

Now you will see that your schema is updated with the corresponding created_atand updated_at columns.

The concrete benefit of this is that it is super easy to do, you don't create an extra migration file and you learn to use a very handy command ;)

I'm on rails 5.0 and none of these options worked. The rails:db:redo will work but isn't a feasible solution for most.

The only thing that worked was

def change
    add_column :products, :created_at, :timestamp
    add_column :products, :updated_at, :timestamp
end

I had the same issue. I wanted the end result to be strictly equivalent to add_timestamps :products on an fresh database.

Instead of running a query to backfill, I ended up doing a 3-steps process.

• add column with null allowed and default to current time to backfill
• change constraint to not null
• remove default

And it is reversible.

    add_column :products, :created_at, :datetime, precision: 6, null: true, default: -> { "CURRENT_TIMESTAMP" }
    add_column :products, :updated_at, :datetime, precision: 6, null: true, default: -> { "CURRENT_TIMESTAMP" }

    change_column_null :products, :created_at, false
    change_column_null :products, :updated_at, false

    change_column_default :products, :created_at, from:  -> { "CURRENT_TIMESTAMP" }, to: nil
    change_column_default :products, :updated_at, from:  -> { "CURRENT_TIMESTAMP" }, to: nil

NB: This is with Rails 6.1 and PostgreSQL