Что изменилось в sqlite-utils 4.1.1

Симон Уиллисон выпустил патч-версию своего популярного инструмента для работы с SQLite-базами данных. На первый взгляд изменение выглядит небольшим — исправлена одна конкретная ситуация в методе table.transform(). Но за этим лаконичным описанием скрывается по-настоящему коварная ловушка, которая могла приводить к молчаливому удалению или изменению данных — без единого сообщения об ошибке.

⚠ Важно для пользователей 4.x
Если вы используете table.transform() внутри транзакций при включённых внешних ключах (PRAGMA foreign_keys = ON) — обновление до 4.1.1 обязательно. Старое поведение могло незаметно уничтожать связанные строки.

Суть проблемы: transform, транзакции и внешние ключи

Чтобы понять, что именно исправлено, нужно разобраться в том, как table.transform() работает «под капотом».

table.transform() реализует паттерн, рекомендованный документацией SQLite: создаётся новая временная таблица с нужной схемой, данные копируются туда, старая таблица удаляется, а временная переименовывается на её место.

Этот подход позволяет делать то, что стандартный ALTER TABLE в SQLite не умеет: менять типы столбцов, удалять колонки, переопределять первичные ключи. Оператор ALTER TABLE в SQLite ограничен: он умеет добавлять колонки и переименовывать таблицы, но не может удалять колонки, менять их типы, изменять статус NOT NULL или первичный ключ.

Теперь представьте такой сценарий:

  1. Открыта явная транзакция (BEGIN).
  2. Включены внешние ключи: PRAGMA foreign_keys = ON.
  3. Вызывается table.transform() на таблице, на которую ссылается другая таблица через внешний ключ с действием ON DELETE CASCADE, ON DELETE SET NULL или ON DELETE SET DEFAULT.
  4. Внутри transform() удаляется старая версия таблицы.
  5. SQLite срабатывает — и автоматически применяет деструктивное действие к строкам в связанной таблице.

Проблема в том, что это происходило без единого предупреждения. Данные удалялись или обнулялись, а код продолжал работать как ни в чём не бывало.

Почему pragma нельзя изменить внутри транзакции

Здесь есть ещё один тонкий момент, связанный с ограничением SQLite. Включить или отключить ограничения внешних ключей в середине многооператорной транзакции (когда SQLite не находится в режиме автокоммита) невозможно. Попытка сделать это не возвращает ошибку — pragma просто не оказывает никакого эффекта.

Иными словами, если транзакция уже открыта, команда PRAGMA foreign_keys = OFF — стандартный способ безопасно перестроить таблицу — перестаёт работать. Отключить проверку внешних ключей на время трансформации невозможно. Именно поэтому удаление старой таблицы в рамках transform() запускало каскадные действия.


flowchart TD
    A["Открыта транзакция\n(BEGIN)"] --> B["PRAGMA foreign_keys = ON"]
    B --> C["table.transform() вызван"]
    C --> D{"Таблица имеет FK\nс CASCADE/SET NULL/SET DEFAULT?"}
    D -- Нет --> E["Трансформация выполняется\nбезопасно"]
    D -- Да --> F["Старая таблица удаляется\nвнутри transform()"]
    F --> G["SQLite применяет\nдеструктивное действие"]
    G --> H["⚠️ Связанные строки\nудалены или изменены\n— без предупреждения"]
    style H fill:#ff6b6b,color:#fff
    style E fill:#51cf66,color:#fff

Что изменилось в 4.1.1

Теперь метод table.transform() самостоятельно обнаруживает опасную комбинацию условий и выбрасывает исключение TransactionError (ошибка транзакции) до того, как начнёт вносить какие-либо изменения.

Конкретно: исключение возникает, если:

  • В данный момент открыта транзакция.
  • Включён PRAGMA foreign_keys.
  • Таблица, которую нужно трансформировать, является родительской для внешнего ключа с одним из деструктивных действий: CASCADE, SET NULL или SET DEFAULT.
ℹ Что такое TransactionError
TransactionError — исключение из модуля sqlite_utils.db, которое sqlite-utils использует для сигнализации о конфликте между состоянием транзакции и запрошенной операцией. Ранее оно уже применялось, например, при вызове db.enable_wal() внутри транзакции. Методы db.enable_wal() и db.disable_wal() теперь выбрасывают TransactionError, если вызваны в момент, когда транзакция открыта, — ранее они молча коммитили открытую транзакцию как побочный эффект смены режима журнала.

Разбор деструктивных действий ON DELETE

Чтобы понять, почему именно CASCADE, SET NULL и SET DEFAULT попали под защиту, стоит разобраться, что каждое из них делает.

ДействиеЧто происходит со связанными строкамиПотеря данных?
CASCADEСтроки в дочерней таблице удаляются вместе с родительской строкойДа, необратимо
SET NULLВ колонке внешнего ключа проставляется NULLСвязь разрушается
SET DEFAULTВ колонке проставляется значение по умолчаниюСвязь разрушается
NO ACTION / RESTRICTТранзакция откатывается или выдаётся ошибкаНет

Если настроено действие SET NULL, то при удалении родительского ключа (для ON DELETE SET NULL) столбцы дочернего ключа во всех строках дочерней таблицы, ссылавшихся на родительский ключ, получают значение NULL.

Действия SET DEFAULT аналогичны SET NULL, за тем исключением, что каждый из столбцов дочернего ключа получает значение по умолчанию, объявленное для этого столбца, вместо NULL.

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

Как это выглядит на практике

Предположим, у нас есть таблица orders (заказы), которая ссылается на customers (клиенты) с действием ON DELETE CASCADE:

import sqlite_utils

db = sqlite_utils.Database("shop.db")

# Создаём таблицы
db["customers"].insert({"id": 1, "name": "Иван Петров"}, pk="id")
db["orders"].insert(
    {"id": 101, "customer_id": 1, "total": 5000},
    pk="id",
    foreign_keys=[("customer_id", "customers", "id")]
)

# Это было опасно до 4.1.1:
with db.conn:  # открываем транзакцию
    # PRAGMA foreign_keys уже включён
    # transform() удалял таблицу customers внутри — и все orders удалялись каскадно!
    db["customers"].transform(column_order=["name", "id"])

В версии 4.1.1 этот код вместо молчаливого удаления заказов немедленно выбросит TransactionError с понятным описанием проблемы.

💡 Обходные пути
Если вам всё же нужно выполнить transform() в подобной ситуации, официальная документация по адресу https://sqlite-utils.datasette.io/en/stable/python-api.html#foreign-keys-and-transactions описывает рекомендуемые обходные пути. Например, можно выполнять трансформацию вне открытой транзакции, или временно отключить PRAGMA foreign_keys до начала транзакции.

Контекст: серия улучшений работы с транзакциями в 4.x

Это исправление — часть более широкой работы по повышению надёжности транзакционного поведения в ветке 4.x. Версия 4.0 помимо небольших, но значимых несовместимых изменений представила три крупные возможности: миграции базы данных, вложенные транзакции через новый метод db.atomic(), и поддержку составных внешних ключей.

В версии 4.0 был введён контекстный менеджер db.atomic(), который теперь используется для каждой операции записи — полная модель описана в разделе «Transactions and saving your changes».

Составные операции, такие как table.transform(), больше не коммитят имеющуюся открытую транзакцию — вместо этого они используют точки сохранения (savepoints) внутри неё.

Итог: почему это важно

Каждый раз, когда библиотека молча изменяет данные вместо того, чтобы сообщить об ошибке, она нарушает одно из ключевых правил безопасной разработки: явное лучше неявного. Исправление в 4.1.1 переводит потенциально катастрофическую ситуацию из разряда «тихой потери данных» в разряд «понятного исключения с возможностью принять решение».

Если вы используете sqlite-utils в production-проектах, особенно там, где активно задействованы внешние ключи с каскадными действиями — обновление до 4.1.1 настоятельно рекомендуется.

pip install --upgrade sqlite-utils
💡 Дополнительные ресурсы