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


UserId
FirstNam
LastName




1
George
Washington


2
Thomas
Jefferson


3
James
Madison


4
James
Monroe


6
John Quincy
Adams


5
John
Adams



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


UserId
FirstName
LastName




1
Paul
Bunion


2
Benjamin
Franklin


3
Mortimer
Schnerd



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