d1-studio

Technical Reference

Data types

D1 Studio follows Cloudflare D1's SQLite data types and best practices.

Core data types

INTEGER

Whole numbers
Best for IDs, counts, ages
Commonly used for primary keys with AUTOINCREMENT

TEXT

String data of any length
UTF-8 encoded
Best for names, descriptions, emails

REAL

Floating point numbers
Best for prices, measurements, scientific calculations

NUMERIC

Exact decimal numbers
Best for financial calculations where precision is critical

BOOLEAN

Stored as INTEGER (0 or 1)
Used for true/false flags

DATETIME

Timestamps and dates
Stored in ISO 8601 format
Can use CURRENT_TIMESTAMP as default value

Special types

JSON/ARRAY Storage

Stored as TEXT
Includes automatic JSON validation
Example array column:

tags TEXT CHECK (json_valid(tags) AND json_type(tags) = 'array')

BLOB

Binary data
Not recommended for large files
Best for small binary data like settings

Type best practices

Below are common patterns and examples for ensuring robust data handling in your D1 database.

Primary keys

Example: User ID starting at 1, automatically incrementing

1, 2, 3, 4...

Dates and times

Your timezone: 2024-01-18 15:30:00 (UTC+8)
Stored in UTC: 2024-01-18 07:30:00
Always store in UTC, convert for display

Money/currency

Use NUMERIC(10,2) for precision. Examples:

1234567.89    Valid
0.50          Valid
99999999.99   Valid
100000000.00  Invalid (exceeds precision)

JSON data

Object example:

{"user": "john", "preferences": {"theme": "dark"}}

Array example:

["tag1", "tag2", "tag3"]

Boolean values

SQLite typically stores booleans as 0 or 1:

true  → 1
false → 0
null  → NULL

Text with size constraints

Username (max 50 chars):

"john_doe" ✓
"this_username_is_way_too_long_and_exceeds_fifty_characters" ✗

Enumerated values

Status values:

"pending"
"active"
"suspended"

Invalid values rejected:

"inactive" ✗
"disabled" ✗

REAL numbers

Scientific values:

3.14159
-0.00123
1.23e-4

For more detailed SQLite type information, refer to the official Cloudflare D1 documentation.

Column constraints

SQLite (and by extension Cloudflare D1) supports the following commonly used constraints:

PRIMARY KEY
UNIQUE
NOT NULL
DEFAULT
CHECK
AUTOINCREMENT (only for INTEGER PRIMARY KEY)
Foreign key constraints

Index best practices

For improved query performance, especially on large datasets, consider creating indexes on columns frequently used in WHERE clauses or JOIN conditions.

CREATE INDEX idx_users_email ON users (email);

Avoid over-indexing, as it can slow down INSERTs and UPDATEs
Use multi-column indexes where it covers common query patterns
Drop or avoid unused indexes to reduce overhead

Documentation