How SQLite Converts Data: Affinity Rules 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 explored storage type determination and column affinity.

We saw how SQLite assigns an initial storage type to values and how columns have a preferred type through affinity.

Today we complete the picture by looking at data conversion, the step where SQLite decides whether a value should remain as it is or be converted to match the column’s affinity.

This is where SQLite’s flexible typing model becomes practical and predictable.

Data Conversion in SQLite

SQLite maintains a relationship between storage types (INTEGER, REAL, TEXT, BLOB, NULL) and column affinity types (TEXT, NUMERIC, INTEGER, REAL, NONE).

When a value is inserted into a table column, the VM performs the following sequence:

1. Determine the initial storage type of the input value.
2. Determine the affinity type of the destination column.
3. Attempt to convert the value to match the column affinity, if appropriate.

If the value already satisfies the affinity rules, it is stored as-is. Otherwise, the VM may convert the value, provided the conversion is lossless and reversible.

Let’s examine how this works for each affinity type.

1. TEXT Affinity

Columns with TEXT affinity prefer textual values.

• Values with storage types TEXT, NULL, or BLOB are stored without modification.
• Numeric values (INTEGER or REAL) are converted into their textual representation before storage.

Example:

INSERT INTO t1(text_column) VALUES(123);

Even though 123 starts as an INTEGER, SQLite converts it to "123" and stores it as TEXT.

2. NUMERIC Affinity

Columns with NUMERIC affinity are the most flexible.

They can store all five storage types, but when a TEXT value is inserted, SQLite tries to interpret it as a number.

• If the text can be converted exactly to an integer or real number, it is stored as INTEGER or REAL.
• If conversion fails, the value remains TEXT.

Example:

INSERT INTO t1(numeric_column) VALUES('200');

The string '200' becomes the integer 200.

But:

INSERT INTO t1(numeric_column) VALUES('hello');

remains stored as TEXT.

SQLite never attempts conversion for NULL or BLOB values.

3. INTEGER Affinity

INTEGER affinity behaves almost the same as NUMERIC affinity.

The difference appears when a value looks like a floating-point number but does not actually contain a fractional part.

Example:

INSERT INTO t1(integer_column) VALUES(10.0);

Since 10.0 has no fractional component, SQLite converts it to the integer 10.

This ensures that integer columns remain compact and efficient when possible.

4. REAL Affinity

REAL affinity also behaves like NUMERIC affinity but prefers floating-point representation.

If an integer value is inserted, SQLite converts it to a floating-point number.

Example:

INSERT INTO t1(real_column) VALUES(10);

Internally, this becomes 10.0.

However, SQLite includes an optimization:
small real numbers without fractional parts may still be stored on disk as integers to save space. They are converted to floating-point format only when read.

5. NONE Affinity

Columns with NONE affinity have no preferred storage type.

SQLite simply stores whatever value arrives, without performing any conversion.

This is the most flexible behavior and typically occurs when:

• The column type is declared as BLOB, or
• No type is specified at all.

A Simple Example

To understand these rules more concretely, consider a table where all columns are typeless:

CREATE TABLE T1(a, b, c);

Since no column types are declared, each column receives NONE affinity.

Now suppose we execute:

INSERT INTO T1 VALUES(177, NULL, 'hello');

The initial storage types assigned by the VM are:

Column	Value	Storage Type
a	177	INTEGER
b	NULL	NULL
c	'hello'	TEXT

Because all columns have NONE affinity, SQLite performs no conversions.

The values are stored exactly as they were received.

The Record Stored in the B-tree

The record occupies 11 bytes, composed of a header and data section.

Let’s break it down.

Header

The header is 4 bytes long.

1. Header size
   • Value: 4
   • Encoded as: 0x04
2. Type for column a
   • Type code: 2 (2-byte signed integer)
   • Encoded as: 0x02
3. Type for column b
   • Type code: 0 (NULL)
   • Encoded as: 0x00
4. Type for column c
   • Type code: 22
   • Indicates TEXT with length (22 − 12) / 2 = 5 bytes
   • Encoded as: 0x16

Data Section

After the header, the actual values appear.

• Data 1: 00 B1 → integer 177
• Data 2: NULL → occupies no bytes
• Data 3: ASCII bytes for "hello"

  68 65 6C 6C 6F

SQLite does not store a terminating zero for strings.

This example highlights several key ideas:

• Types belong to values, not columns.
• Column affinity only influences conversion; it does not enforce strict typing.
• The VM encodes values efficiently based on their storage type.

Because of this design, SQLite can:

• Store heterogeneous data in the same column
• Minimize storage space
• Perform type conversions dynamically during query execution

All while maintaining compatibility with traditional SQL behavior.

What We’ve Built So Far

Across the last several posts, we’ve now covered:

• The SQLite Virtual Machine
• Bytecode execution
• Table and index record formats
• Internal datatypes
• Column affinity
• Data conversion rules

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:

HexmosTech / git-lrc

Free, Unlimited AI Code Reviews That Run on Commit

git-lrc

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 It In Action

See git-lrc catch serious security issues such as leaked credentials, expensive cloud operations, and sensitive material in log statements

git-lrc-intro-60s.mp4

Why

• 🤖 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…

View on GitHub