ha

go-ha

A Go database/sql base driver providing high availability for SQLite databases through NATS-based replication.

Features

• High Availability: Ensure your SQLite databases remain accessible and consistent across multiple nodes.
• Replication: Synchronize data across nodes using NATS messaging.
• Flexible Conflict Resolution: Customize replication strategies and handle conflicts with ChangeSetInterceptor.
• Cluster Modes:
  • Leaderless: Read/write from any node with last-writer-wins resolution.
  • Leader-based: Redirect writes to a leader to prevent conflicts.
• NATS Integration: Choose between embedded or external NATS servers.
• Seamless Integration: Drop-in replacement for existing Go database/sql usage.
• gRPC Support: Expose database operations via gRPC for remote access the sqlite database.
• Snapshotting: Automatic database snapshots for efficient synchronization.
• Cross-database Queries: Transparently execute cross-shard queries using SQL hints /*+ db=DSN */.
• Transaction Undo: Revert already committed transactions.

Architecture

go-ha enables high availability for SQLite by replicating changes across multiple nodes using NATS as the messaging backbone. Each node maintains a local SQLite database and publishes changes to a NATS stream. Other nodes subscribe to this stream and apply the changes, ensuring data consistency.

Comparison with SQLite Session Extension

go-ha provides a modern alternative to SQLite's session extension with enhanced capabilities:

go-ha is designed for distributed systems requiring schema synchronization across nodes, while the session extension is suited for simpler peer-to-peer synchronization.

Installation

go get github.com/litesql/go-ha

Quick Start

Basic Usage

Instance 1

package main

import (
    "database/sql"
    "github.com/litesql/go-ha"
	sqlite3ha "github.com/litesql/go-sqlite3-ha"
)

func main() {
    slog.SetLogLoggerLevel(slog.LevelDebug)
    // Open a connection with HA enabled
    c, err := sqlite3ha.NewConnector("file:my.db?_journal=WAL&_timeout=5000",
		ha.WithName("instance1"),
        ha.WithReplicationID("example"),
		ha.WithGrpcPort(5000),		
		ha.WithEmbeddedNatsConfig(&ha.EmbeddedNatsConfig{
			Port: 4222,
		}))
	if err != nil {
		panic(err)
	}
	defer c.Close()

	db := sql.OpenDB(c)
	defer db.Close()

    // Use like any other database/sql driver
    _, err = db.Exec("CREATE TABLE IF NOT EXISTS users (id INTEGER PRIMARY KEY, name TEXT)")
    if err != nil {
        panic(err)
    }

    // Insert data
    _, err = db.Exec("INSERT INTO users (name) VALUES (?)", "Alice")
    if err != nil {
        panic(err)
    }
}

Instance 2 (leader-based)

package main

import (
    "database/sql"
    "github.com/litesql/go-ha"
	sqlite3ha "github.com/litesql/go-sqlite3-ha"
)

func main() {
    slog.SetLogLoggerLevel(slog.LevelDebug)
    db, err := sql.Open("sqlite3-ha", "file:my2.db?_journal=WAL&_timeout=5000&replicationURL=nats://localhost:4222&replicationID=example&name=instance2&grpcInsecure=true&leaderProvider=static:localhost:5000")
	if err != nil {
		panic(err)
	}
	defer db.Close()

	_, err = db.Exec("INSERT INTO users(name) values('grpc leader redirect')")
	if err != nil {
		panic(err)
	}

	var name string
	err = db.QueryRowContext(context.Background(), "SELECT name FROM users ORDER BY rowid desc LIMIT 1").Scan(&name)
	if err != nil {
		panic(err)
	}

	fmt.Println("User:", name)
}

Cross-Database Queries

Execute queries across multiple databases using SQL hints:

// Query data from all loaded databases from the driver
rows, err := db.Query("/*+ db=.* */ SELECT * FROM users WHERE id = ?", 1)
if err != nil {
    panic(err)
}
defer rows.Close()

Drivers

go-ha provides several driver implementations:

• go-sqlite3-ha: CGO-based driver using go-sqlite3.
• go-sqlite-ha: CGO-free driver using modernc.org/sqlite.
• go-remote-ha: Remote-only driver for accessing HA clusters via gRPC.

Configuration Options

Performance Considerations

• Use embedded NATS for single-machine deployments to reduce latency.
• Configure snapshotInterval based on your write frequency.
• For high-throughput scenarios, consider leader-based clusters to avoid conflicts.
• Monitor NATS stream size and adjust streamMaxAge to prevent unbounded growth.

Troubleshooting

Common Issues

• Replication not working: Ensure NATS server is running and accessible via replicationURL.
• Conflicts in leaderless mode: Implement a custom ChangeSetInterceptor for complex conflict resolution.
• Slow synchronization: Check snapshotter configuration and network latency.
• gRPC connection errors: Verify grpcPort and grpcToken settings.

Projects Using go-ha

• HA: Highly available leaderless SQLite cluster with HTTP and PostgreSQL Wire Protocol
• PocketBase HA: Highly available leaderless PocketBase cluster
• sqlc-http: Generate net/http Go server from SQL

Contributing

We welcome contributions! Please:

1. Fork the repository
2. Create a feature branch
3. Make your changes
4. Add tests if applicable
5. Submit a pull request

For major changes, please open an issue first to discuss the proposed changes.

License

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