Rails 5 uses 't' and 'f' to represent booleans, in SQLite databases, because there is no native boolean support. In Rails 6, the behaviour changed to represent booleans as 1 or 0.
Broken queries
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 't' and 'f' values to 1 or 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.
Automated testing
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