cinchdb 0.1.21

A Git-like SQLite database management system with branching and multi-tenancy

CinchDB

Git-like SQLite database management with branching and multi-tenancy

NOTE: CinchDB is in early alpha. This is project to test out an idea. Do not use this in production.

CinchDB is for projects that need fast queries, isolated data per-tenant or even per-user, and a branchable database that makes it easy to merge changes between branches.

Because it's so lightweight and its only dependencies are pydantic, requests, and Typer, it makes for a perfect local development database that can be controlled programmatically.

On a meta level: I made this because I wanted a database structure that I felt comfortable letting AI agents take full control over.

# Recommended: Install with uv (faster, better dependency resolution)
uv add cinchdb

# Or with pip
pip install cinchdb

# Initialize project
cinch init 

# Create and query tables
cinch table create users name:TEXT email:TEXT
cinch query "SELECT * FROM users"

# Git-like branching
cinch branch create feature
cinch branch switch feature
cinch table create products name:TEXT price:REAL
cinch branch merge-into-main feature

# Multi-tenant support
cinch tenant create customer_a
cinch query "SELECT * FROM users" --tenant customer_a

# Tenant encryption (bring your own keys)
cinch tenant create secure_customer --encrypt --key="your-secret-key"
cinch query "SELECT * FROM users" --tenant secure_customer --key="your-secret-key"

# Future: Remote connectivity planned for production deployment

# Autogenerate Python SDK from database
cinch codegen generate python cinchdb_models/

What is CinchDB?

CinchDB combines SQLite with Git-like workflows for database schema management:

• Branch schemas like code - create feature branches, make changes, merge back
• Multi-tenant isolation - shared schema, isolated data per tenant
• Automatic change tracking - all schema changes tracked and mergeable
• Safe structure changes - change merges happen atomically with zero rollback risk (seriously)
• Type-safe Python SDK - Python SDK with full type safety
• SDK generation from database schema - Generate a typesafe SDK from your database models for CRUD operations

Installation

Requires Python 3.10+:

pip install cinchdb

Quick Start

CLI Usage

# Initialize project
cinch init my_app
cd my_app

# Create schema on feature branch
cinch branch create user-system
cinch table create users username:TEXT email:TEXT
cinch view create active_users "SELECT * FROM users WHERE created_at > datetime('now', '-30 days')"

# Merge to main
cinch branch merge-into-main user-system

# Multi-tenant operations
cinch tenant create customer_a
cinch tenant create customer_b
cinch query "SELECT COUNT(*) FROM users" --tenant customer_a

Python SDK

import cinchdb
from cinchdb.models import Column

# Local connection
db = cinchdb.connect("myapp")

# Create schema
db.create_table("posts", [
    Column(name="title", type="TEXT",nullable=False),
    Column(name="content", type="TEXT")
])

# Query data
results = db.query("SELECT * FROM posts WHERE title LIKE ?", ["%python%"])

# CRUD operations - single insert
post_id = db.insert("posts", {"title": "Hello World", "content": "First post"})

# Batch insert - multiple records at once
posts = db.insert("posts",
    {"title": "First", "content": "Content 1"},
    {"title": "Second", "content": "Content 2"},
    {"title": "Third", "content": "Content 3"}
)

# Or with a list using star expansion
post_list = [
    {"title": "Post A", "content": "Content A"},
    {"title": "Post B", "content": "Content B"}
]
results = db.insert("posts", *post_list)

db.update("posts", post_id, {"content": "Updated content"})

Architecture

Storage Architecture

CinchDB uses a tenant-first storage model where database and branch are organizational metadata concepts, while tenants represent the actual isolated data stores:

.cinchdb/
├── metadata.db                    # Organizational metadata
└── {database}-{branch}/           # Context root (e.g., main-main, prod-feature)
    ├── {shard}/                   # SHA256-based sharding (first 2 chars)
    │   ├── {tenant}.db            # Actual SQLite database
    │   └── {tenant}.db-wal        # WAL file
    └── ...

Key Design Decisions:

• Tenant-first: Each tenant gets its own SQLite database file
• Flat hierarchy: Database/branch form a single context root, avoiding deep nesting
• Hash sharding: Tenants are distributed across 256 shards using SHA256 for scalability
• Lazy initialization: Tenant databases are created on first access, not on tenant creation
• WAL mode: All databases use Write-Ahead Logging for better concurrency

This architecture enables:

• True multi-tenant isolation at the file system level
• Efficient branching without duplicating tenant data
• Simple backup/restore per tenant
• Horizontal scaling through sharding

Components

• Python SDK: Core functionality for local development
• CLI: Full-featured command-line interface

CinchDB API Reference

Core Methods

Database Connection

db = cinchdb.CinchDB(database="myapp", branch="main")

Query Execution

query(sql: str, params: List = None) -> List[Dict]

Execute a SQL query and return results.

# Simple query
users = db.query("SELECT * FROM users WHERE age > ?", [18])
# Expected output: [{"id": 1, "name": "Alice", "age": 25}, ...]

# Query with no results
empty = db.query("SELECT * FROM users WHERE id = ?", [999])
# Expected output: []

Common Errors:

• ValueError: Table 'users' does not exist - Create the table first
• sqlite3.OperationalError - Check SQL syntax

Table Management

create_table(name: str, columns: List[Column], indexes: List[Index] = None) -> Table

Create a new table with specified columns.

from cinchdb.models import Column

table = db.create_table(
    "products",
    columns=[
        Column(name="id", type="INTEGER", primary_key=True),
        Column(name="name", type="TEXT", nullable=False),
        Column(name="price", type="REAL", default=0.0)
    ]
)
# Expected: Table 'products' created in ~5ms

get_table(name: str) -> Table

Get table information and schema.

table = db.get_table("users")
# Returns: Table object with columns, indexes, row count

list_tables() -> List[Table]

List all tables in the database.

tables = db.list_tables()
# Expected output: [Table(name="users"), Table(name="products")]

Data Operations

insert(table: str, *data: Dict) -> Dict | List[Dict]

Insert one or more records into a table.

# Single insert
user = db.insert("users", {"name": "Bob", "email": "[e-mail]"})
# Expected output: {"id": 1, "name": "Bob", "email": "[e-mail]"}

# Bulk insert
users = db.insert("users",
    {"name": "Alice", "email": "[e-mail]"},
    {"name": "Charlie", "email": "[e-mail]"}
)
# Expected output: [{"id": 2, ...}, {"id": 3, ...}]
# Performance: ~1ms per record for small datasets

update(table: str, record_id: str, data: Dict) -> Dict

Update a record by ID.

updated = db.update("users", "1", {"email": "[e-mail]"})
# Expected output: {"id": 1, "name": "Bob", "email": "[e-mail]"}

Common Errors:

• ValueError: Record with ID '999' not found - Check if record exists

delete(table: str, *ids: str) -> int

Delete records by ID.

deleted_count = db.delete("users", "1", "2", "3")
# Expected output: 3 (number of deleted records)

delete_where(table: str, **filters) -> int

Delete records matching filters.

deleted = db.delete_where("users", age__lt=18)
# Expected output: 5 (number of deleted records)

Branch Operations

create_branch(name: str, source_branch: str = "main") -> Branch

Create a new schema branch.

branch = db.create_branch("feature/new-tables")
# Expected: Branch created in ~10ms
# Note: Does not copy tenant data, only schema

list_branches() -> List[Branch]

List all branches.

branches = db.list_branches()
# Expected output: [Branch(name="main"), Branch(name="feature/new-tables")]

merge_branches(source: str, target: str = "main") -> Dict

Merge schema changes between branches.

result = db.merge_branches("feature/new-tables", "main")
# Expected output: {
#   "status": "success",
#   "changes_applied": 3,
#   "conflicts": []
# }

Common Errors:

• ConflictError: Table 'users' has conflicting changes - Resolve conflicts manually

Tenant Management

create_tenant(name: str, lazy: bool = True) -> Tenant

Create a new tenant (isolated data store).

tenant = db.create_tenant("customer_123")
# Expected: Tenant created (lazy mode - database created on first access)
# Performance: <1ms in lazy mode, ~10ms if immediate

list_tenants() -> List[Tenant]

List all tenants.

tenants = db.list_tenants()
# Expected output: [Tenant(name="main"), Tenant(name="customer_123")]

delete_tenant(name: str) -> None

Delete a tenant and all its data.

db.delete_tenant("customer_123")
# Warning: This permanently deletes all tenant data!

Index Management

create_index(name: str, table: str, columns: List[str], unique: bool = False) -> Index

Create an index for better query performance.

index = db.create_index(
    "idx_users_email",
    table="users",
    columns=["email"],
    unique=True
)
# Expected: Index created in ~5ms
# Performance impact: 10-100x faster lookups on indexed columns

list_indexes(table: str = None) -> List[Dict]

List indexes, optionally filtered by table.

indexes = db.list_indexes("users")
# Expected output: [
#   {"name": "idx_users_email", "unique": True, "columns": ["email"]}
# ]

Column Operations

add_column(table: str, column: Column) -> None

Add a new column to an existing table.

from cinchdb.models import Column

db.add_column("users", Column(name="age", type="INTEGER", default=0))
# Expected: Column added to all tenants in ~10ms

drop_column(table: str, column: str) -> None

Remove a column from a table.

db.drop_column("users", "age")
# Warning: This removes the column from all tenants!

rename_column(table: str, old_name: str, new_name: str) -> None

Rename a column.

db.rename_column("users", "email", "email_address")
# Expected: Column renamed across all tenants

View Management

create_view(name: str, sql: str) -> View

Create a SQL view.

view = db.create_view(
    "active_users",
    "SELECT * FROM users WHERE last_login > datetime('now', '-30 days')"
)
# Expected: View created in ~5ms

list_views() -> List[View]

List all views in the database.

views = db.list_views()
# Expected output: [View(name="active_users")]

Performance Guidelines

• Query Performance: Simple queries < 1ms, complex joins < 10ms
• Insert Performance: ~1ms per record for single inserts, ~0.1ms per record for bulk
• Branch Operations: Schema operations < 20ms
• Tenant Creation: Lazy mode < 1ms, immediate mode ~10ms
• Analytics Overhead: < 5% performance impact when enabled

Troubleshooting Common Issues

"Table does not exist"

• Check current database/branch: db.current_branch
• List tables: db.list_tables()
• Ensure tenant is active: db.current_tenant

"Database is locked"

• CinchDB uses WAL mode to minimize locking
• Check for long-running transactions
• Ensure connections are properly closed

Performance Issues

• Create indexes on frequently queried columns
• Use db.get_analytics_stats() to identify slow queries
• Consider tenant sharding for large datasets

Security

CinchDB uses standard SQLite security features:

• WAL mode: Better concurrency and crash recovery
• Foreign key constraints: Enforced data integrity
• File permissions: Standard OS-level access control
• Multi-tenant isolation: Separate database files per tenant

For production deployments, consider additional security measures at the infrastructure level.

Development

git clone https://github.com/russellromney/cinchdb.git
cd cinchdb
make install-all
make test

Future

CinchDB focuses on being a simple, reliable SQLite management layer. Future development will prioritize:

• Remote API server improvements
• Better CLI user experience
• Performance optimizations
• Additional language SDKs (TypeScript, Go, etc.)
• Enhanced codegen features

License

Apache 2.0 - see LICENSE

---

CinchDB - Database management as easy as version control