Duck : Delete Update Create Killer

2010-10-18T23:49:18 / jeremie chassaing

Duck sign, Stockbridge High Street I recently had a remark from Frederic Fadel from Aspectize, telling me about Event Sourcing something like:

Why would you like to write SQL to write data to your read model when our product can do it for you ?

I acknowledge that their product is fancy. You simply declare your db data schema, your UI and services and bind it all together.

But it doesn’t fit well with CQRS and Event Sourcing. And I want to do Event Sourcing for domain and business reasons, not technical reasons.

But he was write on this point :

I don’t want to write SQL to denormalize my events to my queryable storage.

What are my options ?

Writing SQL by hand, but testability is low, and you’ll get a mix of C# to get data from the events, and SQL for the Update queries.
Using an ORM. When doing NHibernate you don’t really write SQL. Testability wont be great anyway.

The problem with ORMs

ORM are usually better at getting data than at changing it. You can do it, but let’s look at what happens.

The ORM loads data from your Db into entities that will be tracked by an identity tracker. Then you change the values in memory . Then the ORM will find what needs to be sent back to the server and make a query to the Db so that the change happens.

But what I need to do is a lot simpler. Just emit some INSERT, UPDATE or DELETE based on current table values and event data.

With an ORM, what happens if the data is changed between loading and saving ? I’ll have to manage some versioning and/or transaction. And I’ll make two roundtrips to the server needlessly.

Here comes Duck

Duck is a kind of ORM oriented toward Delete Update Create.

Don’t ask Duck to load data in memory, it simply can’t.

You simply express how data should change based on current row content and values that you’ll pass.

It avoids the first roundtrip to the database, and make shorter code to express the change.

Let’s see how to use it

First, you should declare a class that has the structure of your table with public get/set properties, and marked with a Table attribute :

	[Table]
	class Species
	{
		public Guid Id { get; set; }
		public string Name { get; set; }
		public string BinomialName { get; set; }
		public bool IsEndangered { get; set; }
		public int Population { get; set; }
	}

It contains current observed species at an observatory.

Then a simple new statement, let’s say that a new species has be registered at the observatory :

	var duck = new DuckContext'(connectionString);
	var speciesId = Guid.NewGuid();
	duck.In<Species>()
	    .Insert(() => 
	       new Species
	       {
		Id = speciesId,
	         Name = "Mallard",
		BinomialName = "Anas platyrhynchos",
		IsEndangered = false,
		Population = 50
	       });

Nothing tricky here..

The observatory noticed a population decay, the species is endangered :

	duck.In<Species>()
	    .Where(r => r.Id == speciesId)
	    .Update(r => new Species {
              Population = r.Population - 40,
              IsEndangered = true});

Here, the use of the current value of Population will not load current value. It will the following statement :

UPDATE Species
SET
    Population = Population - 40,
    IsEndangered = 1
WHERE
    Id = @p0

I chose to create a new Row from the old one rather than change the original one for two reasons :

It makes rows immutable and you don’t have to think about execution order between fields assignments. It’s the way SQL works
Linq Expressions cannot represent statement blocks and assignments in C#3, Duck would have been .Net only…

The –40 is directly in the query here because we used a constant. I we where using a variable, query would contain a parameter

Now the species has disappeared, it should be removed from the observed species (though it could be just an update somewhere else) :

	duck.In<Species>()
	    .Where(r => r.Id == speciesId)
	    .Delete();

Testability

To run your test you just have to use the InMemoryDuckContext… you have then access to Table<T>() that’ll enable you to set up your data and verify after execution that things changed as expected. I’ll talk a bit more about it soon.

Try it now, it’s OSS

You can grab the code at bitbucket and try it now :

http://bitbucket.org/thinkbeforecoding/duck

It’s in F# ! Writing a AST analyzer in F# is far more easy, concise and expressive than in C#. You’ll just have to reference Duck in you project, there’s no direct F# dependency.

Next episode will be about how to mix it with Rx (Reactive Framework) to declare your event handling logic.

Hope you like it, and don’t hesitate to give feedback and suggestions.

// thinkbeforecoding