| 1 |
<!DOCTYPE html> |
| 2 |
<html><head> |
| 3 |
<meta name="viewport" content="width=device-width, initial-scale=1.0"> |
| 4 |
<meta http-equiv="content-type" content="text/html; charset=UTF-8"> |
| 5 |
<link href="sqlite.css" rel="stylesheet"> |
| 6 |
<title>The Advantages Of Flexible Typing</title> |
| 7 |
<!-- path= --> |
| 8 |
</head> |
| 9 |
<body> |
| 10 |
<div class=nosearch> |
| 11 |
<a href="index.html"> |
| 12 |
<img class="logo" src="images/sqlite370_banner.svg" alt="SQLite" border="0"> |
| 13 |
</a> |
| 14 |
<div><!-- IE hack to prevent disappearing logo --></div> |
| 15 |
<div class="tagline desktoponly"> |
| 16 |
Small. Fast. Reliable.<br>Choose any three. |
| 17 |
</div> |
| 18 |
<div class="menu mainmenu"> |
| 19 |
<ul> |
| 20 |
<li><a href="index.html">Home</a> |
| 21 |
<li class='mobileonly'><a href="javascript:void(0)" onclick='toggle_div("submenu")'>Menu</a> |
| 22 |
<li class='wideonly'><a href='about.html'>About</a> |
| 23 |
<li class='desktoponly'><a href="docs.html">Documentation</a> |
| 24 |
<li class='desktoponly'><a href="download.html">Download</a> |
| 25 |
<li class='wideonly'><a href='copyright.html'>License</a> |
| 26 |
<li class='desktoponly'><a href="support.html">Support</a> |
| 27 |
<li class='desktoponly'><a href="prosupport.html">Purchase</a> |
| 28 |
<li class='search' id='search_menubutton'> |
| 29 |
<a href="javascript:void(0)" onclick='toggle_search()'>Search</a> |
| 30 |
</ul> |
| 31 |
</div> |
| 32 |
<div class="menu submenu" id="submenu"> |
| 33 |
<ul> |
| 34 |
<li><a href='about.html'>About</a> |
| 35 |
<li><a href='docs.html'>Documentation</a> |
| 36 |
<li><a href='download.html'>Download</a> |
| 37 |
<li><a href='support.html'>Support</a> |
| 38 |
<li><a href='prosupport.html'>Purchase</a> |
| 39 |
</ul> |
| 40 |
</div> |
| 41 |
<div class="searchmenu" id="searchmenu"> |
| 42 |
<form method="GET" action="search"> |
| 43 |
<select name="s" id="searchtype"> |
| 44 |
<option value="d">Search Documentation</option> |
| 45 |
<option value="c">Search Changelog</option> |
| 46 |
</select> |
| 47 |
<input type="text" name="q" id="searchbox" value=""> |
| 48 |
<input type="submit" value="Go"> |
| 49 |
</form> |
| 50 |
</div> |
| 51 |
</div> |
| 52 |
<script> |
| 53 |
function toggle_div(nm) { |
| 54 |
var w = document.getElementById(nm); |
| 55 |
if( w.style.display=="block" ){ |
| 56 |
w.style.display = "none"; |
| 57 |
}else{ |
| 58 |
w.style.display = "block"; |
| 59 |
} |
| 60 |
} |
| 61 |
function toggle_search() { |
| 62 |
var w = document.getElementById("searchmenu"); |
| 63 |
if( w.style.display=="block" ){ |
| 64 |
w.style.display = "none"; |
| 65 |
} else { |
| 66 |
w.style.display = "block"; |
| 67 |
setTimeout(function(){ |
| 68 |
document.getElementById("searchbox").focus() |
| 69 |
}, 30); |
| 70 |
} |
| 71 |
} |
| 72 |
function div_off(nm){document.getElementById(nm).style.display="none";} |
| 73 |
window.onbeforeunload = function(e){div_off("submenu");} |
| 74 |
/* Disable the Search feature if we are not operating from CGI, since */ |
| 75 |
/* Search is accomplished using CGI and will not work without it. */ |
| 76 |
if( !location.origin || !location.origin.match || !location.origin.match(/http/) ){ |
| 77 |
document.getElementById("search_menubutton").style.display = "none"; |
| 78 |
} |
| 79 |
/* Used by the Hide/Show button beside syntax diagrams, to toggle the */ |
| 80 |
function hideorshow(btn,obj){ |
| 81 |
var x = document.getElementById(obj); |
| 82 |
var b = document.getElementById(btn); |
| 83 |
if( x.style.display!='none' ){ |
| 84 |
x.style.display = 'none'; |
| 85 |
b.innerHTML='show'; |
| 86 |
}else{ |
| 87 |
x.style.display = ''; |
| 88 |
b.innerHTML='hide'; |
| 89 |
} |
| 90 |
return false; |
| 91 |
} |
| 92 |
var antiRobot = 0; |
| 93 |
function antiRobotGo(){ |
| 94 |
if( antiRobot!=3 ) return; |
| 95 |
antiRobot = 7; |
| 96 |
var j = document.getElementById("mtimelink"); |
| 97 |
if(j && j.hasAttribute("data-href")) j.href=j.getAttribute("data-href"); |
| 98 |
} |
| 99 |
function antiRobotDefense(){ |
| 100 |
document.body.onmousedown=function(){ |
| 101 |
antiRobot |= 2; |
| 102 |
antiRobotGo(); |
| 103 |
document.body.onmousedown=null; |
| 104 |
} |
| 105 |
document.body.onmousemove=function(){ |
| 106 |
antiRobot |= 2; |
| 107 |
antiRobotGo(); |
| 108 |
document.body.onmousemove=null; |
| 109 |
} |
| 110 |
setTimeout(function(){ |
| 111 |
antiRobot |= 1; |
| 112 |
antiRobotGo(); |
| 113 |
}, 100) |
| 114 |
antiRobotGo(); |
| 115 |
} |
| 116 |
antiRobotDefense(); |
| 117 |
</script> |
| 118 |
<div class=fancy> |
| 119 |
<div class=nosearch> |
| 120 |
<div class="fancy_title"> |
| 121 |
The Advantages Of Flexible Typing |
| 122 |
</div> |
| 123 |
<details class="fancy_toc"> |
| 124 |
<summary>Table Of Contents</summary> |
| 125 |
<div id="toc_sub"><div class="fancy-toc1"><a href="#introduction">1. Introduction</a></div> |
| 126 |
<div class="fancy-toc1"><a href="#about_flexible_typing">2. About Flexible Typing</a></div> |
| 127 |
<div class="fancy-toc1"><a href="#cases_where_flexible_typing_is_useful">3. Cases Where Flexible Typing Is Useful</a></div> |
| 128 |
<div class="fancy-toc2"><a href="#attribute_tables">3.1. Attribute tables</a></div> |
| 129 |
<div class="fancy-toc2"><a href="#the_value_column_output_from_the_json_tree_virtual_tables">3.2. The "value" column output from the json_tree virtual tables</a></div> |
| 130 |
<div class="fancy-toc2"><a href="#storage_for_dirty_data">3.3. Storage for dirty data</a></div> |
| 131 |
<div class="fancy-toc2"><a href="#dynamic_programming_languages">3.4. Dynamic programming languages</a></div> |
| 132 |
<div class="fancy-toc2"><a href="#data_typename_cross_compatibility">3.5. Data typename cross-compatibility</a></div> |
| 133 |
<div class="fancy-toc2"><a href="#repurposing_unused_or_disused_columns_in_legacy_databases">3.6. Repurposing unused or disused columns in legacy databases</a></div> |
| 134 |
<div class="fancy-toc1"><a href="#perceived_disadvantages_of_flexible_typing_with_rebuttals_">4. Perceived Disadvantages of Flexible Typing (With Rebuttals)</a></div> |
| 135 |
<div class="fancy-toc2"><a href="#we_ve_never_done_it_that_way_before">4.1. We've never done it that way before</a></div> |
| 136 |
<div class="fancy-toc2"><a href="#rigid_type_enforcement_helps_prevent_application_bugs">4.2. Rigid type enforcement helps prevent application bugs</a></div> |
| 137 |
<div class="fancy-toc2"><a href="#rigid_type_enforcement_prevents_data_pollution">4.3. Rigid type enforcement prevents data pollution</a></div> |
| 138 |
<div class="fancy-toc2"><a href="#other_sql_database_engines_don_t_work_this_way">4.4. Other SQL database engines don't work this way</a></div> |
| 139 |
<div class="fancy-toc1"><a href="#if_you_insist_on_rigid_type_enforcement_">5. If You Insist On Rigid Type Enforcement...</a></div> |
| 140 |
<div class="fancy-toc1"><a href="#embrace_freedom">6. Embrace Freedom</a></div> |
| 141 |
</div> |
| 142 |
</details> |
| 143 |
</div> |
| 144 |
|
| 145 |
|
| 146 |
|
| 147 |
|
| 148 |
<h1 id="introduction"><span>1. </span>Introduction</h1> |
| 149 |
|
| 150 |
<p>SQLite provides developers with the freedom to store content in |
| 151 |
any desired format, regardless of the declared datatype of the column. |
| 152 |
Some people find this feature troublesome. Some developers are shocked |
| 153 |
to discover that it is possible to insert text into a column marked INTEGER. |
| 154 |
|
| 155 |
</p><p>This article advocates in favor of the flexible type rules |
| 156 |
in SQLite. |
| 157 |
|
| 158 |
</p><h1 id="about_flexible_typing"><span>2. </span>About Flexible Typing</h1> |
| 159 |
|
| 160 |
<p>Details regarding the flexible type system of SQLite are found |
| 161 |
in the separate <a href="datatype3.html">Datatypes In SQLite</a> document. Here is a quick |
| 162 |
summary: |
| 163 |
|
| 164 |
</p><ul> |
| 165 |
<li><p> Datatype names on column definitions are optional. A column definition |
| 166 |
can consist of just the column name and nothing else. |
| 167 |
</p></li><li><p> When datatype names are provided, they can be just about any text. |
| 168 |
SQLite attempts to deduce the preferred datatype for the column based |
| 169 |
on the datatype name in the column definition, but that preferred |
| 170 |
datatype is advisory, not mandatory. The preferred datatype is |
| 171 |
known as the "column affinity". |
| 172 |
</p></li><li><p> An attempt is made to transform incoming data into the preferred |
| 173 |
datatype of the column. (All SQL database engines do this, not |
| 174 |
just SQLite.) If this transformation is successful, all is well. |
| 175 |
But if unsuccessful, instead of raising an error, SQLite just stores |
| 176 |
the content using its original datatype. |
| 177 |
</p></li><li><p> The above can lead to situations that advocates of rigid typing |
| 178 |
find incommodious: |
| 179 |
</p><center><table border="1" cellpadding="5"> |
| 180 |
<tr><th>Column Datatype</th><th>Types Allowed In That Column |
| 181 |
</th></tr><tr><td>INTEGER</td><td>INTEGER, REAL, TEXT, BLOB |
| 182 |
</td></tr><tr><td>REAL</td><td>REAL, TEXT, BLOB |
| 183 |
</td></tr><tr><td>TEXT</td><td>TEXT, BLOB |
| 184 |
</td></tr><tr><td>BLOB</td><td>INTEGER, REAL, TEXT, BLOB |
| 185 |
</td></tr></table></center> |
| 186 |
</li><li><p> Note that an INTEGER or REAL value will never end up being stored |
| 187 |
in a TEXT column, since an INTEGER or REAL value can and always will |
| 188 |
be converted into its equivalent TEXT representation. Similarly, |
| 189 |
an INTEGER will never be stored in a REAL column because it will |
| 190 |
always be converted into a REAL. But TEXT does not always look like |
| 191 |
an INTEGER or REAL value and so cannot always be converted. And a |
| 192 |
BLOB cannot be converted into anything and nothing else can be |
| 193 |
converted into a BLOB. |
| 194 |
</p></li></ul> |
| 195 |
|
| 196 |
|
| 197 |
<h1 id="cases_where_flexible_typing_is_useful"><span>3. </span>Cases Where Flexible Typing Is Useful</h1> |
| 198 |
|
| 199 |
<p>Some readers, upon first encountering flexible typing in SQLite, ask |
| 200 |
themselves "how could this ever be useful?" Here is an |
| 201 |
attempt to answer that question: |
| 202 |
|
| 203 |
</p><h2 id="attribute_tables"><span>3.1. </span>Attribute tables</h2> |
| 204 |
|
| 205 |
<p> Many applications, especially those that use SQLite as an |
| 206 |
<a href="appfileformat.html">application file format</a>, need a place to store miscellaneous attributes |
| 207 |
such as thumbnail images (as BLOB values), short pieces of text (such |
| 208 |
as the user's name), as well as numeric, date, and JSON values. It is |
| 209 |
convenient to create a single table to handle this storage: |
| 210 |
|
| 211 |
</p><div class="codeblock"><pre>CREATE TABLE attribute(name TEXT PRIMARY KEY, value) WITHOUT ROWID; |
| 212 |
</pre></div> |
| 213 |
|
| 214 |
<p>Without flexible typing, such a table would need to be more complex, |
| 215 |
with separate columns for each possible type of data. Flexible typing |
| 216 |
of the "value" column makes the table conceptually simpler, more |
| 217 |
space-efficient, and easier to access and update. |
| 218 |
|
| 219 |
</p><p>In the <a href="https://fossil-scm.org/">Fossil version control system</a>, each |
| 220 |
repository has a CONFIG table that is used to store all kinds of settings |
| 221 |
with every possible datatype. The user-specific configuration file |
| 222 |
for Fossil (the ~/.fossil file) is a separate SQLite database that contains |
| 223 |
a single attribute table holding the user-specific state across all |
| 224 |
repositories. |
| 225 |
|
| 226 |
</p><p> Some applications use an SQLite database as a pure key-value store. |
| 227 |
The database schema contains a single table that looks something like this: |
| 228 |
|
| 229 |
</p><div class="codeblock"><pre>CREATE TABLE storage(name TEXT PRIMARY KEY, value ANYTHING); |
| 230 |
</pre></div> |
| 231 |
|
| 232 |
<h2 id="the_value_column_output_from_the_json_tree_virtual_tables"><span>3.2. </span>The "value" column output from the json_tree virtual tables</h2> |
| 233 |
|
| 234 |
<p>The <a href="json1.html#jtree">json_tree</a> and <a href="json1.html#jeach">json_each</a> table-valued functions that are |
| 235 |
built into SQLite both have a "value" column that can hold values of |
| 236 |
type INTEGER, REAL, or TEXT depending on the type of the corresponding |
| 237 |
JSON field. For example: |
| 238 |
|
| 239 |
</p><div class="codeblock"><pre>SELECT typeof(value) FROM json_each('{"a":1,"b":2.5,"c":"hello"}'); |
| 240 |
</pre></div> |
| 241 |
|
| 242 |
<p>The query above returns three rows of one column with values |
| 243 |
"integer", "real", and "text", respectively. |
| 244 |
|
| 245 |
</p><h2 id="storage_for_dirty_data"><span>3.3. </span>Storage for dirty data</h2> |
| 246 |
|
| 247 |
<p> Analysts sometimes encounter CSV files where some columns contain |
| 248 |
a mixture of integer, real, and text data. CSV files that are obtained |
| 249 |
from Excel spreadsheet exports commonly have this trait, for example. |
| 250 |
When importing such "dirty data" into an SQL database, it is convenient |
| 251 |
to have flexibly typed columns to import into. |
| 252 |
|
| 253 |
</p><p> Dirty data is not restricted to CSV files coming out of Excel, of |
| 254 |
course. There are many data sources in which a single field might |
| 255 |
contain a mix of types. For example, a data column might contain the number |
| 256 |
of seconds since 1970 sometimes, or a text date string in other cases. |
| 257 |
It is desirable to clean up these inconsistent representations, |
| 258 |
but at the same time it is convenient to be able to store all the different |
| 259 |
representations in the same column of the intermediate database while the |
| 260 |
cleanup is underway. |
| 261 |
|
| 262 |
</p><h2 id="dynamic_programming_languages"><span>3.4. </span>Dynamic programming languages</h2> |
| 263 |
|
| 264 |
<p>SQLite began as a TCL extension that later escaped into the wild. |
| 265 |
TCL is a dynamic language in the sense that the programmer does not need |
| 266 |
to be aware of datatypes. Under the hood, TCL keeps careful track of the |
| 267 |
datatype of every value, but to the developer and user of a TCL program, |
| 268 |
everything looks like a string. Flexible typing is a natural fit for |
| 269 |
use with dynamic programming languages like TCL and others, since with |
| 270 |
a dynamic programming language, you can not always predict in advance what |
| 271 |
datatype a variable will hold. So when you need to store the value of that |
| 272 |
variable into the database, having a database that supports flexible |
| 273 |
typing makes storage much easier. |
| 274 |
|
| 275 |
</p><h2 id="data_typename_cross_compatibility"><span>3.5. </span>Data typename cross-compatibility</h2> |
| 276 |
|
| 277 |
<p>Every SQL database engine seems to have its own unique set of supported |
| 278 |
datatype names: |
| 279 |
|
| 280 |
</p><ul> |
| 281 |
<li> BIGINT |
| 282 |
</li><li> UNSIGNED SMALL INT |
| 283 |
</li><li> TEXT |
| 284 |
</li><li> VARCHAR |
| 285 |
</li><li> VARYING CHARACTER |
| 286 |
</li><li> NATIONAL VARYING CHARACTER |
| 287 |
</li><li> NVARCHAR |
| 288 |
</li><li> JSON |
| 289 |
</li><li> REAL |
| 290 |
</li><li> FLOAT |
| 291 |
</li><li> DOUBLE PRECISION |
| 292 |
</li><li> <i> ... and so forth ...</i> |
| 293 |
</li></ul> |
| 294 |
|
| 295 |
<p>The fact that SQLite will accept any of these names as a valid typename, |
| 296 |
and let you store any kind of content into the column, increases the chances |
| 297 |
that a script written to run on some other SQL database engine will also |
| 298 |
work in SQLite. |
| 299 |
|
| 300 |
</p><h2 id="repurposing_unused_or_disused_columns_in_legacy_databases"><span>3.6. </span>Repurposing unused or disused columns in legacy databases</h2> |
| 301 |
|
| 302 |
<p> Because an SQLite database file is a single file on disk, some |
| 303 |
applications use SQLite as an <a href="appfileformat.html">application file format</a>. This means |
| 304 |
that a single instance of the application might, over the course of its |
| 305 |
life, talk to hundreds or thousands of separate databases, each in a separate |
| 306 |
file. When such applications evolve over years, some columns in the |
| 307 |
underlying database will have their meanings altered subtly. Or, it might |
| 308 |
be desirable to repurpose an existing column to serve two or more purposes. |
| 309 |
This is much easier to do if the column has a flexible datatype. |
| 310 |
|
| 311 |
</p><h1 id="perceived_disadvantages_of_flexible_typing_with_rebuttals_"><span>4. </span>Perceived Disadvantages of Flexible Typing (With Rebuttals)</h1> |
| 312 |
|
| 313 |
<p> The following perceived disadvantages of flexible typing were |
| 314 |
gleaned and compiled from countless posts on Hacker News and |
| 315 |
Reddit and similar forums where developers discuss these sorts of |
| 316 |
things. If you can think of other reasons why flexible typing |
| 317 |
is a bad idea, please contact the SQLite developers or leave a |
| 318 |
post on the <a href="https://sqlite.org/forum/forum">SQLite Forum</a> so |
| 319 |
that your idea can be added to the list. |
| 320 |
|
| 321 |
|
| 322 |
</p><h2 id="we_ve_never_done_it_that_way_before"><span>4.1. </span>We've never done it that way before</h2> |
| 323 |
|
| 324 |
<p> Many skeptics of flexible typing simply express |
| 325 |
shock and disbelief, without offering any rationale for why they think |
| 326 |
flexible typing is a bad idea. Without supporting arguments, one must |
| 327 |
assume their reason for not liking flexible typing is that it is different |
| 328 |
from what they are used to. |
| 329 |
|
| 330 |
</p><p> Presumably, many developers who are aghast at SQLite's flexible |
| 331 |
typing feel this way because they have just never encountered anything |
| 332 |
like it before. All prior exposure to databases and especially SQL |
| 333 |
databases has involved rigid typing, and the readers mental model of |
| 334 |
SQL includes rigid typing as a fundamental feature. Flexible typing |
| 335 |
upsets their world-view. |
| 336 |
|
| 337 |
</p><p> Yes, flexible typing is a new way of thinking about data in an |
| 338 |
SQL database. But new is not necessary bad. Sometimes, and I think |
| 339 |
especially in the case of flexible typing, innovation leads to improvement. |
| 340 |
|
| 341 |
</p><h2 id="rigid_type_enforcement_helps_prevent_application_bugs"><span>4.2. </span>Rigid type enforcement helps prevent application bugs</h2> |
| 342 |
|
| 343 |
<p> It has become a point of doctrine among many programmers that the |
| 344 |
best way to prevent application bugs is strict type enforcement. But I |
| 345 |
find no evidence in support of this. |
| 346 |
|
| 347 |
</p><p> To be sure, strict type enforcement does help prevent some kinds of |
| 348 |
bugs in lower-level languages like C and C++ that present a model that is |
| 349 |
close to machine hardware. But this does not seem to |
| 350 |
be the case for higher-abstraction languages in which all data is |
| 351 |
passed around in a "Value" superclass of some kind which is subclassed |
| 352 |
for the various lower-level data types. When everything is a Value |
| 353 |
object, specific datatypes cease to be important. |
| 354 |
|
| 355 |
</p><p> This technical note is authored by the original author of SQLite. |
| 356 |
I have been writing TCL programs for 27 years. TCL has no type enforcement |
| 357 |
whatsoever. The "Value" class in TCL (called Tcl_Obj) can hold many |
| 358 |
different datatypes, but it presents the content to the program and to |
| 359 |
the application user as a string. And I've had a lot of bugs in |
| 360 |
those TCL programs over the years. But I do not recall a single instance |
| 361 |
where the bugs might have been caught by a rigid type system. I have |
| 362 |
also written a lot of C code over a span of 35 years, not the |
| 363 |
least of which is SQLite itself. I have found the type system in C |
| 364 |
to be very helpful at finding and preventing problems. For the |
| 365 |
<a href="https://fossil-scm.org/">Fossil Version Control System</a>, which is written |
| 366 |
in C, I have even implemented supplemental static analysis programs that |
| 367 |
scan the Fossil source code prior to compilation, looking for problems |
| 368 |
that compilers miss. This works well for compiled programs. |
| 369 |
|
| 370 |
</p><p> The SQL language model is a higher-level abstraction than C/C++. |
| 371 |
In SQLite, every data item is stored in memory as an "sqlite3_value" object. |
| 372 |
There are subclasses of this object for strings, integers, floating-point |
| 373 |
numbers, blobs, and other representations. Everything is passed around |
| 374 |
inside the SQL language implemented by SQLite as "sqlite3_value" objects |
| 375 |
so the underlying datatype does not really matter. I have never found |
| 376 |
rigid type enforcement to be helpful in languages like TCL and SQLite |
| 377 |
that have a single "Value" superclass used to represent any data element. |
| 378 |
Fossil makes extensive use of SQLite in its implementation. There have |
| 379 |
been many bugs in Fossil over its 14-years history, but I cannot recall |
| 380 |
a single bug that might have been prevented by rigid type enforcement in |
| 381 |
the SQLite. Some C-language bugs might have been caught by better type |
| 382 |
enforcement (which is why I wrote the supplemental source code scanners), |
| 383 |
but no SQL bugs. |
| 384 |
|
| 385 |
</p><p> Based on decades of experience, I reject the thesis that rigid |
| 386 |
type enforcement helps prevent application bugs. I will accept and |
| 387 |
believe a slightly modified thesis: Rigid type enforcement helps to |
| 388 |
prevent applications bugs <em>in languages that lack a single |
| 389 |
top-level "Value" superclass</em>. But SQLite does have the |
| 390 |
single "sqlite3_value" superclass, so that proverb does not apply. |
| 391 |
|
| 392 |
</p><h2 id="rigid_type_enforcement_prevents_data_pollution"><span>4.3. </span>Rigid type enforcement prevents data pollution</h2> |
| 393 |
|
| 394 |
<p> Some people contend that if you have rigorous constraints on the |
| 395 |
schema, and especially strict enforcement of column datatypes, this |
| 396 |
will help prevent incorrect data from being added to the database. |
| 397 |
This is not true. It is true that type enforcement might help prevent |
| 398 |
<em>egregiously</em> incorrect data from getting into the system. |
| 399 |
But type enforcement is no help in prevent subtly incorrect data |
| 400 |
from being recorded. |
| 401 |
|
| 402 |
</p><p> So, for example, rigid type enforcement can successfully prevent |
| 403 |
the customer name (text) from being inserted into the integer |
| 404 |
Customer.creditScore column. On the other hand, if that mistake occurs, |
| 405 |
it is very easy to spot the problem and find all affected rows. |
| 406 |
But type enforcement is no help in preventing a bug where the customer |
| 407 |
family name and given name are reversed, since both are text fields. |
| 408 |
|
| 409 |
</p><p> By suppressing easy-to-detect errors and passing through only the |
| 410 |
hard-to-detect errors, rigid type enforcement can actually make it more |
| 411 |
difficult to find and fix bugs. Data errors tend to cluster. If you have |
| 412 |
20 different data sources, most of the data errors will usually come |
| 413 |
from just 2 or 3 of those sources. The presence of egregious |
| 414 |
errors (such as text in an integer column) is a convenient early warning |
| 415 |
signal that something is amiss. The source of the problem can be |
| 416 |
tracked quickly and extra scrutiny applied to the source of the |
| 417 |
egregious errors, thus hopefully also fixing the subtle errors too. |
| 418 |
When egregious errors are suppressed, you lose an important signal |
| 419 |
that helps you to detect and fix the subtle errors. |
| 420 |
|
| 421 |
</p><p> Data errors are inevitable. They will happen regardless of how |
| 422 |
much type checking is done. Rigid type enforcement can catch only |
| 423 |
a small subset of those cases - the most obvious cases. It does |
| 424 |
nothing to help find and fix the more subtle cases. And, by suppressing |
| 425 |
the signal of which data sources are problematic, it can sometimes |
| 426 |
make the subtle errors more difficult to locate. |
| 427 |
|
| 428 |
</p><h2 id="other_sql_database_engines_don_t_work_this_way"><span>4.4. </span>Other SQL database engines don't work this way</h2> |
| 429 |
|
| 430 |
<p> Because SQLite is less restrictive and allows you to do more things, |
| 431 |
SQL scripts that work on other database engines will also usually work |
| 432 |
on SQLite, but scripts written initially for SQLite might not work |
| 433 |
on more restrictive database engines. This can cause problems when |
| 434 |
developers use SQLite for prototyping and testing and then migrate their |
| 435 |
application to a more restrictive SQL engine for deployment. If the |
| 436 |
application was (unintentionally) taking advantage of the flexible |
| 437 |
typing available in SQLite, then it will fail when migrated. |
| 438 |
|
| 439 |
</p><p> People use this problem to argue that SQLite should be more |
| 440 |
restrictive about datatypes. But you could just as easily turn |
| 441 |
that argument around and say that other database engines should be |
| 442 |
more flexible with regard to datatypes. The application was working |
| 443 |
correctly under SQLite, prior to being migrated, after all. If rigid |
| 444 |
type enforcement is really all that useful, why did it break an |
| 445 |
application that was previously working? |
| 446 |
|
| 447 |
</p><h1 id="if_you_insist_on_rigid_type_enforcement_"><span>5. </span>If You Insist On Rigid Type Enforcement...</h1> |
| 448 |
|
| 449 |
<p>As of SQLite version 3.37.0 (2021-11-27), SQLite supports this |
| 450 |
development style using <a href="stricttables.html">STRICT tables</a>. |
| 451 |
|
| 452 |
</p><p>If you find a real-world case where STRICT tables prevented or |
| 453 |
would have prevented a bug in an application, please post a message to the |
| 454 |
<a href="https://sqlite.org/forum/forum">SQLite Forum</a> so that we can add your story |
| 455 |
to this document. |
| 456 |
|
| 457 |
</p><h1 id="embrace_freedom"><span>6. </span>Embrace Freedom</h1> |
| 458 |
|
| 459 |
<p>If flexible typing in an SQL database is a new concept to you, |
| 460 |
I encourage you to give it a try. It probably will not cause you |
| 461 |
any problems and it might make your program simpler and easier to |
| 462 |
write and maintain. I think that even if you are skeptical at first, |
| 463 |
if you will just give flexible typing a try, you will eventually |
| 464 |
come to realize that it is a better approach and will start |
| 465 |
encouraging other database vendors to support at least an ANY |
| 466 |
datatype if not complete SQLite-style type flexibility. |
| 467 |
|
| 468 |
</p><p>Most of the time, flexible typing does not matter because a column |
| 469 |
stores a single well-defined type. But occasionally you will run |
| 470 |
across situations where having a flexible type system makes the |
| 471 |
solution to your problem cleaner and easier. |
| 472 |
</p><p align="center"><small><i>This page was last updated on 2025-06-12 09:10:23Z </small></i></p> |
| 473 |
|