Insight.Database 4.0 has been released!

In Insight.Database v4.0, there was one major goal:

Make it easy to return arbitrary trees of objects from SQL results.

See the full documentation on github.com.

I’m really happy with how our little micro-ORM turned out. Before v4.0, you could return:

  • Single Records
  • Lists of Records
  • Multiple Lists of Records

Each record could be:

  • A single object.
  • A single object with one-to-one mappings to additional records.

The big thing missing was:

  • An object with a list of children (one-to-many).

And of course:

  • An object with a list of children with a list of children.
  • An object with a list of children with other one-to-one mappings.
  • etc.

Now you can do all of this.

One Object

Reading a single object:

Beer beer = connection.Single<Beer>("GetABeer");

List of Objects

Reading a list of objects:

IList<Beer> beer = connection.Query<Beer>("GetAListOfeer");

Two Lists of Objects

Reading two lists of objects:

var results = connection
    .QueryResults<Beer, Glass>("GetAllBeersAndAllGlasses");
IList<Beer> beers = results.Set1;
IList<Glass> glasses = results.Set2;

You can return up to 16 sets of objects this way.

This is equivalent to:

connection.Query(“GetABeerAndProperGlass”, Parameters.Empty,
Query.Returns(Some.Records)
.Then(Some.Records);
[/sourcecode]

Keep an eye on Query.Returns. It gets pretty powerful.

One-To-One Relationships

Reading a list of objects with a one-to-one mapping:

IList<Beer> beers = connection
    .Query<Beer, Glass>("GetABeerAndProperGlass");
Beer beer = beers.First();
Glass glass = beer.Glass;

This is equivalent to:

connection.Query("GetABeerAndProperGlass",
    Parameters.Empty,
    Query.Returns(OneToOne<Beer, Glass>.Records);

Insight will automatically split each record into Beer objects and Glass objects, then assemble them properly. You can get more details by reading [[Object Graphs]].

If you almost always return two objects together, you can put a RecordsetAttribute on the class, so Insight will always try to read in the subobject. It’s ok if the subobject isn’t there.

[Recordset(typeof(Beer), typeof(Glass))]
class Beer { ... }

One To Many Relationships

Here’s where we start to get tricky. For anything advanced, we need to specify the structure that the query returns:

CREATE PROC GetBeerAndPossibleGlasses AS
SELECT * FROM Beer
SELECT b.BeerID, g.8 FROM Beer JOIN Glasses ON (...)
class Beer
{
    public int ID;
    public IList<Glass> Glasses;
}

var results = connection
    .Query("GetBeerAndPossibleGlasses",
    Parameters.Empty,
    Query.Returns(Some<Beer>.Records)
        .ThenChildren(Some<Glasses>.Records);

Now Insight knows that you want a list of beer records, each with a list of glasses that match.

Automatic Assembly

Insight will assume that the Beer class has an ID field, and that the first column in the glasses query is the Beer ID. Then it will break up the glasses into lists and assign them into your Beer objects.

To determine the ID field, Insight uses the following order of precedence:

1. The field explicitly requested in the structure definition.
2. The field on the class marked with the [RecordId] attribute.
3. A field called “ID”.
4. A field called “classID”, where “class” is the short name of the class.
5. A field ending in “ID”.

To determine the target list field, Insight uses the following order of precedence:

1. The field explicitly requested in the structure definition.
2. The field on the class marked with the [ChildRecords] attribute that matches the type of the list.
3. Any field matching the type of the list.

The list field/property can be any of the following types:

* IList
* IEnumerable
* ICollection
* List

If Insight doesn’t guess the ID field or list field right, you can tell it with attributes.

class Beer
{
    [RecordId] public int ID;
    [ChildRecords] public IList<Glass> Glasses;
}

Or you can tell it with hints on the query:

var results = connection.Query("GetBeerAndPossibleGlasses",
    Parameters.Empty,
    Query.Returns(Some<Beer>.Records)
    .ThenChildren(
        Some<Glasses>.Records,
        id: beer => beer.ID,
        into: (beer, glass) => beer.Glasses = glass);

Other Notes:

* The way it’s coded, it’s possible for more than one parent to have a given ID, so it’s actually possible to return many-to-many mappings. Woot.

Mixing and Matching

You can mix and match Then and ThenChildren and also OneToMany records. Using these pieces, you can make almost any structure out of your data.

This is getting a little crazy, but it’s not icky at all! Returning 4 recordsets, with two parents and two children!

Results<Beer, Wine> results = c.QuerySql(
   @"    SELECT * FROM Beer WHERE Type="IPA"
   SELECT b.ID, g.* FROM Glasses g JOIN RightGlass(...) ...
   SELECT * FROM Wine WHERE Type="IPA"
   SELECT w.ID, g.* FROM Glasses g JOIN RightGlass(...) ...",
   parameters,
   Query.Returns(Some<Beer>.Records)
       .ThenChildren(Some<Glass>.Records)
       .Then(Some<Wine>.Records);
       .ThenChildren(Some<Glass>.Records)

PSST! Unit Testing with the PowerShell Suite for Testing

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 &quot;cannot be null&quot;
{ Add-Numbers 1 } | Should Throw &quot;cannot be null&quot;

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 &quot;Enter number ${which}:&quot;
    [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 { &quot;nothing&quot; }
    Get-ChildItem c: | Should Be &quot;nothing&quot;
}

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 &quot;Calculator&quot; {
    Given &quot;two numbers&quot; {
        TestSetup {
            Mock Get-Number { 1 } -when { $which -eq 1 }
            Mock Get-Number { 2 } -when { $which -eq 2 }
        }

        It &quot;adds them&quot; {
            Do-Calculator | Should Be 3
        }
    }

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

        It &quot;throws&quot; {
            { 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

Micro-ORM Benchmarks: February 2013

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

Insight.Database: The Anti-Anti-ORM for .NET

(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&lt;Beer&gt; 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&lt;IBeerRepository&gt;();

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

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

Insight.Database v2.0.0-alpha1 is now in NuGet!

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

 

Insight.Database 2.0: Refactor #1–Expanding Object Graphs

I was able to finish a major refactor last night for Insight.Database (not yet published). It’s time for some feedback before I take the next step.

You were already able to do:

dataReader.AsEnumerable<T>()

dataReader.AsEnumerable<T, T1>()

dataReader.AsEnumerable<T, T1, T2>()

etc. Same thing with Query<T> and ToList<T>.

Now you can do:

dataReader.AsEnumerable<T>(typeof(Graph<T, T1, T2>))

This lets you deserialize an object graph of any number of classes. But really, how often do you need to deserialize more than 5 records in a single recordset?

More importantly, it lets you do:

[DefaultGraph (typeof(Graph<MyClass, MySubClass>))]

class MyClass { … }

So then Insight will automatically try to deserialize the sub-objects if the columns are present in any result set, even if you just do:

dataReader.AsEnumerable<MyClass>()

This should make your code look cleaner in a lot of cases. If you need to override the default, you can still call any of the overloads with multiple classes in the templates.

So far this is working without breaking the interface, and there is a small performance improvement in the low-level code.

All of this is to set up for support for multiple recordsets. Right now, it looks like it’s going to work like this:

var results = connection.QueryMultiple<T1, T2>(…);

This would return a Results<T1, T2> structure:

class Results<T1, T2>

{

IList<T1> Result1;

IList<T2> Result2;

}

The other option would be to return a list/array rather than a class, but then you lose type-safety.

In this pattern, if you need to deserialize multiple recordsets with object graphs, the syntax would get really icky, so having the [DefaultGraph] attribute really makes this easy. (See why that was important?)

You will also be able to override the graphs with a syntax like:

var results = connection.QueryMultiple<T1, T2>(withGraphs: <something>);

I’m not sure yet how to make the code pretty in this case.

One of the common patterns that I’d like to support is returning a recordset along with some data about the results, like paging data

For common use cases, you probably will be able to derive your own Results class and do things like this:

class PageData

{

public int TotalRecords;

}

class PageResults<T> : Results<T, PageData>

{

public TotalRecords { get { return Result2.First().TotalRecords; } }

}

var pagedResults = connection.QueryMultiple<PageResults<MyClass>>(…)

or make your own overload:

var pagedResults = connection.QueryPaged<MyClass>(…)

If you have any suggestions, comments or questions on the syntax (before I code it), please send them along!

Upcoming Changes for Insight.Database 2.0

I’ve had some really good feedback on the latest version of Insight.Database. The goals for the next big push are:

  • Make Multiple Recordsets first-class citizens – make the API and use cases support multiple recordsets more easily.
  • Fully asynchronous code in .NET 4.5 – take on async for all database operations, from Open, ExecuteReader, Read, MoveNext, and Close.
  • Support for CancellationTokens in Async mode.
  • Optimized paths for First and FirstOrDefault in Async mode.

In order to support them, there is going to be some big refactoring coming up. Some of the API changes will be breaking (but I think you will like the results).

  • Async mode will require .NET 4.5 – in order to get all of the logic cleaned up and high-performance, we will have to switch to coding with async/await, and thus need .NET 4.5. Insight.Database 2.0 will not support async in .NET 4.0.
  • Most likely the syntax for querying object graphs will change.

I’d love some feedback on these last two points. Add comments if you have an opinion.

Right now you do to get sub-objects returned, you do:

Query<T1, T2, T3>(…)

We will probably want to use that syntax for handling Multiple Recordsets.

So it would be Query<Rs1, Rs2>, and the syntax for an object graph would be:

Query<Graph<T1, T2, T3>>(…)

It’s debatable whether multiple recordsets or object graphs are more common. The good news is that with the new syntax, you can really easily deserialize multiple recordsets. And you will be able to declare the default object graph deserialization order by adding a marker interface to your object:

class MyObject : Graph<MyObject, Sub1, Sub2>

Then just do:

Query<MyObject>

Then Insight will use the marker interface to know which objects to deserialize, and you won’t have to put that anywhere else in your code.

If you have any ideas about better ways to handle multiple recordsets and object graphs, let me know! This is a holiday project I’ll try to get done over the next few days.

a bit of code