Dependency Injection pattern in database development

24 Jun

To get yourself familiar with dependency injection
pattern please check Wikipedia link here:
http://en.wikipedia.org/wiki/Dependency_injection

The primary database focus is to persist data and guaranty data integrity… well, at list we can say it about many databases. As a result database development evolved in its own procedural-way, and it is hard to argue against such a way or to change it, because in most cased database designs have tendency to harden, solidify, and resist a change after they get built. Besides, who want a change when over the years masses of applications got built on that-old-table. Just try to change it and wait for something to fail…
Whatever reason you have for a change try the Dependency Injection pattern. This pattern injects the “depended-on” code to the destination code automatically by knowing the requirement of the destination code and altering an expected outcome of the destination code. Let’s take very popular BOO and FOO templates for our example:

CREATE PROCEDURE dbo.sp_BOO_manager
AS
BEGIN
   IF dbo.FOO() < dbo.FOOTheshold()
      EXEC BOO 'Have to do BOO logic';
      ELSE MOO 'Have to do MOO logic, instead';
END

Here is how to introduce dependency injection into our code:

  • Create following two schemas: INTERFACE, PROD_IMPLEMENTATION
CREATE SCHEMA INTERFACE;
CREATE SCHEMA PROD_IMPLEMENTATION;
  • Move actual functions and procedures with business logic to the PROD_IMPLEMENTATION schema, like this one:
CREATE PROCEDURE PROD_IMPLEMENTATION.BOO
@Message VARCHAR(50)
AS
BEGIN
    PRINT 'Very complicated proprietary logic';
END
  • Create synonyms in the INTERFACE schema that will point to the functions and stored procedures in the PROD_IMPLEMENTATION schema. Note that synonyms can have the same names, because they are located in the different schemas than actual business logic. For instance:
CREATE SYNONYM INTERFACE.BOO
   FOR PROD_IMPLEMENTATION.BOO;
CREATE SYNONYM INTERFACE.FOO
   FOR PROD_IMPLEMENTATION.FOO;
CREATE SYNONYM INTERFACE.FOOTheshold
   FOR PROD_IMPLEMENTATION.FOOTheshold;
  • Then change dbo.sp_BOO_manager stored procedure to use synonyms from the INTERFACE schema instead of objects themselves. Here is what you will get:
CREATE PROCEDURE dbo.sp_BOO_manage
AS
BEGIN
   IF INTERFACE.FOO() < INTERFACE.FOOTheshold()
      EXEC INTERFACE.BOO 'Have to do BOO logic';
      ELSE INTERFACE.MOO 'Have to do MOO logic';
END

Now our sample code does not bounds to the actual business logic directly, instead it calls an abstracted logic through an interface (which we can override when needed), allowing the calling process to inject desired functionality.
Let’s do it…
Make a new object somewhere in another database:

CREATE PROCEDURE SOMEOTHERDB.MOCK_OBJECTS.BOO
@Message VARCHAR(50)
AS
BEGIN
   DECLARE @OverrideName VARCHAR(100)
     = 'SOMEOTHERDB.MOCK_OBJECTS.BOO'
   PRINT 'Very simple log message';
   INSERT INTO TEST_MESSAGES
      (OverrideName, [Message], EventDate)
      VALUES (@OverrideName,@TmpMessage, GETDATE());
END

Then (on your development server) change BOO synonym to point to the mocked object:

DROP SYNONYM INTERFACE.BOO;
CREATE SYNONYM INTERFACE.BOO
   FOR SOMEOTHERDB.MOCK_OBJECTS.BOO;

After this change, you can call business logic and verify that expected message got logged in to a test table.

--call logic
EXEC dbo.sp_BOO_manager;
--assert result
EXEC DBTD_ASSERT_COLUMN_HAVE_VALUE
   @v_TableName = 'TEST_MESSAGES',
   @v_ColumnName = 'OverrideName',
   @v_Value = 'SOMEOTHERDB.MOCK_OBJECTS.BOO',
   @v_ValueDataType = 'VARCHAR(100)',
   @v_UserMessage = 'BOO functionality should run';

There are a few ways to introduce dependency injection pattern in databases, we have chosen SYNONYMS, because of the following benefit:

  • Synonyms are very simple database objects;
  • Synonym does not change actual business logic they represents;
  • Synonym provides less invasive way to perform injection;
  • When creating synonym developer only need the name of the synonym (already known from consumer side) and the name of the object it represents (which is also known by developer from a provider side );
  • Locating synonyms and business logic in the different schemas allows synonym to have the same name as the object it abstracts, this preserve an existing naming convention used by development team;
  • Synonym can be created on the following objects:
    CLR Stored Procedures, Table-valued Functions, Scalar Functions, and Aggregate Functions, SQL Stored Procedure, Replication-filter-procedures, Extended Stored Procedures, SQL Scalar Functions, SQL Table-valued Functions, SQL Inline-table-valued Function, Views, User-defined Tables, includes local and global temporary tables

Well, here you go, now you have one more tool in your development toolbox. Use injection wisely, make sure you have basic infrastructure tests to go with it, and don’t forget about security.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: