In this video, let's explore how we can define the structure of our tables using the different column types that Active Record provides. As we jump back into the playground, you'll see that I’ve expanded the definition of the posts table to include the 11 different column types that Active Record supports. Let’s walk through each one briefly.

Column Types in Active Record

• Binary – Stores raw binary data, such as the contents of a PNG file.
• Boolean – Stores true or false values.
• String – Stores a small amount of text.
• Text – Stores larger text content.

A good rule of thumb: If a column only needs to hold a tweet's worth of content (under 256 characters), use string. If it needs more than 256 characters, use text.

Numeric Column Types

• Integer – Stores whole numbers (e.g., 1, 2, 3, 42).
• Float & Decimal – Both store real numbers, but with key differences:
  • Float stores numbers imprecisely, which can lead to unexpected behavior in arithmetic operations.
  • Decimal ensures precise and deterministic calculations, making it the preferred choice for financial and mathematical operations.

Date and Time Column Types

• Date – Stores calendar-based dates (e.g., August 12, 2024).
• Time – Stores only a time of day (e.g., 11:15 PM).
• Datetime – Stores both a date and time (e.g., August 12, 2024, at 11:15 PM).

JSON Column Type

• JSON – Stores structured JSON data.

How Active Record Maps to SQLite Storage Types

Since SQLite does not have a rich type system, it doesn’t store these 11 Active Record types as distinct data types. Instead, SQLite simplifies them into just four storage types:

• Binary → Stored as a BLOB.
• Boolean → Stored as an integer (0 or 1), with Active Record enforcing constraints.
• String & Text → Both stored as TEXT.
• Integer → Stored as INTEGER.
• Float & Decimal → Stored as REAL.
• Datetime & Date → Stored as TEXT, using ISO 8601-formatted strings.
• JSON → Stored as TEXT.

For those following SQLite advancements, recent updates allow storing JSON documents as blobs instead of text. However, Active Record does not yet support this feature. While this may change in future Rails updates, for most web applications, the performance difference is negligible.

In the next video, we’ll explore how to inspect the database and verify the actual storage types used by SQLite, helping us understand how data is stored on disk.