Lynn Chang's Blog

How I learned to write raw SQL

Photo by Stephen Andrews on Unsplash

How I learned to write raw SQL

Lynn Chang's photo
Lynn Chang
·

2 min read

In my daily job, I mainly use Rails framework. To make code readable and maintainable, we seldom write raw SQL in the codebase. Instead, we use Rails's ActiveRecord::QueryMethods module which helps developers write beautiful queries quickly. For example,

School.joins(:students).where('students.age > ?', 7).limit(100)

Recently, I was assigned to query different operation metrics, product metrics, and marketing metrics. Due to the diversity of these metrics, I found out that if I know what the raw SQL of the metrics is, not only can I complete the query quickly, but also I can use ActiveRecord::QueryMethods more efficiently.

Here are the 4 steps that how I practice writing raw SQL.

1. Browse the Active Record association declaration

Each metric must derive from one or more models. In the huge codebase, we might not be familiar with every model, only when we can clearly understand the association declaration between the models do we write the correct query condition.

2. Imitation is the start of all learning

At first, I know how to query by Rails ActiveRecord::QueryMethods, but I don't know how to write the raw SQL.

It's OK. Rails console can help us.

When asking about the result below, we can realize that it'll return an ActiveRecord::Relation object.

# rails console

Student.where(name: 'Lynn').class
#=> Student::ActiveRecord_Relation

ActiveRecord::Relation class provides a #to_sql method, it will return sql statement for the relation after it is called.

# rails console

Student.where(name: 'Lynn').to_sql
#=> "SELECT \"students\".* FROM \"students\" WHERE \"students\".\"name\" = 'Lynn'"

When we use #to_sql frequently, we can be more familiar with raw SQL.

3. Practice by Blazer gem

Using #to_sql is like a learning process of input. Output is essential if we would like to be good at writing SQL.

[Blazer](Explore your data with SQL. Easily create charts and dashboards, and share them with your team.) is a Ruby gem.

Explore your data with SQL. Easily create charts and dashboards, and share them with your team.

After installing and configuring it to the application, we can write the raw SQL directly.

Blazer does help my SQL skills a lot.

Photo from: github.com/ankane/blazer

4. Take a quiz from SQLZOO

SQLZOO is a SQL tutorial, which provides many query samples and quizzes that we can try. After learning by imitating and practicing writing SQL, I can finish more and more examination questions in SQLZOO now.

I have a sense of accomplishment during this learning process.

Hope you find this information helpful, also.

SQL