Another day, another undocumented Rails feature!

This time, it’s that ActiveRecord::Base.connection.add_index supports an undocumented option to pass a string argument as the value for ‘column’.

This string is passed directly to the SQL statement, making it possible to use all sorts of fun things to lock down the constraint. It also means that you can make an index more declaratively if you’re not comfortable using the built-in options for key lengths or ordering.

Here’s a real-life example of the String invocation, where I am making a case-insensitive unique index on folder titles that ignores leading and trailing whitespace:

add_index :folders,
          "user_id, TRIM(BOTH FROM LOWER(title))",
          unique: true,
          name: :user_folders_title_unique_idx

I’m making a unique index on "folders"."title", scoped to "folders"."user_id". All standard so far, except for how the title is declared:

TRIM(BOTH FROM LOWER(title))

These are Postgres functions, and will act on the value that is passsed to it. In this case, TRIM will strip whitespace from the string passed to it. I’m adding BOTH so that both leading and trailing whitespace is trimmed. LOWER, unsurprisingly, converts the string passed to lowercase.

As far as I know, because this is passed directly to the CREATE INDEX call, any thing that could be passed to a raw SQL index creation call can be provided to this argument of add_index.

I have also verified that the index call is correctly serialized in schema.rb. In the case of the above index, it is recorded as:

create_table "folders", force: :cascade do |t|
  # ...
  t.index "user_id, btrim(lower((title)::text))", name: "user_folders_title_unique_idx", unique: true
end

Finally, something important to note: Rails can normally figure out what the index should be called by sticking the table name and column names you want in your index together with underscores. Not so in this case - because you are passing your own index statement, you must provide the name: option to add_index like I have above, so that the index can be identified.