How do you test CRUD methods? Just hit the database and be happy.

It’s time for a little programmer heresy.  For the last few months I’ve been reevaluating all of my “best practices” and throwing out anything that creates friction.  Friction is a term I’ve heard Jeff Atwood use fairly often.  It’s a fuzzy term but I think I understand what he’s getting at.  When you’re writing code and you’ve got some momentum going, any time you find yourself struggling against the persistence framework, any time you find yourself jumping through hoops to find that bit of code that’s throwing an error (and is probably called through reflection), any code that makes you scream inside but you have to write it to comply with some standard, pretty much anything that slows you down, that is friction.  Sometimes friction is justified. Most of the time it isn’t.  

Background

So I’ve been doing more and more unit testing lately.  One thing that I run up against often is how to test persistence (Create Read Update Delete) methods.  Below is a diagram of some persistence classes from an application I’m working on.  The classes below handle persistence for my SubmittedBlogUrl entity.  BlogService is the one stop shop that my application layer uses to do everything related to blogs. In Domain Driven Design parlance, the BlogService handles getting and saving data for all classes that are part of the Blog aggregate.  Getting and saving BlogProfiles, BlogPosts, and SubmittedBlogUrls all takes place through the BlogService class.  That doesn’t mean that BlogService contains all of the persistence logic though.  I use a strategy pattern that delegates the actual Delete, Insert, Save, and Update methods to separate persister classes. 

Now the code I want to test are the Delete, Insert, and Update methods in my SubmittedBlogUrlPersister class.  The methods look like this.

public class SubmittedBlogUrlPersister

    {

 

        public void Save(SubmittedBlogUrl dto)

        {

            if (dto.Id == NullValues.NullInt)

            {

                Insert(dto);

            }

            else

            {

                Update(dto);

            }

        }

 

        public void Insert(SubmittedBlogUrl dto)

        {

            SqlRepository repository = new SqlRepository();

            string sql = @"INSERT INTO [dbo].[SubmittedBlogUrl]

                                ([SubmittedByUserGuid]

                                ,[SubmittedOnUtc]

                                ,[IpAddress]

                                ,[Status]

                                ,[BlogUrl])

                            VALUES

                                (@SubmittedByUserGuid

                                ,@SubmittedOnUtc

                                ,@IpAddress

                                ,@Status

                                ,@BlogUrl)

                            SELECT SCOPE_IDENTITY()";

 

            SqlCommand command = repository.GetSqlCommand(sql);

            command.Parameters.Add(repository.CreateParameter("@SubmittedByUserGuid",
              dto.SubmittedByUserGuid));

            command.Parameters.Add(repository.CreateParameter("@SubmittedOnUtc", 
              dto.SubmittedOnUtc));

            command.Parameters.Add(repository.CreateParameter("@IpAddress", dto.IpAddress, 20));

            command.Parameters.Add(repository.CreateParameter("@Status", dto.Status));

            command.Parameters.Add(repository.CreateParameter("@BlogUrl", dto.BlogUrl, 100));

            Object result = repository.ExecuteScalar(command);

            dto.Id = Convert.ToInt32(result);

        }

 

 

        public void Update(SubmittedBlogUrl dto)

        {

            SqlRepository repository = new SqlRepository();

            string sql = @"UPDATE [dbo].[SubmittedBlogUrl]

                           SET [SubmittedByUserGuid] = @SubmittedByUserGuid

                              ,[SubmittedOnUtc] = @SubmittedOnUtc

                              ,[IpAddress] = @IpAddress

                              ,[Status] = @Status

                              ,[BlogUrl] = @BlogUrl

                         WHERE Id = @Id";

 

            SqlCommand command = repository.GetSqlCommand(sql);

            command.Parameters.Add(repository.CreateParameter("@Id", dto.Id));

            command.Parameters.Add(repository.CreateParameter("@SubmittedByUserGuid", 
              dto.SubmittedByUserGuid));

            command.Parameters.Add(repository.CreateParameter("@SubmittedOnUtc",
              dto.SubmittedOnUtc));

            command.Parameters.Add(repository.CreateParameter("@IpAddress", dto.IpAddress, 20));

            command.Parameters.Add(repository.CreateParameter("@Status", dto.Status));

            command.Parameters.Add(repository.CreateParameter("@BlogUrl", dto.BlogUrl, 100));

            repository.ExecuteNonQuery(command);

        }

 

 

        public void Delete(SubmittedBlogUrl dto)

        {

            SqlRepository repository = new SqlRepository();

            string sql = @"DELETE FROM [dbo].[SubmittedBlogUrl]

                           WHERE Id = @Id ";

 

            SqlCommand command = repository.GetSqlCommand(sql);

            command.Parameters.Add(repository.CreateParameter("@Id", dto.Id));

            repository.ExecuteNonQuery(command);

        }

 

    }

As you can see, these methods just create some parameterized T-SQL, package it in a command, and then pass the command off to my repository to be executed. The key thing that I need to test here is the query logic.  I need to make sure I didn’t make any mistakes when writing the SQL and creating parameters, and I need to make sure that the SQL works when run against my database.

Getting to the Tests

So how should I test this?  The TDD purists might say that I should fire up a mocking framework, create some mock objects of my repository and make sure that all right things are passed in.  I also might need to look at a Dependency Injection framework to make it easier to switch out my real SqlRepository with the mock SqlRepository.  And at the end of all this coding I’ll know what???  Well, I’ll just know that I passed a command to a mock object without anything blowing up.  I won’t know if my SQL syntax is right, I won’t know if my SQL works with my database, I won’t know anything that I actually need to know. I call this friction. Lots of effort that at the end of the day doesn’t even get me what I need, a valid test of the SQL in my persister class.

My solution, let’s dumb down the tests and get on with coding.  I’ll create a single CRUD test that instantiates a real SubmittedBlogUrlPersister (not a mock) then creates a new SubmittedBlogUrl object, saves it to the database, updates it, and finally deletes it.  If I make sure that I’m using a dev database (not production) and I make sure that my test cleans up after itself (deletes the data it creates) this should work just fine.  The resulting test looks like this.

[TestClass()]
public class SubmittedBlogUrlPersisterTest

{

    [TestMethod()]

    public void CrudTest()

    {

        // create our test object

        var persister = new SubmittedBlogUrlPersister();

        var service = new BlogService();

        var dto = new SubmittedBlogUrl();

        SubmittedBlogUrl dto2;

        var utcNow = DateTime.UtcNow;

        dto.BlogUrl = "testUrl";

        dto.IpAddress = "testIp";

        dto.SubmittedByUserGuid = Guid.NewGuid();

        dto.SubmittedOnUtc = utcNow;

        // insert it

        persister.Insert(dto);

        // get it

        dto2 = service.GetSubmittedBlogUrl(dto.Id);

        Assert.AreEqual(dto.Id, dto2.Id);

        // update it

        dto2.BlogUrl = "NewUrl";

        persister.Save(dto2);

        dto2 = service.GetSubmittedBlogUrl(dto.Id);

        Assert.AreEqual(dto2.BlogUrl, "NewUrl");

        // delete it

        persister.Delete(dto2);

        dto2 = service.GetSubmittedBlogUrl(dto.Id);

        Assert.IsNull(dto2);

    }

}

Technically, this isn’t a unit test, it’s an integration test since it’s hitting a real database, but I think it’s the right approach for this situation.  The principle here is that when you reach the point where you’re writing tests for methods that contain query logic (like CRUD methods), that might be the right place to ditch the unit tests and switch over to some simple integration tests that write to a real database and test what you really need tested, your query logic.

Addendum

Great comment that I wanted to draw attention to from Steve Freeman who co-authored the book Growing Object-Oriented Software, Guided by Tests.  Steve said..

As a TDD purist who "wrote the book" on Mock Objects and once wrote a foolish paper on mocking out JDBC, I would say that testing against a real database is the right thing to do here (provided you've set up your environment to do this cleanly). The other right thing is to only need such tests at the boundaries of your application, the persistence code should be nicely contained and not leak into the domain code.

That makes a lot of sense to me.  I might be off a bit on what I expect to hear from TDD purists.  I like Steve’s statement that you need integration tests like this only at the boundaries of your application.  In my example, if I’m testing app logic in an MVC controller class that uses my BlogService for persistence, then it makes sense to mock my BlogService and write real unit tests because I really just want to test the logic in my controller.  But at the edge of the application, where I’m touching the database, it’s probably more appropriate to switch over to integration tests.  I think a good rule of thumb is that if the code you’re testing contains any query logic (SQL, LINQ, etc..), go ahead and hit the db.