How Code School Uses SQL

It may not be obvious to all new developers, but SQL is an extremely important language. Almost every application on the web uses a database in some fashion. Whether debugging, improving performance, or just for general application development, knowing SQL can greatly improve a skill set.

Here at Code School, SQL is vital to daily operations. With a Ruby on Rails application, the majority of SQL is handled by ActiveRecord. However, there will be a time where it’s required to use the find_by_sql method when more complicated queries are required, or we want to call a stored procedure. When working with Node.js and communicating with a database, writing SQL is very common.

Essentially, every application at Code School communicates with a database in some form or another. A developer can spend quite a bit of time coding, building features, and fixing bugs without ever having to type any SQL. But SQL is really one of the foundations of software development, and eventually you’ll be faced with having to write some.

One area of development that typically involves writing SQL is metrics and reporting. Because some metrics require querying across large joins and gathering large amounts of data, the typical ORM (object-relational mapping) can become slow or unusable. At Code School, metrics are pulled via SQL because, as a fellow developer says, “relations only get me so far.” What do we prefer? SQL views. A view is a virtual table based on the result set of a SQL statement and is used for security and simplicity. When more and more complicated joins are required to gather needed information, ActiveRecord tends to become less and less performant. So we generally resolve this issue by bypassing the ORM and directly writing SQL that will greatly outperform ActiveRecord. In the past, triggers and stored procedures were previously used at Code School. However, the trend has shifted away from this, and SQL is being written directly within the applications.

While developing Ruby on Rails applications, we often use the backtrace from the Rails server to debug performance and issues. This backtrace includes the SQL that ActiveRecord is actually executing, as well as the time it took to execute each query. It’s that knowledge and understanding of SQL that allows our developers to properly diagnose and tune Rails to be the best it can be. For instance, an n + 1 is easily recognized by looking at the SQL being output by ActiveRecord.

When working with SQL, performance and security are usually the highest priorities. There are ways an application can submit a user’s input directly to the database to be executed. If a user maliciously enters SQL into this field, there is a possibility it could be executed against the database and cause irreparable damage. This is referred to as SQL injection. At Code School, this is something that’s constantly guarded against and corrected where necessary. On top of that, proper joins and indexes are applied at Code School to ensure all applications are not hindered by a slow-performing database (tip: these topics will be covered in greater detail in the coming SQL courses).

Building our SQL tutorial, Try SQL, (obviously) required writing quite a bit of SQL. Two executors were leveraged to build this course that sandbox a database to run SQL code against. One uses a sqlite3 database browserified to run on the client — a fast, safe approach to executing SQL. A second executor uses Docker containers with PostgreSQL running inside. This means more complicated queries are performed on a full-fledged database while still maintaining a high level of security. Each challenge incorporates large amounts of SQL together with tests to ensure the submitted code is error free and accomplishes the task at hand.

With SQL being the backbone of almost all applications at Code School, learning and becoming proficient at SQL is one of the best moves a developer can make. As a developer learns more SQL, the mysteries of ActiveRecord and other ORMs becomes much clearer. Ready to get started with SQL? Be sure to check out our free course, Try SQL! We cover the the fundamentals of SQL, including database and general table structure, and you can start writing SQL. Either way you decide to learn, having SQL under your belt will surely make you a better developer in the end.