Who's Afraid of the Big Bad SQL?

Developers often treat a database as a bit bucket, processing data procedurally or using an ORM. Doing the same processing in SQL can be unfamiliar. I’ll compare the two and show that SQL is not scary. And in Oracle 23c you can hide the SQL within JSON views without need of ORM.

I’ll hazard the guess that you (like me) started your developer career by learning a procedural language like Basic, Pascal, C, C++, C#, Java or something else depending on your generation. There’s something about a procedural language that makes it relatively easy to understand what is happening - you can “single step debug” in your head and walk through your code piece by piece.

In SQL that is not quite as easy. You need to think about dealing with sets of data instead of a single row at a time, and the language has a declarative nature where you specify what you want the done and let the database figure out the details of how exactly to get the individual rows pieced together to form the final result. You can rarely follow along what is happening, but you just execute the statement, witness the result, and then need to figure out from that result what actually happened, so you can make changes and try again. You can’t “single step” your SQL code - it is often more of an iterative trial-and-error development process.

So SQL has aquired a reputation - in my opinion unjustified - of being a hard programming language to learn. There’s a gazillion frameworks and ORMs out there that promise that you can write your applications without SQL. The problem is that you cannot really work with your data without SQL - the frameworks simply generate SQL for you. This can be OK in maybe 80% of the cases, but for the last 20% a human is very likely to be able to write SQL that by far outperforms what the framework generates. And for a human that knows SQL, I believe it is even easier and faster to write the SQL than use the framework.

With Oracle 23c an alternative to the ORMs can be using JSON Relational Duality views, hiding the SQL inside views that can be accessed easily as JSON, giving the best of both worlds.

I will take a single use case - picking items by FIFO (first-in-first-out) in a warehouse - and demonstrate coding this procedurally with an ORM versus doing it in SQL, along the way showing differences and similarities in thought processes of the developer of each method. Ultimately I will show that it is not really harder writing a single 100-line SELECT statement than a procedural application doing the same thing.

At the end, I will wrap the SQL in a JSON Relational Duality view to demonstrate how this can be an alternative or a supplement to the ORM.

The goal of this presentation is to line up a practical use case and walk through in detail developing a solution in a procedural language and compare it to developing the same in SQL.

The idea is to show basic and intermediate code and show that SQL is not a language to be afraid of using properly. This will not be a deep-dive performance comparison of Smart-DB versus Java like Toon Koppelaars does - the intended audience of this presentation is beginner and intermediate, trying to convey a feeling of “oh, this SQL isn’t as hard as I thought - it can actually make my coding simpler and easier.”