Cori (KOR-ee) is currently an idea swirling in my head and this repository an exercise in Readme Driven Development. It is inspired by foreigner, consistency_fail, and the Using database constraints wisely talk given by Barun Singh at BostonRB. Cori will comb your models and database to suggest foreign keys and indexes that should be added to your database to maintain consistency with your application layer.
Right now, Cori does absolutely nothing. I hope to change that over the next month or two, but I wanted to capture and publicize the idea in the hope that it might put me on the hook to deliver...
Add this line to your application's Gemfile:
And then execute:
Or install it yourself with:
$ gem install cori
From within your rails application directory, execute:
This will scan your application and report on the following types of missing indexes or constraints.
- Todo - Missing Foreign Keys: Your database will be scanned for all columns ending in
_type. If those columns do not have foreign keys defined, cori will output the syntax to add them via a migration (requires the
- Todo - Missing Unique Indexes: Your models will be scanned for calls to
:validates_uniqueness_of. If corresponding unique indexes do not exist in your database, cori will output the syntax to add them via a migration. If the uniqueness validation is scoped, Cori will suggest a compound index. Cori will take a best guess at the desired order of these columns, prefering to add foreign key columns first, but the developer should review and possibly ammend the suggested ordering. See the rationale section for details.
- Todo - Missing Indexes on Foreign Key Columns: If indexes do not exist on columns cori identifies as foreign keys, cori will output the syntax to add them via migration. Cori will not suggest indexes on columns that are included as the first column in a compound index.
- Todo - Missing Not Null Constraints: Your models will be scanned for unconditional presense validations. If the corresponding database column does not include a
NOT NULLconstraint, cori will output the syntax to add one via migration.
Todo: What about the other way? What if I have unique indexes on columns not included in a uniqueness validator on the model or a NOT NULL constraint on column that has no presence validator? Should cori reccomend removing these? The goal is application and database consistency... hmm...
Why would anyone want to do any of this?
- Foreign Keys: Adding explicit foreign keys to the database layer offers an additional level of protection over the logic in your application layer. Even if your application logic is rock solid, can you honestly say you will never directly run an insert on your database or that no other application will interface directly with the database?
- Unique Indexes:
:validates_uniqueness_ofdoesn't work as well as you think it does. It is subject to failure in concurrency situations and also when adding multiple records via a parent record that
:accepts_nested_attributes_for. While the latter case can be worked around in application code, the former cannot. Adding a unique index to the database layer is a good safetynet for these situations.
- Indexing Foreign Keys: You almost certainly want to index some of the foreign keys in your database and many will argue you should go ahead and index all of them. Indexed foreign key columns are less important on tables with few rows or with low cardinality in the foreign key column. It's possible the query optimizer will simply ignore your index in that case, meaning you pay a small penalty on writes to maintain the index that has no effect on reads. In my experience, these tables also tend to have few writes so I prefer to err on the side of indexing all foreign keys. The indexes may eventually prove useful as small tables grow.
- NOT NULL Constraints: Presence validators don't have the problem that uniqueness validators do, but if any other applications (or you yourself via db console) have write access to the database, you may want the same protections on the database level that are offered at the application level.
- Fork it
- Create your feature branch (
git checkout -b my-new-feature)
- Commit your changes (
git commit -am 'Added some feature')
- Push to the branch (
git push origin my-new-feature)
- Create new Pull Request