Rails 5 -> 6 Upgrade: SQLite boolean query bug

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:

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:

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:

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)