SQLite Describe Table: Understanding Table Schema

SQLite is a popular open-source database that is easy to use and ideal for small, embedded database applications. When working with SQLite, it’s important to understand how to get metadata about your database tables, such as column names and data types.

This allows you to inspect the structure of your tables and ensure your data is stored as intended. In SQLite, the DESCRIBE and PRAGMA table_info() statements are used to describe tables.

Also read: SQLite Show Tables

A Simple Example

Let’s look at a basic example. First, we’ll create a table called users with two columns:

CREATE TABLE users (
  id INTEGER PRIMARY KEY, 
  name TEXT
);

To describe this table, we can run:

DESCRIBE users;

Which outputs:

id         INTEGER         0             1
name       TEXT            0             0

This shows us the column names, data types, whether they can be NULL, and the default values.

Using PRAGMA table_info()

The PRAGMA table_info() statement provides more detailed metadata about a table. Running:

PRAGMA table_info(users);

Outputs:

0|id|INTEGER|1||1
1|name|TEXT|0||0

The columns provided are:

• Column index
• Column name
• Data type
• Whether the column can be NULL
• Default value
• Whether the column is auto-incremented

This provides additional useful details compared to the simple DESCRIBE output.

Another example of using SQLite DESCRIBE

Let’s look at one more example with some sample analytics data-related columns. First we’ll create a pageviews table:

CREATE TABLE pageviews (
  id INTEGER PRIMARY KEY,
  page_url TEXT,
  visitor_id INTEGER,
  visited_at DATETIME
)

This contains some common web analytics columns. To inspect it, we can run:

PRAGMA table_info(pageviews);

Which outputs details on each column:

0|id|INTEGER|1||1
1|page_url|TEXT|0||0  
2|visitor_id|INTEGER|0||0
3|visited_

Summary

This helps confirm that the table matches our intended structure before adding any data.

The DESCRIBE and PRAGMA table_info() statements are invaluable for inspecting and validating table schemas in SQLite. By mastering their use, you can be confident you are storing data as expected.