Starcounter vs. ORM and DDD

This article will highlight some existing issues with current MVC (Model-view controller) frameworks for software developers and ISVs and then provide solutions by leveraging the Starcounter in-memory application engine.To make it as realistic as possible, several examples of real projects use ASP.NET + Entity Framework (database first and code first) with MS-SQL server or MySQL as the database engine. At the end of the article, there is a simple project to get started so to not just read about it but also to test it out.IntroductionArticle

Working with business applications, you will find almost every application needs to consistently manage and share data between multiple simultaneous users. This functionality is usually provided by database management systems (DBMS). Since the early Seventies most DBMSes implement SQL, a standard language for data manipulations. Then software developers would choose a data storage method based on the advantages and disadvantages of each one. However, all of the applications had roughly the same design: application logic here, and database there.

1. ORMs

SQL database engines manipulate data in terms of Table, Column, and Row. The applications written in any OOP language manipulate data in terms of Class, Property, and Instance.

This mismatch caused a lot of redundant code in the communication layer between the application and the database. To reduce the amount of redundant code, bugs, and development time ORM libraries (Object Relational Mapping) were invented.

ORMs add an additional layer between the application and the database. Any ORM acts like a translator between Table, Column, Row and Class, Property, Instance concepts.

ORMs intend to:

1.1. Remove all SQL code from the application.

1.2. Provide access to database tables in the same way as regular application classes.

1.3. Add the possibility to switch between database engines without changes in application code.

This may sound very good, but unfortunately it’s too good to be true. This is how it really works with present ORMs:

1.1. Reduce the amount of SQL code in the application.

1.2. Provide access to database tables in a similar way as regular application classes.

1.3. Add the possibility to switch between database engines with fewer changes in application code.

This occurs not because ORMs are bad, but because of database engine architecture. Let’s see what is actually holding ORMs back.

1.1. Remove all SQL code from the application

SQL queries are the only way to manipulate data inside any SQL database engine. ORMs should translate every action over an instance of a table class into a SQL query and run it over the database.

ORMs are not yet clever enough to choose the best possible SQL syntax. The huge and complex SQL queries should still be written by developers with SQL knowledge.

Some actions, such as triggers, can be executed inside the database engines only, and the engine supports only SQL.

1.2. Provide access to database tables in the same way as regular application classes

The property values of a class instance and the instance itself are saved in RAM. The property values of a table class saved in the database engine, which could be HDD or another section of RAM. To access an instance of a table class we have to either load all of the property values together, or load each property on demand (lazy loading). Both of these approaches may cause performance issues.

The developer should always keep in mind database limitations when accessing an instance of a table class.

We all know how valuable calculated properties are, but unfortunately they are not supported by ORMs. For example, consider this class and query:

public class OrderItem {
    public int Quantity;
    public decimal Price;
    public decimal Amount {
        return this.Price * this.Quantity;
    }
}

var items = db.OrderItems.Where(x => x.Amount > 1000);

The Where query will be translated into SQL and fail, because the Amount property is not available in the database.

1.3. Switch between database engines without changes in the application code

There are so many different database engines and each has its own unique features, advantages and disadvantages. It is impossible to switch the database engine without reviewing and reworking some pieces of application code. The unique features of the previous engine have to be removed.

Try to migrate a project from MS-SQL to MySQL; from MS-SQL to MS-SQL CE; or vice versa and you will understand the challenges: different data types, different implementation of skip N and take N, different implementation and limitation for sub-queries, views, and stored procedures.

2. Domain-driven design (DDD)

Splitting the business application into two parts (application & database) causes some major issues. To solve those issues Domain-driven design was invented.

Here is a list of some issues which DDD aims to solve:

2.1. The project has the same functionality implemented in the same way or different in different places.

2.2. There is more than one object for the same item.

2.3. There are objects with properties that are not actually attributes of that object.

2.4. There is a very poor relationship among related items.

2.5. Looking at the objects, it is not possible to understand what the whole application is all about.

A full article about DDD is available here: Domain Driven Design - Clear Your Concepts Before You Start.

Now, we will go through each of the points listed above to understand what is happening.

2.1. The project has the same functionality implemented in the same way or different in different places

The application split apart is definitely the reason for this issue. Here is a simple example:

There is a shopping cart with some items inside. Two use cases:

a) The User adds/removes an item from the cart. The Total amount value is calculated automatically before user saves changes. This calculation is made by application.

b) The Administrator lists all of the carts ordered by the Total amount value. The Total amount value is calculated by the database.

Most of the developers would prefer to add another property TotalAmount to the shopping cart. Once a shopping cart item is changed the TotalAmount property is updated. This solves the first issue, but causes the third issue from the list above.

2.2. There is more than one object for the same item

The communication speed between application and database is one of the biggest performance drawbacks. To reduce the amount of transferred data, some huge tables may have two or more ORM classes.

Simple example, the table:

CREATE TABLE Cars (
    ID int, 
    Driver varchar,
    PlateNumber varchar,
    Brand varchar,
    Model varchar,
    Kit varchar,
    Color varchar,
    Engine decimal,
    Travelled int,
    Year int,
    Cost decimal,
    FuelConsumption decimal
)

May yield two scenarios:

a) Display list of all cars – requires all fields.

b) Display list of cars in an autocomplete field – requires only ID, Driver, PlateNumber values.

Using the same ORM class for both cases would load all car fields values from database to application, even if they are not it is not all used. This means more data to transfer, more time to spend, and the risk of a customer dissatisfied in the end.

Using different ORM classes for both cases goes against the DDD pattern. When applications are split apart it causes issues. If the Cars table were inside the application, the issue would not exist.

2.3. There are objects that have properties that are not actually attributes of that object

Very often deep nesting requires redundant properties to increase performance.

This is a simple and quite common model. Now let’s assume we have a survey and need to select all questions for the survey.

This is how it would look in SQL:

SELECT Question.*
FROM Question INNER JOIN Section ON Question.SectionID = Section.ID
    INNER JOIN Page ON Section.PageID = Page.ID
    INNER JOIN Survey ON Page.SurveyID = Survey.ID
WHERE Survey.ID = ?

And this is how it looks with LINQ query:

Context.Questions.Where(q => q.Section.Page.SurveyID = ?);

So, what is wrong with this query? There is a performance issue.

Look at the next diagram. This one looks much more complicated because of four redundant relations.

With these additional relations the SQL query would look as follow:

SELECT *
FROM Question
WHERE Question.SurveyID = ?

Why do we need those redundant properties and relations? The answer is simple: to increase performance. Would we add those relations if the first approach would work fast enough? The answer is no.

2.4. The related items do not work well together

Two tables with a mutual relation could be an example of this issue.

TABLE A (ID, Name, BID);
TABLE B (ID, Name, AID);

Inserting two mutually related rows requires three operations:

1. Insert row A.
2. Insert row B with AID.
3. Update BID of row A.

This code does not work with ORM table classes:

var a = new A();
var b = new B();

a.Name = “Instance of A with B”;
a.B = b;
b.Name = “Instance of B with A”;
b.A = a;

Context.SaveChanges();

Again, this occurs because of the translation between classes and tables. SQL database engines do not support inserting of two or more rows at the same time.

Another example is multi-select values.
 

TABLE Motherboard (
    ID int,
    Brand varchar,
    Socket varchar,
    Ram varchar,
    Name varchar
);

TABLE CPU (
    ID int,
    Brand varchar,
    Socket varchar,
    Name varchar
);

--Cpus supported by motherboards
TABLE MotherboardCpu (
    ID int,
    MotherboardID int,
    CpuID int
);

Selecting and processing rows from the MotherboardCpu table significantly reduces performance when needed to select a list of all motherboards with CPUs supported, when filtered by CPU name, when bound to <select multiple>.

The developers often replace the data model above with the one below:

TABLE Motherboard (
    ID int,
    Brand varchar,
    Socket varchar,
    Ram varchar,
    Name varchar,
    --List of names of supported CPUs in CSV/JSON format
    Cpu text
);

TABLE Cpu (
    ID int,
    Brand varchar,
    Socket varchar,
    Name varchar
);

The Motherboard and CPU are two related entities from a domain design point of view, but do not have any relation inside the database.

2.5. When looking at objects it is not possible to understand what the entire application is all about

Below is an example of an entity diagram of a project.

Each table in the project required an ORM class, JSON representation class, and a JavaScript class. Three classes per table and the table itself is difficult to maintain. The developers decided to save all statuses and types in one table to reduce the number of tables. In the end, the data table diagram looked like this:

The entity diagram is very clear and self-explanatory whereas the data table diagram is totally confusing. The framework limitation caused a trade-off between database purity and code purity.

Starcounter

This diagram below presents the Starcounter architecture.

This diagram shows Starcounter applications running inside the database and with direct access to database objects which are saved in RAM.

There is no round trip between the database and the application. The application classes are the database objects. With Starcounter, ORMs or any other mappers are not required.

Here is a database class definition.

[Database]
public class Vehicle {
    public string PlateNumber;
    public string Brand;
    public string Model;
}

There isn’t a context declaration, or table name or property name mappers, just pure C# classes with an attribute. Here is another example with foreign keys.

[Database]
public class VehicleStatus {
    public string Name;
}

[Database]
public class Vehicle {
    public string PlateNumber;
    public string Brand;
    public string Model;
    public VehicleStatus Status;
}

It’s the same situation again. No foreign key declaration, no mapping, just pure C# properties. Now let’s see which of the above described issues are solved by Starcounter?

1.1. Remove all SQL code from the application

For now, Starcounter still requires some SQL to select objects from the database. However, the amount of SQL you need to write is dramatically reduced because, instead of using joins or multiple queries, you can simply follow properties from one object to the next.

1.2. Provide access to database tables in the same way as regular application classes

Yes, absolutely.

The read-only properties with complex logic are fully supported by database classes. There aren’t any data-type issues between C# and SQL as C# objects are database objects.

1.3. Switch between database engines without changes in the application code

No, the code changes and adjustments are required. Starcounter uses a completely different approach compared to traditional database engines.

2.1. The project has the same functionality implemented in the same way or different in different places

Starcounter does not have a database side and an application side. Database objects are application objects and vice-versa. Moreover, most Starcounter applications share the same model for both the server and client sides. The JavaScript library PuppetJs synchronizes changes between the server and the client.

In the end, there is only one model class, one place to write logic, and fewer possibilities to have the same logic in different places.

2.2. There is more than one object for the same item

There is no need to create any additional classes or mappers for the database objects; the entire item is directly accessible from RAM.

2.3. Objects with properties that are not actually attributes of that object

When there is direct and easy access to every property of every object, there is no need to create additional properties. A CPU should have a reference to a Motherboard, a Motherboard should have a reference to a PC Case, but a CPU should not have any references to a PC Case.

2.4. The related items do not work well together

Unlike traditional SQL databases, Starcounter allows you to store two mutually related objects together. This code works:

Db.Transact(() => {
    var a = new A();
    var b = new B();

    a.Name = "Instance of A with B";
    a.B = b;
    b.Name = "Instance of B with A";
    b.A = a;
});

The multi-select property is even more easier to solve:

[Database]
public class Motherboard {
    public string Brand;
    public string Socket;
    public string Ram;
    public string Name;

    public IEnumarable<MotherboardCpu> MotherboardCpus {
        get {
            return Db.SQL<MotherboardCpu>("
                SELECT o FROM MotherboardCpu o WHERE o.Motherboard = ?", this);
        }
    }

    public string MotherboardCpuNames {
        get {
            return string.Join(", ", this.MotherboardCpus.Select(x => x.Name));
        }
    }
}

[Database]
public class Cpu {
    public string Brand;
    public string Socket;
    public string Name;
}

[Database]
public class MotherboardCpu {
    public Motherboard Motherboard;
    public Cpu Cpu;
}

The readonly MotherboardCpuNames property is fully accessible on the database level. The SQL query below works!

var motherboards = Db.SQL<Motherboard>(
    "SELECT o FROM Motherboard o WHERE o.MotherboardCpuNames LIKE ?",
    "%i7-5___%");

Note: String concatenation is a performance expensive task. Make sure to use it properly.

This version of the query above works 25 times faster.

var motherboards = Db.SQL<Motherboard>(
    "SELECT c.Motherboard FROM MotherboardCpu c WHERE c.Cpu.Name LIKE ?", 
    "%i7-5%").Distinct();

2.5. Looking at the objects it is not possible to understand what the entire application is all about

Starcounter gives a simple and clean way to create as many database classes as needed. It is up to the developer to decide what design pattern to use and what architecture to apply.

Conclusion

For any developer, small startup, or huge enterprise company that has experienced any of the issues described above, there is a solution now available. From my personal experience, I have used ASP.NET MVC, and preferred not to use PHP, and respected Ruby on Rails. Now I could not imagine myself switching back to any of these technologies and writing a huge amount of glue code.

To get started, here is a simple project to try in ASP.NET MVC and using Starcounter, which will provide a good comparison of the two technologies.

About the Author

Kostiantyn Cherniavskyi is a senior applications developer at Starcounter with more than six years of experience with C#, ASP.NET, MVC, PHP, database modelling, MS-SQL, and MySQL.