Different Database Support

NeoMoritsiqu · December 24, 2024, 11:04am

Hello Sebastian,

Recently, due to the high cost of MongoDB, our customers who have on-premise setups are looking to move away from MongoDB. They have asked us to offer alternative database solutions. What can we do in this regard? What approach should we take? Generally, the requests are centered around Oracle, MSSQL, MySQL, and PostgreSQL. PostgreSQL, for example, has support for storing JSON data, among other features. Could you assist me with this matter?

The customers cannot use cloud services.

What can we do?

Sebastian · December 25, 2024, 10:12pm

Nothing at the moment.

Squidex was designed to be database agnostic but it is a lot of work and I don’t want to build it without someone who actually invests with that. The point is that I use MongoDB for the Squidex Cloud as well and therefore I am really confident that it works and because I would not use these databases, I cannot provide the same level of confidency.

I am talking with a customer who might want to sponsor it, but I cannot really promise anything yet.

NeoMoritsiqu · December 25, 2024, 10:35pm

How much cost and time are we talking about for sponsorship? Maybe I can afford it?

Sebastian · December 26, 2024, 2:35pm

I dont know. A few months of work and also the time for extensive testing.

NeoMoritsiqu · December 26, 2024, 9:54pm

This is an important issue, you can write me about it in detail. In the future, it would be good to explain this kind of support in terms of the product. I would like you to discuss how much we can afford in terms of cost. Also, since you know the product well, what kind of path should be followed, what kind of changes do you foresee in which cs projects, what should be done in terms of infrastructure development and performance, what should be chosen, is it possible to give some detailed information under this article, maybe we can undertake some of the development in addition to financial support

Sebastian · December 28, 2024, 11:55pm

This class is actually good starting point:

github.com

Squidex/squidex/blob/master/backend/src/Squidex/Config/Domain/StoreServices.cs

// ==========================================================================
//  Squidex Headless CMS
// ==========================================================================
//  Copyright (c) Squidex UG (haftungsbeschraenkt)
//  All rights reserved. Licensed under the MIT license.
// ==========================================================================

using System.Net;
using System.Text.Json;
using Microsoft.AspNetCore.Identity;
using Microsoft.Extensions.Caching.Distributed;
using Migrations.Migrations.MongoDb;
using MongoDB.Bson;
using MongoDB.Driver;
using MongoDB.Driver.Core.Extensions.DiagnosticSources;
using Squidex.Domain.Apps.Core.Apps;
using Squidex.Domain.Apps.Core.Assets;
using Squidex.Domain.Apps.Core.Contents;
using Squidex.Domain.Apps.Core.Rules;
using Squidex.Domain.Apps.Core.Schemas;

This file has been truncated. show original

As you can see, all Mongo Services are registered here. For each of the services we need an entity framework equivalent.

Some of the services are in external libraries though, e.g.

So I would start with the libs and implement classes in a way that makes in easy to use a single DbContext. Then just go over them one by one.

There are probably 2 challenges, that are solveable:

Array fields like string[], e.g. for tags
Json columns
Event positions (Event Storage in Postgres - DEV Community)

So we need…

A little bit of infrastructure for context management and migration
Implementation for all these classes.
Improved integration tests, e.g. with Testcontainers
Improved api tests to run them against other databases as well: postgresql, mysql, sql server, oracle.

NeoMoritsiqu · December 29, 2024, 6:40am

There are very detailed changes. Thank you for sharing all this. We will start working on it at the first opportunity. First we need to investigate the databases with common features and the constraints of these features. then we need to progress starting from the library and implemented classes as you said. We would love to make it a solution that will work on different databases next year.

Thanks for everything.

Sebastian · January 13, 2025, 9:59am

I am already working on that:

STEP 1: Migrated YDotnet to Entity Framework
STEP 2: Migrated all Squidex.Libs to Entity Framework
STEP 3: Move Event Store to Squidex.Libs and support Entity Framework
STEP 4: Create single assembly for all Mongo specific code in Squidex and improve tests with test container (when suitable).
STEP 5: Migrate OpenIdDict Core to entity framework.
STEP 6: Implement repositories in Squidex using Entity Framework.

NeoMoritsiqu · January 13, 2025, 10:56am

This is great news… I am excitedly waiting for the result. If there is anything that needs support, I will try to do my best.

Sebastian · January 13, 2025, 11:09am

When I am done with STEP 6 I will prepare a new list of classes to implement and you could do some of them, if you want.

Sebastian · January 14, 2025, 10:36pm

Step 3 is done. Events are migrated and implemented in EF.

Sebastian · January 20, 2025, 8:48am

I have finished a lot of prep work and made a list of todos here: Migrate to Entity Framework Core · Issue #1161 · Squidex/squidex · GitHub

I have to create a project and a little bit of infrastructure first, but then you could implement some of the classes if you want.

Sebastian · January 23, 2025, 12:37pm

I am making good progress. I have implemented a few repositories already (squidex/backend/src/Squidex.Data.EntityFramework/Domain/Apps/Entities/Apps at ef · Squidex/squidex · GitHub)

Furthermore I implemented a custom query builder for contents and assets. it is interesting what you have to do to get sorting working with JSON fields:

github.com

Squidex/squidex/blob/ef/backend/src/Squidex.Data.EntityFramework/Providers/SqlServer/SqlServerDialect.cs#L52


      
          }
          
          protected override string FormatField(PropertyPath path, ClrValue? value, bool isJson)
          {
              if (isJson && path.Count > 1)
              {
                  var sb = new StringBuilder();
          
                  if (value == null)
                  {
                      sb.Append("IIF(");
                      sb.Append("ISNUMERIC(");
                      sb.Append("JSON_VALUE(");
                      sb.AppendJsonPath(path);
                      sb.Append(')'); // END JSON_VALUE
                      sb.Append(')'); // END ISNUMERIC
                      sb.Append(" = 1,");
                      sb.Append("FORMAT(");
                      sb.Append("CAST(");
                      sb.Append("JSON_VALUE(");
                      sb.AppendJsonPath(path);

For every repo there is a test against a real database: squidex/backend/tests/Squidex.Data.Tests/Shared/AppRepositoryTests.cs at ef · Squidex/squidex · GitHub

Therefore I can easily compare different providers and SQL vs Mongo

Sebastian · February 4, 2025, 12:00pm

It is working

NeoMoritsiqu · February 5, 2025, 7:22am

This is great news. I would like to take action quickly on all issues such as testing etc. We can send you all the findings we find. When can we start testing? Is there anything missing from the current mongodb version?

Sebastian · February 5, 2025, 2:57pm

I will merge it this week and then you are good to go. What is missing is full text search because I have no idea how to implement it with SQL. It is just soo limited.