Put your SQL Straight into your Endpoint

For 50 years we've accumulated thousands of so called "software development best practices". One of our best practices is that we need to separate the data layer from the representation layer. This is typically done by creating a multitude of services, following the principles of SOLID, resulting in dozens of interfaces. In addition it requires us to use a different view model and database model, at which point we'll need to configure our mapping tools, our IoC container, and I could go on for hours simply talking about the technical requirements our "best practices" would impose upon me.

I'm here to tell you it's all rubbish, and that you're much better simply pasting your SQL straight into your endpoint

If I was to use industry "best practices" to create a simple HTTP controller endpoint returning data from my database, I would need to spend maybe 5 to 50 hours. I'd end up producing maybe 500 lines of code, and I'd have to touch 5 to 25 different files. In this video I show you how I can accomplish the same in 20 seconds by ditching industry "best practices" and simply pasting my SQL straight into my endpoint file.

If you don't have time to watch the video, realise I'm done when I click the "Generate endpoints" button in the screenshot below.

When I've clicked the above button, I've got a new HTTP GET endpoint, requiring a filter argument, that once executed will return JSON to the client containing all records matching the specified filter. The code this produces for me is as follows:

.arguments
   filter:string

// Connecting to database.
data.connect:chinook
   database-type:sqlite

   // Parametrizing [data.select].
   add:x:./*/data.select
      get-nodes:x:@.arguments/*

   // Executing [data.select] slot with our SQL.
   data.select:@"
select distinct c.Email, c.FirstName, c.LastName, g.name
  from Customer c
    inner join Invoice i on c.CustomerId = i.CustomerId
    inner join InvoiceLine ii on i.InvoiceId = ii.InvoiceId
    inner join Track t ON ii.TrackId = t.TrackId
    inner join Genre g ON t.GenreId = g.GenreId
  where g.Name = ""Rock"" and c.Email like @filter
  order by c.Email
"
      database-type:sqlite

   // Returning a result of above invocation to caller.
   return-nodes:x:@data.select/*

25 lines of code, one file, easily understood. No OOP, no view model, no database model, no O/RM library configuration files, no IoC wiring, no AutoMapper, no service implementations, no interfaces, nothing! One simple file containing all the code required to perform the task, and all my code can be read without scrolling.

Not only does this make me 10,000 times faster on delivering the feature in the first place, but it also makes my code 10,000 times easier to maintain. If you need to change something in your "best practices" solution, you'd typically have to touch 5 to 10 different files. If I'm to add another field or join towards another table, I simply modify the SQL I'm executing. And since I'm saving straight to production, I don't need to wait for any pipelines, CI/CD garbage, or have two developers "accept" my pull request.

Drop your Unit Tests

For me unit testing becomes obsolete, because I can now test my code in SQL Studio. If the SQL succeeds executing in SQL studio, I can simply copy and paste it into the Endpoint Generator. This results in my solution being 1,000 times "safer" than your best practices solution, which at best would require a mock object, a pipeline, dozens of unit tests, and 100+ complicating constructs making the solution basically impossible to understand for a single developer.

Not only is it 10,000 times faster, it's also 10,000 times more secure, high quality, and contains less bugs

Conclusion

I'm sure our industry's so called "best practices" have been created with good intentions. I'm sure one developer at one point in time had a good reason to use OOP, SOLID, or DDD. However, just because it worked one time, 25 years ago, for one specific solution, doesn't mean it is a "general rule" we should all follow for all of our solutions.

For 99.9999999999% of your problems, my solution being based upon the principles of KISS is simply superior.

When your peers starts throwing acronyms around, instead of perceiving it as a sign of knowledge, try instead to realise it's a sign of uncertainty. Understand it's an insecure mind, trying to hide itself behind layers upon layers of junk words, who's intentions are to create the illusion of that the software developers knows what he's talking about - When the exact opposite is in fact true.

Being able to discuss 15,000 different software development acronyms is no feat, it's a disease. A disease of the mind, and you don't need to play their game. Just pick up your potatoes, go to your desk, and paste your SQL straight into your endpoint.

There's no shame in being 10,000 times faster than your peers - And the only one who's opinion actually matters in the end (your client who pays your salary), is certainly not going to care about OOP, OOA, OOD, SOLID, DDD or CQRS - He just wants working software - And if you can deliver it better, faster, with higher quality - That's the only thing that actually matters ...

Have a Custom AI Solution

At AINIRO we specialise in delivering custom AI solutions and AI chatbots with AI agent features. If you want to talk to us about how we can help you implement your next custom AI solution, you can reach out to us below.

• Contact AINIRO