OpenSQL - Educational Custom Relational Database

A minimal custom relational database implementation in Python for learning database internals.

Features

• CREATE DATABASE - Database creation and management
• CREATE TABLE - Table creation with columns, constraints, and indexes
• UUIDv7 - Timestamp-ordered UUID generation for efficient indexing
• INSERT - Row insertion with auto-generated UUIDs
• SELECT - Query data with column aliases (AS)
• LEFT JOIN - Left outer join between tables
• RIGHT JOIN - Right outer join between tables

Architecture

• Tables stored as JSON files (one file per table)
• Each table contains schema (columns, indexes) and data (rows)
• File-based storage in database directories

Usage

from custom_rdb import CustomRDB

# Initialize database
db = CustomRDB("./data")
db.create_database("demo_db")
db.use_database("demo_db")

# Create tables
db.create_table("""
    CREATE TABLE users (
        id BINARY(16) PRIMARY KEY DEFAULT (UUID_TO_BIN(UUID())),
        name VARCHAR(100)
    )
""")

# Insert data
db.insert("users", [db.uuidv7(), "Alice"])

# Query data
results = db.select("users")
print(results)

# Joins
joined = db.left_join("users", "orders", "id", "user_id")

Files

• custom_rdb.py - Core database engine
• create_table.py - Table creation parser
• sql_functions.py - SQLite wrapper functions
• sql_demo.sql - SQL examples

Educational Purpose

This project demonstrates:

• SQL parsing and execution
• Table storage and indexing
• Join algorithms
• UUID-based primary keys
• Database file organization

Limitations

• No transactions or ACID guarantees
• Limited SQL syntax support
• No query optimization
• JSON-based storage (not production-ready)

License

Educational use only.