CRUD операции
Тема дорожной карты · PostgreSQL
CRUD операции (создание, чтение, изменение, удаление) представляют собой четыре фундаментальных действия, которые выполняют любые приложения, работающие с базами данных на PostgreSQL. Эти операции являются ключевыми для обеспечения функциональности и эффективности работы с данными в базах данных. Важность CRUD операций заключается в том, что они позволяют приложениям эффективно взаимодействовать с базами данных, обеспечивая возможность создания новых записей, чтения существующих данных, обновления информации и удаления ненужных записей.
CRUD операции используют SQL-операторы SELECT, INSERT, UPDATE и DELETE. Команда INSERT INTO table (col1, col2) VALUES (...) позволяет добавлять новые строки в таблицу, а предложение RETURNING обеспечивает возможность получения сгенерированных значений, таких как первичный ключ типа BIGINT или UUID, без необходимости дополнительного обращения к базе данных. SELECT с фильтрацией (WHERE), сортировкой (ORDER BY) и агрегацией (GROUP BY) является одной из наиболее чувствительных к оптимизации операций, и правильное индексирование и актуальная статистика позволяют планировщику PostgreSQL выбирать наиболее эффективные пути доступа к данным. UPDATE и DELETE поддерживают те же предикаты WHERE и предложение RETURNING, что позволяет получать результаты операций в реальном времени.
Шаблон PostgreSQL INSERT ... ON CONFLICT DO UPDATE (upsert) объединяет создание и изменение в единую атомарную ACID-совместимую операцию, что позволяет выполнять операции в безопасной и контролируемой среде. Этот подход широко применяется в высоконагруженных реляционных базах данных, где необходимость в атомарных операциях особенно высока.
Как это работает
CRUD операции используют SQL-операторы SELECT, INSERT, UPDATE и DELETE. Расширения PostgreSQL включают в себя такие операторы, как INSERT ... ON CONFLICT (col) DO UPDATE (upsert), INSERT ... RETURNING (получить строку без второго SELECT), UPDATE ... WHERE ... RETURNING, DELETE ... RETURNING, а также конструкции Common Table Expressions (CTE) (WITH ... AS), включая writable CTE (WITH inserted AS (INSERT ...) SELECT ...). Параметризованные запросы ($1, $2 в libpq / ? в драйверах) обязательны для обеспечения безопасности приложений и предотвращения SQL-инъекций.
Когда применять
INSERT ... ON CONFLICT используется для идемпотентных записей (без read-then-write race). Параметр RETURNING полезен, когда требуется получить строку сразу после записи. CTE (WITH ... AS) используются для улучшения читаемости сложных запросов; оптимизатор PostgreSQL инлайнит простые non-recursive CTE с версии 12. Всегда параметризуйте запросы — конкатенация строк является первым путем SQL-инъекции. Многошаговые операции должны выполняться в рамках одной транзакции для обеспечения целостности данных.
Типичные ошибки
Типичные ошибки CRUD операций включают использование DELETE FROM tbl без условия WHERE (что приводит к удалению всех данных в таблице); выполнение UPDATE без условия WHERE (что может привести к непреднамеренному обновлению всех записей в таблице); использование огромных списков значений в WHERE id IN (1,2,...,10000) вместо использования = ANY(array) или JOIN (что может привести к перегрузке парсера); отсутствие транзакций для многострочных бизнес-операций (что может привести к частичным записям при сбоях).