What really happens when I use varchar(10) in the sqlite command-line shell?

Question

I'm messing around with SQLite for the first time by working through some of the SQLite documentation. In particular, I'm using Command Line Shell For SQLite and the SoupToNuts SQLite Tutorial on Sourceforge.

According to the SQLite datatype documentation, there are only 5 datatypes in SQLite. However, in the two tutorial documents above, I see where the authors use commands such as

create table tbl1(one varchar(10), two smallint);
create table t1 (t1key INTEGER PRIMARY KEY,data TEXT,num double,timeEnter DATE);

which contain datatypes that aren't listed by SQLite, yet these commands work just fine.

Additionally, when I ran .dump to see the SQL statements, these datatype specifications are preserved:

sqlite> CREATE TABLE Vulnerabilities (
   ...> VulnerabilityID unsigned smallint primary key,
   ...> VulnerabilityName varchar(10),
   ...> VulnerabilityDescription longtext);
sqlite> .dump
PRAGMA foreign_keys=OFF;
BEGIN TRANSACTION;
CREATE TABLE Vulnerabilities (
VulnerabilityID unsigned smallint primary key,
VulnerabilityName varchar(10),
VulnerabilityDescription longtext);
COMMIT;
sqlite>

So, what gives? Does SQLite keep a reference for any datatype specified in the SQL yet converts it behind the scenes to one of its 5 datatypes? Or is there something else I'm missing?

Answer 1

There are not five datatypes, rather 5 datatype "classes" that "real" datatypes fall into. So that, TINYINT, SMALLINT and BIGINT are three different datatypes, but all belonging to the INTEGER storage class.

Answer 2

SQLite uses dynamic typing.
SQLite will allow you to insert an integer into that VARCHAR(10) column.
SQLite will not complain if insert a string longer than 10 characters into that column.

As el.pescado mentions, SQLite has storage classes AKA "affinities".
If you attempt to insert a column belongs to a particular affinity, then SQLite will try to convert that value to match the affinity.
If the conversion doesn't work, the value is inserted as-is.

So while your more granular datatypes are saved (apparently) to the metadata table, they are not being used by SQLite.