golemdb-sql

🚀 Project Overview

github: https://github.com/prekucki/golemdb-sqlalchemy

  GolemDB-SQLAlchemy bridges the gap between traditional SQL databases and decentralized storage by providing a complete SQLAlchemy dialect for GolemDB. This enables developers to use familiar SQL syntax and Python ORMs while leveraging the security and decentralization benefits of blockchain-based data  storage.

 🎯 Problem Statement

Developers want to build decentralized applications but face a steep learning curve when transitioning  from traditional databases to blockchain storage solutions. Current approaches require:

• Learning new query languages and APIs
• Rewriting existing database schemas and queries
• Abandoning familiar ORM frameworks like SQLAlchemy
• Manual data serialization and indexing strategies

💡 Our Solution

import golemdb_sql
# Connect to GolemBase database
    conn = golemdb_sql.connect(
        rpc_url=rpc_url,
        ws_url=ws_url,
        private_key=private_key,
        app_id=app_id,
        schema_id=schema_id
    )
    
    try:
        print("\n1. Creating 'users' table...")
        cursor.execute("""
            CREATE TABLE users (
                id INTEGER PRIMARY KEY,
                name VARCHAR(100) NOT NULL,
                email VARCHAR(200) NOT NULL,
                age INTEGER,
                active BOOLEAN DEFAULT TRUE,
                balance DECIMAL(10,2),
                created_at DATETIME DEFAULT CURRENT_TIMESTAMP
            )
        """)
        
        cursor.execute("CREATE INDEX idx_users_email ON users(email)")
        cursor.execute("CREATE INDEX idx_users_active ON users(active)")

        cursor.execute(
                "INSERT INTO users (id, name, email, age, active, balance) VALUES (%(id)s, %(name)s, %(email)s, %(age)s, %(active)s, %(balance)s)",
                {
                    'id': 1, 
                    'name': 'Alice Smith', 
                    'email': '[email protected]',
                    'age': 28, 
                    'active': True, 
                    'balance': 1250.50
                }
            )
        print("    ✅ INSERT operation successful")
            
        # Test SELECT to verify data exists
        print("  Testing SELECT with %(name)s parameters...")
        cursor.execute("SELECT id, name, email FROM users WHERE id = %(id)s", {'id': 1})
         result = cursor.fetchone()
         if result:
            print(f"    ✅ SELECT operation successful: {result[1]} ({result[2]})")
         else:
            print("    ❌ No data found")

  🏗️ Technical Architecture

  - Signed Integer Encoding: Preserves ordering when mapping SQL integers to GolemDB's uint64 storage

  - DECIMAL String Encoding: Lexicographic ordering for precise financial calculations

  - Multi-tenant Annotations: relation="project.table" enables project isolation

  SQL-to-Annotation Translation

  SELECT * FROM posts WHERE author_id = 42 AND is_published = true

  ↓ Translates to GolemDB query:

  relation="myproject.posts" && idx_author_id=9223372036854775851 && idx_is_published=1

  Complete DB-API 2.0 Implementation

  - PEP 249 Compliant: Works with any Python database tooling

  - Transaction Support: Full commit/rollback functionality

  - Connection Pooling: Thread-safe connection management

  - Type Constructors: Seamless Python ↔ GolemDB type mapping

🎨 Key Features

🔄 Zero Learning Curve

• Drop-in replacement for existing SQLAlchemy applications
• Supports complex JOINs, subqueries, and aggregations
• Full migration support for existing schemas

🏢 Multi-Tenant by Design

• Project-scoped data isolation: projectA.users vs projectB.users
• Secure cross-tenant boundaries
• Horizontal scaling by project

⚡ Performance Optimized

  - Smart indexing with idx_ prefixed annotations

  - Efficient range queries with proper encoding strategies

  - Schema metadata caching for fast query translation

  🛠️ Technical Stack

  - Python 3.10+ with full type hints

  - SQLAlchemy 2.0 compatibility

  - GolemDB SDK 0.1.0 for blockchain integration

  - SQLglot for SQL parsing and transformation

  - TOML for schema persistence