¿ªÔÆÌåÓý

ctrl + shift + ? for shortcuts
© 2025 Groups.io
  1. Testdrivendevelopment
  2. Messages
Single Messages Topics Expanded Polls

Re: unit test condition on sql statement

 

HI Tony,

You most certainly don't want to create SQL queries "by hand",
concatenating strings. I have worked a full year with a codebase
that's been doing this kind of stuff for >15 years and it's a
nightmare, introducing all kinds of side-effect and turning a simple
application into an unmaintainable monster.
As others suggested, there's plenty of libraries out there that
provide higher level abstractions for what you want to do, either
exposing some form of relational algebra, ORM, parameterized queries
and what not.
Then depending on the technology you use, you can focus on unit
testing the business logic, eg. that some business rule produces some
object/structure representing faithfully the needed query or data, and
have "integration" tests that really check your business logic works
with your database. Note that "integration testing" with a different
DB than the one(s) you intend to use in production is also a path
fraught with perils as any single SQL engine is different in subtle
ways from every other.

My 2 cts.
--
Arnaud Bailly - @dr_c0d3

On Thu, Apr 8, 2021 at 11:18 PM Tony Vo <ttrung.vo@...> wrote:

Hi everyone,

I'm running into a scenario where I need to add extra condition in sql statement like this
sqlStatement.sql("select id from table_a")
if (conditionA == true) {
sqlStatement.sql("AND table_a.column_a == 0")
}
// note there's many similar sqlStatement changes per other conditions as above....

The good news is that there's a good amount of integration tests surround this area. However, I feel like it's very rigid to make changes to sql statement based on some business conditions.

The other option I can think of is basically to move the condition to a higher level and then it's probably easier to unit test to check the code to execute different SQL statement based on the condition.
Although I gotta say, the easiest change (least amount of effort is to make the above change and write integration tests for that change)

Moving the business condition to higher level is possible, but I would have more works to break some more dependencies and keeping the existing API unchanged as there're lots of methods calling this method.

Any thoughts/comments/suggestions?
Cheers,
Tony
Join [email protected] to automatically receive all group messages.