I have an MVC web app that's wired against PostgreSQL. The DB uses the full gamut of various referential constraints to ensure referential integrity, as opposed to say the Rails approach, where you try to do most of the checks in the model logic. Also, by choice, I'm not using an ORM, I'm writing the SQL statements by hand for each model.
Now, I want to write unit tests and functional tests for it. The problem is that if I write these tests against the database (as opposed to mocking), I will have to create an entire long hierarchical chain of references for each model I'm testing, or the system won't let me generate them in a vacuum. E.g. I cannot create a model without a foreign key, and the foreign key does have to point to an existing record, etc. all the way up the chain.
The thing to remember is that, unlike with an ORM, I cannot trust my data access layer (DAL) to work as expected. I might have fat fingered a statement somewhere, or maybe just got the logic wrong. I must test my data access layer to do what it's supposed to.
One way to solve this problem is to use something like Rails' Factory Girl, which, when properly configured, will generate the chain of records you need to instantiate any model in your system. This way I test both the DAL and the model logic. This is obviously not ideal because test cases would waste a ton of time hitting the DB and add extra boilerplate that I simply don't need. My suite will go up in run time real fast, which is bad.
The solution I'm thinking of is to:
- have a test suite just for the DAL. This test suite would make sure of creating all of the right references and would only test that the SQL statements are working as expected. References won't really be explicitly tested (seems a bit of an overkill, but it can be done if desired), but at the very least it will make sure that a chain of relations can be built in the "happy path" scenario. This suite is ultimately going to be comparatively short and thus not consume too many cycles.
- mock both model and controller tests, so that things can be tested in isolation without DB reference constaints getting in the way. This is where the vast majority of testing will happen, so mocking the DB makes complete sense as far as saving time goes. This way I'm also not testing the SQL access layer twice, no need to.
My question is, is the proposed solution above how most people solve their SQL with constraints and model/controller tests problem, or am I missing an option here?
