tx2-query

SQL Analytics Layer for TX-2 ECS - enabling advanced analytics, complex queries, and external tool integration while keeping the ECS world as the source of truth.

Overview

tx2-query provides a SQL side-car database for TX-2 applications. It's designed as an analytics layer, NOT an ORM:

• ECS World → SQL Database: One-way synchronization from ECS to SQL
• Component → Table Mapping: Automatic schema generation from component types
• Incremental Sync: Track and sync only changed entities/components
• Multiple Backends: PostgreSQL, SQLite, and extensible to more

Philosophy

• ECS world is always the source of truth
• SQL database is a read-only projection for analytics
• No bi-directional sync - changes flow ECS → SQL only
• Optimized for analytical queries, not transactional workloads

Features

• Schema Generation: Automatic DDL creation from component definitions
• Query Builder: Ergonomic SQL query construction
• Sync API: Incremental synchronization with batching
• Backend Abstraction: Support for multiple SQL databases
• Type-Safe: Leverages Rust's type system for safety
• DuckDB Support: OLAP workloads with columnar storage and window functions

Installation

Add to your Cargo.toml:

[dependencies]
tx2-query = { version = "0.1", features = ["postgres", "sqlite", "duckdb"] }
tokio = { version = "1.0", features = ["full"] }

Quick Start

use tx2_query::prelude::*;
use serde_json::json;

#[derive(Debug)]
struct Player;

#[tokio::main]
async fn main() -> Result<()> {
    // 1. Create schema generator
    let mut schema_gen = SchemaGenerator::new();
    schema_gen.register::<Player>(
        "Player",
        vec![
            ("name", SqlType::Text, false),
            ("email", SqlType::Text, false),
            ("score", SqlType::Integer, false),
        ],
    )?;

    // 2. Connect to database
    let backend = SqliteBackend::memory().await?;
    let sync = QuerySync::new(backend, schema_gen);

    // 3. Initialize schema
    sync.initialize_schema().await?;

    // 4. Track changes from your ECS world
    let mut player_data = HashMap::new();
    player_data.insert("name".to_string(), json!("Alice"));
    player_data.insert("email".to_string(), json!("alice@example.com"));
    player_data.insert("score".to_string(), json!(1500));

    sync.track_component_change(
        1,  // entity_id
        TypeId::of::<Player>(),
        "Player".to_string(),
        player_data,
    ).await?;

    // 5. Flush to database
    sync.flush().await?;

    // 6. Query with builder
    let query = SelectBuilder::new("Player")
        .select(vec!["name", "score"])
        .where_gt("score", json!(1000))
        .order_desc("score")
        .limit(10)
        .build()?;

    let results = sync.query(&query).await?;
    for row in results {
        println!("{}: {}",
            row.get_string("name").unwrap(),
            row.get_i64("score").unwrap()
        );
    }

    Ok(())
}

Database Backends

SQLite

use tx2_query::sqlite::SqliteBackend;

// In-memory database
let backend = SqliteBackend::memory().await?;

// File-based database
let backend = SqliteBackend::file("mydb.sqlite").await?;

PostgreSQL

use tx2_query::postgres::PostgresBackend;

let backend = PostgresBackend::connect(
    "postgresql://user:password@localhost/database"
).await?;

DuckDB

use tx2_query::duckdb::DuckDBBackend;

// In-memory database (perfect for analytics)
let backend = DuckDBBackend::memory().await?;

// File-based database
let backend = DuckDBBackend::file("analytics.duckdb").await?;

// Export to Parquet for external analysis
backend.export_parquet("Player", "players.parquet").await?;

// Import from Parquet
backend.import_parquet("Player", "players.parquet").await?;

Query Builder

The query builder provides an ergonomic, type-safe way to construct SQL queries:

// SELECT with WHERE
let query = SelectBuilder::new("Player")
    .select(vec!["name", "score"])
    .where_eq("active", json!(true))
    .where_gt("score", json!(100))
    .build()?;

// Aggregations
let query = SelectBuilder::new("Player")
    .aggregate(AggregateFunc::Count, "*", Some("total"))
    .aggregate(AggregateFunc::Avg, "score", Some("avg_score"))
    .build()?;

// Ordering and pagination
let query = SelectBuilder::new("Player")
    .select_all()
    .order_desc("created_at")
    .limit(20)
    .offset(40)
    .build()?;

// Complex conditions
let query = SelectBuilder::new("Player")
    .select_all()
    .where_in("level", vec![json!(10), json!(20), json!(30)])
    .where_like("name", "%Alice%")
    .build()?;

Schema Generation

Components automatically map to SQL tables:

#[derive(Debug)]
struct Player;

schema_gen.register::<Player>(
    "Player",  // Table name
    vec![
        ("name", SqlType::Text, false),      // NOT NULL
        ("score", SqlType::Integer, false),
        ("notes", SqlType::Text, true),       // NULL able
    ],
)?;

Generated DDL:

CREATE TABLE IF NOT EXISTS Player (
    entity_id BIGINT NOT NULL,
    name TEXT NOT NULL,
    score INTEGER NOT NULL,
    notes TEXT,
    _tx2_updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (entity_id)
);

Synchronization

Incremental Sync

Track changes as they happen in your ECS world:

// Entity created
sync.track_entity_created(entity_id).await?;

// Component added/modified
sync.track_component_change(
    entity_id,
    component_type_id,
    component_name,
    data,
).await?;

// Entity deleted
sync.track_entity_deleted(entity_id).await?;

// Flush changes to database
sync.flush().await?;

Full Sync

Perform a full synchronization of all entities:

let entities = vec![
    (entity_id, type_id, "Player".to_string(), player_data),
    // ... more entities
];

sync.full_sync(entities).await?;

Configuration

let config = SyncConfig {
    batch_size: 1000,              // Batch size for bulk operations
    background_sync: false,         // Sync in background
    use_transactions: false,        // Use transactions (see note below)
    max_retries: 3,                // Max retries on failure
};

let sync = QuerySync::with_config(backend, schema_gen, config);

Note on Transactions: Transaction support is limited when using connection pooling. For production use with proper transaction isolation, consider implementing connection affinity or using a dedicated transaction connection.

SQL Types

Supported SQL types:

• SqlType::BigInt - 64-bit integer
• SqlType::Integer - 32-bit integer
• SqlType::SmallInt - 16-bit integer
• SqlType::Real - Single precision float
• SqlType::DoublePrecision - Double precision float
• SqlType::Text - Variable length text
• SqlType::Boolean - Boolean
• SqlType::Timestamp - Timestamp
• SqlType::Json - JSON/JSONB data
• SqlType::Bytea - Binary data

Why DuckDB for Analytics?

DuckDB is optimized for OLAP workloads and provides several advantages:

• Columnar Storage: Efficient storage and query performance for analytics
• Window Functions: Advanced analytics with ROW_NUMBER(), RANK(), LAG(), etc.
• Parquet Integration: Export/import data in industry-standard format
• No Server Required: Embedded database, no separate process
• Fast Aggregations: Optimized for GROUP BY, SUM(), AVG(), etc.
• Join Performance: Excellent performance on complex joins

Example analytical query:

use tx2_query::duckdb::DuckDBBackend;
use tx2_query::prelude::*;

let mut backend = DuckDBBackend::memory().await?;
let sync = QuerySync::new(backend, schema_gen);

// Complex analytical query with window functions
let results = sync.query("
    SELECT
        player_name,
        score,
        AVG(score) OVER (PARTITION BY level) as avg_level_score,
        RANK() OVER (ORDER BY score DESC) as global_rank
    FROM Player
    WHERE active = true
").await?;

Use Cases

Analytics Dashboard

// Get player statistics
let query = SelectBuilder::new("Player")
    .aggregate(AggregateFunc::Count, "*", Some("total_players"))
    .aggregate(AggregateFunc::Avg, "score", Some("avg_score"))
    .aggregate(AggregateFunc::Max, "score", Some("high_score"))
    .build()?;

Leaderboards

// Top 10 players
let query = SelectBuilder::new("Player")
    .select(vec!["name", "score", "level"])
    .where_eq("active", json!(true))
    .order_desc("score")
    .limit(10)
    .build()?;

External Tools

Connect BI tools, data visualization platforms, or SQL clients directly to the SQL database for analysis without affecting your ECS world.

Performance Tips

1. Batch Operations: Use appropriate batch_size to balance memory and performance
2. Indexes: Add indexes for frequently queried columns
3. Selective Sync: Only sync components needed for analytics
4. Background Sync: Enable background sync for non-blocking updates
5. Clear Unused Data: Periodically use clear_all() if full re-sync is needed

Testing

# Run all tests
cargo test

# Run with specific backend
cargo test --features sqlite
cargo test --features postgres

# Run integration tests (requires database)
cargo test --test integration_test

License

MIT

Contributing

Contributions are welcome! Please open an issue or PR on GitHub.

Roadmap

• DuckDB backend for OLAP workloads
• ClickHouse backend for time-series analytics
• Historical queries via tx2-pack integration
• Materialized views support
• Query result caching
• Automatic index recommendations

Related Crates

• tx2-core: Core ECS framework
• tx2-link: Serialization and messaging
• tx2-pack: Delta compression and time travel