AGENTSQL

SQL Backend Family for AI Agent Persistence

What is AgentSQL • Features • Architecture • How To Use • Documentation • License

🛎 Important Notices

• Supports SQLite, PostgreSQL, and MySQL
• Implements the AgentDB trait from agentdb
• Includes database migration system for schema management
• Powered by SQLx for type-safe SQL

📌 Table of Contents

🤔 What is AgentSQL

agentsql is a production-ready SQL backend implementation for AI agent persistence. It provides a unified interface for SQLite, PostgreSQL, and MySQL databases through the AgentDB trait, enabling agents to store filesystems, key-value data, and tool call audit logs with a single API.

Built with SQLx for type-safe SQL, AgentSQL handles schema migrations automatically and provides seamless switching between database backends without code changes.

Use Cases

• Development: Use SQLite for local development with zero configuration
• Production: Deploy on PostgreSQL or MySQL for multi-agent systems
• Cloud: Seamlessly migrate between managed database services (AWS RDS, Google Cloud SQL, Azure)
• Testing: Fast in-memory SQLite databases for unit tests
• Edge: Embedded SQLite for resource-constrained environments
• Enterprise: PostgreSQL/MySQL for high-availability deployments

📷 Features

agentsql provides complete SQL backend support for agent persistence with production-grade features:

💾 Multi-Database Support

SQLite

• Zero Configuration: File-based or in-memory databases
• Single File: Entire database in one portable file
• Fast: Ideal for development and embedded systems
• ACID Compliant: Full transaction support
• In-Memory Mode: Perfect for testing

PostgreSQL

• Production Grade: Battle-tested for high-load scenarios
• Advanced Features: JSONB, full-text search, concurrent access
• Scalability: Handles millions of records efficiently
• Cloud Ready: Works with AWS RDS, Google Cloud SQL, Azure Database
• Replication: Built-in streaming replication

MySQL

• Wide Adoption: Industry-standard database
• Compatibility: Works with MySQL, MariaDB, and cloud variants
• Replication: Built-in master-slave replication
• Cloud Services: Compatible with AWS Aurora, Google Cloud SQL
• Performance: Optimized for read-heavy workloads

🔧 Schema Management

• Automatic Migrations: Schema applied on first connection
• Multi-Statement Support: Complex migration scripts
• Inode/Dentry Design: Unix-like filesystem structure
• Indexes: Optimized for filesystem operations
• Tool Call Auditing: Built-in audit trail table
• Version Control: Schema versioning support

🚀 Production Ready

• Connection Pooling: Efficient connection management via SQLx
• Async Operations: Full async/await support with Tokio
• Error Handling: Detailed error messages with context
• Type Safety: Compile-time SQL verification (SQLx)
• NULL Handling: Proper handling of optional fields
• Performance: Optimized queries with prepared statements

📐 Architecture

1. 🏛 Overall Architecture

┌─────────────────────────────────────────────────┐
│            AgentFS High-Level APIs              │
│   (FileSystem, KvStore, ToolRecorder)           │
└────────────────────┬────────────────────────────┘
                     │
┌────────────────────▼────────────────────────────┐
│             AgentDB Trait Interface             │
└────────────────────┬────────────────────────────┘
                     │
┌────────────────────▼────────────────────────────┐
│              AgentSQL (SQLx)                    │
│  • Connection pooling                           │
│  • Query builder                                │
│  • Migration system                             │
│  • Type conversions                             │
└───┬─────────────────┬────────────────┬──────────┘
    │                 │                │
┌───▼──────┐   ┌──────▼──────┐  ┌─────▼──────┐
│  SQLite  │   │ PostgreSQL  │  │   MySQL    │
│  Local   │   │  Production │  │   Cloud    │
└──────────┘   └─────────────┘  └────────────┘

2. 💾 Database Schema

┌────────────────────────────────────────────────┐
│           SQLite / PostgreSQL / MySQL          │
│                                                │
│  ┌──────────────────────────────────────────┐ │
│  │          fs_inode (File Metadata)        │ │
│  │  - ino (PK, AUTO_INCREMENT)              │ │
│  │  - mode (permissions)                    │ │
│  │  - uid, gid                              │ │
│  │  - size, atime, mtime, ctime             │ │
│  └──────────────────────────────────────────┘ │
│                                                │
│  ┌──────────────────────────────────────────┐ │
│  │      fs_dentry (Directory Entries)       │ │
│  │  - id (PK)                               │ │
│  │  - name                                  │ │
│  │  - parent_ino (FK → fs_inode)            │ │
│  │  - ino (FK → fs_inode)                   │ │
│  │  UNIQUE(parent_ino, name)                │ │
│  └──────────────────────────────────────────┘ │
│                                                │
│  ┌──────────────────────────────────────────┐ │
│  │         fs_data (File Content)           │ │
│  │  - id (PK)                               │ │
│  │  - ino (FK → fs_inode)                   │ │
│  │  - offset                                │ │
│  │  - size                                  │ │
│  │  - data (BLOB/BYTEA)                     │ │
│  │  INDEX(ino, offset)                      │ │
│  └──────────────────────────────────────────┘ │
│                                                │
│  ┌──────────────────────────────────────────┐ │
│  │           kv_store (Key-Value)           │ │
│  │  - key (PK)                              │ │
│  │  - value (TEXT)                          │ │
│  │  - created_at, updated_at                │ │
│  └──────────────────────────────────────────┘ │
│                                                │
│  ┌──────────────────────────────────────────┐ │
│  │      tool_calls (Audit Trail)            │ │
│  │  - id (PK)                               │ │
│  │  - name                                  │ │
│  │  - parameters (JSON)                     │ │
│  │  - result (JSON)                         │ │
│  │  - error                                 │ │
│  │  - status (pending/success/error)        │ │
│  │  - started_at, completed_at              │ │
│  │  - duration_ms                           │ │
│  │  INDEX(name), INDEX(started_at)          │ │
│  └──────────────────────────────────────────┘ │
└────────────────────────────────────────────────┘

3. 🔄 Migration Flow

┌────────────────────────────────────────┐
│     SqlBackend::new(config)            │
└─────────────────┬──────────────────────┘
                  │
         ┌────────▼──────────┐
         │  Detect Backend   │
         │  SQLite/PG/MySQL  │
         └────────┬──────────┘
                  │
         ┌────────▼──────────┐
         │  Load Migration   │
         │  SQL for backend  │
         └────────┬──────────┘
                  │
         ┌────────▼──────────┐
         │  Execute Schema   │
         │  Multi-statement  │
         └────────┬──────────┘
                  │
         ┌────────▼──────────┐
         │  Initialize Root  │
         │  inode (ino=1)    │
         └────────┬──────────┘
                  │
         ┌────────▼──────────┐
         │  Ready for Use!   │
         └───────────────────┘

🚙 How to Use

Installation

Add to your Cargo.toml:

[dependencies]
agentdb = "0.1"
agentsql = "0.1"

# Enable the backend(s) you need:
agentsql = { version = "0.1", features = ["sqlite"] }
# agentsql = { version = "0.1", features = ["postgres"] }
# agentsql = { version = "0.1", features = ["mysql"] }
# agentsql = { version = "0.1", features = ["sqlite", "postgres", "mysql"] }

Or install with cargo:

cargo add agentsql --features sqlite

Example: SQLite (Local Development)

use agentsql::SqlBackend;
use agentdb::AgentDB;

#[tokio::main]
async fn main() -> Result<(), Box<dyn std::error::Error>> {
    // Create SQLite database (file-based)
    let db = SqlBackend::sqlite("agent.db").await?;

    // Or use in-memory for testing
    let db = SqlBackend::sqlite(":memory:").await?;

    // Key-value operations
    db.put("config:theme", b"dark".to_vec().into()).await?;

    let theme = db.get("config:theme").await?.unwrap();
    println!("Theme: {}", String::from_utf8_lossy(theme.as_bytes()));

    // SQL queries
    let result = db.query(
        "SELECT * FROM fs_inode WHERE ino = 1",
        vec![]
    ).await?;

    println!("Root inode: {:?}", result.rows.first());

    Ok(())
}

Example: PostgreSQL (Production)

use agentsql::SqlBackend;
use agentdb::AgentDB;
use std::env;

#[tokio::main]
async fn main() -> Result<(), Box<dyn std::error::Error>> {
    // Connect to PostgreSQL
    let database_url = env::var("DATABASE_URL")
        .unwrap_or_else(|_| "postgres://user:pass@localhost/agentfs".to_string());

    let db = SqlBackend::postgres(database_url).await?;

    // Same API as SQLite!
    db.put("agent:status", b"running".to_vec().into()).await?;

    // Execute queries
    let result = db.query(
        "SELECT COUNT(*) as count FROM tool_calls WHERE status = 'success'",
        vec![]
    ).await?;

    Ok(())
}

Example: MySQL (Cloud Deployment)

use agentsql::SqlBackend;

#[tokio::main]
async fn main() -> Result<(), Box<dyn std::error::Error>> {
    // Connect to MySQL (e.g., AWS Aurora)
    let db = SqlBackend::mysql(
        "mysql://user:pass@aurora-cluster.region.rds.amazonaws.com/agentfs"
    ).await?;

    // Scan with prefix
    let sessions = db.scan("session:").await?;
    println!("Found {} active sessions", sessions.keys.len());

    Ok(())
}

🧪 Examples

See the agentfs crate for complete examples demonstrating:

• Basic SQLite usage
• PostgreSQL multi-agent systems
• MySQL cloud deployments

🧪 Testing

Run the test suite:

# Run all tests (SQLite)
cargo test

# Test with PostgreSQL
cargo test --features postgres

# Test with MySQL
cargo test --features mysql

# Run with output
cargo test -- --nocapture

📚 Documentation

Comprehensive documentation is available at docs.rs/agentsql, including:

• API reference for SqlBackend
• Migration system details
• Database-specific configuration
• Performance tuning guides
• Connection pooling best practices

🖊 Author

CryptoPatrick

Keybase Verification: https://keybase.io/cryptopatrick/sigs/8epNh5h2FtIX1UNNmf8YQ-k33M8J-Md4LnAN

🐣 Support

Leave a ⭐ if you think this project is cool.

🗄 License

This project is licensed under MIT. See LICENSE for details.