In SQLite, there are quite a few ways to look at a table’s structure. Therefore, there are quite a few ways we can check the data type of the columns within that table.

There’s also a function that allows us to check the data type of a column returned in a query.

Here are five ways to check the data type of a column in SQLite.

The PRAGMA table_info() Statement

The PRAGMA table_info() statement returns information about a specified table, including its columns and data types.

Here’s an example of returning information about a table called Album.

PRAGMA table_info(Album);

Result:

cid  name      type           notnull  dflt_value  pk
---  --------  -------------  -------  ----------  --
0    AlbumId   INTEGER        1                    1 
1    Title     NVARCHAR(160)  1                    0 
2    ArtistId  INTEGER        1                    0 

In this case, the columns were created with their data type explicitly defined.

Here’s another example:

PRAGMA table_info(Events);

Result:

cid  name       type     notnull  dflt_value  pk
---  ---------  -------  -------  ----------  --
0    EventId    INTEGER  0                    1 
1    EventName           0                    0 
2    StartDate           0                    0 
3    EndDate             0                    0 

In this case, only the first column has its data type explicitly defined.

SQLite uses a different approach to data types than the other major RDBMSs. SQLite uses a dynamic typing system, and so the datatype of a value is associated with the value itself, not with its container.

That said, SQLite does allow us to explicitly specify the data type of columns. In the above examples, some columns were created with their data type explicitly defined, and we can see what those data types are by running the above PRAGMA statement.

The PRAGMA table_xinfo() Statement

The PRAGMA table_xinfo() statement is exactly the same as the PRAGMA table_info() statement, except that it also returns hidden columns on virtual tables:

PRAGMA table_xinfo(Album);

Result:

cid  name      type           notnull  dflt_value  pk  hidden
---  --------  -------------  -------  ----------  --  ------
0    AlbumId   INTEGER        1                    1   0     
1    Title     NVARCHAR(160)  1                    0   0     
2    ArtistId  INTEGER        1                    0   0     

We can see that it’s basically the same as table_info(), except with the extra column.

The .schema Command

Another way of retrieving a table’s structure is by using the .schema command. This is one of several methods you can use to return the SQL used to create the table.

Example:

.schema Album

Result:

CREATE TABLE Chinook.[Album]
(
    [AlbumId] INTEGER  NOT NULL,
    [Title] NVARCHAR(160)  NOT NULL,
    [ArtistId] INTEGER  NOT NULL,
    CONSTRAINT [PK_Album] PRIMARY KEY  ([AlbumId]),
    FOREIGN KEY ([ArtistId]) REFERENCES [Artist] ([ArtistId]) 
		ON DELETE NO ACTION ON UPDATE NO ACTION
);
CREATE INDEX Chinook.[IFK_AlbumArtistId] ON [Album] ([ArtistId]);

This method allows us to generate the SQL required to recreate the table – including specifying any data types.

The sqlite_schema Table

You can use the sqlite_schema table to the same thing as the previous example.

Here’s an example using the same table.

SELECT sql 
FROM Chinook.sqlite_schema 
WHERE tbl_name = 'Album';

Result:

CREATE TABLE [Album]
(
    [AlbumId] INTEGER  NOT NULL,
    [Title] NVARCHAR(160)  NOT NULL,
    [ArtistId] INTEGER  NOT NULL,
    CONSTRAINT [PK_Album] PRIMARY KEY  ([AlbumId]),
    FOREIGN KEY ([ArtistId]) REFERENCES [Artist] ([ArtistId]) 
		ON DELETE NO ACTION ON UPDATE NO ACTION
)
CREATE INDEX [IFK_AlbumArtistId] ON [Album] ([ArtistId]) 

The sqlite_schema table can also be accessed using sqlite_master.

The typeof() Function

You can use the typeof() function to get the data type of a column returned by a query. More specifically, it returns the data type of the given expression.

Example:

SELECT typeof(Title) FROM Album
LIMIT 1;

Result:

text

Here I used the LIMIT clause to limit the result to just one row, otherwise we would see the same result repeated for each row in the table.

Bear in mind that this method returns the data type of the expression returned by the query – not the actual data type assigned to the column. The possible types returned are:

• null
• integer
• real
• text
• blob

Each column in an SQLite database is assigned one of the above type affinities.

These are actually referred to as storage classes. A storage class is more general than a datatype. All values in SQL statements, whether they are literals embedded in SQL statement text or parameters bound to precompiled SQL statements have an implicit storage class. The database engine may convert values between numeric storage classes (INTEGER and REAL) and TEXT during query execution.

More Information about Data Types in SQLite

See SQLite datatypes (SQLite documentation) and The Advantages Of Flexible Typing (SQLite documentation) for more information about data types in SQLite.

Also see STRICT tables (SQLite documentation), a feature that was added in SQLite version 3.37.0 on 2021-11-27 that allow us to refuse the flexible typing system, and instead enforce the traditional rigid type system found in all other SQL database engines and in the SQL standard.