PowerShell is a very powerful language and framework. It’s not just for “advanced batch files”. When programming in PowerShell, you are encouraged to write modular code that can be combined in different ways. Your PowerShell code deserves the same quality testing that other languages provide.

If you look at most unit testing frameworks, they all provide a core set of features for writing test cases:

  • A way to describe correct behavior. Typically this is done through a set of assertions. A test script is written with the “3 As”: Arrange, Act, and Assert. A good assertion library makes the intent of the test easy to code and understand.
  • A way to run one or more tests. A test runner will locate and run tests, and report on the success or failure of the tests.
  • A way to mock parts of the system that are not part of the system under test.

Depending on the framework you choose, you may need to combine two or more frameworks to get all of the pieces that you need.

PSST contains three tools to let you write your unit tests for PowerShell:

  • PShould – a fluent assertion module
  • PSMock – a mocking module
  • PSate – a test runner module

Let’s see how we can start writing PowerShell tests with PSST. Assume we have a calculator class that we want to test:

function Add-Numbers($a, $b) {
   
    if ($a -eq $null) { throw '$a cannot be null' }
    if ($b -eq $null) { throw '$b cannot be null' }

    return $a + $b
}

The first thing you would do is to write some assertions. Let’s import PShould and write a few cases:

Import-Module PShould
Add-Numbers 1 2 | Should Be 3
Add-Numbers 0 0 | Should Be 0

PShould supports lots of tests like “Should Contain” “Should Exist” and “Should Throw”. Let’s check for some throws:

{ Add-Numbers $null 1 } | Should Throw "cannot be null"
{ Add-Numbers 1 } | Should Throw "cannot be null"

Great. Now we have some tests that check the behavior of our calculator. But what if our code does something more complicated, such as opening a file or accepting input? Let’s try that with a calculator that takes input:

function Get-Number {
    param ([int] $which)

    Write-Host "Enter number ${which}:"
    [int] $i = Read-Host
    $i
}

function Do-Calculator {
    return Add-Numbers (Get-Number 1) (Get-Number 2)
}

Do-Calculator

We want to test Do-Calculator, but it takes inputs from the user, so we can’t run an automated test case on it without doing some more work. Here we want to mock the Get-Number method so that we control the results. We will use PSMock to mock the method.

Import-Module PSMock
Enable-Mock | iex
Mock Get-Number { 1 }
Do-Calculator | Should Be 2

PSMock automatically creates a function that matches the one you are mocking, and lets you quickly override the function. It even lets you do conditional mocks:

Mock Get-Number { 1 } -when { $which -eq 1 }
Mock Get-Number { 2 } -when { $which -eq 2 }
Do-Calculator | Should Be 3

You could probably do this simply by writing your own functions, but PSMock lets you dynamically add and remove mocks, restoring the original methods. PSMock also lets you mock commandlets such as Get-ChildItem.

MockContext {
    Mock Get-ChildItem { "nothing" }
    Get-ChildItem c:\ | Should Be "nothing"
}

PSMock also provides a MockContext function, which automatically cleans up any outstanding mocks. This is very important if you are mocking any system commandlets (or you won’t be able to do a dir anymore!)

The next step is to organize our test cases with PSate:

Describing "Calculator" {
    Given "two numbers" {
        TestSetup {
            Mock Get-Number { 1 } -when { $which -eq 1 }
            Mock Get-Number { 2 } -when { $which -eq 2 }
        }

        It "adds them" {
            Do-Calculator | Should Be 3
        }
    }

    Given "a null number" {
        TestSetup {
            Mock Get-Number { $null } -when { $which -eq 1 }
            Mock Get-Number { 2 } -when { $which -eq 2 }
        }

        It "throws" {
            { Do-Calculator } | Should Throw
        }
    }
}

Here we have organized our tests. The Describe block tells us what we are testing. The Given block describes a test scenario, which the TestSetup sets up. The It block asserts our tests for us. PSate even gives us pretty outputs:

Describing Calculator
  Given two numbers
    [+] It adds them [3 ms]
  Given a null number
    [+] It throws [3 ms]

We can even integrate PSate into our automated build process with Jenkins, TFS, or other tools.

Get started with PSST by visiting the project pages for documentation and installation instructions:

    • PShould – a fluent assertion module
    • PSMock – a mocking module
    • PSate – a test runner module

When choosing a micro-ORM, the most important thing is that it matches your coding style and environment. That being said, I stumbled across an old (May 2012) set of benchmarks for Micro-ORMs. Since then, Insight.Database has had a major rewrite with performance optimizations, so I re-ran the benchmarks with the latest versions of the ORMs.

The participants were all pulled from NuGet directly:
- SqlFu 1.2.0
- Dapper.net with extensions 1.12
- PetaPoco 4.0.3
- Massive (whatever version was there before)
- InsightDatabase 2.1.1
- ServiceStack.OrmLite 3.9.35

Not in the running this time:

- FluentData – the benchmark code was incompatible with the latest version, and this was the slowest one last time, so I didn’t bother to try to make it work.

I updated the benchmark code to properly use Insight.Database. The code (on gist).

The results are below. The lessons you should take away are:

  1. If you care about performance, use any of the ones in the list.
  2. Tuning your performance-critical code works.

Executing scenario: FetchSingleEntity
———————————–
Dapper entity – 500 iterations executed in 42.4945 ms
PetaPoco entity – 500 iterations executed in 45.0368 ms
InsightDatabase – 500 iterations executed in 45.07 ms
SqlFu Get – 500 iterations executed in 45.116 ms
OrmLite – 500 iterations executed in 45.5678 ms
SqlFu FirstOrDefault – 500 iterations executed in 45.6268 ms

Massive doesn’t support the action. not explicit type support

Executing scenario: FetchSingleDynamicEntity
———————————–
Dapper dynamic – 500 iterations executed in 42.3505 ms
InsightDatabase – 500 iterations executed in 43.6308 ms
SqlFu dynamic – 500 iterations executed in 44.2722 ms
PetaPoco dynamic – 500 iterations executed in 46.931 ms
OrmLite – 500 iterations executed in 47.6205 ms
Massive – 500 iterations executed in 63.6032 ms
Executing scenario: QueryTop10
———————————–
InsightDatabase – 500 iterations executed in 51.2558 ms
SqlFu – 500 iterations executed in 51.2624 ms
Dapper  – 500 iterations executed in 51.7813 ms
PetaPoco – 500 iterations executed in 53.6952 ms
OrmLite – 500 iterations executed in 62.8887 ms

Massive doesn’t support the action. not explicit type support

Executing scenario: QueryTop10Dynamic
———————————–
OrmLite – 500 iterations executed in 52.4701 ms
Massive – 500 iterations executed in 54.7194 ms
Dapper  – 500 iterations executed in 55.1348 ms
InsightDatabase – 500 iterations executed in 57.8165 ms
SqlFu – 500 iterations executed in 61.1612 ms
PetaPoco dynamic – 500 iterations executed in 61.936 ms
Executing scenario: PagedQuery_Skip0_Take10
———————————–
InsightDatabase – 500 iterations executed in 47.7657 ms
Massive – 500 iterations executed in 54.899 ms
PetaPoco – 500 iterations executed in 99.3063 ms
SqlFu – 500 iterations executed in 100.4722 ms

Dapper  doesn’t support the action. No implicit pagination support
OrmLite doesn’t support the action. No implicit pagination support

Executing scenario: ExecuteScalar
———————————–
OrmLite – 500 iterations executed in 33.827 ms
InsightDatabase – 500 iterations executed in 36.847 ms
SqlFu scalar int – 500 iterations executed in 39.3225 ms
PetaPoco int – 500 iterations executed in 40.8184 ms
Dapper scalar int – 500 iterations executed in 41.1621 ms
Massive – 500 iterations executed in 58.1274 ms
Executing scenario: MultiPocoMapping
———————————–
Dapper  – 500 iterations executed in 42.2382 ms
SqlFu – 500 iterations executed in 44.1905 ms
InsightDatabase – 500 iterations executed in 44.2572 ms
PetaPoco – 500 iterations executed in 47.1851 ms

Massive doesn’t support the action. Specified method is not supported.
OrmLite doesn’t support the action. Suports only its own specific source format

Executing scenario: Inserts
———————————–
InsightDatabase – 500 iterations executed in 50.354 ms
SqlFu – 500 iterations executed in 57.7487 ms
PetaPoco – 500 iterations executed in 65.2865 ms
Dapper – 500 iterations executed in 66.763 ms
OrmLite – 500 iterations executed in 74.8407 ms

massive doesn’t support the action. Couldn’t figure how to insert pocos with auto increment id

Executing scenario: Updates
———————————–
InsightDatabase – 500 iterations executed in 42.2859 ms
OrmLite – 500 iterations executed in 44.6915 ms
SqlFu – 500 iterations executed in 47.8716 ms
PetaPoco – 500 iterations executed in 49.9807 ms
Dapper  – 500 iterations executed in 71.3594 ms
massive – 500 iterations executed in 109.57 ms

(TL;DR – Stop writing Data Access Layer code. Define your database as a service and use Insight.Database to automatically implement it for you.)

A colleague of mine pointed me to the article “ORM is an anti-pattern”. In short, the article says that using an ORM seems like a good idea at first, but in the long run you have more bad consequences than good ones. We’ve used the ActiveRecord pattern for projects for years, and I’d have to agree that it’s really nice to use when you are starting out a new Domain-Driven-Design project. However, after 10 years on a code base with this pattern, it often becomes hard to update and change the framework. So I’d have to agree with this article. But being a micro-ORM author myself, how do I justify my own ORM code?

Let’s see if we can treat our database the way it should be treated: as a service.

If we define stored procedures to access our database, we are defining a service to access some data. It has a lot of advantages, including being able to tell how all of your applications are accessing your data.

CREATE PROC InsertBeer @type varchar(128), @description varchar(128) AS
    INSERT INTO Beer (Type, Description) OUTPUT inserted.ID
        VALUES (@type, @description)
GO

CREATE PROC GetBeerByType @type [varchar] AS SELECT * FROM Beer WHERE Type = @type 
GO

Great. Now we have to call the stored procedures. The problem with most data access libraries is that you have to write a lot of code to send and retrieve your objects from the database. Using System.Data.SqlClient is a painful amount of work, so most people use an ORM framework like EntityFramework, or ActiveRecord to map your objects to the interface. But that’s still complicated and eventually falls down under its own weight.

What we want is a simple method to generate a .NET proxy for the stored procedures. The simplest thing we could write to proxy the service is an interface:

public interface IBeerRepository
{
    void InsertBeer(Beer beer);
    IList<Beer> GetBeerByType(string type);
}

If you always work through an interface for your storage service, you can reap benefits like:

  • Easy inversion of control (IoC) with a library like Ninject.
  • Easy mocking of your storage layer with a library like Moq.
  • Easy versioning of your service – just add a new service.

Insight.Database v2.1 now has the ability to automatically implement your interface and invoke your stored procedures. It’s as simple as:

// automatically implement the interface on my database connection
DbConnection c = new SqlConnection(connectionString);
IBeerRepository i = c.As<IBeerRepository>();

// just call the methods
i.InsertBeer(b);
var results = i.GetBeerByType("ipa");

It’s really that simple.

So now you can treat your database server like any other service.

  1. Define your service – create stored procedures
  2. Define your proxy – create an interface
  3. Call your service – with connection.As<Interface>()
  4. Replace your service later – re-implement the interface a different way

And if you need to use transactions, tweak the mapping, or access the database directly (e.g. Bulk Copy), the library exposes that too.

Now back to my original point: if you are “mapping” “objects” to “relational” databases, isn’t that bad? Answer: your object code is always going to have to call some external service and serialize/deserialize the data. Insight.Database now makes that easy with a database service. The issue with ActiveRecord-style ORMs is that the database and relational structure get embedded within your objects, and it’s that coupling that makes it an anti-pattern.

Insight.Database Links

Finally. It’s been a long period of late nights, but I can’t think of anything else I’d want to add, or any more code that I can delete and have it still actually work.

  • v2.0 has full async reads in .NET 4.5, automatic multi-recordset processing, customizable binding rules, tons of optimizations, and more code than I can remember.
  • In Package Manager, turn on “Include PreRelease” packages to get the new package.
  • v2.0 should be compile-compatible with v1.x. (Except for a few APIs I’m pretty sure nobody is using.) It’s not binary-compatible with v1.x.
  • If you are using Insight.Database.Schema, please upgrade to v2.0.7, which no longer has a dependency on Insight.Database v1.0.
  • Documentation coming soon! (The v1.x docs should be ok for the basic stuff.)
  • After the docs are done and I run the new version through some other existing projects, I’ll push out a stable release, but why not get started?

Links

 

Follow

Get every new post delivered to your Inbox.

Join 126 other followers