Understanding SQL Through Rails

Structured and relational databases are everywhere. It is often said that web applications are front end interfaces to a back end database. In a stateless protocol like HTTP, your database keeps the state and serves as the persistence layer. It is the brains behind the machine.

In the traditional sense of the MVC design pattern, your data model solves the problem. The way you architect the database has a direct correlation to the overall solution. If you end up with a database full of convoluted bloat and duplication, your front end code will mirror exactly that.

So, in this article I would like to take a look at SQL databases. As it turns out, Rails makes this easy. The framework makes it easy to think about real world problems as data models and relationships.

The relationships in your data model turn models into real world “objects”. The awesomeness in this is how well it fits within an OOP paradigm. It simplifies code bases, and makes the architecture intelligible and agile.

My hope is that, by the end of this article, you’ll gain an appreciation for structured and relational databases. This will help you understand why your back end structured storage is critical to a sound architecture.

And we all want to design a beautiful architecture, right? Let’s get started.

Setup

You are more than welcome to follow along if you like. I’ll have the code linked at the end of this article.

To get started, type up this canonical command:

rails new understanding_sql_through_rails

For this particular example, it’s a run of the mill posting system. It consists of Users, Posts, and Categories. From this, we’ll define relationships and get down and dirty with SQL.

The primary focus here is the Model within the MVC design pattern.

So, type up:

rails g model User name:string{30}
rails g model Post title:string{40} content:text{3200} user:references:index
rails g model Category name:string{15}
rails g migration CreateCategoriesPostsJoinTable categories:index posts:index

As a best practice, I recommend setting max limits for your fields. In a structured database, the computer will have a much easier time indexing data when you set reasonable sizes. The big advantage of structured data sets is that it takes up a specific amount of room so it can be easily queried. My recommendation is to use this to your advantage. (Note: This is a general statement that applies to most RDBMS systems, with the exception PostgreSQL, where field lengths do not affect performance.)

Now, finish it up with:

rake db:migrate

Easy. Rails has me all setup with my brick and mortar data model. You can investigate your migration files to see what gets generated under the covers. What is awesome about this framework is how simple it is to setup my relational database through simple commands.

To wrap up, make sure the correct associations are in your models.

In app/models/category.rb:

class Category < ActiveRecord::Base
  has_and_belongs_to_many :posts
end

In app/models/post.rb:

class Post < ActiveRecord::Base
  belongs_to :user
  has_and_belongs_to_many :categories
end

In app/models/user.rb

class User < ActiveRecord::Base
  has_many :posts
end

These associations come built into Rails right out of the box. Pay attention to these and read them out loud. A user has many posts, a post belongs to a user, for example. When we get down to the SQL level, these relationships will make perfect sense.

Now put this in your db/seeds.rb:

user = User.create({name: 'Joe'})
post = Post.create({title: 'Post', content: 'Something', user_id: user.id})
Category.create({name: 'Cat1', post_ids: [post.id]})

Finish it up with:

rake db:seed

It is finished.

Relationships

With Rails, your entire database is setup. Now, it is time for us to delve deep into what just happened. The framework does a good job of abstracting it away. Alas, for us, it is time to figure out how this all works.

Type up:

rails console
> post = Post.first
> post.categories
> category = Category.first
> category.posts
> user = User.first
> user.posts

The framework fires all kinds of SQL for you using the relationships. As you can see, a post has and belongs to many categories. Categories have and belong to many posts. A user has many posts.

These relationships are highly intuitive, since you can talk about them in regular plain English. What is so radical in this is you don’t even have to know anything about the technology under the covers. Any person that knows enough about the problem is now fluent in the domain model.

In any solution, the domain model makes up the objects and relationships from the real world. It is the medium you use to relate to your customers. It facilitates communication with folks that don’t care how you solve their problem.

A client or person that only cares about your end product would be well versed in this. The client is more than happy to relate the problem in terms of the domain. This means you, as the architect, are now able to relate a real world problem into a computer program.

Needless to say, remember what I said about how your model solves the problem?

Somehow, your SQL database has a way to track these relationships. But the question remains, how?

SQL Queries

This time, type up:

rails dbconsole

Let the record show that we are officially off the Rails world and in SQL. SQLite should be the default database.

If we want to query posts that belong to a user with an id of one, try:

SELECT id, title FROM posts WHERE posts.user_id = 1;

As shown, when a child object belongs to a parent, the child gets a foreign key added from the parent object.

Now for something harder, say I want posts that belong to the ‘Cat1’ category:

SELECT posts.id,
  posts.title
FROM categories,
  posts,
  categories_posts
WHERE categories.id = categories_posts.category_id
  AND posts.id = categories_posts.post_id
  AND categories.name = 'Cat1';

Here is a diagram of what these relationships look like:

I am using implicit joins. It makes it easy to see the relationships within the WHERE clause. In any given SQL object or table, the id is the primary key. Notice how the primary key becomes the fulcrum on which to draw relationships. The computer will always be better at crunching numbers so primary keys are the ideal place to link information.

I see SQL as Simple Query Language. As such, in my practice I use it for simple querying of information. Doing much more than this means putting business logic inside your SQL. I recommend you avoid this at all costs. Your domain logic does not belong in SQL.

With this foundation, you should be able to make effective use of SQL relationships to reduce data duplication. Every time you see rows in a table full of duplicate data, it is time to step back and think about relationships.

After all, that’s what working with a relational and structured database gets you.

Optimize

You may or may not have noticed the weird :index I added to the end of some columns. I added these to the model generator that creates migration. Turns out, these are indexes. In SQL, indexes optimize your database when you query information across relationships. Every primary key has an index by default, but relational foreign keys do not.

Type this up in dbconsole to see these in action inside SQLite:

.schema

You should see something like:

CREATE INDEX "index_categories_posts_on_category_id_and_post_id"
ON "categories_posts" ("category_id", "post_id");
CREATE INDEX "index_categories_posts_on_post_id_and_category_id"
ON "categories_posts" ("post_id", "category_id");
CREATE INDEX "index_posts_on_user_id" ON "posts" ("user_id");

So, when it comes time architect your domain model, you should be an expert at delivering a high performance solution that makes your client super-happy.

Conclusion

That is a wrap. In my book, SQL databases will be around for a while. They’ll outlive my generation and my children’s generation. I hope you can see how powerful SQL is and how to use it effectively in a real world problem.

You may find the rest of the code on GitHub.

Happy Hacking!