Basic data types

As you already know, all SQL database engines besides SQLite use static type. A data type is an attribute that specifies the type of data of any object. In SQLite, every column, variable, and expression has a corresponding data type.

SQLite uses a dynamic type system, where the data type of a value is connected to the value itself rather than its container. It's important to know that SQL queries used in statically typed databases also work in SQLite. Please note that because SQLite uses dynamic typing, it can perform functions that conventional rigidly typed databases cannot.

How are values stored in SQLite?

When you want to store or update a value in the SQLite database, one of the following storage classes will be used:

Data Types vs. Storage Classes

At this point, you may ask yourself how data types differ from storage classes. Simply put, data types are more specialized. For example, the INTEGER storage class contains seven integer data types of different lengths. The choice that you make affects disk performance.

Type affinity

You may be wondering how to choose a storage class for a column. Generally speaking, a column can store any type of data. However, each column has a preferred storage class. It's called affinity. Any column in a table in an SQLite database will have one of these types of affinities:

How do we determine affinity?

In order to avoid confusion, a set of rules were developed to determine the affinity of any column. Let's take a look at them:

1. If a column type contains the INT string, it has an integer affinity.

2. If a column type contains any of the TEXT, CHAR, VARCHAR, or CLOB strings, it has a TEXT affinity.

3. If a column type contains the BLOB string, or if the kind is not specified, it has a BLOB affinity.

4. If a column type contains strings like REAL, FLOAT, or DOUB, then it has a REAL affinity.

5. If none of the above applies, then the affinity is NUMERIC.

It is important to follow the right order when we determine affinity. For example, if we have a column of the CHARINT type, both rules 1 and 2 apply. However, the first rule takes priority, and the column affinity becomes INTEGER.

Affinity name examples

Now that you know what affinity is and how to determine it, let's look at some of the common examples of how the 5 rules apply:

Remember that the numeric arguments, such as CHARACTER(20), no longer impose any size restrictions on numeric values, strings, or BLOBs.

Before we continue, let's study another example. What is the affinity if we declare a FLOATING POINT type? At first glance, you might think that it is REAL. However, you should look at the INT at the end of POINT. Because of that, FLOATING POINT has an INTEGER affinity.

Here's another example. What is the affinity of the declared type of STRING? Well, to answer this, you should go back to the 5 rules above. Since the first 4 rules do not apply, the affinity becomes NUMERIC, not TEXT.

Boolean Data type

The Boolean data type is commonly used in databases, so how is it stored in SQLite? In SQLite, there is no Boolean storage class. Therefore, we store Boolean values as 1 for true and 0 for false.

Date and Time Data types

As you may have noticed from the 5-point rule above, SQLite doesn't have a specialized storage class for date or time, so how do we deal with them? The built-in date and time functions allow applications to flexibly convert between formats while storing dates and times as INTEGER, REAL, or TEXT values. Take a look at the table below:

Date and time data types are transformed to NUMERIC data types in SQLite databases:

• TIME

• TIMESTAMP

• DATE

• DATETIME

Consider the following example:

create table table_name (date1 text, time1 text);

Now, we add the current date and time to the example table. To do this, we will use the datetime function.

insert into table_name (date1, time1) values(datetime('now'), datetime('now', 'localtime'));

select * from table_name;

First, we created a table called table_name with a date and time property, and then we used an insert into statement to enter the date and time.

Numeric Data types

The SQLite database has different NUMERIC data types, all of which are converted into an INTEGER, NUMERIC, and REAL data types. Let’s take a look at some examples:

String Data types

SQLite converts all string data types into the TEXT data type. Remember that all size restrictions are ignored.

Conclusion

SQLite supports a wide variety of data types. However, it is also quite adaptable in that way. Any value type can be used with any data type. Unlike other database management systems, SQLite brings some novel ideas in data types, such as type affinity and storage classes.