The Advantages Of Flexible Typing
Small. Fast. Reliable.<br>Choose any three.
Home<br>Menu<br>About<br>Documentation<br>Download<br>License<br>Support<br>Purchase
Search
About<br>Documentation<br>Download<br>Support<br>Purchase
Search Documentation<br>Search Changelog
The Advantages Of Flexible Typing
Table Of Contents<br>1. Introduction
2. About Flexible Typing
3. Cases Where Flexible Typing Is Useful
3.1. Attribute tables
3.2. The "value" column output from the json_tree virtual tables
3.3. Storage for dirty data
3.4. Dynamic programming languages
3.5. Data typename cross-compatibility
3.6. Repurposing unused or disused columns in legacy databases
4. Perceived Disadvantages of Flexible Typing (With Rebuttals)
4.1. We've never done it that way before
4.2. Rigid type enforcement helps prevent application bugs
4.3. Rigid type enforcement prevents data pollution
4.4. Other SQL database engines don't work this way
5. If You Insist On Rigid Type Enforcement...
6. Embrace Freedom
1. Introduction
SQLite provides developers with the freedom to store content in<br>any desired format, regardless of the declared datatype of the column.<br>Some people find this feature troublesome. Some developers are shocked<br>to discover that it is possible to insert text into a column marked INTEGER.
This article advocates in favor of the flexible type rules<br>in SQLite.
2. About Flexible Typing
Details regarding the flexible type system of SQLite are found<br>in the separate Datatypes In SQLite document. Here is a quick<br>summary:
Datatype names on column definitions are optional. A column definition<br>can consist of just the column name and nothing else.
When datatype names are provided, they can be just about any text.<br>SQLite attempts to deduce the preferred datatype for the column based<br>on the datatype name in the column definition, but that preferred<br>datatype is advisory, not mandatory. The preferred datatype is<br>known as the "column affinity".
An attempt is made to transform incoming data into the preferred<br>datatype of the column. (All SQL database engines do this, not<br>just SQLite.) If this transformation is successful, all is well.<br>But if unsuccessful, instead of raising an error, SQLite just stores<br>the content using its original datatype.
The above can lead to situations that advocates of rigid typing<br>find incommodious:
Column DatatypeTypes Allowed In That Column<br>INTEGERINTEGER, REAL, TEXT, BLOB<br>REALREAL, TEXT, BLOB<br>TEXTTEXT, BLOB<br>BLOBINTEGER, REAL, TEXT, BLOB
Note that an INTEGER or REAL value will never end up being stored<br>in a TEXT column, since an INTEGER or REAL value can and always will<br>be converted into its equivalent TEXT representation. Similarly,<br>an INTEGER will never be stored in a REAL column because it will<br>always be converted into a REAL. But TEXT does not always look like<br>an INTEGER or REAL value and so cannot always be converted. And a<br>BLOB cannot be converted into anything and nothing else can be<br>converted into a BLOB.
3. Cases Where Flexible Typing Is Useful
Some readers, upon first encountering flexible typing in SQLite, ask<br>themselves "how could this ever be useful?" Here is an<br>attempt to answer that question:
3.1. Attribute tables
Many applications, especially those that use SQLite as an<br>application file format, need a place to store miscellaneous attributes<br>such as thumbnail images (as BLOB values), short pieces of text (such<br>as the user's name), as well as numeric, date, and JSON values. It is<br>convenient to create a single table to handle this storage:
CREATE TABLE attribute(name TEXT PRIMARY KEY, value) WITHOUT ROWID;
Without flexible typing, such a table would need to be more complex,<br>with separate columns for each possible type of data. Flexible typing<br>of the "value" column makes the table conceptually simpler, more<br>space-efficient, and easier to access and update.
In the Fossil version control system, each<br>repository has a CONFIG table that is used to store all kinds of settings<br>with every possible datatype. The user-specific configuration file<br>for Fossil (the ~/.fossil file) is a separate SQLite database that contains<br>a single attribute table holding the user-specific state across all<br>repositories.
Some applications use an SQLite database as a pure key-value store.<br>The database schema contains a single table that looks something like this:
CREATE TABLE storage(name TEXT PRIMARY KEY, value ANYTHING);
3.2. The "value" column output from the json_tree virtual tables
The json_tree and json_each table-valued functions that are<br>built into SQLite both have a "value" column that can hold values of<br>type INTEGER, REAL, or TEXT depending on the type of the corresponding<br>JSON field. For example:
SELECT typeof(value) FROM json_each('{"a":1,"b":2.5,"c":"hello"}');
The query above returns three rows of one column with values<br>"integer", "real", and "text", respectively.
3.3. Storage for dirty data
Analysts sometimes encounter CSV files where some columns contain<br>a mixture of integer, real, and text data. CSV files that...