Рекомендации по работе с JSONB в PostgreSQL¶

Этот документ содержит рекомендации по правильному и безопасному использованию типа данных JSONB в PostgreSQL из Go-кода.

Найденные проблемы¶

В текущей имплементации репозитория авторизации (auth_repository.go) используется небезопасный подход к работе с JSONB-данными:

Прямое форматирование SQL-запросов с использованием fmt.Sprintf()
Ручное экранирование строк, которое может быть неполным
Отсутствие параметризации запросов
Избыточная сложность кода и низкая читаемость

// Пример текущего подхода (небезопасно)
metadataStr := string(metadataJSON)
metadataStr = strings.ReplaceAll(metadataStr, "'", "''")

directQuery := fmt.Sprintf(`
    INSERT INTO users (id, metadata) VALUES ('%s', '%s')
`, id, metadataStr)

_, err = db.ExecContext(ctx, directQuery)

Преимущества параметризованных запросов¶

Безопасность: Защита от SQL-инъекций
Читаемость: Более чистый и понятный код
Переиспользование: Возможность повторного использования подготовленных запросов
Производительность: Более эффективное выполнение запросов на сервере
Типобезопасность: Корректное преобразование типов данных

Пример полной имплементации¶

func (r *Repository) CreateUserWithJSON(ctx context.Context, user *models.User) error {
    // Подготовка JSON-метаданных
    metadataObj := map[string]interface{}{
        "full_name": user.FullName,
        "settings": map[string]interface{}{
            "theme": "default",
            "notifications": true,
        },
    }

    metadataJSON, err := json.Marshal(metadataObj)
    if err != nil {
        return fmt.Errorf("ошибка маршалинга метаданных: %w", err)
    }

    // Начинаем транзакцию
    tx, err := r.db.BeginTxx(ctx, nil)
    if err != nil {
        return fmt.Errorf("ошибка начала транзакции: %w", err)
    }
    defer func() {
        if err != nil {
            tx.Rollback()
        }
    }()

    // Вставляем пользователя
    userQuery := `
        INSERT INTO users (
            id, email, password_hash, status, created_at, updated_at, metadata
        ) VALUES (
            $1, $2, $3, $4, $5, $6, $7
        )
    `

    _, err = tx.ExecContext(ctx, userQuery,
        user.ID,
        user.Email,
        user.PasswordHash,
        user.Status,
        user.CreatedAt,
        user.UpdatedAt,
        metadataJSON,
    )

    if err != nil {
        return fmt.Errorf("ошибка создания пользователя: %w", err)
    }

    // Фиксируем транзакцию
    if err = tx.Commit(); err != nil {
        return fmt.Errorf("ошибка фиксации транзакции: %w", err)
    }

    return nil
}

Тестирование¶

Для проверки различных подходов и их сравнения были созданы тесты в директории tests/. Запустите их с помощью скриптов:

./run_test.sh - запуск функциональных тестов
./run_benchmark.sh - запуск бенчмарков производительности

Особенности работы с UUID и JSONB одновременно¶

При работе с JSONB в таблицах, содержащих поля типа UUID, необходимо учитывать следующие особенности:

Для полей с типом UUID в PostgreSQL нужно передавать именно UUID объекты, а не строки
Преобразование строковых UUID в объекты UUID необходимо делать перед передачей в SQL-запрос

Пример правильного подхода:

// Модель с UUID хранящимся как строка
type Wallet struct {
    ID        string    // В Go используем строку
    CreatedBy string    // В Go используем строку
    Tags      []byte    // JSON в виде []byte
}

// Преобразуем ID из строки в UUID объект
var walletUUID uuid.UUID
if wallet.ID == "" {
    walletUUID = uuid.New()
    wallet.ID = walletUUID.String()
} else {
    // Преобразуем строку ID в UUID
    var err error
    walletUUID, err = uuid.Parse(wallet.ID)
    if err != nil {
        return fmt.Errorf("ошибка парсинга UUID для ID: %w", err)
    }
}

// Преобразуем CreatedBy из строки в UUID
userUUID, err := uuid.Parse(wallet.CreatedBy)
if err != nil {
    return fmt.Errorf("ошибка парсинга UUID для CreatedBy: %w", err)
}

// Маршалим JSON и преобразуем в строку
jsonStr := string(wallet.Tags)

// Запрос с параметрами
query := `
    INSERT INTO wallets (id, tags, created_by)
    VALUES ($1, $2::jsonb, $3)
`

// Выполняем запрос, передавая правильные типы
_, err = db.ExecContext(
    ctx, query,
    walletUUID,      // UUID объект для поля id (типа UUID)
    jsonStr,         // Строка для JSONB поля
    userUUID,        // UUID объект для поля created_by (типа UUID)
)

Типичные ошибки и их решения¶

Ошибка: invalid input syntax for type json Решение: Передавайте JSON как строку (string(jsonBytes)) с явным указанием типа (::jsonb)
Ошибка: invalid input syntax for type uuid: "some-uuid-string" Решение: Используйте uuid.Parse() для преобразования строк в UUID объекты
Ошибка: cannot use type []string as type []interface{} Решение: Конвертируйте в []interface{} перед маршалингом
Ошибка: sql: converting argument $N type: unsupported type []string Решение: Преобразуйте слайс в JSON с помощью json.Marshal перед передачей в запрос

Заключение¶

Безопасность работы с базой данных является критически важным аспектом разработки. Всегда используйте параметризованные запросы для работы с любыми данными, особенно с JSONB, которые могут содержать сложные структуры и специальные символы.

Используйте параметризованные запросы с явным указанием типа ::jsonb
Преобразуйте байты JSON в строку перед передачей: string(jsonBytes)
Для работы с массивами строк используйте промежуточное преобразование в []interface{}
Для полей типа UUID используйте объекты UUID, а не строки
Всегда тщательно проверяйте API методов для передачи данных

Более подробные примеры работы с JSONB можно найти в существующих репозиториях проекта. Имплементация в backend/storage/repository/blockchain_repository.go предоставляет безопасные альтернативы для работы с JSONB и UUID и может быть использована как образец для рефакторинга существующего кода.

Рекомендации по работе с JSONB в PostgreSQL¶

Найденные проблемы¶

Рекомендуемый подход¶

1. Базовый подход с параметризованными запросами¶

2. Подход с явным указанием типа JSONB¶

3. Использование именованных параметров (рекомендуется для сложных запросов)¶

4. Транзакционный подход для нескольких связанных операций¶

Преимущества параметризованных запросов¶

Рекомендации для работы с JSONB¶

Пример полной имплементации¶

Тестирование¶

Рекомендации по работе с массивами и слайсами в JSONB¶

Результаты тестирования разных подходов¶

Особенности работы с UUID и JSONB одновременно¶

Типичные ошибки и их решения¶

Заключение¶