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
file to circumvent some of these quirks.
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:
When Rails executes this, it will create a database index on the non-existent
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
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.
when moving to another database, this will cause an error.
Consider the following SQLite example,
The first and third statement will create an index on the
while the second will create an index on the string literal
However, when the strict mode is enabled,
the second statement will raise an error.
Thanks to this PR
it is possible to enable SQLite strict mode by adding the following line to the
Running the same migration as before will now raise an error: