Handling Advanced Database Features with Rails

Among Rails programmers, advanced database features are often a point of contention. Some contend that constraints, triggers, and procedures are essential; some shun them completely, saying that intelligence belongs in the application only. I am sympathetic to the argument that all business logic belongs in the application; it is nearly impossible to make agile changes to changing requirements when logic is split between two locations. Still, I believe that constraints, triggers, and even stored procedures have their place in enterprise applications. In order to explain why, we'll have to examine a distinction that comes up often in relation to this debate: the difference between application and integration databases.

Application Versus Integration Databases

Martin Fowler differentiates between application databases and integration databases.* The basic distinction is that an integration database is shared among many applications, while an application database "belongs" to the one application using it. In this sense, "application" can mean one program or multiple programs within an application boundary (the same logical application). Usually this distinction refers to how the schema is organized; in Rails, integration databases are often referred to as databases with "legacy schemas." In application databases, integration can still be performed through messaging at the application layer rather than the database layer.

Rails is opinionated about how your database schemas should be structured: the primary key should be id, foreign keys should be thing_id, and table names should be plural. This is not database bigotry; Rails has to choose a sensible default for the "convention over configuration" paradigm to be effective. It is relatively painless to change almost any of these defaults. Rails plays nice with integration databases.

Many Rails developers shun integration databases as unnecessary; they maintain that all integration should be done at the application layer. Some take that a step further and state that data integrity checking belongs in the application only, to keep all business logic in the same place. Although this might be ideal, the real world is not always that nice. Even if all integration can be done at the application level, there are still plenty of valid reasons to use database constraints.

In addition, most databases in the enterprise tend to become integration databases over time. Databases that are useful for one purpose are often appropriated for another use. Sometimes you have a database that isn't under your control, and you want to use the data without performinga full ETL (extract, transform, load). Even running a small script against your database without using the ActiveRecord model, or maintaining the database manually through a console client such as mysql or psql, means you have something accessing your database outside of your domain model. If the validations in the domain are the only way to ensure that the data is consistent, this may lead to problems.