Unit Testing. Logic in Domain vs Logic in SQL

jadjare — Mon, 12 Mar 2018 11:04:27 +0000

Unit Testing. Logic in Domain vs Logic in SQL

We have a table of 10 million records that include details of a location, a product, order date and units ordered.

We need to query this data to find the sum of units ordered by product, for a given set of locations.
We have to apply a business rule that says that if the order date is N days in the past then the ordered units are classifed as late units else they are classified as pending units.

This information will be displayed in a read on table (i.e. an onscreen report)
| Product | Late Units | Pending Units |

Our current design is to have a SQL Query that is passed the date on which ordered units should be classified as late units. The logic that then aggregates the data and places the units either in "late" or "Pending" is then applied in the SQL Stored Procedure.
The query results are then placed into a Data Transfer Object (e.g. {"product": "a", "lateUnits": 0, "pendingUnits": 100} ) and supplied to the client application via a Web Api.

The challenge with this design is it becomes much harder to write a suite of unit tests to demonstrate the business rules are correctly applied. At present we have created canned integration test data, then we spin up an a special integration test instance of the database and run tests against the sample data.

This allows us to take advantage of the SQL query engine for performance, whilst having a reasonable degree of testing coverage.

I'm still not 100% convinced of the approach we're taking and wonder what approach other developer's might take to solve this problem, e.g. would you load all the data into the domain and apply the logic there, apply the logic in SQL, or another approach?

DEV Community: jadjare

Unit Testing. Logic in Domain vs Logic in SQL

Unit Testing. Logic in Domain vs Logic in SQL