Distant SQLite - SQLite gRPC Server

A gRPC server that provides remote access to SQLite database (concurrent access using SQLite's WAL mode)

Project Structure

distant-sqlite/
├── src/
│   ├── main.rs                     # gRPC server entry point
│   ├── client_auth.rs              # Example client with authentication
│   ├── auth.rs                     # Authentication module
│   └── sqlite_server/
│       ├── mod.rs                  # Module exports
│       └── sqlite_server.rs        # Core SQLite service implementation
├── proto/
│   └── sqlite.proto                # gRPC service definition
├── Cargo.toml                      # Dependencies and build configuration
├── build.rs                        # Protocol buffer compilation
└── database.db                     # SQLite database file (created at runtime)

Quick Start

Building the Project

# Clone the repository
git clone <repository-url>
cd distant-sqlite

# Build the project
cargo build --release

# Run the server
cargo run --bin server

# run client example
cargo run --bin client-auth

Client Implementation Examples

Rust Client

use tonic::{Request, metadata::MetadataValue};
use sqlite::sqlite_service_client::SqliteServiceClient;
use sqlite::{ReadQueryRequest, WriteQueryRequest};

#[tokio::main]
async fn main() -> Result<(), Box<dyn std::error::Error>> {
    let mut client = SqliteServiceClient::connect("http://[::1]:50051").await?;
    let token = "secret-token-123"; // Authentication token

    // Create table with authentication
    let mut create_request = Request::new(WriteQueryRequest {
        query: "CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT, age INTEGER)".to_string(),
    });
    // Add Bearer token to metadata
    create_request.metadata_mut().insert(
        "authorization",
        MetadataValue::from_str(&format!("Bearer {}", token))?
    );
    client.exe_write_query(create_request).await?;

    // Insert data with authentication
    let mut insert_request = Request::new(WriteQueryRequest {
        query: "INSERT INTO users (name, age) VALUES ('Alice', 30)".to_string(),
    });
    insert_request.metadata_mut().insert(
        "authorization",
        MetadataValue::from_str(&format!("Bearer {}", token))?
    );
    client.exe_write_query(insert_request).await?;

    // Query data with authentication
    let mut read_request = Request::new(ReadQueryRequest {
        query: "SELECT * FROM users".to_string(),
    });
    read_request.metadata_mut().insert(
        "authorization",
        MetadataValue::from_str(&format!("Bearer {}", token))?
    );
    let response = client.exe_read_query(read_request).await?;

    // Process results...
    for row in response.into_inner().rows {
        for (column, value) in row.columns {
            match &value.value_type {
                Some(sqlite::value::ValueType::TextValue(s)) => println!("{}: {}", column, s),
                Some(sqlite::value::ValueType::IntegerValue(n)) => println!("{}: {}", column, n),
                Some(sqlite::value::ValueType::RealValue(f)) => println!("{}: {}", column, f),
                Some(sqlite::value::ValueType::NullValue(_)) => println!("{}: NULL", column),
                Some(sqlite::value::ValueType::BlobValue(b)) => println!("{}: {} bytes", column, b.len()),
                None => println!("{}: UNKNOWN", column),
            }
        }
    }

    Ok(())
}

Python Client

import grpc
import sqlite_pb2
import sqlite_pb2_grpc

async def main():
    async with grpc.aio.insecure_channel('[::1]:50051') as channel:
        stub = sqlite_pb2_grpc.SqliteServiceStub(channel)

        # Authentication metadata
        metadata = [('authorization', 'Bearer secret-token-123')]

        # Create table with authentication
        request = sqlite_pb2.WriteQueryRequest(
            query="CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT, age INTEGER)"
        )
        response = await stub.ExeWriteQuery(request, metadata=metadata)

        # Insert data with authentication
        request = sqlite_pb2.WriteQueryRequest(
            query="INSERT INTO users (name, age) VALUES ('Alice', 30)"
        )
        response = await stub.ExeWriteQuery(request, metadata=metadata)

        # Query data with authentication
        request = sqlite_pb2.ReadQueryRequest(query="SELECT * FROM users")
        response = await stub.ExeReadQuery(request, metadata=metadata)

        for row in response.rows:
            for column, value in row.columns.items():
                if value.HasField('text_value'):
                    print(f"{column}: {value.text_value}")
                elif value.HasField('integer_value'):
                    print(f"{column}: {value.integer_value}")
                elif value.HasField('real_value'):
                    print(f"{column}: {value.real_value}")
                elif value.HasField('null_value'):
                    print(f"{column}: NULL")
                elif value.HasField('blob_value'):
                    print(f"{column}: {len(value.blob_value)} bytes")

SQLite Configuration

• WAL Mode: Enables concurrent readers with writers
• Synchronous NORMAL: Balances durability with performance
• Busy Timeout: 30-second timeout for handling locked databases
• WAL Autocheckpoint: Automatic cleanup every 1000 pages