How do I recover when tSQLt faketable does not reset table mapping?

Question

faketable function did not reassign to normal. All my tables I used faketable on now contain the content of the values I used in the insert of the unit test. It was many tables and it has left my database useless. Please help address this problem or at least its cause. This makes me very nervous about using this in our CI deployment process and maybe more importantly in our local development efforts.

Answer 1

It is possible one of your tests or your code, left the transaction in a state where it could not be rolled back. This would typically result in seeing one or more tests with an "Error" (instead of "Success" or "Failure") in the results.

In these cases, the FakeTable operation is not rolled back, and the tables are left in their faked state.

Under the covers, FakeTable renames the table and creates a new copy of it. When the rename happens, the operation is logged in the tSQLt.Private_RenamedObjectLog.

For example, you can use the following code to reproduce an error that tSQLt cannot gracefully rollback from:

EXEC tSQLt.NewTestClass 'SOF_Example'
GO

CREATE TABLE SOF_Example.MyTable (i INT);
GO

INSERT INTO SOF_Example.MyTable (i) VALUES (5);
GO

CREATE PROCEDURE SOF_Example.[test fake a table]
AS
BEGIN
    EXEC tSQLt.FakeTable 'SOF_Example.MyTable';

    INSERT INTO SOF_Example.MyTable (i) VALUES (12);

    COMMIT;
END;
GO

EXEC tSQLt.Run 'SOF_Example';

You can use this code to look into the renamed table log:

SELECT OriginalName, SCHEMA_NAME(schema_id) + '.' + name AS [Name of Renamed Table], create_date
FROM tSQLt.Private_RenamedObjectLog
JOIN sys.objects ON ObjectId = object_id;

If you've re-executed the tests many times, you may have many entries in the log for each faked table. You can use the create_date to help determine which one contains the original data.

Now, with all that said: It is best to not write and execute test cases in a database where you must preserve the data. The best approach is to use a database that contains no user data (only the essential configuration data at most). You should be developing and unit testing out of a blank database. Populated databases should be used for other forms of testing, such as integration, usability, performance, etc.