Skip to content

uRadical/sqliteproxy

BranchesTags

Repository files navigation

SQLiteProxy Logo

SQLite Multi-Tenant Proxy

A lightweight, efficient proxy for routing SQLite database queries to organization-specific databases in Go applications. This library enables seamless multi-tenancy while maintaining complete compatibility with the standard database/sql interface.

📋 Overview

SQLiteProxy automatically routes queries to organization-specific SQLite databases based on context information. It uses only standard library interfaces, making it a true drop-in replacement for *sql.DB.

graph TB
    A[Repository Layer] -->|Standard sql.DB Methods| B[SQLiteProxy or sql.DB]
    B -->|Configuration Choice| C[Multi-Tenant Mode]
    B -->|Configuration Choice| D[Single-Tenant Mode]
    C -->|Routes Based on Context| E[Org A Database]
    C -->|Routes Based on Context| F[Org B Database]
    C -->|Routes Based on Context| G[Org C Database]
    D -->|Direct Connection| H[Single Database]
Loading

✨ Features

  • Zero Custom Interfaces: Uses only standard Go library types
  • 100% Drop-in Compatible: Implements the same methods as *sql.DB
  • Configuration-Based: Switch between multi-tenant and single-tenant modes with a simple configuration change
  • Dynamic DSN Resolution: Flexible mapping of organization IDs to database paths
  • Connection Pooling: Efficient connection management per organization
  • Context Driven: Uses organization info from context for routing
  • Schema Migrations: Built-in migration system with auto, manual, and check modes

📦 Installation

go get uradical.io/go/sqliteproxy

🚀 Quick Start

Basic Usage

import (
    "context"
    "database/sql"
    
    "uradical.io/go/sqliteproxy"
)

// Choose implementation based on configuration
var db interface {
    QueryRowContext(context.Context, string, ...interface{}) *sql.Row
    ExecContext(context.Context, string, ...interface{}) (sql.Result, error)
    Close() error
}

if config.MultiTenantMode {
    // Multi-tenant mode with SQLite proxy
    proxy := sqliteproxy.NewSQLiteProxy("./data/orgs")
    db = proxy
    
    // Add organization to context
    ctx = context.WithValue(context.Background(), 
          sqliteproxy.DefaultOrgIDKey, "organization-id")
} else {
    // Single-tenant mode with standard sql.DB
    sqlDB, _ := sql.Open("sqlite3", "./data/app.db")
    db = sqlDB
    
    // Standard context
    ctx = context.Background()
}

// Repository works the same either way
repo := NewUserRepository(db)
user, err := repo.FindByID(ctx, 123)

Repository Implementation

Your repositories can use standard database/sql methods without any awareness of multi-tenancy:

type UserRepository struct {
    db interface {
        QueryRowContext(ctx context.Context, query string, args ...interface{}) *sql.Row
        ExecContext(ctx context.Context, query string, args ...interface{}) (sql.Result, error)
    }
}

func NewUserRepository(db interface {
    QueryRowContext(ctx context.Context, query string, args ...interface{}) *sql.Row
    ExecContext(ctx context.Context, query string, args ...interface{}) (sql.Result, error)
}) *UserRepository {
    return &UserRepository{db: db}
}

func (r *UserRepository) FindByID(ctx context.Context, id int64) (*User, error) {
    // Works with both SQLiteProxy and standard sql.DB
    row := r.db.QueryRowContext(
        ctx, 
        "SELECT id, username, email FROM users WHERE id = ?", 
        id,
    )
    
    // Rest of the implementation...
}

🏗️ Architecture

The SQLiteProxy is designed to be a direct replacement for *sql.DB while adding multi-tenancy capabilities:

classDiagram
    class sqlDB {
        +QueryContext(ctx, query, args) *sql.Rows
        +ExecContext(ctx, query, args) sql.Result
        +BeginTx(ctx, opts) *sql.Tx
        +Close() error
    }
    
    class SQLiteProxy {
        -dsnResolver func(orgID, baseDSN) string
        -connections map[string]*sql.DB
        +QueryContext(ctx, query, args) *sql.Rows
        +ExecContext(ctx, query, args) sql.Result
        +BeginTx(ctx, opts) *sql.Tx
        +Close() error
    }
    
    sqlDB <|-- SQLiteProxy : implements same interface
Loading

Request Flow

This diagram shows how a database query flows through the system:

sequenceDiagram
    participant R as Repository
    participant P as SQLiteProxy
    participant DB as SQLite Databases
    
    R->>P: QueryContext(ctx, "SELECT * FROM users")
    P->>P: Extract orgID from context
    P->>P: Resolve DSN from orgID
    P->>P: Get/create connection for DSN
    P->>DB: Forward query to org-specific DB
    DB->>P: Return results
    P->>R: Return results to repository
Loading

📝 Configuration Options

The SQLiteProxy can be configured in various ways:

Basic Configuration

// Basic initialization
proxy := sqliteproxy.NewSQLiteProxy("./data/orgs")

// With custom options
proxy := sqliteproxy.NewSQLiteProxy(
    "./data/orgs",
    // Custom organization ID key in context
    sqliteproxy.WithOrgIDKey("my_custom_org_key"),
    
    // Custom DSN resolver
    sqliteproxy.WithDSNResolver(func(orgID, baseDir string) string {
        return fmt.Sprintf("%s/tenant_%s.db", baseDir, orgID)
    }),
    
    // Connection pool settings
    sqliteproxy.WithMaxOpenConns(10),
    sqliteproxy.WithMaxIdleConns(5),
    sqliteproxy.WithConnMaxLifetime(time.Hour),
    sqliteproxy.WithConnMaxIdleTime(time.Minute * 10),
)

With Migrations

// Add migrations for automatic schema management
migrations := []sqliteproxy.Migration{
    {Version: 1, Name: "create_users_table", Up: `CREATE TABLE users (...)`},
    {Version: 2, Name: "add_name_column", Up: `ALTER TABLE users ADD COLUMN name TEXT`},
}

proxy := sqliteproxy.NewSQLiteProxy("./data/orgs",
    sqliteproxy.WithMigrations(migrations...),
    sqliteproxy.WithMigrationMode(sqliteproxy.MigrationModeAuto),
)

🔄 Schema Migrations

SQLiteProxy includes a comprehensive migration system designed for multi-tenant applications.

Migration Modes

  • Auto Mode (default): Migrations run automatically when a tenant connects
  • Manual Mode: Migrations must be explicitly triggered via API
  • Check Mode: Connections fail if schema is outdated (for strict production environments)

Using Migrations

// 1. Define your migrations
migrations := []sqliteproxy.Migration{
    {
        Version: 1,
        Name:    "initial_schema",
        Up:      `CREATE TABLE users (id INTEGER PRIMARY KEY, email TEXT)`,
        Down:    `DROP TABLE users`, // Optional for rollback
    },
    // Add more migrations as your schema evolves
}

// 2. Configure proxy with migrations
proxy := sqliteproxy.NewSQLiteProxy("./data",
    sqliteproxy.WithMigrations(migrations...),
    sqliteproxy.WithMigrationMode(sqliteproxy.MigrationModeAuto),
)

// 3. For production, use the migrator API
migrator := proxy.Migrator()

// Check migration status
statuses, _ := migrator.Status(ctx)
for _, status := range statuses {
    fmt.Printf("Org %s: version %d/%d\n", 
        status.OrgID, status.CurrentVersion, status.TargetVersion)
}

// Run migrations manually
migrator.MigrateOrg(ctx, "org-123")        // Single org
migrator.MigrateBatch(ctx, orgIDs)         // Multiple orgs
migrator.MigrateAll(ctx)                   // All orgs

// Dry run to see pending migrations
pending, _ := migrator.DryRun(ctx, "org-123")

// Rollback if needed (requires Down migrations)
migrator.Rollback(ctx, "org-123", targetVersion)

Migration Best Practices

  1. Development: Use MigrationModeAuto for seamless development
  2. Production: Use MigrationModeManual or MigrationModeCheck for controlled deployments
  3. New Tenants: Always get the latest schema automatically
  4. Rollbacks: Always provide Down migrations for critical changes

🔄 Converting Existing Applications

Single-Tenant to Multi-Tenant

Converting an existing single-tenant application to multi-tenancy is seamless:

graph TB
    subgraph "Step 1: Original Single-Tenant App"
        A1[Repository] -->|sql.DB| B1[Single SQLite DB]
    end
    
    subgraph "Step 2: Refactor Repository"
        A2[Repository] -->|"interface{ QueryContext, ExecContext }"| B2[DB Implementation]
    end
    
    subgraph "Step 3: Configuration-Based Choice"
        A3[Repository] -->|Same Interface| B3[Configuration]
        B3 -->|"config.MultiTenant=false"| C3[sql.DB]
        B3 -->|"config.MultiTenant=true"| D3[SQLiteProxy]
    end
Loading

The key advantages:

  1. No Repository Changes: Your repository code doesn't need to change at all
  2. Configuration-Based: Switch between single and multi-tenant modes via configuration
  3. Standard Interfaces: No custom interfaces or types required
  4. Gradual Migration: Convert one repository at a time

Migration Steps

  1. Update Repository Constructors: Change from concrete *sql.DB type to interface requirements

    // Before
func NewUserRepo(db *sql.DB) *UserRepo {
    return &UserRepo{db: db}
}

// After
func NewUserRepo(db interface {
    QueryRowContext(context.Context, string, ...interface{}) *sql.Row
    ExecContext(context.Context, string, ...interface{}) (sql.Result, error)
}) *UserRepo {
    return &UserRepo{db: db}
}

  2. Update Main/Config Code: Add logic to choose implementation

    var database interface {
    // Required methods
    QueryRowContext(context.Context, string, ...interface{}) *sql.Row
    ExecContext(context.Context, string, ...interface{}) (sql.Result, error)
    Close() error
}

if config.MultiTenant {
    database = sqliteproxy.NewSQLiteProxy("./data")
} else {
    database = sql.Open("sqlite3", "./data/app.db")
}

// Create repositories
userRepo := repository.NewUserRepo(database)
orderRepo := repository.NewOrderRepo(database)

  3. Update Context Creation: Ensure organization ID is in context when in multi-tenant mode

    if config.MultiTenant {
    ctx = context.WithValue(ctx, sqliteproxy.DefaultOrgIDKey, orgID)
}

🔒 Security Considerations

  • DSN Resolution: Use a custom DSN resolver to validate organization IDs and prevent path traversal attacks
  • Context Validation: Be careful about context propagation throughout your application
  • Query Isolation: While queries go to separate databases, be mindful of resource limits on the host system
  • Error Handling: Handle errors from the proxy appropriately to prevent information leakage

🧪 Testing

Mocking for Unit Tests

The interface-based approach makes mocking very simple:

type mockDB struct {
    queryRowFunc func(ctx context.Context, query string, args ...interface{}) *sql.Row
    execFunc func(ctx context.Context, query string, args ...interface{}) (sql.Result, error)
}

func (m *mockDB) QueryRowContext(ctx context.Context, query string, args ...interface{}) *sql.Row {
    return m.queryRowFunc(ctx, query, args...)
}

func (m *mockDB) ExecContext(ctx context.Context, query string, args ...interface{}) (sql.Result, error) {
    return m.execFunc(ctx, query, args...)
}

// In tests
mock := &mockDB{
    queryRowFunc: func(ctx context.Context, query string, args ...interface{}) *sql.Row {
        // Return mock data
    },
}
repo := NewUserRepo(mock)

Integration Testing

For integration tests, you can use the actual proxy with in-memory SQLite databases:

// Create in-memory database proxy
proxy := sqliteproxy.NewSQLiteProxy(
    ":memory:",
    sqliteproxy.WithDSNResolver(func(orgID, _ string) string {
        return fmt.Sprintf("file::memory:?cache=shared&org=%s", orgID)
    }),
)

// Initialize with test data
initTestData(ctx, proxy)

// Run tests with the proxy
repo := NewUserRepo(proxy)
// ...

📚 Advanced Usage

Middleware for HTTP Handlers

Extract organization ID from HTTP requests:

func OrganizationMiddleware(next http.Handler) http.Handler {
    return http.HandlerFunc(func(w http.ResponseWriter, r *http.Request) {
        // Extract org ID from header, subdomain, JWT, etc.
        orgID := r.Header.Get("X-Organization-ID")
        
        // Add to context
        ctx := context.WithValue(r.Context(), sqliteproxy.DefaultOrgIDKey, orgID)
        
        // Call next handler with updated context
        next.ServeHTTP(w, r.WithContext(ctx))
    })
}

Database Initialization

Initialize organization databases on first access:

proxy := sqliteproxy.NewSQLiteProxy(
    "./data",
    sqliteproxy.WithDSNResolver(func(orgID, baseDir string) string {
        dbPath := filepath.Join(baseDir, fmt.Sprintf("%s.db", orgID))
        
        // Check if database exists
        if _, err := os.Stat(dbPath); os.IsNotExist(err) {
            // Initialize database
            if err := initDatabase(dbPath); err != nil {
                log.Printf("Error initializing database: %v", err)
            }
        }
        
        return dbPath
    }),
)

Using Multiple Organization Identifiers

Support different ways to identify organizations:

// Organization from subdomain
func GetOrgFromSubdomain(r *http.Request) string {
    host := r.Host
    parts := strings.Split(host, ".")
    if len(parts) >= 3 {
        return parts[0]
    }
    return "default"
}

// Organization from path
func GetOrgFromPath(r *http.Request) string {
    path := r.URL.Path
    parts := strings.Split(path, "/")
    if len(parts) >= 2 {
        return parts[1]
    }
    return "default"
}

// Use in middleware
func OrgMiddleware(next http.Handler) http.Handler {
    return http.HandlerFunc(func(w http.ResponseWriter, r *http.Request) {
        var orgID string
        
        // Try various methods to get org ID
        if orgID = r.Header.Get("X-Organization"); orgID != "" {
            // From header
        } else if orgID = GetOrgFromSubdomain(r); orgID != "" {
            // From subdomain
        } else if orgID = GetOrgFromPath(r); orgID != "" {
            // From path
        } else {
            // Default
            orgID = "default"
        }
        
        ctx := context.WithValue(r.Context(), sqliteproxy.DefaultOrgIDKey, orgID)
        next.ServeHTTP(w, r.WithContext(ctx))
    })
}

📈 Performance & Production Readiness

Performance Comparison

SQLiteProxy vs Direct SQLite Performance

SQLiteProxy maintains near-identical performance to direct SQLite while adding multi-tenancy. Benchmark testing shows equivalent or better transaction throughput compared to direct connections.

Performance Considerations:

  • Connection Pooling: Efficient per-organization connection management
  • Context Overhead: Minimal organization ID lookup cost
  • Transaction Strength: Particularly efficient with transaction-heavy workloads
  • Monitoring: Use Stats() method for production monitoring

🛡️ Production Readiness & Robustness

SQLiteProxy has undergone comprehensive stress testing to ensure production readiness. Our "evil" test suite pushes the library to its limits across multiple dimensions.

Evil Stress Test Results

62,000+ operations tested across 7 production nightmare scenarios

Test Success Rate Operations Scenario
🚀 Concurrent Chaos 87.7% 10,000 100 goroutines × 200 orgs × 50 ops each
🧠 Memory Torture 100% 50,000 No leaks detected across 1,000 iterations
⚡ Resource Starvation 94.5% 2,000 100 orgs fighting for 20 connections
🔄 Transaction Hell 100% 1,000 10 concurrent long-running transactions
🛡️ Malicious Attacks 100% 13 vectors SQL injection, path traversal, null bytes
⚡ Rapid Shutdown 100% Variable Clean exit under concurrent load
❌ Context Chaos 100% Variable Pre-cancel, mid-operation, timeout patterns

Key Achievement: Fixed critical resource exhaustion bug - 374% improvement from 20% to 94.5% success rate.

✅ VERDICT: Production Ready - Survives extreme load with graceful degradation

Error Recovery & Resilience Patterns

stateDiagram-v2
    [*] --> Normal: System Start
    Normal --> ResourceLimit: High Load Detected
    ResourceLimit --> EvictionTriggered: Connection Limit Reached
    EvictionTriggered --> ConnectionsFreed: Idle Connections Removed
    ConnectionsFreed --> Normal: Capacity Available
    
    ResourceLimit --> GracefulDegradation: Eviction Insufficient
    GracefulDegradation --> Normal: Load Decreases
    
    Normal --> MaliciousInput: Attack Detected
    MaliciousInput --> SecurityBlock: Input Validation Failed
    SecurityBlock --> Normal: Request Rejected
    
    Normal --> ContextCancelled: Client Timeout
    ContextCancelled --> CleanupResources: Graceful Handling
    CleanupResources --> Normal: Resources Released
    
    Normal --> Shutdown: Stop Signal
    Shutdown --> GracefulShutdown: Wait for Active Requests
    GracefulShutdown --> [*]: Clean Exit
    
    note right of EvictionTriggered
        Intelligent LRU eviction
        maintains 50%+ success rate
        even under extreme load
    end note
    
    note right of SecurityBlock
        Handles SQL injection,
        path traversal, malformed
        input gracefully
    end note
Loading

Chaos Engineering Results

%%{init: {"pie": {"textPosition": 0.5}, "themeVariables": {"pieSectionTextColor": "#000000", "pieTitleTextSize": "25px", "pie1": "#4caf50", "pie2": "#ffeb3b", "pie3": "#f44336"}}}%%
pie title Chaos Engineering Test Results
    "Complete Success" : 71
    "Graceful Degradation" : 24
    "Hard Failures" : 5
Loading

Key Robustness Features

  1. Intelligent Resource Management

    • LRU-based connection eviction
    • Automatic cleanup under pressure
    • 94.5% success rate even when exceeding limits

  2. Security-First Design

    • Path traversal protection
    • SQL injection resistance
    • Input validation and sanitization

  3. Graceful Degradation

    • Clean error handling under all conditions
    • No panics or crashes detected in stress tests
    • Proper resource cleanup on failures

  4. Production Monitoring

    • Built-in metrics and tracing
    • Connection pool statistics
    • Performance monitoring hooks

Running the Benchmarks

To run the benchmarks and generate the performance chart:

# Run the comparison benchmarks
go test -bench=BenchmarkComparison -benchmem ./...

# Run the evil stress tests
go test -v ./sqliteproxy_tests -run TestEvil

To generate this chart and run the benchmarks yourself:

go run ./cmd/benchmark_chart.go

Last updated: 2025-07-30

🤝 Contributing

Contributions are welcome! Please feel free to submit a Pull Request.

📄 License

This project is licensed under the MIT License - see the LICENSE file for details.

About

proxy to use sqlite for multi tenant

Resources

Readme

License

MIT license
Activity
Custom properties

Stars

0 stars

Watchers

2 watching

Forks

0 forks
Report repository

Releases

No releases published

Packages

 
 
 

Contributors

Languages