Save Helpers

[JordanMarr]

The Idea

I'm interested in adding a layer of "save helpers" (for lack of a better name) on top of the query builders. Just a few light "ORM" style features to eliminate some of the boilerplate code of diffing and saving changes.

The Problem

There is too much boilerplate code required to partition a collection of records in insert, update, delete collections to be saved.
(This is driven by a user posted issue, and also my own projects.)

The Current Workflow

• User wants to save a collection of records
• User must diff records and partition them to be inserted, updated or deleted
• User must perform any inserts, updates and deletes.

My boilerplate process to diff changes and save is as follows:

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

A Better Solution

In order to simplify this problem, I think the following would be a prerequisite:

• The code generation would need to provide a way for the save helper to obtain PK values.

I think this might look like a method that allows the save helper to obtain PK information that can be used to diff and partition the save collection.

I am open to ideas on dealing with this that range in complexity and simplicity. I'm posting this to see if any smart people can help brainstorm and architect a solution (as my brain needs a kickstart).

I specifically do not want to provide a stateful "Change Tracking" mechanism like EF or SQLProvider.)
Instead, I want something to help with the diff'ing.

The simplest solution would:

• Provide a function that partitions the incoming records into rowsToInsert, rowsToUpdate and rowsToDelete collections. The user would still be responsible for insert, updating and deleting. It would use the yet to be generated PK info to do so.
  (Or perhaps the user could just pass in one or more getPK functions instead of needing to generate that information into the types.)

A more complex version might also:

• Create the transaction and do the queries as well.
• Bonus points if you could also give it a list of concurrency fields to check

@jwosty @MangelMaxime @MargaretKrutikova @ntwilson @Jmaharman @devinlyons @EverybodyKurts

[JordanMarr]

This is what I've come up with so far.

let saveUsers(cf: ContextFactory) (users: User seq) =
    async {
        use ctx = cf.OpenContext()

        let! existingRows = 
            selectAsync' (Shared ctx) {
               for u in dbo.Users do
               select u
            }

        let incomingRows : dbo.Users list = 
            incoming
            |> List.map (fun u -> { Id = u.Id; FName = u.FName; LName = u.LName })

        do! Diff.Compare(incomingRows, existingRows , getPK = _.Id)
                .Add(fun added-> 
                    insert {
                        into dbo.Users
                        entities added
                    }
                )
                .Change(fun changed ->
                    update {
                        for row in dbo.Users do
                        set row.FName changed.FName
                        set row.LName changed.LName
                        where (row.Id = changed.Id)
                    }
                .Remove(fun removed -> 
                    delete {
                        for row in dbo.Users do
                        where (row.Id = removed.Id)
                    }
                )
                .SaveAsync(ctx) |> Async.Ignore
    }

Supports:

• Add overload 1: calls your insert query for each added item.
• Add overload 2: calls your insert only once and passes all added items in as an AtLeastOnce collection if count > 0.
• Change: calls your update query for each changed item.
• Remove: calls your delete query for each removed item.
• SaveTask: saves and returns a Task<SaveResult>. Creates a transaction by default.
• SaveAsync: saves and returns an Async<SaveResult>. Creates a transaction by default.

[ntwilson]

Our workflow is pretty different. I'll share a bit about our use-case not because I think SqlHydra should support any of it, but if any of the ideas in here are applicable to our use-case that's maybe an indication that it's got some general application in other areas.
We deal with mostly time series data, and we never update records, we just timestamp them and insert new values (so we can access the data set at any point in time). When we pull in new data, we partition it into 3 categories:

1. Completely new data. As in the PK is one we don't have in our database yet. Obviously these records are then inserted.
2. Duplicate data. As in the PK and all other data besides the timestamp matches what we already have in the database. This data is just skipped with no action taken.
3. Updated data. As in the PK is one we have, but the values are different from the latest values for that PK (note that the values might duplicate some earlier record, but they count as an update if they differ from the latest values). These are inserted.

So that said, if SqlHydra had a way to generate the Primary Keys of each table, that would be immensely helpful for us. We have to manually write out our own PK selectors, and this is brittle and can introduce subtle bugs when a schema is updated.

My other thought is that once you can get a PK from SqlHydra, I feel as if a lot of the functionality just boils down to:

• A Map.intersect (like they have in FSharpPlus) and a Map.diff (which I can't find existing anywhere, but is pretty easy to build)
• Some helper functions for quickly inserting, deleting, updating a seq<'a>

Once you have the existing rows and incoming rows, the partitioning is just 5 lines of code.

module Map =
  let diff left right = Map.fold (fun state key _ -> Map.remove key state) left right

let existingRowMap = existingRows |> List.groupBy primaryKey<dbo.UserProjectFilters> |> Map
let incomingRowMap = incomingRows |> List.groupBy primaryKey<dbo.UserProjectFilters> |> Map
let rowsToAdd = Map.diff incomingRowMap existingRowMap |> Map.values
let rowsToRemove = Map.diff existingRowMap incomingRowMap |> Map.values
let rowsToChange = FSharpPlus.Map.intersect incomingRows existingRows |> Map.values

I don't particularly mind if SqlHydra does that partitioning for you, but it seems a little out of scope to me to have the DB query library do that because it's just general collections processing and not very much code.

Most of the rest of the boilerplate I think just comes from things like the whole match on rowsToAdd to skip a query if there isn't at least one, and the loop through all the rowsToRemove, etc.
How would you feel about making some general-purpose helpers available, like

• insertIfAtLeastOneAsync : seq<'a> -> Async<int> (if the sequence is empty, just async { return 0 })
• removeAll : seq<'a> -> Async<int> (would be possible if SqlHydra has access to PK. Maybe even a single record remove : 'a -> Async<int> would cut some boilerplate)
• updateAll : seq<'a> -> Async<int> (again, would be possible if SqlHydra has access to PK. Maybe even a single record update : 'a -> Async<int> would cut some boilerplate)
  Those could end up being useful in a really broad set of use-cases beyond sort of "ORM-lite" functionality.

[JordanMarr]

Great feedback.

Re: PK generation
I agree that being responsible to write your own PK selector is error prone and should be unnecessary since SqlHydra has access to the PKs already. I'm undecided on the best way to represent PK info it in the generated types.
Maybe a GetPK or GetIdentity method could be added to the record that would:

• return a PK value
• return a tuple of values in the case of a compound PK
• return the entire record if no PK exists for the table

The above Diff helper could add a member constraint for the GetPK method which would eliminate the need to manually provide one.

Re: Helpers
I am definitely interested in identifying and eliminating tedious boilerplate code with helpers.

[JordanMarr]

I feel like the AtLeastOne feature is one that most people dislike using.
The original intent was to avoid starting a task/async block for no reason if the list was empty, but I think that it maybe isn't worth the overhead.

I think I will leave it, but also add a new overload of the entities custom operation that just takes a regular seq and have the Run method return the default of 'InsertReturn in the case where entities is empty.

[MangelMaxime]

Hello @JordanMarr,

I didn't use SqlHydra since sometimes so my knowledge is not so fresh on it.

Not that I don't like it, I am trying to push it on the new project I am working on with a client 😉

With that said, indeed I remember having trouble with the AtLeastOne feature. For reference it was in this related to that issue #39.

I never had the case of need the diff helper that you presents in this discussion, but the exposed API seems to make sense. However, I am unsure how easy it will be to create a contract which is generic enough to cover most use cases.

If this is the case, perhaps something that could be explored is to add it to the documentation and explains to people how it works so they can craft their own version of it? This the approach that was taken in Thoth.Json to cover specific use case which cannot be included in the core library as it is too specific / difficult to abstract.

Doc page

Source code (because the documentation will have a rework soon and I am not sure to guarantee the link validity in the future

[JordanMarr]

Thanks for the feedback, @MangelMaxime.

Providing a sample of the Diff source code without including it into the project does seem like a good strategy to avoid having to maintain a one-size-fits-all utility.