STRICT таблицы в SQLite: строгая типизация колонок

Зачем нужны STRICT-таблицы

SQLite славится своим вольным отношением к типам данных. Объявляешь колонку как INTEGER, суёшь туда строку "hello" — а SQLite только пожмёт плечами и спокойно сохранит её. Такая гибкость была осознанным архитектурным решением ещё в 90-х, но разработчиков, привыкших к Postgres или MySQL, она сбивает с толку и удобно прячет баги.

Именно эту проблему и решают STRICT-таблицы, появившиеся в SQLite 3.37. Строгая типизация включается отдельно для каждой таблицы, и с этого момента типы колонок начинают означать ровно то, что в них написано.

CREATE TABLE users (
    id    INTEGER PRIMARY KEY,
    name  TEXT NOT NULL,
    age   INTEGER
) STRICT;

INSERT INTO users (name, age) VALUES ('Ada', 30);
SELECT * FROM users;

Ключевое слово STRICT ставится после закрывающей скобки. Всё остальное выглядит как обычный CREATE TABLE. Разница проявляется в тот момент, когда вы пытаетесь записать в колонку значение неподходящего типа.

Что на самом деле проверяют STRICT-таблицы

В обычной таблице SQLite задействует механизм type affinity: он пытается привести значение к объявленному типу, а если не получается — сохраняет его как есть. В STRICT-таблице такое несоответствие сразу превращается в ошибку.

CREATE TABLE accounts (
    id      INTEGER PRIMARY KEY,
    balance INTEGER
) STRICT;

INSERT INTO accounts (balance) VALUES (100);     -- хорошо
INSERT INTO accounts (balance) VALUES ('100');   -- хорошо, '100' преобразуется без проблем
INSERT INTO accounts (balance) VALUES ('oops');  -- ошибка

Попробуйте то же самое на обычной (не-STRICT) таблице — и третий INSERT спокойно пройдёт: SQLite без зазрения совести сохранит строку 'oops' в колонке, которую вы объявили как INTEGER. А через пару месяцев агрегирующий запрос выдаст какую-то дичь, и вы убьёте полдня на поиски причины. STRICT-таблицы заставляют ошибку всплыть прямо при вставке — там, где её ещё легко починить.

Вот какую ошибку вы увидите:

Ошибка выполнения: невозможно сохранить значение TEXT в столбец INTEGER accounts.balance

Чётко, сразу, не отвертишься.

Пять допустимых типов

В STRICT-таблицах разрешены только пять типов:

• INTEGER — целые числа.
• REAL — числа с плавающей точкой.
• TEXT — строки.
• BLOB — сырые байты.
• ANY — любой тип, без приведения.

И всё. Привычные алиасы, которые SQLite обычно проглатывает без вопросов — VARCHAR(255), DOUBLE, BOOLEAN, DATETIME, INT, — внутри STRICT-таблицы вызовут ошибку:

CREATE TABLE bad (
    id   INTEGER PRIMARY KEY,
    name VARCHAR(255)
) STRICT;

Ошибка:

Parse error: unknown datatype for bad.name: "VARCHAR(255)"

Чтобы это починить, используйте одно из пяти канонических имён. VARCHAR(255) превращается в TEXT, DATETIME — тоже в TEXT (SQLite всё равно хранит даты как ISO-строки), а BOOLEAN становится INTEGER (со значениями 0 и 1).

Тип ANY как лазейка

ANY — единственный тип, который разрешает колонке в STRICT-таблице хранить разнородные значения. Удобно, например, для универсальной колонки value в таблице ключ-значение:

CREATE TABLE settings (
    key   TEXT PRIMARY KEY,
    value ANY
) STRICT;

INSERT INTO settings VALUES ('max_users', 100);
INSERT INTO settings VALUES ('site_name', 'Coddy');
INSERT INTO settings VALUES ('ratio', 1.5);

SELECT key, value, typeof(value) FROM settings;

ANY ведёт себя в STRICT-таблицах по-особенному: значения сохраняются без того приведения типов, которое это же слово подразумевает в обычных таблицах. Строка '100' остаётся строкой, а целое число 100 — целым числом. Вызовы typeof() в запросе выше это наглядно подтверждают.

В обычной (не-STRICT) таблице колонка с аффинностью ANY привела бы строки, похожие на числа, к числовому типу. А STRICT сохраняет исходный тип ровно таким, какой он есть.

STRICT и PRIMARY KEY

Есть один тонкий момент: в обычной таблице объявление INTEGER PRIMARY KEY — это особый случай. Такая колонка становится псевдонимом для rowid и принимает только целые числа. Остальные варианты первичного ключа ведут себя свободнее.

В STRICT-таблице тип колонки проверяется строго, независимо от того, является ли она первичным ключом:

CREATE TABLE products (
    sku   TEXT PRIMARY KEY,
    name  TEXT NOT NULL,
    price REAL
) STRICT;

INSERT INTO products VALUES ('A-001', 'Виджет', 9.99);
INSERT INTO products VALUES (42, 'Гизмо', 14.99);  -- 42 преобразуется в '42'? Нет — ошибка.

SELECT * FROM products;

Второй INSERT падает с ошибкой. В обычной таблице (без STRICT) число 42 молча улеглось бы в колонку TEXT первичного ключа. А здесь SQLite честно скажет, что так делать нельзя.

Совмещение STRICT и обычных таблиц

Режим STRICT включается на уровне таблицы, а не базы. В одном и том же файле спокойно уживаются строгая таблица users и расслабленная events. Внешние ключи между ними работают ровно так же, как и без STRICT.

CREATE TABLE users (
    id   INTEGER PRIMARY KEY,
    name TEXT NOT NULL
) STRICT;

CREATE TABLE events (
    id      INTEGER PRIMARY KEY,
    user_id INTEGER REFERENCES users(id),
    payload   -- тип не указан; применяются правила сродства типов
);

INSERT INTO users (name) VALUES ('Борис');
INSERT INTO events (user_id, payload) VALUES (1, 'вход');
INSERT INTO events (user_id, payload) VALUES (1, 42);

SELECT id, payload, typeof(payload) FROM events;

В таблице events нет ни STRICT, ни объявленного типа для payload, поэтому туда можно класть что угодно. Иногда полезно — но как поведение по умолчанию это рискованно. Оставляйте «бестиповое» хранение для случаев, когда вам действительно нужна колонка-«мешок всего подряд».

Когда использовать STRICT

Для новых схем ответ — «практически всегда». Цена смешная: одно ключевое слово на таблицу плюс необходимость помнить пять канонических имён типов. А выгода в том, что баги, которые обычно тихо накапливаются в данных, всплывают сразу — на том самом INSERT, который их породил.

STRICT можно не включать, если:

• Вы поддерживаете старую базу SQLite, где существующая схема завязана на нестрогую типизацию.
• Вы целитесь в SQLite старше версии 3.37 (октябрь 2021) — там этого ключевого слова просто нет.
• Вам реально нужно, чтобы колонка хранила значения разных типов. Но даже в этом случае лучше взять STRICT-таблицу с колонкой ANY, чем обычную таблицу: так всё остальное остаётся под контролем.

Короткий чек-лист для перевода обычной таблицы в STRICT:

• Замените VARCHAR, CHAR, NVARCHAR на TEXT.
• Замените DOUBLE, FLOAT, NUMERIC на REAL.
• Замените BOOLEAN, BIT, TINYINT на INTEGER.
• Замените DATETIME, TIMESTAMP, DATE на TEXT (или INTEGER, если храните unix-таймстампы).
• Допишите STRICT после закрывающей скобки.

Дальше: первичные ключи

STRICT-таблицы наводят порядок в том, как колонки хранят данные. Следующий логичный шаг — навести порядок в том, какая колонка идентифицирует строку. У первичных ключей в SQLite есть пара особенностей (особенно вокруг INTEGER PRIMARY KEY и rowid), которые стоит понимать до того, как вы начнёте проектировать реальную схему.