Today I’ve been on technical meeting where TSQLUnit was discussed as approach of testing database.
From what I understood it looks like this is very powerful and great tool for testing stored procedures and other execution code inside of SQL Server. It allows easily create setup for each test with minimal database schema, needed exactly for some particular stored procedure. After test got executed it rolls back to state before running test. Cool!
From my point of view this is really good thing to test stored procedures in isolated environment, as any good UT does with testing code. There is also question about integration of those tests with changes in real database and one great mind have produced using schemabindings in SQL Server.
It should be also possible to integrate TSQLUnit with CI by using NAnt.
Why?
There is only one thing I kept in my mind and did not talk about it during meeting. It is question: why do we still write lot of stored procedures? Yea, I understand that sometimes they are really needed and that there are some reporting projects that might require fetching lot of data at once. But during the meeting it looked like guys are going to use it for many other projects. Hm… From my point of view, this kind of tests should be written in regular programming language, executing some code and fetching needed data as it will be used further, after roll-backing inserted data – therefore we have integration tests. All them should run separately. And as per me this should be fine unless you have logic in database – I agree, that in this case we have to come to some database testing tools. Why should we have logic it in database unless there are some special requirements?
I still feel a bit frustrated, because for me it is quite hard to answer for the question of having logic in code or having some heavy portion of it in database. I vote for first variant, but maybe I do not have enough experience to understand why it is great to have logic in database. Who knows?
What do you think?
6 comments
| Markdown | Result |
|---|---|
| *text* | text |
| **text** | text |
| ***text*** | text |
| `code` | code |
| ~~~ more code ~~~~ |
more code |
| [Link](https://www.example.com) | Link |
| * Listitem |
|
| > Quote | Quote |
I'm also for keeping business logic in code, not stored procedures. When you push logic into stored procedures, application code becomes procedural and quite ugly (well, at least for me, maybe others call it pretty).
I've blogged on what I think about SPs too:
http://www.codeproject.com/Articles/105548/Stored-Procedures-Another-look.aspx
But there's also another advantage of keeping BL in SPs anyways. Think about how easy it is to fix some bug in production. All you need is to send an .sql to someone who can run it on production server. There's no need to redeploy whole app.
gecka, thank you for your comment. I read your post about your opinion in regards of stored procedures and I agree with you completely.
Great post, Andry. Here is what i think:
– generally speaking – use stored proc, as well as views primarily to isolate the application from directly accessing to the tables. Both for the data reads and writes.
– Bind your ORM entities to stored procedures, never bind directly to a table
– always use SQL and thus stored proc for any data analytics. SQL is a very powerful data analysis language. Examples:
A. reports – never use your ORM and business entities for the reports, unless the report is simply displaying the attributes of one entity
B. whenever you need to return a scalar value or a record set to the caller application based on a big amount of data, either from a single or multiple tables. 2 reasons:
reason 1.Manually constructed SQL – either stored procedure or user defined function will do much better job than the SQL produced by ORM
reason 2. with stored procedure you keep the data within SQL server boundaries while ORM will have to "pump" big amount of data used for calculation outside of SQL server boundaries which is a hit on performance
Roman, thank you for such comprehensive comment. It really makes a lot of sense. So I agree with idea of running stored procedures for reporting purposes.
Although I do NOT agree with this:
"- Bind your ORM entities to stored procedures, never bind directly to a table"
Did you mean here some special scenarios or are you talking in general? Because if this is for general I completely disagree. Even more, ORM-s are not so good in binding to stored procedures and to views.
You would want to do it for the same one reason – so the application, including the ORM does not talk to the underline table schema directly. The fact ORM's are problematic with stored procedures is a different story. Although i have seen people using this approach with MS EF 4 with no problems. Using the updatable views is another option…
Roman, I do not see any advantages of making ORM use SPs and Views instead of tables. Abstracting the table schema – why would you do that?
I'd prefer using tables directly – all relational databases support tables (obviously), but some do not support SPs. SQLite for example, does not support SPs and it is widely used for unit testing your data access layer by creating an in-memory database.
If you want to isolate your application from changes in table schema, well that depends on the project probably, but table schemas doesn't change that often I guess. If they do, you just need to update the mappings of ORM – that should be as easy as fixing Stored Procedures to match the schema.
If you want to have SPs just in case you might need to add something later – that's violation of YAGNI.