Holochain Data

A wrapper around sqlx configured for Holochain's needs, providing SQLite database connections with encryption, migrations, and query patterns.

Features

• SQLCipher encryption - Full database encryption with secure key management
• WAL mode - Write-Ahead Logging enabled for better concurrency
• Embedded migrations - Migration files compiled into the binary
• Connection pooling - Automatic pool sizing based on CPU count
• Configurable sync levels - Control SQLite's durability guarantees

Query Patterns

sqlx provides several approaches for mapping Rust types to database queries. See src/example.rs for detailed examples.

1. query_as with FromRow derive (Recommended)

The most ergonomic approach for most use cases:

use sqlx::FromRow;

#[derive(FromRow)]
struct SampleData {
    id: i64,
    name: String,
    value: Option<String>,
}

let data = sqlx::query_as::<_, SampleData>(
    "SELECT id, name, value FROM sample_data WHERE id = ?"
)
.bind(id)
.fetch_one(&pool)
.await?;

Pros:

• Automatic mapping from columns to struct fields
• Type-safe with clear struct definitions
• Good balance of ergonomics and flexibility

Cons:

• Column names must match struct field names (or use #[sqlx(rename = "...")])
• Runtime column mapping (no compile-time verification)

2. Manual Row access

Direct access to row data by index:

let row = sqlx::query("SELECT id, name FROM sample_data WHERE id = ?")
    .bind(id)
    .fetch_one(&pool)
    .await?;

let id: i64 = row.get(0);
let name: String = row.get(1);

Pros:

• Maximum flexibility
• No struct definitions needed for simple queries
• Can handle dynamic column sets

Cons:

• Easy to make mistakes with column indices
• Less type-safe
• More verbose

3. Compile-time checked macros (query! / query_as!)

Recommended approach - Provides compile-time SQL verification using offline prepared queries.

let data = sqlx::query_as!(
    SampleData,
    "SELECT id, name, value FROM sample_data WHERE id = ?",
    id
)
.fetch_one(&pool)
.await?;

Pros:

• Compile-time verification of queries against actual schema
• Type inference from database
• Catches SQL errors at compile time
• Works offline with prepared query metadata (.sqlx/ directory)

Cons:

• Requires running cargo sqlx prepare when schema changes
• Additional .sqlx/ directory must be committed to version control

Development Setup

For compile-time query verification to work, you need to maintain prepared query metadata:

# Initial setup (or after schema changes)
cd crates/holochain_data

# Create/update the database schema
DATABASE_URL=sqlite:$(pwd)/dev.db sqlx database create
DATABASE_URL=sqlite:$(pwd)/dev.db sqlx migrate run

# Generate query metadata for offline compilation
DATABASE_URL=sqlite:$(pwd)/dev.db cargo sqlx prepare -- --lib

The .sqlx/ directory contains query metadata and should be committed to version control.

Note: The DATABASE_URL environment variable must point to the development database using inline syntax as shown above.

CI Integration

In CI, queries are verified without needing a database connection:

# Just check that queries are valid (uses committed .sqlx/ metadata)
cargo check -p holochain_data

When schema changes, developers must run cargo sqlx prepare locally and commit the updated .sqlx/ files.

Recommendation for Holochain

Use compile-time checked macros (#3) as the default pattern:

• Catches SQL errors at compile time
• Type inference from actual database schema
• Works offline in CI using prepared query metadata
• No runtime cost for query verification

Use query_as with FromRow derive (#1) for:

• Queries that need to be constructed dynamically
• Situations where compile-time checking isn't practical

Use manual Row access (#2) only for:

• Dynamic queries where column set isn't known
• Simple utility queries that don't warrant a struct
• Performance-critical code where you need fine control

Example Usage

use holochain_data::{open_db, HolochainDataConfig};

// Set up database with encryption and a pool with 4 readers.
let key = DbKey::generate(passphrase).await?;
let config = HolochainDataConfig::new()
    .with_key(key)
    .with_sync_level(DbSyncLevel::Normal)
    .with_max_readers(4);

let db = open_db(path, db_id, config).await?;

// Migrations run automatically
// Now use the connection pool for queries

See tests/integration.rs for comprehensive examples.

Validating SQL Queries

This crate uses sqlx's compile-time query checking to validate all SQL queries against the schema. The .sqlx/ directory contains prepared query metadata that allows offline verification.

To regenerate the query metadata after schema or query changes:

cd crates/holochain_data
DATABASE_URL=sqlite:$(pwd)/dev.db sqlx database create
DATABASE_URL=sqlite:$(pwd)/dev.db sqlx migrate run
DATABASE_URL=sqlite:$(pwd)/dev.db cargo sqlx prepare -- --lib

In CI, queries are validated using the committed .sqlx/ metadata without requiring a live database connection.