RDBMS support

[alexeyzimarev]

@alexeyzimarev: I've got a very crude implementation of subscriptions using polling: https://github.com/claudiuchis/eventsourcing.sql. I have created tests to cover the event store, aggregate and subscriptions (all stream, single stream), for now using Postgresql. But this implementation can work with any SQL database with very little changes, including Sqlite. (Postgresql has a very basic notification mechanism to observe changes to a table, so that could be used for subscriptions using Postgresql to switch from polling to pushing). If you think this is worth adding to the contrib repo, let me know and I can add support for other SQL databases (MySql, SqlServer, Sqlite).

Originally posted by @claudiuchis in #19 (comment)

[alexeyzimarev]

I can take it to the core lib. I'd say that the first version can implement IEventReader and IEventWriter, and other things like stream deletion and truncation can be done later. I checked SqlStreamStore as well, and I see that they keep a separate table for streams. I'd also do the same, as well as extend it to keep the stream metadata (max age, max count, last known event number).

I see that some people might want to have the checkpoint to be updated in the same transaction as the read models, if both the sub, and its projections are using the same database. It only makes sense for non-idempotent projections, and I personally would not do it, but I can expect some demand for it. I assume it should be possible by passing the transaction as part of the context, but I need to check.

[alexeyzimarev]

@claudiuchis I finished the PR if you want to review it. Found a couple of small unrelated bugs when making subscriptions work... There's a bit of a mismatch between what I expect to be in the checkpoint record (starting from 0) and what the first sequence element is (one), but it works.

[claudiuchis]

@alexeyzimarev All looks good to me. Very nicely done!

For subscriptions, I see that you haven't added any delay if no new messages are available. I guess the network latency between each read (which could be 10 to 50 ms) and the fact that a new connection is created with each read should be enough to avoid hammering the db, even for higher loads, right?

I'll be looking to use this with my next project, and provide more feedback.

[alexeyzimarev]

It's the same way as, for example, NServiceBus SQL transport works. The database doesn't suffer from repeated calls that much when the query is so simple, I can see on a monitoring dashboard of the production MS SQL Server instance that a simple query that hits the database 2K times per minute uses very little CPU (as an example). It bothers people who look at query logs in the Management Studio, and they start to freak out, but in reality it's ok :)

[alexeyzimarev]

Plus, although the connection is created for each call, it comes from the pool anyway.

[alexeyzimarev]

Postgres support release in 0.9.0.

[Tamer-Abdelsalam]

event store, aggregate and subscriptions (all stream, single stream), for now using Postgresql. But this implementation can work with any SQL database

Hello,
I really need for MS SQL database integration with eventStore for a project I am working based on Eventuous, when can this feature take place in Eventuous, please ?

[alexeyzimarev]

MS SQL and Postgres are available. If anyone wants to pick up things like SQLite and MySQL - feel free. I might do MySQL as I want to try it with Aurora.