Put your SQL directly into your HTTP endpoint code

Creating an HTTP web API endpoint by adding hardcoded SQL directly into your endpoint's method is the equivalent of cursing in church. I have worked with software developers who would become physically violent if I told them to do such a thing. However, let's stop and ask ourselves why this apparently is such a bad thing. I really can't see the problem here. Hence, put your SQL directly into your HTTP endpoint code.

There are tons of arguments against it, but once you really think about these, few of them makes much sense for most projects today. Once you've started implementing your project, it's highly unlikely you'll ever want to change database implementation from MySQL to PostgreSQL. So the argument of being able to exchange your database implementation is basically mute.

Using an ORM, especially in complex SQL, often results in sub optimal queries, so most of the time it's much better to manually construct your SQL than to use an ORM tool to abstract away your SQL. Sure, there exists valid arguments here related to caching, but caching is just as easily applied by leaning on functional constructs, and applying the SQL directly into the endpoint method.

Creating a database repository only results in that the code responsible for querying your database and the code responsible for returning the result to the client ends up in different places. This makes the code harder to navigate and maintain, and is quite frankly useless unless you need to reuse the same SQL code in multiple endpoints.

The more files you have to touch to maintain your code, the more difficult your code becomes to maintain

In fact, the idea that you need to create several abstractions between your endpoint and the database access layer is probably remnants of bad ideas originating from weaknesses in OOP. Once you're within a functional programming language, it's quite easy to put the actual SQL query directly inside the HTTP endpoint method, while still applying several layers of "abstractions" between your actual SQL code and the endpoint itself. This ensures that the code responsible for querying the database, and the code responsible for returning the result to the caller are in the same file, in the same method. This makes your project much easier to maintain and navigate. And if you need additional abstractions, you can use functional constructs to intercept the execution of your SQL, and the endpoint method itself.

Superstition

In Africa there's a migratory bird specie that will fly in a large circle every time they migrate. Zoologists couldn't figure out why, before they asked geologists for help. The behaviour originated from the fact that some 20 million years ago or something, there was a mountain there. The birds had to fly around the mountain to migrate, and this ended up becoming a part of their "survival of the fittest" strategy. Today the mountain has been gone for millions of years, but the birds still spends a lot of energy on taking huge detours. A lot of the things we do as software developers are similar types of remnants from a distant past, that made sense decades ago, but are no longer useful to us.

In Hyperlambda we even created GUI components to create SQL based HTTP web API endpoints. Above is a screenshot of the process. This allows any SQL guru to wrap his SQL inside an HTTP web API endpoint, without knowing any other languages but SQL. Below is a video where I walk you through the process.

Honestly, I cannot think of a single reason why the above is not a valid way to create at least some of your HTTP web API endpoints, interacting with your SQL database somehow. It takes care of SQL injections, it allows you to intercept the code and add additional layers of abstractions, it keeps the code clean and neat, it allows me to reuse the same Hyperlambda file in multiple projects if I want to, etc, etc, etc. If you want to reproduce what I'm doing in the above video, you can register a free trial cloudlet below.

• Create a free Low-Code SQL generator cloudlet