{"id":358,"date":"2026-11-04T09:15:00","date_gmt":"2026-11-04T09:15:00","guid":{"rendered":"https:\/\/backendside.com\/blog\/?p=358"},"modified":"2026-11-21T11:34:19","modified_gmt":"2026-11-21T11:34:19","slug":"sqlite-data-types-type-affinity","status":"publish","type":"post","link":"https:\/\/backendside.com\/blog\/2026\/11\/04\/sqlite-data-types-type-affinity\/","title":{"rendered":"SQLite Data Types and Type Affinity: The Surprises That Bite You"},"content":{"rendered":"<p class=\"lead\">Coming to SQLite from PostgreSQL, MySQL or SQL Server, most developers assume a column declared <code>INTEGER<\/code> holds integers and a <code>VARCHAR(10)<\/code> holds at most ten characters. In SQLite, neither is true by default. SQLite uses <strong>dynamic typing<\/strong>: the type belongs to each value, and a column&rsquo;s declared type only expresses a preference called <strong>type affinity<\/strong>. That flexibility is deliberate and occasionally handy &mdash; but it produces some genuinely surprising bugs. Here is how the system works, the surprises it causes, and how to get strict typing back when you want it.<\/p>\n<h2>Five storage classes<\/h2>\n<p>Every value stored in SQLite has one of exactly five storage classes:<\/p>\n<table>\n<thead>\n<tr>\n<th>Storage class<\/th>\n<th>Holds<\/th>\n<\/tr>\n<\/thead>\n<tbody>\n<tr>\n<td><code>NULL<\/code><\/td>\n<td>The missing value<\/td>\n<\/tr>\n<tr>\n<td><code>INTEGER<\/code><\/td>\n<td>Signed integer, stored in 1&ndash;8 bytes depending on size (up to 64-bit)<\/td>\n<\/tr>\n<tr>\n<td><code>REAL<\/code><\/td>\n<td>8-byte IEEE floating-point number<\/td>\n<\/tr>\n<tr>\n<td><code>TEXT<\/code><\/td>\n<td>A string in the database encoding (usually UTF-8)<\/td>\n<\/tr>\n<tr>\n<td><code>BLOB<\/code><\/td>\n<td>Raw bytes, stored exactly as given<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<p>There is no separate date, boolean, decimal or fixed-length string type. You can always ask what a value really is with <code>typeof()<\/code>:<\/p>\n<pre style=\"background:#1e1d1b;color:#eceae6;padding:1rem 1.15rem;border-radius:8px;overflow-x:auto;font-size:.85rem;line-height:1.5;\"><code>SELECT typeof(42), typeof(4.2), typeof('42'), typeof(x'2A'), typeof(NULL);\n-- integer | real | text | blob | null<\/code><\/pre>\n<h2>How affinity is decided<\/h2>\n<p>Each column gets one of five affinities &mdash; INTEGER, TEXT, BLOB, REAL or NUMERIC &mdash; worked out from the declared type name by these rules, <strong>applied in order<\/strong>:<\/p>\n<ol>\n<li>If the type contains <code>INT<\/code> &rarr; INTEGER affinity.<\/li>\n<li>Otherwise, if it contains <code>CHAR<\/code>, <code>CLOB<\/code> or <code>TEXT<\/code> &rarr; TEXT affinity.<\/li>\n<li>Otherwise, if it contains <code>BLOB<\/code>, or no type is given &rarr; BLOB affinity.<\/li>\n<li>Otherwise, if it contains <code>REAL<\/code>, <code>FLOA<\/code> or <code>DOUB<\/code> &rarr; REAL affinity.<\/li>\n<li>Otherwise &rarr; NUMERIC affinity.<\/li>\n<\/ol>\n<p>Simple substring matching on the type name leads to some memorable results:<\/p>\n<table>\n<thead>\n<tr>\n<th>Declared type<\/th>\n<th>Affinity<\/th>\n<th>Why<\/th>\n<\/tr>\n<\/thead>\n<tbody>\n<tr>\n<td><code>VARCHAR(255)<\/code><\/td>\n<td>TEXT<\/td>\n<td>Contains CHAR; the 255 is ignored<\/td>\n<\/tr>\n<tr>\n<td><code>BIGINT<\/code>, <code>TINYINT<\/code><\/td>\n<td>INTEGER<\/td>\n<td>Contains INT<\/td>\n<\/tr>\n<tr>\n<td><code>FLOATING POINT<\/code><\/td>\n<td>INTEGER<\/td>\n<td>&ldquo;POINT&rdquo; contains INT, and rule 1 wins<\/td>\n<\/tr>\n<tr>\n<td><code>STRING<\/code><\/td>\n<td>NUMERIC<\/td>\n<td>Matches none of the keywords<\/td>\n<\/tr>\n<tr>\n<td><code>DECIMAL(10,2)<\/code><\/td>\n<td>NUMERIC<\/td>\n<td>No precision or scale is enforced<\/td>\n<\/tr>\n<tr>\n<td><code>BOOLEAN<\/code>, <code>DATE<\/code>, <code>DATETIME<\/code><\/td>\n<td>NUMERIC<\/td>\n<td>No such types exist<\/td>\n<\/tr>\n<tr>\n<td>(no type)<\/td>\n<td>BLOB<\/td>\n<td>Values stored exactly as given<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<p>Affinity then drives conversion on the way in. A column with <strong>TEXT<\/strong> affinity converts numbers to text. A column with <strong>INTEGER<\/strong> or <strong>NUMERIC<\/strong> affinity converts text that looks like a number into a number &mdash; and quietly keeps anything else as text. <strong>BLOB<\/strong> affinity converts nothing.<\/p>\n<h2>Surprise 1: text in an integer column<\/h2>\n<pre style=\"background:#1e1d1b;color:#eceae6;padding:1rem 1.15rem;border-radius:8px;overflow-x:auto;font-size:.85rem;line-height:1.5;\"><code>CREATE TABLE orders (id INTEGER PRIMARY KEY, qty INTEGER, note VARCHAR(5));\nINSERT INTO orders (qty, note) VALUES ('12', 'this is far longer than five');\nINSERT INTO orders (qty, note) VALUES ('twelve', 'ok');\n\nSELECT qty, typeof(qty), length(note) FROM orders;\n-- 12      | integer | 28\n-- twelve  | text    | 2<\/code><\/pre>\n<p>The string <code>'12'<\/code> was converted to an integer, but <code>'twelve'<\/code> was stored as text without complaint, and the &ldquo;five-character&rdquo; column accepted 28 characters. Your application, not the database, was expected to enforce both.<\/p>\n<h2>Surprise 2: leading zeros disappear<\/h2>\n<p>The flip side of helpful conversion: anything that looks numeric becomes a number in an INTEGER or NUMERIC column, and numbers have no leading zeros.<\/p>\n<pre style=\"background:#1e1d1b;color:#eceae6;padding:1rem 1.15rem;border-radius:8px;overflow-x:auto;font-size:.85rem;line-height:1.5;\"><code>CREATE TABLE customers (zip STRING, phone NUMERIC);\nINSERT INTO customers VALUES ('02134', '0044123456789');\nSELECT zip, phone FROM customers;\n-- 2134 | 44123456789<\/code><\/pre>\n<p><code>STRING<\/code> looks like a text type but has NUMERIC affinity, so the ZIP code lost its zero. Identifiers that happen to be made of digits &mdash; postal codes, phone numbers, account numbers, product codes &mdash; belong in <code>TEXT<\/code> columns. A related trap: <code>'3.0e+5'<\/code> stored in a NUMERIC column becomes the integer 300000.<\/p>\n<div style=\"border:1px solid #c5d3f8;background:linear-gradient(135deg,#eef2fd 0%,#ffffff 72%);border-radius:14px;padding:1.5rem 1.65rem;margin:2rem 0;\">\n<div style=\"font-size:.7rem;font-weight:700;letter-spacing:.08em;text-transform:uppercase;color:#2d5be3;margin-bottom:.55rem;\">&#128295; BackendSide Tool<\/div>\n<h4 style=\"margin:0 0 .45rem;font-size:1.15rem;color:#1a1916;font-weight:700;\">SQLiteDesk &mdash; Imports That Keep Your Leading Zeros<\/h4>\n<p style=\"margin:0 0 1.05rem;color:#3d3c38;font-size:.92rem;line-height:1.65;\">Leading zeros usually vanish during a CSV import. <strong>SQLiteDesk<\/strong> imports CSV and JSON with type inference and creates the table for you, but values that would lose digits as numbers stay text &mdash; ZIP codes, phone and account numbers come through intact. Large CSV files are streamed, so import size is not limited by memory.<\/p>\n<p>  <a href=\"https:\/\/backendside.com\/sqlitedesk.php\" style=\"display:inline-flex;align-items:center;gap:.4rem;background:#2d5be3;color:#ffffff;font-weight:600;font-size:.85rem;padding:.6rem 1.2rem;border-radius:6px;text-decoration:none;\">Explore SQLiteDesk &rarr;<\/a>\n<\/div>\n<h2>Surprise 3: comparisons and sorting across types<\/h2>\n<p>When values of different storage classes are compared, SQLite orders them as <strong>NULL &lt; INTEGER and REAL &lt; TEXT &lt; BLOB<\/strong>. A number is always &ldquo;less than&rdquo; any text, and text that contains digits sorts as text:<\/p>\n<pre style=\"background:#1e1d1b;color:#eceae6;padding:1rem 1.15rem;border-radius:8px;overflow-x:auto;font-size:.85rem;line-height:1.5;\"><code>CREATE TABLE t (v);                 -- no declared type: BLOB affinity\nINSERT INTO t VALUES (9), ('10'), (100), ('9');\n\nSELECT v FROM t ORDER BY v;         -- 9, 100, '10', '9'\nSELECT count(*) FROM t WHERE v = 9; -- 1 (the text '9' does not match)<\/code><\/pre>\n<p>Columns with INTEGER, REAL or NUMERIC affinity convert a text literal before comparing, so <code>WHERE qty = '12'<\/code> works. A column with no declared type does not, which is how &ldquo;the row is right there but the query finds nothing&rdquo; bugs happen. If a column ended up with mixed types, <code>CAST(v AS INTEGER)<\/code> in the query is the quick fix and cleaning the data is the real one.<\/p>\n<h2>Surprise 4: NULL is not an empty string<\/h2>\n<p>SQLite treats <code>NULL<\/code> and <code>''<\/code> as completely different values. <code>WHERE name = ''<\/code> will not find NULLs, <code>WHERE name = NULL<\/code> finds nothing at all (use <code>IS NULL<\/code>), and <code>count(name)<\/code> skips NULLs but counts empty strings. Tools that show both as a blank cell make this very easy to get wrong when editing data by hand.<\/p>\n<div style=\"border:1px solid #c5d3f8;background:linear-gradient(135deg,#eef2fd 0%,#ffffff 72%);border-radius:14px;padding:1.5rem 1.65rem;margin:2rem 0;\">\n<div style=\"font-size:.7rem;font-weight:700;letter-spacing:.08em;text-transform:uppercase;color:#2d5be3;margin-bottom:.55rem;\">&#128295; BackendSide Tool<\/div>\n<h4 style=\"margin:0 0 .45rem;font-size:1.15rem;color:#1a1916;font-weight:700;\">SQLiteDesk &mdash; NULL Is Never Mistaken for an Empty String<\/h4>\n<p style=\"margin:0 0 1.05rem;color:#3d3c38;font-size:.92rem;line-height:1.65;\">In the <strong>SQLiteDesk<\/strong> grid, NULL shows as a faded italic <em>NULL<\/em>, never confused with an empty string, and <strong>Ctrl+0<\/strong> sets a cell to NULL explicitly. Sort the whole table by clicking a header or type a WHERE condition to filter it &mdash; both run in the database, not just on the visible page &mdash; and only the cells you actually changed are written back.<\/p>\n<p>  <a href=\"https:\/\/backendside.com\/sqlitedesk.php\" style=\"display:inline-flex;align-items:center;gap:.4rem;background:#2d5be3;color:#ffffff;font-weight:600;font-size:.85rem;padding:.6rem 1.2rem;border-radius:6px;text-decoration:none;\">Explore SQLiteDesk &rarr;<\/a>\n<\/div>\n<h2>Surprise 5: the types that do not exist<\/h2>\n<ul>\n<li><strong>Booleans<\/strong> are integers. <code>TRUE<\/code> and <code>FALSE<\/code> are accepted keywords, but they are simply 1 and 0.<\/li>\n<li><strong>Dates and times<\/strong> have no type. Pick one representation and stick to it: ISO-8601 text (<code>'2026-11-04 09:15:00'<\/code>, which sorts correctly), Unix time as an INTEGER, or a Julian day number as a REAL. The built-in <code>date()<\/code>, <code>datetime()<\/code>, <code>strftime()<\/code> and <code>julianday()<\/code> functions understand all three.<\/li>\n<li><strong>Decimals<\/strong> are floating point. <code>DECIMAL(10,2)<\/code> gives NUMERIC affinity, and fractional values are stored as REAL, so <code>0.1 + 0.2<\/code> is not exactly <code>0.3<\/code>. Store money as an INTEGER number of cents.<\/li>\n<li><strong>Very large integers<\/strong> beyond the 64-bit range (about 9.22 &times; 10<sup>18<\/sup>) become REAL and lose precision &mdash; store long identifiers as text.<\/li>\n<\/ul>\n<h2>Surprise 6: INTEGER PRIMARY KEY vs INT PRIMARY KEY<\/h2>\n<p>A column declared exactly <code>INTEGER PRIMARY KEY<\/code> becomes an alias for the table&rsquo;s internal <code>rowid<\/code>: lookups by it are as fast as possible, and it auto-assigns values. Declare it <code>INT PRIMARY KEY<\/code> or <code>BIGINT PRIMARY KEY<\/code> instead and you get an ordinary column with a separate unique index &mdash; still INTEGER affinity, but no rowid alias and no automatic numbering. One word makes the difference.<\/p>\n<h2>Getting real type safety: STRICT tables<\/h2>\n<p>Since SQLite 3.37.0 you can add the <code>STRICT<\/code> keyword to a table. Every column must then use one of <code>INT<\/code>, <code>INTEGER<\/code>, <code>REAL<\/code>, <code>TEXT<\/code>, <code>BLOB<\/code> or <code>ANY<\/code>, and values that cannot be converted losslessly are rejected:<\/p>\n<pre style=\"background:#1e1d1b;color:#eceae6;padding:1rem 1.15rem;border-radius:8px;overflow-x:auto;font-size:.85rem;line-height:1.5;\"><code>CREATE TABLE payments (\n    id          INTEGER PRIMARY KEY,\n    amount_cents INTEGER NOT NULL,\n    reference   TEXT NOT NULL,\n    meta        ANY\n) STRICT;\n\nINSERT INTO payments (amount_cents, reference) VALUES ('1250', 'A-001'); -- ok: '1250' becomes 1250\nINSERT INTO payments (amount_cents, reference) VALUES ('abc', 'A-002');\n-- Error: cannot store TEXT value in INTEGER column payments.amount_cents<\/code><\/pre>\n<p>An <code>ANY<\/code> column in a STRICT table keeps whatever you give it without conversion. One caveat: SQLite versions older than 3.37.0 cannot read the schema of a database that contains STRICT tables, so check what versions your users or tools run.<\/p>\n<p>If you cannot use STRICT, <code>CHECK<\/code> constraints do the same job column by column, and work on any version:<\/p>\n<pre style=\"background:#1e1d1b;color:#eceae6;padding:1rem 1.15rem;border-radius:8px;overflow-x:auto;font-size:.85rem;line-height:1.5;\"><code>CREATE TABLE products (\n    sku    TEXT    NOT NULL CHECK (typeof(sku) = 'text' AND length(sku) &lt;= 12),\n    price  INTEGER NOT NULL CHECK (typeof(price) = 'integer' AND price &gt;= 0)\n);<\/code><\/pre>\n<h2>Auditing an existing database<\/h2>\n<p>Inherited a database and suspect mixed types? Group each column by <code>typeof()<\/code>:<\/p>\n<pre style=\"background:#1e1d1b;color:#eceae6;padding:1rem 1.15rem;border-radius:8px;overflow-x:auto;font-size:.85rem;line-height:1.5;\"><code>SELECT typeof(qty) AS stored_as, count(*) AS n\nFROM orders\nGROUP BY typeof(qty);\n-- integer | 9812\n-- text    | 14     &lt;- these are your bugs<\/code><\/pre>\n<div style=\"border:1px solid #c5d3f8;background:linear-gradient(135deg,#eef2fd 0%,#ffffff 72%);border-radius:14px;padding:1.5rem 1.65rem;margin:2rem 0;\">\n<div style=\"font-size:.7rem;font-weight:700;letter-spacing:.08em;text-transform:uppercase;color:#2d5be3;margin-bottom:.55rem;\">&#128295; BackendSide Tool<\/div>\n<h4 style=\"margin:0 0 .45rem;font-size:1.15rem;color:#1a1916;font-weight:700;\">SQLiteDesk &mdash; Design Tables with a Live DDL Preview<\/h4>\n<p style=\"margin:0 0 1.05rem;color:#3d3c38;font-size:.92rem;line-height:1.65;\">The <strong>SQLiteDesk<\/strong> Schema Designer lets you create tables visually &mdash; column types, primary key and auto-increment, NOT NULL, unique, defaults and foreign keys &mdash; while a live DDL preview shows exactly the <code>CREATE TABLE<\/code> statement you are about to run. For STRICT tables and CHECK constraints, the SQL workspace has syntax highlighting and schema-aware autocomplete. Available on the Microsoft Store with a free trial.<\/p>\n<p>  <a href=\"https:\/\/backendside.com\/sqlitedesk.php\" style=\"display:inline-flex;align-items:center;gap:.4rem;background:#2d5be3;color:#ffffff;font-weight:600;font-size:.85rem;padding:.6rem 1.2rem;border-radius:6px;text-decoration:none;\">Explore SQLiteDesk &rarr;<\/a>\n<\/div>\n<h2>Key takeaways<\/h2>\n<ul>\n<li>SQLite types belong to <strong>values<\/strong>; a column&rsquo;s declared type only sets its <strong>affinity<\/strong>.<\/li>\n<li>Affinity comes from <strong>substring rules applied in order<\/strong>, which is why <code>STRING<\/code> is numeric and <code>FLOATING POINT<\/code> is integer.<\/li>\n<li>Store digit-based identifiers as <strong>TEXT<\/strong> to keep leading zeros; store money as <strong>integer cents<\/strong>.<\/li>\n<li>There are no native boolean, date or decimal types &mdash; choose a representation and be consistent.<\/li>\n<li>Only <code>INTEGER PRIMARY KEY<\/code>, spelled exactly that way, aliases the rowid.<\/li>\n<li>Use <strong>STRICT tables<\/strong> (3.37.0+) or <code>CHECK (typeof(...))<\/code> constraints when you want the database to enforce types, and audit old data with <code>typeof()<\/code>.<\/li>\n<\/ul>\n","protected":false},"excerpt":{"rendered":"<p>In SQLite a column type is a suggestion, not a rule. Learn the five storage classes, how type affinity is worked out from a declared type, the surprises it causes (lost leading zeros, text in integer columns, odd sorting, no dates or booleans), and how STRICT tables and CHECK constraints bring back real type safety.<\/p>\n","protected":false},"author":1,"featured_media":423,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[6,8],"tags":[32,33,37],"class_list":["post-358","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-sqlite","category-tutorials","tag-sqlite","tag-sqlitedesk","tag-type-affinity"],"_links":{"self":[{"href":"https:\/\/backendside.com\/blog\/wp-json\/wp\/v2\/posts\/358","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/backendside.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/backendside.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/backendside.com\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/backendside.com\/blog\/wp-json\/wp\/v2\/comments?post=358"}],"version-history":[{"count":1,"href":"https:\/\/backendside.com\/blog\/wp-json\/wp\/v2\/posts\/358\/revisions"}],"predecessor-version":[{"id":424,"href":"https:\/\/backendside.com\/blog\/wp-json\/wp\/v2\/posts\/358\/revisions\/424"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/backendside.com\/blog\/wp-json\/wp\/v2\/media\/423"}],"wp:attachment":[{"href":"https:\/\/backendside.com\/blog\/wp-json\/wp\/v2\/media?parent=358"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/backendside.com\/blog\/wp-json\/wp\/v2\/categories?post=358"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/backendside.com\/blog\/wp-json\/wp\/v2\/tags?post=358"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}