CRUD-операции с использованием PostgreSQL, MySQL и Sequelize

Архитектура управления данными: концепция CRUD

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

> CRUD — это акроним, обозначающий четыре базовые функции, используемые при работе с базами данных: Create (создание), Read (чтение), Update (модификация) и Delete (удаление). > > Intellect.icu

Термин был популяризирован в 1983 году Джеймсом Мартином в его книге по проектированию информационных систем. С тех пор эти четыре операции стали стандартом де-факто для реляционных баз данных, таких как PostgreSQL и MySQL, а также легли в основу архитектурного стиля REST API.

Create: Создание новых записей

Операция создания отвечает за добавление новых данных в систему. В контексте реляционных баз данных это означает добавление новой строки в существующую таблицу. Каждая новая запись обычно получает уникальный идентификатор (Primary Key), который позволяет в дальнейшем обращаться именно к ней.

В языке структурированных запросов (SQL) за операцию создания отвечает команда INSERT INTO.

При выполнении этой команды база данных проверяет соответствие типов данных и ограничения (например, уникальность email). Если проверка пройдена успешно, данные физически сохраняются на диске.

Представим интернет-магазин, который ежедневно регистрирует новых покупателей. Если в день регистрируется 500 новых пользователей, база данных выполняет операцию Create 500 раз. Если каждая запись пользователя занимает 2 килобайта памяти, то за день база данных увеличится на 1000 килобайт (около 1 мегабайта) только за счет новых регистраций.

Read: Извлечение и чтение информации

Чтение — это процесс получения данных из базы без их изменения. Это самая частая операция в большинстве веб-приложений. Когда пользователь открывает ленту новостей, просматривает каталог товаров или заходит в свой профиль, система выполняет операцию Read.

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

Основные возможности операции чтения: * Фильтрация по условиям (поиск конкретных значений) * Сортировка результатов (по алфавиту, по дате, по возрастанию цены) * Ограничение количества возвращаемых строк (пагинация) * Объединение данных из нескольких связанных таблиц

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

Если в таблице магазина находится 100 000 товаров, операция SELECT без фильтрации попытается загрузить все 100 000 записей в оперативную память сервера. Это может привести к сбою. Использование правильных условий фильтрации позволяет извлечь, например, только 20 товаров для одной страницы каталога, что занимает миллисекунды.

Update: Обновление существующих данных

Жизнь данных динамична: пользователи меняют пароли, товары получают скидки, а статьи редактируются. За изменение уже существующих записей отвечает операция Update, которая реализуется через SQL-команду UPDATE.

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

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

Рассмотрим финансовый пример. Допустим, товар стоил 2000 руб., и магазин решил применить скидку в 25%. Новая цена рассчитывается как руб. Система выполняет операцию Update, заменяя старое значение 2000 на новое значение 1500 в соответствующей ячейке базы данных.

Delete: Удаление информации

Последняя операция жизненного цикла — удаление. Она применяется, когда данные больше не нужны системе. В SQL для этого используется команда DELETE.

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

Здесь используется оператор строгого неравенства , который указывает базе данных удалить всех пользователей, чья дата последней авторизации меньше (то есть раньше), чем 1 января 2022 года.

В современной разработке существует два подхода к удалению данных:

Жесткое удаление (Hard Delete): Физическое стирание строки из базы данных с помощью команды DELETE. Данные исчезают навсегда (если нет резервных копий).

Мягкое удаление (Soft Delete): Строка физически остается в таблице, но обновляется специальное поле (например, is_deleted меняется с 0 на 1). Для этой операции фактически используется команда UPDATE, а не DELETE.

Мягкое удаление позволяет восстановить данные при случайном нажатии кнопки "Удалить" пользователем. Если пользователь удаляет свой профиль, система просто скрывает его, меняя статус. База данных размером 50 гигабайт не уменьшится в объеме при мягком удалении, так как физически байты остаются на диске.

Сводная таблица операций

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

| Операция CRUD | Назначение | Команда SQL | Метод HTTP (REST API) | | :--- | :--- | :--- | :--- | | Create | Создание новой сущности | INSERT | POST | | Read | Чтение и поиск данных | SELECT | GET | | Update | Изменение существующих данных | UPDATE | PUT / PATCH | | Delete | Удаление сущности | DELETE | DELETE |

Понимание этих четырех базовых операций является ключом к разработке любых информационных систем. Независимо от того, используете ли вы чистый SQL-код или современные ORM-библиотеки вроде Sequelize, под капотом всегда будут выполняться именно эти фундаментальные действия.

Установка и настройка СУБД PostgreSQL и MySQL

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

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

Сравнение архитектурных особенностей

Прежде чем переходить к установке, важно понимать, какую именно систему выбрать для конкретного проекта. MySQL исторически создавалась с упором на максимальную скорость чтения данных, что сделало ее идеальным выбором для блогов, новостных сайтов и простых интернет-магазинов. PostgreSQL, напротив, изначально проектировалась как строго соответствующая стандартам SQL система с мощным математическим аппаратом и поддержкой сложных типов данных.

| Характеристика | MySQL | PostgreSQL | | :--- | :--- | :--- | | Основной фокус | Высокая скорость простых транзакций | Сложные аналитические запросы и целостность | | Поддержка JSON | Базовая | Продвинутая (индексирование JSONB) | | Типы данных | Стандартные реляционные | Массивы, геоданные, пользовательские типы | | Стандартный порт | 3306 | 5432 | | Лицензия | GNU GPL (принадлежит Oracle) | PostgreSQL License (открытая) |

> Выбор СУБД определяет архитектурные границы программного продукта на годы вперед. Миграция между реляционными базами данных возможна, но всегда сопряжена с высокими рисками потери информации и длительными простоями сервиса. > > Хабр: Разворачиваем MySQL

Рассмотрим пример из практики. Если интернет-магазин обрабатывает 50 000 заказов в сутки, обе базы данных справятся с этой нагрузкой без проблем. Однако, если архитектура приложения требует хранения сложных вложенных структур (например, динамических характеристик товаров, которые меняются от категории к категории), использование формата JSONB в PostgreSQL позволит выполнять поиск по этим характеристикам в десятки раз быстрее, чем в MySQL.

Развертывание и базовая настройка MySQL

Процесс установки MySQL на сервер под управлением операционной системы Linux (на примере дистрибутивов на базе Debian/Ubuntu) состоит из нескольких последовательных шагов.

Обновление индексов пакетов операционной системы.

Установка мета-пакета сервера базы данных.

Запуск встроенного скрипта безопасности для первоначальной настройки.

После установки физических файлов необходимо настроить выделение оперативной памяти. В MySQL за обработку транзакций по умолчанию отвечает подсистема хранения InnoDB. Главный параметр, влияющий на производительность — это размер буферного пула, в котором кэшируются данные и индексы.

Объем памяти для буферного пула рассчитывается по формуле: Общий объем оперативной памяти сервера × 0,7.

Если сервер располагает 16 гигабайтами оперативной памяти, то под нужды InnoDB следует выделить около 11,2 гигабайт. Эта настройка указывается в конфигурационном файле mysqld.cnf.

После настройки конфигурации необходимо создать базу данных и пользователя, от имени которого наше будущее приложение будет выполнять CRUD-операции. Использовать суперпользователя (root) для работы приложения категорически запрещено правилами информационной безопасности.

В этом SQL-скрипте мы создаем изолированную базу shop_db и пользователя app_user, которому разрешено подключаться только с локального сервера (localhost). Команда GRANT выдает права на выполнение любых операций (включая INSERT, SELECT, UPDATE, DELETE) исключительно внутри созданной базы.

Установка и конфигурация PostgreSQL

Установка PostgreSQL также выполняется через пакетный менеджер операционной системы. Однако философия настройки этой СУБД существенно отличается.

Конфигурация PostgreSQL разделена на два основных файла: * postgresql.conf — отвечает за параметры производительности, выделение памяти и сетевые настройки. * pg_hba.conf — управляет правилами аутентификации клиентов (Host-Based Authentication).

Для обеспечения высокой производительности в postgresql.conf настраивается параметр shared_buffers, который определяет объем памяти, используемый базой данных для совместного кэширования. Рекомендуемое значение составляет 25% от доступной оперативной памяти.

Дополнительно настраивается параметр work_mem, который отвечает за память, выделяемую под операции сортировки (например, при использовании ORDER BY). Если в запросе участвует большое количество строк, и мегабайт, база данных начнет использовать медленный жесткий диск для временного хранения данных сортировки, что приведет к резкому падению производительности.

Пример настройки postgresql.conf:

Параметр listen_addresses = '*' указывает серверу принимать подключения со всех IP-адресов. Однако, чтобы внешнее приложение действительно смогло подключиться, необходимо добавить разрешающее правило в файл pg_hba.conf.

Создание пользователя (в терминологии PostgreSQL — роли) и базы данных выглядит следующим образом:

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

Подготовка к интеграции с приложением

После успешной установки, настройки выделения памяти и создания пользователей, СУБД готова к приему подключений. В современной веб-разработке приложения редко отправляют сырые SQL-запросы напрямую. Вместо этого используются ORM-библиотеки (Object-Relational Mapping), которые транслируют код, написанный на языке программирования, в SQL-команды.

Для подключения приложения к настроенной базе данных используется строка подключения (Connection String), которая содержит все необходимые учетные данные в едином формате.

Формат строки подключения: Протокол://Пользователь:Пароль@Хост:Порт/Имя_Базы

Пример для PostgreSQL: postgres://app_user:StrongPass123!@127.0.0.1:5432/shop_db

Пример для MySQL: mysql://app_user:StrongPass123!@127.0.0.1:3306/shop_db

Если количество одновременных подключений к базе данных превышает допустимые лимиты (например, ), прямое подключение может вызвать отказ в обслуживании. В таких случаях между приложением и СУБД устанавливается специальный балансировщик — пулер соединений (например, PgBouncer для PostgreSQL), который удерживает открытые соединения и переиспользует их.

Правильная установка и базовая настройка СУБД — это фундамент, на котором строится стабильная работа всего приложения. В следующих материалах мы рассмотрим, как именно связать программный код на платформе Node.js с подготовленными базами данных с помощью библиотеки Sequelize.

Основы ORM Sequelize и подключение к базам данных

В современной веб-разработке прямое написание SQL-запросов в коде приложения встречается все реже. На смену ручному формированию строк пришла концепция Object-Relational Mapping (ORM) — технология программирования, которая связывает базы данных с концепциями объектно-ориентированных языков.

Вместо того чтобы отправлять в базу данных текстовые команды, разработчик работает с обычными объектами языка программирования. ORM-библиотека берет на себя задачу перевода этих объектов в валидный SQL-код, учитывая особенности конкретной системы управления базами данных (СУБД), будь то PostgreSQL или MySQL.

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

Сравнение подходов: чистый SQL против ORM

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

| Характеристика | Чистый SQL | ORM-библиотека | | :--- | :--- | :--- | | Синтаксис | Строковые литералы, зависящие от диалекта СУБД | Методы объектов и классов языка программирования | | Безопасность | Требует ручной защиты от SQL-инъекций | Автоматическое экранирование параметров | | Миграция БД | Необходимо переписывать запросы при смене СУБД | Достаточно изменить конфигурацию подключения | | Производительность | Максимальная (нет накладных расходов) | Незначительное снижение из-за трансляции кода |

Для платформы Node.js стандартом де-факто стала библиотека Sequelize. Это мощный инструмент, поддерживающий работу с множеством реляционных баз данных и предоставляющий надежные механизмы для выполнения CRUD-операций, управления транзакциями и связями между таблицами.

Инициализация и настройка подключения

Для начала работы с Sequelize необходимо установить саму библиотеку, а также драйвер для выбранной базы данных. Драйвер — это низкоуровневый модуль, который физически устанавливает сетевое соединение с сервером СУБД.

После установки пакетов создается экземпляр класса Sequelize. Существует два основных способа передачи параметров подключения: через единую строку (URI) или через объект конфигурации. Второй способ предпочтительнее для сложных проектов, так как позволяет детально настроить поведение системы.

В приведенном примере конфигурации ключевым параметром является dialect, который указывает библиотеке, какой именно диалект SQL генерировать под капотом. Параметр logging отключает вывод каждого сгенерированного SQL-запроса в консоль, что полезно для производственной среды.

Управление пулом соединений

Особого внимания заслуживает объект pool. Установка соединения с базой данных — это ресурсоемкая операция, требующая времени на сетевое рукопожатие и аутентификацию. Если приложение будет открывать новое соединение для каждого запроса пользователя, сервер быстро исчерпает свои ресурсы.

Для решения этой проблемы используется пул соединений (Connection Pool) — набор заранее открытых и готовых к использованию подключений.

Рассмотрим математику работы пула. Пусть время выполнения одного запроса к БД составляет 50 миллисекунд. Если параметр max установлен в значение 5, пул может одновременно обрабатывать 5 параллельных запросов. Пропускная способность пула = Количество соединений × (1000 мс / Время запроса). В нашем случае: .

Это означает, что приложение сможет обрабатывать до 100 обращений к базе данных в секунду, используя всего 5 физических соединений. Если одновременно поступит запросов, где — количество активных обращений, лишние запросы будут поставлены в очередь ожидания на время до 30 000 миллисекунд (параметр acquire).

Определение моделей данных

После успешного подключения необходимо описать структуру данных. В терминологии ORM таблица базы данных описывается с помощью Модели (Model). Модель представляет собой класс, а каждая строка в таблице — это экземпляр (объект) этого класса.

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

В этом коде метод define создает новую модель. Объект DataTypes используется для абстрагирования типов данных.

DataTypes.INTEGER транслируется в INT для MySQL или INTEGER для PostgreSQL*. * DataTypes.STRING(50) превращается в VARCHAR(50). * Параметр timestamps: true (включен по умолчанию) автоматически добавляет в таблицу колонки createdAt и updatedAt, которые библиотека будет обновлять самостоятельно при любых CRUD-операциях.

Помимо базовых типов, Sequelize поддерживает сложные структуры данных, которые особенно хорошо работают в связке с PostgreSQL. Например, тип DataTypes.JSONB позволяет хранить неструктурированные данные и выполнять по ним быстрый поиск, а DataTypes.UUID генерирует уникальные идентификаторы, которые практически невозможно подобрать, что критически важно для безопасности публичных API.

Синхронизация структуры

Определив модели в коде, необходимо убедиться, что физические таблицы в базе данных соответствуют этому описанию. Sequelize предоставляет механизм автоматической синхронизации через метод sync().

Существует три режима синхронизации:

Стандартный (User.sync()): создает таблицу, только если она не существует. Безопасен, но не применяет изменения, если в код добавлена новая колонка.

Жесткий (User.sync({ force: true })): удаляет существующую таблицу (DROP TABLE IF EXISTS) и создает ее заново. Приводит к полной потере данных. Используется только на этапе начальной разработки или в автоматических тестах.

Мягкий (User.sync({ alter: true })): анализирует текущую структуру таблицы в БД и сравнивает ее с моделью в коде. Генерирует команды ALTER TABLE для добавления или изменения колонок без удаления существующих данных.

Пример применения мягкой синхронизации для всего пула моделей:

Метод authenticate() выполняет легковесный тестовый запрос к серверу, чтобы проверить правильность учетных данных и доступность хоста. Только после успешной проверки запускается процесс синхронизации.

Важно отметить, что в крупных проектах автоматическая синхронизация через sync() считается антипаттерном. Вместо нее используются миграции — специальные файлы, в которых разработчик вручную описывает каждый шаг изменения схемы базы данных. Миграции гарантируют предсказуемость обновлений на боевых серверах и позволяют легко откатить изменения в случае ошибки.

Понимание принципов настройки подключения, управления пулом и определения моделей создает надежный фундамент. Настроенная инфраструктура позволяет беспрепятственно перейти к манипулированию данными: созданию записей, их чтению, обновлению и удалению с использованием объектно-ориентированного подхода.

Реляционные базы данных: проектирование и связи между таблицами

Проектирование структуры хранения информации — фундаментальный этап разработки любого приложения. В основе большинства современных систем лежит реляционная модель данных, которая организует информацию в виде двумерных массивов, называемых таблицами. Каждая строка в такой таблице представляет собой отдельную запись, а столбец — атрибут этой записи.

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

> Реляционная модель представляет базу данных как набор отношений. Отношение — это не что иное, как таблица значений. Каждая строка в таблице обозначает реальный объект или связь. > > Guru99: Реляционная модель данных в СУБД

Нормализация данных

Процесс проектирования базы данных неразрывно связан с нормализацией — приведением структуры таблиц к оптимальному виду. Главная цель нормализации заключается в устранении избыточности данных и предотвращении аномалий при выполнении CRUD-операций.

Существует несколько нормальных форм, но на практике разработчики чаще всего доводят схему до третьей нормальной формы (3НФ).

* Первая нормальная форма (1НФ) требует, чтобы все атрибуты были атомарными (неделимыми). Например, ФИО "Иванов Иван Иванович" лучше разбить на три отдельные колонки: фамилия, имя и отчество. * Вторая нормальная форма (2НФ) обязывает каждую неключевую колонку зависеть от всего первичного ключа целиком. * Третья нормальная форма (3НФ) запрещает транзитивные зависимости — неключевые колонки не должны зависеть от других неключевых колонок.

Если в таблице сотрудников указан отдел ("Бухгалтерия") и телефон этого отдела ("555-1234"), то при изменении номера телефона придется обновлять записи всех бухгалтеров. Вынесение отделов в отдельную таблицу решает эту проблему, оставляя в таблице сотрудников лишь ссылку на отдел. Если в бухгалтерии работает 15 человек, мы заменяем 15 текстовых дубликатов названия и телефона на 15 коротких числовых ссылок.

Ключи: фундамент связей

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

Первичный ключ (Primary Key) — это уникальный идентификатор записи в таблице. Он гарантирует, что в таблице не будет двух абсолютно одинаковых строк. Чаще всего в роли первичного ключа выступает числовое поле id с автоинкрементом.

Внешний ключ (Foreign Key) — это колонка в одной таблице, которая содержит значение первичного ключа из другой таблицы. Именно через внешние ключи физически реализуются связи.

Представим интернет-магазин. В таблице покупателей есть клиент с id равным 15. В таблице заказов создается колонка customer_id. Если этот клиент оформит три покупки на суммы 1200 руб., 3400 руб. и 800 руб., то в таблице заказов появятся три новые строки. В каждой из них поле customer_id будет содержать число 15. Общая сумма покупок клиента составит 5400 руб., при этом его личные данные (имя, телефон) хранятся только в одном месте — в таблице покупателей.

Типы отношений между таблицами

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

| Тип связи | Обозначение | Описание | Пример из реальной жизни | | :--- | :--- | :--- | :--- | | Один к одному | One-to-One (1:1) | Одной записи в первой таблице соответствует строго одна запись во второй. | Пользователь и его паспортные данные. | | Один ко многим | One-to-Many (1:M) | Одной записи в первой таблице может соответствовать несколько записей во второй. | Автор и написанные им статьи. | | Многие ко многим | Many-to-Many (M:N) | Множеству записей первой таблицы соответствует множество записей второй. | Студенты и учебные курсы. |

Реализация связи «Один ко многим»

Связь One-to-Many является самой распространенной в веб-разработке. В ORM-библиотеке Sequelize для ее настройки используются методы hasMany (имеет много) и belongsTo (принадлежит к).

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

Особенности связи «Многие ко многим»

Связь Many-to-Many невозможно реализовать напрямую с помощью двух таблиц. Если один студент может посещать несколько курсов, а один курс содержит множество студентов, добавление внешних ключей в любую из этих таблиц приведет к нарушению структуры данных.

Для решения этой проблемы создается промежуточная таблица (Junction Table). Она содержит как минимум две колонки — внешние ключи, ссылающиеся на обе связываемые таблицы.

Допустим, в онлайн-школе обучается 50 студентов, и доступно 10 различных курсов. Если каждый студент запишется ровно на 3 курса, то в таблице студентов останется 50 записей, в таблице курсов — 10 записей, а в промежуточной таблице появится ровно 150 строк (50 студентов умножить на 3 курса). Максимально возможное количество строк в промежуточной таблице вычисляется как произведение количества записей в связываемых таблицах: .

В Sequelize такая связь настраивается через метод belongsToMany, где обязательно указывается имя промежуточной таблицы через параметр through.

Ссылочная целостность и каскадное удаление

При связывании таблиц возникает проблема управления зависимыми данными. Ссылочная целостность (Referential Integrity) — это набор правил, гарантирующих, что внешние ключи всегда указывают на существующие строки.

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

Запрет удаления (RESTRICT): база данных выдаст ошибку и не позволит удалить пользователя, пока существуют связанные с ним заказы.

Каскадное удаление (CASCADE): при удалении пользователя база данных автоматически удалит все его заказы.

Установка пустого значения (SET NULL): пользователь будет удален, а в колонке customer_id у его заказов появится значение NULL (если колонка это позволяет).

Выбор стратегии зависит от требований бизнеса. Для финансовых операций чаще всего используют RESTRICT, чтобы не потерять историю транзакций, а для пользовательского контента (например, комментариев к удаленной статье) отлично подходит CASCADE.

Грамотное проектирование связей на начальном этапе разработки избавляет от множества проблем с производительностью и консистентностью данных при масштабировании проекта.