Diffrent 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.