Nelknet.LibSQL.Bindings 0.1.0-alpha

Nelknet.LibSQL

A native .NET client library for libSQL databases, providing a complete ADO.NET implementation for seamless integration with existing .NET applications.

Overview

Nelknet.LibSQL is a C# client library that provides native bindings to libSQL, a fork of SQLite that adds support for replication, remote connections, and other distributed features. This library follows the ADO.NET pattern, making it easy to use for developers familiar with other .NET database providers.

Key Features

• Native Performance: Direct P/Invoke bindings to the libSQL C library
• ADO.NET Compliant: Implements standard interfaces (DbConnection, DbCommand, DbDataReader, etc.)
• Modern C#: Uses LibraryImport for better performance and AOT compatibility
• Cross-Platform: Supports Windows, Linux, and macOS (x64 and ARM64)
• libSQL Features: Access to remote databases, embedded replicas, and sync capabilities
• Type Safe: Comprehensive type mapping between libSQL and .NET types

Installation

Install via NuGet Package Manager:

dotnet add package Nelknet.LibSQL.Data

Or via Package Manager Console:

Install-Package Nelknet.LibSQL.Data

Note: The package is currently in alpha. To install pre-release versions:

dotnet add package Nelknet.LibSQL.Data --prerelease

Quick Start

Basic Usage

using Nelknet.LibSQL.Data;

// Create and open a connection
using var connection = new LibSQLConnection("Data Source=local.db");
connection.Open();

// Create a table
using var createCmd = connection.CreateCommand();
createCmd.CommandText = @"
    CREATE TABLE users (
        id INTEGER PRIMARY KEY,
        name TEXT NOT NULL,
        email TEXT UNIQUE
    )";
createCmd.ExecuteNonQuery();

// Insert data
using var insertCmd = connection.CreateCommand();
insertCmd.CommandText = "INSERT INTO users (name, email) VALUES (@name, @email)";
insertCmd.Parameters.AddWithValue("@name", "Alice");
insertCmd.Parameters.AddWithValue("@email", "alice@example.com");
insertCmd.ExecuteNonQuery();

// Query data
using var queryCmd = connection.CreateCommand();
queryCmd.CommandText = "SELECT * FROM users";
using var reader = queryCmd.ExecuteReader();

while (reader.Read())
{
    var id = reader.GetInt32(0);
    var name = reader.GetString(1);
    var email = reader.GetString(2);
    Console.WriteLine($"User {id}: {name} ({email})");
}

Remote Database Connection

// Connect to a remote libSQL database
var connectionString = "Data Source=libsql://your-database.turso.io;AuthToken=your-auth-token";
using var connection = new LibSQLConnection(connectionString);
connection.Open();

// Use the connection normally...

Embedded Replica with Sync

// Create an embedded replica that syncs with a remote primary
var connectionString = @"
    Data Source=local-replica.db;
    SyncUrl=libsql://your-database.turso.io;
    AuthToken=your-auth-token;
    ReadYourWrites=true";

using var connection = new LibSQLConnection(connectionString);
connection.Open();

// Sync with remote
await connection.SyncAsync();

// Perform local reads with remote consistency
using var cmd = connection.CreateCommand();
cmd.CommandText = "SELECT * FROM products WHERE price > 100";
using var reader = cmd.ExecuteReader();
// ... process results ...

Using Transactions

using var connection = new LibSQLConnection("Data Source=local.db");
connection.Open();

using var transaction = connection.BeginTransaction();
try
{
    using var cmd = connection.CreateCommand();
    cmd.Transaction = transaction;
    
    // Multiple operations in a transaction
    cmd.CommandText = "UPDATE accounts SET balance = balance - 100 WHERE id = 1";
    cmd.ExecuteNonQuery();
    
    cmd.CommandText = "UPDATE accounts SET balance = balance + 100 WHERE id = 2";
    cmd.ExecuteNonQuery();
    
    transaction.Commit();
}
catch
{
    transaction.Rollback();
    throw;
}

Async Operations

using var connection = new LibSQLConnection("Data Source=local.db");
await connection.OpenAsync();

using var cmd = connection.CreateCommand();
cmd.CommandText = "SELECT COUNT(*) FROM users";
var count = await cmd.ExecuteScalarAsync();

Console.WriteLine($"Total users: {count}");

Connection String Options

Design Choices

Why the Experimental libSQL API?

This library uses libSQL's experimental C API (libsql_* functions) rather than the SQLite C API (sqlite3_* functions). This design choice was made to:

1. Access libSQL-specific features: The experimental API provides access to replication, remote connections, and other distributed features not available in SQLite.

2. Follow official patterns: The official Go client uses the same experimental API, validating this as the intended approach for native clients.

3. Maintain consistency: Using a single API throughout the library avoids potential conflicts between different memory management and state handling approaches.

Trade-offs and Limitations

This library, like other official libSQL clients (Python, Go, etc.), focuses on core database functionality and libSQL-specific features rather than exposing the full SQLite API. The following SQLite features are intentionally not supported:

• Custom SQL functions (sqlite3_create_function)
• SQLite backup API (sqlite3_backup_*)
• Extended result codes (sqlite3_extended_result_codes)
• Blob I/O (sqlite3_blob_*)
• Load extensions (sqlite3_load_extension)
• Authorization callbacks (sqlite3_set_authorizer)
• Progress handlers (sqlite3_progress_handler)

If you need these advanced SQLite features, consider using Microsoft.Data.Sqlite or System.Data.SQLite instead.

ADO.NET Implementation

The library implements the standard ADO.NET interfaces to ensure compatibility with existing .NET code and patterns:

• Familiar API for .NET developers
• Compatible with ORMs like Dapper
• Follows Microsoft.Data.Sqlite patterns where applicable
• Supports both synchronous and asynchronous operations

Architecture

Nelknet.LibSQL/
├── Nelknet.LibSQL.Bindings/     # P/Invoke bindings to libSQL C API
│   ├── LibSQLNative.cs          # Native method declarations
│   ├── SafeHandles.cs           # Safe handle wrappers
│   └── NativeStructs.cs         # Native structure definitions
└── Nelknet.LibSQL.Data/         # ADO.NET implementation
    ├── LibSQLConnection.cs      # DbConnection implementation
    ├── LibSQLCommand.cs         # DbCommand implementation
    ├── LibSQLDataReader.cs      # DbDataReader implementation
    └── LibSQLParameter.cs       # DbParameter implementation

Requirements

• .NET 8.0 or later
• libSQL native library (automatically included via NuGet)
• Supported platforms:
  • Windows x64/ARM64
  • Linux x64/ARM64
  • macOS x64/ARM64

License

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

Acknowledgments

• libSQL - The underlying database engine
• DuckDB.NET - Inspiration for native binding patterns
• Microsoft.Data.Sqlite - Reference for ADO.NET implementation

Documentation

Getting Started

• Getting Started Guide - Quick introduction and basic usage
• Connection Strings - All connection string options explained
• Code Examples - Practical examples for common scenarios

Advanced Topics

• Performance Tuning - Optimization techniques and best practices
• API Reference - Complete API documentation

Example Projects

• BasicExample - Comprehensive console application demonstrating all major features
• More Examples - Additional example projects

Current Status & Roadmap

✅ Completed Features

• Full ADO.NET implementation (Connection, Command, DataReader, etc.)
• Local file database support
• Transaction support
• Parameter binding (named and positional)
• Bulk insert operations
• Connection pooling
• Schema introspection
• Embedded Replica Support - Sync with remote libSQL servers ✨ NEW
  • Manual and automatic sync operations
  • Read-your-writes consistency
  • Offline mode for disconnected operation
  • Sync event notifications
• Comprehensive test suite (318+ tests)
• NuGet packages ready for publishing

🚧 In Progress / Planned

• Phase 21: Remote Connection Support - Direct HTTPS/libSQL protocol connections
• Phase 22: Batch Operations - Execute multiple statements atomically
• Phase 23: Interactive Transactions - Long-lived transactions with application logic
• Phase 24: Additional Features - Encryption, in-memory databases, migrations

Feature Comparison

Links

• libSQL Documentation
• Turso Database - Managed libSQL hosting
• NuGet Package
• Report Issues