Editorials

Implement SQL Mocks Using a Schema

Today I am demonstrating one way of using the SCHEMA capability of an SQL Database to help automate unit testing. When doing unit testing you usually want the input to be static so that you can assert what the valid accurate output should be. If the input is constantly changing, then your testing can only validate if results were returned rather than all of the results returned were exactly what was required.

One way you can implement this on any database is to create a schema for unit testing with tables having the same data structure as that found in the dbo schema, but data that is static. I like to create a schema called MOCK for this purpose because the name clearly tells me that the data contained in these tables is not for production use. Moreover, during production deployment, I can drop all of the mock tables if necessary.

Today I am going to demonstrate two Users tables. They are identical in design. The difference is one is owned by the schema MOCK and the other is owned by the schema dbo. As far as the database is concerned, these are two completely different tables. Fully qualified they are dbo.Users and MOCK.Users. For unit testing we are going to take advantage of the ability to set a default schema for a user, so that when we reference the Users table without fully qualifying, my test user will resolve to MOCK.Users, and my other users resolve to dbo.Users.

First let’s create a database, the MOCK schema, and the two Users tables.

CREATE Database Demo
GO

USE [Demo]
GO

CREATE SCHEMA MOCK
GO

CREATE TABLE MOCK.Users (
   UserId INT NOT NULL
  ,FirstName VARCHAR(32) NOT NULL
  ,LastName VARCHAR(32) NOT NULL)
GO

INSERT INTO MOCK.Users (UserId, FirstName, LastName) VALUES
 (1, 'Paul', 'Bunion')
,(2, 'Benjamin', 'Franklin')
,(3, 'Mortimer', 'Schnerd')

GO
CREATE TABLE dbo.Users (
   UserId INT NOT NULL
  ,FirstName VARCHAR(32) NOT NULL
  ,LastName VARCHAR(32) NOT NULL)

GO
INSERT INTO dbo.Users (UserId, FirstName, LastName) VALUES
(1, 'George', 'Washington')
,(2, 'Thomas', 'Jefferson')
,(3, 'James', 'Madison')
,(4, 'James', 'Monroe')
,(6, 'John Quincy', 'Adams')
,(5, 'John', 'Adams')
GO

Now I’m going to create a new database user UnitTester. This is a SQL Server user, not a windows user…it really doesn’t matter as long as I can create a security object for the database.

USE [Master]
GO

CREATE LOGIN [UnitTester] WITH PASSWORD=N'ImAUnitTester', DEFAULT_DATABASE=[master], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
GO

Now that I have a valid SQL Server login, I create a Database User for my Demo database. I don’t grant any permissions to UnitTester so that they have to be explicitly granted. I also set the default schema for UnitTester to MOCK. This means that when SQL Server resolves the fully qualified name for a query when one is not provided it will first attempt to find the object in the MOCK schema.

USE [Demo]
GO

CREATE USER [UnitTester] FOR LOGIN [UnitTester]
GO

ALTER USER [UnitTester] WITH DEFAULT_SCHEMA = [MOCK]
GO

GRANT SELECT ON MOCK.Users to UnitTester
GO

Let’s test our implementation. First I simply run a query with myself as the user. I run the query SELECT * FROM Users. This query resolves Users as dbo.Users because my default SCHEMA is dbo.

SELECT * FROM Users

Results From My Login Default to DBO.Users
UserIdFirstNamLastName
1GeorgeWashington
2ThomasJefferson
3JamesMadison
4JamesMonroe
6John QuincyAdams
5JohnAdams

In order to test UnitTester, because I have full permissions I can emulate the UnitTester user and see how things work.

The command SETUSER ‘UnitTester’ changes my credentials temporarily to be that of UnitTester. Now when I run the query SELECT * FROM Users, the table Users is resolved to the default schema of UnitTester which is MOCK.Users.

SETUSER 'UnitTester'

SELECT * FROM Users

SETUSER

Results From UnitTester Default Schema MOCK.Users
UserIdFirstNameLastName
1PaulBunion
2BenjaminFranklin
3MortimerSchnerd

While SCHEMAS were not created for unit testing, the behavior provided make it possible to implement Unit Tests without having to make major adjustments to your code.

Cheers,

Ben

Facebooktwittergoogle_plusredditpinterestlinkedinmail
  • Rick Foster

    I tend to stay far away from allowing testing on production boxes, even though the production tables can be protected, resources cannot be protected and a runaway query drags down production. I don’t even allow a test database next to the production database. Now a SQL instance on a production box can be throttled however nothing beats testing on a separate test server with a database copy using the same schema/table names. Only the server name has to be changed in scripts/ODBC and you can test as the user. Just my two cents, okay call it three 😉

    • Ben

      This editorial points back to the one from the previous day.

      The point of a Unit test is not to use production data. You want static data so that your data based assertions do not change because the underlying data has changed.

      You can do this in a number of ways, one of which I will be writing today…database migrations.

      What I am demonstrating in this case is the ability in a test environment to have two separate tables working identically, the instance chosen by the credentials of the client. If the client is your development service, it uses the dbo version. If the client is your automated unit test framework, it uses the MOCK version.

      This method allows you to write automated unit tests against things such as stored procedures while separating unit test and application data, actually exercising the exact same stored procedure code. This proves that your stored procedure works correctly without having to run a system test, and validate the results manually.

      I agree that you wouldn’t want to have the mock data in your production database. Since I don’t deploy to production through anything other than database migration tools, I can exclude mock objects from my production database migrations.

      Thanks for your helpful comments. I had some assumptions in my demonstration that were not clear.

      Ben

    • Mark Anderson

      we have separate dev instances, but changing instances doesn;t solve the problem of static data.. I think Ben’s suggestion is a good one

    • This doesn’t have anything to do with production. There are different types of tests, some that are best executed as automated, repeatable and verifiable. These require static data environments.

  • Mark Anderson

    I think this is agood method Ben, for the reason you describe: we need static data for unit testing and we don’t want to change code. Our dev data is continually refreshed from production, so we need static data. Gonna give this a try