loading...
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 profile image Geshan Manandhar Originally published at geshan.com.np ・5 min read

“SQL, Lisp, and Haskell are the only programming languages that I’ve seen where one spends more time thinking than typing.“ — Philip Greenspun

Even with thinking more than typing SQL (Structured Query Language) we software engineers use it as a way to pull data only.

We usually don’t leverage SQL’s power of data manipulation and do the needed changes in code.

This is quite prevalent in software engineers who work in web applications. Another thing we miss is, if we do the manipulation in SQL directly the pulled data will be the same format for any programming language. This post aims to enlighten you about the powers of SQL you might know but generally don’t use.

Tea Lights image from [Pixabay](https://pixabay.com/en/tea-lights-candles-light-prayer-2223898/)Tea Lights image from Pixabay

TLDR;

Use SQL to do math like sum, average etc. Utilize it for grouping one to many relational values like getting categories of product. Leverage SQL for string manipulation like using CONCAT_WS for concating first name and last name. Exploit SQL to sort by a custom priority formula. Examples below…

The Example

It will be easier to explain the superpowers of SQL putting it in action on an example. Below is a basic schema with 2 tables in MYSQL for a refunds microservice:

There are 2 refunds and 7 related payments as example data.

Some assumptions

For the refunds microservice example schema and applications following assumptions are made:

  1. Refunds microservice and data structure store the fk_item (the id of the ordered/delivered item), but it is not a hard foreign key.

  2. Item can be refunded in either cash or credit for the amount paid for the same.

  3. Items can be refunded many times as long as remaining balance can cover requested refund amount for each cash and credit. For example, item was paid 50 in cash and 50 in credit. 2 refunds of 20 cash and 20 credit can be done. So after these transactions balance will be 10 cash and 10 credit for that item (50–20–20).

  4. Each refund can have multiple items payment. Each payment can be of type either cash or credit.

  5. All amounts are stored in cents so they are integers.

Now let’s use some SQL powers. You can find the example with related queries running on SQL Fiddle.

Do the math in SQL

As software engineers, let’s say if we need to find the total cash and credit amount refunded for an item what would we do? We would run something like:

SELECT fk_item, fk_refund, amount, is_cash 
FROM payment WHERE fk_item=2001;

With current data, it will give 3 rows like below:

With these 3 rows, we would loop over them. If it is cash accumulate it to cashBalance variable, if not sum it up to creditBalace variable. Rather than that it would be a lot easier (probably faster) to do in SQL like:

SELECT fk_item, SUM(amount) AS total_paid, IF(is_cash = 1, 'cash', 'credit') as type 
FROM payment WHERE fk_item = 2001 GROUP BY fk_item, is_cash;

Resulting in:

The result is easy now if you need the total refund for the item just change the GROUP BY to be on fk_item and it’s done. For 2 and 3 records it won’t feel significant. If there were say 20 refunds for that item, the first solution with a loop is writing more code with no gain. Like sum, other SQL functions can be used too. Simple math operations like sum, multiply, average etc can be easy with SQL. This means no more loops.

Use GROUP_CONCAT to fetch related 1:m relation values

Group concat is a robust operation in SQL databases. It is instrumental when you need to get data from one to many relationship. For instance, you want to get all tags for a blog post or you want to get all categories of a product. Concerning this refunds example, one item can be refunded multiple times. So we will get all the refunds associated with the item id. To get this we will run only 1 query and get it without any loops in the code like below:

SELECT fk_item, GROUP_CONCAT(DISTINCT fk_refund) refund_ids 
FROM payment WHERE fk_item = 2001;

This results in:

Now we know that item 2001 has been refunded twice for 2 refunds. It will be easy to explode the refund Ids with , and proceed with any related operation.

String manipulation

Many string manipulation tasks like substring, concatenation, change case, and string compare can be done in SQL. With this example, I am going to show the usage of CONCAT_WS. It is concat with a separator. It can also be used to select for instance first_name and last_name with space in between.

In case of having an optional middle name COALESCE can be used with CONCAT_WS. That is something for you to explore :).

In this example, I will select refund_nr with its related reason:

SELECT CONCAT_WS("-", refund_nr, reason) AS refund_nr_with_reason 
FROM refund;

Resulting in:

If this needs to be shown on the credit note document, for example, no extra code is needed to join the values again. SQL makes it one step easier again.

Sorting with a custom formula

All software engineers know you can sort based on a column. But if you are given a custom priority formula to sort, what would you do? Probably again resort back to code and loop to sort. So lets set the priority formula rules for above example:

  1. Premium customer refunds get the highest priority (we hack it with a priority of 9999999999)

  2. Other than premium customers cash refunds get a priority of amount * 25 for credit it is amount * 20.

As per above rules it is decided that premium customers and priority above 50000 (in cents) will be processed first. Then other refunds will be processes. Let’s get the priority refunds as below:

SELECT r.refund_nr, r.reason, p.fk_item, p.amount, p.is_cash,
IF(p.premium_customer = 1, 9999999999, p.amount * (IF(is_cash = 1, 25, 20))) AS priority 
FROM refund AS r INNER JOIN payment AS p ON r.id = p.fk_refund 
HAVING priority > 50000 
ORDER BY priority DESC

The results are below:

With proper use of IF in SQL sorting by a custom priority formula is a lot easier than trying to do it with loops in code. Notice that even smaller amounts like 7.5 (750 cents) and 9.0 (900 cents) came to highest priority as these refund payment amounts were associated with premium customers.

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

You can play with the example and run your queries on SQL fiddle.

Conclusion

There are other tricks of SQL that can help you as a software engineer. Like, UPDATE with INSERT using ON DUPLICATE KEY UPDATE. Whenever you have an itch of doing some manipulation for data pulled in from database in code with loops, think again. The main takeaway from this story is:

Exploit the power of SQL to write less code because “the best code is the code that was never written”. If it is not written there is no need to maintain it.


Originally published at geshan.com.np.

Posted on by:

geshan profile

Geshan Manandhar

@geshan

Sr. Software Engineer | Agile follower | Speaker | Google Developer Expert -- Blogging at Geshan.com.np.

Discussion

markdown guide
 

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.

 

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.

 

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.

 

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.

 

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

 

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.

 

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.

 

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!

 

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.

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!

 

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.

 
 

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.

 

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

 

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 ;) ?

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.

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

I meant stored procedures in SQL itself. CREATE PROCEDURE.

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

 
 

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

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

 

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.

 

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.

 

Well articulated and I share the same view.

 
 

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.

 

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

 

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

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.

 

"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 ?

 

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.

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...

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.

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

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?

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

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)
 

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.

 

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!

 

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.

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

 

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...

 

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!

 

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.

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.

 

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.

 
 

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.

 

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

 

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.

 

Good warning sign to take of.

 

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.

 

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.

 

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?

 

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.

 

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.

 
  • 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 !
 
 

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.

 

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!

 

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

 
 

I agree with your main point, but not with your arguments. Doing math/string manip/etc in SQL is just not smart. You say it's "writing less code that has to be maintained", but that SQL code doesn't just magically go into some compartment where it doesn't have to be maintained too. And honestly, SQL is harder to maintain than whatever your application is written in.

Now, the really big thing that you didn't touch hard on is that you should NOT be doing any sort of inter-table data manipulation in SQL. The DB server software knows how to do this more efficiently than your code does, so you should be offloading that whenever possible. That's the real benefit in putting the functionality into SQL instead of your application.

 

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!

 

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.

 

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!

 

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.

 

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.

 

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

 

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...

 

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

 

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.

 
 

Doing it in SQL should perform faster. Thanks!

 

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

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

 

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

 

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

 

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.

 

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

 

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.

 
 

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.

 

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

 

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

 

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