Design patterns for DDD

[nkosi23]

Hello!

I started to use SQLHydra but had to move away from it because I couldn't find a simple and elegant way to use Domain Driven Design. But today I realized that I could have tried to ask the question here.

The main item i struggled with was upserting Root Aggregates. A root aggregate is a top-level entity responsible for managing the internal consistency of multiple records. In practice, this means that reading and writing a root aggregate most of the time means reading and writing from and to multiple tables. The recommended practice therefore is to realize these operations atomically.

I struggled to come up with an elegant and scalable pattern to upsert a root aggregate, and to a lesser extent to load it from the DB. I felt that it was a bit hacky and that I was spending a lot of time writing boilerplate code. Between managing transactions manually and the verbose syntax to write joins, I found my implementation ugly and painful in terms of development experience.

Since the application I am developing is a large scale enterprise application, I need to ensure that things will be elegant and easy to develop even as complexity grows. But just writing read and upsert code for a single root aggregate was quite heavy on boilerplate code, while I got this stuff for free when I was using EF in C#.

I am very much interested in coming back to SQL instead of relying of ORMs, I see value in it. I was enjoying SQLHydra's API a lot before coming across this challenge. But I need to remain pragmatic and ensure that I do not multiply development time by 3 due to the need to write may more boilerplate code. Therefore, my specific question is: what would be the recommended pattern to serialize/deserialize a root aggregate using SQLHydra?

If there was a way to use stored procedure for every database operations I'd even be happy to do so, but i couldn't find a convenient pattern to pass an F# record to a store procedure (for upserts), and when it comes to reads, building a strongly-typed model without relying on magic strings felt painful as well.

But I suspect that it was probably because I was not familiar enough with the capabilities of SQL Hydra. This is the reason I'd appreciate if someone could illustrate how they'd approach this problem with basic pseudo-code samples.

[JordanMarr]

Your post has made me realize that it would be very helpful for the community if I created a comparison of the various data access libraries for F#, either as a blog post or as a simple "Should I Use SqlHydra?" section in the readme.

You are right that SqlHydra is definitely not an ORM like EF. I think of it very much as a "strongly typed SQL" DSL. While I agree that ORM features can be incredibly convenient and useful, I generally prefer to write SQL manually, do my own joins, and handle my own updates. It is more manual work, but I'm generally fine with that because it's less magic behind the scenes as I control everything with SQL.

As you pointed out, the "ORM" features of EF that are missing from SqlHydra are: 1) A little bit easier to query related records without manually writing a join based on FK relationships 2) Ability to analyze mutated properties to generate updates to multiple tables in the graph 3) Ability to analyze which items were added/removed from a related table

I think SqlHydra is a solid enough base now that I am willing to consider scaffolding some light ORM style helpers that could take advantage of code generation to assist in these tasks.

For 1, I can imagine returning related FK information with the generated types that could be used in the Query CE to provide a joinOnFk style operation.

For 2 and 3, I am open to any ideas on helpers, generated or otherwise, to reduce the burden of these kinds of manual updates, as long as it's not fully going down the path of tracking state, etc.

Alternatively, the SQLProvider library already has some of these ORM style features:

• Related tables exist on the generated types as a method
• Entities are classes (not records) and have mutable properties, just like EF. Mutating these properties tracks state so you an just commit the changes.

The funny thing is that I just finished converting a very large enterprise app from SQLProvider to SqlHydra, and in many cases, the SqlHydra was the same or less. The only pain point was for upserts on object graphs where SqlHydra generally resulted in a little bit more code. For my projects, I always map the generated DB entities into DTOs, and so I had to map back from DTO to generated entity regardless, which eliminated much of the benefit of the ORM features around mutable property changes being mapped.

[JordanMarr]

I forgot to ask, are you using SQL Server or something else?

[nkosi23]

Thank you a lot for your comprehensive answer! Indeed you listed a pain point I also encountered that I forgot to mention: how to detect when an item is added or removed from a collection in an object graph: deleting and re-adding all items on every update felt very inefficient, and this is the point that convinced me to move from SQLHydra to Marten.Fsharp instead (a library optimized to turn postgresql into a documentdb) as it looked like opening a can of worms and facing increasingly non-trivial fundamental questions.

We have the same philosophy when it comes to turning entities into DTOs before persisting them! And this is one of the things I love with F#, as doing in so in F# is very convenient and safe while it requires a lot of boilerplate in C#. So i was hoping to use this opportunity to move back from persisting stuff as json documentd to relational data storage as it greatly facilitates querying, analytics, and makes it easier to reuse data outside of the main application.

I will think about the 3 items you listed, full change tracking may not be necessary, I think that enabling the user to indicate in an opt-in mannee if a specific collection (1 to many relationships) should be updated (ie. Removing all records and readding them with their updates) should be enough. All basic properties can and should always be upserted anyway (for DDD), and the same is actually true even for collections if we stick to the blue book, but it may not be efficient in practice. This could be a good tradeoff.

[nkosi23]

@JordanMarr I am using PostgreSQL

[JordanMarr]

@JordanMarr I am using PostgreSQL

The reason I ask is that you can at least use the PostgreSQL onConflictDoUpdate to perform an upsert with your child records:

    /// Inserts an address or updates it if it already exists.
    let upsertAddress address = 
        insertTask (Create openContext) {
            for a in Person.Address do
            entity address
            onConflictDoUpdate a.AddressID (
                a.AddressLine1,
                a.AddressLine2,
                a.City,
                a.StateProvince,
                a.CountryRegion,
                a.PostalCode,
                a.ModifiedDate
            )
        }

[JordanMarr]

Rather than deleting all and re-inserting, I usually do the following instead to find changes:

Prep:

• Create a transaction from the QueryContext
• Pull a list of existing records for that aggregate from DB.

Deletes:

• Create a Set of PK values from incoming records
• Use this to filter the existing records where PK value is not contained in the Set of incoming data (because it was deleted)
• Create a delete query with the resulting idsToDelete

Upserts:

• Put the existing records into a Map with the PK as the key
• Create a for loop through incoming records
  • For each incoming record, lookup up the existing record from the map
  • Check for changes (either comparing the entire record for equality or just check individual columns)
  • If changes are detected, do an upsert. (Or, if you don't care to check for changes, just do an upsert and let PostgreSQL do the diffing for you.)

Cleanup:

• Commit transaction

This pattern works for me, but it is a bit of boilerplate code. I will ponder this to see if there are any patterns we could use to eliminate boilerplate code.

[nkosi23]

This is interesting and similar to the implementation I was moving toward back then (i did come across the upsert feature for postgresql but unfortunately some of the challenges and concerns we mentioned here remained)

As a remark, one thing that can be tricky is that the developer is not necessarily aware of the changes made to the entity when it persists the entity to the database (and therefore cannot be expected to be able to choose between doing a delete or an upsert, both functions would need to be applied every time).

This is often the case with DDD since we tend to encapsulate behaviour precisely so that the developer calling the function does not need to understand the entity-level side effects. For example for a function:

let adjustedOrder = adjustOrder order

The developer does not necessarily know what has changed in the order (nested collections may have been updated etc...). Requiring entity-level side effects to be explicit would deeply affect the way domain models are implemented and prevent the creation of the most useful encapsulations around business logic (polymorphic behaviours etc...).

That being said, the way you have modelled the problem space is very cool and it feels like it may indeed be possible to abstract away the boilerplate after all.

[JordanMarr]

I started to brainstorm today on some helpers to reduce the boilerplate involved with diffing a collection of rows to be saved. For starters, I came up with a DiffService helper that will partition your rows for you:

https://github.com/JordanMarr/SqlHydra/discussions/83#discussioncomment-8509988

That might be a good start.