Tools: SQLite’s Flexible Typing: Storage Types and Column Affinity

Storage Type Determination ## 1. Values Specified as SQL Literals ## 2. Values Bound Through API Functions ## Expression Result Types ## Column Affinity Determination ## How SQLite Determines Column Affinity ## 1. INTEGER affinity ## 2. TEXT affinity ## 3. NONE affinity ## 4. REAL affinity ## 5. NUMERIC affinity ## Order of Evaluation Matters ## CREATE TABLE AS SELECT ## What’s Coming Next ## HexmosTech / git-lrc ## Free, Unlimited AI Code Reviews That Run on Commit ## See It In Action Hello, I'm Maneshwar. I'm working on git-lrc: a Git hook for Checking AI generated code. In the previous post, we discussed datatype management inside SQLite’s Virtual Machine (VM) and how the VM is responsible for assigning storage types and performing conversions. Today we take a deeper look at two key concepts that make SQLite unique among database systems: Together, these explain why SQLite is often described as “typeless” and how it still manages to remain compatible with traditional SQL databases. SQLite is frequently described as a typeless database engine. This means it does not enforce strict domain constraints on table columns. In most cases, any type of value can be stored in any column, regardless of the column’s declared SQL type. There is one notable exception: the INTEGER PRIMARY KEY column (the rowid). This column can only store integer values. If the VM encounters a value that cannot be interpreted as an integer for this column, the insertion is rejected. SQLite even allows tables to be created without specifying column types at all: Since there is no strict typing requirement, the question becomes: How does SQLite decide what storage type a value should have? The VM determines the initial storage type based on how the value enters the system. When a value appears directly in an SQL statement, SQLite determines its storage type according to its syntax. In this notation, the hexadecimal digits define the raw byte sequence stored in the database. If a value does not match any of these patterns, the VM rejects it and query execution fails. Values can also enter SQLite through parameter binding using the sqlite3_bind_* API family. Each binding function explicitly determines the storage type: In this case, SQLite simply uses the storage type closest to the native type provided by the application. Values produced during query execution such as results of expressions or function calls do not have predetermined types during statement preparation. Instead, their storage types are determined at runtime. The VM evaluates the expression and assigns a storage type based on the operator and the computed result. Similarly, user-defined SQL functions may return values with any storage type. Although SQLite is typeless, it still tries to remain compatible with traditional SQL databases that use static typing. To achieve this, SQLite introduces the concept of column affinity. Column affinity is not a strict rule, but rather a recommendation about the preferred storage type for values stored in a column. The column’s declared type influences how SQLite tries to convert values, but it does not strictly restrict them. Each column belongs to one of five affinity categories: Note that some names (TEXT, INTEGER, REAL) are also used as storage types internally. Context determines whether we are referring to affinity or storage type. SQLite determines column affinity by inspecting the declared SQL type in the CREATE TABLE statement. The VM checks the declaration using the following rules, evaluated in order: If the declared type contains the substring INT, the column receives INTEGER affinity. If the declared type contains CHAR, CLOB, or TEXT, the column receives TEXT affinity. Note that VARCHAR contains the substring CHAR, so it also maps to TEXT affinity. If the declared type contains BLOB, or if no type is specified, the column receives NONE affinity. If the declared type contains REAL, FLOA, or DOUB, the column receives REAL affinity. If none of the previous rules match, the column receives NUMERIC affinity. SQLite applies these rules in order, and the pattern matching is case-insensitive. Even though it contains the substring BLOB, the substring INT appears earlier in the rule list. Therefore, the column receives INTEGER affinity, not NONE. SQLite is intentionally forgiving—even misspelled type declarations still map to some affinity. Another interesting case occurs when tables are created using: Now that we understand: we can finally examine how these rules interact during query execution. In the next post, we’ll walk through data conversion with a simple example and see how the VM dynamically converts values when evaluating SQL expressions. AI agents write code fast. They also silently remove logic, change behavior, and introduce bugs -- without telling you. You often find out in production. git-lrc fixes this. It hooks into git commit and reviews every diff before it lands. 60-second setup. Completely free. Any feedback or contributors are welcome! It’s online, source-available, and ready for anyone to use. ⭐ Star it on GitHub: Free, Unlimited AI Code Reviews That Run on Commit AI agents write code fast. They also silently remove logic, change behavior, and introduce bugs -- without telling you. You often find out in production. git-lrc fixes this. It hooks into git commit and reviews every diff before it lands. 60-second setup. Completely free. See git-lrc catch serious security issues such as leaked credentials, expensive cloud operations, and sensitive material in log statements Templates let you quickly answer FAQs or store snippets for re-use. Are you sure you want to hide this comment? It will become hidden in your post, but will still be visible via the comment's permalink. Hide child comments as well For further actions, you may consider blocking this person and/or reporting abuse CODE_BLOCK: CREATE TABLE T1(a, b, c); Enter fullscreen mode Exit fullscreen mode CODE_BLOCK: CREATE TABLE T1(a, b, c); CODE_BLOCK: CREATE TABLE T1(a, b, c); CODE_BLOCK: INSERT INTO t1 VALUES('hello'); Enter fullscreen mode Exit fullscreen mode CODE_BLOCK: INSERT INTO t1 VALUES('hello'); CODE_BLOCK: INSERT INTO t1 VALUES('hello'); CODE_BLOCK: INSERT INTO t1 VALUES(123); Enter fullscreen mode Exit fullscreen mode CODE_BLOCK: INSERT INTO t1 VALUES(123); CODE_BLOCK: INSERT INTO t1 VALUES(123); CODE_BLOCK: INSERT INTO t1 VALUES(3.14); INSERT INTO t1 VALUES(2e5); Enter fullscreen mode Exit fullscreen mode CODE_BLOCK: INSERT INTO t1 VALUES(3.14); INSERT INTO t1 VALUES(2e5); CODE_BLOCK: INSERT INTO t1 VALUES(3.14); INSERT INTO t1 VALUES(2e5); CODE_BLOCK: INSERT INTO t1 VALUES(NULL); Enter fullscreen mode Exit fullscreen mode CODE_BLOCK: INSERT INTO t1 VALUES(NULL); CODE_BLOCK: INSERT INTO t1 VALUES(NULL); CODE_BLOCK: INSERT INTO t1 VALUES(X'ABCD'); Enter fullscreen mode Exit fullscreen mode CODE_BLOCK: INSERT INTO t1 VALUES(X'ABCD'); CODE_BLOCK: INSERT INTO t1 VALUES(X'ABCD'); CODE_BLOCK: sqlite3_bind_int(...) sqlite3_bind_text(...) sqlite3_bind_blob(...) Enter fullscreen mode Exit fullscreen mode CODE_BLOCK: sqlite3_bind_int(...) sqlite3_bind_text(...) sqlite3_bind_blob(...) CODE_BLOCK: sqlite3_bind_int(...) sqlite3_bind_text(...) sqlite3_bind_blob(...) CODE_BLOCK: SELECT 10 + '20'; Enter fullscreen mode Exit fullscreen mode CODE_BLOCK: SELECT 10 + '20'; CODE_BLOCK: SELECT 10 + '20'; CODE_BLOCK: INT INTEGER BIGINT SMALLINT Enter fullscreen mode Exit fullscreen mode CODE_BLOCK: INT INTEGER BIGINT SMALLINT CODE_BLOCK: INT INTEGER BIGINT SMALLINT CODE_BLOCK: CHAR VARCHAR TEXT CLOB Enter fullscreen mode Exit fullscreen mode CODE_BLOCK: CHAR VARCHAR TEXT CLOB CODE_BLOCK: CHAR VARCHAR TEXT CLOB CODE_BLOCK: BLOB CREATE TABLE t1(a); Enter fullscreen mode Exit fullscreen mode CODE_BLOCK: BLOB CREATE TABLE t1(a); CODE_BLOCK: BLOB CREATE TABLE t1(a); CODE_BLOCK: REAL FLOAT DOUBLE DOUBLE PRECISION Enter fullscreen mode Exit fullscreen mode CODE_BLOCK: REAL FLOAT DOUBLE DOUBLE PRECISION CODE_BLOCK: REAL FLOAT DOUBLE DOUBLE PRECISION CODE_BLOCK: DECIMAL BOOLEAN DATE NUMERIC Enter fullscreen mode Exit fullscreen mode CODE_BLOCK: DECIMAL BOOLEAN DATE NUMERIC CODE_BLOCK: DECIMAL BOOLEAN DATE NUMERIC CODE_BLOCK: BLOBINT Enter fullscreen mode Exit fullscreen mode CODE_BLOCK: CREATE TABLE new_table AS SELECT ... Enter fullscreen mode Exit fullscreen mode CODE_BLOCK: CREATE TABLE new_table AS SELECT ... CODE_BLOCK: CREATE TABLE new_table AS SELECT ... - Storage type determination - Column affinity determination - TEXT Values enclosed in quotes are interpreted as strings. - INTEGER Unquoted numbers without a decimal point or exponent. - REAL Numbers containing a decimal point or exponent. - sqlite3_bind_int → INTEGER - sqlite3_bind_double → REAL - sqlite3_bind_text → TEXT - sqlite3_bind_blob → BLOB - The declared type of each column is derived from the affinity of the expression in the SELECT clause. - Default values for these columns are NULL. - The implicit rowid column always has INTEGER type and cannot be NULL. - How SQLite assigns storage types - How it determines column affinity - 🤖 AI agents silently break things. Code removed. Logic changed. Edge cases gone. You won't notice until production. - 🔍 Catch it before it ships. AI-powered inline comments show you exactly what changed and what looks wrong. - 🔁 Build a habit, ship better code. Regular review → fewer bugs → more robust code → better results in your team. - 🔗 Why git? Git is universal. Every editor, every IDE, every AI…