Two ORM models for C# and Python. Need a better solution

Question

Python

Greetings.

A description of the current situation is below.

The existing project we were hired to clean up has a fairly large database in active use. Moreover, the company has a large number of customers, respectively, each customer receives its own copy of the database at the time of deploying the solution.

The process is not organized - the versioning of the database is not maintained, refactoring and normalization of the database has been requested for a long time, updating the databases to the new version is done manually by an engineer.

Base - MS SQL Server. The question arose about the use of the ORM layer and the transfer of numerous database changes to the responsibility of the ORM toolkit (including through support for the migration mechanism). The project is written in C#, but there is also a Python geo service that works very closely with the same databases. The Python service is under development and is designed to automate a number of actions with databases. He currently uses SQL Alchemy. Migrations are not configured.

So: we have C# and Python code that use the same base. C# code works directly with the database (ADO.NET), python code uses SQL Alchemy.
To work with the database from the C# code side, we also decided to use ORM. The choice fell on EF 6.0, although this is not important - paid solutions are also considered.

Thus, a not very pleasant situation develops - support for two models at the same time. That is, we change the table - we change both models at once. This is all fine if the models were under our control, but teaching the other party to work with python + two new ORMs + making them commit all changes simultaneously and immediately in two models is almost unrealistic. Therefore, I want to ask the community if anyone has come across this issue and how it was resolved. Ideally, it would be good to maintain the model in only one language.

Here's what we (were) ideas:

1. We just support two ORM models. The above option is not suitable.
2. The most advanced model performs migrations (here we are talking about C # solutions - SQL Alchemy, although powerful, but several of its migration solutions simply do not work with SQL Server), and less simply reverses the database and tracks changes (I doubt that SQL Alchemy is maybe)
3. We describe the model, say, in XML and then parse it with C # and Python parsers and simply update the models.
4. Translation of python code to IronPython. It will not work, because the geo service most likely simply will not work.

Have you experienced something similar? What solution would you use? What can you advise.

Thanks

Answer 1

We need an extra layer of abstraction. Let's say we take a database + EF, write web services on WebAPI, deploy WebAPI. From the application code in C#, we access via REST, from Python in the same way.

Answer 2

This option seems relatively simple:
1) Describe all models in the form of JSON-Schema
2) Write a tool to convert this schema into code. Shouldn't be difficult.

Answer 3

Hm. When using Code First Migrations for Entity Framework, you don’t need to get into the sql structure at all, because when changing models in the context, a command is called that updates the database structure according to the current models - that is, just versioning, and saves the current DB structure in itself code, i.e. when deploying to a clean base, these migration steps are applied sequentially.
By the way, why was python chosen as the second language and how are the projects interacting?

Answer 4

That is, we change the table - we change both models at once.

Absolutely wrong approach when using ORM. More correctly, we change the model, and the ORM engine changes the base.

Answer 5

I would stop at the 2nd option. The difficulty, of course, is that if there are two different VCS repositories, then you are tormented with the synchronization of branches ... JSON / XML / etc parsers of model descriptions are another failure circuit, at least, as a maximum - costly in terms of man hours for development / support.
Another tip: connect the administration and "bend" the other side :)