Add timestamps to existing table in db Rails 5+

Question

Trying to add Timestamps to existing table. According to Api documenation add_timestamps

Here is my code in migration:

  def change
    add_timestamps(:products, null: false)
  end

Getting error:

*-- add_timestamps(:products, {:null=>false})
rails aborted!
StandardError: An error has occurred, this and all later migrations canceled:
SQLite3::SQLException: Cannot add a NOT NULL column with default value NULL: ALTER TABLE "products" ADD "created_at" datetime NOT NULL*

I've also tried all solution in this thread

Same error... Rails 5.1.4 Ruby 2.4.0

Actually you could do it adding the columns without passing `options`, that's what `add_timestamps` do in Rails 5, `null: false` is the default value. Correct me if I'm wrong. — Sebastián Palma, Oct 02 '17 at 08:22

spickermann · Accepted Answer · 2020-04-03T06:24:49.977

42

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

edited Apr 03 '20 at 06:24

answered Oct 02 '17 at 07:28

spickermann

100,941
9
101
131

3

Risky to use an ActiveRecord model in your migrations -- for example if you rename `Product` later this will fail. Better to just do it with SQL: `update "UPDATE products SET created_at = NOW(), updated_at = NOW()"` which will always work. – Meekohi Jan 27 '20 at 18:53

score 37 · Answer 2 · answered Jun 06 '19 at 11:28

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

score 3 · Answer 3 · answered Oct 23 '18 at 17:03

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 ;)

jeremiemv · Answer 4 · 2021-01-22T00:53:46.347

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

score 0 · Answer 5 · answered Jun 16 '20 at 03:54

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

score 0 · Answer 6 · answered Mar 30 '23 at 13:43

I did similar to @jeremiemv, except a few less steps. If you don't change the column default after adding timestamps with a default, then the default value shows up on the table structure as the time that the migration ran.

The difference with my solution also, is that you don't need to manage the null: true then false when you are providing a default value.

add_timestamps :products, null: false, default: Time.current
change_column_default :products, :created_at, nil
change_column_default :products, :updated_at, nil

Rails 7 and MySQL here

Note that this migration is irreversible. `change_column_default` should have `from` & `to` options. — Al17, Jun 08 '23 at 10:14

Add timestamps to existing table in db Rails 5+

6 Answers6

Linked