SQLite — це чудова база даних для багатьох, якщо не для більшості, додатків на Laravel. За правильної конфігурації (до чого ми дійдемо далі) вона може забезпечити відмінну продуктивність, навіть якщо ваша програма обробляє багато одночасних запитів.
Але чому обирати її, а не MySQL? На це питання можна відповісти запитанням: чому обирати MySQL, якщо для вашого випадку використання підходить SQLite? Головна перевага SQLite полягає в тому, що ваша база даних — це один файл, який легко резервувати й ділитися ним. На відміну від "правильної" бази даних, як MySQL, для неї необхідно запускати цілий демон. Щодо продуктивності, для деяких випадків SQLite може бути швидшою, оскільки вона використовується безпосередньо одним і тим же процесом, що обробляє веб-запит — без міжпроцесорної комунікації та мережевих затримок.
Проте, зрозуміло, що SQLite не підходить для всього. Якщо ви плануєте масштабувати свій додаток на кілька серверів, ви не зможете. Бази даних SQLite є файлами, які повинні знаходитись на тому ж сервері, що й ваш веб-сервер. Хіба що ви скористаєтеся такими рішеннями, як Turso, але це вже виходить за рамки цієї статті.
Якщо ваша база даних складна і має безліч одночасних записів, MySQL буде кращим вибором. Вона може блокувати окремі рядки, тоді як SQLite блокує всю базу даних. Тож на практиці не рекомендується використовувати SQLite для великих кількостей одночасних, розподілених, масштабованих додатків.
Проте більшість додатків не відповідають цим вимогам. Багато з них — це внутрішні інструменти, які не зазнають великої активності, або ж користувацькі додатки, що мають небагато користувачів. Якщо ви просто вертикально масштабуватимете такі програми (переходячи на потужніший сервер, розгляньте потужні сервери Hetzner — ви здивуєтеся, скільки апаратури можна отримати за $50 на місяць), можна досягти досить гарної продуктивності. Але, знову ж таки, це не ідеальний варіант для кожного додатка. Навіть якщо вам не потрібно великих масштабів, ви можете віддати перевагу наявності балансувальника навантаження і щонайменше двох веб-серверів для резервування. Кожен додаток унікальний.
Якщо ви вирішили використовувати SQLite, врахуйте кілька практичних рекомендацій.
Увімкнення режиму WAL дозволяє SQLite розкрити свій потенціал у виробництві. Коротко кажучи, за замовчуванням SQLite працює в режимі rollback, що означає, що читачі блокують записувачів і навпаки. Режим WAL (write-ahead log) записує дані у окремий файл (періодично об'єднуючи з головним файлом бази даних), що дозволяє читачам не блокувати записувачів (і навпаки), але записувачі все ще блокують один одного. Однак це не велика проблема, оскільки більшість операцій виконується швидко, тому ви просто не хочете, щоб читання блокувало записання (і навпаки...). Для докладнішої інформації раджу курс від Аарона Франсіса "High Performance SQLite", якщо ви любите навчальні курси. Якщо ні, документація SQLite також корисна.
Щоб увімкнути режим WAL, достатньо виконати:
pragma journal_mode = wal;Це стійкий pragma, тож його потрібно встановити лише один раз. Окрім режиму журналу, варто налаштувати busy_timeout. Завдяки цьому, якщо база даних заблокована для запису, нові транзакції чекатимуть деякий час, а не відразу терміново відмовлятимуться:
pragma busy_timeout = 10000;Щоб зробити це в Laravel:
// config/database.php
'sqlite' => [
// ...
'busy_timeout' => 10000,
'journal_mode' => 'wal',
],SQLite використовує динамічну типізацію, що вимагає більшої уваги на рівні додатка.
Ви можете використовувати жорсткі таблиці, але, наскільки мені відомо, Laravel не має способу їх створення у міграціях, та ви не можете перетворити вже створену таблицю в жорстку таблицю.
Головне, що потрібно пам’ятати: движок бази даних не заперечує щодо зберігання різних даних у таблиці, тому ви повинні вжити запобіжних заходів на рівні додатка.
Крім того, хоча це не строго пов'язано лише з SQLite, переконайтеся, що ваші касти моделей Eloquent забезпечують узгодженість.
Наприклад, у мене є таблиця, де я зберігаю позначки часу у вигляді unix-міток. Вони використовуються як публічні ID цих записів (фактично номери версій). SQLite не може нормалізувати їх у єдиний формат (хоча ви можете використовувати деякі функції для перетворення даних datetime у формат, який вам потрібен), тому ви повинні впевнитися, що ваші касти Eloquent використовують точний формат, який вам потрібен.
Спочатку я мав деякі дані, створені безпосередньо за допомогою фасаду DB (прості низькорівневі оператори), але згодом я створив додаткові записи, використовуючи модель Eloquent. У цій моделі я використовував каст datetime для стовпця timestamp, але вся логіка очікувала, що дані зберігатимуться як unix-мітки. Касти Eloquent приховують це після того, як моделі були отримані, коли всі ці стовпці правильно перетворюються на екземпляри Carbon. Але де це може підвести, так це запити зі вставками where():
// Старий запис
Version::where('timestamp', $unixTimestamp)->first(); // працює
// Запис, створений з Eloquent
Version::where('timestamp', $unixTimestamp)->first(); // nullВесь виправлення полягало в тому, що використовується datetime:U каст, а не просто datetime. Тож це більше була помилка на рівні додатка, ніж вроджена проблема SQLite, хоча про це варто думати більше, оскільки SQLite просто зберігає дані в тому вигляді, в якому їх передаєте ви (або ваш ORM). Тож намагайтеся, щоб ваші касти чітко вказували, в якому форматі ви очікуєте отримати дані.
Це основна причина, чому я пишу цю статтю. SQLite має особливу поведінку, про яку потрібно знати: за замовчуванням, транзакції не отримують блокування, поки не виникне потреба в них, а якщо ви починаєте з блокування для читання, а потім намагаєтеся перейти до блокування для запису в тій же транзакції, це відразу завершується невдачею, незважаючи на ваш настрой busy_timeout.
Це багато слів — що це означає у коді?
DB::transaction(function () use ($attributes) {
$user = User::firstWhere('github_id', $attributes['github_id']);
// Користувача з таким github_id НЕ знайдено, але знайдено користувача з
// тим самим email, давайте пов'яжемо їх разом
if (! $user && isset($attributes['email']))
$user = User::firstWhere('email', $attributes['email']);
if ($user) {
$user->update(collect($attributes)->only([
'github_id',
'github_username',
'github_token',
'github_refresh_token',
])->all());
} else {
$user = User::create(array_merge($attributes, [
'email_verified_at' => now(),
'password' => bin2hex(random_bytes(32)),
]));
}
});Це спрощений приклад з логіки OAuth. У реальному коді існує кілька додаткових шляхів, але це працює для нашого прикладу. Спочатку ми намагаємось отримати користувача (це отримує блокування для читання), а потім намагаємося записати користувача (це намагається поліпшити до блокування для запису). В деяких випадках можна використати єдину операцію upsert, але суть у тому, що в багатьох випадках ви не будете мати такого, є безліч транзакцій, які спершу читають, а лише потім намагаються записати.
Що відбувається тут, так це те, що якщо є активне блокування запису коли ми намагаємося підвищити наше блокування до запису, ми отримуємо одразу SQLITE_BUSY. Наш busy_timeout не може цьому завадити.
Це відбувається тому, що SQLite за замовчуванням використовує відкладені транзакції. Є й інші типи транзакцій, які дозволяють нам отримати блокування для запису з самого початку: IMMEDIATE і EXCLUSIVE. Вони трохи відрізняються в тих чи інших режимах журналу, але є еквівалентними в режимі WAL, який ми використовуємо.
Отже, якщо ми почнемо нашу транзакцію, використовуючи BEGIN IMMEDIATE TRANSACTION замість BEGIN DEFERRED TRANSACTION (або просто BEGIN, що робить те саме), ми не зіткнемося з проблемою отримання SQLITE_BUSY при спробі підвищити до блокування для запису — ми починаємо з блокування для запису (конкретно, зарезервованого блокування, див. пояснення нижче).
Тут є деякий нюанс, і я спрощую, як працюють блокування SQLite. Багато людей знають про блокування читання і запису, тому я говорю про них. Це базова концепція синхронізації. Але під капотом SQLite використовує кілька інших типів блокувань, а саме: RESERVED і PENDING. Як я зрозумів, RESERVED фактично говорить: "Я збираюся записати в якийсь момент, читачі можуть продовжувати, але нові записувачі ні", PENDING зупиняє нових читачів (і записувачів), чекає, поки завершаться поточні записи, а потім переходить до EXCLUSIVE, в основному це означає "Я збираюся писати прямо зараз". А EXCLUSIVE — це справжнє блокування для запису.
Проблема в тому, що ми не можемо зробити це в PHP. Я маю на увазі, ми можемо зробити:
$pdo->exec('begin immediate transaction');Але ми не можемо насправді зробити це в Laravel. Laravel має власну абстракцію для транзакцій DB, яка додає трохи додаткової логіки, яку ми все ще хочемо використовувати, але в кінцевому підсумку використовує PDO::beginTransaction() під капотом, що означає, що в Laravel ми завжди отримуємо відкладені транзакції.
Коли я дізнався це, я подумав, що напевно має бути налаштування для цього. Поглиблюючись в код Laravel, я тільки знайшов виклик beginTransaction(), нічого корисного, тож перевірив, чи є у PHP якісь налаштування для цього (зазвичай це були б атрибути PDO). Їх немає. Я знайшов випадок на GitHub з 2022 року, що просить цю можливість. Спочатку я надав просту можливість відтворення:
<?php
// Ця репродукція потребує розширення pcntl
$immediateTx = isset($argv[1]) && $argv[1] === 'immediate';
if (file_exists($path = '/tmp/sqlite_tx_mode_demo.sqlite')) unlink($path);
$pdo = new PDO('sqlite:/tmp/sqlite_tx_mode_demo.sqlite');
$pdo->exec('pragma busy_timeout=5000');
assert($pdo->query('pragma busy_timeout', PDO::FETCH_ASSOC)->fetch()['timeout'] === 5000);
$pdo->exec('create table foo (bar)');
$pdo->exec('insert into foo (bar) values ("baz")');
// Два процеси
$pid = pcntl_fork();
if ($immediateTx) $pdo->exec('begin immediate transaction');
else $pdo->beginTransaction();
// Починаємо з спільного блокування. Якщо цього немає, код виглядає нормально,
// оскільки перше зусилля у кожній транзакції — це запис, тому, коли він отримує
// належне блокування, це ексклюзивне (?). Однак, якщо ми починаємо з
// отримання спільного блокування, то транзакція вдасться лише в одному процесі й не вдасться в іншому.
$pdo->query('select * from foo')->fetch();
sleep(1);
// ГОЛОВНА ПРОБЛЕМА: У даний момент у нас є два одночасні записувачі, але обидва почали з відкладених транзакцій,
// що викликає у SQLite помилку SQLITE_BUSY. Якщо обидва транзакції почніть як негайні, цей код
// буде абсолютно нормальним, лише якщо затримка не перевищує 5-секундний тайм-аут, установлений на початку.
$pdo->exec('update foo set bar = "xyz"');
assert($pdo->query('select * from foo')->fetch(PDO::FETCH_ASSOC)['bar'] === 'xyz');
if ($immediateTx) $pdo->exec('commit');
else $pdo->commit();
assert($pdo->query('select * from foo')->fetch(PDO::FETCH_ASSOC)['bar'] === 'xyz');
printf("[%s] Успіх\n", $pid);
// Чекаємо на дочірній процес перед виходом
if ($pid) pcntl_waitpid($pid, $status);Код досить простий. Ми просто створюємо два процеси, обидва використовують одну й ту ж базу даних SQLite, спочатку читаючи, чекаємо 1 секунду, а потім намагаючись записати.
Якщо ви запустите це, написавши php script.php, одна транзакція успішна, а інша не вдається. Якщо ви запустите php script.php immediate, що викличе виконання exec('begin immediate transaction') замість beginTransaction(), обидві транзакції завершуються успішно.
Це викликало багато неприємностей під час виробництва, тому в міжчасі я вивчив кілька можливих рішень і, можливо, використав одне з найбільш простих: обгортку навколо DB::transaction(), що спочатку записує в фіктивну таблицю, а потім виконує функцію транзакції. Це чудово спрацювало:
/**
* Обгортка для DB::transaction(), що забезпечує ексклюзивне блокування з самого початку
* і таким чином тайм-аут busy_timeout працює, як і очікується. Це слід використовувати замість
* DB::transaction(), коли транзакція ПОЧИНАЄТЬСЯ З ЧИТАННЯ і ймовірно буде конкурентною.
*
* @template T
* @param Closure(): T $callback
* @return T
*/
function immediate_transaction(Closure $callback, int $attempts = 1) {
return DB::transaction(function () use (&$callback) {
// Починаємо з запису, щоб не застрягати з блокуванням на читання
DB::statement('update dummy_table set foo = "bar"');
return $callback();
}, $attempts);
}Винятки з мого коду зникли. Мій трекер помилок був спокійний. Але через кілька днів я отримав виняток з внутрішньої логіки обмеження швидкості Laravel (яка використовує кеш, що використовує драйвер БД з DB::transaction()) з точно такою ж проблемою:
Загальна помилка: 5 база даних заблокована
Тому це потрібно вирішити на нижньому рівні. Я знову перевірив випадок на GitHub і вирішив спробувати — створив запит на внесення змін в php-src. Це стара кодова база C з величезною кількістю макросів, але через годину або близько того можна досить чітко зрозуміти. У підсумку я подав PR, що додає атрибути PDO\Sqlite для налаштування режиму транзакцій, сподіваючись, що це увійде в PHP 8.5 (вихід у листопаді). Атрибути PDO — ідеальне рішення тут, оскільки Laravel дозволяє вам встановлювати їх у конфігурації підключення до бази даних. Ніяка логіка не потрібно змінювати, це просто налаштування з'єднання PDO. Всі виклики beginTransaction() (а отже, й DB::transaction()) використовуватимуть налаштований режим транзакції.
Але я не можу чекати PHP 8.5, у мене вже є винятки в виробництві. Тому я поглянув на внутрішню логіку бази даних Laravel і знайшов спосіб обійти це в Laravel. Все підключення до бази даних (SQLiteConnection, MySQLConnection тощо) розширюють основний клас Connection, який використовує трейти ManagesTransactions. Там ми можемо знайти логіку, що викликає beginTransaction(), і нам потрібно просто змінити її на exec('begin immediate transaction').
Laravel дозволяє нам перевизначити "розв’язувач" для драйвера бази даних (таких як 'sqlite'):
Connection::resolverFor('sqlite', function (...$args) {
return new SQLiteConnectionOverride(...$args);
});Це просто встановлює статичну властивість, тож її можна розмістити в AppServiceProvider::register(), щоб запустити якомога раніше.
У нашому перевизначеному класі ми просто робимо так:
class SQLiteConnectionOverride extends SQLiteConnection
{
protected function createTransaction()
{
if ($this->transactions == 0) {
$this->reconnectIfMissingConnection();
try {
// ПЕРЕВИЗНАЧЕННЯ тут:
$this->getPdo()->exec('begin immediate transaction');
// Замість:
// $this->getPdo()->beginTransaction();
} catch (Throwable $e) {
$this->handleBeginTransactionException($e);
}
} elseif ($this->transactions >= 1 && $this->queryGrammar->supportsSavepoints()) {
$this->createSavepoint();
}
}
}Мені це не надто подобається, але це прекрасно працює і охоплює будь-які виклики DB::transaction(), навіть у внутрішній логіці Laravel, такій як кешування чи обмеження швидкості, де я не можу використовувати свою обгортку.
Використовуйте режим WAL та розумний busy_timeout. Обидва можна налаштувати безпосередньо в config/database.php
Звертайте увагу на типи даних, проявляйте додаткову обережність, щоб ваші касти Eloquent були точними
Ймовірно, варто використовувати наведене вище перевизначення? Це здається неправильним і не повинно бути необхідним, але з усієї інформації, яку я міг знайти, а також різними скриптами, які я написав, це справжнє обмеження в PHP (сподіваюся, лише до PHP 8.5) і, відповідно, в Laravel.
SQLite — це фантастична база даних, і мені подобається, наскільки простою є моя серверна установка. Резервне копіювання просте, як запланована команда Laravel VACUUM, що копіює базу даних у новий файл, який завантажується в безпечне сховище. Але наразі це вимагає деякого розуміння та дисципліни у вашому коді додатку.