Почему стоит предпочесть таблицы STRICT в SQLite

Эван Хан (Evan Hahn) выступает за добавление STRICT в определения таблиц SQLite. Строгая таблица обеспечивает соблюдение фиксированных типов колонок так же, как и большинство SQL-движков, вместо поведения SQLite по умолчанию, когда тип колонки служит скорее рекомендацией, а не правилом. Всё отличие сводится к одному ключевому слову в конце инструкции CREATE TABLE:

CREATE TABLE people (name TEXT) STRICT;

Поведение по умолчанию: type affinity

В обычной таблице SQLite объявленный тип колонки не ограничивает то, что в ней можно хранить. SQLite назначает каждой колонке type affinity - предпочтительный способ преобразования входящих значений, а затем сохраняет то, что получилось в итоге. Если значение можно преобразовать к этому типу без потерь, оно преобразуется; в противном случае исходное значение сохраняется без изменений. Поэтому колонка INTEGER без проблем примет 'garbage', и эта строка так и останется строкой. Из-за механизма affinity и '123', и 123 сохраняются как целое число 123, но значение '1O' (с буквой O вместо нуля), вставленное в целочисленную колонку, останется текстом '1O' - преобразование без потерь невозможно, поэтому SQLite сохраняет значение, а не отклоняет его.

Такая всеядность распространяется и на объявление схемы. SQLite позволяет объявлять колонки с типами, которых в ней вовсе нет: DATETIME, JSON, UUID или явная опечатка вроде BLOBB парсятся без ошибок и распределяются по affinity на основе сопоставления с шаблоном имени типа. Объявленный тип в лучшем случае служит документацией, а в худшем - скрытой ложью, поскольку его соблюдение никем не контролируется.

Корни этого уходят в историю проекта. Как объяснил на HN один из контрибьюторов SQLite (rogerbinns), SQLite начиналась как локальная база данных для разработки на основе dbm, где каждое хранимое значение фактически было строкой, а код автоматически конвертировал типы при необходимости: оператор + парсил строки в числа, складывал их и преобразовывал результат обратно в строку. Язык-обёртка TCL работал точно так же. Лишь в SQLite 3 (2004 год) появился настоящий бэкенд хранения с отдельными типами. Гибкая типизация - это наследие концепции "всё есть строка", позже задокументированное как намеренная возможность в статье "The Advantages Of Flexible Typing".

Что меняет STRICT

Строгая таблица принудительно проверяет объявленные типы:

• Вставка или обновление со значением неподходящего типа отклоняется. Попытка записать текст в колонку INTEGER приведёт к ошибке вместо скрытого сохранения строки. Преобразования без потерь по-прежнему работают - значение '123' в целочисленную колонку запишется нормально, так что проверка оценивает возможность точного представления, а не буквальный тип.
• Типы колонок должны быть настоящими. Разрешены только INT, INTEGER, REAL, TEXT, BLOB и ANY. Несуществующие типы вроде DATETIME или UUID вызовут ошибку ещё на этапе CREATE TABLE, выявляя опечатки и заблуждения о том, какие типы реально поддерживаются в SQLite.
• Каждая колонка должна иметь явно указанный тип; запрос CREATE TABLE tbl (name) будет отклонён.

Если колонка со смешанными типами действительно нужна, предусмотрен обходной путь - тип ANY: он принимает целые числа, текст, числа с плавающей точкой и blob'ы даже внутри строгой таблицы. Отличие от поведения по умолчанию заключается в том, что ANY явно декларирует намерение, вместо того чтобы так скрытно вела себя каждая колонка.

Это укладывается в классификацию из type-system-axes. Поведение SQLite по умолчанию - это эквивалент слабой динамической типизации для баз данных: типы проверяются поздно (или никогда), а значения неявно приводятся. Режим STRICT сдвигает модель в сторону сильной типизации: приведение выполняется только без потерь, в противном случае следует жёсткий отказ. Это перекликается с мыслью из заметки type-systems-vocabulary о неточности термина "строгая": реальная шкала здесь показывает, сколько неявного приведения допускает система, а не в какой момент происходят проверки. Параллель видна и в unsigned-sizes-c3-mistake: нестрогое поведение по умолчанию (беззнаковые размеры, гибкие колонки) кажется удобным, но незаметно пропускает целый класс багов, которые более строгий вариант громко отклонил бы.

Ограничения и компромиссы

Сделать существующую таблицу строгой через ALTER нельзя. Миграция выполняется вручную: создать новую строгую таблицу, перенести данные через INSERT ... SELECT, удалить старую таблицу и переименовать новую. Если в старой таблице уже лежат данные, нарушающие типы, копирование завершится с ошибкой, и данные придётся сначала очистить или привести через CAST - то есть проверка выполнит свою работу, просто позже, чем хотелось бы.

Для строгих таблиц требуется SQLite 3.37.0 (ноябрь 2021 года) или новее. Базу данных со строгой таблицей старые версии SQLite не смогут открыть вовсе, даже для чтения других, не связанных с ней таблиц.

Теоретически возможны вопросы к производительности - движок выполняет проверку типов при каждой вставке или обновлении, - но неформальный тест Хана (миллионы строк, 100 колонок) не выявил ощутимой разницы и показал идентичный размер на диске. Он предполагает, что строгие таблицы могут быть даже немного быстрее за счёт отсутствия несовпадений affinity, но замеров не проводил.

Сами разработчики SQLite этого энтузиазма не разделяют; на их странице о гибкой типизации перечислены вполне разумные сценарии её применения: чистые key-value хранилища, колонки для произвольных наборов атрибутов и импорт "грязных" CSV, где предпочтительнее сохранить каждое значение, чем потерять его из-за отклонённой вставки. Режим STRICT не станет вариантом по умолчанию: как отметил Саймон Уиллисон (Simon Willison) в обсуждении, SQLite относится к обратной совместимости почти как к святыне и не будет молча делать каждый CREATE TABLE строгим. По той же причине проверка внешних ключей отключена по умолчанию (хотя флаг SQLITE_DEFAULT_FOREIGN_KEYS=1 включает её при собственной сборке), а режим WITHOUT ROWID остаётся опциональным.

Практическая позиция Хана: предпочитать STRICT с самого начала; правило делать строгими все новые таблицы - разумный компромисс, пусть и ценой неоднородности правил внутри схемы. В обсуждении всплыли и другие пробелы SQLite в работе с типами - например, здесь по-прежнему нет отдельного типа для timestamp, хотя целочисленные Unix timestamp'ы работают со встроенными функциями даты и времени. О граблях на уровне индексов из той же серии "база данных молча сделала не то, что задумывалось", см. database-index-gotchas. STRICT задаёт дисциплину на уровне схемы; о дисциплине на уровне runtime (режим журнала, таймауты busy и жизненный цикл WAL, способный заблокировать читателей в базе, куда никто не пишет) см. sqlite-in-production.