Intro to Postulate ORM

• Download Postulate.MergeUI-1.0.16 - 1.1 MB
• Download Postulate.Orm-0.9.147 - 109.6 KB

Introduction

In this post, I would like to talk about an ORM library I'm working on called "Postulate." (GitHub) I've been into ORMs for a long time -- as both developer and consumer. Over the years, I've never really found a great balance of power and productivity -- until now, possibly.

Why don't I give up and use Entity Framework?

There are a few reasons:

1. I hate migrations. They are complicated and demanding.
2. I don't like EF's broad focus and reach. I'd like my ORM layer to be lighter and simpler.
3. I don't agree with EF's conventions regarding primary, foreign keys, and inheritance.
4. I'm not comfortable with Linq as a wholesale replacement of SQL. Linq to Entities is genius, but you have to buy into the whole EF stack or develop your own Linq provider.

What's cool about Postulate?

1. Its CRUD operations are pretty simple -- syntactical ease and concision is probably my main objective.
2. It has a neat way of merging model changes to the database with a special app that I'll talk about here. I also have a walkthrough video here: https://vimeo.com/219400011. All of it is open source, and the merge API can be applied in different ways.
3. There are features like change and delete tracking, that I think are useful in many applications. There are also some great ways to standardize how you do audit tracking with user names and date stamps. I come from a multi-tenant system background, and there's a feature to help prevent data from drifting between tenants via the [ColumnAccess] attribute.

What's likely to be controversial about Postulate?

1. Model classes must inherit from Record<TKey>, so it's not pure POCO. The reason I have this is so I can enforce a convention regarding primary keys as well as offer extension points for validation, events, and permissions.
2. There is some inline SQL used when forming WHERE clauses used in a few places. I would like to offer generic parsing of Lambda expressions, but I don't know how to do that. I welcome any assistance!
3. Postulate doesn't have navigation properties. I think they're a bit overrated, for one thing, but also very complex and risky to implement, IMO.

This introduction will focus on getting started and doing CRUD operations in a console app and using the Schema Merge app. I won't talk about queries in general nor more realistic use in an MVC app, for example. A while back, I refactored query capability to a different project called Postulate.Sql. Likewise, I have a project Postulate.Mvc that is specific to ASP.NET MVC.

Walkthrough

To get started:

1. Create a console app, set the .NET version to 4.6.1, and install the nuget package Postulate.Orm.
2. Download/install the Schema Merge app from this article or the GitHub release page. Edit the post-build event for the console app project so it has this command:

   "C:\Users\Adam\AppData\Local\Adam O'Neil Software\Postulate Schema Merge\PostulateMergeUI.exe" 
   "$(TargetPath)"

   You can get the EXE portion of the command from the Postulate Schema Merge desktop icon:

   

   The ($TargetPath) macro is defined by Visual Studio, and passes the build output filename to the Postulate merge app.

3. Add a SQL Server connection string to an app.config file. This walkthrough will assume the connection name "demo." It's not necessary for the database to exist -- but make sure the string is otherwise valid, and has permission to create a database if it doesn't exist.

Here's what mine looks like. Notice I'm using localhost where I have a SQL Server instance. You can also use Azure or any other connection that works in your environment. Note that my database name is CodeProjectDemo.

Now you're ready to write some code.

Root object: SqlServerDb<TKey>

First, create a class that inherits from SqlServerDb<TKey>. The TKey argument is the primary key type of all your tables in the database. Postulate supports three key types: int, long, and Guid. This constructor is enough to get going:

public class DemoDb : SqlServerDb<int>
{
    public DemoDb() : base("demo")
    {
    }
}

Nothing very surprising here, but we need to add another constructor for the benefit of the Schema Merge app. Schema Merge requires an explicit configuration object because the Schema Merge app has its own configuration distinct from your console app.

public DemoDb(Configuration configuration) : base(configuration, "demo")
{
}

The default constructor takes an optional userName argument. This comes into play for applying user and timestamps on records. For a console app, we can use Environment.UserName. The final class looks like this:

namespace PostulateIntro
{
    public class DemoDb : SqlServerDb<int>
    {
        public DemoDb(Configuration configuration) : base(configuration, "demo")
        {
        }

        public DemoDb() : base("demo", Environment.UserName)
        {
        }
    }
}

Model classes: Record<TKey>

Now we're ready to start creating model classes. Let's do something simple:

public class Employee : Record<int>
{
    [Required]
    [MaxLength(50)]
    public string FirstName { get; set; }

    [Required]
    [MaxLength(50)]
    public string LastName { get; set; }

    [Required]
    [MaxLength(9)]
    public string SSN { get; set; }

    public DateTime DateOfBirth { get; set; }

    [MaxLength(50)]
    public string Phone { get; set; }

    [MaxLength(50)]
    public string Email { get; set; }
}

When you build the project, Visual Studio will churn a bit. You should see this message after a few seconds:

Followed by this:

Every time you build your project, and there are model class changes, the Schema Merge app will appear, and show you the actions and impacted objects in a tree view on the left. The corresponding SQL will appear on the right. Click Execute button in the upper right to execute the SQL.

If you close the Schema Merge app without executing, it won't come up again unless you make more model changes or you Rebuild the project.

Let's go ahead and execute what we have. You should see this message:

Now we can go back to coding, and make some more model changes. Our first table has been created.

Let's add a new property to our employee class, a foreign key in this case:

[ForeignKey(typeof(Employee))]
public int? ManagerId { get; set; }

Postulate has a ForeignKey attribute like EF, but it's a different attribute -- don't confuse them. Postulate requires the type of the primary table. (You can optionally enable cascade delete and indexing -- see ForeignKeyAttribute.cs.)

When I build the project, I see this. My employee table is being dropped and re-built to add the new column. If the table had data, there would be no DROP statement. Postulate will never drop tables that have data. In that case, columns simply add to the end of the table. Note also there's a foreign key being created as well.

I hope this gives you a sense of what you can do with Postulate Schema Merge. It's pretty good at detecting structural changes and generating correct SQL to execute them. This was the hardest part of Postulate by far. If you have suggestions or run into bugs, please bring them to my attention by creating an Issue on the GitHub repo.

The walkthrough video I linked above goes a little farther in demonstrating what you can do with Schema Merge. For now, let's create and work with some data.

CRUD Operations

Let's start off by creating and saving a single Employee record. I leaped into it without thinking and wrote this:

class Program
{
    private static DemoDb _db = new DemoDb();

    static void Main(string[] args)
    {
        var emp = new Employee() 
        { LastName = "Whoever", FirstName = "Hegel" };
        _db.Save(emp);
        Console.WriteLine($"emp id = {emp.Id}");
        Console.ReadLine();
    }
}

You shouldn't have to think too much about it! That's one of my primary design goals. Notice I have a static variable _db. I could've declared it locally to the Main method, but out of habit (from MVC controller development), I made it static at the class level.

The Save method performs an insert or update depending on the state of the record. There is no explicit insert or update method. Well, there is an Update method, but it's used for a somewhat different case. Normally, within an MVC action for example, and for typical situations, you'd use the Save method.

When I ran this, I got this error:

It lets me know I'm missing a couple required fields. So let's fix that and try again. I re-wrote it like this, and ran it:

That's more like it!

There are a couple ways to find an existing record: Find and FindWhere. Find accepts a TKey argument, while FindWhere accepts a string WHERE clause. Let's try both -- first the Find method. I know that employee ID 1 was created just a bit ago, so I pass that as the key argument. You can see in my little slice of the console window that "Hegel Whoever" was found.

In cases where I don't have a TKey, I can use a string WHERE clause. Note how the lastName is passed as a parameter using Dapper-style syntax.

Deleting a record is not hard. There are three delete methods -- you can probably tell what they do from the method names. They all accept a TRecord generic argument for the model type you want to delete followed by either a WHERE clause and parameters or a TKey.

Deleting records gets a little more interesting when we add the [TrackDeletions] attribute to a model class. This lets us track who's deleting records and when, as well as restore capability -- as long as foreign keys aren't violated. Remember when we set the user name in the DemoDb constructor to Environment.UserName? That user name is passed to subsequent methods that may need a standard way of knowing the current user -- without depending on a particular identity provider.

Deleting a record with [TrackDeletions] in effect causes Postulate to serialize objects to XML as it's deleted. A deleted table is created if it doesn't exist, and the record data is saved there. (Be aware that the production credentials you end up using may not have permission to create schemas and tables at runtime, so it may help you to create the deleted schema and delete tracking tables in production before general use.)

_db.DeleteOne<Employee>(1);

To restore the record:

_db.RestoreOne<Employee>(1);

You can read more about delete tracking at the Postulate wiki. While we're on the subject of tracking, let me describe change tracking briefly. Add the [TrackChanges] attribute to model classes in order to capture the before/after state of records that you Save or Update. Example:

Now, I will find, update, and save a record. Notice that the TKey value is 2 because I'm working with the record I restored a bit ago, and it has gotten a new identity.

var emp = _db.Find<Employee>(2);
emp.LastName = "Whomever";
_db.Save(emp);

Now I can query the change history of the record:

You can read more about change tracking at the Postulate wiki.

Conclusion

I hope this is enough to pique your curiosity and give Postulate a try! I welcome contributions on the GitHub repo and any feedback.

The Schema Merge app has a separate repo: Postulate.MergeUI.

If there's interest, I'll talk about Postulate.Sql and Postulate.Mvc, which show this ORM in more realistic use.

Also, have a look at the video, which goes a little deeper than the walkthrough above.

Credits

The Schema Merge app's SQL syntax-aware code view is made possible by Pavel Torgashov's magnificent Fast Colored Text Box.