Rails 5 uses
'f' to represent booleans, in SQLite databases, because there is no native boolean support. In Rails 6, the behaviour changed to represent booleans as
This behaviour change breaks certain queries. For example, WHERE queries that search for boolean values:
1 Account.where(upgraded: 't')
Fixing broken queries
There are a few ways to solve the problem. One option is to do a database migration, to update
'f' values to
0. However, this solution creates risk because it makes rolling back more difficult. The reverse migration would need to be applied, in order to roll back.
Another option is to re-write queries to search for the specific boolean representation, in addition to the truthy value:
1 Account.where('upgraded IS ? OR upgraded IS ?', 't', true)
The drawback of this solution is that it adds some complexity (technical debt) to the Rails application. However, it does mean we can upgrade/rollback between Rails 5 and 6 easily.
Also, this change can be temporary. Once the Rails 6 upgrade has been fully tested, we can do a DB migration and revert the complex query.
Why wasn’t this bug caught by automated tests? The specs start each test run with a fresh database. That means, they never test a ‘Rails 5 database’ against a Rails 6 app.
We can write a tests for this by doing a raw SQL insert. It’s ugly but does the job:
1 2 3 4 before do sql = 'INSERT INTO accounts (name, upgraded, created_at, updated_at) VALUES ("example", "t", "2022-02-17 08:22:35.864889", "2022-02-17 08:22:35.864889")' records_array = ActiveRecord::Base.connection.execute(sql) end