Wrong postgresql constraint in Rails 7 schema

Question

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?

max · Accepted Answer · 2022-11-09 09:30:29Z

1

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:

edited Nov 9, 2022 at 9:30

answered Nov 9, 2022 at 9:23

max

103k15 gold badges113 silver badges177 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

max Over a year ago

I would really recommend the first linked article as it has some good tips for dealing with the growing pains of SQL schema dumps.

23tux Over a year ago

I didn't know there is a SQL dump format for the schema, very cool! Thanks!

Collectives™ on Stack Overflow

Wrong postgresql constraint in Rails 7 schema

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related