I am trying to confirm best practice around architecture I am working with.

Frontend <-> API gateway <-> Service layer (Rest API) <-> DB

I have an incoming request which is asking for huge data set (>20000 rows) from multiple DB tables clubbed into one single data set. Now there are two school of thoughts and I want to know which one is better suited to this situation.

  1. Customize DB query to return only tailored data set as needed above. But this includes hard-coding at DB level where service layer calls a predefined query/ set of predefined queries always. Downside I see is any DB change leading to 2 sets of changes - one at DB & second at service logic
  2. Service layer is generating custom query on-the-run and calls DB with this query to get data set. This will ensure immunity to DB and service layer can tweak queries as needed without any hard-coding at DB level.

What is the best practice to follow, any addendum is also welcome.

P.S. As we have large data set & performance constraints - still I am in favor of approach no. 2

  • 2
    Now there are two schools of thoughts and I want to know which one is better suited to this situation. You will hear this often: " the one that best suits your needs and requirements". An architectural decision is not only made upon best practices or upon "what works for others". You have put each solution in context. Your context. The project's context and see which one meets your expectations and in which way they do – Laiv Mar 25 '19 at 11:37
  • 1
    If changing the db platform is not in vision, leave the database stuff at the database. Use the full potential of the database tools such as views, stored procs, etc. and document everything clearly. – NoChance Mar 25 '19 at 13:12

You are missing a Repository or DAL layer which should abstract the SQL from your service layer.

Not sure what you mean by "generating custom query on-the-run" though. You should have fixed queries which you have tested for performance.

Generally your choice is between "big query which returns exactly the data you want" vs "several small queries who's results are combined by the service layer". and which way you go depends on the reusability and cacheability of the smaller queries.

Say I can query my List of Customers with Id and Name and my List of Orders with CustomerId. It makes sense to keep them separate as I can use the List Of Customers for lots of things unrelated to orders.

If I'm sending mass emails for an advertising campaign though I'll probably only want each customers emails address once. So returning it from the DB with the rest of the fields is going to be better.

  • Thanks. Its between one big DB query/procedure containing hardcoded 'if-else ' conditions baked in DB and called by service layer VS keeping DB intact and creating Java stored procedures with custom query to get required flexibility so it ensures we do not mess around with DB at a later stage. – Finn Mar 26 '19 at 4:27
  • both of those options would seem to break the rule about not putting business logic in the DB – Ewan Mar 26 '19 at 7:49
  • After much discussions, we will encapsulating DB logic in stored procedures & calling these via Java service layer. – Finn Mar 27 '19 at 4:14

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.