SQL or NoSQL? That’s the question. How to use both in one application.

A common problem when building SSAS solutions is that some customers want to deploy your application on-premises. Often there are some requirements of what you can install and what not. The database is one limitation, especially when there is an existing SQL server already set up, e.g. backup, maintenance, security, etc. So, how can you use NoSQL for the cloud solution and SQL for the on-premises one?

Here is all the code that you need to save an object, regardless of your choice of a database. You just pass an object and get the id.

var id = await database.CreateItemAsync(item);

For the impatient, you can jump to the repository on GitHub: https://github.com/tachev/NoSQL2SQL.git

We’ll get back to the code later, but let me provide some additional information.

I won’t go into much detail about the differences between NoSQL and SQL databases. The “NoSQL” term is widely used, but it has different interpretations. Basically, those are databases optimized to serve a particular type of data. The most common type of NoSQL is the document databases. They are a good choice when you are building web applications, as you are serving documents. I’ll use this type of NoSQL. What I like most about it is the scalability and the freedom to change the schema. SQL, on the other hand, is well known to most of the database administrators and, from that perspective, seems a better solution for security, backup, etc.

Let me explain how we use both. In our case, NoSQL is serving the cloud application. We can have either database serve the on-premises (standalone) application. To achieve that without creating separate solutions, we created an implementation of NoSQL using any SQL database.

When building a SASS application, you would want to start quickly using the services that the cloud offers. Most of the NoSQL databases have similar API, and at this stage, it doesn’t matter which one you are going to choose. In this case, we choose Cosmos DB (the former DocumentDB).

I believe that the code should be self-explanatory, so I’ll only briefly explain the structure. You can find the full solution on GitHub: https://github.com/tachev/NoSQL2SQL.git

The solution has 5 projects:

• Interfaces – contains the common interfaces.
• CosmosDB and CosmosDBTests – reference implementation for NoSQL. I used CosmosDB in Azure.
• MSSQL and MSSQLTests – reference implementation for SQL. Bind any MS SQL database here. I used MS SQL service in Azure.

You may look at the unit tests for samples of the CRUD operations.

Note that:

• You need to set up your system environment variables:
  • For CosmosDB:
    • documentdb:endpoint – contains the endpoint in Azure.
    • documentdb:authKey – contains one of the authentication keys.
  • For MS SQL:
    • sqldb – contains the connection string to the MS SQL server.
• The code for unit tests is the same (it’s actually the same file). We need a separate project for each database.
• The code for SQL is pretty generic so it can be easily ported to any SQL database.
• The only supported predicate in the MS SQL reference implementation is the type (item => item.Id = itemId).

Let me know if you have any questions or comments how I can improve the blog and/or the source code.

Thanks,
George

Update: Thanks for the feedback. The most voted blog is done: Upsert function