YesDB

Beta — A lightweight relational database built from scratch in Python, with SQL support, B-tree storage, and a cloud Backend-as-a-Service. Ready to use for real projects; still under active development, so expect the occasional rough edge.

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/DROP TABLE, CREATE/DROP INDEX, SELECT, INSERT, UPDATE, DELETE, ALTER TABLE
• Data Types: INTEGER, TEXT, REAL, BLOB
• Query Features: WHERE, ORDER BY, LIMIT, OFFSET, DISTINCT, INNER/LEFT JOIN
• B-Tree Storage: Efficient indexing and data retrieval
• Secondary Indexes: CREATE INDEX accelerates equality lookups on non-primary-key columns
• Transactions: BEGIN / COMMIT / ROLLBACK for INSERT/UPDATE/DELETE
• Foreign Keys: REFERENCES constraints, enforced on INSERT/UPDATE, with RESTRICT on DELETE
• 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)

-- Foreign keys (enforced on INSERT/UPDATE; RESTRICT on DELETE of a referenced row)
CREATE TABLE orders (
  id INTEGER PRIMARY KEY,
  user_id INTEGER REFERENCES users(id),
  total REAL
)

-- 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

-- Joins (INNER by default, or LEFT [OUTER])
SELECT users.name, orders.total FROM orders
  JOIN users ON orders.user_id = users.id
SELECT users.name, orders.total FROM users
  LEFT JOIN orders ON users.id = orders.user_id

-- 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

-- Secondary indexes (speeds up equality WHERE lookups)
CREATE INDEX idx_users_email ON users (email)
DROP INDEX idx_users_email

-- Drop table
DROP TABLE users

-- Transactions (INSERT/UPDATE/DELETE only — DDL isn't allowed mid-transaction)
BEGIN
INSERT INTO users VALUES (NULL, 'Dana', 'dana@example.com', 28)
ROLLBACK  -- or COMMIT

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.6.1 (Beta)

Changelog

v0.6.1

• Fix (critical): WHERE ... AND/OR ... silently matched every row instead of applying the condition — affected SELECT, UPDATE, and DELETE alike (e.g. DELETE FROM t WHERE a = 1 AND b = 2 deleted the entire table). _evaluate_where now recursively evaluates AND/OR instead of only handling a single comparison.
• Fix (critical): Once a table's B-tree grew past one page, INSERT/UPDATE/DELETE could misroute a key that exactly matched an internal split point — silently failing UPDATE/DELETE (reported success, changed nothing) and allowing duplicate PRIMARY KEY rows on INSERT. Internal-node routing in chidb/btree.py now matches search()'s (already-correct) equality-aware logic.
• Fix: INSERT of a REAL value was silently truncated to an integer (no FLOAT opcode existed in the bytecode VM). Added one; 10.5 no longer becomes 10.
• Fix: Negative number literals (-5, -3.25) couldn't be parsed at all, in INSERT, UPDATE ... SET, or WHERE.
• Fix: ORDER BY on a column not included in the SELECT list sorted by the wrong values entirely (it resolved the column's position against the full schema but applied it to the already-projected row). SELECT name FROM t ORDER BY age now actually orders by age.
• Improved: The DBM bytecode VM's DELETE opcode was a no-op stub (didn't call btree.delete()) — fixed, and documented that it and several other codegen paths (generate_select/generate_update/generate_delete/generate_create_table) aren't wired into real query execution today (every statement except INSERT is handled directly in api.py), so nobody mistakes them for working code.

v0.6.0

• New: Foreign key constraints — column TYPE REFERENCES table(column) in CREATE TABLE (and ALTER TABLE ADD COLUMN). Enforced on INSERT and UPDATE (a non-NULL value must match an existing row in the referenced table/column); DELETE from a referenced row is rejected (RESTRICT) while any other table still references it. CREATE TABLE validates the referenced table/column exist up front, rather than failing only on first insert.

v0.5.0

• New: BEGIN / COMMIT / ROLLBACK transactions for INSERT/UPDATE/DELETE. Implemented as an in-memory snapshot-and-restore of the pager's page cache (where writes already live until flush()), so ROLLBACK is a pure in-memory operation — no partial writes ever reach disk. DDL (CREATE/DROP TABLE, ALTER TABLE, CREATE/DROP INDEX) is not allowed inside an active transaction, since it flushes to disk immediately and an in-memory rollback couldn't undo that. Closing a database with an active transaction rolls it back automatically.

v0.4.0

• New: CREATE INDEX name ON table (column) / DROP INDEX name — secondary indexes that accelerate column = value WHERE lookups from a full table scan down to a direct lookup. Implemented as an in-memory index (the hand-rolled B-tree only supports integer keys), automatically rebuilt when a database is reopened and kept in sync across INSERT/UPDATE/DELETE. DROP TABLE cascades to drop any indexes defined on it.

v0.3.0

• New: JOIN support — INNER JOIN (default) and LEFT [OUTER] JOIN, chainable across multiple tables, with table.column qualified references in SELECT, ON, WHERE, and ORDER BY. Unqualified column names resolve automatically when unambiguous across the joined tables, and raise a clear error when they're not. Not yet supported together with GROUP BY/aggregate functions.

v0.2.5

• New: A real first-run welcome — bare yesdb and yesdb signup now show a block-letter YESDB logo alongside the version/Beta tagline, plus a "New here? Get started" quickstart (signup → init → push, with a pointer to local mode) instead of just a bare help dump.

v0.2.4

• Changed: Dropped "educational" framing everywhere (PyPI classifiers/keywords, package docstring, README, SECURITY.md) in favor of a clear Beta positioning — YesDB is usable for real projects now, not just a teaching toy.
• New: Proper intro banners — yesdb signup, yesdb-local <db>, and yesdb shell <db> now show the product name, version, and Beta status on first touch. signup also points you to the next step (yesdb init).
• Fix: The Cloud server's /docs page showed a hardcoded, never-updated 0.1.0 API version — now reads the real package version.
• Removed a stale, unreferenced duplicate README (readME.md) left over from a very early pre-rename version of the project.

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 in active beta. It's built from scratch in Python and usable today for real projects and prototypes — you'll also see exactly how a database works internally along the way. Report issues on GitHub; for workloads with strict production/security requirements, an established database (PostgreSQL, MySQL, SQLite) is still the safer choice for now.