Cannot save empty array to database when serialized

Question

In Ruby on Rails, a model with a serialized array field will not update on .save() if the array is empty, where it previously had data.

I'm using:

• Ruby 2.2.1

• Rails 4.2.1

• sqlite3 1.3.10

  I created a new model with a field set as text:

  rails g model User name:string example:text

In the User.rb file I added:

serialize :example, Array

I instantiated a new instance of the User class:

test = User.new
<User id: nil, name: nil, example: [], created_at: nil, updated_at: nil>

Then I save the user to make sure it saves correctly:

test.save()
(0.1ms)  begin transaction
SQL (0.4ms)  INSERT INTO "users" ("created_at", "updated_at") VALUES (?, ?)  [["created_at", "2015-05-27 16:17:31.902342"], ["updated_at", "2015-05-27 16:17:31.902342"]]
(0.7ms)  commit transaction
=> true

And added some data in so that it feels happy and purposeful:

test.example.push(1)
test.example.push(2)

And save it up:

test.save()
(0.1ms)  begin transaction
SQL (0.3ms)  UPDATE "users" SET "example" = ?, "updated_at" = ? WHERE "users"."id" = ?  [["example", "---\n- 1\n- 2\n"], ["updated_at", "2015-05-27 16:17:50.331777"], ["id", 1]]
(0.8ms)  commit transaction
=> true

And made sure everything saved nicely:

test
<User id: 1, name: nil, example: [1, 2], created_at: "2015-05-27 16:17:31", updated_at: "2015-05-27 16:17:50">

I deleted one item, verified it has been deleted, and saved it, making sure the SQL output shows the UPDATE:

test.example.delete(1)
 => 1
test
<User id: 1, name: nil, example: [2], created_at: "2015-05-27      16:17:31", updated_at: "2015-05-27 16:17:50">
test.save()
(0.1ms)  begin transaction
SQL (0.9ms)  UPDATE "users" SET "example" = ?, "updated_at" = ? WHERE "users"."id" = ?  [["example", "---\n- 2\n"], ["updated_at", "2015-05-27 16:18:30.148553"], ["id", 1]]
(8.9ms)  commit transaction
=> true

I deleted the last piece of data from array, verified empty array, and saved it. Note the lack of an UPDATE action and that it returns true:

test.example.delete(2)
=> 2
test
<User id: 1, name: nil, example: [], created_at: "2015-05-27 16:17:31", updated_at: "2015-05-27 16:18:30">
test.save()
(0.1ms)  begin transaction
(0.1ms)  commit transaction
=> true

Multiple saves get the same result. A new User object still has that last piece of data in it:

test = User.find(1)
User Load (0.2ms)  SELECT  "users".* FROM "users" WHERE "users"."id" = ? LIMIT 1  [["id", 1]]
<User id: 1, name: nil, example: [2], created_at: "2015-05-27 16:17:31", updated_at: "2015-05-27 16:18:30">

A workaround is removing the "Array" from the serialize line in the model initializes the field as nil. But this means the first time I add data to a new instance I have to set the field manually to an empty array (test.example = []) in order to call .push() on it. Everything works fine in this setup, and the newly emptied array saves happily to the DB.

I saw a closed issue on the Rails Github indicating that serialized columns should always be saved, but have no idea if this is pertinent:

https://github.com/rails/rails/issues/8328

I could not discern anything from the serialize source code that could illuminate me:

http://apidock.com/rails/ActiveModel/Serializers/Xml/Serializer/serialize

Why does adding "Array" at the end of the serialize line cause empty arrays to not be saved to the database?

Answer 1

SQLite does not support an Array column type. I think what's happening is when you try to save the User with an empty array for the example attribute, it's being interpreted as no change to the example column. What happens if you try this after you create your test user with example data?

test.example = nil
test.save

Alternatively, what happens with this?

test.example = [nil]
test.save

It seems like the solution would be to use an ActiveRecord callback such as before_save to check the user model's example attribute to determine if it is an empty array. If so, set the attribute to nil or [nil] (whichever works) and then the data should persist accordingly.