Керування та використання бази даних SQLite 3

SQLite 3 — популярна вбудована реляційна система керування базами даних. Завдяки своїй легкості, відсутності необхідності у серверному процесі та зберіганню даних в одному файлі, вона ідеально підходить для мобільних додатків, вбудованих систем, невеликих десктопних додатків і для локального середовища розробки. SQLite 3 проста в управлінні та використанні. Цей матеріал є докладним посібником для користувачів ServBay: як ефективно працювати з SQLite 3 у середовищі ServBay — включно з перевіркою встановлення, підключення, базовими операціями, резервним копіюванням і відновленням, оптимізацією продуктивності та питаннями безпеки.

Огляд

ServBay — це комплексне середовище локальної веб-розробки, у складі якого вже інтегровано SQLite 3 та необхідні бібліотеки розробника. Ви можете працювати з нею напряму через командний рядок або через різні мови програмування (PHP, Node.js, Python, Go тощо), які підтримує ServBay — без додаткового встановлення. Це суттєво спрощує використання SQLite 3 у місцевій розробці.

Встановлення та налаштування SQLite 3

SQLite 3 інтегровано в ServBay за замовчуванням, отже не потребує окремого встановлення. Виконуваний файл та бібліотеки SQLite 3 вже знаходяться в пакеті ServBay.

Перевірка встановлення SQLite 3

Щоб швидко перевірити, чи доступний виконуваний файл SQLite 3 у середовищі ServBay, відкрийте термінал і введіть команду:

bash

sqlite3 --version

Якщо команда повертає номер версії (наприклад, 3.41.2 2023-03-26 11:52:19 ...), це означає, що SQLite 3 успішно інтегровано і готова до використання.

Створення та підключення до бази даних SQLite 3

База даних SQLite 3 — це фактично один файл. За замовчуванням рекомендується зберігати файли баз даних у каталозі /Applications/ServBay/db/sqlite, але ви можете вказати будь-який інший шлях залежно від потреб проєкту. Створення чи підключення до бази даних можна виконати за допомогою командного рядка sqlite3 або через бібліотеки в різних мовах програмування.

Підключення через командний рядок

Використання sqlite3 у командному рядку — найпростіший спосіб керування базою даних.

Створити або підключитися до бази: Введіть у терміналі команду. Якщо файл бази за вказаним шляхом ще не існує, він буде створений; якщо вже існує — буде відкритий.
bash
```
sqlite3 /Applications/ServBay/db/sqlite/servbay.db
```
1
(В цьому прикладі база даних має назву servbay.db, що відображає бренд ServBay)
Далі ви побачите промпт sqlite>, де можна виконувати SQL-команди чи .commands.
Вихід з SQLite 3: Щоб завершити роботу в інтерфейсі командного рядка, введіть команду .exit:
sql
```
.exit
```
1

Підключення через мови програмування

Більшість мов програмування, які підтримуються в ServBay, мають бібліотеки для роботи з SQLite 3. Ось приклади для PHP і Node.js.

PHP-приклад

У ServBay, як правило, вже встановлено розширення PHP для SQLite 3 (php-sqlite3). Використовуйте вбудований клас SQLite3 для підключення до БД.

php

<?php
// Шлях до файлу бази даних. Рекомендується зберігати у db/sqlite каталозі ServBay
$database_file = '/Applications/ServBay/db/sqlite/servbay.db';

// Підключення до SQLite 3
// Якщо файл не існує — буде створено автоматично
$db = new SQLite3($database_file);

if (!$db) {
    die("Не вдалося підключитися до SQLite 3: " . $db->lastErrorMsg());
}

echo "Успішне підключення до SQLite 3: " . $database_file . "\n";

// Створення таблиці (якщо не існує)
$create_table_sql = "CREATE TABLE IF NOT EXISTS users (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT NOT NULL,
    email TEXT UNIQUE
)";
$db->exec($create_table_sql);
echo "Таблиця 'users' створена або вже існувала.\n";

// Вставка даних
$name = 'ServBay Demo User';
$email = 'demo@servbay.demo';
$insert_sql = $db->prepare('INSERT INTO users (name, email) VALUES (:name, :email)');
$insert_sql->bindValue(':name', $name, SQLITE3_TEXT);
$insert_sql->bindValue(':email', $email, SQLITE3_TEXT);

if ($insert_sql->execute()) {
    echo "Дані успішно додані.\n";
} else {
    echo "Не вдалося додати дані: " . $db->lastErrorMsg() . "\n";
}


// Запит даних
$query_sql = "SELECT id, name, email FROM users";
$result = $db->query($query_sql);

if ($result) {
    echo "Результати запиту:\n";
    while ($row = $result->fetchArray(SQLITE3_ASSOC)) {
        echo "ID: " . $row['id'] . ", Name: " . $row['name'] . ", Email: " . $row['email'] . "\n";
    }
} else {
     echo "Помилка запиту: " . $db->lastErrorMsg() . "\n";
}


// Закриття підключення
$db->close();
unset($db); // Звільнення ресурсів
echo "З'єднання з БД закрито.\n";

?>

Node.js-приклад

У Node.js для роботи з SQLite 3 потрібен модуль sqlite3 (його слід встановити через npm). У терміналі перейдіть у папку з проектом і виконайте:

bash

npm install sqlite3

Далі можна використовувати цей модуль у Node.js-коді:

javascript

const sqlite3 = require('sqlite3').verbose();
const path = require('path');

// Вказуємо шлях до файлу бази. Використовуємо path.join для кросплатформеності
const dbPath = path.join('/Applications/ServBay/db/sqlite', 'servbay.db');

// Підключення до SQLite 3
// Якщо файл не існує — буде створений
let db = new sqlite3.Database(dbPath, sqlite3.OPEN_READWRITE | sqlite3.OPEN_CREATE, (err) => {
  if (err) {
    console.error('Не вдалося підключитися до SQLite 3:', err.message);
  } else {
    console.log('Успішне підключення до бази SQLite.');
  }
});

// Послідовне виконання команд у правильному порядку
db.serialize(() => {
  // Створення таблиці (якщо не існує)
  db.run(`CREATE TABLE IF NOT EXISTS users (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT NOT NULL,
    email TEXT UNIQUE
  )`, (err) => {
    if (err) {
      console.error('Створити таблицю не вдалося:', err.message);
    } else {
      console.log('Таблиця "users" створена або вже існувала.');
    }
  });

  // Додавання даних
  const name = 'ServBay Demo User';
  const email = 'demo@servbay.demo';
  db.run(`INSERT INTO users (name, email) VALUES (?, ?)`, [name, email], function(err) {
    if (err) {
      // SQLITE_CONSTRAINT — помилка унікальності
      if (err.errno === 19) { // SQLITE_CONSTRAINT
         console.warn(`Користувач '${name}' (${email}) вже існує, пропущено.`);
      } else {
         console.error('Не вдалося додати дані:', err.message);
      }
    } else {
      console.log(`Успішно додано рядок, ID: ${this.lastID}`);
    }
  });

  // Запит даних
  db.all(`SELECT id, name, email FROM users`, [], (err, rows) => {
    if (err) {
      throw err;
    }
    console.log('Результати запиту:');
    rows.forEach((row) => {
      console.log(`ID: ${row.id}, Name: ${row.name}, Email: ${row.email}`);
    });
  });
});

// Закриття з'єднання
db.close((err) => {
  if (err) {
    console.error('Не вдалося закрити з\'єднання:', err.message);
  } else {
    console.log('З\'єднання з базою закрито.');
  }
});

Примітка: У реальних додатках завжди перевіряйте безпеку шляху до файлу БД та коректно обробляйте помилки й конкуренцію доступу.

Керування базою даних

Основні SQL операції

Після підключення до бази даних можна виконувати стандартні команди SQL. Ось поширені приклади:

Створення таблиці: визначення структури даних.

sql

CREATE TABLE products (
    product_id INTEGER PRIMARY KEY AUTOINCREMENT,
    product_name TEXT NOT NULL,
    price REAL DEFAULT 0.00
);

Вставлення даних: додавання нових записів.

sql

INSERT INTO products (product_name, price) VALUES ('ServBay T-Shirt', 19.99);
INSERT INTO products (product_name, price) VALUES ('ServBay Sticker Pack', 4.99);

Запит даних: вибірка записів.

sql

SELECT * FROM products;
SELECT product_name, price FROM products WHERE price > 10.00;

Оновлення даних: зміна існуючих записів.

sql

UPDATE products SET price = 24.99 WHERE product_name = 'ServBay T-Shirt';

Видалення даних: видалення записів.

sql

DELETE FROM products WHERE product_id = 1;
DELETE FROM products; -- Видалити всі записи з таблиці

Видалення таблиці: повне видалення таблиці та її вмісту.
sql
```
DROP TABLE products;
```
1

Виконуйте ці команди у sqlite3 або через бібліотеки у відповідній мові програмування.

Резервне копіювання та відновлення

Бекап і відновлення в SQLite дуже прості, адже база — це один файл.

Резервне копіювання

Найпростіший спосіб створити резервну копію — скопіювати файл БД. Рекомендується зберігати резервні копії у /Applications/ServBay/backup/sqlite.

Приклад у командному рядку:

bash

# Створити папку резервних копій (якщо не існує)
mkdir -p /Applications/ServBay/backup/sqlite

# Скопіювати базу для резерву
# Додавайте дату/час у ім'я файлу для розрізнення копій
cp /Applications/ServBay/db/sqlite/servbay.db /Applications/ServBay/backup/sqlite/servbay_$(date +%Y%m%d_%H%M%S).db

Також можна скористатися командою .backup у sqlite3 для так званого "гарячого" бекапу (не перериваючи роботу з БД), але у локальній розробці зазвичай достатньо прямого копіювання файлу.

Відновлення бази даних

Щоб відновити БД, просто скопіюйте файл резервної копії у початкову директорію. Перед цим обов'язково зупиніть всі сервіси або додатки, які можуть звертатися до цієї БД, — щоб уникнути блокування чи пошкоджень!

bash

# Припустимо, ви відновлюєте останню резервну копію
# Знайти останній резервний файл, напр.: servbay_20231027_103000.db
LATEST_BACKUP=$(ls -t /Applications/ServBay/backup/sqlite/servbay_*.db | head -n 1)

# Перевірити, чи файл існує
if [ -z "$LATEST_BACKUP" ]; then
  echo "Помилка: жодної резервної копії не знайдено."
  exit 1
fi

echo "Використовується резервна копія: $LATEST_BACKUP"

# Зупиніть служби, які використовують БД ...
# Наприклад, якщо PHP-додатки активно використовують базу, переконайтеся, що жоден PHP-процес не працює

# Перезаписати існуючий файл бази резервною копією
cp "$LATEST_BACKUP" /Applications/ServBay/db/sqlite/servbay.db

echo "Відновлення завершено."

# Перезапустіть відповідні служби або додатки...

ВАЖЛИВО: Пряме копіювання файлів може бути небезпечним, якщо база зараз використовується чи відбувається запис. Найкраще зупинити сервіси, або використовувати теплі/гарячі бекап-API (реалізовані через бібліотеки).

Оптимізація продуктивності

SQLite 3 має низку PRAGMA-команд і стратегії індексування для оптимізації швидкодії.

Оптимізація за допомогою індексів

Створення індексів на колонках, за якими часто відбувається фільтрація (WHERE), об'єднання (JOIN) чи сортування (ORDER BY) — значно прискорює запити.

sql

-- Унікальний індекс на email у користувачах
CREATE UNIQUE INDEX idx_users_email ON users(email);

-- Обычний індекс на product_name у продуктах
CREATE INDEX idx_products_name ON products(product_name);

Оптимізація запитів

Використовуйте EXPLAIN QUERY PLAN, щоб побачити, як SQLite виконує ваш SQL-запит і чи використовує індекси:

sql

EXPLAIN QUERY PLAN SELECT * FROM users WHERE email = 'demo@servbay.demo';
EXPLAIN QUERY PLAN SELECT product_name FROM products ORDER BY price DESC;

Аналізуйте чи є звернення до індексу, чи виконується повне сканування таблиці (SCAN TABLE).

Оптимізація через налаштування (PRAGMA)

PRAGMA-команди дозволяють налаштувати параметри роботи SQLite 3 у процесі виконання. Ось найважливіші для продуктивності:

PRAGMA journal_mode; / PRAGMA journal_mode = mode;: визначає режим ведення журналу. Режим WAL (Write-Ahead Logging) дає кращу конкуренцію доступу та швидкість запису, особливо коли багато читачів і паралельних писачів. За замовчуванням може бути DELETE або TRUNCATE. Змінити на WAL:
sql
```
PRAGMA journal_mode = WAL;
```
1
PRAGMA synchronous; / PRAGMA synchronous = level;: визначає рівень синхронізації з файловою системою. Значення OFF дає максимальну швидкість запису, але ризик втрати даних при збоях. FULL чи NORMAL — безпечніше, але повільніше. Для локальної розробки рекомендовано OFF.
sql
```
PRAGMA synchronous = OFF;
```
1
PRAGMA cache_size; / PRAGMA cache_size = pages;: задає кількість сторінок у кеші в пам’яті. Більше кешу — менше звернень до диску (швидше), але більше RAM.
sql
```
PRAGMA cache_size = -20000; -- 20MB кешу (від’ємне число — в КБ)
```
1

Зверніть увагу: PRAGMA-параметри діють лише для поточного підключення. Якщо потрібно, застосовуйте їх при кожному підключенні чи ініціалізації додатка.

Безпека

Хоча SQLite 3 — файлово орієнтована база, базові безпекові заходи необхідні навіть у локальному середовищі.

Доступ до файлу

Файл бази повинен мати такі права доступу, щоб лише користувач ServBay або потрібний процес мав читання/запис.

bash

# Наприклад: надати привілеї читання/запису лише власнику
# Враховуйте під якого користувача стартує ServBay
chmod 600 /Applications/ServBay/db/sqlite/servbay.db

Шифрування даних

SQLite 3 за замовчуванням не підтримує шифрування. Якщо ви працюєте з чутливими даними — можете скористатись розширенням SQLCipher для шифрування, але це додаткове встановлення та специфічний API.

Часті питання та вирішення

Не вдається підключитися до SQLite 3

Перевірте шлях до файлу: впевніться, що шлях у команді чи у коді відповідає дійсності та що файл існує.
Перевірте права доступу: користувач, що запускає ServBay або термінал, має мати права читання/запису до файлу. Перевірте ls -l /Applications/ServBay/db/sqlite/servbay.db і скористайтеся chmod чи chown для зміни при потребі.
Можливе пошкодження файлу: спробуйте підключитися через sqlite3. Якщо з помилкою — файл міг бути зіпсований. Виконайте відновлення з резерву.

Проблеми із блокуванням бази (Database is locked)

Під час записів SQLite 3 блокує весь файл. Якщо кілька процесів намагаються записати або навіть читати (залежно від режиму журналу) — може з’явитися помилка блокування.

Перевірте паралельний доступ: переконайтеся, що не запущено кілька додатків/скриптів/серверів, які працюють з однією і тією ж базою.
Увімкніть режим WAL: PRAGMA journal_mode = WAL; суттєво полегшує конкурентний доступ. Застосовуйте відразу після підключення.
Обробляйте помилки блокування: при помилці "database is locked" не варто одразу фейлити — краще спробувати ще раз через деякий час. Часто у бібліотеках SQLite передбачено таймаут очікування.
Приклад для Node.js sqlite3:
javascript
```
db.configure("busyTimeout", 5000); // Таймаут 5000 мс (5 секунд)
```
1
У PHP-класі SQLite3 прямої властивості busyTimeout немає, але перепробуйте ручну реалізацію таймауту чи використання нижчого рівня API (якщо це реалізовано).

Підсумок

ServBay — це локальне середовище розробки, яке вже має потужну і просту у використанні SQLite 3. Завдяки цьому посібнику ви навчитесь перевіряти встановлення, підключатись, керувати даними, створювати резервні копії й виконувати відновлення, оптимізовувати продуктивність і застосовувати основні заходи безпеки для SQLite 3 у ServBay. Завдяки своїй легкості та гнучкості, SQLite 3 чудово підходить для локального розвитку, тестування або невеликих проектів. Використовуйте його разом з іншими інструментами ServBay (PHP, Node.js, Python, Go, різноманітні сервери й бази даних) для побудови комплексних додатків та тестування їх локально.

Керування та використання бази даних SQLite 3 ​

Огляд ​

Встановлення та налаштування SQLite 3 ​

Перевірка встановлення SQLite 3 ​

Створення та підключення до бази даних SQLite 3 ​

Підключення через командний рядок ​

Підключення через мови програмування ​

PHP-приклад ​

Node.js-приклад ​

Керування базою даних ​

Основні SQL операції ​

Резервне копіювання та відновлення ​

Резервне копіювання ​

Відновлення бази даних ​

Оптимізація продуктивності ​

Оптимізація за допомогою індексів ​

Оптимізація запитів ​

Оптимізація через налаштування (PRAGMA) ​

Безпека ​

Доступ до файлу ​

Шифрування даних ​

Часті питання та вирішення ​

Не вдається підключитися до SQLite 3 ​

Проблеми із блокуванням бази (Database is locked) ​

Підсумок ​

Керування та використання бази даних SQLite 3

Огляд

Встановлення та налаштування SQLite 3

Перевірка встановлення SQLite 3

Створення та підключення до бази даних SQLite 3

Підключення через командний рядок

Підключення через мови програмування

PHP-приклад

Node.js-приклад

Керування базою даних

Основні SQL операції

Резервне копіювання та відновлення

Резервне копіювання

Відновлення бази даних

Оптимізація продуктивності

Оптимізація за допомогою індексів

Оптимізація запитів

Оптимізація через налаштування (PRAGMA)

Безпека

Доступ до файлу

Шифрування даних

Часті питання та вирішення

Не вдається підключитися до SQLite 3

Проблеми із блокуванням бази (Database is locked)

Підсумок