rails: insert_all throws `ActiveRecord::NotNullViolation` for timestamps

Steps to reproduce

# frozen_string_literal: true

require "bundler/inline"

gemfile(true) do
  source "https://rubygems.org"

  git_source(:github) { |repo| "https://github.com/#{repo}.git" }

  gem "rails", github: "rails/rails"
  gem "sqlite3"
end

require "active_record"
require "minitest/autorun"
require "logger"

# This connection will do for database-independent bug reports.
ActiveRecord::Base.establish_connection(adapter: "sqlite3", database: ":memory:")
ActiveRecord::Base.logger = Logger.new(STDOUT)

ActiveRecord::Schema.define do
  create_table :posts, force: true do |t|
    t.string :content

    t.timestamps
  end
end

class Post < ActiveRecord::Base
end

class BugTest < Minitest::Test
  def test_insert_all
    Post.insert_all([
      { content: "first" },
      { content: "second" }
    ])
  end
end

Expected behavior

I think insert_all should auto set the timestamp value unless explicitly given.

Actual behavior

If a Model has t.timestamps then it by default add NOT NULL constraint on created_at and updated_at due to which insert_all fails unless created_at and updated_at values are given.

System configuration

Rails version: 6.0.0.beta1

Ruby version: v2.6.0-dev

About this issue

Original URL
State: closed
Created 5 years ago
Reactions: 25
Comments: 19 (16 by maintainers)

Links to this issue

Why does Active Record Migrations not set default value for created_at in the migration files?

Most upvoted comments

For this use-case, I would suggest giving the timestamp columns a default value: (c.f. #20005)

- t.timestamps
+ t.timestamps default: -> { 'CURRENT_TIMESTAMP' }

I think insert_all shouldn’t autofill timestamps for a couple of reasons:

It’s a sister to update_all, which does not
It’s a “close to the metal” experience so it should do as little as possible:
- because its leanness is the quality you want when you’re optimizing a bottleneck and picking insert_all over create
- because it’s natural to have more “road feel” at that level (i.e. fewer manipulations of the inputs you pass to the INSERT that gets generated, fewer buffers between you and the database’s constraints and validations)

+22

boblail on Mar 6, 2019

for the latest version of the rails, you can use the create_with option for default attributes.

Post.create_with(created_at: Time.now, updated_at: Time.now).insert_all([ { content: "first" }, { content: "second" } ]) refer to this: https://api.rubyonrails.org/classes/ActiveRecord/Persistence/ClassMethods.html#method-i-insert_all

ghost on Jul 15, 2022

@rusikf no, that feature was rejected

simi on May 25, 2020

Ahh, thanks for clarifying! …and congrats on 6.0

ansonhoyt on Aug 23, 2019

My thinking is that a great use case for insert_all is importing data from a CSV which might not have columns for created_at/updated_at, so I think it does make sense for Rails to have your back there as it does for create/update via ActiveRecord::Timestamp.

On the other hand, if people are using upserts we don’t know if a record exists in the db beforehand, so we don’t know if we should provide created_at too. But that’s perhaps a larger problem with upsert_all.

Having a way to provide defaults ala insert_all(data, defaults: { created_at: Time.now, updated_at: Time.now }) could also work.

How does insert_all fare as a relation? For instance, does author.posts.insert_all(data) infer :author_id?

kaspth on Mar 11, 2019

Hmm, tried the where(created_at: Time.now) but still had to supply created_at: Time.now in the attributes array.

ansonhoyt on Aug 23, 2019

No, I don’t think we should support automatic inference of timestamps and prefer Post.where(created_at: Time.now).insert_all(posts) as mentioned in the comment.

Also, the insert_all docs don’t show timestamps having to be passed, but this issue leads me to believe they do need to be provided.

That suggests the documentation needs to be updated. Please do investigate.

kaspth on Aug 23, 2019

I have prepared solution for this based on https://github.com/rails/rails/pull/35631 and https://github.com/rails/rails/pull/35635 (merged).

With those code changes it should be easier, but still transparent how to handle created_at/updated_at columns during upsert/upsert_all.

now = Time.now
row = { slug: 'new-title', post: 'New post' }

Post.upsert_all([row],
            unique_by: { columns: [:slug]},
            defaults: { created_at: now, updated_at: now },
            update: [:post, :updated_at])

It looks similar to me to update_all where you need to specify updated_at value manually as well if you need to update it:

Post.update_all(post: 'deleted', updated_at: Time.now)

We can update documentation to make it clear and also include this in rails guides as well.

simi on Mar 20, 2019

Thanks for your response @boblail . Adding timestamps feels necessary to me because

If we are choosing over insert_all over create then we might need to write down migration just to add default: -> { 'CURRENT_TIMESTAMP' } which does not feel right to me.
While writing a migration to add default 'CURRENT_TIMESTAMP' to timestamp. I did face a weird issue. Default was only added to updated_at and not to created_at. Might be I am doing something wrong here 😄

change_column_default :messages, :created_at, -> { 'CURRENT_TIMESTAMP' }
change_column_default :messages, :updated_at, -> { 'CURRENT_TIMESTAMP' }

It’s a sister to update_all

Here the timestamp not null constraint is already satisfied so even if it doesn’t update it won’t fail but in case of insert_all it would until set default. If setting timestamp is not option may good error message when NotNullViolation is thrown for timestamps.

Maybe Rails team member could guide us here. cc/ @sikachu @kaspth

abhaynikam on Mar 6, 2019