Orchard Harvest 2017–YesSql
Sébastien Ros gave a surprise demo of YesSql, the document database interface over relational databases that powers Orchard Core’s data access. YesSql stores plain objects into documents stored on a relational database. It supports SQL Server, MySql, Postgre, Sqlite, LightningDB, and in-memory. YesSql also allows for querying those objects using queryable indexes. Those indexes are projections of documents into structured tables, that only exist for the purpose of being queried.
YesSql supports map indexes that map one document to one or more index records, and map-reduce indexes that aggregate documents. For all operations, you create a transactional session from a store object. The store object needs to be initialized once before the database can be used for YesSql. The session can save documents using the Save method, which results in a JSON-serialized record in the database. Known classes, as well as anonymous objects can be persisted.
When fetching documents, it’s possible to get a dynamic object when the type is not known. If you know the type, you can GetAsync<KnownType>(id).
An index is built by deriving from IndexProvider<T>. The Describe method defines the mapping from the type T to another type that derives from MapIndex, and has properties that will get serialized as individual columns of the index table in the database. That table can then be queried in rich manners by calling QueryAsync<T, TIndex>(), that can then be chained with Where, OrderBy, and other Linq extensions.
When building an index, it’s possible to pre-filter documents, which means that there is no runtime cost to filtering when the conditions are known in advance.
It’s also possible to perform joined queries between several indexes.
Map/reduce indexes can first do a Map, then usually a Group, and finally compute some aggregate values over the group in Reduce. There is an optional Delete method that describes how to update an index in case of document deletion, to avoid having to re-compute the whole index.
All indexes are automatically maintained.
During questions, Sébastien explained how integer document ids are acquired: the sessions get assigned next ids one by one, and different sessions will be assigned new ids that are guaranteed to be owned by them. The result is not necessarily sequential, but enables clients to know unique ids on creation without round-tripping to the database.