SQLite: бази даних для PHP-розробника

01 · ЩО ТАКЕ SQLITE

База як файл

MySQL і PostgreSQL це сервери: окремий процес, порт, користувачі, і PHP говорить з ними мережею. SQLite працює всередині процесу PHP через pdo_sqlite і читає один файл на диску. Немає що встановлювати й налаштовувати, базу можна скопіювати як звичайний файл, а режим :memory: тримає її в памʼяті до кінця зʼєднання.

Ціна простоти в паралельності. Читати можуть багато зʼєднань одночасно, а записувати лише одне: SQLite блокує на запис . Режим дозволяє читачам не заважати записувачу, але другого записувача не додає.

СЛОВНИКБлокування бази. SQLite тримає блокування на рівні файлу. Коли зʼєднання пише, інше на запис отримує SQLITE_BUSY: у PDO це General error: 5 database is locked. Скільки чекати, задає busy_timeout: pdo_sqlite за замовчуванням ставить 60 секунд, а з нулем друге зʼєднання падає одразу.

СЛОВНИКWAL. Write-Ahead Log: зміни дописуються в файл -wal поруч з базою, а читачі бачать останній закомічений знімок. PRAGMA journal_mode = WAL вмикає режим, і він зберігається в самому файлі бази.

Друга особливість у типах. Тип колонки в SQLite лише : INTEGER-колонка прийме рядок 'від 4000', а VARCHAR(10) не обріже тридцять символів. Таблиці (з версії 3.37) роблять типи справжніми.

СЛОВНИКТипова спорідненість. Рекомендований тип колонки. Значення, яке можна перетворити, перетворюється (рядок '4000' стає числом), а яке не можна, зберігається як є, з власним типом. typeof() показує, що лежить насправді.

СЛОВНИКSTRICT-таблиця. CREATE TABLE … STRICT з SQLite 3.37: колонка приймає лише свій тип, а значення не того типу дає помилку cannot store TEXT value in INTEGER column.

02 · ОДИН ЗАПИСУВАЧ

Хто чекає в SQLite

Три зʼєднання до одного файлу: читач тримає відкриту транзакцію, записувач оновлює рядок, другий записувач оновлює інший рядок. У режимі журналу за замовчуванням (DELETE) записувач не може закомітити, поки є читач. У WAL коміт проходить, а читач далі бачить свій знімок. Другий записувач не проходить у жодному режимі.

СЦЕНАРІЙ

Читач або записувач A Записувач B t0

BEGIN · SELECT views → 0

BEGIN

читає

UPDATE … WHERE id = 1

читає

COMMIT · ⏳ чекає читача

читає

database is locked

COMMIT

КОМІТ ЧЕКАЄРежим за замовчуванням. Поки A тримає читаючу транзакцію, B може змінити рядок у своїй транзакції, але на COMMIT їй потрібне виключне блокування файлу, а його не дають, поки є читач.

PDO · SQLite 3.45

SQLSTATE[HY000]: General error: 5 database is locked

Читач або записувач A Записувач B t0

BEGIN · SELECT views → 0

BEGIN

читає

UPDATE … WHERE id = 1

SELECT views → 0

COMMIT

ЧИТАЧ НЕ ЗАВАЖАЄУ WAL зміни B ідуть у файл -wal, і коміт не чекає на читачів. A до кінця своєї транзакції бачить знімок, з якого почала: 0, а не нове значення.

файли бази

site.sqlite  site.sqlite-shm  site.sqlite-wal

Читач або записувач A Записувач B t0

BEGIN · UPDATE … WHERE id = 1

BEGIN

🔒 запис у всю базу

UPDATE … WHERE id = 2 · ⏳

COMMIT

database is locked

ОДИН ЗА РАЗЗаписує лише одне зʼєднання на всю базу, хоч рядки різні, хоч режим WAL. B чекає busy_timeout (у PDO за замовчуванням 60 секунд) і падає, якщо A не встигла закомітити. З нульовим тайм-аутом падає одразу.

PDO · ATTR_TIMEOUT = 2

SQLSTATE[HY000]: General error: 5 database is locked   -- через 2,0 с

Сценарії відтворено на SQLite 3.45.1 з busy_timeout = 0, щоб помилка зʼявлялась одразу. Без цього PDO чекав би до 60 секунд і лише тоді повернув ту саму помилку.

03 · КОЛИ ДОРЕЧНА

Де SQLite справляється, а де ні

SQLite добра там, де записує один процес, а читають багато. Межа проходить не через обсяг даних (файл може мати терабайти), а через паралельний запис і мережу.

Задача

тести Laravel · DB_DATABASE=:memory: так База в памʼяті процесу, RefreshDatabase мігрує її з нуля, і жодного сервера в CI. Ціна в тому, що перевіряється SQLite, а не прод.

CLI-утиліта, десктоп чи мобільний застосунок так Один файл поруч з програмою, без встановлення сервера. Саме так SQLite використовують браузери й мобільні застосунки.

сайт, де читають багато, а пишуть рідко так Блог, документація, невеликий каталог на одному сервері з локальним диском. У WAL читачі не заважають записувачу.

черга, сесії чи лічильник переглядів під навантаженням обережно Кожен запис бере блокування всієї бази, і записи стають у чергу. Довга транзакція тримає всіх: через busy_timeout вони отримують database is locked.

кілька серверів застосунку з однією базою ні Файл на мережевому диску ламає блокування, а кожен сервер зі своєю копією бачить свої дані. Тут потрібна клієнт-серверна база.

Межа «пишуть рідко» залежить від тривалості транзакцій: короткі записи по кілька мілісекунд SQLite проводить один за одним.

04 · ТИПИ

Тип колонки це порада

У звичайній таблиці SQLite перетворює значення до типу колонки, якщо може, а якщо ні, зберігає як є. Рядок '4000' стає числом, 'від 4000' лишається текстом в INTEGER-колонці, і WHERE salary_from = 4000 його не знайде. Довжину VARCHAR SQLite не перевіряє зовсім.

PostgreSQL і MySQL у строгому режимі на такі значення відповідають помилкою. Тож тест на SQLite пропустить дані, які на проді не запишуться.

SQLite 3.45 PostgreSQL 16 MySQL 8.0

1CREATE TABLE vacancies (id INTEGER PRIMARY KEY,

2 salary_from INTEGER, title VARCHAR(10));

3INSERT INTO vacancies VALUES (1, '4000', 'Senior PHP Developer (Laravel)');

4INSERT INTO vacancies VALUES (2, 'від 4000', 'PHP');

5SELECT id, salary_from, typeof(salary_from), length(title)

6FROM vacancies;

8CREATE TABLE vacancies_strict (id INTEGER PRIMARY KEY,

9 salary_from INTEGER) STRICT;

10INSERT INTO vacancies_strict VALUES (2, 'від 4000');

1|4000|integer|30
2|від 4000|text|3

Runtime error: cannot store TEXT value in INTEGER column vacancies_strict.salary_from (19)

Перший рядок перетворено на число, другий збережено текстом. VARCHAR(10) пропустив 30 символів. STRICT виправляє обидва, але Laravel створює звичайні таблиці.

1CREATE TABLE vacancies (id int PRIMARY KEY,

2 salary_from integer, title varchar(10));

3INSERT INTO vacancies VALUES (2, 'від 4000', 'PHP');

4INSERT INTO vacancies VALUES (1, '4000', 'Senior PHP Developer (Laravel)');

ERROR:  invalid input syntax for type integer: "від 4000"
ERROR:  value too long for type character varying(10)

Обидва записи відхилено. Рядок '4000' PostgreSQL перетворив би, а 'від 4000' ні.

1INSERT INTO vacancies VALUES (2, 'від 4000', 'PHP');

2INSERT INTO vacancies VALUES (1, '4000', 'Senior PHP Developer (Laravel)');

ERROR 1366 (HY000): Incorrect integer value: 'від 4000' for column 'salary_from' at row 1
ERROR 1406 (22001): Data too long for column 'title' at row 1

Так поводиться строгий режим, який Laravel вмикає для MySQL параметром strict => true.

05 · ALTER TABLE

Що SQLite не вміє змінити

ALTER TABLE у SQLite вміє небагато: додати колонку, перейменувати колонку чи таблицю (з 3.25) і видалити колонку (з 3.35). Змінити тип, додати обмеження чи колонку з DEFAULT CURRENT_TIMESTAMP у таблицю з рядками не можна.

Laravel обходить це сам: для change() і для обмежень на SQLite він створює нову таблицю, копіює рядки й перейменовує її. У тестах це непомітно, а на файловій базі з даними це повне копіювання таблиці.

SQLite 3.45 PostgreSQL 16

1ALTER TABLE vacancies ADD COLUMN salary_to INTEGER;

2ALTER TABLE vacancies RENAME COLUMN salary_to TO salary_max;

3ALTER TABLE vacancies DROP COLUMN salary_max;

5ALTER TABLE vacancies ALTER COLUMN title TYPE VARCHAR(300);

6ALTER TABLE vacancies ADD CONSTRAINT salary_positive

7 CHECK (salary_from > 0);

8-- у таблиці вже є рядки:

9ALTER TABLE vacancies ADD COLUMN created_at TEXT

10 DEFAULT CURRENT_TIMESTAMP;

-- перші три виконано
Parse error: near "ALTER": syntax error
Parse error: near "CONSTRAINT": syntax error
Error: Cannot add a column with non-constant default

NOT NULL без DEFAULT теж не додати до таблиці з рядками: Cannot add a NOT NULL column with default value NULL.

1ALTER TABLE vacancies ALTER COLUMN title TYPE varchar(300);

ALTER TABLE

PostgreSQL і MySQL змінюють тип на місці. Розширення varchar у PostgreSQL навіть не переписує таблицю.

06 · ПЕРШИЙ У ГРУПІ

Перший перегляд кожного відвідувача

Звіт аналітики шукає першу сторінку, з якої відвідувач зайшов на сайт. У PostgreSQL для цього є DISTINCT ON: один рядок на кожне значення, перший за ORDER BY. Ні SQLite, ні MySQL його не знають.

Переносний варіант це ROW_NUMBER() OVER (PARTITION BY …) у підзапиті. Віконні функції є в SQLite з 3.25 і в MySQL з 8.0, тож цей запит однаковий для всіх трьох баз.

PostgreSQL 16 SQLite 3.45 MySQL 8.0

1SELECT DISTINCT ON (visitor) visitor, source, path

2FROM visits

3ORDER BY visitor, occurred_at, id;

 visitor | source |   path
---------+--------+-----------
 a1f3    | google | /jobs
 b7c2    | direct | /salaries

Порядок у ORDER BY вирішує, який рядок групи лишиться, тому спершу колонка з DISTINCT ON, потім час.

1SELECT DISTINCT ON (visitor) visitor, source, path

2FROM visits ORDER BY visitor, occurred_at, id;

4SELECT visitor, source, path FROM (

5 SELECT visitor, source, path,

6 ROW_NUMBER() OVER (PARTITION BY visitor

7 ORDER BY occurred_at, id) AS rn

8 FROM visits

9) AS t

10WHERE rn = 1 ORDER BY visitor;

Parse error: near "ON": syntax error

a1f3|google|/jobs
b7c2|direct|/salaries

Той самий результат через віконну функцію. Номер рядка зʼявляється після WHERE, тому фільтр rn = 1 стоїть у зовнішньому запиті.

1SELECT DISTINCT ON (visitor) visitor, source, path

2FROM visits ORDER BY visitor, occurred_at, id;

ERROR 1064 (42000): You have an error in your SQL syntax; … near
'ON (visitor) visitor, source, path FROM visits ORDER BY visitor, occurred_at, id'

Запит з ROW_NUMBER() з вкладки SQLite працює в MySQL 8.0 без змін і дає ті самі два рядки.

07 · RIGHT І FULL JOIN

Повне зовнішнє зʼєднання

Компанії без вакансій і вакансії без компанії в одному результаті дає FULL JOIN. SQLite навчилась RIGHT і FULL JOIN у версії 3.39 (2022), до того їх збирали з двох LEFT JOIN через UNION. MySQL FULL JOIN не має досі.

SQLite 3.45 PostgreSQL 16 MySQL 8.0

1SELECT c.name, v.title

2FROM companies c

3FULL JOIN vacancies v ON v.company_id = c.id

4ORDER BY c.name, v.title;

|Laravel Developer
Dnipro Labs|
Kvitka Soft|PHP Developer

NULL у SQLite при сортуванні за зростанням іде першим, як у MySQL. У PostgreSQL останнім.

1SELECT c.name, v.title

2FROM companies c

3FULL JOIN vacancies v ON v.company_id = c.id

4ORDER BY c.name, v.title;

    name     |       title
-------------+-------------------
 Dnipro Labs |
 Kvitka Soft | PHP Developer
             | Laravel Developer

1SELECT c.name, v.title

2FROM companies c

3FULL JOIN vacancies v ON v.company_id = c.id;

ERROR 1064 (42000): You have an error in your SQL syntax; … near
'FULL JOIN vacancies v ON v.company_id = c.id'

У MySQL FULL JOIN збирають як LEFT JOIN UNION RIGHT JOIN.

08 · SQLITE У LARAVEL

Налаштування для тестів і для файлу

У config/database.php підключення sqlite має pragma-параметри: journal_mode, busy_timeout, synchronous і foreign_key_constraints. Laravel виконує PRAGMA для кожного заданого параметра при зʼєднанні, а null лишає значення SQLite і PDO.

php database.php Тести в памʼяті

1

2<env name="DB_CONNECTION" value="sqlite"/>

3<env name="DB_DATABASE" value=":memory:"/>

ЩО ПЕРЕВІРЯЄ: Кожен процес тестів отримує свою порожню базу, RefreshDatabase запускає міграції. Паралельні тести не заважають одне одному: у кожного своя памʼять.
КОЛИ БРАТИ: Швидкі тести, які не залежать від особливостей продової бази.

php database.php Файлова база

1# .env

2DB_CONNECTION=sqlite

3# database/database.sqlite за замовчуванням

5// config/database.php, підключення sqlite

6'journal_mode' => 'wal',

7'busy_timeout' => 5000,

8'synchronous' => 'normal',

ЩО ПЕРЕВІРЯЄ: WAL дає читачам не чекати на запис. busy_timeout задає, скільки другий записувач чекає перед database is locked: без нього діє 60 секунд з pdo_sqlite, і запит висить хвилину. synchronous = normal у WAL безпечний щодо цілісності, але останні коміти можуть загубитись при вимкненні живлення.
КОЛИ БРАТИ: Невеликий сайт чи внутрішній інструмент на одному сервері.

php database.php Зовнішні ключі

1'foreign_key_constraints' => env('DB_FOREIGN_KEYS', true),

ЩО ПЕРЕВІРЯЄ: У самій SQLite зовнішні ключі вимкнені за замовчуванням: PRAGMA foreign_keys повертає 0, і cascadeOnDelete() нічого не видаляє. Laravel вмикає їх цим параметром.
КОЛИ БРАТИ: Завжди. Без нього тести не помітять ні порушеного ключа, ні каскаду.

php database.php Тести на продовій базі

1

2<env name="DB_CONNECTION" value="pgsql"/>

3<env name="DB_DATABASE" value="phpukraine_test"/>

5vendor/bin/pest -c phpunit.pgsql.xml

ЩО ПЕРЕВІРЯЄ: Той самий набір тестів проти справжнього PostgreSQL. Повільніше, але перевіряє гілки, які на SQLite не виконуються.
КОЛИ БРАТИ: Окремим кроком CI або перед релізом, коли в коді є сирий SQL чи розгалуження за драйвером.

09 · ЯК У PHP UKRAINE

Тести на SQLite, прод на PostgreSQL

phpunit.xml запускає всі тести на SQLite в памʼяті, а tests/Pest.php підключає RefreshDatabase до всіх Feature-тестів. Сайт при цьому працює на PostgreSQL, тож код має працювати на обох, і там, де бази розходяться, видно розгалуження. Міграція вакансій створює GIN-індекси й колонку tsvector лише на pgsql. Лічильник міст розгортає JSON-масив через jsonb_array_elements_text на PostgreSQL і через json_each на SQLite. Звіт аналітики шукає перший перегляд кожного відвідувача за день у PHP, а не через DISTINCT ON, саме щоб працювати на SQLite. Ціна компромісу: у тестах виконується лише SQLite-гілка, а PostgreSQL-гілку перевіряє прод.

php phpunit.xml фрагмент

1<php>

2 <env name="APP_ENV" value="testing"/>

3 <env name="CACHE_STORE" value="array"/>← кеш у масиві, не в таблиці

4 <env name="DB_CONNECTION" value="sqlite"/>

5 <env name="DB_DATABASE" value=":memory:"/>← база в памʼяті процесу

6 <env name="DB_URL" value=""/>

7 <env name="QUEUE_CONNECTION" value="sync"/>

8 <env name="SESSION_DRIVER" value="array"/>

9</php>

src/Hiring/Infrastructure/Catalog/EloquentJobCatalog.phpcityCounts() питає getDriverName() і для pgsql бере jsonb_array_elements_text, для решти json_each. Коментар у коді прямо називає SQLite тестовою базою.

src/Analytics/Infrastructure/Persistence/EloquentTrafficReport.phpentries() читає перегляди за період упорядкованими за occurred_at і id і бере перший рядок кожного відвідувача в PHP, через ??=. Правило в .ai/rules/analytics.md пояснює: так працюють тести на SQLite, без DISTINCT ON.

src/ContentOps/Infrastructure/Persistence/EloquentContentTaskRepository.phpleaseBatch() блокує задачі через lockForUpdate(). На SQLite Laravel не дописує FOR UPDATE, бо його там немає, тож конкуренцію двох воркерів тести не перевіряють.

src/Search/Infrastructure/EloquentSearchEngine.phpПошук порівнює LOWER(колонка) LIKE з терміном у нижньому регістрі. У SQLite lower() знає лише латиницю: lower('Київ') лишається 'Київ'. Тести пошуку шукають латинські слова, тож різниці з PostgreSQL не помічають.

10 · ТИПОВІ ПОМИЛКИ

Де SQLite підводить

✕ 1

Зелені тести на SQLite як доказ для проду

Тести не виконують гілки для PostgreSQL, не перевіряють сирий SQL, FOR UPDATE, регістр кирилиці й типи. Усе це вперше запускається на проді.

Натомість: Код, що розгалужується за драйвером, покривайте окремим прогоном на справжній базі, хоча б у CI.

✕ 2

Регістронезалежний пошук кирилицею

LIKE у SQLite не враховує регістр лише для латиниці, а lower() і upper() кирилицю не змінюють. 'Київ' LIKE 'київ' дає 0, хоча 'Kvitka' LIKE 'kvitka' дає 1.

Натомість: Зберігайте нормалізовану колонку в нижньому регістрі, яку заповнює PHP через mb_strtolower, або підключайте розширення ICU.

✕ 3

Зовнішні ключі вимкнені

Чиста SQLite не перевіряє FOREIGN KEY, поки зʼєднання не виконає PRAGMA foreign_keys = ON. Скрипт чи інструмент поза Laravel пише сирітські рядки без жодної помилки.

Натомість: У Laravel лишайте foreign_key_constraints увімкненим. В інших клієнтах виконуйте PRAGMA після кожного підключення.

✕ 4

database is locked під навантаженням

Черга, сесії й лічильники в одній файловій базі: кожен запис блокує всю базу. Паралельні запити стоять у черзі до 60 секунд і падають з General error: 5 database is locked.

Натомість: Увімкніть WAL, задайте розумний busy_timeout і тримайте транзакції короткими. Якщо записувачів багато, час переходити на клієнт-серверну базу.

✕ 5

Дати як рядки

Окремого типу дати немає: datetime() повертає текст, і порівняння йде як порівняння рядків. '2026-10-05 9:00' більше за '2026-10-05 10:00', бо '9' > '1'.

Натомість: Пишіть дати в одному форматі Y-m-d H:i:s з нулями попереду. Eloquent так і робить, проблеми виникають у сирих вставках.

11 · СЛОВНИК

Блокування бази: SQLite тримає блокування на рівні файлу. Коли зʼєднання пише, інше на запис отримує SQLITE_BUSY: у PDO це General error: 5 database is locked. Скільки чекати, задає busy_timeout: pdo_sqlite за замовчуванням ставить 60 секунд, а з нулем друге зʼєднання падає одразу.
WAL: Write-Ahead Log: зміни дописуються в файл -wal поруч з базою, а читачі бачать останній закомічений знімок. PRAGMA journal_mode = WAL вмикає режим, і він зберігається в самому файлі бази.
Типова спорідненість: Рекомендований тип колонки. Значення, яке можна перетворити, перетворюється (рядок '4000' стає числом), а яке не можна, зберігається як є, з власним типом. typeof() показує, що лежить насправді.
STRICT-таблиця: CREATE TABLE … STRICT з SQLite 3.37: колонка приймає лише свій тип, а значення не того типу дає помилку cannot store TEXT value in INTEGER column.

12 · ПЕРЕВІР СЕБЕ

Питання зі співбесід

Усі питання з SQL →

Не треба піднімати сервер бази: база створюється в памʼяті процесу, RefreshDatabase мігрує її з нуля, і тести не залежать одне від одного. Це швидко й однаково працює на ноутбуці та в CI. Платять за це тим, що перевіряється не продова база.

Тип колонки в SQLite це спорідненість: значення, яке не перетворюється на число, зберігається як текст. Перевірити можна через typeof(salary_from). Запобігти допомагає STRICT-таблиця, CHECK (typeof(salary_from) = 'integer') або валідація в застосунку. PostgreSQL і MySQL у строгому режимі такий запис відхилили б.

Зміни пишуться в окремий файл -wal, а читачі бачать останній закомічений знімок. Тому читач більше не блокує коміт записувача, і навпаки. Але записувач однаково один на всю базу: другий чекає busy_timeout і отримує database is locked, якщо перший не встиг.

Ризики: різні типи й обмеження, регістр кирилиці, відсутні DISTINCT ON і FOR UPDATE, транзакційний DDL, різний порядок NULL, сирий SQL і гілки за getDriverName(), які в тестах не виконуються. Зменшити: писати переносний SQL або ховати діалект за інтерфейсом репозиторію, тримати окремий прогін тестів на PostgreSQL у CI хоча б для інфраструктурних тестів, і перевіряти міграції на копії продової бази.

← ПОПЕРЕДНЯMySQL, MariaDB і PostgreSQL НАСТУПНА →Коли потрібна не реляційна база

ТЕМАMySQL, MariaDB і PostgreSQL ТЕМАВіконні функції ТЕМАJOIN ТЕМА · ТЕСТУВАННЯФейки адаптерів ТЕМА · ТЕСТУВАННЯВиди тестів ТЕМАВіконні функції ПИТАННЯ · SQLЧим відрізняються INNER JOIN і LEFT JOIN?