Sql knowledge will make you a better developer

An ORM is supa-dupa , but what if it's getting difficult ? This is why you need SQL knowledge

4 minutes

Sql knowledge will make you a better developer

Huh ? Why ? I have an ORM …

Yes indeed, you are using an ORM like Eloquent, and so do i. I use it as much as possible, I actually love it. But I do look at the queries the ORM generates and can interpret them because I know SQL. I’ve actually learned it at school 20 years ago and it is still very actual.

How to look into the SQL an ORM generates

I’ll talk about Laravel, the framework I’m using every day. Laravel has some great third party tools that makes it very easy to look into that SQL. In fact, Laravel has its own tool to look into it , the tool called telescope.

I like telescope , but I have to admit that I don’t use it so much. I’m a bigger fan of a debugbar like that of Barry van den heuvel. It has a tab to view the sql statements that were executed by your application.

And what is the advantage of that insight ?

It unlocks the possibility of speed monitoring on database level. Tools like telescope or barryvdh debugbar show you the amount of time a sql needs to be executed against your database. So if a query is slow you can copy it and run it against your database in a query tool like Querious. And than some SQL knowledge can be very useful.

Another pitfall can be detected very fast with those toolbars. The 1 + n problem. Assume you create a relation between a post and a user ( author ). Every post has an author. If you create an overview page showing you 100 posts with the corresponding user then you have to get the authors of that posts. While taking a look into the sql’s ran it’s possible that you detect a 1 + n problem. Meaning that for all the 100 posts an additional query is executed, resulting in 1 + 100 SQL statements in total. This can be solved by eager loading the author. Which will result in a total of 2 queries instead of 101.

The importance of indexes is underestimated.

And index is a mechanism that helps your database system to find data faster. Assume the relation between a post and a user. The id in the users table will be indexed automatically by your database, because it’s the primary key. But in the posts table the user_id ( foreign key ) will not be indexed automatically. Assume you have a big dataset it will be much harder for your database system to retrieve the related posts for a user is there is no index on the user_id in the posts table.

You have to take the same in account when searching data. It’s much harder for your database system to retrieve data from an unindexed field. I’ve seen situations where the addition of an index speeds up the query from 10 seconds to a couple of milliseconds.

So take care of your indexes !

That’s all ?

No it isn’t … What about scripts that need nitro speed … Scripts that store data into exports like csv files. Or maybe situations where you have to calculate sums on related tables. You can hand over the heavy lifting to your php script. Although, most of the time it’s a better idea to put some logic into your sql via subselects or logical operations in your sql. This makes it a lot easier for your php scripts and your server. Luckily we have the possibility to add raw statements to and eloquent builder, so you can combine the bost of both worlds. But, look out for sql injection while using raw statements if you rely on user input.

My opinion on sql knowledge nowadays

In my opinion there is a growing lack of sql knowledge. We’ve seen a huge shift to ORM’s the past 20 years , and i’m a big fan of it. But there is also a big shift in sql knowledge. There is a growing lack of underlying knowledge and this makes it a lot more difficult to keep focus on overal speed of an application. Especially in companies where there is no dedicated database engineer this can be a problem. Because in a development environment with little data everything is super fast. But what if there are a million records in that unindexed database.

The base concepts and pitfalls that come with joins, subselects, grouping aren’t learned thoroughly anymore at schools and that is something we really feel in the field.

this article has been read 6 times