Rails 7 adds default value support for binary columns for SQLite


While ActiveRecord is a behemoth encompassing a large feature-set, some WTFs pop up every now and then. One such WTF is the lack of support for default values for binary columns in SQLite.

Before

Let’s create a string column in a model that uses a SQLite database and add a default value to it.

# db/migrate/20220821142603_add_string_to_physician.rb
# class AddStringToPhysician < ActiveRecord::Migration[7.0]
#   def change
#     add_column :physicians, :signature, :string, default: "Regards"
#   end
# end

irb(main):001:0> physician = Physician.create! name: "Belle"
                 ...
irb(main):002:0> physician.signature
=> "Regards"
irb(main):003:0> physician.reload.signature
=> "Regards"

Let’s do the same with a binary column.

# db/migrate/20220821142330_add_greeting_to_physician.rb
# class AddGreetingToPhysician < ActiveRecord::Migration[7.0]
#   def change
#     add_column :physicians, :greeting, :binary, default: "Hello!"
#   end
# end

irb(main):001:0> physician = Physician.create! name: "Belle"
   (0.6ms)  SELECT sqlite_version(*)
  TRANSACTION (0.0ms)  begin transaction
  Physician Create (0.3ms)  INSERT INTO "physicians" ("name", "created_at", "updated_at", "greeting", "signature") VALUES (?, ?, ?, ?, ?)  [["name", "Belle"], ["created_at", "2022-08-21 15:47:44.957101"], ["updated_at", "2022-08-21 15:47:44.957101"], ["greeting", nil], ["signature", "Regards"]]
  TRANSACTION (0.4ms)  commit transaction
...
irb(main):002:0> physician.greeting
=> nil
irb(main):003:0> physician.reload.greeting
=> "Hello!"

This creates an inconsistency in the behavior of Sqlite with Rails.

After

Fortunately, this PR adds support for reading a binary column’s default values before the its data is read from the database.

irb(main):001:0> physician = Physician.create! name: "Belle"
   (0.6ms)  SELECT sqlite_version(*)
  TRANSACTION (0.0ms)  begin transaction
  Physician Create (0.3ms)  INSERT INTO "physicians" ("name", "created_at", "updated_at", "greeting", "signature") VALUES (?, ?, ?, ?, ?)  [["name", "Belle"], ["created_at", "2022-08-21 15:47:44.957101"], ["updated_at", "2022-08-21 15:47:44.957101"], ["greeting", "<11 bytes of binary data>"], ["signature", "Regards"]]
  TRANSACTION (0.4ms)  commit transaction
...
irb(main):002:0> physician.greeting
=> "Hello!"
irb(main):003:0> physician.reload.signature
=> "Hello!"

Join Our Newsletter