Почему стоит использовать строгие (STRICT) таблицы в SQLite
Разработчик Эван Хан (Evan Hahn) в своём блоге объясняет, почему считает нужным использовать таблицы STRICT в SQLite, по его мнению, эта возможность недооценена. Строгая таблица получается добавлением ключевого слова STRICT в конец оператора CREATE TABLE, например: CREATE TABLE people (name TEXT) STRICT.
Главное преимущество, SQLite перестаёт разрешать вставку значения не того типа в колонку. В обычной («нестрогой») таблице команда INSERT INTO people_nonstrict (age) VALUES ('garbage'), где age, целочисленная колонка, отработает без единой ошибки, а в строгой таблице та же вставка завершится ошибкой о невозможности сохранить текстовое значение в целочисленной колонке. То же правило действует и для UPDATE. При этом если значение можно без потерь преобразовать к нужному типу, например, строку '123' в число, такая вставка по-прежнему разрешена даже в строгой таблице.
Второй плюс, строгие таблицы не дают создать колонку с «мусорным» или несуществующим типом. По умолчанию SQLite молча принимает любые указанные типы, включая опечатки и несуществующие названия вроде GARBAGE, DATETIME, JSON, UUID или BLOBB. В строгой таблице такие определения колонок вызывают ошибку уже при создании таблицы: разрешены только шесть типов, INT, INTEGER, REAL, TEXT, BLOB и ANY, и тип для каждой колонки обязателен (просто CREATE TABLE tbl (name) без указания типа не сработает). При этом гибкость не исчезает полностью: колонка с типом ANY принимает вообще любые значения, числа, текст, BLOB, даже в строгой таблице, хотя сам автор не нашёл этому применения на практике.
Среди недостатков Хан называет четыре. Во-первых, существующую нестрогую таблицу нельзя перевести в STRICT через ALTER, нужно вручную создать новую строгую таблицу с той же схемой, скопировать в неё данные командой вида INSERT ... SELECT, удалить старую таблицу и переименовать новую. Это рискованно, если в исходных данных уже встречаются значения не того типа: копирование просто упадёт с ошибкой, и данные придётся сначала очистить или привести к нужному типу. Если же вводить правило «делать строгими только новые таблицы», в проекте возникает непоследовательность, часть таблиц защищена, часть нет.
Во-вторых, сами разработчики SQLite не разделяют позицию автора: в официальной документации есть отдельная страница «The Advantages Of Flexible Typing» («Преимущества гибкой типизации»), где нестрогое поведение по умолчанию преподносится как осознанный выбор, а не недостаток. Там же приводятся легитимные сценарии для нестрогих таблиц, например, использование таблицы как хранилища «ключ-значение» или как места для разнородных атрибутов, а также импорт «грязного» CSV-файла без потери исходных данных. Хан также упоминает, что в исходном коде SQLite встречается комментарий, называющий нестрогие таблицы legacy («унаследованными»), но доверяет официальной документации больше, чем этому комментарию.
В-третьих, строгие таблицы появились только в SQLite версии 3.37.0, вышедшей в ноябре 2021 года, в более старых версиях этой возможности нет вовсе. Важная деталь совместимости: старая версия SQLite вообще не сможет прочитать базу данных, в которой есть хотя бы одна строгая таблица, даже если саму эту таблицу читать не пытаться.
В-четвёртых, автор разбирает вопрос производительности. Теоретически строгие таблицы должны работать чуть медленнее, поскольку SQLite дополнительно проверяет типы при вставке и обновлении данных. Хан провёл собственный неформальный эксперимент: вставил миллионы строк в таблицу со 100 колонками на нескольких машинах и не заметил ощутимой разницы в скорости, а размер файла на диске оказался одинаковым для строгой и нестрогой таблицы. Он допускает, что строгие таблицы теоретически могут работать даже быстрее, за счёт того, что исключается случайное несовпадение affinity (типового сходства) колонок, но специально это не измерял.
В заключение Хан пишет, что, на его взгляд, плюсы строгих таблиц перевешивают минусы: жёсткая типизация исключает целый класс ошибок и помогает поддерживать целостность данных. Он не считает это универсальным решением, но называет строгие таблицы простой в применении практикой, которая того стоит.
Ключевые факты
- Модификатор STRICT в конце CREATE TABLE не даёт вставить в INTEGER-колонку текст (ошибка при INSERT), кроме случаев, когда значение можно без потерь превратить в нужный тип, например, строку '123' в число.
- В строгих таблицах разрешены только шесть типов колонок, INT, INTEGER, REAL, TEXT, BLOB, ANY, попытка задать несуществующий тип вроде DATETIME, JSON, UUID или опечатку GARBAGE даёт ошибку уже при создании таблицы (в обычной таблице такие типы молча принимаются).
- Тип ANY сохраняет полную гибкость колонки даже в строгой таблице, можно хранить и числа, и текст, и BLOB.
- Перевести существующую таблицу в STRICT через ALTER нельзя, нужно вручную создать новую строгую таблицу, скопировать данные и переименовать её, что рискованно при уже сохранённых данных неверного типа.
- STRICT доступен только с SQLite 3.37.0 (ноябрь 2021); более старая версия SQLite вообще не может прочитать базу данных, где есть хотя бы одна строгая таблица.
Почему это важно
SQLite, один из самых массово используемых движков баз данных, но по умолчанию он ведёт себя нестрого: без единой ошибки позволяет положить текстовое значение в числовую колонку или создать колонку с несуществующим типом (например, из-за опечатки). Такое поведение, источник трудноуловимых багов: некорректные данные попадают в базу молча и всплывают только позже, в самый неподходящий момент. Модификатор STRICT добавляет в SQLite привычную для многих других СУБД жёсткую проверку типов, при вставке и обновлении данных, а также при самом создании таблицы. Автор считает эту возможность недооценённой именно потому, что она устраняет целый класс ошибок почти бесплатно: достаточно одного слова в определении таблицы.
Кому это важно
В первую очередь, разработчикам, которые проектируют схему базы данных SQLite с нуля и хотят с самого начала защититься от ошибок типов данных. Актуально для всех, кто встраивает SQLite в приложения, мобильные, десктопные, встраиваемые системы, локальные инструменты, и предпочитает получать явную ошибку при попытке записать данные не того типа, а не тихое искажение данных. Полезно и командам, которые вручную пишут SQL-миграции и хотят подстраховаться от опечаток вроде несуществующих типов колонок (DATETIME, JSON, UUID вместо предусмотренных SQLite типов).
Как это применить
Чтобы сделать таблицу строгой, достаточно дописать ключевое слово STRICT в конец оператора CREATE TABLE, например: CREATE TABLE people (name TEXT) STRICT. В строгой таблице разрешены только шесть типов колонок, INT, INTEGER, REAL, TEXT, BLOB и ANY, и каждой колонке обязательно нужно явно указать один из них (просто CREATE TABLE tbl (name) без типа не сработает). Если нужна колонка без строгой типизации, для неё можно использовать тип ANY, он принимает любые значения (числа, текст, BLOB) даже в строгой таблице. Перевести уже существующую таблицу в STRICT напрямую через ALTER нельзя: нужно создать новую строгую таблицу с той же схемой, скопировать в неё данные командой вида INSERT ... SELECT, удалить старую таблицу и переименовать новую, при этом если в старых данных уже встречаются значения не того типа, копирование завершится ошибкой и данные придётся сначала очистить или привести к нужному типу. Использовать STRICT можно только начиная с SQLite версии 3.37.0, вышедшей в ноябре 2021 года.
Можно ли доверять
Это личный опыт и мнение разработчика Эван Хана, а не официальная рекомендация SQLite, более того, сами разработчики SQLite придерживаются противоположной позиции: в официальной документации есть отдельная страница «The Advantages Of Flexible Typing» («Преимущества гибкой типизации»), где нестрогое поведение по умолчанию преподносится как осознанный выбор, а не недостаток. Хан честно признаёт контраргументы: разработчики SQLite приводят легитимные случаи для нестрогих таблиц (хранилище «ключ-значение», разнородные атрибуты, импорт «грязных» CSV-данных без потери информации), и сам он не считает строгие таблицы панацеей. Утверждение автора об отсутствии просадки в производительности основано на его собственном неформальном тесте (вставка миллионов строк в таблицу со 100 колонками), а не на строгом бенчмарке, он сам оговаривается, что тестировал не досконально. Материал получил заметный отклик на Hacker News, 280 баллов и 131 комментарий, что говорит о живом интересе технического сообщества, хотя объём обсуждения сам по себе не заменяет ни бенчмарк, ни проверку в бою.
Риски и подводные камни
Совместимость версий: старая версия SQLite (до 3.37.0) вообще не сможет прочитать базу данных, в которой есть хотя бы одна строгая таблица, даже если пытаться прочитать другие, обычные таблицы. Миграция существующих таблиц в STRICT ручная и рискованная: при некорректных данных старого типа копирование в новую таблицу упадёт с ошибкой, и потребуется предварительная очистка данных. Если применять STRICT только к новым таблицам, а старые оставлять как есть, в проекте возникает непоследовательная защита данных, часть таблиц под контролем, часть нет, а это может удивлять сильнее, чем единообразно слабая валидация. Наконец, теоретически строгие таблицы делают чуть больше работы при вставке и обновлении данных из-за проверки типов, хотя в собственном тесте автора разницы в скорости и размере файла на диске обнаружено не было.
«Мне не хочется ввязываться в спор о статической и динамической типизации, но в большинстве случаев я не согласен. Я сам сталкивался со множеством багов, когда неожиданный тип данных вызывал скрытые проблемы. Я бы предпочёл, чтобы такие ошибки сразу же громко проявлялись.»
— Эван Хан, автор блога evanhahn.com