Validations in Rails and MySQL - Part 1

Ruby On Rails provides good ways to validate data. So does MySQL.

This raises a question: how much validation do we want and when do we want it?

Suppose we're building a typical web application where a user creates an account with his name and email address, both required.

Example Before Validations

To define a "User" model: 

  class User < ActiveRecord::Base
  end

To create a "users" table: 

  class CreateUsers < ActiveRecord::Migration

    def self.up create_table :users do |t|
     t.string :name
     t.string :email
    end

    def self.down
     drop_table :users
    end

  end

To describe the "users" table: 

  mysql> describe users;
  +-------------+--------------+------+-----+------------+----------------+
  | Field       | Type         | Null | Key | Default    | Extra          |
  +-------------+--------------+------+-----+------------+----------------+
  | id          | int(11)      | NO   | PRI | NULL       | auto_increment | 
  | name        | varchar(255) | YES  |     | NULL       |                | 
  | email       | varchar(255) | YES  |     | NULL       |                | 
  +-------------+--------------+------+-----+------------+----------------+

We can see that the name and email are both of type varchar(255) and both can be NULL. But that's a problem!

The official email specification says that an email address can have a maximum of 320 characters. Also, our application requirements say that each user must have an email address, so we don't want any NULL values.

Let's handle both issues by improving our code with validations. == Example With Validations

To define a "User" model with validations: 

  class User < ActiveRecord::Base
    validates_presence_of :name
    validates_presence_of :email
    validates_length_of :email, :maximum => 320
  end

To create a "users" table: 

  class CreateUsers < ActiveRecord::Migration

    def self.up create_table :users do |t|
     t.string :name, :null => false
     t.string :email, :null => false, :limit => 320 
    end

    def self.down
     drop_table :users
    end

  end

To show the "users" table: 

  mysql> describe users;
  +-------------+--------------+------+-----+------------+----------------+
  | Field       | Type         | Null | Key | Default    | Extra          |
  +-------------+--------------+------+-----+------------+----------------+
  | id          | int(11)      | NO   | PRI | NULL       | auto_increment | 
  | name        | varchar(255) | NO   |     | NULL       |                | 
  | email       | varchar(320) | NO   |     | NULL       |                | 
  +-------------+--------------+------+-----+------------+----------------+

You can see that the name field cannot be null, the email address cannot be null, and the email address can have a maximum of 320 characters. This is what we want.

Comparison Of Validations In Rails And MySQL

In our example, the validations are happening in two places.

Here is a general comparison of some of the key differences.

Rails Validations MySQL Validations
Summary Keeps business logic all in one place: the Rails mode. Spreads business logic among the Rails model and MySQL table
When Typically when a model calls #save, #valid?, etc. Typically wehenever data is created or updated in a table
Suitability Especially good if the same app will use a different database Especially good if a different app will use this same database
Extensibility Rails has more flexible validations suitable for web applications, and can be supplemented with any Ruby code that we want to write, such as our own custom validations. MySQL has faster validations because they are highly optimized for common cases, and can be supplemented with SQL features such as stored procedures, functions, and triggers.
Optimizability Rails can validate without ever needed to call the database MySQL can optimize for space and data access speed, especially in cases where the fields are fixed-width (e.g. int, boolean, char) instead of variable-width (e.g. varchar, memo)
Cautions Try to ensure Rails handles all data changes, so the database never gets corrupted. Try to ensure the Rails model #valid? works as you want, and has useful error messages.

Recommendations

We typically use validations in both Rails and MySQL because it gives us the best possible coverage to ensure our data is consistent, although it does take a bit more time.

When we need to build a large site, or we need the fastest-possible database writes, then we consider doing these kinds of validations only in Rails, not in MySQL.


What's Next?

blog comments powered by Disqus


WebGet SixArm • http://webget.com • Copyright © 2010 • All Rights Reserved