Software Engineering Stack Exchange is a question and answer site for professionals, academics, and students working within the systems development life cycle. It only takes a minute to sign up.
Sign up to join this community
Do you test your SQL or SQL generated by your database framework?
There are frameworks like DbUnit that allow you to create real in-memory database and execute real SQL. But its very hard to use(not developer-friendly so to speak), because you need to first prepare test data(and it should not be shared between tests).
P.S. I don't mean mocking database or framework's database methods, but tests that make you 99% sure that your SQL is working even after some hardcore refactoring.
You can spend way too much time and energy testing a persistence layer and getting very little value out of it if you're too concerned about the perfect test setup, this is somewhat mitigated if your company standardizes to the testing method so it is easy to setup a second or third time. But in general you can test the most important aspects just by using SQL in the setup and teardown methods, as well using custom sql assertions.
I never tried DbUnit. What I do with nHibernate is creating test suites that create the database & data at setup (using both SchemaExport and batch inserts), then run tests that check both entities and queries.
I use local development databases such as SQL Server Express of Oracle Express Edition depending on the project.
In one very large project I was part of, we used the Rollback technique. Each test was creating a transaction which was rolled back at the end. This is described here.
But there is a better way of testing. Yesterday, I read a book called nHibernate 3.0 Cookbook from Jason Dentler in which the author propose a method called Fast testing with SQLite in-memory database.
I didn't test it yet, but that recipe is fully detailed and looks promising.
I'm sure you can download the sample code somewhere (wasn't able to find it quickly), but I highly suggest to buy the book. It contains various other test techniques such as Fluent nHibernate Persistance Tester and Ghostbusters test.
That technique is also explained in this blog post.
Yes A function or any data alteration can be tested the same as any other language. If you can test adding, deleting or updating values in an array, is working with a table that different?
If I'm trying to optimize a select statement, it will get tested against the same dataset before and after for two reasons: 1) it must return the same data 2) it's the best way to see if it is faster (compare apples to apples). Since database do so many things behind the scenes where you have less control (statistics, index fragmentation, query optimization & caching), you have to be careful when comparing performance from environment or set of data to the next. Then you can change the criteria or even the test database (Other company or different backup/version).
This is the way I set up my NHibernate integration tests:
I first create a fresh db before running my integration tests. Each query and entity mapping is tested in its own fixture.
I run each test in a transaction that is rolled back on completion. Each test is responsible for setting up test data.
This approach has worked fairly well for me especially testing queries and whether or not they are returning expected results.
In my current project I first attempted to test my SQL by checking that it matched the expected values. Then I made a change to my SQL generation that caused every query to change (in a way that made no difference to all but the newest test case) thus causing all my tests to fail.
Ugh...
Now, I test against a in memory SQLite database and the framework I use (SQLalchemy) takes care of handling the differences between that an my target database. I encapsulate the process of obtaining a database so that I can switch my tests to run against my actual database server (Firebird) occasionally, which takes considerably longer to run.
