DEV Community

Cover image for You Can Do it in SQL, Stop Writing Extra Code for it

You Can Do it in SQL, Stop Writing Extra Code for it

Geshan Manandhar on December 14, 2018

“SQL, Lisp, and Haskell are the only programming languages that I’ve seen where one spends more time thinking than typing.“ — Philip Greenspun Eve...
Collapse
 
melezhik profile image
Alexey Melezhik • Edited

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.

Collapse
 
buinauskas profile image
Evaldas Buinauskas • Edited

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.

Collapse
 
geshan profile image
Geshan Manandhar

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.

Thread Thread
 
buinauskas profile image
Evaldas Buinauskas • Edited

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.

Thread Thread
 
sdedalus profile image
David

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.

Thread Thread
 
divakarraj profile image
Divakar Rajashekaran

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.

Thread Thread
 
geshan profile image
Geshan Manandhar

Maybe you are right!

Collapse
 
geshan profile image
Geshan Manandhar

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.

Collapse
 
ralcr profile image
Cristian Baluta

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

Collapse
 
katafrakt profile image
Paweł Świątkowski

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

Thread Thread
 
jdelgit profile image
jdelgit
Thread Thread
 
ralcr profile image
Cristian Baluta

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.

Collapse
 
darkain profile image
Vincent Milum Jr

Some things to keep in mind.

Depending on the SQL logic, this could add additional CPU resource pressure on the database server, a service that is hard to scale. Web servers are significantly easier to scale to handle the processing workload.

At least one of the examples looks like it may break the SQL query optimizer in MySQL or MariaDB, causes a full table scan, significantly harming performance. If tables are small, this is no issue, but even for small business workloads, I'm generally dealing with tables on the scale of 1mil to 100mil rows.

Others have noted that this could make parts of the database harder to maintain. Personally, I use some of these methods described above, but they're handled through a SQL query generator, the logic still entirely exists in the application layer, it simply generates and then passes the required SQL query string to the database to handle the operations.

I simply make these notes as someone who has crafted web sites with data processing times in the sub-100ms range, with the fastest now averaging 6ms.

Collapse
 
connor_mc_d profile image
Connor McDonald 🍸☕️

If the concern is scalability of the database, as counter intuitive as it might sound, doing more work outside the database can make the database work harder then if that work was done inside the database.

This is not just an idle rant - here's a full exploration of that precise topic

youtube.com/watch?v=8jiJDflpw4Y

Collapse
 
tadman profile image
Scott Tadman

You'll always want to keep in mind how your application is deployed and where you have the most resources.

One application I worked on was deployed on a provider with a pricing model where you paid for web server instances but the database itself was bundled in as part of the package. This lead to a design that off-loaded as much as possible onto the database as it was basically "free" compute. The database instance was far, far faster than any of the individual web servers.

In modern configurations you're going to have a lot of web instances and a few databases that you want to keep lightly loaded so they're responsive. This is especially true if leaning hard on your database means buying more ultra-expensive database server licenses.

Collapse
 
geshan profile image
Geshan Manandhar

It is a balance of trade-offs as it is usually in distributed systems. Choosing the right trade-off is one of the keys to success in our field.

Collapse
 
geshan profile image
Geshan Manandhar

I like your point of view, databases are not easily scalable that's where managed services with load balancers come into play I suppose. About the full table scan, this is an example and setting indexes correctly would help tackle the problem. One can always run an EXPLAIN and plan next steps.

About maintenance, I believe none of the code should be in the DB itself it should be in git managed repos. I am also not promoting writing triggers or unnecessary views. 6ms is impressive, at that point there should be a good amount of caching involved. Thanks!

Collapse
 
darkain profile image
Vincent Milum Jr

6ms is with zero caching, that's all live database queries and html template processing.

The query in question about the SQL optimizer is the one with the HAVING statement. I'd have to check, but limiting the number of rows in a HAVING statement based on an IF statement in the column selector I believe requires a full table scan. The GROUP BY statements should all be good though, especially considering the advances in optimizing these in the past two years.

Thread Thread
 
geshan profile image
Geshan Manandhar

Sounds interesting for 6 ms.

For the having query it was just an elaborated example, if it was a real DB query I would have surely run an explain to see if that is the way to go. Thanks!

Collapse
 
diysportscar profile image
Dominic Peterson

I find the unwillingness to properly leverage the power of the database very confusing. It's like having a firetruck with big, powerful hoses that you drive up to the fire and then use those hoses to fill little individual buckets that you carry over to throw on the fire one at a time :(

I have constant conversations with (mostly) younger devs who seem unwilling to learn some database design theory and some SQL that could save them huge amounts of messing about with ORMs.

Collapse
 
geshan profile image
Geshan Manandhar

Amazing analogy!

Collapse
 
rhymes profile image
rhymes

Well said :-)

Window functions are my favorite "hidden" super power. The intro of the linked article says it all:

There was SQL before window functions and SQL after window functions: that’s how powerful this tool is.

Collapse
 
geshan profile image
Geshan Manandhar

Not sure of this.

Collapse
 
rhymes profile image
rhymes

Ah ah I think it's just a hyperbole, but they really are useful

Thread Thread
 
geshan profile image
Geshan Manandhar

I hope they are! :)

Thread Thread
 
rhymes profile image
rhymes

They are, you can do a long way to calculate base statistics or setup things like leaderboards where you give rows scores based on some columns

Collapse
 
qm3ster profile image
Mihail Malo

Oh my lord, pagination.
I am so profoundly shocked by the concept of pagination I'm litterally SHYDDING MAISELF right now.

Collapse
 
rhymes profile image
rhymes

Not exactly Mihail, pagination is what you do with LIMIT and OFFSET, and has been there since forever.

Window functions are useful to correlate different rows and building charts or leaderboard, giving them weights, using them as a state machine (since it's a rolling window you can know which row is "previous") and other stuff.

You can obviously do that all of that stuff using a programming language (and sometimes you should) but if you don't need to, why not use them ;) ?

Thread Thread
 
qm3ster profile image
Mihail Malo • Edited

No, I get it, I see how it is both useful and how it might be obscure even to those that write some SQL.
I was just making fun of the hyped up description.

I'm also in the camp that says SQL is over used. The two popular and malignant ways it's overused is when one DB is shared between many if not all microservices (regardless of how well written the stored procedures are), and ORMs when used mixed with procedural app code and not as query builders.

SQL can be an acceptable structured datastore (with logic) for a given service, alone or together with other sources of truth. But not every service would benefit from one of those.

But most importantly, it's a poor object store and an even worse inter-service communication channel. And I've seen it used as both a lot.

Thread Thread
 
rhymes profile image
rhymes • Edited

No, I get it, I see how it is both useful and how it might be obscure even to those that write some SQL.

It took me a while to get it but I was actually writing a basic leaderboard and all of the "in language" solutions I tried were dog slow. Window functions instead use indexes :)

I'm also in the camp that says SQL is over used.

I don't know, it really depends on too many factors.

The two popular and malignant ways it's overused is when one DB is shared between many if not all microservices (regardless of how well written the stored procedures are),

Eh eh yeah, say no to stored procedures 99.99% of the time. The only placed I worked at where we had those was at a financial company and they used procedures written in Python that did math calculations inside the DB. A mess to maintain but insanely fast.

Say no to microservices that synchronize through a shared DB. That's a distributed monolith :D

Thread Thread
 
qm3ster profile image
Mihail Malo

I meant stored procedures in SQL itself. CREATE PROCEDURE.

Thread Thread
 
rhymes profile image
rhymes

Yeah, let's avoid stored procedures if we can. Agreed

Collapse
 
samuyi profile image
Samuyi

Are we going to state the obvious elephant in the room? Going through the comments it's obvious that many developers do not know about SQL or databases in general. Its why they are getting in their feelings. The problem isn't the SQL code, it's probably because you don't understand the underlying data structure or know SQL well enough.

Putting application logic in SQL is good practice in my opinion. Maybe not all logic, but at least a good chunk. This makes your application faster and scale well. Adding more application servers would not make it faster or scale well for that matter. Understanding the underlying database and data structure would go a long way in scaling that application.

As for testing it's possible to test SQL code. I haven't seen people do autonomous testing of SQL code, but it's possible.

Collapse
 
connor_mc_d profile image
Connor McDonald 🍸☕️

Agreed. Perhaps the biggest obstacle to any technology (SQL included) is rarely the technology itself, but people's comfort factor with it.

I'm just as guilty of that myself. I know that the best applications use database access (SQL) intelligently, they use middle tier functionality intelligently, and client tier functionality intelligently. But most apps have a strong bias in just one of these areas, simply because of the biases of the developer/development team involved.

Collapse
 
geshan profile image
Geshan Manandhar

Thanks for your viewpoint!

Collapse
 
divakarraj profile image
Divakar Rajashekaran

Well articulated and I share the same view.

Collapse
 
akashkava profile image
Akash Kava

Primary reason not to do SQL was SQL Injection and wrong way devs write SQL. That's why we shifted out from SQL to ORM, but for high performance queries, we ended up doing that in SQL. This creates testing nightmare.

Collapse
 
pavonz profile image
Andrea Pavoni

You can use database wrappers rather than full ORMs. They let you to write queries in your programming language and then “translate it”” into the native db syntax. Elixir Ecto is an excellent example of this technique (or at least, the best I know of). I prefer this to, for example, Ruby’s ActiveRecord/ActiveModel.

My 2 cents

Collapse
 
akashkava profile image
Akash Kava

You can call database wrapper or full ORMs, but it is basically ORM of different sort when you don't write sql directly.

Thread Thread
 
pavonz profile image
Andrea Pavoni

I'm sorry, but I disagree. An ORM is something different from a database wrapper.

An ORM is mostly peculiar of OOP languages, because it represents your database (tables, columns, etc...) in form of objects and they are very tighted between eah other. If your library also generates queries for you, that is a plus.

A database wrapper is simpler and more generic. I used a bad explanation in my last comment but the gist is that I use it to communicate with a db in a different way than an ORM. For example, I could write my query manually, then the library wraps/sanitizes it before talking with the db. How I map the result to my language data structure is a different problem, just like how it might generate a query for me.

Collapse
 
connor_mc_d profile image
Connor McDonald 🍸☕️

"Primary reason not to do SQL was SQL Injection..."

That strikes me as like saying "We do not use the Math library, because devs keep using division instead of multiplication"

All languages are a tool for getting stuff done - use a tool intelligently and you get intelligent results. Use a tool poorly and you get a poor result. I don't see how SQL is any different to any other tool in that regard ?

Collapse
 
akashkava profile image
Akash Kava

For one man army with knowledge of SQL Injection, your argument is solid, but if you have 100s of developers, avoiding SQL Injection without ORM is impossible unless you have time to review every query ever written.

Thread Thread
 
connor_mc_d profile image
Connor McDonald 🍸☕️

I can only speak from my area of expertise (Oracle) but in that instance, a single query will tell me where SQL injection risk points are.

connor-mcdonald.com/2016/05/30/sql...

Thread Thread
 
chidioguejiofor profile image
Chidiebere Ogujeiofor

Akash I really think that a basic crash course(or even a youtube video link) to 100s of developers on how to avoid SQL injections (eg by using say Prepared Statements ) is better than using a less optimized solution.

Also, it helps the developers become better at their craft and you would be helping their growth in their careers.

Thread Thread
 
akashkava profile image
Akash Kava

Try it with 100 developers and let me know if all of them follow it correctly !!

Thread Thread
 
chidioguejiofor profile image
Chidiebere Ogujeiofor

Have you ever had a team of 100 backend engineers working on an API without each of them having to be in sub-teams with each team having a team lead before?

Thread Thread
 
chidioguejiofor profile image
Chidiebere Ogujeiofor

My argument is simple. It is the role of the team lead or more senior guys on a team to ensure that SQL injection the most basic error while writing SQL does not happen. If they can't ensure that then the app would be buggy anyways

Thread Thread
 
qm3ster profile image
Mihail Malo

Serious question: Why are there 100 unique developers writing queries directly against the one same database, whether via SQL plaintext, query builders or even ORMs?
I see only two scenarios:

  1. They are all reimplementing the same tiny API over and over again. This API should be owned by one team and provided as a library, network endpoint, or both.
  2. The database has grown too complex and has long become the bottleneck for development by such a large number of people. It should be split so that teams own their schemas (without necessarily having access to production data in these schemas)
Collapse
 
qm3ster profile image
Mihail Malo

SQL injection is caused by underuse of SQL, not overuse of SQL.
If the logic was parameterized stored procedures in the DB, not queries string-built on the application server, there wouldn't be a vulnerability.

In most industries, it's ridiculous to allow arbitrary query access to production database.

Collapse
 
geshan profile image
Geshan Manandhar

If you put SQL in the db it might be hard to test but if it's in code it shouldn't be hard to test. Thanks!

Collapse
 
akashkava profile image
Akash Kava

Mocking is hard, creating unique instance of DB, seeding it, running 1000s of tests against it in parallel, all of that is hard, I never said it is impossible. It becomes terribly slow. I have created the tool to do it and we are doing it, but it is too slow compared to ORM's with in memory mocking.

Thread Thread
 
geshan profile image
Geshan Manandhar

Thinking of tesing with databae is hard, mocking it or at max using a in memory db is the better approach IMHO.

Collapse
 
jbristow profile image
Jon Bristow

This is all good, but it leaves out a major point: Pulling directly from a database (or a filesystem) and exposing that to the consumer is not really scalable.

Sure, it works fine in a single application with its own database, but imagine 50 different webpages and services working off that same data. If one service makes a change to the database, then everyone needs to make changes to their code.

But, if everyone depends on a data accessor library (or service, or whatever), only the accessor needs to change! Everyone else's contracts will remain the same.

True, you're probably not going to swap out the entire underlying store all at once, (It happens, though... Especially early on in the lifecycle of the datastore.)

That being said, everything above is still applicable! Databases often are highly optimized for pretty much everything you pointed out (though sometimes full-string operations slow things down a LOT... I'm looking at you, Bob. I saw you put that LIKE statement on a multi-million row join. I warned you. I warned you about the stairs.)

I'm looking at a bunch of BigQuery stuff right now at my current job, and I keep seeing these queries that over-use with(). It's a simple join, Karen! It's not even a NOT IN or fancy grouping! But BQ is usually fast enough that this doesn't even matter...

Collapse
 
geshan profile image
Geshan Manandhar

Appreciate your views. As the example mentions it is a "microservice" so it is assumed that the DB is accessed by only one application. If you have 50 applications accessing one DB there is a bigger problem to solve IMHO. Not using SQL well is a different problem like you pointed to Bob for the like query. That could even run kinda OK if the indexes are placed logically. Thanks!

Collapse
 
jessekphillips profile image
Jesse Phillips

I would think that a database would be the one common thing when doing Mico services. Do you really scatter the data around like you do service endpoints. (my experience using and mocking micro services may not be representative.

Thread Thread
 
geshan profile image
Geshan Manandhar

Better to keep data central.

Thread Thread
 
qm3ster profile image
Mihail Malo

Keeping data central causes all the problems @jbristow mentioned.
And a "data accessor library" isn't a solution, it's just a bottleneck, in terms of flexibility if not even performance for your whole system.

Microservices should own their data, and communicate through APIs they expose, ideally through commands and events.

Collapse
 
ericsgagnon profile image
ericsgagnon

I've moved from ORM's to SQL guery generators. It gives a lot more freedom than most ORM's, let's you leverage the power of the DBMS, and promotes maintainability. I find it's a reasonable compromise for environments where the data isn't entirely stable and no REST api's are supported.

Collapse
 
geshan profile image
Geshan Manandhar

Good path taken, kudos!

Collapse
 
imthedeveloper profile image
ImTheDeveloper • Edited

There appears to be some angst in the comments which I feel are formed from some sort of professional bias. However I see the sentiment of your publication and I have to agree on a number of points. There appears to be a slight ignorance as to the benefit of using SQL to reduce complexities in application code but I believe the power of some languages has proliferated the tendency to pull all the data and manipulate it afterwards. Whilst this gives great flexibility it also brings with it real inefficient setups. I recently investigated some slow load speeds which turned out to be a developer pulling every column in on every query for simple search statements in a very wide table. Whilst the code looked really neat and the orm gave very shorthand code it feels as though there is little to no thinking about the database optimisations and even query optimisations that can be made. It's kind of treating an orm as just this magical gateway to fire hose data into the application.

Collapse
 
geshan profile image
Geshan Manandhar

Thanks for the support. ORMs need to be used wisely.

Collapse
 
danroc profile image
Daniel da Rocha

I have recently started putting logic in the DB, mostly when turning to Postgraphile which "turns" my DB into a GraphQL server.
Despite the slow start, I am starting to really feel the potential, especially when creating functions for inserts and special table joins.
Also, pgTAP makes testing quite simple.

Collapse
 
thiagoa profile image
Thiago Araújo Silva • Edited

GROUP_CONCAT is cool, but I stopped using it in MySQL 5.6. Unfortunately, it has a maximum_length setting which by default limits the return value to 1024 characters. It's the kind of bug that can go unnoticed for months, or only be evident with table growth. I wish it didn't have a limit (or that you could set no limit on it) and did not require fiddling with the DB settings. So beware! :)

I'm not sure about newer versions though.

Collapse
 
geshan profile image
Geshan Manandhar

Good warning sign to take of.

Collapse
 
redfred7 profile image
Fred Heath • Edited

To everyone here claiming that SQL is hard to test and troubleshoot: All SQL statements return a result-set. By using SQL Views, Functions and Stored Procedures you can test SQL code as you would test any other API call your code makes. Seriously, it's not an issue.

Collapse
 
connor_mc_d profile image
Connor McDonald 🍸☕️

Agreed. A SQL statement is just code. It has an intended purpose and an expected result. That's pretty much what unit testing is designed to work with, no?

Collapse
 
dandy profile image
dandy

True, SQL is indeed fast and powerful in data aggregation/manipulation.

We can also pre-compute these data aggregations/manipulations if they are queried frequently by users. I think running these aggregations queries, on the fly, on table with millions of records, by multiple users, maybe a slight overhead on CPU/RAM? No/Yes? Don't know.

Collapse
 
qm3ster profile image
Mihail Malo

True, event-updated projections are a much easier to conceptualize and scale than DB views.

Collapse
 
geshan profile image
Geshan Manandhar

That is where you need to profile the queries, run some explains, check the resource usage and decide where is it better to run it on the DB or code. Thanks!

Collapse
 
fjo_costa profile image
Fernando Costa

I'm trying hard, but I don't understand the concept of this post. From the purpose of databases to infrastructure management, I don't think I'd ever apply this method, even if it could cost me 10x in performance(in which case I would simply scale).

Use the superpowers of SQL to make your life easier as a software engineer.

Nope.

Collapse
 
rhymes profile image
rhymes

Hi @fjo_costa ,

it's the not the first time I hear this argument, and I'm not picking on you but I would honestly like to understand why you're against the idea of achieving 10x speed by using a well tested query or two.

I know it's harder to maintain if the specs change, but you can still swap the query for something else in the future, especially because ORMs tend to catch up to SQL features at some point.

Also, lots of languages have safe SQL generators, which mean you don't have to actually write the entire query, you can use tools like Arel.

I've built a query engine for composable conditions to select targets to send push notifications to once on it and I probably wrote two or three of pure SQL in total. I was still using the ORM, just augmented it through relational algebra, PostgreSQL json and basic geo support. It would have been immensely slower to do "in the app" (especially with hundres of thousands of devices). The engine was super fast and I had no caching.

I assure you I didn't anything special with tests, just tested that by selecting a condition, I had this or that device back, all inside the ORM.

Collapse
 
geshan profile image
Geshan Manandhar
  • its test what you write not the library you are using and SQL has been around longer than many languages I know. SQL is one of the most battle-tested things. Thanks for your views @rhymes !
Collapse
 
geshan profile image
Geshan Manandhar

I can agree to disagree :)

Collapse
 
edoardoc profile image
Edoardo Ceccarelli

good post, probably the scenario could also be bigger: I was using everything but the group_concat which looks really useful, here is a so discussion about a postgres equivalent

stackoverflow.com/a/2561297/436085

Collapse
 
samuraiseoul profile image
Sophie The Lionhart

I'm always torn on how to actually implement this advice though. Sure its faster and in some ways easier to use the built in SQL stuff, especially if you're trying to reduce down to a value like with sum, but how can you deal with that in an ORM-esque way so you still have a type safe representation of that row for manipulating down the way? I can see the desire to have some custom calculated row in the return from sql but then what happens to the representation when you pass it around? I'm not sure how to balance these two things I guess, the pure SQL and the classed OOP type safe current approach.

Collapse
 
geshan profile image
Geshan Manandhar

This is the million dollar question :) Yes as soon as you use an ORM your ability to run raw SQL queries becomes difficult. This depends on you and your team to set a guideline to come around this problem. For that, it is better to talk milliseconds and performance choosing a solution that is fast in execution and relatively easier to write and maintain would be my suggestion. Thanks!

Collapse
 
seimic profile image
seimic

one way is to compose the orm representation of the data from the raw table and lazy loaded views mapped readonly. In case of JPA this means @SecondaryTable pointing to a View and columns with insertable=false, updatable=false. by this they never appear in any insert or update statement and the additional aggregation logic is cleanly separated from the raw data model. works fine.

Collapse
 
rhymes profile image
rhymes

The balance is isolation.

You isolate the code that uses the custom SQL, you document it and test it well and like everything else, you try to have all the code that needs it use that single function that returns the DB value.

Your app can still use the ORM for 99%, but that doesn't mean we shouldn't take advantage of the DB if after measuring we conclude that the tradeoff is worth it.

Collapse
 
ben profile image
Ben Halpern

I needed this

Collapse
 
geshan profile image
Geshan Manandhar

Thanks!

Collapse
 
itaditya profile image
Aditya Agarwal

I have used SQL database in Node.js with Knex. Knex allows us to write raw SQL queries. So we can have version control over our DB queries.

From the comments I understand testing database is hard but I don't think we need to do that. Why directly test the database. Can't we just test the APIs which are using the database.

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.

Collapse
 
geshan profile image
Geshan Manandhar

I am not telling to put the SQL in the db, it will be in code tracked with git and potentially unit tested. If that does not aid maintainability I am not super sure what will :).

I agree with your 2nd point generally. Thanks!

Collapse
 
davidchandra95 profile image
David Andrian Chandra

How about the performance?

Collapse
 
geshan profile image
Geshan Manandhar

Doing it in SQL should perform faster. Thanks!

Collapse
 
davidchandra95 profile image
David Andrian Chandra

Welp. My company even prefer not to do an age calculation from database for the performance consideration :/

Thread Thread
 
geshan profile image
Geshan Manandhar

Probably it will be faster, profile it and show the numbers. Thanks!

Collapse
 
djviolin profile image
István Lantos • Edited

So... Because the author of this article "recommended" on facebook to have conversation here, instead of there, I would gladly accept this and copy here my two facebook comments, for enjoyment. Probably I'm not worthy to read, because I'm never was in the top 7 badge here on dev.to, but here we go:


Just because you can, it doesn't mean you have to. Putting way too much logic in SQL means instead of spawning a new API or client-side node as a worker in your cluster with one click, now you have to tamper with the replication of your database cluster (which systems usually tide to master-slave replication, like Postgres). You will also spend sh*tload of money to give enough resources to those DBs, instead of putting those simple math functions into client side React and call it a day. Of course, you will send more data from the API to client, but you have to decide which is more painful for your pocket (spoiler alert: probably a fat and slow DB). Client-side code also can be fasten up with CDNs, which is worth pennies nowadays. Choose which solution you prefer more...


Geshan Manandhar on fb: "Also, with all due respect if you think you are good enough try writing something of this sort, get similar views, reactions and comments. Get a top 7 badge on dev to. Then I would listen to and reply to your comments. Thanks and Happy New Year!"

WTF, maaan... Don't be so triggered. This post picked by the dev.to team the second time on facebook. We comment here, beacuse your article is relevant, right now, right here, not where and whom you want.

The examples in your article (like SUM, CONCAT and DISTINCT keyword) is nothing new or putting more logic into SQL: is just the standard way how a database engineer design their queries and it is part of their everyday toolset. But I would like to add one exception: string manipulation. PLEASE-JUST-DON'T! In fact, if case sensitivity is not important (most of the time, it doesn't) try to store them insensitive, like citext type in Postgres. So basic thing, and yet, in a governmental software used by every city and village in my country, released their product with case sensitive tables, causing some people to appear many times, in every single city and village (around 2000+), because, you know, humans working in offices. Doing any string manipulation or not ignoring case sensitivity can lead to serious problems and usually the DB admin is the guy, who need to fix the colossal f*ck up with hard work later on, never the backend developer. Don't shoot yourself on the leg. Also, using string manipulation to concat strings with "-", is just tossing money out of the window. Your visitors CPU can make it for free, including converting to uppercase the first letter of every words, etc. Putting this logic into the DB, is just...

Collapse
 
geshan profile image
Geshan Manandhar

Some of the things you are pointing out are correct. Like any other tool, SQL should be used wisely and after profiling performance. Thanks!

 
jppage profile image
jppage

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.

Collapse
 
anikethsdeshpande profile image
Aniketh Deshpande

Advanced concepts like SQL window functions make it very easy to fetch data, summarise group results, in a quite unique way

Collapse
 
keithchristian profile image
Keith Christian • Edited

Nice article but you should make it clear at the beginning that you are specifically discussing MySQL functions, the title makes it appear as though all these functions are in generic SQL.

Collapse
 
geshan profile image
Geshan Manandhar

Thanks for the comment. It's more about doing stuff in SQL than the flavor of SQL.

Collapse
 
keithchristian profile image
Keith Christian

Geshan, Good points you made, my comment was primarily to help beginners new to SQL. We all can relate.

SQLite, PostgreSQL, etc., don't use the same syntax in some examples.

Thanks for listening.

Collapse
 
itaditya profile image
Aditya Agarwal

I wrote something similar but for MongoDB. codeburst.io/things-i-wish-i-new-b...

Collapse
 
tommyxlos profile image
Thomas Los

Refreshing to see in an age were ORM's and SQL-handicaped dev's are the rule and no longer the exception.

Collapse
 
geshan profile image
Geshan Manandhar

You can refresh as many times as you want, but I guess nothing much is going to change :)

Collapse
 
geshan profile image
Geshan Manandhar

BTW this post has caught some fire on hacker news too - news.ycombinator.com/item?id=18679957 :)

Collapse
 
ktravelet profile image
Kyle travelet

Thank you! My most recent project I implemented a materialized view that has some pretty nasty SQL in it but makes the data accessing so much easier for everyone.

Collapse
 
geshan profile image
Geshan Manandhar

Generally, I am not a fan of views but yes they have their place too. Thanks!

Collapse
 
geshan profile image
Geshan Manandhar

Thanks for the comment, because it can be done != it should always be done. As commented earlier always weigh your trade-offs.