SyncDB — Git for Databases

A peer-to-peer database engine that works like Git, but for structured tabular data. Every node maintains its own local SQLite replica and full commit history. Nodes sync over TCP, automatically resolving concurrent edits using Last-Write-Wins conflict resolution, eliminating the disruptiveness of merge conflicts.

How it works

To interact with SyncDB, a Git-based command workflow is applied, but to structured data tables rather than files. Users on each node create tables with named and typed columns, and insert rows to, update cells in, and remove rows from these tables. Each change is staged locally, and users can commit staged changes with a message and push them to an origin server. Users on other nodes can pull these changes from the origin server.

Multiple tables can coexist on each node, and SyncDB supports operations like deleting entire tables, using diff to compare the state of a table between any two commits, viewing of full commit logs, checking out the state at past commits, and fully reverting to past commits.

Each SyncDB node maintains an operation log that records sets, deletes, and schema changes as immutable operations with a vector clock snapshot, and this log along with metadata are stored in a SQLite Database.

Each node also uses a vector clock to track causalities between nodes. During a sync, namely a push or pull operation, nodes connect through TCP and perform a bidirectional handshake: sending vector clocks, computing the diff, and exchanging only missing operations.

If there are concurrent writes to the same cell in a database, then Last-Write-Wins conflict resolution is used as a tiebreaker, ensuring that all replicas converge to the same value, and avoiding the tediousness of merge conflicts commonly experienced in traditional Git.

Installation

Requires Python 3.10+ and no other external dependencies.

git clone https://github.com/smriti-kumar/syncdb
cd syncdb
pip install -e .

Usage

Terminal 1: start the central server:

$ syncdb-server origin 8000

Terminal 2 node A:

$ syncdb nodeA 8001

Terminal 3: node B:

$ syncdb nodeB 8002

Commands

Command	Description
create <table> <col:type> ...	Stage a new table creation
insert <table> <row_key> <col=val> ...	Stage a row insertion
update <table> <row_key> <col> <val>	Stage a cell update
remove <table> <row_key>	Stage a row deletion
delete <table>	Stage a table deletion
commit <message>	Commit all staged changes
status	Show staged changes
discard	Clear staged changes
push	Push commits to origin
pull	Pull commits from origin
show <table>	Display table (includes staged changes)
history	Show full commit log
history <table>	Show commits touching a table
diff <id_a> <id_b> <table>	Diff two commits
checkout <commit_id>	View state at a past commit
revert <commit_id>	Revert to a past commit (non-destructive)
help	Display commands and format
exit	Shut down node

Using SyncDB across machines

By default all nodes run on localhost. To sync across machines on the same network, pass the LAN IP of the machine running the server. For machines on different networks, Tailscale can create a private mesh network, and the localhost should be replaced with each machine's Tailscale IP. No code changes are required.

Project structure

syncdb/
├── clock.py
├── commit.py
├── db.py
├── network.py
├── node_runner.py
├── protocol.py
├── node.py
├── server.py
pyproject.toml
README.md