Janken SQL Hub - Database Query Management Library

A high-performance, modular Rust library for parameterizable SQL query management with support for SQLite.

🎯 Overview

Janken SQL Hub enables developers to define SQL queries with parameters in a database-agnostic way, automatically generating prepared statements for different database backends while preventing SQL injection attacks.

Core Capabilities

• ✅ Parameterizable SQL Templates - @param_name syntax in queries, types defined separately
• ✅ Dynamic Table Names - #table_name syntax for parameterizable table names
• ✅ Multi-Database Support - SQLite (?1,?2), PostgreSQL planned for future releases
• ✅ SQL Injection Protection - Automatic prepared statement generation
• ✅ Quote-Aware Parsing - Parameters inside quotes are treated as literals
• ✅ Type Safety & Validation - Parameter type validation with constraints (range, pattern, enum, table name validation)
• ✅ Parameter Constraints - Range limits, regex patterns, enumerated values, and table name validation

🚀 Quick Start

Janken SQL Hub enables developers to define SQL queries with parameters in a database-agnostic way, automatically generating prepared statements for different database backends while preventing SQL injection attacks.

✨ Key Features

Parameter Syntax

-- Basic parameter syntax - @params default to string type if no args specified
SELECT * FROM users WHERE id=@user_id AND name=@user_name

-- Dynamic table name parameters - always table_name type with optional constraints
SELECT * FROM #table_name WHERE id=@user_id

-- List parameters for IN clauses - always list type with item type validation
SELECT * FROM users WHERE id IN :[user_ids] AND status IN :[statuses]

-- Parameters in quoted strings (treated as literals)
SELECT * FROM users WHERE name='@literal_text'

Multi-Database Support 🚧 Under Construction

The library is designed for future multi-database support with automatic prepared statement generation:

// Single query definition automatically generates both formats
let args = serde_json::json!({"id": {"type": "integer"}});
let query = QueryDef::from_sql("SELECT * FROM users WHERE id=@id", Some(&args))?;
// SQLite:   SELECT * FROM users WHERE id=?1
// PostgreSQL: SELECT * FROM users WHERE id=$1 (planned)

Current Status: SQLite fully supported. PostgreSQL implementation is planned for future releases.

🚀 Usage Guide

1. Define Queries (JSON Configuration)

Each query definition contains:

• "query": Required - The SQL statement with @parameter (#table_name) placeholders
• "args": Optional - only needed to override default types or add constraints
• "returns": Optional - Array of column names for SELECT queries (determines JSON response structure)

{
  "get_user": {
    "query": "SELECT id, name, email FROM users WHERE id=@user_id",
    "returns": ["id", "name", "email"],
    "args": {
      "user_id": {"type": "integer"}
    }
  },
  "create_user": {
    "query": "INSERT INTO users (name, email) VALUES (@name, @email)",
    "args": {
      "name": {"type": "string"},
      "email": {"type": "string"}
    }
  },
  "search_users": {
    "query": "SELECT id, name FROM users WHERE age > @min_age AND age < @max_age",
    "returns": ["id", "name"],
    "args": {
      "min_age": {"type": "integer"},
      "max_age": {"type": "integer"}
    }
  },
  "get_user_by_status": {
    "query": "SELECT * FROM users WHERE status=@status",
    "returns": ["id", "name", "email", "status"],
    "args": {
      "status": {
        "type": "string",
        "enum": ["active", "inactive", "pending"]
      }
    }
  },
  "get_user_by_email": {
    "query": "SELECT * FROM users WHERE email LIKE @pattern",
    "returns": ["id", "name", "email"],
    "args": {
      "pattern": {
        "type": "string",
        "pattern": "\\S+@\\S+\\.\\S+"
      }
    }
  },
  "query_from_table": {
    "query": "SELECT * FROM #source WHERE id=@id AND name=@name",
    "returns": ["id", "name"],
    "args": {
      "id": {"type": "integer"},
      "name": {"type": "string"},
      "source": {"enum": ["source"]}
    }
  },
  "insert_into_dynamic_table": {
    "query": "INSERT INTO #dest_table (name) VALUES (@name)",
    "args": {
      "dest_table": {"enum": ["accounts", "users"]},
      "name": {"type": "string"}
    }
  },
  "get_users_by_ids": {
    "query": "SELECT id, name FROM users WHERE id IN :[user_ids]",
    "returns": ["id", "name"],
    "args": {
      "user_ids": {"itemtype": "integer"}
    }
  }
}

2. Load Queries

use janken_sql_hub::{DatabaseConnection, QueryDefinitions};

// Load from JSON file
let queries = QueryDefinitions::from_file("queries.json")?;

// Or load from JSON object
let json = serde_json::json!({...});
let queries = QueryDefinitions::from_json(json)?;

3. Execute Queries

// Setup SQLite connection
let sqlite_conn = DatabaseConnection::SQLite(Connection::open_in_memory()?);
let mut conn = DatabaseConnection::SQLite(conn);

// Get user by ID (returns QueryResult with JSON data and SQL execution details)
let params = serde_json::json!({"user_id": 42});
let query_result = conn.query_run(&queries, "get_user", &params)?;
// Access JSON results: query_result.data
// Access executed SQL statements: query_result.sql_statements (for debugging)

// Create new user
let params = serde_json::json!({"name": "Alice", "email": "alice@example.com"});
let query_result = conn.query_run(&queries, "create_user", &params)?;

// Query from dynamic table
let params = serde_json::json!({"source": "accounts", "id": 1, "name": "John"});
let query_result = conn.query_run(&queries, "query_from_table", &params)?;

// Insert into dynamic table
let params = serde_json::json!({"dest_table": "users", "name": "Bob"});
let query_result = conn.query_run(&queries, "insert_into_dynamic_table", &params)?;

4. Parameter Types and Constraints Supported

Automatic Type Assignment:

• @param parameters: Default to "string" type (can be overridden)
• #table_name parameters: Automatically assigned "table_name" type
• :[list_param] parameters: Automatically assigned "list" type

// Parameter types (all case-insensitive)
"integer", "string", "float", "boolean", "table_name", "list"

// Constraint types
"range": [min, max]     // For numeric types (integer/float)
"pattern": "regex"      // For string types (e.g., email validation)
"enum": [value1, ...]   // For any type (allowed values). Table names support enum only.
"itemtype": "type"      // For list types: specifies the type of each item in the list

// Examples in args object
"id": {"type": "integer"}                                                 // Basic integer (overridden from default string)
"balance": {"type": "float", "range": [0.0, 1000000.0]}                   // Float with range
"status": {"type": "string", "enum": ["active", "inactive", "pending"]}  // String enum
"email": {"type": "string", "pattern": "\\S+@\\S+\\.\\S+"}              // String with regex
"user_ids": {"itemtype": "integer"}                                     // List of integers for IN clauses
"names": {"type": "string"}                                              // Explicit string type (same as default)
"source": {"enum": ["users", "accounts"]}                               // Table name enum (table_name type auto-assigned)
"targets": {}                                                          // List parameter (no constraints needed)

⚡ Performance Characteristics

• Regex Compilation: One-time lazy static initialization
• Parameter Parsing: O(n) where n = SQL length
• Query Execution: Database-dependent (SQLite ~2-3x slower prepared vs raw, PostgreSQL similar)
• Memory Usage: Minimal (regex + parameter vectors)
• Zero-Copy: Parameter values passed by reference where possible

🧪 Quality Assurance

• Test Coverage: 100% coverage
• Zero Warnings: cargo clippy -- -D warnings clean
• Memory Safety: Rust ownership system guarantees
• Type Safety: Compile-time parameter validation
• SQL Injection: Automatic prepared statements prevent attacks

📈 Roadmap

Planned Enhancements

• PostgreSQL native connection implementation

Database Backend Priorities

1. ✅ SQLite (complete)
2. � PostgreSQL (planned implementation)

Built with ❤️ in Rust for type-safe, performant database query management.