Rails adds :strict option to the default SQLite database.yml file

SQLite is usually very forgiving of unexpected or unusual inputs. However, when moving from SQLite to another database, this can cause problems. Rails 7.1 adds a :strict option to the default database.yml file to circumvent some of these quirks.

Before

The default mode of SQLite allows database indexes to be created for columns that don’t even exist. This is because of the way SQLite resolves single and double strings, which found its way into the way Rails adds indexes.

Let’s consider the following migration:

  class AddAgeToPatients < ActiveRecord::Migration[7.1]
    def change
      add_column :patients, :age, :integer
      add_index :patients, :birthday
    end
  end

When Rails executes this, it will create a database index on the non-existent birthday column. This is because SQLite resolves single and double strings differently. Single strings are resolved as column names, while double strings are resolved as string literals.

So when Rails executes the add_index statement, it will resolve :birthday as a string literal, and create a computed index for that string. It seems silly to create an index for a string literal, but it’s not a problem for SQLite. However, when moving to another database, this will cause an error.

Consider the following SQLite example,

  CREATE INDEX index_patients_birthday ON patients (birthday);
  CREATE INDEX index_patients_birthday ON patients ('birthday');
  CREATE INDEX index_patients_birthday ON patients ("birthday");

The first and third statement will create an index on the birthday column, while the second will create an index on the string literal birthday. However, when the strict mode is enabled, the second statement will raise an error.

After

Thanks to this PR it is possible to enable SQLite strict mode by adding the following line to the database.yml file:

  strict: true

Running the same migration as before will now raise an error:

  == 20221017105541 AddAgeToPatients: migrating =================================
  -- add_column(:patients, :age, :integer, {:default=>10})
    -> 0.0016s
  -- add_index(:patients, :birthday)
  rails aborted!
  StandardError: An error has occurred, this and all later migrations canceled:

  SQLite3::SQLException: no such column: birthday
  /Users/swaathi/code/work/has_many_through_connection_test/db/migrate/20221017105541_add_age_to_patients.rb:4:in `change'

  Caused by:
  ActiveRecord::StatementInvalid: SQLite3::SQLException: no such column: birthday
  /Users/swaathi/code/work/has_many_through_connection_test/db/migrate/20221017105541_add_age_to_patients.rb:4:in `change'

  Caused by:
  SQLite3::SQLException: no such column: birthday
  /Users/swaathi/code/work/has_many_through_connection_test/db/migrate/20221017105541_add_age_to_patients.rb:4:in `change'
  Tasks: TOP => db:migrate:up:primary
  (See full trace by running task with --trace)