Debian Codegraph @

www/flextypegood.html

raw
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