Wrong postgresql constraint in Rails 7 schema-CodePudding

I have this migration in a Rails 7 app using Postgresql 13:

class AddContractsDateRangeConstraint < ActiveRecord::Migration[7.0]
  def up
    execute "CREATE EXTENSION IF NOT EXISTS btree_gist"
    execute <<~SQL
      ALTER TABLE contracts
        ADD CONSTRAINT date_overlap_exclude
        EXCLUDE USING GIST (
          employee_id WITH =,
          daterange(starts_on, ends_on) WITH &&
        )
    SQL
  end

  def down
    execute "ALTER TABLE contracts DROP CONSTRAINT date_overlap_exclude"
  end
end

When executing via rails db:migrate the constraint gets added to the DB like this and everything works:

app_development=# \d  contracts
...
Indexes:
...
    "date_overlap_exclude" EXCLUDE USING gist (employee_id WITH =, daterange(starts_on, ends_on) WITH &&)
...

The generated schema.rb from Rails looks like this:

create_table "contracts", force: :cascade do |t|
  # ...
  t.index "employee_id, daterange(starts_on, ends_on)", name: "date_overlap_exclude", using: :gist
  # ...
end

This looks suspicious, as it lacks the whole EXCLUDE part from my constraint. And indeed, when creating the database from the generated schema using rails db:schema:load the constraint is broken and is generated like this:

app_development=# \d  contracts
...
Indexes:
    "contracts_pkey" PRIMARY KEY, btree (id)
    "date_overlap_exclude" gist (employee_id, daterange(starts_on, ends_on))

And of course, the whole constraint doesn't work anymore. Any idea how to solve this?

CodePudding user response：

The Rails Ruby schema dumper works very well for simple applications and use cases but it only actually understands a very limited subset of SQL. As you have experienced here any parts of the database schema that it doesn't understand are simply lost in translation.

Once you get the point where you need to use functions, constraints, materialized views or any other database specific features you need to use SQL schema dumps which are much more high fidelity since its just using the databases own dumping tools to output the schema as SQL. While you're losing the simplicity and database-agnostic characteristics of schema.rb this is an inevitable change as the complexity of the application grows.

You can switch the schema format with a simple line in your configuration:

module YourApp
  class Application < Rails::Application
 
    # Add this line:
    config.active_record.schema_format = :sql
  end
end

When you run rails db:schema:dump it will now output a structure.sql file instead. At this point you should delete your schema.rb and check structure.sql into version control.

See: