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

Answer 1

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

Answer 2

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