Знакомство с 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 — это способ сказать базе: «не пропускай такие состояния», и пусть она будет вашим строгим охранником на входе данных.