Re: unit test condition on sql statement
Hi, ? What does "almost as fast" mean in practice?
A few stats about our codebase in this 14 months old Java project: - we have almost 800 classes of production code with ~32K LOC (empty lines excluded) - we have almost 500 classes of tests with ~36K LOC
- JUnit reports 2400+ tests in these 500 classes (some are parameterized tests) - the test coverage is about 80% - out of those 500 classes about 70 are those that run against a real Postgres database - the whole test suite runs in under 12 seconds on my M1 Mac Mini, almost as fast on an i7 laptop with Ubuntu and in about 1minute on older Macs.
This has been sufficiently fast for our team. We know a few tricks we can apply to speed it up slightly if needed.? To run a single DB-bound test - there is a slight overhead - it probably takes a few seconds. But this has never been a problem for us and there is no tendency in the team to avoid writing them. Sometimes it actually feels a welcome change from mocking.
One additional benefit of those tests is that we have started to rely and use more specific SQL features in our code. E.g. triggers, constraints, custom functions, etc. This has allowed us to move some logic closer to the data while maintaining the confidence that TDD gives us in building those. We have not yet seen a need for a view but that probably lies on the horizon and I can create that view with a DB migration in true TDD style and make sure that the data that I insert into the tables is available properly via the trigger.
Best regards, Erik ?
|
Re: unit test condition on sql statement
If you are using Views, there is also the possibility to shift the unit tests to the database layer to ensure some of its logic (at least with some databases, e.g. MS SQL using tSQLt or Oracle using utPLSQL). Database logic can then be treated as separate module, which can be beneficial.
|
Re: unit test condition on sql statement
What does "almost as fast" mean in practice?
20 ms is "almost as fast" as 0.5 ms for the first hundred or so tests.
That said: *sometimes* in the case of tricky dynamically built sql (for example: lists with paging, sorting, and filtering on multiple fields), I like to write integration-with-the-database tests, regardless of the method/tool used to build the queries.
A couple of the optimizations you might end up at here are:
1) introduce a view to make querying more convenient
2) introduce eventuality for performance reasons (for example a materialized view that is updated on a schedule).
Sometimes it feels like turning these problems that are easily/elegantly solved in SQL into tests over OO programming language abstractions makes it harder to find the simplest solution.
|
Re: unit test condition on sql statement
? Although purists would not call them unit tests, we have this kind of code inside repository classes and the tests of those classes are executing against a real database.
It is almost as fast as regular unit tests on modern hardware and it gives us great confidence that the code actually does what it is supposed to do.
Indeed. I would only become concerned about this if we found ourselves copying and pasting the same set of integrated tests for every new table that we introduce. After a while, we can trust ourselves to write "select * from TABLE where A and B and C and D...". In that situation, I'd rather write tests that expect { TABLE, [A, B, C, D, ...] }.
The integrated tests scam happens when we start to stubbornly refuse to replace integrated tests with microtests, due to the Sunk Cost Fallacy or because "there's just too much".
We are using Postgres and when doing local development we have a docker-compose.yml which is used to start the database only once. To execute the tests we clear the database, re-run all migrations and then the test. Each test is run in a separate transaction which is rolled back after execution. This way the database is in a clean state for each successive test. An abstract DbTest base class takes care of all initialisation and teardown logic so that adding a new repository and its tests is very easy.
Although these tests are definitely slower that regular unit tests, in practice the speed difference is not that big on human scale (not millisecond scale) that it is not an issue for us and we have never turned away from writing these test because they are slow or hard to write.
This works great until it doesn't. The real question becomes how we react when it stops working for us: do we roll up our sleeves and refactor or do we give in and watch the test suite just become slower and slower and slower until it strangles the project. If it keeps working for us, then we keep doing it! :) -- J. B. (Joe) Rainsberger :: ?:: ::
-- J. B. (Joe) Rainsberger :: :: :: Teaching evolutionary design and TDD since 2002
|
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.
I strongly agree with this over the long term!
If we only need to format a WHERE clause from a List of expressions, then it suffices to write that code ourselves. Once we need to format a third or fourth kind of SQL Expression, I would gladly invest a few days in finding a suitable library to do that for us. I would look specifically for a library that lets us check SQL Expression objects as values without needing to execute the query on a database. -- J. B. (Joe) Rainsberger :: ?:: ::
-- J. B. (Joe) Rainsberger :: :: :: Teaching evolutionary design and TDD since 2002
|
Re: unit test condition on sql statement
Hi Tony and all On the matter of building SQL queries, I successfully tried the JOOQ library in the past (supposing you're using Java, I don't see any mention of the language).? I don't know if it's your case but it's free to use for open source DB engines.? Since it has lots of features - and I don't work with it since years - maybe it can help in building SQL queries under some boolean conditions/predicates.?
Indeed! After a few months, I would probably be reaching for a library like this one, especially if I noticed that we had already built 15% of it. :) -- J. B. (Joe) Rainsberger :: ?:: ::
-- J. B. (Joe) Rainsberger :: :: :: Teaching evolutionary design and TDD since 2002
|
Re: unit test condition on sql statement
On Fri, Apr 9, 2021 at 2:18 AM Avi Kessner < akessner@...> wrote: ? Perhaps, instead of moving the business decisions up a level, try moving the sql string generation, down a level? This way the existing api is the same, but internally the strings can have a level of indirection and you can test the queries independently m
Indeed, if Tony tries to move the business decisions up a level, then eventually he'll probably want to move other related code up a level, tool, resulting in the same structure as would have resulted from moving "format SQL Expression" behavior down a level.
Good news: all roads lead to the same place. :) -- J. B. (Joe) Rainsberger :: ?:: ::
-- J. B. (Joe) Rainsberger :: :: :: Teaching evolutionary design and TDD since 2002
|
Re: unit test condition on sql statement
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?
I'm intentionally replying for reading what others have to say, so that I'm not anchored.
First, there's how I would refactor if I refactored. I would probably separate choosing the elements of the WHERE clause from putting them together. Eventually, the WHERE clause could become a textbook example of the Specification pattern, but as a first step, it looks like connectWithAnd(List<WhereClauseExpression terms) would be enough. I could write tests that check for WhereClauseExpression values (themselves Strings at first) and then maybe just 1 or 2 tests for connectWithAnd(), because that function is really just "join terms with the string ' AND '". (Maybe that's Too Simple to Break?) You could imagine how to expand this over time: a little library for formatting SQL statements from various "SQL Expression" objects and then domain-level behavior for computing SQL Expression values that we can easily check with assertEquals(). The goal is just to separate the domain behavior from the technology integration of formatting SQL statements as text. I'd build the SQL Statements library slowly by removing duplication from SQL statements as I found it.
I use a similar trick when writing tests for multiline strings. Most of my tests check the _lines_, expecting a List<String>, and then I compose the production code with join(newline), because that's the boring part. This helps me avoid annoying and boring edge cases, such as "should there be a newline at the end?" and I can be sure that none of the lines include newline characters inside them. My code tends to evolve library functions for lines() and unlines() and then the rest of the time I have functions from List<String> to List<String> (or maybe even Stream<String> in Java).
Next, there's when I would refactor. I would almost certainly write tests for formatting the SQL statement separately from writing tests for _executing_ the SQL statements. I might not need tests for executing the SQL statements if I already trust my SQL execution library. I might write some smoke (integrated) tests that execute 1 or 2 examples of the SQL statement just to make sure that I can detect when tables change. I would then let duplication in the tests guide me to separate the "format the SQL statements" tests into the two categories of tests from the previous paragraph (1: compute the correct List of clauses; 2: format the List of clauses into an SQL statement by connecting them with AND).
If you and I were working together on this project and you wanted to continue using integrated tests for now, I would argue against you for maybe 30 seconds. Pretty quickly, we'd see that executing many SELECT statements with similar structure, even on an in-memory database, would become boring and I'd push harder to check the SQL statements as text in memory without always executing them. -- J. B. (Joe) Rainsberger :: ?:: ::
-- J. B. (Joe) Rainsberger :: :: :: Teaching evolutionary design and TDD since 2002
|
Re: unit test condition on sql statement
I practice this quite a bit too. I call them contract-tests or integration-tests, depending on which terminology mood I am in that day.? I do my best to keep the number of such tests low, and the portability high. I find it important to differentiate them from unit tests out of respect to CI/CD engineers, due to the different?environmental setup needs, and also to adhere to the law of least surprises.
One man's pureism is another man's pragmatism.
Hi Tony,
Although purists would not call them unit tests, we have this kind of code inside repository classes and the tests of those classes are executing against a real database.
It is almost as fast as regular unit tests on modern hardware and it gives us great confidence that the code actually does what it is supposed to do.
We are using Postgres and when doing local development we have a docker-compose.yml which is used to start the database only once. To execute the tests we clear the database, re-run all migrations and then the test. Each test is run in a separate transaction which is rolled back after execution. This way the database is in a clean state for each successive test. An abstract DbTest base class takes care of all initialisation and teardown logic so that adding a new repository and its tests is very easy.
Although these tests are definitely slower that regular unit tests, in practice the speed difference is not that big on human scale (not millisecond scale) that it is not an issue for us and we have never turned away from writing these test because they are slow or hard to write.
I can give more details about our setup if anyone is interested.?
Best regards, Erik
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
|
Re: unit test condition on sql statement
Hi Tony,
Although purists would not call them unit tests, we have this kind of code inside repository classes and the tests of those classes are executing against a real database.
It is almost as fast as regular unit tests on modern hardware and it gives us great confidence that the code actually does what it is supposed to do.
We are using Postgres and when doing local development we have a docker-compose.yml which is used to start the database only once. To execute the tests we clear the database, re-run all migrations and then the test. Each test is run in a separate transaction which is rolled back after execution. This way the database is in a clean state for each successive test. An abstract DbTest base class takes care of all initialisation and teardown logic so that adding a new repository and its tests is very easy.
Although these tests are definitely slower that regular unit tests, in practice the speed difference is not that big on human scale (not millisecond scale) that it is not an issue for us and we have never turned away from writing these test because they are slow or hard to write.
I can give more details about our setup if anyone is interested.?
Best regards, Erik
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
|
Re: unit test condition on sql statement
Alternatively, write the adapter and test against one of the in-memory databases. That might get you a surprising distance.
|
Re: unit test condition on sql statement
Tony,
I wouldn't test that the sqlStatement is what I expect. I would test that it returns the right results.
I generally put such SQL-building logic into a data access object--an adapter class to the database that isolated my application from everything that was database-specific. Then I can test my application using fake DAOs for really fast tests that don't touch the database.
In parallel, I test my data access object against a test database that I can setup data under test control and know what results to expect. Working in Java, I used dbunit to manipulate and verify the database.
- George
toggle quoted message
Show quoted text
On 4/8/21 5:01 PM, Tony 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
--
----------------------------------------------------------------------
* George Dinwiddie *
Software Development
Consultant and Coach
----------------------------------------------------------------------
|
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
toggle quoted message
Show quoted text
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
|
Re: unit test condition on sql statement
Hi Tony and all On the matter of building SQL queries, I successfully tried the JOOQ library in the past (supposing you're using Java, I don't see any mention of the language).? I don't know if it's your case but it's free to use for open source DB engines.? Since it has lots of features - and I don't work with it since years - maybe it can help in building SQL queries under some boolean conditions/predicates.? Hope this helps.?
Il ven 9 apr 2021, 07:18 Avi Kessner < akessner@...> ha scritto: Perhaps, instead of moving the business decisions up a level, try moving the sql string generation, down a level? This way the existing api is the same, but internally the strings can have a level of indirection and you can test the queries independently m
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
|
Re: unit test condition on sql statement
Perhaps, instead of moving the business decisions up a level, try moving the sql string generation, down a level? This way the existing api is the same, but internally the strings can have a level of indirection and you can test the queries independently m
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
|
Re: unit test condition on sql statement
Hi Tony,
have you considered creating a class that takes whose sole responsibility it to generate the SQL strings? ?Have the existing class rely on the SQL generator, then unit test the generator.
Hope that helps! Graff
On Thursday, April 8, 2021, 05:18:08 PM EDT, 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
|
unit test condition on sql statement
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
|
Re: Swift Extension on Array - not working ... hmmm
David,
If I understand what you're doing, you should just be able to say?
? return lineup[n % lineup.count]
The line you have:
? let?indexWrapped = n?%?indexMax
already sets the indexWrapped to a number from 0..<indexMax i.e. 0..<lineup.count, so there's no need for the "if" statement following.
(I'm assuming no negative indexes will happen.)
For the ?: operator, some other time:), you probably want to think of it as an expression:
? ? let variable = (condition) ? answer1 : answer2
You don't have to assign the result of ?: - you can use it directly in another expression, but it gets messy pretty quickly.
-- Bill
|
Re: Swift Extension on Array - not working ... hmmm
I'm learning & sharing... if you have critique of this... I could learn more!
|
Re: Swift Extension on Array - not working ... hmmm
class LineUp {
? ? var lineup = ["Bob", "Tony", "Iris", "Jane", "Nan", "Dave", "George", "Ringo", "Shannon" ]
?? ?
? ? subscript(n: Int) -> String {
? ? ? ? var index = 0
? ? ? ? let indexMax = lineup.count
? ? ? ? let indexWrapped = n % indexMax
? ? ? ? //indexWrapped <= 0 ?? index = indexMax : index = indexWrapped - 1
? ? ? ? if ( indexWrapped <= 0 ) { index = indexMax - 1 } else { index = indexWrapped - 1 }
? ? ? ? return lineup[index]
? ? }
}
?
let list = LineUp()
?
print("\(list[1]) batter")
?
list.lineup.append("Paul")
print("\(list[17]) battting last")
print("\(list[18]) battting last")
print("\(list[19]) battting last")
print("\(list[20]) battting last")
print("\(list[21]) battting last")
?
|
J. B. Rainsberger
Gu?laugur Egilsson
David Koontz