Are databases dead?

I mean relational databases. I happen to be a highly experienced SQL Server and Oracle database developer. At the same time, I have very strong .net and CSharp skills server side and web architecture. It is a rare mix, and whilst many claim they are fullstack, they aren't. The reason is, they give zero respect to databases. They don't understand relational modelling, how to get good performance from databases, the benefits of set based data access.

We now have many ways to completely abstract away the persistence layer, to bring data retrieval into the application layer. Entity Framework, in many ways, is brilliant. It combines the power of linq - a functional approach to data access on objects, and expression tree parsing to SQL.

This is a godsend to many coders who absolutely hate databases. I know it and have seen it. Firstly, SQL developers are seen as crusty weirdos who somehow shouldn't be in development at all.

Then there are NoSQL databases, places where data can get persisted, offers large scalability, although, without the ACID features needed to guarantee data consistency.

Most fullstack development is a myth

It shouldn't be a surprise that each layer within a stack can take months to years to learn. This means we see people specialising despite the management myth that everybody is equally skilled in everything.

The reason why it is preferable for fullstack capabilities is because it cuts down on translation between layers. If your developer is capable of building a decent front-end and building the effective data model, able to decide where business logic should sit - this is far more optimal than having different teams each doing their part.

Technical and Personnel thoughts

The object array is a table?

The one common thing to connect everything is the object array. It happens that this is true;

• A POCO is the same as a record in a table.
• A rowset is a collection of records.
• An IEnumerable is the same as an SQL Resultset.
• Table variables are translatable to IEnumerable.
• The json array object maps perfectly to an IEnumerable and table variable.

SQL Server has some pretty unique features.

• The output inserted.* collects new identity values.
• Table variables can collect inserted identity records.
• Databases maintain transactions.
• Table variables operates outside transactions, in Oracle, this is known as PRAGMA Autonomous.

It means, we can pass in a lot of table variables, do stuff with the data and get data back out. The data layer can become, if chosen to be, idempotent and immutable.

Why doing too much data access in C# with Entity Framework is a terrible idea

Entity Framework is incredible. It lets coders get access to data and if they understand set based data access, engineers proficient with SQL gain a lot through linq. Because .Net requires the assembly to get compiled, any issue with query logic has to be rereleased. As a developer who has worked on large production systems, being able to release an SQL fix as opposed to a new release of a library can be a huge benefit. (Technically, views, functions are compiled but not in the same way).

Another reason why EF is a real issue, is for this reason;

/*pseudo code, so don't shoot me :)*/

var customer = dbset<customer>;
var order = dbset<order>;
var sales = dbset<sales>;

The above is likely to result in multiple roundtrips. Clever people may state, we could do a join on the above BUT maybe we don't want to.
The below is a single round trip.

SELECT * FROM customer;
SELECT * FROM order ;
SELECT * FROM sales ;

Suddenly, we have reduced the number of connections to and from the database.

Table variables and Dapper POCOs

Dapper is an incredible framework which undertakes mapping database objects to C#/.Net objects. Even better, it supports retrieval of multiple datasets in one single query execution.

To get the idea, here is some sample code;

public class typOrder
{
public int OrderId {get;set;}
public DateTime OrderDate {get;set;}
public int CustomerID {get;set;}
public int TotalItems {get;set;}
public decimal OrderCost {get;set;}
}

create type app.typOrder as table 
(
 OrderId int, 
 OrderDate datetime, 
 CustomerID int,
 TotalItems int,
 OrderCost decimal
)

More interestingly IEnumerable = app.typOrder;

What is Database Domain Driven Design (DDDD)

I read the book on DDD, read up on the Complex Event Processing pattern, and the Command Query Responsibility Segregation (CQRS). It struck me how perfect SQL Server is for being able to let significant parts of the data management and processing remain in the database.

Solving the problem of teams specialising

It became clear, the major reason why multi-layer projects fail is because either, there isn't sufficient cross platform skills or the teams fail to communicate with each other.

What one team sees is inherently different to another team. This is why business logic was moved out of the database tier to the business layer.

Model View Controller is a pattern created to solve this. The Model (Data Tier) can do its stuff independently of the View (Presentation Layer). In reality we get many more layers. By creating so much separation, the frontend and backends get obfuscated.

Thinking object arrays. If every layer, fundamentally operates on the concept of object arrays, it becomes easier for each team to talk with other teams. Sure, there may be some extra presentation considerations and some more specific logic needed in the code layer, but typically, what exists in the database is what gets displayed in the front end.

This makes sense because data modelling and record state is a complicated problem best solved by SQL.

So DDDD is what again?

It is letting all layers have aggregate roots, aggregates, value objects and entities. We accept there will be variability in some features of these but, we can look at any layer and see the same structures. This increases understanding across teams.

Example of DDDD

I am not going to write all the code on this post. I have a complete system for doing this on my projects but it is more about the concepts than implementation.

Database

(All parameters/variables are table variables).

create procedure app.SetOrder 
@typOrder app.typOrder
,@typCustomer app.typCustomer
,@typProduct app.typProduct
,@typShipping app.typShipping

AS 

DECLARE @typOrder_OUTPUT app.typOrder
,@typCustomer_OUTPUT app.typCustomer
,@typProduct_OUTPUT app.typProduct
,@typShipping_OUTPUT app.typShipping 

INSERT INTO @typOrder_OUTPUT
SELECT ... FROM ETC

...

SELECT * FROM @typOrder_OUTPUT 
SELECT * FROM @typCustomer_OUTPUT 
SELECT * FROM @typProduct_OUTPUT 
SELECT * FROM @typShipping_OUTPUT 

Hopefully, you follow the above. We pass in table variables, we do stuff with that data and return the results. Importantly, the database is responsible for managing the state of the data and whether data gets returned, committed etc. I left out a lot of code.

People will say, that is too much code, well you can write metadata views to return a lot of the repetitive code to avoid lots of the same effort.

Most importantly, we can see how that above structure could be returned to a front-end MVVM.

public class SetOrder_Data

(
public IEnumerable<typOrder> typOrder{get;set;}
public IEnumerable<typCustomer>typCustomer {get;set;}
public IEnumerable<typProduct>typProduct{get;set;}
public IEnumerable<typShipping>typShipping {get;set;}
}

… 

return JsonData(setOrder_Data);

Within the API controller, we have returned the same object to the client that is in the database. Furthermore, we aren't at risk of overposting attacks because we have decided the objects.

Conclusion on DDDD

This post will be a lot to take in, a lot has been left out. Noteably my code on top of Dapper to do all the data management within C#. I wrote a number of database objects to use metadata to help generate code automatically too.

It may seem like a lot of hard work but it significantly simplifies data representation across layers.