Jezz Santos - 421 - Aug 4, 2014

More of a design question than a specific tech problem. But a starter to learn a little more about how people are using pieces of SS that we are not so familiar with.

We are at a stage in our evolving architecture (sorry that statement might scare some folks) where we need to get more granular with what we can query from our persistence store. Today we use Azure Table storage and our data is arranged into a few columns, some of which contains serialized complex objects (as JSON blobs). We are constrained by Azure to only query on exact matches to column values. which is very limiting, as we cant search for data values within the JSON blobs. 

At some point we may need to go relational for some data, primarily to give us the ability to query on some of the fields in those complex objects. In other words, break them out of serialized blobs into related tables - yes,yes. but we may be able to avoid relations db, if there are other worthy options.

We are not familiar with ORMlite, or other ServiceStack supported tech for storage. 
We can choose to go Azure SQL or other repos and we can choose any translation layer in between.

We are looking at the next step. The goal is to take an incremental step towards creating an opt-in technology pattern (for only some of our data structures) that gives us a better ability to query on specific fields of our data structures.

We are not noobs to the techologies themselves (quite the contrary), just how we might take that next incremental step using something on the SS roadmap that gives us a little more freedom than just plain azure table storage.

Any suggestions?

Stephen Brannan:

For ORM I use nhibernate but I’ve read a lot of great things about ORMLite and it’s a good lightweight and fast alternative. As for a relational database I’ve been using SQL Azure (SQL Server in Azure) with great success. I only use nhibernate because I was using nhibernate before I started using ServiceStack. If this was from scratch I’d would definitely take another look at ORMLite.

Another thought since your using Azure table storage… Maybe consider “indexing” your json blobs by creating index tables in azure table storage. This of course requires a lot of planning to store the data just right for searching. 

In addition if your goal is added searching features… possibly look at using Lucene (not sure about it’s cloud viability though.) Quick search found…

Personally I’ve always liked SQL/RDBMS for data persistence and have never thought of NoSQL as a replacement for RDBMS’s (for general purpose development), more of a compliment to do what SQL isn’t good at, e.g. Redis is utility belt that’s provides fast atomic access to distributed core comp-sci collections that’s great for caching and maintaining other highly transient data and includes many added features like Pub/Sub, great for decoupled cross-platform comms between servers with client bindings on most platforms.

So I view NoSQL as a complement technology as I’ve always preferred storing persistent data in RDBMS’s. Whilst I’ve liked RDBMS’s I’ve just disliked Heavy .NET ORMs which I found needlessly complex and required too much unnecessary configuration to get started. Like most libs/fx’s I believe we should be promoting code-first POCO’s where ever possible which maximizes re-use and interoperability with the least friction. I also dislike most artificial abstractions that have been invented over SQL which I found to be a leaky abstraction that makes it hard to know what query is being generated as well as how to construct a specific SQL query.

Which were basically the reasons behind developing (and now only using) OrmLite - a Fast, Simple, DRY and typed ORM that provides thin layer over SQL to enable a typed, cross-platform API that supports multiple RDBMS’s and high-level common data access patterns like References and Optimistic Concurrency. OrmLite’s API’s are just extension methods over ADO.NET’s underlying IDbConnection which allows seamless interoperability with other Micro ORMs like Dapper that does the same thing. Extension methods are a great way to add features without adding abstractions (and why SS uses them heavily), making it easy to extend OrmLite’s API with your own custom extensions whilst leaving full access to the underlying ADO.NET functionality making it easy to create transactions or executing different command types like Stored Procs as well as Raw SQL if needed.

As OrmLite is a code-first ORM that maps 1:1 to existing tables it requires minimum projection and the least effort to get started. A useful feature coming from Table Storage is its support for transparently blobbing complex types using pluggable type serializers ( which is a productive way to store complex, schema-less, non-relational or non aggregate root data when needed.

I like it because its clean and simple and the API is intuitive (i.e. high affinity with underlying SQL) and the resulting SQL predictable and doesn’t require a heavy context or configuration. Even after many years OrmLite still has one of the best stories for creating tables from POCO’s and Insert external data from DTOs on the fly (i.e. without any config) as seen in:

Jezz Santos:

Yeah, I hear you. generally true for us in the past too. Was clearing the pre-conceived ideas and biases to open ourselves to exploring new tech ideas in the market today (if any).
Going from simple table storage to full blown AzureSQL seems a little YAGNI for most of our stuff at this stage.

Wanted to see if there is anything between the two for now to get some early runs on the board, before we go the full hog RDBMS.