Поддержка нескольких реляционных СУБД в одном проекте: PostgreSQL и MSSQL

Особенности B2B-развёртывания iLocks Online

iLocks Online — программно-аппаратный комплекс для управления безопасностью и контролем доступа в гостиницах, отелях и апартаментах. Система объединяет работу с электронными замками, пользователями, объектами размещения, правами доступа и событиями безопасности.

Продукт ориентирован на корпоративный рынок и разворачивается в инфраструктуре конкретного заказчика. Это важное отличие от SaaS-решений, где разработчик заранее контролирует окружение и может самостоятельно выбрать единый технологический стек для всех клиентов.

В B2B-проектах ситуация обычно сложнее. У клиента уже могут быть собственные стандарты информационной безопасности, требования к размещению данных, внутренняя IT-команда и набор разрешённых технологий. В одной организации PostgreSQL используется как основная СУБД, в другой уже много лет эксплуатируется MSSQL. Если у клиента есть лицензия на MSSQL и специалисты, которые умеют его администрировать, переход на другую СУБД может быть для него нежелателен.

Поэтому в iLocks Online PostgreSQL используется как СУБД по умолчанию, но при необходимости система может быть развёрнута на MSSQL.

Поддержка двух СУБД позволяет гибче проходить технические согласования и учитывать существующую инфраструктуру заказчика. При этом у такого решения есть обратная сторона: поддержка нескольких баз данных добавляет проекту сложности и увеличивает стоимость разработки и сопровождения.

Дополнительная нагрузка появляется в нескольких областях:

конфигурация подключения;
создание и обновление схемы базы данных;
миграции;
типы данных;
последовательности;
интеграционные тесты;
диагностика проблем в разных окружениях.

Поэтому поддержка двух и более СУБД оправдана только тогда, когда за ней стоит явная бизнес-потребность. В нашем случае такая потребность связана с особенностями B2B-развёртывания и инфраструктурой клиентов.

Выбор провайдера базы данных

В проекте используется Entity Framework Core. Для каждой СУБД подключается собственный пакет:

Npgsql.EntityFrameworkCore.PostgreSQL — для PostgreSQL;
Microsoft.EntityFrameworkCore.SqlServer — для MSSQL.

Выбор СУБД выполняется через конфигурацию приложения. Например, в appsettings.json можно хранить тип базы данных и строку подключения:

{
  "Database": {
    "Provider": "PostgreSql",
    "ConnectionString": "Host=localhost;Port=5432;Database=ilocks;Username=postgres;Password=password"
  }
}

Для MSSQL конфигурация будет выглядеть иначе:

{
  "Database": {
    "Provider": "SqlServer",
    "ConnectionString": "Server=localhost;Database=ilocks;Trusted_Connection=True;TrustServerCertificate=True"
  }
}

Тип базы данных описывается перечислением:

public enum DbProvider
{
    PostgreSql,
    SqlServer
}

Настройки подключения можно представить следующим классом:

public sealed class DatabaseOptions
{
    public DbProvider Provider { get; set; }

    public string ConnectionString { get; set; } = string.Empty;
}

При запуске приложения нужная СУБД регистрируется в зависимости от настроек:

public static IServiceCollection AddApplicationDatabase(
    this IServiceCollection services,
    IConfiguration configuration)
{
    var databaseOptions = configuration
        .GetSection("Database")
        .Get<DatabaseOptions>()
        ?? throw new InvalidOperationException(
            "Database configuration is missing.");

    services.AddSingleton(databaseOptions);

    services.AddDbContext<ApplicationDbContext>(options =>
    {
        switch (databaseOptions.Provider)
        {
            case DbProvider.PostgreSql:
                options.UseNpgsql(databaseOptions.ConnectionString);
                break;

            case DbProvider.SqlServer:
                options.UseSqlServer(databaseOptions.ConnectionString);
                break;
        }
    });

    return services;
}

При таком подходе прикладной код не должен знать, какая именно СУБД используется в конкретной инсталляции. Сервисы и обработчики работают с одним ApplicationDbContext, а конкретный пакет доступа к базе выбирается на этапе запуска приложения.

Это позволяет сохранить общими:

бизнес-логику;
сервисы;
репозитории;
большую часть конфигурации сущностей;
LINQ-запросы;
логику заполнения справочников.

Различия остаются только там, где они действительно связаны с особенностями конкретной СУБД.

Миграции и начальное заполнение базы

Общая логика применения миграций может выглядеть одинаково для обеих СУБД:

public static async Task InitializeDatabaseAsync(
    IServiceProvider serviceProvider,
    CancellationToken cancellationToken = default)
{
    await using var scope = serviceProvider
        .CreateAsyncScope();

    var dbContext = scope.ServiceProvider
        .GetRequiredService<ApplicationDbContext>();

    await dbContext.Database.MigrateAsync(cancellationToken);

    await SeedDataAsync(dbContext, cancellationToken);
}

При этом для PostgreSQL и MSSQL создаются отдельные наборы миграций. Миграция, сгенерированная для одной СУБД, не является универсальной и не должна рассматриваться как миграция для другой.

EF Core учитывает выбранный пакет доступа к базе при генерации миграций. В результате SQL и операции изменения схемы будут соответствовать конкретной СУБД. Поэтому миграции для PostgreSQL и MSSQL необходимо создавать, хранить и проверять отдельно.

Общей при этом остаётся логика запуска миграций и первоначального заполнения базы:

await dbContext.Database.MigrateAsync(cancellationToken);

await SeedDataAsync(dbContext, cancellationToken);

Почему для этого проекта выбран Entity Framework Core

Главная причина использования Entity Framework Core — возможность описывать большую часть работы с данными на уровне общей модели и LINQ, не привязывая прикладной код к синтаксису конкретной СУБД.

У MSSQL используется собственный диалект SQL — T-SQL, или Transact-SQL. Он расширяет стандартный SQL конструкциями и функциями, специфичными для платформы Microsoft.

У PostgreSQL также есть собственный диалект SQL — обычно его называют диалектом SQL PostgreSQL. Кроме того, PostgreSQL поддерживает процедурный язык PL/pgSQL, который используется, например, для функций и хранимых процедур.

Различия между СУБД проявляются не только в названии диалекта. Отличаться могут:

синтаксис ограничения количества записей;
функции работы со строками и датами;
выражения для генерации идентификаторов;
типы данных;
поведение NULL;
именование объектов;
синтаксис изменения схемы;
особенности блокировок и конкурентного доступа.

Например, один и тот же запрос с ограничением количества записей может выглядеть по-разному:

-- MSSQL
SELECT TOP (@limit)
    *
FROM Locks
WHERE IsActive = 1
ORDER BY Name;

-- PostgreSQL
SELECT
    *
FROM "Locks"
WHERE "IsActive" = TRUE
ORDER BY "Name"
LIMIT @limit;

В прикладном коде этот сценарий можно описать единым LINQ-запросом:

var activeLocks = await dbContext.Locks
    .Where(lockEntity => lockEntity.IsActive)
    .OrderBy(lockEntity => lockEntity.Name)
    .Take(limit)
    .ToListAsync(cancellationToken);

EF Core преобразует LINQ-выражение в SQL с учётом выбранной СУБД.

Такой подход позволяет убрать из прикладного кода большую часть различий между базами. Нам не нужно самостоятельно поддерживать две реализации каждого стандартного сценария работы с данными.

Если же писать запросы вручную для каждой СУБД, со временем появляются несколько рисков.

Во-первых, реализации могут начать расходиться. В один запрос добавляется новое условие, сортировка или ограничение, а в другой это изменение забывается.

Во-вторых, исправление ошибки может попасть только в одну версию запроса. Тогда проблема будет проявляться исключительно у клиентов, использующих определённую СУБД.

В-третьих, усложняется тестирование. Вместо проверки одной бизнес-операции команде приходится поддерживать несколько реализаций одного и того же сценария.

Поэтому, если проект изначально должен работать с двумя или более СУБД, большую часть прикладных запросов следует писать на LINQ. Это уменьшает дублирование, снижает вероятность расхождения поведения и делает рефакторинг безопаснее.

При этом задача не состоит в том, чтобы полностью запретить SQL. В отдельных местах он может быть необходим или удобнее LINQ. Важно другое: не допускать случайного распространения кода, зависящего от конкретной СУБД, по всему приложению. Если SQL-код всё же нужен, его следует изолировать и отдельно проверять для соответствующей базы.

С какими сложностями пришлось работать

Проверка миграций для разных СУБД

Для PostgreSQL и MSSQL создаются разные наборы миграций. Каждая из них должна проверяться отдельно на той СУБД, для которой была сгенерирована.

Недостаточно убедиться, что миграция успешно создалась в проекте. Необходимо проверить, что она:

применяется к пустой базе;
корректно обновляет существующую базу;
работает после последовательного применения предыдущих миграций;
не приводит к потере данных;
создаёт структуру, соответствующую ожиданиям приложения;
учитывает особенности конкретной СУБД.

Особенно внимательно приходится проверять:

изменение типов колонок;
добавление значений по умолчанию;
переименование таблиц и колонок;
создание последовательностей;
изменение ограничений;
миграции, связанные с датами и временем.

Для каждой СУБД необходимо иметь возможность развернуть базу с нуля, применить полный набор миграций и проверить работу основных сценариев приложения.

Пользовательские sequence (последовательности)

В iLocks Online используются пользовательские sequence (последовательности). Они применяются, например, для генерации идентификаторов сущностей.

Проблема заключается в том, что синтаксис использования sequence в PostgreSQL и MSSQL отличается.

Для PostgreSQL значение по умолчанию можно задать следующим образом:

builder.Property(entity => entity.Id)
    .HasDefaultValueSql(
        "nextval('\"seqTestName\"')");

Для MSSQL используется другой синтаксис:

builder.Property(entity => entity.Id)
    .HasDefaultValueSql(
        "(NEXT VALUE FOR [seqTestName])");

Чтобы не смешивать эти различия с общей конфигурацией сущности, настройку можно применять отдельно в OnModelCreating:

protected override void OnModelCreating(
    ModelBuilder modelBuilder)
{
    modelBuilder.HasSequence("seqTestName");
    base.OnModelCreating(modelBuilder);

    var entityBuilder = modelBuilder.Entity<TestEntity>();

    entityBuilder.ToTable("TestEntities");

    entityBuilder.HasKey(entity => entity.Id);

    entityBuilder.Property(entity => entity.Id)
        .ValueGeneratedOnAdd();

    if (Database.IsNpgsql())
    {
        entityBuilder.Property(entity => entity.Id)
            .HasDefaultValueSql(
                "nextval('\"seqTestName\"')");
    }
    else if (Database.IsSqlServer())
    {
        entityBuilder.Property(entity => entity.Id)
            .HasDefaultValueSql(
                "(NEXT VALUE FOR [seqTestName])");
    }
}

Общая конфигурация сущности остаётся единой, а различается только SQL-выражение, используемое для генерации идентификатора.

Важно проверять не только создание миграции, но и фактическую вставку данных в обе базы. Sequence должна корректно создаваться, использоваться и продолжать работу после обновления схемы.

Различия в типах данных

EF Core предоставляет общий API для настройки типов, но поведение самих СУБД всё равно может отличаться.

На практике особенно внимательно нужно работать со следующими типами:

DateTime (UTC или местное время);
Guid (поддержка UUIDv4, UUIDv7);
строковыми типами (максимальной и минимальной длиной);
JSON-данными (JSON в MSSQL, JSONB в Postgres);
числовыми типами, если для них важна точность.

Для дат необходимо заранее определить, в каком виде они хранятся в системе: в UTC, в локальном времени или вместе с часовым смещением. Если это решение не зафиксировано, настройки окружения и особенности СУБД могут привести к разному поведению приложения.

Например, для времени создания сущностей используется единый подход:

builder.Property(entity => entity.CreatedAt)
    .IsRequired();

Но одной настройки модели недостаточно. Важно, чтобы приложение одинаково формировало и интерпретировало это значение независимо от выбранной базы.

Регистрозависимость и имена объектов

PostgreSQL и MSSQL могут по-разному обрабатывать имена таблиц, колонок и значения при сравнении.

Чтобы схема была предсказуемой, команда придерживается нескольких правил:

явно задаёт имена таблиц и колонок;
использует единый стиль именования;
не полагается на автоматическое преобразование регистра;
избегает имён, которые могут быть ключевыми словами одной из СУБД;
проверяет запросы на обеих базах.

Например:

builder.ToTable("AccessEvents");

builder.Property(entity => entity.CreatedAt)
    .HasColumnName("CreatedAt");

Такая конфигурация не устраняет все различия между СУБД, но снижает зависимость от настроек конкретного окружения.

Как проверяется совместимость с двумя СУБД

Подключение двух пакетов само по себе ещё не означает полноценную совместимость приложения с двумя базами данных. Необходимо проверять весь жизненный цикл каждой поддерживаемой СУБД.

Важные сценарии включают:

создание базы с нуля;
применение полного набора миграций;
обновление существующей базы;
повторный запуск миграций;
заполнение справочников;
создание и изменение основных сущностей;
фильтрацию и сортировку;
пагинацию;
работу с датами;
операции с sequence;
конкурентные изменения.

Ключевые интеграционные тесты запускаются отдельно на PostgreSQL и MSSQL. Для этого используются реальные экземпляры баз данных, а не только заглушки или in-memory-провайдеры.

Сценарии, зависящие от конкретной SQL-функции, структуры индекса или особенностей выбранной СУБД, также необходимо проверять интеграционными тестами. Это позволяет убедиться не только в корректности кода приложения, но и в фактическом взаимодействии с базой данных:

какой SQL генерирует EF Core;
как применяются отдельные миграции;
как создаются sequence;
одинаково ли работают основные операции;
сохраняется ли целостность данных после изменений схемы.

Выводы

Поддержка PostgreSQL и MSSQL в iLocks Online появилась как ответ на конкретную бизнес-потребность. Продукт разворачивается в инфраструктуре заказчика, поэтому команда не всегда может выбирать СУБД самостоятельно. Возможность использовать уже имеющийся у клиента MSSQL упрощает внедрение системы и позволяет учитывать его корпоративные стандарты.

Чтобы сохранить единую кодовую базу, общую работу с данными необходимо строить на Entity Framework Core и LINQ. Различия между СУБД при этом не исчезают, а изолируются в отдельных наборах миграций, настройках типов данных, конфигурации sequence и других местах, где поведение PostgreSQL и MSSQL отличается.

Отдельное внимание нужно уделять не только запуску приложения, но и целостности данных. Поддержка двух СУБД должна означать, что операции создания и изменения схемы, обновления сущностей и заполнения справочников приводят к согласованному результату в каждой базе. Именно поэтому миграции и ключевые интеграционные сценарии необходимо проверять на реальных экземплярах обеих СУБД.

Поддержка нескольких СУБД оправдана тогда, когда она помогает решать реальные задачи продукта или бизнеса. Для iLocks Online это требование B2B-рынка и разнообразие инфраструктуры клиентов. Такой подход даёт заказчикам больше свободы при развёртывании системы, а продукту — возможность работать в организациях с разными корпоративными стандартами, сохраняя единую кодовую базу.