When I first started learning SQL, I used to write classic Perl CGI scripts with my “old friends”. It is wonderful; A transformation in my career as a software engineer. Without that initial understanding, how to divide business concepts into separate tables, then link and search across those tables with English syntax.

Back then, my colleague and I had to programmatically build each INSERT , UPDATE and SELECT . We didn’t use a relational mapper object (it may already exist; it doesn’t seem to be in the minds of the team). A lot of times, building SQL queries is tedious and error-prone. So imagine my pleasure when I first choose Rails and start replacing raw SQL with elegant Ruby code doing database operations.

Currently, 14 years after creating my first Rails application, I rarely have to write my own SQL. Active Record has handled all the work. It helps protect against silly errors and security holes, when used properly. To be honest, recalling SQL immediately made me encounter some difficulties and wasted a lot of time thinking about how to write an SQL statement when ActiveRecord didn’t support it.

Has anyone of us been wondering, with Active Record as powerful as it is now, why does a Rails developer need to have an understanding of SQL?

It can be said that, when working with ActiveRecord in Rails, we almost do not need to know about SQL to build a stable application, but with the 7 scenarios below, you may need to rethink. on getting to know SQL better

Better manual queries with Active Record

Note that this is not always the case. Active Record is a very powerful tool that greatly supports Rails developers, but not in all cases.

What happens when you want to include data from a separate table in your query? If you know SQL, then you will know you need to use JOIN . ActiveRecord conveniently incorporates a joins method to accomplish this, but if you don’t know the jargon, or the concept behind it, you may find yourself looking blindly for an answer.

Execute queries that Activerecord cannot do

Active Record abstracts a lot of SQL, but not all. For example, you cannot perform LEFT OUTER JOIN by using Active Record alone until Rails 5 is released. If your application is still running an earlier version or you need to use a single SQL function for your database vendor, then you will need to write the SQL query manually.

That comes with a downside: Part of the magic behind Active Record is the abstract class that it applies to many database vendors. Writing raw SQL instead of it can make your code less flexible. This means that if you switch from database engine to database engine, you may need to rewrite the query. Make sure that you have done the full test and be sure to apply this method.

As mentioned, we will probably only change database engine a few times, so don’t worry if you wrote SQL manually because Active Record doesn’t support it.

Perform special queries – ad-hoc queries

Your app may have a strong admin page with nice reports presented in a clever user interface, but what would you do when asked about some data that wasn’t immediately available? via the user interface? Depending on your production environment setup, immediate access to the data is possible via the database console. You can immediately handle those jobs through SQL queries.

Improve application performance

As you know, Active Record takes a few extra steps to convert the query from a sequence of Ruby methods to an SQL statement to move to the database engine . However, it is not always fast enough. In such cases, it’s important to understand how to compile an SQL query that gets the information you need quickly and accurately.

The same database vendor lock-in warning applies. If you use performance tips that are only available to the current database provider, you will need to rewrite the query if you convert engines.

Understand inheritance code

Active Record gains new features with every Rails release. If your application has already been posted to production, you may encounter queries that could not be performed by Active Record at the time and therefore written by hand. With some understanding of SQL, you can understand what previous developers need to do with the code and query mentioned. You can confidently change queries and even use the latest version of Active Record.

Among the reasons I have listed here, this is the reason I encounter most often in the projects that I work on. Raw SQL really stands out when hooked up to beautiful Ruby code, but I know it’s no coincidence or stealth that developers have previously used it. And sometimes, I can rewrite the query with Active Record tools that aren’t available to previous developers and minimize the contextual transition for future developers.

Not just working with Rails

I have worked professionally with Ruby, Perl, Python, PHP, ASP, JavaScript and maybe some other languages ​​that I forgot, to develop backend for web applications. In all those scenarios, the SQL-based database is constant. If you’ve ever found yourself working with a language or framework other than Ruby on Rails, the techniques of connecting the framework to the database may be completely different, but hidden behind that, it’s likely that it’s still SQL. . And understanding the fundamentals will help you pick out what’s unique about how the framework interacts with the database.

Prepare for the interview

This may not sound like a good idea, but in fact it is a very important thing for your long-term benefits. Any job will often list (strong) knowledge of SQL as a mandatory requirement. If you enter an interview tomorrow morning, can you demonstrate such an understanding?

No one can guarantee that new languages ​​or frameworks will eventually replace Ruby and Rails. But with SQL, that would be hard to replace. And as I was hoping now to convince you, there are many other necessary reasons to keep certain knowledge about SQL, because you never know when it will be useful.

Article translated from source . Thank you for watching!