Зміна даних: INSERT, UPDATE, DELETE

Що таке DML і чим зміна даних небезпечніша за читання?

DML (data manipulation language) — це команди, що змінюють вміст таблиць: INSERT, UPDATE, DELETE. Різниця з SELECT принципова: читання нічого не ламає, найгірший його наслідок — повільний запит, а от одна команда зміни без WHERE здатна зіпсувати дані всього спільного стенда — і розгрібати наслідки доведеться всій команді. Більшість клієнтів працює в режимі autocommit — кожен запит фіксується миттєво, кнопки «скасувати» після цього немає. Саме тому в багатьох компаніях QA має на прод лише read-only доступ: хто має право на UPDATE, той має і всі шанси на аварію. Без DML тестувальнику все одно нікуди: підготувати дані, відтворити баг «саме з таким станом», прибрати за автотестом — усе це зміна даних.

Навіщо в INSERT явно перелічувати стовпці, якщо можна передати значення по порядку?

Явний перелік стовпців — дешева страховка від зміни схеми. Форма без переліку (INSERT INTO users VALUES (...)) прив'язана до фізичного порядку колонок: варто комусь додати в таблицю новий стовпець — і всі такі вставки або почнуть падати, або, що гірше, мовчки розкладуть значення не в ті поля. Другий бонус — самодокументація: запит із переліком читається без звірки зі схемою, одразу видно, що саме вставляється. Це той випадок, де кілька зайвих слів у запиті економлять годину розслідування.

Що станеться зі стовпцями, яких немає в переліку INSERT?

Вони отримають значення за замовчуванням (DEFAULT) або NULL, якщо дефолту немає. Якщо ж на стовпці стоїть обмеження NOT NULL без дефолту, вставка впаде з помилкою — у PostgreSQL це null value in column ... violates not-null constraint. Для QA це не перешкода, а перший шар перевірок цілісності: обмеження в базі — теж вимоги, і їх теж тестують. Тобто «вставка не пройшла через NOT NULL» — це не завжди баг вашого запиту, іноді це база коректно захищає інваріант, який хтось намагається порушити.

Що робить INSERT ... SELECT і чому його небезпечно запускати наосліп на живому стенді?

INSERT ... SELECT бере значення не зі списку VALUES, а з результату запиту — стандартний спосіб розмножити тестові дані або скопіювати зріз у таблицю-пісочницю. Небезпека в тому, що вставиться рівно стільки рядків, скільки поверне SELECT: може бути нуль, а може мільйон, і ви це побачите вже постфактум. Тому перед INSERT ... SELECT на живому стенді той самий SELECT варто запустити окремо й подивитися на обсяг. Це той самий принцип, що й із WHERE в UPDATE: спершу очима переконайся, скільки рядків зачепить операція, а вже потім виконуй.

Чому id у таблиці можуть іти дірками (1, 2, 5, 9)? Це баг «записи зникають»?

Ні, це нормальне життя автоінкремента. Послідовність (sequence) віддає чергове значення ще до завершення вставки. Якщо транзакція відкотилась або INSERT упав на обмеженні — видане значення назад не повертається, воно просто пропадає, лишаючи дірку. Тому 1, 2, 5, 9 — не «у нас зникають записи», а сліди відкатів і невдалих вставок. Ключова теза: автоінкремент гарантує унікальність і зростання, але не суцільність. Якщо хтось заводить баг «стрибають id», перше питання — а чи справді зникли самі рядки, чи це лише пропуски в нумерації.

Як отримати id щойно вставленого рядка?

Залежить від СУБД — це типове діалектне питання. У PostgreSQL найелегантніше через RETURNING: вставка одразу повертає вказані стовпці створеного рядка (INSERT ... RETURNING id, created_at). У MySQL конструкції RETURNING немає — після вставки викликають функцію LAST_INSERT_ID(), а в SQL Server схожу роль грає OUTPUT. Сам механізм є скрізь, різниться синтаксис. Практичний сенс задачі: вставив рядок — треба його id, щоб передати далі (у наступний запит, в API-виклик, у прибирання автотеста). Саме тому RETURNING у фікстурах — стандартний спосіб зробити тест, який уміє прибрати за собою: створив користувача, забрав id, у teardown видалив рівно його. Бонус — RETURNING працює також з UPDATE і DELETE, показуючи, що саме ви змінили чи видалили.

Що станеться, якщо виконати UPDATE без WHERE? Як цьому запобігти?

WHERE в UPDATE синтаксично необов'язковий, тому UPDATE users SET status = 'blocked' — валідний запит, що змінить статус кожного рядка таблиці. Жодного «ви впевнені?» база не покаже, а в режимі autocommit зміна зафіксується миттєво — скасувати вже не вийде. Запобігання — це робочий ритуал, а не одне визначення: спершу SELECT з тією самою умовою WHERE, щоб очима побачити, що саме зачепить зміна; потім BEGIN (відкрити транзакцію); потім UPDATE; потім подивитися на affected rows — якщо очікували 1 рядок, а зачепило 20 000, то ROLLBACK, і нічого не сталося; і лише коли число збігається — COMMIT. На співбесіді інтерв'юер слухає саме цей ритуал, а не переказ визначення: він перевіряє реальний досвід зі стендами.

UPDATE відпрацював без помилки, але affected rows = 0. Це успіх?

Формально помилки немає, але майже завжди це означає, що WHERE нікого не знайшов — і зміна просто не відбулась. Причини типові: одрук у значенні умови, не те середовище, або дані вже змінені кимось іншим раніше. Тому кількість зачеплених рядків — корисний оракул в обидва боки: забагато рядків сигналить про надто широкий WHERE, нуль рядків — про надто вузький або хибний. Практичний висновок: у скриптах і перевірках орієнтуйтеся на affected rows, а не лише на відсутність помилки, інакше «зелений» результат маскуватиме те, що операція нічого не зробила.

Чим DELETE відрізняється від TRUNCATE?

DELETE — звичайна DML-операція: проходить рядок за рядком, підтримує WHERE (можна видалити частину), для кожного рядка спрацьовують тригери на видалення, усе відбувається в транзакції і відкочується ROLLBACK-ом. TRUNCATE миттєво спорожняє всю таблицю: WHERE він не підтримує (тільки все), рядки не перебирає (тому й швидкий на великих таблицях), тригери на видалення не смикає. Слабка відповідь на співбесіді — «TRUNCATE швидший»; сильна розкладає по осях: вибірковість, тригери, транзакційність, лічильники автоінкремента. Є ще важлива діалектна різниця, і називати її треба з обох боків: у PostgreSQL TRUNCATE транзакційно безпечний щодо даних і відкотиться, якщо транзакція навколо не закомітилась, а в MySQL/InnoDB він спричиняє неявний commit — відкату не підлягає, і сам довідник MySQL класифікує його як DDL, а не DML.

Чим TRUNCATE відрізняється від DROP TABLE?

TRUNCATE прибирає рядки, але лишає саму таблицю з її структурою, індексами й обмеженнями — після нього таблиця порожня, але існує. DROP TABLE знищує саму таблицю: зникають і дані, і структура, і індекси, і обмеження — об'єкта більше немає. TRUNCATE за поведінкою ближчий до DDL, DROP — чистий DDL і інструмент міграцій та адміністрування. Для QA практичний висновок: повне скидання таблиць у своїй ізольованій базі перед прогоном — TRUNCATE, бо швидко й таблиця лишається; а DROP у ручній роботі тестувальника робити нічого — ним керують міграції.

Чи можна відкотити TRUNCATE транзакцією?

Залежить від СУБД, і це класична пастка, тому відповідати треба обома діалектами одразу. У PostgreSQL TRUNCATE транзакційно безпечний щодо даних: усічення буде коректно відкочене, якщо транзакція навколо не закомітилась, тож BEGIN ... ROLLBACK справді повертає дані на місце (разом із RESTART IDENTITY, якщо його вказали). У MySQL/InnoDB TRUNCATE спричиняє неявний commit — він фіксується одразу і відкату не підлягає; довідник MySQL і класифікує його як DDL, а не DML, бо задля швидкодії команда обходить DML-механізм видалення й перестворює таблицю. Обидві доки праві: PostgreSQL має транзакційний DDL, MySQL — ні, а стандарт частину поведінки цієї команди лишає на розсуд реалізації. Практичне правило: не покладайтеся на відкат TRUNCATE без певності щодо конкретної бази — на MySQL «спробую в транзакції, а якщо що, відкочу» знищує дані незворотно і без жодної помилки. Заодно лічильники автоінкремента поводяться по-різному — MySQL при TRUNCATE скидає AUTO_INCREMENT на початок, PostgreSQL за замовчуванням послідовності не чіпає (для скидання є RESTART IDENTITY).

Чому TRUNCATE таблиці іноді не спрацьовує, а база повертає помилку?

Найчастіша причина — на цю таблицю посилаються зовнішні ключі з інших таблиць. TRUNCATE таку таблицю просто так не спорожнить: інакше в залежних таблицях лишилися б сирітські записи, що посилаються на вже неіснуючі рядки, а це порушення цілісності. У PostgreSQL є два виходи: усікати пов'язані таблиці разом однією командою, або явно написати TRUNCATE ... CASCADE — і тоді спорожніють і залежні таблиці. Останнє — гарна ілюстрація, чому руйнівні команди треба читати двічі: CASCADE тихо винесе дані з таблиць, які ви прямо не називали.

Що таке upsert і яку проблему він вирішує?

Upsert — атомарна операція «вставити, а якщо запис уже є — оновити». Він вирішує проблему гонки в наївному підході «спершу SELECT, чи існує, потім INSERT»: між вашим SELECT і INSERT той самий запис може створити хтось інший (паралельний тест, інший інженер), і ваш INSERT упаде на унікальному обмеженні з duplicate key value violates unique constraint. У PostgreSQL upsert — це INSERT ... ON CONFLICT (...) DO UPDATE (або DO NOTHING), у MySQL — INSERT ... ON DUPLICATE KEY UPDATE, у стандарті SQL — команда MERGE. Головне не синтаксис, а навіщо: upsert робить підготовку даних ідемпотентною (idempotent) — повторний запуск сіда не падає і не плодить дублікатів. Для автотестів це властивість номер один.

Видаляємо користувача — що станеться з його замовленнями?

Правильна відповідь починається з «залежить від правила на зовнішньому ключі», а не з одного сценарію. За замовчуванням FK поводиться як заборона (RESTRICT / NO ACTION): видалити користувача, на якого посилаються замовлення, не вийде — база поверне помилку порушення зовнішнього ключа (у PostgreSQL SQLSTATE 23503). ON DELETE CASCADE розвертає логіку: видалення батька автоматично зносить усіх нащадків, і ланцюжок може бути довгим — користувач → замовлення → позиції замовлень. ON DELETE SET NULL — компроміс: діти живуть далі, але посилання обнуляється (orders.user_id = NULL). Для тестувальника наслідок прямий: ваш «маленький» DELETE одного рядка на стенді з каскадами може тихо винести половину пов'язаних даних, тому схему FK треба знати до видалення, а не з'ясовувати після.

Що таке сирітські записи (orphaned records) і звідки вони беруться?

Сирітські записи — рядки, що логічно посилаються на батьківську сутність, якої вже немає. Найчастіша причина — коли зовнішнього ключа в схемі взагалі немає (так роблять заради швидкості або через легасі): база не боронить видалити батька, дочірні рядки лишаються висіти в порожнечу і потім ламають звіти та JOIN-и (рядок просто випадає з внутрішнього з'єднання). Це протилежність каскаду: там FK є і чистить нащадків автоматично, тут FK немає і ніхто не чистить. Пошук сирітських записів — типова перевірка цілісності бази, яку QA робить окремим запитом (LEFT JOIN з умовою, що батьківський ключ IS NULL). Це один із класів багів «дані ніби є, а звіти брешуть».

Чи можна змінювати дані напряму в базі тестового стенда?

Це питання про зрілість, і категоричне «ніколи» тут така сама слабка відповідь, як і бравада «та легко». Правильно — «можна, але з дисципліною». На проді — тільки читання: змінювати дані руками табу навіть із найкращими намірами, для цього існують міграції і support-процедури з рев'ю. На стенді зміни припустимі, але за правилами: де можливо — через застосунок (спрацює вся логіка: валідації, хешування пароля, події), прямий SQL лише коли потрібен стан, якого штатним шляхом не досягти; свої дані помічати унікальними префіксами (qa.autotest+...@example.com), щоб прибирання DELETE ... WHERE email LIKE 'qa.autotest%' не зачепило чужого; будь-яка ручна зміна — через ритуал SELECT → транзакція → перевірка → COMMIT; масові зміни (TRUNCATE, чистки) — тільки узгоджено з командою, бо спільний стенд ділять розробники, інші тестувальники й нічний прогін регресії.

Три кейси, де зміна даних вирішує, чи зіпсуєш ти спільний стенд: захисний ритуал UPDATE у сесії psql, Playwright-фікстура на RETURNING з ідемпотентним сідом і прибиранням, і рішення «DELETE чи TRUNCATE» з перевіркою каскадів. Скрізь — що робити і чому.

Кейс 1. Ритуал безпечного UPDATE на живому стенді

Треба заблокувати одного користувача на спільному стенді. Спокуса — написати UPDATE users SET status = 'blocked' WHERE ... і натиснути Enter. Проблема: у режимі autocommit помилка в WHERE фіксується миттєво й без попередження. Тому спершу дивимось очима, а міняємо в транзакції.

-- 1) ТОЙ САМИЙ WHERE, але SELECT: очима переконатись, кого зачепить
SELECT id, email, status FROM users WHERE email = 'qa.test@example.com';
-- повернуло рівно 1 рядок — те, що треба

-- 2) відкриваємо транзакцію
BEGIN;

-- 3) сама зміна
UPDATE users SET status = 'blocked' WHERE email = 'qa.test@example.com';
-- клієнт друкує: UPDATE 1  ← affected rows = 1, збігається з очікуванням

-- 4) число збіглося → фіксуємо
COMMIT;

А тепер той самий сценарій, коли в WHERE закрався одрук (status замість email):

BEGIN;
UPDATE users SET status = 'blocked' WHERE status = 'active';
-- клієнт друкує: UPDATE 20431  ← очікували 1, а зачепило 20 тисяч!
ROLLBACK;                          -- нічого не сталося, стенд цілий

Що дивитися і чому:

• SELECT тією ж умовою — до, а не після. Він відповідає на єдине важливе питання: скільки й яких саме рядків зачепить зміна. Побачив несподіване число — виправив WHERE, і жоден рядок ще не зіпсовано.
• affected rows — оракул в обидва боки. UPDATE 20431 замість UPDATE 1 — надто широкий WHERE; UPDATE 0 — надто вузький або хибний (одрук у значенні, не той стенд, дані вже змінені кимось). Відсутність помилки ще не означає, що зміна зробила те, що ви хотіли.
• BEGIN перетворює незворотне на зворотне. Поки немає COMMIT, будь-яку катастрофу скасовує ROLLBACK. Це і є перетворення синтаксично необов'язкового WHERE на практично обов'язкову дисципліну.

Кейс 2. Playwright-фікстура: RETURNING для прибирання, upsert для ідемпотентності

Фікстурі потрібні дві властивості: забрати id створеного рядка (щоб прибрати рівно його) і не падати на повторному прогоні. Перше дає RETURNING, друге — upsert.

import { test as base } from '@playwright/test';
import { Pool } from 'pg';

const pool = new Pool({ connectionString: process.env.DATABASE_URL });

// фікстура створює користувача напряму в БД і видаляє його після тесту
const test = base.extend<{ userId: number }>({
  userId: async ({}, use) => {
    // унікальний email на кожен прогін замість фіксованого
    const email = `qa.autotest+${Date.now()}@example.com`;
    const { rows } = await pool.query(
      `INSERT INTO users (email, name) VALUES ($1, $2) RETURNING id`,
      [email, 'Fixture User'],
    );
    const id = rows[0].id; // цей id і використовуємо, і видаляємо

    await use(id);

    // teardown: прибираємо рівно свій рядок за відомим id
    await pool.query(`DELETE FROM users WHERE id = $1`, [id]);
  },
});

test('заблокований користувач не бачить кабінет', async ({ page, userId }) => {
  // ... тест користується userId ...
});

Довідкові дані, які мають існувати перед тестом (тарифи, ролі, фіча-флаги), сідять ідемпотентним upsert-ом — тоді повторний запуск не падає на унікальному обмеженні:

-- ON CONFLICT робить сід ідемпотентним: другий запуск не впаде і не подвоїть рядок
INSERT INTO plans (code, title, price)
VALUES ('qa_trial', 'QA Trial', 0)
ON CONFLICT (code) DO UPDATE SET title = EXCLUDED.title, price = EXCLUDED.price;

Що дивитися і чому:

• RETURNING id замість другого запиту. Одна вставка одразу віддає згенерований ключ — не треба після INSERT окремо шукати «а який же id мені видало». У MySQL той самий сенс дає LAST_INSERT_ID(), у SQL Server — OUTPUT.
• Унікальний email рятує від гонки прибирання. Фіксований email на кожен прогін означає duplicate key при паралельних воркерах або коли попередній прогін не прибрав за собою. Унікальний суфікс (таймстемп, а ще надійніше — UUID) прибирає колізію в корені.
• Upsert — не для рядків тесту, а для довідників. Дані, які тест очікує побачити, готують ідемпотентно; дані, які тест створює, роблять унікальними й видаляють у teardown. Це різні задачі з різними інструментами.

Кейс 3. DELETE чи TRUNCATE — і чому спершу дивляться на FK

Перед прогоном треба очистити таблицю orders у своїй базі. Здається, що вибір між DELETE FROM orders і TRUNCATE TABLE orders — лише про швидкість. Насправді він про каскади й транзакції.

Пастка чекає, якщо на orders посилаються order_items. TRUNCATE TABLE orders відмовить (cannot truncate a table referenced in a foreign key constraint), бо лишилися б сирітські записи. У PostgreSQL є CASCADE — але він тихо винесе й order_items, які ви прямо не називали:

-- відмовить, поки є FK з order_items
TRUNCATE TABLE orders;

-- спорожнить І orders, І order_items — читати двічі перед виконанням
TRUNCATE TABLE orders CASCADE;

А після ручних видалень корисно перевірити, чи не лишилось сирітських записів — типова перевірка цілісності, якщо FK у схемі немає:

-- позиції замовлень, що посилаються на неіснуюче замовлення
SELECT oi.id, oi.order_id
FROM order_items oi
LEFT JOIN orders o ON o.id = oi.order_id
WHERE o.id IS NULL;   -- порожньо = сирітських записів немає

Що дивитися і чому:

• TRUNCATE — не «швидкий DELETE». Він не фільтрує рядки, не запускає тригери на видалення, а лічильник автоінкремента скидає в MySQL і не чіпає в PostgreSQL. Плутати їх у скрипті сідінгу — джерело сюрпризів.
• Захисна дужка BEGIN/ROLLBACK навколо TRUNCATE працює лише на одному з двох стендів. У PostgreSQL усічення транзакційно безпечне щодо даних і відкотиться, якщо транзакція не закомітилась. У MySQL/InnoDB воно дає неявний commit — ROLLBACK після нього не поверне нічого, і помилки ви теж не побачите. Той самий скрипт прибирання на MySQL-стенді знищує дані незворотно.
• CASCADE треба читати двічі. І у TRUNCATE ... CASCADE, і в ON DELETE CASCADE на FK одна команда може зачепити дерево таблиць. «Видалив одного користувача — зникли його замовлення й документи» — це не містика, а налаштований каскад.
• LEFT JOIN ... IS NULL — детектор сирітських записів. Коли FK у схемі немає (легасі, оптимізація), цілісність ніхто не боронить автоматично, і цей запит ловить рядки, що вже нікуди не посилаються, до того як вони зламають звіт.

INSERT і вставка даних

• Розумію, що DML (INSERT/UPDATE/DELETE) змінює дані, а SELECT — ні, і чому саме тому QA на проді часто read-only.
• Можу пояснити, чому явний перелік стовпців в INSERT — страховка від зміни схеми, а форма без переліку мовчки зсуває значення.
• Знаю, що неперелічені стовпці отримують DEFAULT або NULL, а NOT NULL без дефолту валить вставку помилкою обмеження.
• Розумію, що INSERT ... SELECT вставляє стільки рядків, скільки поверне SELECT, тому спершу той самий SELECT треба запустити окремо.

Автоінкремент і RETURNING

• Можу пояснити, що автоінкремент гарантує унікальність і зростання, але не суцільність — дірки в id після відкатів і невдалих вставок це норма.
• Знаю, як забрати згенерований id (RETURNING у PostgreSQL, LAST_INSERT_ID() у MySQL, OUTPUT у SQL Server) і навіщо це в автотестах — передати далі й прибрати за собою в teardown.

UPDATE і безпека змін

• Пам'ятаю, що WHERE в UPDATE/DELETE синтаксично необов'язковий, а autocommit фіксує зміну миттєво без «ви впевнені?».
• Можу відтворити захисний ритуал: SELECT тією ж умовою → BEGIN → зміна → перевірка affected rows → COMMIT або ROLLBACK.
• Розумію, що UPDATE з affected rows = 0 — це не успіх, а сигнал, що WHERE нікого не знайшов (одрук, не той стенд, дані вже змінені).

DELETE vs TRUNCATE vs DROP

• Знаю різницю DELETE vs TRUNCATE: вибірковість (WHERE), тригери на видалення, швидкість на великій таблиці, категорія (DML проти DDL у класифікації MySQL).
• Знаю різницю TRUNCATE vs DROP: TRUNCATE лишає порожню таблицю зі структурою, DROP знищує саму таблицю з індексами й обмеженнями.
• Знаю діалектні пастки TRUNCATE з обох боків: у PostgreSQL усічення транзакційно безпечне щодо даних і відкотиться, якщо транзакція не закомітилась; у MySQL/InnoDB — неявний commit без відкату. Лічильники теж різняться — MySQL скидає AUTO_INCREMENT, PostgreSQL не чіпає без RESTART IDENTITY.
• Не пишу прибирання як BEGIN → TRUNCATE → перевірка → ROLLBACK без певності щодо СУБД: на MySQL воно знищує дані незворотно й без жодної помилки.
• Розумію, чому TRUNCATE таблиці під зовнішніми ключами відмовляє і що CASCADE тихо спорожнить і залежні таблиці.

Upsert та ідемпотентність

• Можу пояснити гонку в «спершу SELECT, потім INSERT» (падіння на унікальному обмеженні в паралельному середовищі) і знаю атомарні upsert-и проти неї: INSERT ... ON CONFLICT (PostgreSQL), INSERT ... ON DUPLICATE KEY UPDATE (MySQL), MERGE (стандарт).
• Розумію, що upsert робить сідінг ідемпотентним: повторний запуск не падає і не плодить дублікатів — властивість номер один для автотестів.

Каскади зовнішніх ключів

• Знаю три правила FK на видалення батька: RESTRICT/NO ACTION (заборона, помилка 23503), CASCADE (зносить нащадків), SET NULL (обнуляє посилання).
• Розумію обидва боки FK при видаленні: з каскадом DELETE одного рядка зносить дерево пов'язаних даних (тому схему FK знаю до видалення), без FK — лишає сирітські записи (orphaned records), що ламають звіти і JOIN-и.

Дисципліна на спільних стендах

• Розумію правило read-only на проді: якщо доступ технічно дозволяє UPDATE на проді — вважаю, що не дозволяє.
• Знаю дві практики безпечних даних на стенді: створювати через застосунок (API/UI — спрацьовує вся логіка: валідації, хешування, події), а свої записи мітити унікальним префіксом, щоб прибирання DELETE ... LIKE 'qa.autotest%' не зачепило чужого.
• Пам'ятаю, що масові зміни (TRUNCATE, чистки, перезаливання довідників) — тільки узгоджено з командою і заздалегідь.