Rails Migrations and PostgreSQL Constraints 7
A question was posed on the Rails mailing list concerning how one would go about adding CONSTRAINTs to the database tables with ActiveRecord::Migration.
One argument was raised stating that it is easier to handle these in plain SQL schema files. I disagree. :-)
Migrations to the Rescue
Databases evolve and I have recently found the Migration structure to be perfect for handling iterations and schema changes. Using the #execute method has helped move more of my code into the Ruby/Rails framework… and that just makes things easier to manage in the long-run. This is the approach that we are using at PLANET ARGON with some of our current client projects.
# db/migrate/6_add_foreign_key.rb
class AddForeignKey < ActiveRecord::Migration
def self.up
execute "ALTER TABLE bees ADD CONSTRAINT beehive_id_fkey FOREIGN KEY
(beehive_id) REFERENCES beehives (id);"
end
def self.down
execute "ALTER TABLE bees DROP CONSTRAINT beehive_id_fkey;"
end
endThis gives us an easy way to use the standard, #create_table syntax for building our tables with Ruby… and then we can slap these constraints on later.
This would add the constraints…
rake migrate VERSION=6
...run tests…
rake
...roll back…
rake migrate VERSION=5
I have found that this approach is really useful with testing in Rails. When I think that I have everything working great (without CONSTRAINTS in PostgreSQL), I run another migration to add a bunch of foreign key and data constraints to the tables and… run my tests again.
Let’s give Active Record a Hug
This has helped me gain some trust in Active Record while still giving me that comforting feeling that PostgreSQL is acting as the body guard for my data.
Even if you don’t end up using Migrations to handle these types of database schema changes, I would highly suggest that you model your implementation after this. I’ve worked with many database schemas and this just makes it easy to add your new change and run one command to commit it to the database.
...and now I go play with beehives…
Enjoying the content? Be sure to subscribe to my RSS feed.
Hopefully there’s some kind of notification so you’ll see this comment on such an old blog post :-)
I’m currently writing an application with RoR & Postgres as it’s database (I’ll let you know if it every gets anywhere close to being finished).
I’m finding the migrations side of Activerecord an absolute godsend. It would be great if some of the constraints features of Postgres were available rather than having to use the EXECUTE method.
I was wondering if the OPTIONS could be used to specify constraints, is this something you’ve looked at at all?
Big thanks for this post! :)
Unfortunately, thanks to MySQL’s idiotic habit of using inconsistent syntax, your foreign key stuff is not DB-agnostic to the “prefered” Rails DB (and also the one that many of my clients run).
The self.up will work for MySQL, but the self.down will fail, because in MySQL, foreign keys work like this:
ALTER TABLE foo ADD CONSTRAINT foo_fk …
ALTER TABLE foo DROP FOREIGN KEY foo_fk …
(sigh) If only the toy database would use:
ALTER TABLE foo DROP CONSTRAINT foo_fk
...then it would work.
Fortunately, as with the rest of Ruby, ActiveRecord can be extended to fix this. At that point, one might as well put all of the FK stuff in there as well… but that is a post for another day.
Unfortunately, thanks to MySQL’s idiotic habit of using inconsistent syntax, your foreign key stuff is not DB-agnostic to the “prefered” Rails DB (and also the one that many of my clients run).
The self.up will work for MySQL, but the self.down will fail, because in MySQL, foreign keys work like this:
ALTER TABLE foo ADD CONSTRAINT foo_fk …
ALTER TABLE foo DROP FOREIGN KEY foo_fk …
(sigh) If only the toy database would use:
ALTER TABLE foo DROP CONSTRAINT foo_fk
...then it would work.
Fortunately, as with the rest of Ruby, ActiveRecord can be extended to fix this. At that point, one might as well put all of the FK stuff in there as well… but that is a post for another day.
If migrations aren’t used to create the test or production dbs, your FKs will never be added to those databases. I recently found this: http://wiki.rubyonrails.org/rails/pages/Foreign+Key+Schema+Dumper+Plugin
Hopefully, this will fix the issue, make FKs a database agnostic part of Rails and get pushed into the main line code.
Plugins from RedHill rocks. Thanks.
There’s a real useful printable PDF cheatsheet for Rails Migrations here: Rails Migrations Cheatsheet