YesDB

A lightweight relational database built from scratch in Python with SQL support, B-tree storage, and a cloud Backend-as-a-Service for students.

Installation

# Local only
pip install yesdb

# With cloud support
pip install yesdb[cloud]

Quick Start

Local Mode

from yesdb import connect

db = connect("myapp.db")
db.execute("CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT, age INTEGER)")
db.execute("INSERT INTO users VALUES (NULL, 'Alice', 30)")
results = db.execute("SELECT * FROM users")
for row in results:
    print(row)
db.close()

Cloud Mode

YesDB Cloud lets you host your database on a remote server. Perfect for student projects that need a real backend.

1. Sign up and create a database

yesdb signup
# Email: student@uni.edu
# Password: ********
# -> Account created! Logged in.

yesdb init myproject
# -> Created yesdb/ folder with schema.py
# -> Database "myproject" created on cloud.

2. Define your schema

After running yesdb init, you'll have a yesdb/schema.py file in your project. Edit it:

# yesdb/schema.py
from yesdb import Table, Column, Integer, Text, Real

users = Table("users", [
    Column("id", Integer, primary_key=True),
    Column("name", Text),
    Column("email", Text),
])

products = Table("products", [
    Column("id", Integer, primary_key=True),
    Column("name", Text),
    Column("price", Real),
])

3. Push your schema to the cloud

yesdb push
# -> Connecting to myproject database...
# -> Table "users" created
# -> Table "products" created
# -> Schema synced. 2 tables pushed.

4. Use in your code

from yesdb import connect

db = connect("myproject")  # uses saved credentials automatically

db.execute("INSERT INTO users VALUES (NULL, 'Alice', 'alice@uni.edu')")
db.execute("INSERT INTO users VALUES (NULL, 'Bob', 'bob@uni.edu')")

rows = db.execute("SELECT * FROM users")
for row in rows:
    print(row)

Every response includes the database engine's internal logs (B-tree operations, SQL parsing, page allocations), so you can see exactly what's happening under the hood.

5. Use with FastAPI or Flask

# main.py
from fastapi import FastAPI
from yesdb import connect

app = FastAPI()
db = connect("myproject")

@app.get("/users")
def get_users():
    rows = db.execute("SELECT * FROM users")
    return {"users": [list(row) for row in rows]}

@app.post("/users")
def create_user(name: str, email: str):
    db.execute(f"INSERT INTO users VALUES (NULL, '{name}', '{email}')")
    return {"status": "created"}

CLI Commands

yesdb signup              # Create an account
yesdb login               # Login to existing account
yesdb init <db_name>      # Initialize a project with a cloud database
yesdb push                # Push schema.py to the cloud
yesdb databases           # List your databases
yesdb shell <db_name>     # Interactive SQL shell against a cloud database

Local CLI Shell

yesdb-local mydatabase.db

YesDB> CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT, age INTEGER);
YesDB> INSERT INTO users VALUES (NULL, 'Alice', 30);
YesDB> SELECT * FROM users;

.help          Show help
.tables        List all tables
.schema        Show table schemas
.exit          Exit shell

Features

• SQL Support: CREATE, SELECT, INSERT, UPDATE, DELETE, DROP, ALTER TABLE
• Data Types: INTEGER, TEXT, REAL, BLOB
• Query Features: WHERE, ORDER BY, LIMIT, OFFSET, DISTINCT
• B-Tree Storage: Efficient indexing and data retrieval
• No Dependencies: Pure Python implementation (local mode)
• Cloud BaaS: Host your database remotely with a single command
• Schema DSL: Define tables in Python, push to cloud
• Engine Logs: See B-tree splits, page allocations, and SQL parsing in every response
• Interactive Shell: Built-in SQL shell (local and cloud)
• Auto-increment: PRIMARY KEY auto-increment support

SQL Examples

-- Create table
CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT, email TEXT, age INTEGER)

-- Insert data
INSERT INTO users VALUES (NULL, 'Alice', 'alice@example.com', 30)
INSERT INTO users VALUES (NULL, 'Bob', 'bob@example.com', 25)

-- Query with conditions
SELECT * FROM users WHERE age > 25
SELECT name, email FROM users WHERE name = 'Alice'

-- Order and limit
SELECT * FROM users ORDER BY age DESC
SELECT * FROM users LIMIT 10 OFFSET 5

-- Update and delete
UPDATE users SET age = 31 WHERE name = 'Alice'
DELETE FROM users WHERE age < 18

-- Alter table
ALTER TABLE users ADD COLUMN country TEXT

-- Drop table
DROP TABLE users

How Cloud Mode Works

Your machine                   YesDB Cloud Server
────────────────               ──────────────────
yesdb CLI (signup/login/push)   ┌─────────────────┐
   <-> HTTPS                    │ Tailscale Funnel │
yesdb SDK (connect/execute)     │  └─ FastAPI      │
                                │     ├─ auth      │
                                │     └─ data/     │
                                │       ├─ user1/  │
                                │       │  └─ *.db │
                                │       └─ user2/  │
                                │          └─ *.db │
                                └─────────────────┘

The server can run on any machine reachable over Tailscale — a cloud VM, a home desktop, whatever's available. Tailscale Funnel handles public HTTPS without opening router ports or managing a domain/certificate. See deploy/setup.sh.

• Each user gets their own isolated account with multiple databases
• All traffic is encrypted over HTTPS
• Authentication via API key (generated at signup, saved locally)
• Database engine logs are returned with every query for full transparency

Security

Local Mode

• Path validation (blocks system file access)
• Resource limits (SQL length, record size)
• Input validation (table/column names)

Cloud Mode

• HTTPS encryption (TLS via Let's Encrypt)
• API key authentication (SHA-256 hashed, never stored in plaintext)
• Password hashing (bcrypt)
• Per-user data isolation
• Request size limits

Development

Install from Source

git clone https://github.com/AzharAhmed-bot/yes_db.git
cd yes_db
pip install -e ".[cloud]"

Run Tests

pip install pytest
pytest

Requirements

• Python 3.8+
• No external dependencies (local mode)
• requests (cloud mode, installed with pip install yesdb[cloud])

License

MIT License - see LICENSE file

Links

• PyPI: https://pypi.org/project/yesdb/
• GitHub: https://github.com/AzharAhmed-bot/yes_db
• Issues: https://github.com/AzharAhmed-bot/yes_db/issues

Version

Current version: 0.2.3 (Beta)

Changelog

v0.2.3

• Fix: SELECT ... WHERE ... (without ORDER BY/LIMIT/DISTINCT/GROUP BY) silently returned wrong data — the WHERE clause never actually resolved column values, and column projection (SELECT col FROM t) returned full rows instead of the requested columns. Both now go through the same correct execution path as every other SELECT variant.
• Fix: Error messages were being needlessly hidden in production mode — sanitize_error_message() collapsed every error down to generic strings like "Invalid input" or "An error occurred," including safe, expected ones (table already exists, unknown table, SQL syntax errors). Added a QueryError class for errors that only ever echo back names/tokens the caller already supplied — these now show their real message instead of being sanitized away.
• Improved: yesdb push output redesigned — one line per table with a real status (created / already exists, skipped / failed: reason) and an accurate summary, instead of a misleading "Table 'X' created" claim for every table regardless of outcome. Raw engine logs moved behind a new --verbose/-v flag.
• Fix: logs captured before a statement failed during push were silently dropped instead of reaching --verbose output.

v0.2.2

• Fix: yesdb push claimed every table in your local schema.py was "created" even when the server rejected some of them (e.g. re-pushing after a table already exists). The real per-table result was already shown via engine logs; the misleading summary line is now removed.

v0.2.1

• Fix: yesdb signup/yesdb login default server URL pointed at a dead Azure endpoint (no longer hosted). Now points at the live, self-hosted Cloud instance.
• Fix: Package metadata (pyproject.toml, setup.cfg) and README referenced the wrong GitHub repo name (yesdb instead of yes_db), producing broken links.

v0.2.0 (Beta)

• New: GROUP BY with aggregate functions — COUNT, COUNT(*), SUM, AVG, MIN, MAX, combinable with WHERE, ORDER BY, and LIMIT/OFFSET.
• Fix: Cloud server connection pool had a race condition — concurrent requests could open duplicate connections to the same database, or run unsynchronized queries against it, risking corruption. Access per database is now serialized.
• New: Query execution timeout on the cloud server (default 10s, configurable via YESDB_QUERY_TIMEOUT_SECONDS) so a slow query can't block every other user of that database indefinitely.
• Status: Moved from Alpha to Beta. Local Mode is beta-ready; Cloud Mode is deployed and live (self-hosted, see Cloud Mode section).

v0.1.5 (bug fixes)

• Fix: from yesdb import connect now works correctly. A yesdb compatibility package is included so the import matches the PyPI package name.
• Fix: Table.to_sql() now emits the PRIMARY KEY constraint in the generated CREATE TABLE SQL, so auto-increment works as expected when inserting NULL into a primary key column.

Note: YesDB is an educational database built from scratch to teach database internals. For production systems, consider SQLite, PostgreSQL, or MySQL.