In our last video, we explored the 11 different column types that Active Record supports and how SQLite stores them using four different storage types. But how can you be certain about how SQLite is actually storing your data?

In this video, we’ll look at how to use SQLite’s TYPEOF function to gain clear introspection into how the database stores and handles different column types.

Exploring Data Storage with Active Record and SQLite

Let’s jump back into our IRB session playground, where we have our posts table with all 11 column types, along with our Post model. We’ll set everything up—creating the posts table, schema migrations, and metadata tables—and then insert a new post record.

When we check the logs, we see some interesting details about how Active Record serializes data:

• Binary Data → Stored as a raw binary blob. Active Record doesn’t display the full binary data in logs since large payloads (like a PNG file) would be inefficient to log. Instead, it simply notes the size in bytes.
• Boolean Values → Stored as an integer (0 for false, 1 for true).
• Strings and Text → Stored as TEXT, appearing exactly as expected.
• Integer, Float, and Decimal → Stored in their respective numeric formats, with floats and decimals mapped to SQLite’s REAL storage type.

Handling Date, Time, and Datetime in SQLite

For date and time values, Active Record serializes them as ISO 8601 strings:

• Date → Stores only the year, month, and day.
• Time → Stores the year, month, day, hours, minutes, and seconds, but normalizes the date value to January 1, 2000 to ensure compatibility with SQLite’s date functions.
• Datetime → Stores the full timestamp, including year, month, day, hours, minutes, seconds, and fractional seconds.

By preserving ISO 8601 formatting, Active Record ensures that SQLite’s date functions work predictably and efficiently while keeping datetime values readable and sortable.

How JSON is Stored in SQLite

The JSON column is simply stored as TEXT, formatted with keys wrapped in double quotes and structured as expected.

Although recent SQLite updates allow storing JSON documents as BLOBs, Active Record does not yet support this feature. However, for most web applications, the performance difference is negligible, and the Rails team is working on adding support in the future.

Using SQLite’s TYPEOF Function to Verify Storage Types

Now, let’s verify how SQLite actually stores these values in the database using the TYPEOF function.

By running:

SELECT TYPEOF(datetime_column) FROM posts;

We can confirm that the datetime column is stored as TEXT, just as expected.

If you want to have a hundred percent certainty of how a particular column is being stored, this is the tool you want to reach for:

def type_of(column_name)
  ActiveRecord::Base.connection.execute(
	"SELECT TYPEOF(#{column_name}) AS type FROM posts;"
	).first['type'].upcase
end

We can set up this function to run this query passing in a column name and we're going to pretty print the SQLite storage type.

typeof 'binary'

Using this, we can inspect all 11 column types:

• Binary → Stored as BLOB
• Boolean → Stored as INTEGER
• String & Text → Stored as TEXT
• Integer → Stored as INTEGER
• Float & Decimal → Stored as REAL
• Date, Time & Datetime → Stored as TEXT (ISO 8601 format)
• JSON → Stored as TEXT

Why TYPEOF is a Valuable Tool

This TYPEOF function is an essential tool for verifying how data is stored in the database, ensuring that Active Record’s serialization methods align with SQLite’s storage mechanisms.

In the next video, we’ll explore how Active Record bridges the gap between Ruby classes and SQLite storage types, creating a seamless and predictable development experience.