Check if a good request has been issued
Typically, I find myself writing block tests against database calls, and I always run into the same problem: how do I check if a good query is being sent to the database?
Example. I have this class that will send the final update to the database in the following form:
update credential set password_hash = ?, password_crypt = ?, password_plain = ? where id = ?
(this is a password migration tool, please ignore the security concerns with the field password_plain
)
By writing a test class for this class, I mocked the database access class (in this case, I'm using Spring JDBCTemplate) and grabbed the released sql. Once I have my sql, I do the following checks:
String space = "\\s+";
String optSpace = "\\s*";
String something = ".+";
String optSomething = ".*";
sql = sql.toLowerCase();
assertTrue(sql.matches(optSpace + "update" + space + "credential" + space + "set" + space + something));
assertTrue(sql.matches(something + space + "set" + space + optSomething + "password_hash" + optSpace + "=" + optSpace + "\\?" + something + "where" + something));
assertTrue(sql.matches(something + space + "set" + space + optSomething + "password_crypt" + optSpace + "=" + optSpace + "\\?" + something + "where" + something));
assertTrue(sql.matches(something + space + "set" + space + optSomething + "password_plain" + optSpace + "=" + optSpace + "\\?" + something + "where" + something));
assertTrue(sql.matches(something + space + "where" + space + optSomething + "id" + optSpace + "=" + optSpace + "\\?" + optSomething));
With these checks, I do confirm that the released SQL contains the most important parts of the update, for example:
- the correct table is updated.
- all 3 fields are updated to the values passed as parameters
-
id
used in a statementwhere
with its value as a parameter
I could just check if the request issued is exactly the expected request above, but that would make the test too constrained for future changes and would fail if any part of the request is changed, even if the update stays true. Since I think the tests are written for use mostly in the future (when you change software and need more confidence to do so) and not in the present, this option will make the test useless.
Well, finally, I am announcing my question: what are the best options we have for validating the released SQL?
I see many projects that build small embedded databases with little data to test database related classes, but I wanted to write a cleaner alternative to unit test (if I can call it)
source to share
I don't think there is a good alternative to testing a real database (even if it is embedded, etc.). At this point, you are checking that your SQL is syntactically valid, but that it actually works. e.g. Do you know this will violate restrictions etc ...
Mocking, etc. all is well and good, but at some point you need to test the database. I would ensure that you don't test the database whenever possible and then bite the bullet and create tests around a small database (with appropriate rollbacks / rebuilds, etc.) to actually confirm the correct db functionality.
source to share
I implore you to reconsider the benefits of these tests because
- checks execution not behavior
- when you change the SQL query in the future (or someone just adds a harmless space by mistake), you will have a failed test even if you retained the behavior.
For the final DataAccessLayer, I would recommend writing an integration test. One that works against a real but minimal DB. Sure, these tests will be slow, but the confidence in what they offer is well worth it.
So, write tests with GetCustomers () and verify that the returned DTO contains the correct data and verify that the SQL query you issued is X.
source to share
don't assert your sql. it is pointless. you end up comparing the sql string passed in with another string (also created by you so that there is no validation), or you have to implement your own database. instead, just use the existing one. check is a query that returns correct data or correctly modified data in the database. use dbunit or sth.
source to share