Rails 7 supports NullsFirst for all databases


In the beginning of this year, Rails 6.1 added support for nulls_first and nulls_last Arel methods for PostgreSQL. This was a great addition since, unlike most databases, PostgreSQL would push data with null values to the top when ordering columns. However, in most situations, this was not preferred.

Before

Rails 6.1 introduced the following Arel methods only to PostgreSQL databases to control null data ordering.

irb> User.arel_table[:name].desc.nulls_last

(0.9 ms)  SELECT "users"."name" FROM "users"
          ORDER BY name DESC NULLS LAST

=> [["Adam Driver"], ["Bob Dylan"], [""]]

This was fantastic for PostgreSQL users as the default is set to “NULLS FIRST”. Unfortunately, all other databases were missing out on this.

After

Fortunately, ANSI SQL has an option to allow the database to specify where NULLS come out in the sort order,

ORDER BY column ASC NULLS FIRST

MS SQL, SQLite, Oracle, and PostgreSQL all follow this syntax. Unfortunately, MySQL does not.

Rails 7 now introduces support for nulls_first() and nulls_last() for all major databases, except MySQL. When trying to use nulls_last() on MySQL databases, it will throw a runtime error.