Best way to befriend sqlalchemy database and eventsourcing

[SkatoriDev]

Spent a lot of time trying to figure out how to make eventsourcing work with actual sqlalchemy (flask + postgresql + sqlalchemy) data. My goal is to dump database data itself using this library (to delegate version control to it)

What is the best way to do it? I was not able to simply inherit my db models from Aggregate class (got conflicting metaclasses error), workarounds didn't seem to help too, maybe you can help me with the solution/architecture?

The goal is to store events, snapshots and other data, connected to the database entries/models, using this library

[johnbywater]

Hi @TheRoD2k,

Thanks for raising the issue. I can help you.

I'm not fully clear what you are trying to do, but if you are looking to use SQLAlchemy for the persistence module, there's an extension package for that here: https://github.com/pyeventsourcing/eventsourcing-sqlalchemy

Otherwise, if that's not what you are trying to do, if you might perhaps explain a little bit more what you are trying to accomplish, that would help me to understand.

Best wishes,

John

[SkatoriDev]

Hi @TheRoD2k,

Thanks for raising the issue. I can help you.

I'm not fully clear what you are trying to do, but if you are looking to use SQLAlchemy for the persistence module, there's an extension package for that here: https://github.com/pyeventsourcing/eventsourcing-sqlalchemy

Otherwise, if that's not what you are trying to do, if you might perhaps explain a little bit more what you are trying to accomplish, that would help me to understand.

Best wishes,

John

I have sqlalchemy Models and I want to store events/changes for every row using eventsourcing My problem is thatI can't figure out how to bind Aggregate (I wrote custom class to avoid the problem of metaclass mixin) to instances of database rows representations I understand that it might be much easier to store copies/diffs of db rows in database itself, but the project is going to use eventsourcing for other purposes as well

What I really struggle to achieve is to make model class a true Aggregate to easily control versions with a minimum changes to the interface

[johnbywater]

Are you try to do "change data capture" (CDC) on rows that get updated?

[SkatoriDev]

Are you try to do "change data capture" (CDC) on rows that get updated?

Quiet close For example: I have a Prices table with lots of columns (~15). On every change I want to save the diff as an event with the ability to restore the state at any moment. For example, if I need to check the Price row for the Product at specific date, I want to restore it from initial state and accumulated diffs. And I want to avoid storing lots all 15 rows, as most of the time only one of them changes. And I also want to avoid using the extra table and logic for it, as eventsourcing lib is going to be used with other data that is not db-exclusive. So it seems like I need to make a db's Model class an aggregate, which I, at the moment, can't figure out how to do.

The solution I came with is to assign the Aggregate for every price instance using additional aggregate_id column. It works, but doesn't feel natural

[johnbywater]

I suppose we first need to think about the command which causes the changes...

Without considering any legacy code you might have, one way to support querying for the state of a Price at a specific date-time is: (1) Write an event sourced application that has Price aggregates that allow the 15 attributes to be assigned new values, so that events will be triggered when the attributes are changed; (2) project the state of this application into an index that supports querying for an aggregate version number by aggregate ID and date-time; (3) use the aggregate version number with the ID to get the aggregate at that time; (4) if the Price aggregates have so many events that reconstructing the state of the aggregate from the events will be too slow, then consider use snapshotting to improve the performance.

[johnbywater]

@TheRoD2k if you are still interested in this issue, please feel free to reopen it. ☺️