0

I have a model (Company) with a HABTM self-join relationship (the join table calls associated companies suppliers and purchasers).

I want to prevent companies from ever having duplicate suppliers or purchasers, so I'm adding a uniqueness constraint on supplier-purchaser pairs. I'm adding this constraint at both the database level:

create_table :supply_link, id: false do |t|
  t.belongs_to :supplier,  null: false, index: true
  t.belongs_to :purchaser, null: false, index: true
end

add_index :supply_link, [:supplier_id, :purchaser_id], unique: true

and the data model level:

class Company < ApplicationRecord
  has_and_belongs_to_many :purchasers, -> { distinct },
                                       join_table: :supply_link,
                                       class_name: :Company,
                                       foreign_key: :supplier_id,
                                       association_foreign_key: :purchaser_id
  has_and_belongs_to_many :suppliers, -> { distinct },
                                      join_table: :supply_link,
                                      class_name: :Company,
                                      foreign_key: :purchaser_id,
                                      association_foreign_key: :supplier_id

end

But in trying to write a spec for this arrangement, I noticed something strange: typically, data model validations preempt database constraints — but not here.

That is, if you set both a data model validation (validates :email, presence: true) and a DB constraint (t.string :email, null: false), then the data model catches invalid operations (like User.create(email: nil)) before the DB ever even sees them.

But in this case, when I try to add the same supplier to a Company multiple times, it goes to the database first, and raises an error. If I remove the database constraint altogether, then the data model handles it the right way, and the duplicate entries never get added to the suppliers attribute. two things happen: 1) the duplicate associations all appear in the join table, but 2) the model doesn't show any duplicates for company.suppliers (thanks @Pavel Mihailyuk). I've tested this manually in the console, and with RSpec/FactoryGirl.

What's going on here, and what's the right way to write the specs? Should I keep both validations and expect invalid operations to raise an ActiveRecord error? Should I remove the database constraint and rely solely on the data model's -> { distinct } scope? Should I report this as a bug in Rails?

Ryan Lue
  • 916
  • 10
  • 29
  • "...and the duplicate entries never get added to the `suppliers` attribute" - it it is not a fact. You have to test it in db console not in Rails one due to `-> { distinct }`. Golden rule: only DB constraints will save your data :) – Pavel Mikhailyuk Jul 07 '17 at 12:45
  • You again! You're absolutely right; thanks, I knew I forgot to check somewhere. – Ryan Lue Jul 07 '17 at 16:40
  • Never trust Rails or any part of it :) Build your app around DB, add DB constraints/indexes as many as required by business logic. Always check what exactly query AR have built, what exactly DB state is after migration (example `\d tablename` in psql). That is only way to have live production app :) – Pavel Mikhailyuk Jul 07 '17 at 17:14
  • Ok, so this still begs the question: how should one write specs to ensure that the DB constraints work in cases where invalid data is normally caught by the data model? You can't expect an `ActiveRecord` error when the data model doesn't try to save invalid input... – Ryan Lue Jul 09 '17 at 04:35
  • You can search for "HABTM uniq". There are many answers on SO. Like https://stackoverflow.com/questions/21896450/habtm-duplicate-records – Pavel Mikhailyuk Jul 10 '17 at 08:59
  • As for test, you have to decide what the app flow in case of duplicate insert attempt. I.e. who should catch exception. Then you can build test on top of it – Pavel Mikhailyuk Jul 10 '17 at 09:02

0 Answers0