28

I have a active project that has always used C#, Entity Framework, and SQL Server. However, with the feasibility of NoSQL alternatives daily increasing, I am researching all the implications of switching the project to use MongoDB.

It is obvious that the major transition hurdles would be due to being "schema-less". A good summary of what that implies for languages like C# is found here in the official MongoDB documentation. Here are the most helpful relevant paragraphs (bold added):

Just because MongoDB is schema-less does not mean that your code can handle a schema-less document. Most likely, if you are using a statically typed language like C# or VB.NET, then your code is not flexible and needs to be mapped to a known schema.

There are a number of different ways that a schema can change from one version of your application to the next.

How you handle these is up to you. There are two different strategies: Write an upgrade script. Incrementally update your documents as they are used. The easiest strategy is to write an upgrade script. There is effectively no difference to this method between a relational database (SQL Server, Oracle) and MongoDB. Identify the documents that need to be changed and update them.

Alternatively, and not supportable in most relational databases, is the incremental upgrade. The idea is that your documents get updated as they are used. Documents that are never used never get updated. Because of this, there are some definite pitfalls you will need to be aware of.

First, queries against a schema where half the documents are version 1 and half the documents are version 2 could go awry. For instance, if you rename an element, then your query will need to test both the old element name and the new element name to get all the results.

Second, any incremental upgrade code must stay in the code-base until all the documents have been upgraded. For instance, if there have been 3 versions of a document, [1, 2, and 3] and we remove the upgrade code from version 1 to version 2, any documents that still exist as version 1 are un-upgradeable.

The tooling for managing/creating such an initialization or upgrade scripts in SQL ecosystem is very mature (e.g. Entity Framework Migrations)

While there are similar tools and homemade scripts available for such upgrades in the NoSQL world (though some believe there should not be), there seems to be less consensus on "when" and "how" to run these upgrade scripts. Some suggest after deployment. Unfortunately this approach (when not used in conjunction with incremental updating) can leave the application in an unusable state when attempting to read existing data for which the C# model has changed.

If

"The easiest strategy is to write an upgrade script."

is truly the easiest/recommended approach for static .NET languages like C#, are there existing tools for code-first schema migration in NoSql Databases for those languages? or is the NoSql ecosystem not to that point of maturity?

If you disagree with MongoDB's suggestion, what is a better implementation, and can you give some reference/examples of where I can see that implementation in use?

jth41
  • 3,808
  • 9
  • 59
  • 109
  • There are many types of NoSQL databases and you cannot say about maturity nor tools in general. https://db-engines.com/en/ranking – Andrzej Martyna Mar 19 '18 at 19:38
  • Important source of maturity could be standards like SQL but there are little standards among NoSQLs. Even if one exists like SPARQL for graph databases it is not honored by some of popular implementations. IMHO it is a shame for industry and for a few strong brands that thinks it is good to provide proprietary solutions while it is not. – Andrzej Martyna Mar 19 '18 at 19:45
  • Even if the approach with scripting migrations is the easiest one it would be like trying to mimic SQL DB in NoSQL. Better approach is to accept that there is much dynamism in NoSQL and it should influence coding style and patterns as well – Andrzej Martyna Mar 19 '18 at 20:12
  • @AndrzejMartyna could you give me a better reference of how to handle this NoSQL "dynamism" should influence coding style and patterns in C# than what [I referenced from mongoDb's website](https://mongodb.github.io/mongo-csharp-driver/2.3/reference/bson/mapping/schema_changes/)? The Existence of tooling and documentation seems a far better indication of maturity than mere ["references" approach in the db-engines.com website you refer to](https://db-engines.com/en/ranking_definition. – jth41 Mar 21 '18 at 22:55
  • 2
    The lack of interest in this question combined with the lack of information around the web about this topic lead me to believe there is no available solution to making these technologies work in our production environment without significant investment in rolling our own solution to manage schema changes as our C# models change. – jth41 Mar 21 '18 at 22:55
  • stay tuned ;), I would like to answer the question, in a day or two – Andrzej Martyna Mar 23 '18 at 06:51
  • While interesting question, I'm not sure it fits SO QA format. It's rather opinion-based. – Imre Pühvel Mar 23 '18 at 09:31
  • I'm not sure the community agrees with that assesment given the votes and stars. I'm totally ok with an answer that says something to the effect of "You don't do it like EF code migrations. Here is an explanation and some links to examples of the right way of using C# with NoSQL" – jth41 Mar 23 '18 at 14:40
  • 1
    “if you are using a statically typed language like C# or VB.NET, then your code is not flexible” is an extremely opinionated statement. Be careful with it. – Malachi Sep 28 '18 at 17:16

2 Answers2

28

Short version

Is "The easiest strategy is to write an upgrade script." is truly the easiest/recommended approach for static .NET languages like C#?

No. You could do that, but that's not the strength of NoSQL. Using C# does not change that.

are there existing tools for code-first schema migration in NoSql Databases for those languages?

Not that I'm aware of.

or is the NoSql ecosystem not to that point of maturity?

It's schemaless. I don't think that's the goal or measurement of maturity.

Warnings

First off, I'm rather skeptical that just pushing an existing relational model to NoSql would in a general case solve more problems than it would create.

SQL is for working with relations and on sets of data, noSQL is targeted for working with non-relational data: "islands" with few and/or soft relations. Both are good at what what they are targeting, but they are good at different things. They are not interchangeable. Not without serious effort in data redesign, team mindset and application logic change, possibly invalidating most previous technical design decision and having impact run up to architectural system properties and possibly up to user experience.

Obviously, it may make sense in your case, but definitely do the ROI math before committing.

Dealing with schema change

Assuming you really have good reasons to switch, and schema change management is a key in that, I would suggest to not fight the schemaless nature of NoSQL and embrace it instead. Accept that your data will have different schemas.

Don't do upgrade scripts

.. unless you know your application data set will never-ever grow or change notably. The other SO post you referenced explains it really well. You just can't rely on being able to do this in long term and hence you need a plan B anyway. Might as well start with it and only use schema update scripts if it really is the simpler thing to do for that specific case.

I would maybe add to the argumentation that a good NoSQL-optimized data model is usually optimized for single-item seeks and writes and mass-updates can be significantly heavier compared to SQL, i.e. to update a single field you may have to rewrite a larger portion of the document + maybe handle some denormalizations introduced to reduce the need of lookups in noSQL (and it may not even be transactional). So "large" in NoSql may happen to be significantly smaller and occur faster than you would expect, when measuring in upgrade down-time.

Support multiple schemas concurrently

Having different concurrently "active" schema versions is in practice expected since there is no enforcement anyway and that's the core feature you are buying into by switching to NoSQL in the first place.

Ideally, in noSQL mindset, your logic should be able to work with any input data that meets the requirements a specific process has. It should depend on its required input not your storage model (which also makes universally sense for dependency management to reduce complexity). Maybe logic just depends on a few properties in a single type of document. It should not break if some other fields have changed or there is some extra data added as long as they are not relevant to given specific work to be done. Definitely it should not care if some other model type has had changes. This approach usually implies working on some soft value bags (JSON/dynamic/dictionary/etc).

Even if the storage model is schema-less, then each business logic process has expectations about input model (schema subset) and it should validate it can work with what it's given. Persisted schema version number along model also helps in trickier cases.

As a C# guy, I personally avoid working with dynamic models directly and prefer creating a strongly typed objects to wrap each dynamic storage type. To avoid having to manage N concurrent schema version models (with minimal differences) and constantly upgrade logic layer to support new schema versions, I would implement it as a superset of all currently supported schema versions for given entity and implement any interfaces you need. Of course you could add N more abstraction layers ;) Once some old schema versions have eventually phased out from data, you can simplify your model and get strongly typed support to reach all dependents.

Also, it's important for logic layer should have a fallback or reaction plan should the input model NOT match the requirements for carrying out the intended logic. It's up to app when and where you can auto-upgrade, accept a discard, partial reset or have to direct to some trickier repair queue (up to manual fix if no automatics can cut it) or have to just outright reject the request due to incompatibility.

Yes, there's the problem of querying across sets of models with different versions, so you should always consider those cases as well. You may have to adjust querying logic to query different versions separately and merge results (or accept partial results if acceptable).

There definitely are tradeoffs to consider, sure.

So, migrations?

A downside (if you consider migrations tool set availability) is that you don't have one true schema to auto generate the model or it's changes as the C# model IS the source-of-truth schema you're currently supporting. Actually, quite similar to code-first mindset, but without migrations.

You could implement an incoming model pipe which auto-upgrades the models as they are read and hence reduce the number schema versions you need to support upstream. I would say this is as close to migrations as you get. I don't know any tools to do this for you automatically and I'm not sure I would want it to. There are trade-offs to consider, for example some clients consuming the data may get upgraded with different time-line etc. Upgrade to latest may not always be what you want.

Conclusion

NoSQL is by definition not SQL. Both are cool, but expecting equivalency or interchangeability is bound for trouble.

You still have to consider and manage schema in NoSQL, but if you want one true enforced & guaranteed schema, then consider SQL instead.

Imre Pühvel
  • 4,468
  • 1
  • 34
  • 49
  • Thanks for the answer. The information in your 'Support multiple schemas concurrently' is the sort of explanation I am looking for. Could you add some reference links to some documentation or examples where this sort of approach is being used in C#. – jth41 Mar 23 '18 at 14:37
  • I totally agree with the answer in the way it explain SQL - NoSQL differences and how it affects development work – Andrzej Martyna Mar 23 '18 at 15:09
  • 3
    No doubt! It is good information. I just feel it is lacking part of [what makes for a great answer](https://stackoverflow.com/help/how-to-answer) `The answer can be “don’t do that”, but it should also include “try this instead”... Links to external resources are encouraged` As written, it provides no indication of what next steps I should take to understand the problem domain. What can I reference as an example of how he has suggested to use C# and NoSQL? – jth41 Mar 23 '18 at 16:26
  • I like your approach to follow SO guidance and I would like to learn it also - see what huge answer I put :( Maybe the question is too broad so answer must be more cross-cutting? I haven't found any nice references to include... – Andrzej Martyna Mar 23 '18 at 17:13
  • @jth41 Unfortunately I don't have any direct links or references at hand as it is based on various tried or gathered bits and pieces, personal taste, and mostly based on work on a proprietary project. – Imre Pühvel Mar 23 '18 at 20:17
  • I agree that given answer is not a full ready-to-use solution. That's part of the point I was trying to make. The Q is so broad and there are so many possible solutions with very different properties, with significant smaller and larger details, that delving into specifics would imho be limiting the universal nature and usability of the answer. Even now, it is missing a ton of "buts" and "it depends"-clauses but I hope it at least provided a direction of thinking and emphasized some concerns to tackle that would be helpful for people in OP's position. – Imre Pühvel Mar 23 '18 at 20:24
5

While Imre's answer is really great and I agree with it in every detail I would like to add more to it but also trying to not duplicate information.

Short version

If you plan to migrate your existing C#/EF/SQL project to MongoDB it is a high chance that you shouldn't. It probably works quite well for some time, the team knows it and probably hundreds or more bugs have been already fixed and users are more or less happy with it. This is the real value that you already have. And I mean it. For reasons why you should not replace old code with new code see here: https://www.joelonsoftware.com/2000/04/06/things-you-should-never-do-part-i/.

Also more important than existence of tools for any technology is that it brings value and it works as promised (tooling is secondary).

Disclaimers

  1. I do not like the explanation from mongoDB you cited that claims that statically typed language is an issue here. It is true but only on a basic, superficial level. More on this later.

  2. I do not agree that EF Code First Migration is very mature - though it is really great for development and test environments and it is much, much better than previous .NET database-first approaches but still you have to have your own careful approach for production deployments.

  3. Investing in your own tooling should not be a blocker for you. In fact if the engine you choose would be really great it is worthwhile to write some specific tooling around it. I believe that great teams rarely use tooling "off the shelves". They rather choose technologies wisely and then customize tools to their needs or build new tools around it (probably selling the tool a year or two years later).

Where the front line lays

It is not between statically and dynamically typed languages. This difference is highly overrated. It is more about problem at hand and nature of your schema. Part of the schema is quite static and it will play nicely both in static and dynamic "world" but other part can be naturally changing with time and it fits better for dynamically typed languages but not in the essence of it.

You can easily write code in C# that has a list of pairs (key, value) and thus have dynamism under control. What dynamically typed languages gives you is impression that you call properties directly while in C# you access it by "key". While being easier and prettier to use for developer it does not save you from bigger problems like deploy schema changes, access different versions of schemas etc.

So static/dynamic languages case is not an issue here at all. It is rather drawing a line between data that you want to control from your code (that is involved in any logic) and the other part that you do not have to control strictly. The second part do not have to be explicitly and minutely expressed in schema in your code (it can be rather list or dictionary than named fields/properties because maintaining such fields costs you but does not brings any value).

My Use Case

Once upon a time my team has made a project that uses three different databases:

  1. SQL for "usual" configuration and evidence stuff
  2. Graph database to make it natural to build wide network of arbitrarily connected objects
  3. Document database tuned for searching (Elastic Search in fact) to make searching instant and really modern (like dealing with typos or the like)

Of course it is a challenge to deploy such wide technology stack but each part of it brings its best to the whole solution. The aim of the project is to search through a knowledge base of literally anything (projects, peoples, books, products, documents, simply anything).

That's why SQL is here only to record a list of available "knowledge databases" and users assigned to them. The schema here is obvious, stable and trivial. There is low probability of changes in the future.

Next, graph database allows to literally "throw" anything into the database from different sources around and connect things with each other. The idea, to put it simply, is to have objects accessible by ID.

Next, Elastic search is here to accumulate IDs and a selected subset of properties to make them searchable in the instant. Here the schema contains only ID and list of pairs (key, value).

As the final step, to put it simply, the solution calls Elastic Search, gets Ids and displays details (schema is irrelevant as we treat it as a list of pairs key x value, so GUI is prepared to build screens dynamically).

Though the way to the solution was really painful.

We tested a few graph databases by running concept proofs to find that most of them simply does not work in operations like updating data! (ugh!!!) Finally we have found one good enough DB.

On the other hand finding and using Elastic Search was a great pleasure! Though being great you have to be aware that under pressure of uploading massive data it can break so you have to adjust your tooling to adapt to it.

(so no silver bullet here).

Going into more widely used direction

Apart from my use case which is kind of extreme usually you have sth "in-between".

For example a database for documents. It can have almost static "header" of fields like ID, name, author, and so on and your code can manage it "traditionally" but all other fields could be managed in a way that it can exists or not and can have different contents or structure.

"The header" is the part you decided to make it relevant for the project and controllable by the project. The rest is rather accompanying than crucial (from the project logic point of view).

Different approaches

I would rather recommend to learn about strengths of particular NoSQL database types, find answers why were they created, why are they popular and useful. Then answer in which way they can bring benefits to your project. BTW. This is interesting why you have indicated MongoDB?

The other way around would be to answer what are your project's current greatest weaknesses or greatest challenges from technological point of view - being it performance, cost of support changes, need to scale significantly or other. Then try to answer if some NoSQL DB would be great at resolving the issue.

Conclusion

I'm sure you can find benefits of NoSQL databases to your project either by replacing part of it or by bringing new values to users (searching for example?). Either way I would prefer a really good technology that brings what it promises rather than looking if it is fully supported by tools around it.

And also concept proof is a really good tool to check technologies in a scenario that is very simple but at the same time meaningful for you. But the approach should be not to play with technologies but aggressively and quickly prove or disprove quality of them.

There are so much promises and advertises around that we should protect ourselves by focusing of the real things that works.

Andrzej Martyna
  • 455
  • 1
  • 9
  • 13