Foreign Key constraint issues using sequel and database cleaner

Question

I am running into issues using database cleaner with sequel and sqlite foreign key constraints. Specifically, I am using the :truncation strategy with Capybara integration tests.

For the given sample schema:

CREATE TABLE users(id INTEGER PRIMARY KEY, name TEXT);
CREATE TABLE events(id INTEGER PRIMARY KEY, title TEXT);

CREATE TABLE events_users(
  user_id INTEGER,
  event_id INTEGER,

  FOREIGN KEY(user_id) REFERENCES users(id),
  FOREIGN KEY(event_id) REFERENCES events(id)
);

And sequel models:

class User < Sequel::Model
  many_to_many :events
end

class Event < Sequel::Model
  many_to_many :users
end

Running the following:

# normally this would be run in
# an rspec before(:each) for my :feature specs
DatabaseCleaner.start
DatabaseCleaner.strategy = :truncation

bob = User.create(name: "bob")
sally = User.create(name: "sally")
event = Event.create(title: "Everyone's invited")
event.users << [bob, sally]

DatabaseCleaner.clean

Results in an error

SQLite3::ConstraintException: FOREIGN KEY constraint failed (Sequel::ForeignKeyConstraintViolation)

I can get around this by altering my before statement to disable the foreign_keys PRAGMA:

DB.foreign_keys = false
DatabaseCleaner.start
DatabaseCleaner.strategy = :truncation

(or not using FOREIGN KEY in my tables), but that seems wrong since I want the benefits that foreign keys constraints — or at least I think I do ;).

Is this a fundamental misunderstanding of how to use foreign key constraints, or is there a better way to do this?

score 2 · Accepted Answer · answered Jun 18 '17 at 00:29

It's been almost two years and I've given up attempting to solve this cleanly T_T.

I experienced random FOREIGN KEY constraint failed issues during a recent sequel upgrade. To resolve this, I switched from DatabaseCleaner.strategy = :truncation to DatabaseCleaner.strategy = :deletion.

There's been a thorough analysis of the benefits/costs of :truncate versus :delete and the answer (at least for postgres) is it depends. So far :delete seems to be a little faster for my small testing data set.

Foreign Key constraint issues using sequel and database cleaner

1 Answers1