Подключение PostgreSQL

Постоянное хранилище отделяет время жизни данных от времени жизни процесса сервера. Четыре курса в памяти исчезают вместе с приложением, а таблица PostgreSQL сохраняется между запусками. В этом уроке заменим реализацию repository и рассмотрим границы соединения, SQL-параметров и асинхронного чтения, сохранив публичные endpoints каталога.

Снимок lesson-14/after требует отдельной, заранее созданной локальной базы professorweb_api_lab. Он не подключается к базе ProfessorWeb и не создаёт объекты автоматически при запуске. SQL, установка зависимости, приложение и запросы при подготовке не выполнялись. Все описанные ответы ожидаемые; реальные права, сеть и совместимость среды требуют последующего выполнения читателем.

Отдельная база и исходная таблица

Для примера выбраны PostgreSQL 17.11 с кодировкой UTF-8 и Npgsql 10.0.0, закреплённый в CatalogApi.csproj. Это намеренно заданные версии курса, а не обещание использовать последний выпуск каждого инструмента. Пакет 10.0.0 опубликован на официальной странице NuGet Npgsql; механизм работы описан в документации проекта.

В пустой учебной базе читатель позднее вручную применяет database/create-and-seed.sql. Его полное содержимое включено в снимок:

-- Only a separately created professorweb_api_lab on an isolated local PostgreSQL.
-- The application NEVER creates, drops or migrates tables automatically.
CREATE TABLE courses (
    id text PRIMARY KEY,
    title text NOT NULL CHECK (char_length(title) BETWEEN 2 AND 120),
    topic text NOT NULL CHECK (topic IN ('frontend', 'publishing')),
    lessons integer NOT NULL CHECK (lessons BETWEEN 1 AND 500)
);
INSERT INTO courses(id,title,topic,lessons) VALUES
('javascript','Современный JavaScript','frontend',20),
('html-css','HTML и CSS','frontend',16),
('performance','Производительность сайта','frontend',12),
('markdown','Статический сайт из Markdown','publishing',12);

Четыре строки повторяют seed предыдущих уроков. ID остаются строковыми: перенос данных в SQL не является причиной заменить адрес /api/courses/javascript новым числом. Первичный ключ гарантирует уникальность, а ограничения фиксируют допустимую тему, длину названия и число уроков. База не полагается исключительно на HTTP-validator, потому что данные могут приходить и из другого разрешённого клиента.

Файл применяется один раз на пустой песочнице. Он не содержит DROP, очистки или автоматического reset. При продолжении с уже подготовленного урока не выполняйте его заново. Для настоящего ручного применения полезно останавливать SQL-сценарий при первой ошибке, например настройкой ON_ERROR_STOP=1 в psql, вместо продолжения частично подготовленной схемы.

Конфигурация и граница доступа

Строка подключения поступает через ConnectionStrings:Catalog. В снимке есть ConnectionStrings.example.txt с заменяемыми обозначениями роли и пароля. Это заметка о формате переменной ConnectionStrings__Catalog, а не настоящий .env и не автоматически загружаемый файл. Создайте отдельную локальную роль и передавайте её данные внешним провайдером; не сохраняйте рабочие секреты в Git и не публикуйте их в HTTP-ответе.

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

В Program.cs создаётся singleton NpgsqlDataSource через фабрику контейнера, а repository зарегистрирован scoped:

builder.Services.AddSingleton<NpgsqlDataSource>(_ => NpgsqlDataSource.Create(connectionString));
builder.Services.AddScoped<ICatalogRepository, PostgresCatalogRepository>();

Data source управляет пулом и служит фабрикой операций. Он рассчитан на совместное использование, а контейнер владеет созданным им объектом и завершает его при остановке host. Мы не создаём singleton открытого соединения и не сохраняем reader в поле сервиса. Scoped-repository лишь получает долговечный data source и выполняет короткие независимые операции.

SQL остаётся внутри repository

Полная реализация чтения находится в Data/PostgresCatalogRepository.cs:

using Npgsql;
namespace CatalogApi;
public sealed class PostgresCatalogRepository(NpgsqlDataSource dataSource) : ICatalogRepository
{
    public async Task<IReadOnlyList<Course>> GetAllAsync(CancellationToken token)
    {
        await using var command = dataSource.CreateCommand(
            "SELECT id, title, topic, lessons FROM courses ORDER BY id");
        await using var reader = await command.ExecuteReaderAsync(token);
        var items = new List<Course>();
        while (await reader.ReadAsync(token))
            items.Add(new(reader.GetString(0), reader.GetString(1), reader.GetString(2), reader.GetInt32(3)));
        return items;
    }
    public async Task<Course?> FindAsync(string id, CancellationToken token)
    {
        await using var command = dataSource.CreateCommand(
            "SELECT id, title, topic, lessons FROM courses WHERE id = $1");
        command.Parameters.Add(new NpgsqlParameter { Value = id });
        await using var reader = await command.ExecuteReaderAsync(token);
        return await reader.ReadAsync(token) ? new(reader.GetString(0), reader.GetString(1), reader.GetString(2), reader.GetInt32(3)) : null;
    }
}

GetAllAsync объявляет явные столбцы и порядок ORDER BY id. Поэтому порядок списка после перехода к PostgreSQL меняется на порядок идентификаторов, а не случайное физическое расположение строк. Значения и общая сумма остаются прежними. Клиент не должен считать предыдущий порядок seed вечным правилом, если такой контракт отдельно не обещан.

FindAsync использует $1, а ID добавляется как параметр команды. Значение не вставляется в SQL конкатенацией. Поэтому строка с кавычкой остаётся данными поиска и не становится частью SQL-синтаксиса. Параметры нужны даже при предполагаемо простом ID: внешний клиент способен отправить значения, которые не были предусмотрены человеком.

ExecuteReaderAsync и каждый ReadAsync получают token запроса. Reader и command обёрнуты в await using, чтобы ресурсы освобождались и при успешном возврате, и при исключении. Чтение завершается внутри метода, после чего наружу выходит обычная модель, а не живой reader с привязанным соединением. Это делает границу владения понятной.

Отсутствие строки даёт null и затем предметный 404. Ошибка доступа, отсутствующая таблица или неработающий сервер базы дают исключение и неожиданный сбой приложения. Нельзя перехватить все такие ситуации и вернуть null: клиент увидит «курс не найден», хотя хранилище вообще не удалось прочитать.

Ожидаемые данные и ограничения

После настоящей подготовки таблицы GET /api/courses/javascript должен сохранить объект с 20 уроками. GET списка возвращает четыре курса, но отсортированные по ID: html-css, javascript, markdown, performance. Фильтр minLessons=16 оставляет первые два соответствующих курса. Число 60 можно получить суммированием значений, однако сам endpoint пока не выдаёт вычисленную сводку.

Repository здесь читает весь набор для списка, а endpoint затем фильтрует его в памяти. Для четырёх записей это удобное сохранение поведения предыдущего шага. Для большой таблицы понадобится отдельный запрос с фильтром, стабильной сортировкой и ограничением страницы. Эти операции входят в продолжение и не считаются оптимизированными автоматически из-за подключения базы.

У строки подключения есть другие настройки, например время ожидания и размер пула. В текущем снимке мы не имитируем подбор production-параметров и не публикуем выдуманные измерения. Значения нужно согласовать с реальной нагрузкой и лимитами отдельного сервера. Также этот Development-проект ещё нельзя выставлять как рабочий сервис без входа, политик доступа и модели эксплуатации.

При чтении строки мы явно доверяем ограниченному порядку столбцов и их NOT NULL-определению. Если изменить SELECT, тип столбца или допускаемость NULL, нужно обновить и создание модели. Сам факт параметризации WHERE не защищает от ошибок отображения схемы. Поэтому SQL и constructor читаются рядом, а изменение таблицы рассматривается вместе с кодом repository.

Роль приложения также не обязана создавать таблицы. Учебную схему может подготовить отдельная разрешённая роль, а runtime-роли достаточно доступа к нужным операциям над этой таблицей. Минимальные права являются отдельным решением среды; пример строки подключения не назначает их автоматически и не оправдывает использование администратора базы.

Публичный API сохранил адреса, а источник данных получил явный ресурсный и асинхронный контракт. В следующем уроке добавим служебное поле хранения и отделим его от объекта, который разрешено возвращать клиенту.