Лекция 3. Работа с базами данных в веб-приложениях

1. Введение в базы данных

Что такое база данных

База данных (БД) — это организованная коллекция структурированных данных, хранящихся в электронном виде и управляемых системой управления базами данных (СУБД). В контексте веб-приложений БД служит для:

Хранения данных пользователей, контента, настроек
Обеспечения целостности данных через ограничения и транзакции
Оптимизации доступа к данным через индексы и запросы
Масштабирования хранения и обработки больших объёмов информации

Типы баз данных

Реляционные (SQL) БД

Принцип: данные хранятся в таблицах со строгой схемой
Связи: между таблицами через внешние ключи
Язык запросов: SQL (Structured Query Language)
Примеры: PostgreSQL, MySQL, SQLite, Oracle, SQL Server

Преимущества:

ACID-транзакции (Atomicity, Consistency, Isolation, Durability)
Строгая схема данных
Мощные возможности запросов
Зрелость и надёжность

Нереляционные (NoSQL) БД

Документные: MongoDB, CouchDB
Ключ-значение: Redis, DynamoDB
Колоночные: Cassandra, HBase
Графовые: Neo4j, ArangoDB

Преимущества:

Гибкая схема данных
Горизонтальное масштабирование
Высокая производительность для специфических задач

2. SQL основы

Основные типы данных

Тип	Описание	Примеры
`INTEGER`	Целые числа	`1`, `-5`, `1000`
`VARCHAR(n)`	Строки переменной длины	`'Hello'`, `'World'`
`TEXT`	Длинные тексты	`'Описание товара...'`
`BOOLEAN`	Логические значения	`TRUE`, `FALSE`
`DECIMAL(p,s)`	Числа с плавающей точкой	`99.99`, `123.45`
`DATE`	Даты	`'2024-01-15'`
`TIMESTAMP`	Дата и время	`'2024-01-15 14:30:00'`
`UUID`	Уникальные идентификаторы	`'550e8400-e29b-41d4-a716-446655440000'`

Создание таблиц

-- Создание таблицы пользователей
CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    email VARCHAR(255) UNIQUE NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Создание таблицы верификации OTP
CREATE TABLE otp_verification (
    id SERIAL PRIMARY KEY,
    email VARCHAR(255) NOT NULL,
    code VARCHAR(6) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (email) REFERENCES users(email) ON DELETE CASCADE
);

Основные SQL операции

SELECT — выборка данных

-- Получить всех пользователей
SELECT * FROM users;

-- Получить пользователя по email
SELECT * FROM users WHERE email = 'user@example.com';

-- Получить OTP коды для email
SELECT code, created_at FROM otp_verification
WHERE email = 'user@example.com'
ORDER BY created_at DESC;

-- Подсчет пользователей
SELECT COUNT(*) as total_users FROM users;

-- Получить последний OTP код
SELECT code FROM otp_verification
WHERE email = 'user@example.com'
ORDER BY created_at DESC
LIMIT 1;

INSERT — вставка данных

-- Добавить пользователя
INSERT INTO users (email)
VALUES ('user@example.com');

-- Добавить OTP код
INSERT INTO otp_verification (email, code)
VALUES ('user@example.com', '123456');

-- Добавить несколько OTP кодов
INSERT INTO otp_verification (email, code) VALUES
('user@example.com', '654321'),
('admin@example.com', '789012');

UPDATE — обновление данных

-- Обновить email пользователя
UPDATE users
SET email = 'newemail@example.com'
WHERE email = 'oldemail@example.com';

-- Обновить OTP код
UPDATE otp_verification
SET code = '999999'
WHERE email = 'user@example.com'
AND created_at = (SELECT MAX(created_at) FROM otp_verification WHERE email = 'user@example.com');

DELETE — удаление данных

-- Удалить пользователя (OTP коды удалятся автоматически из-за CASCADE)
DELETE FROM users WHERE email = 'user@example.com';

-- Удалить старые OTP коды (старше 10 минут)
DELETE FROM otp_verification
WHERE created_at < NOW() - INTERVAL '10 minutes';

Связи между таблицами

Внешние ключи (Foreign Keys)

-- Связь уже создана в CREATE TABLE
-- otp_verification.email -> users.email

-- Добавить дополнительную связь по ID (если нужно)
ALTER TABLE otp_verification ADD COLUMN user_id INTEGER;
ALTER TABLE otp_verification
ADD CONSTRAINT fk_otp_user_id
FOREIGN KEY (user_id) REFERENCES users(id);

JOIN операции

-- Получить пользователей с их последними OTP кодами
SELECT
    u.email,
    o.code,
    o.created_at
FROM users u
LEFT JOIN otp_verification o ON u.email = o.email;

-- Получить всех OTP коды с информацией о пользователях
SELECT
    u.email,
    o.code,
    o.created_at
FROM otp_verification o
JOIN users u ON o.email = u.email
ORDER BY o.created_at DESC;

3. Индексы и производительность

Что такое индексы

Индекс — это структура данных, которая ускоряет поиск и сортировку в таблице. Индексы создаются по одному или нескольким столбцам.

Типы индексов

Создание индексов

-- Индекс по email пользователей (уже уникальный)
CREATE UNIQUE INDEX idx_users_email ON users(email);

-- Индекс по email в OTP таблице
CREATE INDEX idx_otp_email ON otp_verification(email);

-- Индекс по дате создания OTP
CREATE INDEX idx_otp_created_at ON otp_verification(created_at);

-- Составной индекс для быстрого поиска OTP по email и дате
CREATE INDEX idx_otp_email_created ON otp_verification(email, created_at);

Анализ производительности запросов

-- Показать план выполнения запроса поиска OTP
EXPLAIN SELECT * FROM otp_verification WHERE email = 'user@example.com';

-- Простой анализ
EXPLAIN ANALYZE SELECT * FROM otp_verification
WHERE email = 'user@example.com'
ORDER BY created_at DESC LIMIT 1;

4. Транзакции и ACID

Понятие транзакции

Транзакция — это последовательность операций с БД, которая выполняется как единое целое. Либо все операции выполняются успешно, либо ни одна не выполняется.

ACID свойства

Atomicity (Атомарность) — все операции транзакции выполняются или не выполняются
Consistency (Согласованность) — БД остается в корректном состоянии
Isolation (Изолированность) — транзакции не влияют друг на друга
Durability (Долговечность) — изменения сохраняются после завершения транзакции

Примеры транзакций

-- Начало транзакции
BEGIN;

-- Добавить пользователя
INSERT INTO users (email) VALUES ('user@example.com');

-- Добавить OTP код
INSERT INTO otp_verification (email, code) VALUES ('user@example.com', '123456');

-- Подтвердить изменения
COMMIT;

-- В случае ошибки - откатить
-- ROLLBACK;

Уровни изоляции

-- Установка уровня изоляции
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;

-- Основные уровни:
-- READ COMMITTED - читает только зафиксированные данные (по умолчанию)
-- SERIALIZABLE - полная изоляция транзакций

5. ORM и работа с БД из приложений

Что такое ORM

Object-Relational Mapping (ORM) — это технология программирования, которая связывает объекты в коде с таблицами в БД. ORM позволяет работать с БД используя объектно-ориентированный подход.

Миграции схемы БД

Настройка Alembic

# Установка Alembic
pip install alembic

# Инициализация в проекте
alembic init alembic

# Настройка alembic.ini
# sqlalchemy.url = sqlite:///./app.db

Создание миграции

# Автоматическое создание миграции
alembic revision --autogenerate -m "Add users and otp_verification tables"

# Применение миграции
alembic upgrade head

# Откат миграции
alembic downgrade -1

Пример миграции

"""Add users and otp_verification tables

Revision ID: 001
Revises:
Create Date: 2024-01-15 10:00:00.000000

"""
from alembic import op
import sqlalchemy as sa

def upgrade():
    # Создание таблицы users
    op.create_table('users',
        sa.Column('id', sa.Integer(), nullable=False),
        sa.Column('email', sa.String(255), nullable=False),
        sa.Column('created_at', sa.DateTime(), nullable=True),
        sa.PrimaryKeyConstraint('id'),
        sa.UniqueConstraint('email')
    )

    # Создание таблицы otp_verification
    op.create_table('otp_verification',
        sa.Column('id', sa.Integer(), nullable=False),
        sa.Column('email', sa.String(255), nullable=False),
        sa.Column('code', sa.String(6), nullable=False),
        sa.Column('created_at', sa.DateTime(), nullable=True),
        sa.PrimaryKeyConstraint('id'),
        sa.ForeignKeyConstraint(['email'], ['users.email'], ondelete='CASCADE')
    )

    # Создание индексов
    op.create_index('idx_otp_email', 'otp_verification', ['email'])
    op.create_index('idx_otp_created_at', 'otp_verification', ['created_at'])

def downgrade():
    # Удаление индексов
    op.drop_index('idx_otp_created_at', table_name='otp_verification')
    op.drop_index('idx_otp_email', table_name='otp_verification')

    # Удаление таблиц
    op.drop_table('otp_verification')
    op.drop_table('users')

Управление миграциями

# Просмотр истории миграций
alembic history

# Просмотр текущей версии
alembic current

# Применение до конкретной версии
alembic upgrade 001

# Откат до конкретной версии
alembic downgrade 000

# Создание пустой миграции
alembic revision -m "Custom migration"

Пример добавления поля

"""Add status field to users table

Revision ID: 002
Revises: 001
Create Date: 2024-01-16 10:00:00.000000

"""
from alembic import op
import sqlalchemy as sa

def upgrade():
    # Добавление поля status в таблицу users
    op.add_column('users', sa.Column('status', sa.String(20), nullable=True, default='active'))

    # Обновление существующих записей
    op.execute("UPDATE users SET status = 'active' WHERE status IS NULL")

    # Создание индекса по статусу
    op.create_index('idx_users_status', 'users', ['status'])

def downgrade():
    # Удаление индекса
    op.drop_index('idx_users_status', table_name='users')

    # Удаление поля
    op.drop_column('users', 'status')

Интеграция с FastAPI

# main.py - автоматическое применение миграций при запуске
from alembic.config import Config
from alembic import command

def run_migrations():
    """Применить миграции при запуске приложения"""
    alembic_cfg = Config("alembic.ini")
    command.upgrade(alembic_cfg, "head")

# В FastAPI приложении
from contextlib import asynccontextmanager

@asynccontextmanager
async def lifespan(app: FastAPI):
    # Применение миграций при запуске
    run_migrations()
    yield

app = FastAPI(lifespan=lifespan)

6. Подключение к БД в веб-приложениях

Connection Pooling

from sqlalchemy import create_engine

# Настройка пула соединений
engine = create_engine(
    'sqlite:///books.db',
    pool_size=5,           # Количество соединений в пуле
    max_overflow=10        # Дополнительные соединения
)

Dependency Injection (FastAPI)

from fastapi import Depends
from sqlalchemy.orm import Session

def get_db():
    db = SessionLocal()
    try:
        yield db
    finally:
        db.close()

@app.get("/users/")
def get_users(db: Session = Depends(get_db)):
    users = db.query(User).all()
    return users

@app.post("/users/")
def create_user(email: str, db: Session = Depends(get_db)):
    user = User(email=email)
    db.add(user)
    db.commit()
    return user

@app.post("/otp/")
def create_otp(email: str, code: str, db: Session = Depends(get_db)):
    otp = OTPVerification(email=email, code=code)
    db.add(otp)
    db.commit()
    return otp

@app.get("/otp/{email}")
def get_latest_otp(email: str, db: Session = Depends(get_db)):
    otp = db.query(OTPVerification).filter(
        OTPVerification.email == email
    ).order_by(OTPVerification.created_at.desc()).first()
    return otp

Обработка ошибок БД

from fastapi import HTTPException

@app.post("/books/")
def create_book(title: str, author: str, year: int, price: int, db: Session = Depends(get_db)):
    try:
        book = Book(title=title, author=author, year=year, price=price)
        db.add(book)
        db.commit()
        return book
    except Exception as e:
        db.rollback()
        raise HTTPException(status_code=500, detail="Database error")

7. Безопасность работы с БД

SQL-инъекции

Проблема: Небезопасная конкатенация строк в SQL-запросах

# НЕПРАВИЛЬНО - уязвимо к SQL-инъекциям
query = f"SELECT * FROM users WHERE username = '{username}'"

# ПРАВИЛЬНО - использование параметризованных запросов
query = "SELECT * FROM users WHERE username = %s"
cursor.execute(query, (username,))

ORM защита

# SQLAlchemy автоматически защищает от SQL-инъекций
user = db.query(User).filter(User.username == username).first()

# Использование bind параметров
stmt = text("SELECT * FROM users WHERE username = :username")
result = db.execute(stmt, {"username": username})

Права доступа

-- Создание пользователя БД
CREATE USER app_user WITH PASSWORD 'password';

-- Предоставление прав на таблицу
GRANT SELECT, INSERT, UPDATE, DELETE ON books TO app_user;

Шифрование данных

import hashlib

# Простое хеширование паролей
def hash_password(password: str) -> str:
    return hashlib.sha256(password.encode()).hexdigest()

8. Оптимизация производительности

Анализ медленных запросов

-- Показать план выполнения
EXPLAIN ANALYZE SELECT * FROM books WHERE author = 'Толстой';

Кэширование

from functools import lru_cache

# Кэширование в памяти
@lru_cache(maxsize=100)
def get_book_by_id(book_id: int):
    return db.query(Book).filter(Book.id == book_id).first()

9. Резервное копирование и восстановление

Создание бэкапов

# Бэкап SQLite базы
cp books.db backup_$(date +%Y%m%d).db

# Бэкап PostgreSQL
pg_dump -h localhost -U username -d books_db > backup.sql

Восстановление

# Восстановление SQLite
cp backup_20240115.db books.db

# Восстановление PostgreSQL
psql -h localhost -U username -d books_db < backup.sql

10. Мониторинг и диагностика

Мониторинг производительности

-- Размер таблицы
SELECT pg_size_pretty(pg_total_relation_size('books'));

-- Количество записей
SELECT COUNT(*) FROM books;

Логирование

import logging

# Настройка логирования
logging.basicConfig(level=logging.INFO)
logger = logging.getLogger(__name__)

# Логирование операций
def create_book(title: str, author: str):
    logger.info(f"Creating book: {title} by {author}")
    # ... создание книги

11. Современные подходы

Миграции схемы

# Alembic миграция
from alembic import op
import sqlalchemy as sa

def upgrade():
    op.create_table('books',
        sa.Column('id', sa.Integer(), nullable=False),
        sa.Column('title', sa.String(255), nullable=False),
        sa.Column('author', sa.String(255), nullable=False),
        sa.Column('year', sa.Integer(), nullable=True),
        sa.Column('price', sa.Integer(), nullable=True),
        sa.PrimaryKeyConstraint('id')
    )

def downgrade():
    op.drop_table('books')

12. Практические рекомендации

Лучшие практики

Всегда используйте параметризованные запросы для защиты от SQL-инъекций
Создавайте индексы для часто используемых полей в WHERE и ORDER BY
Используйте транзакции для операций, которые должны выполняться атомарно
Настройте connection pooling для эффективного использования соединений
Регулярно создавайте бэкапы и тестируйте процедуры восстановления
Мониторьте производительность и оптимизируйте медленные запросы
Используйте миграции для управления схемой БД
Применяйте принцип наименьших привилегий для пользователей БД

Типичные ошибки

❌ Неправильно:

# SQL-инъекция
query = f"SELECT * FROM users WHERE username = '{username}'"

# Отсутствие транзакций
db.add(user)
db.add(order)
db.commit()  # Если order не добавится, user останется

✅ Правильно:

# Безопасный запрос
query = "SELECT * FROM users WHERE username = %s"
cursor.execute(query, (username,))

# Транзакция
with db.begin():
    db.add(user)
    db.add(order)
    # Автоматический commit или rollback

Заключение

Работа с базами данных — критически важная часть разработки веб-приложений. Понимание:

Основ SQL и реляционной модели данных
Принципов ACID и управления транзакциями
ORM технологий для удобной работы с БД
Безопасности и защиты от атак
Оптимизации производительности и мониторинга
Резервного копирования и восстановления

позволяет создавать надёжные, безопасные и производительные веб-приложения, способные эффективно работать с большими объёмами данных.

Современные веб-приложения требуют не только знания основ, но и понимания современных подходов: микросервисной архитектуры, NoSQL решений, кэширования и горизонтального масштабирования.