re: You Can Do it in SQL, Stop Writing Extra Code for it VIEW POST

FULL DISCUSSION
 

Putting application logic into DB scope might end up with hard to maintain and troubleshoot code, though I can agree sometimes knowledge of SQL help you getting things done, but I'd leave this approach for custom reports based on SQL queries rather than for web application or something when you are free to process data in controller level.

 

Not only that, but imagine wanting to move to a different database that doesn't have this features. Anyway, is sql a programming language?

 

I have never seen a company moving to another database. This is something that just does not happen.

From my mobile experience there is Parse who burned its developers 2 years ago. My personal app is still not working because i could not replicate in Firebase the query based on location coordinates. Since then i learned to use clean code and the database is switchable whenever i wish. I do have one right now where an iOS, Mac and command line app use the same codebase but different databases.
Of course, if you have something highly important no need to worry about this and better to implement how is more efficient.

 

SQL is not a programming language. It's a query language (Structured Query Language). You use it to either query a database to find data that fits a set of criteria, or manipulate that data (adding more data, deleting data, or modifying data). SQL is nice because different databases typically respond to basic SQL queries in the same way. More advanced functionality may have differences in syntax between databases, though.

For Spring Boot projects you would typically run your unit tests against an in memory database so it's important that the business logic is not in the database and that the code is database agnostic. The Java 8 API, for example, provides excellent data processing features like SUM, grouping etc. Putting business logic in the database is a retrograde step and should be avoided if possible.

 

Company I work for has exactly this issue. Literally all the logic (sometimes even presentational logic) is done in the database.

This is nightmare to maintain and now really difficult to refactor.

Also it's difficult to test SQL code due to lack of tools.

 

Probably as most of you agree it is a bad practice due to:

  • cannot track code in DVCS like git
  • cannot test DB code
  • a lot harder to scale

It is better to convenience the devs and mgmt to slowly move it out if possible.

First one is not true.

We successfully version database code using Git, have working pull requests, continuous integration and delivery.

Second is not true as well. For instance, T-SQL code can be tested using tSQLt framework. Yet again. This is more difficult and not a widely adopted practice, but it's possible and companies do it.

But in any case. Even though things are possible and can be done in database, it doesn't mean it should be.

Database testing is a nightmare I hope to never repeat. Just because a thing can be done do not mean that it should be done.

It's not true in anyway that DB code is hard to test. As long as the person is aware of DB based code units it's just the same.

 

Thanks for the comment. I am not advocating putting any logic in DB. The code still stays in your git repo. I am just trying to demonstrate the power of SQL for simple tasks can be a lifesaver.

code of conduct - report abuse