1

What's the reason behind making methods for database queries instead of allowing raw queries? for example get(...)->where(...).

  • Guess1_ We're repeating ourselves otherwise(DRY) ---> I don't think this is a good reason because this way, it will still be repeated inevitably.
  • Guess2_ By using methods we have the benefit of avoiding typos! ---> Typos could happen on methods too!
  • Guess3_ By using methods IDEs can help us type queries faster! ---> Sounds like a good reason.
  • Guess4_ They are more readable! ---> Another good reason, but I'm not still sure about the reason the designers came up with this decision.
  • Guess5_ Some parts of the queries could be omitted and replaced by some more abstract bindings, and they also become more concise in some cases, for example: $books = Book::selectAll(); (in PHP) ---> Good reason.

I'm trying to figure out the main reason designers came up with this decision. I think raw queries are much more flexible to write! For example you don't have to think about how "this example query Q" could be written using these package of methods! q()->g()->y('D',a_callback_here)->z();

Thank you in advance.

  • 1
    One upside of having an ORM is that you can use a variety of database engines, and can expect it to work the same way - which might not be the case for raw queries. – Alex Szabó Sep 16 '18 at 18:32
  • 1
    do you mean why use queryable ORMs or why Hide SQL in repositories? – Ewan Sep 16 '18 at 18:51
  • Somebody please correct me if I'm wrong, but I believe that using a good ORM mitigates against many types of attacks such as SQL injection. – user949300 Sep 16 '18 at 22:47
  • 2
    @user949300: just like parametrized queries. Using an ORM just to prevent SQL injection attacks is like choosing Haskell over C just to avoid buffer overflow bugs. – Arseni Mourzenko Sep 16 '18 at 22:49
  • @Arseni Mourzenko Considering all the expensive and dangerous bugs and hacks caused by buffer overflows and SQL injection, ORMs and Haskell look mighty good to me. – user949300 Sep 17 '18 at 4:50
2

It's just an abstraction.

Aside basic mapping between in-code objects and relational database tables, the goals of an ORM are:

  • To abstract the database away from the developer. The idea behind an ORM is that any developer who knows the programming language can persist data in a relational database with no prior knowledge of SQL. Since SQL is quite difficult to learn, this is a nice benefit to have.

  • To be able to optimize queries. Queries generated by an ORM won't always be the same as the queries written by developers. Sometimes a developer, if he have enough skills, would write a better (i.e. most efficient) query. Sometimes, however, ORMs would do a better job. If you take an enterprise-grade ORM and a programmer who learned SQL for less than a year, the ORM would often do a much better job.

Those benefits don't target only beginner programmers. Developers who know SQL quite well could still benefit from an ORM:

  • It's not particularly interesting to write simple SQL queries by hand and maintain them later. Why not letting the machine do the work?
  • Sometimes, for more complex cases, an ORM can have a clever solution to a problem in terms of the SQL query.
  • When an ORM failed to do its work properly, it's not a big issue; ORMs let you override their work by a custom SQL query.
4

If you write raw SQL everywhere, and the database schema changes (or another database is used, with different features), you would have to go through potential hundreds or thousands of code pieces and adjust them, potentially having no idea what they each are doing, because someone else wrote them. Most of the changes you do would be 99% the same, hundreds of times.

If you have a method, you just change the method. Done.
And you are sure you caught all places.

  • 1
    I don't see the difference. grep and sed (or a replace all of an IDE) would do the job even if you use hardcoded SQL queries. I would even take the risk to assert that it would be actually easier to do with plain SQL queries as soon as they are grouped within the same file: smaller risk of breaking things, and the diff would be so much nicer to read. – Arseni Mourzenko Sep 16 '18 at 22:47
  • 4
    @ArseniMourzenko, your comment makes me think you have never seen real-world SQL statements. I'm not talking about SELECT * FROM table INTO fields, that is rather rare. – Aganju Sep 16 '18 at 23:51
  • So my Guess1 is mostly the best guess except that I missed one point: "If duplicated code can be controlled from on point, maybe then, it's NOT a cursed duplicated code!" Thank you @Aganju. – aderchox Sep 17 '18 at 11:02
2

One potential benefit of using such methods would be to abstract away the differences between SQL dialects. The same SQL query used on MySQL may not be optimal for, or even compatible with, MS SQL, for example. Potentially, they could also be used with non-SQL databases.

(That said, I too generally also prefer direct SQL)

  • Thank you. This is interesting you prefer direct SQL. You mean you don't use ORMs (personally)? I thought every scaled project would be bound to using them. – aderchox Sep 17 '18 at 10:57
  • @Narnia Some projects need an abstraction layer e.g. because some clients use Oracle and others SQL Server. With other projects, I know there will only be a single database. Then this is a question of preference: writing raw SQL is just as valid as using some abstraction. Personally, I don't see the point in constructing queries like $table->select(...)->where(...)->execute() when I could simply write the SQL. I still need to understand SQL when using such methods – they are a leaky abstraction. In any case, it's more important to separate data access from business logic. – amon Sep 17 '18 at 12:26
  • @Narnia no, I do not use ORM's, and yes, I've worked on very large database projects. While they save some typing, I just don't see the advantage over the long term. – GrandmasterB Sep 17 '18 at 17:45

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.