Create your next Web API in SQL
A lot of developers are skilled with SQL, but don't know C#. With Magic SQL is all you need to create a Web API, because it automates the tedious and repetitive parts
Join the DZone community and get the full member experience.Join For Free
My CTO is a kick ass SQL guy, but he doesn't know much C#, so wrapping his SQL into a Web API is difficult for him. Every now and then he throws me some SQL statement or Stored Procedure in our Teams chat, and asks me to wrap it into an HTTP endpoint for him, because one of my colleagues needs it for our frontend parts. The process is almost the exact same every single time.
- Wrap the SQL into an SqlQuery invocation using Entity Framework
- Create some sort of view model matching the return from the Store Procedure
- Choose which roles are allowed to access the endpoint
- Parametrise the SQL invocation
- Execute the SQL and return the content as my view model
Realising I could completely automate this process using Magic, providing high level UX components doing the entirety of the job, resulted in the following click button solution.
At which point both my CTO and the other SQL gurus at my job can solve their own problems in seconds, without even needing help from me - Empowering my colleagues, while eliminating manual work.
Backend developers are obsolete
This might sound funny, especially coming from a guy who's spent most of his life being a backend developer - But regardless of what your job is, your primary concern should be how to make yourself obsolete. This is true for CEOs, CTOs, cab drivers, and software developers. This is especially true for repetitive tasks, where the "copy paste factor" is high. Besides, I would assume that no sane developer actually wants to spend his day manually creating view models, wrapping stored procedures, or hand written SQL, and return the result to the client anyway. And if your existing developers disagrees with this statement, you should probably find some new guys and gals to fill their roles.
With Magic though, I can just copy the SQL sent to me by my CTO over Teams, create a new HTTP endpoint with Magic, and VOILA - It's up in DEV before I can even spell "git push". Don't believe me? The next screenshot shows the entire process!
In case you're wondering ...
- Authorisation - CHECK!
- All major HTTP verbs - CHECK!
- Parametrising your SQL - CHECK!
- 100% control over URLs - CHECK!
You can find the SQL I'm wrapping in the above video below.
Basically, 95% of the SQLs my CTO sends me, can now be wrapped inside an endpoint dynamically, within 5 seconds - And I get to enjoy an additional coffee break, while my CTO becomes empowered, arguably doing my job - CHECK! ;)
I'm not sure I would recommend it, but at the very least in theory, this allows you to create an entire Web application and its backend, using Microsoft SQL Server, and exclusively wrapping Stored Procedures into Magic, using your iPhone's browser as your main IDE - Which I think is kind of cool. I might not be there completely yet, but with the speed I am improving Magic by now, I'll be able to have cats literally create backend software systems 6 months down the road. No wonder she's laughing :)
Simply put because 95% of the work is done by simply clicking a button. Why would I want to do such a thing you may ask me? Because I can is my answer. And before you start stating this is bad design, you need a repository pattern to access the database, etc, etc, etc - This is a repository pattern implementation. Besides, adding 15 layers of strongly typed abstractions to invoke a Stored Procedure, resulting in that as the SP is changed, your code needs to be maintained is not best practices regardless of what "Patterns of Enterprise Application Development" and Martin Fowler tries to convince you about. Simply put because at each maintenance point, you've got 15 more files to edit, and 15 more moving parts that can go wrong.
KISS - Keep It Stupid Simple
Opinions expressed by DZone contributors are their own.