What is DrySQL?

DrySQL is a Ruby plug-in that extends ActiveRecord and applies the DRY principle to Object-Relational Mapping.

DrySQL reflects on your database's schema and automatically generates your model classes' associations and validations, and correctly identifies your columns and keys.

The philosophy behind DrySQL is that you define columns, keys, constraints, and relationships on your database, and you shouldn't need to re-define any of these things in your application code.

What Databases are Supported by DrySQL?

• MySQL >= 5.0

• PostgreSQL >= 8.0

• SQL Server
  - Should work with any version of SQL Server that implements the information_schema views

• Oracle
  - Should work with any version of Oracle that implements the information_schema views

• DB2 LUW
  - You must correctly install IBM's DB2 Rails adapter in order to use this

• DB2 iSeries
  - You must correctly install IBM's DB2 Rails adapter (ibm_db) in order to use this.
  - You must install DB2 Connect
  - Your schema must contain the iSeries information_schema views. These are created automatically by DB2 following execution of a CREATE SCHEMA statement, with the exception of schemas created as old-style iSeries "libraries"
  

*Support for SQLite will be in a future release.

Overview of Features

1. Legacy DB table & column support
DrySQL doesn't make assumptions about the names of your columns. If the primary key for your Employee table isn't called “employee_id”...fine. DrySQL gets all of the information about your table from the DB's information schema. With legacy tables, the only burden the programmer has to bear is to call set_table_name in their class definition. If your table name follows the ActiveRecord naming conventions...your class definition can be empty or even non-existant (Check out the By Example section for an example of this)

2) A DB connection is not required in order to process your class definition.
DrySQL does not assume that it is being used in a Rails web application and does not assume that a DB connection is available when your class is loaded. DrySQL does not attempt to use a DB connection until you instantiate an ActiveRecord::Base subclass, so you can “start” your application without a connection (and avoid a performance hit on startup, if that matters to you)

3) Automatic generation of associations and validations on instantiation
Associations and validations for ActiveRecord::Base subclasses are generated on object instantiation. This means that your app doesn't take a performance hit for DB I/O until you need to. If you never use your ActiveRecord::Base::Employee class, DrySQL never attempts to map it to a DB table or generate associations and validations. This also means that you don't need to “do” anything in order to generate associations and validations. You don't need to run a script or explicitly call a method in order to generate associations and validations for your class. They are generated automatically for your class the first time you create an instance of it. Obviously, your app takes a small performance hit for this extra DB I/O the first time each ActiveRecord::Base subclass is instantiated, but it's a one-time-only hit (per ActiveRecord::Base subclass).

4) Seamless integration with ActiveRecord.
DrySQL was implemented as extensions to existing ActiveRecord classes and modules. All you need to do to get the benefits of DrySQL is require_gem 'drysql' somewhere in your code before you attempt to instantiate ActiveRecord objects (either through Class.new or any ActiveRecord finder method).

5) Support for composite unique keys.
ActiveRecord provides an indexes method that aims to identify unique keys on your table, but breaks down when you introduce composite unique keys (eg. False positives on components of composite keys). DrySQL has support for identifying composite unique keys, and the groundwork has been laid for future support of composite PKs and FKs

6) Introduction of new validation: validates_nullability_of
A common pattern is to use the validates_presence_of validation on columns that have been declared NOT NULL. For character data, this validation errors on an empty string, even though this value would be considered valid by the DB. DrySQL introduces a validation that actually corresponds with the DB's NOT NULL constraint. This validation is automatically generated only in situations where the DB would reject a NULL (eg. the validation is not generated if a default value is specified for the column or if the column is auto-generated, since the DB accepts a NULL value in these cases)

7. On-the-fly DB schema re-generation
DrySQL introduces ActiveRecord::Base.regenerate_cached_schema (as of version 0.1.4), which deletes cached constraints, columns, keys, associations, and validations for a particular model class. This allows you to change your underlying DB structure and have DrySQL and ActiveRecord re-generate these artifacts for your model class the next time it is instantiated...without having to terminate your application and re-start.

8. Upfront Association and Validation Generation
DrySQL introduces ActiveRecord::Base.generate_orm (as of version 0.2.0), which will generate the associations and validations for all your model classes. If DrySQL encounters a table that is referred to by one of your model classes, but does not have a model class declared for it, DrySQL will generate the model class in memory if the table follows ActiveRecord naming conventions. DrySQL continues to generate associations and validations dynamically by default (i.e. on first instantiation of a model class), but this new method offers some flexibility to those who prefer to take the performance hit upfront. The upfront strategy also offers slightly better per-model performance for association generation.