Posts tagged SQL

Why Unit Test without Mocking your EF Repository

Every once and a while I get a fair amount of flak about how I unit test my services. I’m a firm believer in the Onion Architecture and Repository Patterns and the techniques developed at Online Advisors for implementing those patterns are still better then any other implementation I’ve seen.

Y87zIwvThe repository itself is generic and thin. You can see an implementation on this GitHub page. With the underlying data store implementation hidden behind a generic interface that makes it very easy to mock the calls to the database, but should you?

Any Unit Testing purist will tell you that Unit Testing is only testing one small, isolated, functional unit of code. When that unit of code go out of scope it needs to be mocked. This is all well and good, and I do a fair amount of testing like this. But then Entity Framework Code First and automatic migrations came along, coupled with SQLCE or LocalDB why mock your repository?

When my repository method is generic (i.e. a Get, Save, Delete) there is no real logic in there. But when I test it I gain the added benefit of verify that data going to the database and back is correct. Additionally using the Seed method of Entity Framework it’s very easy to scaffold in test data that can be utilized by the test.

Anyone who’s mocked complex data structures knows your basically writing a lot of that by hand, or storing it in a file and deserializing it to be served up to tests. If your going though that trouble, then why not just use EF, Code First, Automatic Migrations and Seeding? It really is a time saving once you got the data in the seed statement.

Additionally it’s helped me find concurrency and data access issues. Because of how unit tests are executed using a lightweight SQL instance I was trace down race conditions and slow LINQ statements. None of which would have been possible by just testing the service or logic layer in isolation.

When I test this way I still call it unit testing. The way it gets the data, either through an In-Memory array, Mock or LocalDB is not what’s being tested. As long as my repository method itself has no code or logic it will be side effect free.

This isn’t without it’s faults and it’s not perfect. Based on their very nature SQLCE and LocalDB are very lightweight version of SQL Server. This means they can’t perform the way normal SQL does, operate in the same way or even use some of the same data types. For example SQLCE doesn’t support the Geo data type.

So it may not be perfect for every scenario or database. But if it’s an option, will be side effect free and won’t cause interdependency issues between tests then really is the difference between that and Mocking? Besides the fact that your exercising more of your code base?

Resgrid is a SaaS product utilizing Microsoft Azure, providing logistics, management and communication tools to first responder organizations like volunteer fire departments, career fire departments, EMS, search and rescue, CERT, public safety, disaster relief organizations, etc. It was founded in late 2012 by myself and Jason Jarrett (staxmanade).

Azure SQL Server V12 Preview

Recently I’ve been utilizing the new V12 Azure SQL Server preview for Resgrid. So far the performance is good and for a preview it’s been pretty smooth, compared to other Azure SQL Preview’s I’ve done. The reason for moving to the V12 Preview was because of an issue I had migrating to an S2 database, instead of S2 I moved straight to S3 as it’s preview price point was the same.

sql-azureBut it hasn’t been all sunshine. unicorns and rainbows. Overall the experience for a preview server has been good there wasn’t any listed issues that would help someone make an informed decision.

The biggest area I’ve run into issues has been with backing up and restoring from the V12 Preview server. Normal operations that can fail in strange ways and cause you to loose some time trying to figure them out.

Import/Export Data Tier Application Is Broken (DACPAC’s)

Ever since moving to SQL Azure I’ve utilized the DACPAC’s (Data Tier Application Import/Export) to backup and restore the SQL database for local testing and off-cloud recovery With the V12 SQL Preview this operation doesn’t work, or only works in some cases. I’ve tried it with Developer editions of SQL Server 2012 and 2014 without luck.

The error importing the data tier application was:

“The specified schema name “sys” either does not exist or your do not have permission to use it.”

Importing the database from a non v12 preview server and it works fine. Sys does exist and being an sa I do have rights to use it.

Exporting/Restore in Azure Doesn’t work

It’s not just local DACPAC’s that don’t work. Utilizing exporting and new database creation utilizing a dacpac in blob storage doesn’t work either. This was the primary way I would create the QA\Test\Staging database for testing. I’ve tried this against V12 and non-V12 servers and get the same issue.

It’s possible that the server I upgraded to the V12 preview is having issues. But the copy database between 2 V12 server’s works well. This is how I’ve been getting around the DACPAC issues.

Utilize Azure DB Copy to another v12 Server

Currently the way I’m working around the above issues is to copy the database function from within Azure. I’ve upgraded the QA/Test server to V12 Preview and utilized the built in SQL Database Copy to go from production to test. The benefit here is that the operation is super fast.

I’ve been told you can go from V12 to a non-V12 server, but this didn’t work for me.

Resgrid is a SaaS product utilizing Microsoft Azure, providing logistics, management and communication tools to first responder organizations like volunteer fire departments, career fire departments, EMS, search and rescue, CERT, public safety, disaster relief organizations, etc. It was founded in late 2012 by myself and Jason Jarrett (staxmanade).

UPDATE 3/5/2015: Check out this post on fixing the Import/Export issue.

Go to Top