SQLite is usually very forgiving of unexpected
or unusual inputs.
However, when moving from SQLite to another database,
this can cause problems.
Rails 7.0 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)