Implementing a Simple Relational Database from Scratch in Python

Relational databases are at the core of most modern applications, but have you ever wondered how they actually work under the hood? In this post, we will build a very basic relational database system in Python. This is meant for educational purposes and will cover:

• Storing tables and records in memory;
• Implementing basic SQL-like operations (SELECT, INSERT, WHERE);
• Understanding indexing and query efficiency.

By the end, you will have a minimal database engine you can extend or experiment with.

1. Representing Tables and Records

In relational databases, data is stored in tables composed of rows (records) and columns (fields). We can represent a table as a list of dictionaries, where each dictionary is a row.

# Define a simple table for Users
users_table = []

# Each row is a dictionary mapping column names to values
users_table.append({"id": 1, "name": "Alice", "email": "alice@example.com"})
users_table.append({"id": 2, "name": "Bob", "email": "bob@example.com"})

print(users_table)

2. Implementing Basic Insert Operation

We can create a function to insert new rows into our table dynamically:

def insert(table, row):
    table.append(row)

# Insert a new user
insert(users_table, {"id": 3, "name": "Charlie", "email": "charlie@example.com"})
print(users_table)

3. Querying Data: SELECT and WHERE

A relational database becomes powerful when you can query data efficiently. Let's implement a simple SELECT function with optional filtering:

def select(table, columns=None, where=None):
    results = []
    for row in table:
        if where is None or where(row):
            if columns:
                filtered_row = {col: row[col] for col in columns}
                results.append(filtered_row)
            else:
                results.append(row)
    return results

# Example: select all users
print(select(users_table))

# Example: select only names
print(select(users_table, columns=["name"]))

# Example: select where id > 1
print(select(users_table, where=lambda r: r["id"] > 1))

4. Simple Indexing for Faster Lookup

Indexes allow databases to quickly find records without scanning the entire table. We'll create a very basic index on the id field:

# Build a simple index
user_index = {row["id"]: row for row in users_table}

# Lookup by id
user = user_index.get(2)
print(user)

This demonstrates how hashing can speed up lookups dramatically compared to iterating through the whole table.

5. Putting It Together: Mini Database Class

We can encapsulate all functionality in a class to make it more structured:

class MiniDatabase:
    def __init__(self):
        self.tables = {}

    def create_table(self, name):
        self.tables[name] = []

    def insert(self, table_name, row):
        self.tables[table_name].append(row)

    def select(self, table_name, columns=None, where=None):
        table = self.tables[table_name]
        results = []
        for row in table:
            if where is None or where(row):
                if columns:
                    filtered_row = {col: row[col] for col in columns}
                    results.append(filtered_row)
                else:
                    results.append(row)
        return results

# Usage
db = MiniDatabase()
db.create_table("users")
db.insert("users", {"id": 1, "name": "Alice"})
db.insert("users", {"id": 2, "name": "Bob"})
print(db.select("users", columns=["name"]))

6. What’s Next?

This is a very simplified database, but it illustrates the main concepts behind relational databases:

• Tables and rows;
• Querying with filters;
• Indexing for faster lookup.

Next steps you could explore:

• Supporting multiple tables with foreign key relationships;
• Implementing basic joins;
• Storing tables on disk for persistence;
• Adding query optimization techniques.

Databases power nearly every modern application. By understanding these internal mechanisms, you gain a deeper appreciation for SQL engines like PostgreSQL, MySQL, and SQLite.