Writing a DAL with TDD

[Cramer]

I've finally gotton board with TDD (test driven development) and wow is it
effective! I went from sceptic to True Believer with my first effort.

My question: According to the various books and articles I have read about
TDD, a good unit test does not rely on the database or other such
external/environmental conditions. More generally, a good unit test is
atomic and makes as few assumptions about its runtime environment as
possible. But what about developing unit tests for a DAL - whose purpose in
life is to interact with a database?

Your thoughts and opinions are appreciated.

 

[mgsram]

I've finally gotton board with TDD (test driven development) and wow is it
effective! I went from sceptic to True Believer with my first effort.

My question: According to the various books and articles I have read about
TDD, a good unit test does not rely on the database or other such
external/environmental conditions. More generally, a good unit test is
atomic and makes as few assumptions about its runtime environment as
possible. But what about developing unit tests for a DAL - whose purpose in
life is to interact with a database?

Your thoughts and opinions are appreciated.

I would test the behavior in the DAL. I m not very well versed with DB
programming
but, as far as i know DAL abstracts a bunch of SQL statements or calls
to
stored procedures in the DB.

So I would test for setting up the adapter object that actually
tunnels the call to the DB.
The rationale is that if you have validated the behavior that sets up
the adapter object,
then it will work correctly when you connect a actual DB.

If the Adapter object cannot be mocked direclty, then I would write a
simple wrapper around
that, that mocks the adapter.

 

[Mr. Arnold]

I've finally gotton board with TDD (test driven development) and wow is it
effective! I went from sceptic to True Believer with my first effort.

My question: According to the various books and articles I have read about
TDD, a good unit test does not rely on the database or other such
external/environmental conditions. More generally, a good unit test is
atomic and makes as few assumptions about its runtime environment as
possible. But what about developing unit tests for a DAL - whose purpose
in life is to interact with a database?

Your thoughts and opinions are appreciated.

You could do the unit test of the DAL through the interfaces of the Business
layer that interacts with the DAL.

This link has shows about TDD and unit testing of a TDD solution.

http://www.polymorphicpodcast.com/



There is also this which can show you how to develop your solutions, and how
you can test through the interface. You should be able to develop a test
harness for the interface or use something line Nunit.


MODEL-VIEW-PRESENTER

http://www.polymorphicpodcast.com/

click 'Shows'

click 'Design Patterns Bootcamp: Model View * Patterns*

view parts 1-5

 

[Ben Voigt [C++ MVP]]

I've finally gotton board with TDD (test driven development) and wow
is it effective! I went from sceptic to True Believer with my first
effort.
My question: According to the various books and articles I have read
about TDD, a good unit test does not rely on the database or other
such external/environmental conditions. More generally, a good unit
test is atomic and makes as few assumptions about its runtime
environment as possible. But what about developing unit tests for a
DAL - whose purpose in life is to interact with a database?

The unit test needs to create a new database in a known state, open it using
the DAL under test, verify the results, clean up the database.

 

[mgsram]

The unit test needs to create a new database in a known state, open it using
the DAL under test, verify the results, clean up the database.

Ben,
Then it wont be a unit test in the TDD sense. In fact i would argue
that its not unit test at all and more of Integration testing. The
point is your tests would be "lazy" and would not help you drive the
design of DAL which is what the goal is.

 

[Ben Voigt [C++ MVP]]

Ben,
Then it wont be a unit test in the TDD sense. In fact i would argue
that its not unit test at all and more of Integration testing. The
point is your tests would be "lazy" and would not help you drive the
design of DAL which is what the goal is.

Well... ok. This might no be considered a unit test anymore, it is quite
heavy.

But there's no reason that you couldn't still develop the test before the
code. You could even use the test to drive database layout (this might not
be very effective at normalization though).

 

[mgsram]

Well... ok. This might no be considered a unit test anymore, it is quite
heavy.

But there's no reason that you couldn't still develop the test before the
code. You could even use the test to drive database layout (this might not
be very effective at normalization though).

Absolutely,
I agree. In fact thats how i would even design my Database tables.

 

[John B]

I've finally gotton board with TDD (test driven development) and wow is it
effective! I went from sceptic to True Believer with my first effort.

My question: According to the various books and articles I have read about
TDD, a good unit test does not rely on the database or other such
external/environmental conditions. More generally, a good unit test is
atomic and makes as few assumptions about its runtime environment as
possible. But what about developing unit tests for a DAL - whose purpose in
life is to interact with a database?

Your thoughts and opinions are appreciated.

Similar to what Ben said,
We used to have a "test" database (or one of each, mssql, mysql, oracle)
that each time would be cleaned out and then repopulated by each test.
It might not be very scaleable but worked well for what we were doing at
the time which encompassed 500+ tests running in around 20 seconds.

JB

 

[Cramer]

Similar to what Ben said,
We used to have a "test" database (or one of each, mssql, mysql, oracle)
that each time would be cleaned out and then repopulated by each test.
It might not be very scaleable but worked well for what we were doing at
the time which encompassed 500+ tests running in around 20 seconds.

Thank you John,
Along the lines of what you did, I have heard unit tests (against a DB)
doing something like this: (1) open a transaction in the test fixture setup;
(2) run the tests; (3) roll back the transaction in the test fixture
teardown.

-Cramer

 

[Frans Bouma [C# MVP]]

Ben,
Then it wont be a unit test in the TDD sense. In fact i would argue
that its not unit test at all and more of Integration testing. The
point is your tests would be "lazy" and would not help you drive the
design of DAL which is what the goal is.

'Designing' a dal using TDD is silly anyway, as a DAL has an
unlimited amount of usage scenario's so you can't design it by starting
with tests, you can only design it in a more generic way and use
unittests for integration tests and automated checks for expected edge
cases in the generic design.

Anyone who thinks s/he can design a DAL using TDD has never even made
an attempt to do so.

FB

--
------------------------------------------------------------------------
Lead developer of LLBLGen Pro, the productive O/R mapper for .NET
LLBLGen Pro website: http://www.llblgen.com
My .NET blog: http://weblogs.asp.net/fbouma
Microsoft MVP (C#)
------------------------------------------------------------------------

 

[Frans Bouma [C# MVP]]

On Apr 24, 11:52 am, "Ben Voigt [C++ MVP]" <[email protected]>
wrote:
Cramer wrote:
I've finally gotton board with TDD (test driven development)

and wow >>> is it effective! I went from sceptic to True Believer
with my first >>> effort.

My question: According to the various books and articles I have

read >>> about TDD, a good unit test does not rely on the database
or other >>> such external/environmental conditions. More
generally, a good unit >>> test is atomic and makes as few
assumptions about its runtime >>> environment as possible. But what
about developing unit tests for a >>> DAL - whose purpose in life
is to interact with a database?

The unit test needs to create a new database in a known state,

open >> it using the DAL under test, verify the results, clean up
the >> database.

Your thoughts and opinions are appreciated.

Ben,
Then it wont be a unit test in the TDD sense. In fact i would
argue that its not unit test at all and more of Integration
testing. The point is your tests would be "lazy" and would not
help you drive the design of DAL which is what the goal is.

Well... ok. This might no be considered a unit test anymore, it is
quite heavy.

But there's no reason that you couldn't still develop the test
before the code. You could even use the test to drive database
layout (this might not be very effective at normalization though).

Absolutely,
I agree. In fact thats how i would even design my Database tables.

err.... how can an abstract entity definition be based on a test? A
test drives API design, not which attributes belong to which entity
definition, which supertype it inherits from nor which entity
definitions it has relationships with over which attributes nor which
relationships are objectified and form entities themselves

FB

--
------------------------------------------------------------------------
Lead developer of LLBLGen Pro, the productive O/R mapper for .NET
LLBLGen Pro website: http://www.llblgen.com
My .NET blog: http://weblogs.asp.net/fbouma
Microsoft MVP (C#)
------------------------------------------------------------------------

 

[Frans Bouma [C# MVP]]

Thank you John,
Along the lines of what you did, I have heard unit tests (against a
DB) doing something like this: (1) open a transaction in the test
fixture setup; (2) run the tests; (3) roll back the transaction in
the test fixture teardown.

You can do that, you can also use separate databases for separate
groups of tests. We for example use separate databases for fetch
testing our o/r layer and saving data and another database for
combinations of the two. This way you don't lose a lot of time when
databases have to be cleaned up nor do you have to pre-define data to
insert, as that's already there (for the fetch database for example).

However, the biggest part of testing your DAL isn't about TDD. It's
about testing the expected edge cases of your design. I can't stress
enough how dumb it is to design a DAL using TDD, unless you want a dal
with a gazillion methods scattered all over the place and no uniform
way of data flow, graph management etc.

For example having a graph of customer, order, order details, product,
employee objects, how is that persisted? In what order? How do you
calculate that order? Are newly db-generated pk values synced at once,
or delayed? if an entity isn't 'dirty' (changed) but has an association
with an entity which is new and which will get a PK set in the DB when
it is saved, is it expected that this non-dirty entity is saved as
well? (yes). When will that fail?

You won't get to those problems when using TDD to access your data, as
you will program out what you need for the features USING the DAL.
That's ok, if your DAL is very small and your database has perhaps 3
tables, but if your database has 200 tables, you're not going to
succeed at all when you start with TDD and program your way down to the
database, simply because you soon enter a complex area where things
have to be solved in a generic way, but when you want to do so, you've
to abandone TDD altogether (read: you're forced to think up-front and
make design decisions up front, oh my) to get things setup generically.

That's not to say TDD doesn't work in this area. You can perfectly
fine design your API accessing the DAL, i.e. your query interface,
using TDD, however don't fall for the assumption that when you write,
say, 1000 tests, your DAL works nor think that by writing 1000 tests
and the code in the DAL necessary to run the tests, your DAL is
finished and suitable for your application.

TDD people bash the crap out of me when I tell stories like the one
above. It's up to you what to believe, but I can assure you: I know
what it takes to write a big feature rich data access framework and
it's not doable with TDD.

That's not to say unittests suck, on the contrary, however as
automated tests for the integration tests: does our code also work on
oracle? When I specify 2, 3, 4, predicates, 0 1 or 3 sort operators,
project from a projection from a projection or not, does it still work?

Though what to test? A DAL has unlimited use-cases, writing a test for
every one of them is undoable. So you've to look at the design of your
DAL. Reason about where things might go wrong and write tests for THAT.

FB

--
------------------------------------------------------------------------
Lead developer of LLBLGen Pro, the productive O/R mapper for .NET
LLBLGen Pro website: http://www.llblgen.com
My .NET blog: http://weblogs.asp.net/fbouma
Microsoft MVP (C#)
------------------------------------------------------------------------

 

[mgsram]

You can do that, you can also use separate databases for separate
groups of tests. We for example use separate databases for fetch
testing our o/r layer and saving data and another database for
combinations of the two. This way you don't lose a lot of time when
databases have to be cleaned up nor do you have to pre-define data to
insert, as that's already there (for the fetch database for example).

However, the biggest part of testing your DAL isn't about TDD. It's
about testing the expected edge cases of your design. I can't stress
enough how dumb it is to design a DAL using TDD, unless you want a dal
with a gazillion methods scattered all over the place and no uniform
way of data flow, graph management etc.

For example having a graph of customer, order, order details, product,
employee objects, how is that persisted? In what order? How do you
calculate that order? Are newly db-generated pk values synced at once,
or delayed? if an entity isn't 'dirty' (changed) but has an association
with an entity which is new and which will get a PK set in the DB when
it is saved, is it expected that this non-dirty entity is saved as
well? (yes). When will that fail?

You won't get to those problems when using TDD to access your data, as
you will program out what you need for the features USING the DAL.
That's ok, if your DAL is very small and your database has perhaps 3
tables, but if your database has 200 tables, you're not going to
succeed at all when you start with TDD and program your way down to the
database, simply because you soon enter a complex area where things
have to be solved in a generic way, but when you want to do so, you've
to abandone TDD altogether (read: you're forced to think up-front and
make design decisions up front, oh my) to get things setup generically.

That's not to say TDD doesn't work in this area. You can perfectly
fine design your API accessing the DAL, i.e. your query interface,
using TDD, however don't fall for the assumption that when you write,
say, 1000 tests, your DAL works nor think that by writing 1000 tests
and the code in the DAL necessary to run the tests, your DAL is
finished and suitable for your application.

TDD people bash the crap out of me when I tell stories like the one
above. It's up to you what to believe, but I can assure you: I know
what it takes to write a big feature rich data access framework and
it's not doable with TDD.

That's not to say unittests suck, on the contrary, however as
automated tests for the integration tests: does our code also work on
oracle? When I specify 2, 3, 4, predicates, 0 1 or 3 sort operators,
project from a projection from a projection or not, does it still work?

Though what to test? A DAL has unlimited use-cases, writing a test for
every one of them is undoable. So you've to look at the design of your
DAL. Reason about where things might go wrong and write tests for THAT.

FB

--
------------------------------------------------------------------------
Lead developer of LLBLGen Pro, the productive O/R mapper for .NET
LLBLGen Pro website:http://www.llblgen.com
My .NET blog:http://weblogs.asp.net/fbouma
Microsoft MVP (C#)
------------------------------------------------------------------------

I would encourage posting this on the yahoo groups for TDD. You will
certainly get better answers. IMO, once again what Frank is narrating
is Integration problems

-Sriram

 

[Cramer]

I would encourage posting this on the yahoo groups for TDD. You will
certainly get better answers. IMO, once again what Frank is narrating
is Integration problems

-Sriram

Whether he is narrating integration problems or not, I think it's utterly
naive to drive a database design from the point of view of the code that
will use the database - which is what you advocated elsewhere. I have been
in the industry for a very long time and have spent a lot of time cleaning
up databases, many times doing complete rewrites, after the initial
developers before me failed to make the database model the real world, and
instead designed the database to model their current application needs.
Applying TDD to the design of a database itself, by definition, means you
are modeling your application's needs and not the real world of the
business. That's a huge mistake that many systems never recover from (and if
the system is important enough, will eventually necessitate a complete
redesign and reimplementation of the database according to a new data model
that captures the real-world of the business).

As I stated in my OP here, I'm absolutely fascinated by TDD and now consider
myself to be a True Believer after having been a sceptic for a long time.
But trying to apply TDD to develop a DAL seems particularly troublesome -
especially if we are to follow the TDD dogma carefully (i.e., have our unit
tests atomic, completely self-contained, etc).

The benefits of TDD seem readily apparent - to me anyway - when applied to
things like developing an application framework or parsing engine, or
designing some new algorithm for a particular business scenario. But when it
comes to designing a DAL... or ASP.NET Web application, where a lot of code
MUST interact with external systems or be dependent on things like ASP.NET
Application state, Session state, or the HTTP request pipeline (which as of
IIS 7 is now fully integrated with the IIS request pipeline), then we're
forced to make our unit tests (if we can even make them true unit tests)
depend on external things like the HTTP request pipeline or an external
database. I know, you'll say that we "just" mock those things...

I understand mocks. But at what point are we dedicating our development time
to developing complex mocks? Let me see you mock the HTTP Request pipeline
and unit test some code that overrides ASP.NET's Application (Global) .Init
method. If your targeting IIS7 and the request pipeline (say your code
programmatically wires up HTTP modules to handle the various pipeline
events), then your mock would have to emulate IIS7 AND ASP.NET application
startup. That's one enormous mock. At what point does it lose it's value
(especially if you have to make it emulate significant functionality in a
complex integrated system, like the ASP.NET/IIS7 pipeline).

In short, I don't think that TDD is equally valuable for the development of
all types of application components. My OP here was posted so that I could
find out other's opinions on the "TDD-ability" of a DAL.

Given the responses here, and silence from some of this group's more
talented developers (Skeet, Duniho, etc), coupled with the strongest
advocate's (for TDD-ing a dal) promotion of even designing a data model
based on how the application will interact with the database (thereby losing
a bunch of credibity) - I'm concluding that it's (1) difficult or impossible
to go the TDD route in earnest with a DAL, unless (2) we mock the he!! out
of the database and therefore dedicate absolutely huge amounts of effort the
developing mocks. That's not worth it, IMHO.

-Cramer

 

[mgsram]

Whether he is narrating integration problems or not, I think it's utterly
naive to drive a database design from the point of view of the code that
will use the database - which is what you advocated elsewhere. I have been
in the industry for a very long time and have spent a lot of time cleaning
up databases, many times doing complete rewrites, after the initial
developers before me failed to make the database model the real world, and
instead designed the database to model their current application needs.
Applying TDD to the design of a database itself, by definition, means you
are modeling your application's needs and not the real world of the
business. That's a huge mistake that many systems never recover from (and if
the system is important enough, will eventually necessitate a complete
redesign and reimplementation of the database according to a new data model
that captures the real-world of the business).

As I stated in my OP here, I'm absolutely fascinated by TDD and now consider
myself to be a True Believer after having been a sceptic for a long time.
But trying to apply TDD to develop a DAL seems particularly troublesome -
especially if we are to follow the TDD dogma carefully (i.e., have our unit
tests atomic, completely self-contained, etc).

The benefits of TDD seem readily apparent - to me anyway - when applied to
things like developing an application framework or parsing engine, or
designing some new algorithm for a particular business scenario. But when it
comes to designing a DAL... or ASP.NET Web application, where a lot of code
MUST interact with external systems or be dependent on things like ASP.NET
Application state, Session state, or the HTTP request pipeline (which as of
IIS 7 is now fully integrated with the IIS request pipeline), then we're
forced to make our unit tests (if we can even make them true unit tests)
depend on external things like the HTTP request pipeline or an external
database. I know, you'll say that we "just" mock those things...

I understand mocks. But at what point are we dedicating our development time
to developing complex mocks? Let me see you mock the HTTP Request pipeline
and unit test some code that overrides ASP.NET's Application (Global) .Init
method. If your targeting IIS7 and the request pipeline (say your code
programmatically wires up HTTP modules to handle the various pipeline
events), then your mock would have to emulate IIS7 AND ASP.NET application
startup. That's one enormous mock. At what point does it lose it's value
(especially if you have to make it emulate significant functionality in a
complex integrated system, like the ASP.NET/IIS7 pipeline).

In short, I don't think that TDD is equally valuable for the development of
all types of application components. My OP here was posted so that I could
find out other's opinions on the "TDD-ability" of a DAL.

Given the responses here, and silence from some of this group's more
talented developers (Skeet, Duniho, etc), coupled with the strongest
advocate's (for TDD-ing a dal) promotion of even designing a data model
based on how the application will interact with the database (thereby losing
a bunch of credibity) - I'm concluding that it's (1) difficult or impossible
to go the TDD route in earnest with a DAL, unless (2) we mock the he!! out
of the database and therefore dedicate absolutely huge amounts of effort the
developing mocks. That's not worth it, IMHO.

-Cramer

Cramer,
Do you have a problem statement that I can use to verify what you are
saying? Of course this is for my benefit and I will be glad to share
my findings with you.

-Sriram

 

[Jon Skeet [C# MVP]]

Given the responses here, and silence from some of this group's more
talented developers (Skeet, Duniho, etc)

In general, don't draw any conclusions from me being silent on a topic.
I don't have as much time for checking the newsgroup as I used to, and
to be honest I hadn't even seen this thread until just now.

I have friends who certainly *have* mocked out their data access. I've
found it too difficult to do in the past, but they're more skillful at
TDD than I am.

 

[Arne Vajhøj]

You could do the unit test of the DAL through the interfaces of the
Business layer that interacts with the DAL.

I don't think the BLL developers will appreciate having
to guess whether their unit test failures are due to their
bugs or due to bugs in the DAL somebody else delivered
without unit testing.

Arne

 

[Arne Vajhøj]

I've finally gotton board with TDD (test driven development) and wow is it
effective! I went from sceptic to True Believer with my first effort.

My question: According to the various books and articles I have read about
TDD, a good unit test does not rely on the database or other such
external/environmental conditions. More generally, a good unit test is
atomic and makes as few assumptions about its runtime environment as
possible. But what about developing unit tests for a DAL - whose purpose in
life is to interact with a database?

If you were to be very strict about unit test then you need a mock
ADO.NET provider.

But I would suggest being a bit pragmatic and test DAL against a
real database.

Arne

 

[Arne Vajhøj]

'Designing' a dal using TDD is silly anyway, as a DAL has an
unlimited amount of usage scenario's so you can't design it by starting
with tests, you can only design it in a more generic way and use
unittests for integration tests and automated checks for expected edge
cases in the generic design.

I don't think DAL is more generic than any other layer except
presentation layers. Other layers are supposed to be reusable too.

And the difference between "TDD for DAL" and "unit tests for
generic design of DAL" sound more as a difference in terminology
than a real difference in process.

Arne

 

[Arne Vajhøj]

Whether he is narrating integration problems or not, I think it's utterly
naive to drive a database design from the point of view of the code that
will use the database - which is what you advocated elsewhere. I have been
in the industry for a very long time and have spent a lot of time cleaning
up databases, many times doing complete rewrites, after the initial
developers before me failed to make the database model the real world, and
instead designed the database to model their current application needs.
Applying TDD to the design of a database itself, by definition, means you
are modeling your application's needs and not the real world of the
business. That's a huge mistake that many systems never recover from (and if
the system is important enough, will eventually necessitate a complete
redesign and reimplementation of the database according to a new data model
that captures the real-world of the business).

If the scope for the DL and DAL is to be reusable, then TDD will also
work for them.

I know that TDD purists will argue KISS and YAGNI, but most development
methodologies work best with a bit of pragmatism.

As I stated in my OP here, I'm absolutely fascinated by TDD and now consider
myself to be a True Believer after having been a sceptic for a long time.
But trying to apply TDD to develop a DAL seems particularly troublesome -
especially if we are to follow the TDD dogma carefully (i.e., have our unit
tests atomic, completely self-contained, etc).

The benefits of TDD seem readily apparent - to me anyway - when applied to
things like developing an application framework or parsing engine, or
designing some new algorithm for a particular business scenario. But when it
comes to designing a DAL... or ASP.NET Web application, where a lot of code
MUST interact with external systems or be dependent on things like ASP.NET
Application state, Session state, or the HTTP request pipeline (which as of
IIS 7 is now fully integrated with the IIS request pipeline), then we're
forced to make our unit tests (if we can even make them true unit tests)
depend on external things like the HTTP request pipeline or an external
database. I know, you'll say that we "just" mock those things...

I understand mocks. But at what point are we dedicating our development time
to developing complex mocks? Let me see you mock the HTTP Request pipeline
and unit test some code that overrides ASP.NET's Application (Global) .Init
method. If your targeting IIS7 and the request pipeline (say your code
programmatically wires up HTTP modules to handle the various pipeline
events), then your mock would have to emulate IIS7 AND ASP.NET application
startup. That's one enormous mock. At what point does it lose it's value
(especially if you have to make it emulate significant functionality in a
complex integrated system, like the ASP.NET/IIS7 pipeline).

In short, I don't think that TDD is equally valuable for the development of
all types of application components. My OP here was posted so that I could
find out other's opinions on the "TDD-ability" of a DAL.

Given the responses here, and silence from some of this group's more
talented developers (Skeet, Duniho, etc), coupled with the strongest
advocate's (for TDD-ing a dal) promotion of even designing a data model
based on how the application will interact with the database (thereby losing
a bunch of credibity) - I'm concluding that it's (1) difficult or impossible
to go the TDD route in earnest with a DAL, unless (2) we mock the he!! out
of the database and therefore dedicate absolutely huge amounts of effort the
developing mocks. That's not worth it, IMHO.

It is not surprising that a methodology need to be customized to fit
with a given development context.

Arne