Universal Local DB (universal-local-db)

Universal embedded local database with a Supabase-like fluent API.

Universal Local DB brings the beloved developer experience of Supabase directly to your local computer. It is designed for developers who need local persistence without spinning up Docker containers, managing cloud connections, or wrangling complex ORMs.

Create tables effortlessly using simple schemas or raw SQL queries, and manipulate your data with an intuitive, chainable client: db.table("users").insert(...), .select(), .update(), and .delete().

---

⚡ Highlights

• Supabase-Style DX: Intuitive fluent query builder (select, insert, update, delete, upsert, eq, order, limit, single).
• Zero External Dependencies: Built entirely on Python's robust SQLite engine with WAL mode and thread safety.
• Flexible Schema Management: Define tables with declarative dictionary shortcuts ("id", "string", "int", "json", "datetime") or raw SQL statements.
• Native JSON Serialization: Pass native Python dict and list objects directly into queries; they are auto-serialized on write and auto-parsed on read.
• Atomic Transactions: Built-in context manager with nested savepoint support and automatic rollbacks on error.
• Built-in Web Studio: Zero-dependency dark-mode visual table editor and SQL runner accessible right in your browser via universal-db studio.
• Fast Package Management: Native support for uv.

---

📦 Installation

Install using uv (recommended) or pip:

# Using uv
uv add universal-local-db

# Or using pip
pip install universal-local-db

---

🚀 Quickstart

from universal_local_db import create_client

# 1. Initialize client (file path or ':memory:')
db = create_client("./app.db")

# 2. Create a table using simple schema definitions
db.schema.create_table(
    "users",
    {
        "id": "id",                                # Auto-incrementing primary key
        "name": "string",                          # TEXT NOT NULL
        "email": {"type": "string", "unique": True},# Unique string
        "age": "int?",                             # Nullable integer
        "metadata": "json",                        # Auto-serialized JSON
        "created_at": "datetime",                  # Timestamp default current
    },
)

# 3. Insert records (single or batch)
db.table("users").insert([
    {
        "name": "Alex River",
        "email": "alex@example.com",
        "age": 28,
        "metadata": {"role": "Engineer", "theme": "dark"},
    },
    {
        "name": "Sam Taylor",
        "email": "sam@example.com",
        "age": 32,
        "metadata": {"role": "Designer", "theme": "light"},
    },
]).execute()

# 4. Query with chaining filters
users = (
    db.table("users")
    .select("id, name, age, metadata")
    .gte("age", 25)
    .order("age", descending=True)
    .limit(10)
    .execute()
)

for user in users:
    print(f"User: {user['name']} | Role: {user['metadata']['role']}")

# 5. Fetch a single record
user = (
    db.table("users")
    .select("*")
    .eq("email", "alex@example.com")
    .single()
    .execute()
)
print("Single user found:", user.data["name"])

# 6. Update records
db.table("users").update({"age": 29}).eq("email", "alex@example.com").execute()

# 7. Delete records
db.table("users").delete().eq("email", "sam@example.com").execute()

# Clean up
db.close()

---

📖 Feature Guide

1. Creating Tables & Schemas

You can define tables in two ways:

A. Declarative Schema Builder (Fast & Concise)

Use built-in type shortcuts:

Shortcut	SQLite Output
"id"	INTEGER PRIMARY KEY AUTOINCREMENT
"uuid"	TEXT PRIMARY KEY
"string" / "text"	TEXT NOT NULL
"string?" / "text?"	TEXT (nullable)
"int" / "integer"	INTEGER NOT NULL
"int?" / "integer?"	INTEGER (nullable)
"float" / "real"	REAL NOT NULL
"float?" / "real?"	REAL (nullable)
"bool" / "boolean"	INTEGER NOT NULL DEFAULT 0
"json" / "json?"	TEXT (auto-parsed to dict/list)
"datetime" / "timestamp"	TIMESTAMP DEFAULT CURRENT_TIMESTAMP

Or use detailed dictionary definitions:

db.schema.create_table("products", {
    "id": "id",
    "sku": {"type": "string", "unique": True},
    "title": "string",
    "price": {"type": "float", "default": 0.0},
    "tags": "json",
    "in_stock": {"type": "bool", "default": True},
})

B. Raw SQL Queries

Run full DDL statements directly:

db.query("""
    CREATE TABLE IF NOT EXISTS orders (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        user_id INTEGER REFERENCES users(id),
        total REAL NOT NULL,
        status TEXT DEFAULT 'pending'
    );
""")

Schema Utilities

# Check if table exists
if db.schema.has_table("users"):
    print("Table exists!")

# List all tables
tables = db.schema.list_tables()

# Inspect column metadata
columns = db.schema.get_table_info("users")

# Create and drop indexes
db.schema.create_index("products", ["sku"], unique=True)
db.schema.drop_index("idx_products_sku")

# Drop a table
db.schema.drop_table("temp_data")

Modifying Existing Tables (Add, Drop, Rename Columns)

You can alter tables dynamically either in Python code or via SQL in LocalBase Studio:

In Python (db.schema):

# Add a new column
db.schema.add_column("users", "phone", "string?")
db.schema.add_column("users", "avatar", "file?")

# Rename a column
db.schema.rename_column("users", "phone", "mobile_number")

# Drop a column
db.schema.drop_column("users", "mobile_number")

# Rename the table
db.schema.rename_table("users", "members")

Via SQL (In Studio SQL Editor or db.query()):

-- Add a column
ALTER TABLE users ADD COLUMN phone TEXT;

-- Rename a column
ALTER TABLE users RENAME COLUMN phone TO mobile_number;

-- Drop a column
ALTER TABLE users DROP COLUMN mobile_number;

-- Rename a table
ALTER TABLE users RENAME TO members;

---

2. Querying & Filters (Supabase-Style)

All filters chain fluently on .table(name):

query = db.table("users").select("id, name, age")

Available Filters

• Equality / Comparison:
  • .eq("status", "active") — WHERE status = ?
  • .neq("status", "archived") — WHERE status != ?
  • .gt("age", 18) — WHERE age > ?
  • .gte("age", 18) — WHERE age >= ?
  • .lt("age", 65) — WHERE age < ?
  • .lte("age", 65) — WHERE age <= ?
• Pattern Matching:
  • .like("email", "%@domain.com") — WHERE email LIKE ?
  • .ilike("name", "alex%") — Case-insensitive LIKE
  • .contains("bio", "engineer") — Substring match
• In / Not In:
  • .in_("role", ["admin", "editor"]) — WHERE role IN (?, ?)
  • .not_in("role", ["banned"]) — WHERE role NOT IN (?)
• Null Checks:
  • .is_null("deleted_at") — WHERE deleted_at IS NULL
  • .is_not_null("email") — WHERE email IS NOT NULL

Sorting & Pagination

(
    db.table("posts")
    .select("*")
    .order("created_at", descending=True)
    .limit(20)
    .offset(40)
    .execute()
)

Single Row Helpers

• .single(): Expects exactly 1 row. Raises NotFoundError if 0 rows, or MultipleRowsError if > 1 row.
• .maybe_single(): Returns 1 row or None if 0 rows. Raises MultipleRowsError if > 1 row.

---

3. Upsert Support

Perform an insert or update in a single atomic call:

db.table("settings").upsert(
    {"key": "theme", "value": "dark"},
    on_conflict="key"
).execute()

---

4. Transactions

Group multiple write operations atomically:

with db.transaction():
    db.table("accounts").update({"balance": 400}).eq("id", 1).execute()
    db.table("accounts").update({"balance": 600}).eq("id", 2).execute()
# Automatically commits on block exit, or rolls back if an exception occurs

---

5. Working with Results (QueryResult)

The .execute() method returns a QueryResult object:

res = db.table("users").select().execute()

# Inspect metadata
print("Total rows:", res.count)
print("Data:", res.data)

# Direct iteration and indexing
for row in res:
    print(row["name"])

first_row = res[0]
first_item = res.first()

# Serialize to JSON
json_output = res.to_json(indent=2)

---

🖥️ Built-in Web Studio

LocalBase comes with a built-in zero-dependency Web Studio GUI (inspired by Supabase Studio).

Run it from the command line:

uv run universal-db studio ./app.db --port 8080 --open

Or start it directly from Python:

db = create_client("./app.db")
db.start_studio(port=8080, open_browser=True)

Features:

• 📊 Browse all tables and inspect schemas.
• 📋 Live paginated data view.
• ⚡ Raw SQL Query Editor with formatted results table.
• 🌓 Modern dark theme matching Supabase.

---

🛠️ Command-Line Interface (CLI)

The package includes the universal-db (and localbase) CLI:

# Initialize a new local database
universal-db init ./my_data.db

# Inspect tables and column schemas
universal-db tables ./my_data.db

# Run a quick SQL query and view JSON output
universal-db query ./my_data.db "SELECT * FROM users LIMIT 5"

# Launch the browser Studio GUI
universal-db studio ./my_data.db --port 8080

---

🧪 Development & Testing

This project uses uv for dependency management and testing.

# Clone the repository
git clone https://github.com/your-username/universal-local-db.git
cd universal-local-db

# Install dependencies and create virtual environment
uv sync

# Run tests
uv run pytest

# Run quickstart demo
uv run python examples/quickstart.py

---

📄 License

MIT License. Free for personal and commercial projects.