Jon's Programming Blog

Database Programming Yin Yang

There are two different ways people like to write their SQL scripts when calling the database. One way is to write the SQL statements in their application code. Sometimes they will write an ORM to make this process similar to their language constructs. Sometimes they will write the SQL code directly in procedures.

Pros and Cons of Developing in the Application

Pros and Cons of Developing in the Database


Depending on the size of your organization it might be difficult to actually get code in your database. Which seems odd to me, they will let you write SQL in your application but not let you easily add a procedure to your database? Maybe because they aren’t using tSQLt to test their stored procedures? Not sure. I think many times when it is difficult to get code on your database it might be equally difficult to release the application, so both would have friction.

So, do you keep your code in the application or the database? It probably depends. You will get some coupling between the application and the stored procedures though; returning JSON can definitely help alleviate some of that coupling though. If your application only sends back JSON to the user then I think this method might work for you; if you only have simple queries it might not be worthwhile but I’ve always had to write more intricate queries, so I haven’t had that luxury. In T-SQL you can also send back errors over 50,000 and use those as HTTP Status Codes. I’ve found name spacing the SQL code by application with schemas makes it easier to know which application the stored procedures rely on.

If you use an ORM you don’t need to learn yet another language. I would think that SQL code is so important to development that it should probably be the first language that is learned rather than the second.

If you were to really go crazy you could start defining your endpoints in your database too and just do everything in the database. Almost like PostgRest and many others do. Of course, if you go to this extreme you would need your database to be able to scale horizontally assuming it would need to eventually scale in the first place. Many databases don’t really need to scale since they might be an internal app for the company. Most applications don’t become huge, at least the ones I’ve worked on.

I’m sure there might be other pros and cons. Let me know what other ones you think of!


Problems with DAO


2017-10-15 - Added some more pros to developing database first.
2017-11-20 - From Steve Smith’s interview on .NET Rocks! Starts around 50 minutes 11 second mark.

Desert Code Camp 2017 An Introduction to Functional Programming in JavaScript