sqlx-d1 Database Abstraction Layer

The sqlx-d1 crate provides a unified database interface for both Cloudflare D1 and Durable Object SQLite storage, enabling compile-time SQL verification while targeting WASM.

Key Files

• patch/sqlx-d1/ - The sqlx-d1 crate implementation
• crates/bf_user/src/storage_sqlx.rs - DO storage using sqlx-d1
• crates/bf_resident_db/src/operations/ - D1 database operations

Architecture Overview

┌─────────────────────────────────────────────────────────────────┐
│                    Compile Time (Native)                        │
├─────────────────────────────────────────────────────────────────┤
│  sqlx-sqlite loads → verifies SQL → generates type-safe code   │
└─────────────────────────────────────────────────────────────────┘
                              │
                              ▼
┌─────────────────────────────────────────────────────────────────┐
│                    Runtime (WASM)                               │
├─────────────────────────────────────────────────────────────────┤
│  ┌─────────────────┐              ┌─────────────────┐          │
│  │  D1Connection   │              │  DOConnection   │          │
│  │  (D1 Database)  │              │  (DO SqlStorage)│          │
│  └────────┬────────┘              └────────┬────────┘          │
│           │                                │                    │
│           └────────────┬───────────────────┘                    │
│                        ▼                                        │
│           ┌─────────────────────────┐                          │
│           │   Unified D1 Database   │                          │
│           │   Type Implementation   │                          │
│           └─────────────────────────┘                          │
└─────────────────────────────────────────────────────────────────┘

D1Connection vs DOConnection

D1Connection - Cloudflare D1 Database

Used for shared state across all workers (properties, devices, credentials).

// Creating a D1 connection from worker environment
let d1 = env.d1("DB")?;
let conn = sqlx_d1::D1Connection::new(d1);

// Executing queries
let property = sqlx_d1::query_as!(
    Property,
    "SELECT * FROM properties WHERE id = ?",
    property_id
)
.fetch_optional(&conn)
.await?;

Characteristics:

• Created from worker::D1Database binding
• Shared across all workers in the application
• Single instance per worker invocation
• Supports both &mut D1Connection and &D1Connection executors

DOConnection - Durable Object SQLite

Used for per-property isolated storage (guests, PIN codes, access logs).

// Creating a DO connection in a Durable Object
#[durable_object]
impl DurableObject for UserDurableObject {
    fn new(state: State, env: Env) -> Self {
        let conn = DOConnection::new(&state).expect("Failed to create DO connection");
        Self { state, env, conn }
    }
}

// Executing queries (same API as D1Connection)
let guests = sqlx_d1::query_as!(
    Guest,
    "SELECT * FROM guests WHERE status = ?",
    "Active"
)
.fetch_all(&conn)
.await?;

Characteristics:

• Created from worker::State in a Durable Object
• Each DO instance has isolated SQLite database
• Data persists across DO invocations
• Automatic migration support

Compile-Time SQL Verification

The macro system verifies SQL at compile time using two strategies:

Strategy 1: Live Database (Development)

When running locally with miniflare:

.wrangler/state/v3/d1/miniflare-D1DatabaseObject/*.sqlite

The macros connect to this SQLite file to verify queries.

Strategy 2: Offline Mode (CI/Production)

Uses cached query metadata in .sqlx/ directory:

# Generate cache for CI builds
cargo sqlx prepare

Cache files: .sqlx/query-{SHA256(sql)}.json

Query Macro Family

query! - Basic Queries

sqlx_d1::query!(
    "INSERT INTO guests (id, name, email) VALUES (?, ?, ?)",
    guest_id,
    name,
    email
)
.execute(&conn)
.await?;

query_as! - Type-Safe Row Mapping

#[derive(Debug)]
struct Guest {
    id: String,
    name: String,
    email: String,
}

let guests = sqlx_d1::query_as!(
    Guest,
    "SELECT id, name, email FROM guests WHERE property_id = ?",
    property_id
)
.fetch_all(&conn)
.await?;

query_scalar! - Single Column Values

let count: i64 = sqlx_d1::query_scalar!(
    "SELECT COUNT(*) FROM guests WHERE status = 'Active'"
)
.fetch_one(&conn)
.await?;

query_file! - External SQL Files

let result = sqlx_d1::query_file!(
    "queries/complex_report.sql",
    start_date,
    end_date
)
.fetch_all(&conn)
.await?;

Unchecked Variants

Skip compile-time verification (use sparingly):

sqlx_d1::query_unchecked!("SELECT * FROM dynamic_table")

Migration System

Both D1 and Durable Objects use the same migration system.

Migration Files

migrations/
├── 001_create_guests.sql
├── 002_add_pin_codes.sql
├── 003_add_access_logs.sql
└── 004_add_device_states.sql

File naming: VERSION_description.sql where VERSION is an integer.

Running Migrations

use sqlx_d1::{migrations, run_migrations};

// In a Durable Object
async fn ensure_initialized(&self) -> worker::Result<()> {
    let previously_applied = run_migrations(
        &mut self.conn,
        migrations!("./migrations")
    ).await?;
    
    console_log!("Applied {} migrations", previously_applied);
    Ok(())
}

Migration Tracking

Migrations are tracked in _sqlx_migrations table:

Column	Type	Description
version	INTEGER	Migration version number
description	TEXT	Migration description
installed_on	INTEGER	Unix timestamp
success	INTEGER	1 if successful
checksum	TEXT	SHA-256 of migration
execution_time	INTEGER	Time in nanoseconds

Type System

D1Value - Runtime Value Representation

WASM (Production):

pub struct D1Value(worker::send::SendWrapper<worker::wasm_bindgen::JsValue>);

Native (Testing):

pub enum D1Value {
    Null,
    Bool(bool),
    Int(i64),
    Real(f64),
    Text(String),
    Blob(Vec<u8>),
}

Supported Types

Rust Type	SQLite Type
bool	INTEGER (0/1)
i32, i64	INTEGER
f32, f64	REAL
String, &str	TEXT
Vec<u8>, &[u8]	BLOB
Option<T>	NULL or T

JSON Serialization Pattern

For complex types, serialize to JSON TEXT:

let permissions_json = serde_json::to_string(&guest.permissions)?;

sqlx_d1::query!(
    "INSERT INTO guests (id, permissions) VALUES (?, ?)",
    guest.id,
    permissions_json
)
.execute(&conn)
.await?;

Transaction Limitations

Important: Cloudflare D1 does not support explicit transactions.

// These are NO-OPs on D1
impl TransactionManager for D1TransactionManager {
    fn begin(conn: &mut D1Connection) -> ResultFuture<'_, ()> {
        Box::pin(async { Ok(()) })  // No-op
    }
    
    fn commit(conn: &mut D1Connection) -> ResultFuture<'_, ()> {
        Box::pin(async { Ok(()) })  // No-op
    }
    
    fn rollback(conn: &mut D1Connection) -> ResultFuture<'_, ()> {
        Box::pin(async { Ok(()) })  // No-op
    }
}

Workaround: Design operations to be idempotent or use single-statement upserts.

Error Handling Patterns

Standard Error Conversion

sqlx_d1::query!(...)
    .execute(&conn)
    .await
    .map_err(|e| UserError::DatabaseError(format!("Failed to create guest: {}", e)))?;

With Context

use anyhow::Context;

let guest = sqlx_d1::query_as!(Guest, "SELECT * FROM guests WHERE id = ?", id)
    .fetch_optional(&conn)
    .await
    .context("Failed to fetch guest")?
    .ok_or_else(|| anyhow::anyhow!("Guest not found"))?;

Code Examples

D1 Operations (bf_resident_db)

// crates/bf_resident_db/src/operations/credentials.rs

#[worker::send]
pub async fn create_credential(
    conn: &mut sqlx_d1::D1Connection,
    env: &Env,
    req: CreateCredentialRequest,
) -> DbResult<Credential> {
    let id = Uuid::new_v4().to_string();
    let now = now_timestamp();
    let encrypted_password = encrypt_password(&req.password, env)?;

    sqlx_d1::query!(
        r#"
        INSERT INTO credentials (
            id, type, name, username, password_encrypted,
            created_at, last_used, created_by
        )
        VALUES (?, ?, ?, ?, ?, ?, NULL, ?)
        "#,
        id,
        req.credential_type.as_str(),
        req.name,
        req.username,
        encrypted_password,
        now,
        req.created_by
    )
    .execute(&mut *conn)
    .await
    .map_err(|e| DbError::Internal(format!("Failed to create credential: {}", e)))?;

    get_credential_by_id(conn, &id).await?.ok_or_else(|| {
        DbError::Internal("Failed to retrieve created credential".to_string())
    })
}

DO Operations (bf_user)

// crates/bf_user/src/storage_sqlx.rs

#[worker::send]
pub async fn create_guest(conn: &DOConnection, guest: Guest) -> UserResult<()> {
    let permissions_json = serde_json::to_string(&guest.permissions)?;
    let status_json = serde_json::to_string(&guest.status)?;

    sqlx_d1::query!(
        r#"
        INSERT INTO guests (
            id, name, email, phone_number,
            access_start, access_end, pin_code, is_permanent,
            invited_by, invited_at, status, permissions, created_at
        ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
        "#,
        guest.id,
        guest.name,
        guest.email,
        guest.phone_number,
        guest.access_start,
        guest.access_end,
        guest.pin_code,
        guest.is_permanent,
        guest.invited_by,
        guest.invited_at,
        status_json,
        permissions_json,
        guest.created_at
    )
    .execute(conn)
    .await
    .map_err(|e| UserError::DatabaseError(format!("Failed to create guest: {}", e)))?;

    Ok(())
}

Best Practices

1. Always use parameterized queries - The macros enforce this at compile time
2. JSON for complex types - Serialize structs to TEXT columns
3. Idempotent operations - Design for retry safety without transactions
4. Run migrations on DO init - Call run_migrations in first request handling
5. Use appropriate connection type - D1 for shared state, DO for per-entity data
6. Cache prepare in CI - Run cargo sqlx prepare before deployment

See Also

• workers-architecture.md - How workers use these connections
• user-durable-object.md - DO-specific patterns