Знайомство з SQL для застосунків

1. Навіщо потрібна реляційна БД

Перші тижні навчання програмуванню зазвичай дуже затишні: у вас є MutableList, ви додали туди елементи — і життя прекрасне. Але лише доти, доки ви не закриєте програму. Потім список… чарівним чином зникає, як мотивація в пʼятницю о 18:59. І ось тут зʼявляється база даних: вона зберігає дані так, щоб ті жили довше за одну сесію застосунку, і щоб правила коректності даних трималися не «на чесному слові», а працювали системно.

Реляційна база даних (РБД) — це спосіб зберігати дані у вигляді таблиць, повʼязаних між собою зрозумілими правилами. «Реляційна» — тому що важливі відношення (relations) між наборами даних. У навчальному світі це виглядає майже як Excel, але із суперсилою: таблиці вміють захищати дані від хаосу, підтримувати звʼязки та виконувати запити, які «пробігають» мільйонами рядків швидше, ніж ви встигнете сказати: «А можна я просто циклом пройдуся?».

Щоб далі не плутатися, закріпімо базові терміни.

Мінісловник: таблиця, рядок, стовпець, схема

Тут є важлива ідея: таблиця — це не рядок. Таблиця — це колекція записів одного типу. Рядок — це один запис. Стовпці — це поля запису. А все разом «пакується» в поняття «схема», тобто домовленість про структуру даних. Якщо ви колись сперечалися в команді: «А поле email може бути порожнім чи ні?» — схема потрібна якраз для того, щоб суперечка закінчилася (зазвичай — перемогою здорового глузду).

2. Схема як контракт даних

Коли застосунок зберігає дані у списках, уся коректність тримається на вашому коді: забули перевірку — отримали сміття. У базі даних частину таких перевірок можна «винести» в схему. Це дуже схоже на добрий дизайн API: ви не сподіваєтеся, що користувач вашої функції завжди передасть правильне значення, — ви задаєте контракт. Так само й база даних має «знати», яке значення в таблиці вважається коректним.

Схема описує, які таблиці існують, які в них стовпці, які типи даних і які обмеження цілісності застосовуються. В SQL схему зазвичай створюють командами CREATE TABLE ....

У цій лекції ми не налаштовуємо конкретну СУБД (PostgreSQL/SQLite/MySQL) і не запускаємо сервер. Натомість будуємо модель, щоб далі JDBC і Exposed не виглядали «чарівними заклинаннями». Тому приклади будуть у вигляді SQL-рядків у Kotlin.

Приклад: таблиця користувачів

Навіть якщо наш застосунок — про витрати, корисно почати з простого: users із id, email, name. Це концентрат ключових обмежень: первинний ключ, унікальність і заборона NULL.

fun main() {
    val createUsers = """
        CREATE TABLE users (
            id INTEGER PRIMARY KEY,
            email TEXT UNIQUE NOT NULL,
            name TEXT NOT NULL
        )
    """.trimIndent()

    println(createUsers)
    // CREATE TABLE users ( ... )
}

Зверніть увагу на зміст, а не на конкретні типи (INTEGER, TEXT) — у різних СУБД нюанси відрізняються. Нам важливіше інше: тут описано контракт. email — унікальний і обовʼязковий, name — обовʼязковий, а id — головний ідентифікатор рядка.

3. Ключі та обмеження цілісності

У програмуванні часто хочеться, щоб усе було гнучко: «ну нехай буде порожній email», «ну нехай буде два однакові записи», «ну я потім почищу». База даних — той самий суворий викладач, який каже: «спочатку правильні дані — потім залік». І хоча це інколи дратує, у реальних проєктах така суворість рятує від дуже дорогих помилок.

Обмеження цілісності (constraints) бувають різні, але сьогодні нам потрібні чотири базові: PRIMARY KEY, NOT NULL, UNIQUE, FOREIGN KEY.

Primary Key (PK): «паспорт» рядка

Первинний ключ — це стовпець (або набір стовпців), який унікально ідентифікує рядок. У прикладному коді PK потрібен, щоб ви могли впевнено сказати: «онови ось цей запис» або «видали ось цей запис», не ризикуючи зачепити зайве.

Якщо PK немає, ви починаєте писати DELETE WHERE name = 'Coffee' — і раптом видаляєте всю каву в історії людства. А потім питаєте: «Чому люди не люблять програмістів?».

NOT NULL: «значення має бути»

NOT NULL — це заборона на відсутність значення. Тобто стовпець має мати значення в кожному рядку. Важливо: NULL — це не порожній рядок і не нуль. Це «значення невідоме/відсутнє».

Якщо у витрати немає суми — це дивна витрата. Тому сума майже завжди має бути NOT NULL.

UNIQUE: «повторюватися не можна»

UNIQUE захищає від дублікатів за змістом. Наприклад, email зазвичай має бути унікальним. Можна намагатися тримати це лише в коді, але в розподіленому світі код не завжди єдиний вхід (міграції, адміністративні скрипти, інтеграції). База даних — остання лінія оборони.

Foreign Key (FK): «посилання на іншу таблицю»

Зовнішній ключ задає звʼязок між таблицями: наприклад, expenses.category_id посилається на categories.id. FK потрібен, щоб не зʼявлялися «висячі посилання»: витрата на категорію, якої не існує.

4. Таблиці та звʼязки на прикладі Expense Tracker

Зараз ми зробимо важливий крок: переведемо звичні обʼєкти з коду в табличну структуру. Уявімо наш практичний проєкт як CLI-застосунок Expense Tracker, який зберігає витрати із сумою, описом і категорією.

У памʼяті ми могли зберігати це так:

• категорію як рядок "food" прямо всередині витрати;
• або категорію як окремий обʼєкт.

У реляційній моделі це зазвичай перетворюється на дві таблиці: categories і expenses. Категорія — окрема сутність, а витрата зберігає посилання на неї.

Щоб візуально закріпити ідею, ось схема звʼязків (не SQL, просто «мапа»):

flowchart LR
    C[categories] -->|id = PK| E[expenses]
    E -->|category_id = FK -> categories.id| C

Так, стрілки тут двоспрямовані за змістом: категорія «має багато витрат», а витрата «належить одній категорії».

SQL-опис таблиць: категорії та витрати

Покажемо схему у вигляді Kotlin-рядків.

fun main() {
    val createCategories = """
        CREATE TABLE categories (
            id INTEGER PRIMARY KEY,
            name TEXT UNIQUE NOT NULL
        )
    """.trimIndent()

    val createExpenses = """
        CREATE TABLE expenses (
            id INTEGER PRIMARY KEY,
            amount_cents INTEGER NOT NULL,
            description TEXT NOT NULL,
            category_id INTEGER NOT NULL,
            FOREIGN KEY (category_id) REFERENCES categories(id)
        )
    """.trimIndent()

    println(createCategories)
    println(createExpenses)
}

Тут є кілька практичних рішень.

Ми використовуємо amount_cents як INTEGER, а не DOUBLE. Це типовий прийом для грошей: зберігати суму в цілих «мінімальних одиницях» (центах), щоб не ловити сюрпризи двійкової точності. У майбутніх лекціях про JDBC/Exposed ми це особливо цінуватимемо, бо «грошові» баги — це ті, які дуже швидко перетворюють програміста на філософа.

5. Мінімальний SQL для CRUD

Якщо сильно спростити, більшість застосунків навколо нас роблять одне й те саме: створюють записи, читають записи, оновлюють записи та видаляють записи. Це називають CRUD (Create, Read, Update, Delete). В SQL це відповідає чотирьом базовим операціям: INSERT, SELECT, UPDATE, DELETE.

Зараз наша мета — не вивчити «весь SQL на світі», а навчитися впізнавати ці команди в коді та писати їх так, щоб вони були безпечними й передбачуваними.

INSERT: додати новий рядок

INSERT створює запис. Зазвичай ви перелічуєте стовпці та значення.

У реальному JDBC-коді ми майже завжди використовуватимемо параметри ? (про це нижче), але поки просто зафіксуймо шаблон.

fun main() {
    val insertCategorySql = "INSERT INTO categories(name) VALUES (?)"
    val insertExpenseSql = """
        INSERT INTO expenses(amount_cents, description, category_id)
        VALUES (?, ?, ?)
    """.trimIndent()

    println(insertCategorySql) // INSERT INTO categories(name) VALUES (?)
    println(insertExpenseSql)  // INSERT INTO expenses(...) VALUES (?, ?, ?)
}

SELECT: прочитати дані

SELECT — це «вибірка». У мінімальному варіанті: які стовпці хочемо отримати і з якої таблиці.

fun main() {
    val selectAllCategoriesSql = "SELECT id, name FROM categories"
    val selectExpenseByIdSql = "SELECT id, amount_cents, description, category_id FROM expenses WHERE id = ?"

    println(selectAllCategoriesSql) // SELECT id, name FROM categories
    println(selectExpenseByIdSql)   // SELECT ... FROM expenses WHERE id = ?
}

WHERE id = ? — ключова частина: ми вибираємо конкретний запис за первинним ключем.

UPDATE: змінити запис

UPDATE майже завжди має містити WHERE. Без WHERE ви змінюєте всі рядки в таблиці. Іноді це потрібно в міграціях, але в прикладному коді це найчастіше катастрофа, а не корисна можливість.

fun main() {
    val updateExpenseDescriptionSql = """
        UPDATE expenses
        SET description = ?
        WHERE id = ?
    """.trimIndent()

    println(updateExpenseDescriptionSql)
}

DELETE: видалити запис

Із DELETE — та сама історія: без WHERE ви зносите всю таблицю.

fun main() {
    val deleteExpenseSql = "DELETE FROM expenses WHERE id = ?"
    println(deleteExpenseSql) // DELETE FROM expenses WHERE id = ?
}

6. SELECT і безпека запитів

Як читати SELECT-запити

Коли ви починаєте писати запити, в голові часто звучить: «Я просто хочу список витрат за сьогодні». А далі починається: SELECT ... WHERE ... ORDER BY ... LIMIT ... — і здається, що це заклинання. Насправді це цілком логічний ланцюжок кроків, просто записаний текстом.

У SELECT є кілька дуже практичних елементів, які трапляються постійно.

WHERE: фільтр «залиш тільки відповідне»

WHERE задає умову відбору. Це як filter для колекції, тільки виконується в самій базі даних (і зазвичай ефективніше). Тут корисно памʼятати ваше «колекційне мислення»: ми вже проходили перетворення та фільтрації колекцій у Kotlin.
SQL робить подібне, тільки на боці сховища.

ORDER BY: сортування

Сортування особливо часто потрібне у списках: «покажи останні витрати». В SQL це ORDER BY created_at DESC (якщо є дата/час) або ORDER BY id DESC (дуже грубий, але інколи прийнятний наближений варіант).

LIMIT: «дай перші N»

LIMIT обмежує кількість рядків у відповіді. Це корисно і для UX, і для продуктивності.

Зберімо це в один приклад запиту «останні 10 витрат» (без дат, щоб не забігати наперед):

fun main() {
    val recentExpensesSql = """
        SELECT id, amount_cents, description, category_id
        FROM expenses
        ORDER BY id DESC
        LIMIT 10
    """.trimIndent()

    println(recentExpensesSql)
}

Параметризовані запити (?)

Зараз буде принцип, який звучить нудно, але рятує проєкти: SQL-запит має бути фіксованим текстом, а дані потрібно передавати окремо — параметрами.

У SQL параметр позначають ? (у JDBC-світі). Ми не писатимемо сьогодні JDBC-код — це буде в наступній лекції. Але звичку варто сформувати вже зараз: не «вклеювати» значення в рядок запиту.

Чому це важливо:

• По-перше, це зменшує ризик помилок із лапками та екрануванням. Рядки з апострофами, пробілами, дивними символами — усе це база даних уміє коректно обробити, якщо ви передаєте значення параметрами.
• По-друге, це захищає від SQL-інʼєкцій — ситуацій, коли користувацьке введення перетворюється на частину SQL-коду. Навіть якщо ваш застосунок «не про безпеку», краще одразу писати так, щоб потім не перевчатися.

Порівняймо два підходи на рівні Kotlin-рядків.

fun main() {
    val email = "bob@example.com"

    val unsafe = "SELECT id, email, name FROM users WHERE email = '$email'"
    val safe = "SELECT id, email, name FROM users WHERE email = ?"

    println(unsafe) // SELECT ... WHERE email = 'bob@example.com'
    println(safe)   // SELECT ... WHERE email = ?
}

Другий варіант виглядає «незавершеним», доки ви не знаєте JDBC. Але це якраз правильний стиль: запит — шаблон, а значення — окрема сутність.

Що зберігати рядком, а що — посиланням

Коли ви проєктуєте таблиці, виникає типове питання: «А можна я просто зберігатиму категорію рядком у expenses.category? Навіщо мені окрема таблиця?»

Іноді — можна. Але частіше окрема таблиця вигідніша, тому що, якщо категорія окрема, ви уникаєте друкарських помилок (Food vs food vs fod), можете перейменувати категорію в одному місці, можете зберігати додаткові поля (іконка, порядок, колір), і у вас зʼявляється контроль через FK.

Щоб це не було абстракцією, уявімо два дизайни.

Варіант A: категорія рядком у expenses

fun main() {
    val createExpensesFlat = """
        CREATE TABLE expenses (
            id INTEGER PRIMARY KEY,
            amount_cents INTEGER NOT NULL,
            description TEXT NOT NULL,
            category TEXT NOT NULL
        )
    """.trimIndent()

    println(createExpensesFlat)
}

Це простіше, дає змогу швидше стартувати, і таблиць менше. Але в довгостроковій перспективі майже завжди спливають проблеми з якістю даних.

Варіант B: окрема таблиця + FK

Ми вже показували це вище, але тепер зміст зрозуміліший: категорія — окрема сутність.

fun main() {
    val createCategories = """
        CREATE TABLE categories (
            id INTEGER PRIMARY KEY,
            name TEXT UNIQUE NOT NULL
        )
    """.trimIndent()

    val createExpenses = """
        CREATE TABLE expenses (
            id INTEGER PRIMARY KEY,
            amount_cents INTEGER NOT NULL,
            description TEXT NOT NULL,
            category_id INTEGER NOT NULL,
            FOREIGN KEY (category_id) REFERENCES categories(id)
        )
    """.trimIndent()

    println(createCategories)
    println(createExpenses)
}

Тут застосунок трохи складніший: щоб додати витрату, потрібно знати category_id. Але далі це стане зручніше: ми зможемо зберігати категорії в довіднику та коректно посилатися на них.

Шар SQL-шаблонів

Зараз ми зробимо маленький, але важливий крок до «практичного» дизайну: зберемо наші SQL-запити в одному місці, щоб далі (у JDBC та Exposed) не розмазувати рядки по всьому проєкту. Це ще не DAO і не Repository у повному сенсі, але вже дисципліна: запити — поруч, імена — узгоджені, формат — єдиний.

Зробимо обʼєкт ExpenseSql, який містить лише рядки. Жодної магії.

object ExpenseSql {
    val createCategories = """
        CREATE TABLE categories (
            id INTEGER PRIMARY KEY,
            name TEXT UNIQUE NOT NULL
        )
    """.trimIndent()

    val createExpenses = """
        CREATE TABLE expenses (
            id INTEGER PRIMARY KEY,
            amount_cents INTEGER NOT NULL,
            description TEXT NOT NULL,
            category_id INTEGER NOT NULL,
            FOREIGN KEY (category_id) REFERENCES categories(id)
        )
    """.trimIndent()
}

І додамо CRUD-шаблони. Зверніть увагу: всюди, де дані приходять ззовні, стоять ?.

object ExpenseSql {
    val insertCategory = "INSERT INTO categories(name) VALUES (?)"
    val findCategoryByName = "SELECT id, name FROM categories WHERE name = ?"

    val insertExpense = """
        INSERT INTO expenses(amount_cents, description, category_id)
        VALUES (?, ?, ?)
    """.trimIndent()

    val listExpenses = """
        SELECT id, amount_cents, description, category_id
        FROM expenses
        ORDER BY id DESC
        LIMIT ?
    """.trimIndent()

    val deleteExpenseById = "DELETE FROM expenses WHERE id = ?"
}

Це поки «просто рядки». Але в наступній лекції ці рядки стануть аргументами для PreparedStatement, і ви побачите, як «фіксований SQL + параметри» перетворюється на реальний робочий код — без конкатенації та без сюрпризів.

7. Типові помилки

Помилка №1: плутати таблицю й рядок, проєктуючи схему «на око».
Часто новачок каже: «У мене таблиця витрати», маючи на увазі один запис, і починає додавати поля так, ніби таблиця — це обʼєкт. Корисна звичка: щоразу проговорювати вголос «таблиця — це багато рядків», а «рядок — це одна витрата/один користувач». Тоді проєктування стає значно спокійнішим.

Помилка №2: робити UPDATE або DELETE без WHERE.
Це класика, яка трапляється навіть у досвідчених розробників, — просто тому, що запит виглядає «логічно», доки не усвідомлюєте масштаб. Без WHERE ви змінюєте або видаляєте всі рядки. У прикладному коді правило просте: якщо це не усвідомлена масова операція (і ви не готові до наслідків), то WHERE обовʼязковий — зазвичай за id.

Помилка №3: зберігати важливі обмеження лише в коді, ігноруючи NOT NULL і UNIQUE.
Спочатку здається: «Ну я ж перевіряю в Kotlin, навіщо в БД?» Потім зʼявляється імпорт даних, міграція, ручний скрипт адміністратора або конкурентний запуск — і раптом у базі лежить сміття. Обмеження в БД — це страховка від реального світу, де не весь доступ іде через ваш ідеальний код.

Помилка №4: «вклеювати» значення в SQL через конкатенацію.
Навіть якщо ви поки не думаєте про безпеку, конкатенація ламається на лапках, пробілах і спецсимволах. А якщо думати про безпеку — відчиняє двері до SQL-інʼєкцій. Правильна звичка формується рано: запит — фіксований текст, значення — параметри (?). У наступній лекції ми побачимо, як це робиться технічно через JDBC.

Помилка №5: намагатися «обійтися без ключів», бо «і так знайдемо за полями».
Іноді хочеться не заводити id і оновлювати за «природними» полями (name, description). Це швидко призводить до неоднозначності й дивних багів: поле змінилося — і ви вже не можете знайти запис, або знайшли не той. Первинний ключ — не бюрократія, а фундамент адресації рядків.

Помилка №6: робити звʼязки «за змістом», але не фіксувати їх FOREIGN KEY.
Можна зберігати categoryId як число й «вірити», що воно існує в categories. Але тоді зʼявляться витрати з category_id = 9999, і ви довго налагоджуватимете, хто і коли це зробив. FK — це спосіб сказати базі: «не пропускай такі стани». Нехай вона буде вашим суворим охоронцем на вході даних.