On Fri, May 20, 2016 at 10:43 PM, Guyren Howe <guyren@gmail.com> wrote:
On May 20, 2016, at 13:38 , Pierre Chevalier Géologue <pierrechevaliergeol@free.fr> wrote: > > Le 04/05/2016 18:29, Szymon Lipiński a écrit : >> On the other hand, when I was trying to store all my logic in a >> database, there was just one thing that made me hate it. Testing. >> Testing the procedures inside the database was not easy, not funny, and >> too much time consuming. > > Yes, very good point.
Are there any best practices or tricks to make this easier?
Strangely I have never had a problem testing stored procedures. You have to create a data set for the tests of course and that is the hardest part, but there are some really nice things:
1. If your test scripts always roll back you can run them on a production database as a troubleshooting step
2. It is easy to hook things up to a TAP harness (whether using PgTAP or some hand-rolled solution). I think it would be harder to connect to xunit though. So use TAP ;-)
3. I usually create a test results table (in my test case, rolled back after!) which stores the test description and pass status. That makes it easy to check using other tools.
Usually I set aside a range of things (negative id's for example) for testing purposes.
I had problems, and I'm really interested in making it work for me. I have a couple of questions:
How do you manage versioning of the stored procedures? Especially do you have any problems upgrades?
What about testing logic which is outside the database? Do you use pgtap for testing the schema only, or to test some of the external logic as well?
Do you use logic inside and outside the database at the same time?
How does this scale to a couple of servers when the load is so huge you need to have e.g. ten physical web servers at front? It seems for me that it would be easier to spread the cpu logic overhead to plenty of servers instead of having just one machine which needs to do all the things. But maybe I'm wrong.