# SQLite ## Що це таке SQLite — це рушій SQL-бази даних, який працює як бібліотека всередині програми, що ним користується: без серверного процесу, мережевого порту й адміністратора, а таблиці, індекси, тригери та представлення лежать разом в одному звичайному файлі на диску. Назва трапляється й у формі `sqlite3`: так звуть консольну оболонку, так названо єдиний файл із сирцями на C, `sqlite3.c`, і з `sqlite3_` починається кожна функція її C-інтерфейсу. Сам проєкт описує рушій як самодостатній, безсерверний, без конфігурації та транзакційний і радить порівнювати його з `fopen()`, а не з Oracle. Він заміняє саморобний файл даних, а не сервер бази даних. Проєкт стартував 9 травня 2000 року, а код перебуває в суспільному надбанні (public domain), тож перед вбудовуванням у продукт не треба розбиратися з ліцензією. Формат файлу — частина домовленості: базу, записану на 32-бітній машині, можна скопіювати на 64-бітну або між процесорами big-endian і little-endian та відкрити без змін. Розробники мають намір підтримувати SQLite до 2050 року, а Бібліотека Конгресу США рекомендує цей формат для зберігання даних. З усіма увімкненими можливостями бібліотека може займати менше ніж 900KiB, тому вона живе в телефонах, автомобілях, годинниках і настільних програмах так само, як і на серверах. Усередині це повноцінний SQL: з'єднання, підзапити, представлення, тригери й транзакції з властивостями ACID, які зберігаються навіть тоді, коли живлення зникає посеред запису. Від серверних баз SQLite відрізняють два рішення. Перше стосується паралельної роботи: читати одночасно може скільки завгодно з'єднань, а писати в кожну мить — лише одне, і блокування охоплює весь файл. Друге стосується типів. Оголошений тип стовпчика — лише побажання, яке SQLite називає спорідненістю (affinity), а справжній тип належить кожному окремому значенню. Тож стовпчик, оголошений як `INTEGER`, охоче прийме текст, геть не схожий на число. Межа з альтернативами чітка. PostgreSQL, MySQL і SQL Server — спільні сервіси, до яких багато машин звертаються мережею. SQLite — приватне сховище однієї програми на одній машині. Порівняно з файлами CSV чи JSON SQLite додає мову запитів, індекси, обмеження й транзакції ціною однієї залежності, яку більшість мов програмування вже мають у комплекті. ## Чому це важливо Найважче питання про базу даних часто стосується не швидкості, а того, хто її обслуговуватиме. Сервер треба встановити, оновлювати, захищати, моніторити й резервувати, і комусь за це платять. SQLite прибирає цю статтю витрат цілком. Відкрити базу — означає відкрити файл, резервна копія — це копія файлу, знята через backup API навіть під час роботи, а набір тестів може дати кожному тесту власну свіжу базу. SQLite змінює й уявлення про те, чим може бути файл. Програма, яка інакше вигадувала б власний формат, писала б до нього парсер і сподівалася, що збій не трапиться посеред збереження, може тримати свій стан у SQLite й отримати атомарне збереження задарма. Проєкт сам називає такі сценарії: формат файлу застосунку, контейнер для перенесення даних між системами, де отримувач пише SQL замість парсера, і тимчасова база для сортування та підсумовування даних, що прийшли в іншій формі. Вибір зводиться до одного питання, на яке можна відповісти ще до першого рядка коду: скільки процесів пишуть і на скількох машинах? Одна програма на одній машині, що пише короткими сплесками, — рідна територія SQLite, і більшість сайтів із низьким чи помірним трафіком почуваються на ній добре. Кілька серверів, що пишуть в одні й ті самі дані, або багато користувачів, які пишуть щомиті, — це справа клієнт-серверної бази. Розмір рідко щось вирішує, адже один файл уміщує до 281 терабайта. Вирішує кількість тих, хто пише. Навіть на рідній території є ціна. `ALTER TABLE` уміє лише перейменувати таблицю чи стовпчик і додати або видалити стовпчик, тож більшість інших змін структури означає нову таблицю, копіювання рядків і обмін назвами. Зовнішні ключі вимкнено, доки кожне з'єднання не ввімкне їх саме. А вільна типізація тихо пропускає хибні дані, якщо таблицю не оголошено так, щоб вона їх відкидала. ## Як це виглядає на практиці Для генератора резюме, листів-рекомендацій, портфоліо та супровідних листів SQLite — джерело істини: [усе береться з бази SQLite на 23 таблиці](/uk/portfolio/built-a-database-driven-cv-and-portfolio-generator-144/), від досягнень і компаній до профілів і рекомендацій, яку збирає конвеєр із 19 кроків: від порожнього файлу до повної бази однією командою. Кожен крок можна безпечно повторити, тож конвеєр проганяють і по наявній базі, не дублюючи жодного рядка. З цього одного файлу 41 модуль Python загальним обсягом 10 580 рядків видає шість форматів, і залежностей під час виконання рівно дві. Документ для певного читача — це запит, який визначає профіль, а не відредагована копія. Ціну сторінка теж називає: тепер система — єдиний спосіб отримати документ. Портфоліо цього сайту генерується з тієї ж бази, і тут SQLite — вхідні дані для збирання. [Експортер відкриває файл SQLite лише для читання й пише 495 сторінок](/uk/portfolio/generated-321-achievement-pages-from-a-sqlite-export-132/) трьома мовами, користуючись тільки стандартною бібліотекою Python, а результат зберігається в репозиторії, тож сайт збирається без середовища генератора. Адреси сторінок зберігаються як рядки, по одному на адресу на досягнення, тож переформульоване речення більше не пересуває сторінку. Під час тієї ж роботи зафіксували пастку з наступного розділу: порівняння періоду з текстовим стовпчиком зачепило всі тридцять два рядки. Найменший і найтиповіший випадок — SQLite як місце, де переглядають результати. Конвеєр, що перетворював сирі показники електроенергії на погодинне споживання по зонах, записував результати в CSV або [у легку базу SQLite для перегляду](/uk/portfolio/engineered-hourly-electricity-consumption-aggregation-pipeline-in-python-4/), і версія на Python обробляла дані за один день за 34 мс, а за тридцять днів — за 180 мс. Така база не потребує жодного налаштування: будь-хто з оболонкою `sqlite3` відкриє файл і поставить йому запит. ## Де виникають проблеми Більшість клопотів із SQLite починається тоді, коли з нею поводяться як із маленьким PostgreSQL. Це інша машина з іншими гострими кутами. **Фільтр за роком повертає всі рядки.** SQLite перетворює значення перед порівнянням лише тоді, коли один із боків має спорідненість. Результат функції на кшталт `substr()` її не має, як і голе число, тому текст `'2019'` порівнюється з цілим `2025` як є. А в порядку сортування SQLite будь-яке число стоїть перед будь-яким текстом: ```sql CREATE TABLE stint (period TEXT, hours TEXT); INSERT INTO stint VALUES ('2019-03', '9'), ('2024-11', '10'), ('2026-01', '42'); SELECT count(*) FROM stint WHERE substr(period, 1, 4) >= 2025; -- 3 SELECT count(*) FROM stint WHERE CAST(substr(period, 1, 4) AS INTEGER) >= 2025; -- 1 ``` Явно приводьте типи там, де текст зустрічається з числом. Новим таблицям давайте справжні типи й ключове слово `STRICT`, доступне з версії 3.37.0: суворий стовпчик `INTEGER` і далі приймає `'42'` та зберігає його як число, а `'ten'` відкидає з помилкою. **Запис раптом завершується помилкою `database is locked`.** Одне з'єднання тримає блокування запису, а наступний записувач чекає або здається одразу. Причина зазвичай у довгій транзакції або в читачеві, який у типовому режимі rollback-журналу затримує записувача. Увімкніть write-ahead logging, щоб читачі й записувач перестали блокувати одне одного, тримайте транзакції запису короткими і задайте busy timeout, щоб записувач трохи чекав, а не повертав помилку. Якщо записувачів справді багато й вони пишуть постійно, це сигнал переходити на сервер. **Файл псується на мережевому диску.** SQLite покладається на файлові блокування операційної системи, а в багатьох мережевих файлових системах саме вони реалізовані з помилками, тож дві машини можуть водночас писати в ту саму частину файлу. Write-ahead logging над мережевою файловою системою не працює взагалі, бо всі процеси, що користуються базою, мають ділити пам'ять на одній машині. Тримайте базу на локальному диску тієї єдиної програми, яка в неї пише, а якщо даних потребують інші машини, поставте перед ними сервер. **Рядок переживає батьківський запис, на який посилається.** Зовнішні ключі записано в схемі, але вони неактивні, доки кожне з'єднання не виконає `PRAGMA foreign_keys = ON`. Видалення через з'єднання, яке це пропустило, тихо залишає рядки-сироти. Вмикайте прагму там, де програма відкриває з'єднання, в одному місці коду, і перевіряйте тестом, що вона справді ввімкнена. ## Що почитати далі - [About SQLite](https://sqlite.org/about.html) — опис від самого проєкту: у процесі програми, без сервера, у суспільному надбанні, один файл для всіх платформ. - [Datatypes In SQLite](https://sqlite.org/datatype3.html) — класи зберігання, спорідненість типів і правила порівняння, що стоять за пасткою вище. - [STRICT Tables](https://sqlite.org/stricttables.html) — як змусити таблицю дотримуватися оголошених типів стовпчиків. - [Write-Ahead Logging](https://sqlite.org/wal.html) — читачі й записувач паралельно і чому для цього потрібна одна машина. - [Appropriate Uses For SQLite](https://sqlite.org/whentouse.html) — де SQLite доречна, де краще підходить клієнт-серверна база, і контрольний список. - [SQLite Foreign Key Support](https://sqlite.org/foreignkeys.html) — чому обмеження за замовчуванням вимкнені і як їх увімкнути.