In-Memory SQL Database

Requirements & System Scope

Functional Scope (In-Scope)

Schema Definitions: Build table schema catalogs mapping column names to strict type rules.
Type-Checked Inserts: Validate incoming row fields against table types before completing inserts.
Filtered Query Executors: Perform SELECT selections supporting WHERE filters, sorted ORDER BY constraints, and row LIMIT values.
Transactional Undo Logs: Track row mutations to support basic transaction scopes (BEGIN, COMMIT, ROLLBACK).

Explicit Boundaries (Out-of-Scope)

No High Disk File Serialization: Bypasses persisting data pages to disk or maintaining WAL logs.
No Complex SQL Joins (Hash/Merge Joins): Excludes foreign key relationships, multi-table joins, or sub-query parsers.

Class Diagram & Entity Relationships

Structural layout showing relationships between schemas, query builders, and row lists:

Polymorphic Schemas: Table stores row records and maps column definitions.
Row Documents: Rows are stored as fast primitive maps matching column names to values.

Design Patterns & SOLID Principles

Interpreter/Command Pattern (Database Commands): Decoupling database executions into distinct operation commands (Insert, Select, Delete) helps support atomic undo executions for robust rollbacks, satisfying OCP.
Single Responsibility Principle (SRP):Table manages row storage; Transaction tracks catalog states; Database manages table registers.

Core Execution Workflows

Transaction & Rollback Lifecycle

Caller initiates transaction block: beginTransaction().
Enable transactional tracking and instantiate undo logs.
Perform insert: insert("users", newRow).
1. Validate columns against target types.
2. Write insert records and original states into the transaction undo log.
If user executes commit(): clear undo log and finalize transaction status.
If user executes rollback():
1. Read undo logs in reverse order.
2. Remove inserted rows to restore original database state.

Concurrency & Thread Safety Strategy

When multiple threads insert, select, and delete rows in parallel:

Read-Write Locking: Guard table mutations with reentrant read-write locks, ensuring thread-safe reads in parallel while maintaining absolute write serialization.
Isolated Transaction Snapshots: Keep uncommitted updates isolated to transaction contexts to prevent dirty reads.

Complete Clean Code Blueprint

Clean reference designs demonstrating SQL executors in Java and Python:

// ─── JAVA BLUEPRINT ──────────────────────────────────────────────────────────
import java.util.*;
import java.util.concurrent.*;
import java.util.concurrent.locks.*;
import java.util.function.Predicate;

enum ColumnType { INTEGER, STRING }

class Column {
    private final String name;
    private final ColumnType type;
    private final boolean isPrimaryKey;

    public Column(String name, ColumnType type, boolean isPrimaryKey) {
        this.name = name;
        this.type = type;
        this.isPrimaryKey = isPrimaryKey;
    }

    public String getName() { return name; }
    public ColumnType getType() { return type; }
    public boolean isPrimaryKey() { return isPrimaryKey; }
}

class Table {
    private final String name;
    private final Map<String, Column> schema = new LinkedHashMap<>();
    private final List<Map<String, Object>> rows = new ArrayList<>();
    private final Map<String, Map<Object, List<Map<String, Object>>>> indexes = new ConcurrentHashMap<>();
    private final ReentrantReadWriteLock rwLock = new ReentrantReadWriteLock();
    private String primaryKeyColumn = null;

    public Table(String name) {
        this.name = name;
    }

    public void addColumn(String name, ColumnType type, boolean isPrimaryKey) {
        rwLock.writeLock().lock();
        try {
            Column col = new Column(name, type, isPrimaryKey);
            schema.put(name, col);
            if (isPrimaryKey) {
                this.primaryKeyColumn = name;
                // Initialize primary key index
                indexes.put(name, new ConcurrentHashMap<>());
            }
        } finally {
            rwLock.writeLock().unlock();
        }
    }

    public void insert(Map<String, Object> row) {
        rwLock.writeLock().lock();
        try {
            validateRow(row);

            // Enforce Primary Key uniqueness
            if (primaryKeyColumn != null) {
                Object pkVal = row.get(primaryKeyColumn);
                if (pkVal == null) throw new IllegalArgumentException("Primary Key value cannot be null.");
                Map<Object, List<Map<String, Object>>> pkIndex = indexes.get(primaryKeyColumn);
                if (pkIndex.containsKey(pkVal)) {
                    throw new IllegalArgumentException("Duplicate Primary Key exception for value: " + pkVal);
                }
            }

            // Perform insertion
            Map<String, Object> rowCopy = new HashMap<>(row);
            rows.add(rowCopy);

            // Update indices
            for (String indexCol : indexes.keySet()) {
                Object val = row.get(indexCol);
                if (val != null) {
                    indexes.get(indexCol).computeIfAbsent(val, k -> new CopyOnWriteArrayList<>()).add(rowCopy);
                }
            }
        } finally {
            rwLock.writeLock().unlock();
        }
    }

    public void delete(Predicate<Map<String, Object>> predicate) {
        rwLock.writeLock().lock();
        try {
            Iterator<Map<String, Object>> iterator = rows.iterator();
            while (iterator.hasNext()) {
                Map<String, Object> row = iterator.next();
                if (predicate.test(row)) {
                    iterator.remove();
                    // Update index maps
                    for (String indexCol : indexes.keySet()) {
                        Object val = row.get(indexCol);
                        if (val != null) {
                            List<Map<String, Object>> indexedRows = indexes.get(indexCol).get(val);
                            if (indexedRows != null) {
                                indexedRows.remove(row);
                                if (indexedRows.isEmpty()) {
                                    indexes.get(indexCol).remove(val);
                                }
                            }
                        }
                    }
                }
            }
        } finally {
            rwLock.writeLock().unlock();
        }
    }

    public List<Map<String, Object>> select(List<String> projection, 
                                            Predicate<Map<String, Object>> predicate,
                                            Comparator<Map<String, Object>> orderComparator, 
                                            int limit) {
        rwLock.readLock().lock();
        try {
            List<Map<String, Object>> matched = new ArrayList<>();
            for (Map<String, Object> row : rows) {
                if (predicate == null || predicate.test(row)) {
                    matched.add(row);
                }
            }

            if (orderComparator != null) {
                matched.sort(orderComparator);
            }

            List<Map<String, Object>> result = new ArrayList<>();
            int count = 0;
            for (Map<String, Object> row : matched) {
                if (limit > 0 && count >= limit) break;
                Map<String, Object> projected = new LinkedHashMap<>();
                for (String col : projection) {
                    if (schema.containsKey(col)) {
                        projected.put(col, row.get(col));
                    }
                }
                result.add(projected);
                count++;
            }
            return result;
        } finally {
            rwLock.readLock().unlock();
        }
    }

    private void validateRow(Map<String, Object> row) {
        for (Map.Entry<String, Column> entry : schema.entrySet()) {
            String colName = entry.getKey();
            Column col = entry.getValue();
            Object value = row.get(colName);

            if (value == null && col.isPrimaryKey()) {
                throw new IllegalArgumentException("Primary Key column " + colName + " cannot be null.");
            }

            if (value != null) {
                if (col.getType() == ColumnType.INTEGER && !(value instanceof Integer)) {
                    throw new IllegalArgumentException("Invalid type for column " + colName + ". Expected Integer.");
                }
                if (col.getType() == ColumnType.STRING && !(value instanceof String)) {
                    throw new IllegalArgumentException("Invalid type for column " + colName + ". Expected String.");
                }
            }
        }
    }

    public List<Map<String, Object>> getRawRows() {
        rwLock.readLock().lock();
        try {
            List<Map<String, Object>> copies = new ArrayList<>();
            for (Map<String, Object> r : rows) {
                copies.add(new HashMap<>(r));
            }
            return copies;
        } finally {
            rwLock.readLock().unlock();
        }
    }

    public void restoreRows(List<Map<String, Object>> backup) {
        rwLock.writeLock().lock();
        try {
            rows.clear();
            for (Map<String, Object> indexMap : indexes.values()) {
                indexMap.clear();
            }
            for (Map<String, Object> r : backup) {
                insert(r);
            }
        } finally {
            rwLock.writeLock().unlock();
        }
    }
}

// Transaction Session Handler
class Transaction {
    private final Database db;
    private final Map<String, List<Map<String, Object>>> snapshots = new HashMap<>();
    private boolean active = false;

    public Transaction(Database db) {
        this.db = db;
    }

    public void begin() {
        if (active) throw new IllegalStateException("Transaction already in progress.");
        active = true;
        snapshots.clear();
        for (String tableName : db.getTableNames()) {
            Table t = db.getTable(tableName);
            snapshots.put(tableName, t.getRawRows());
        }
        System.out.println("[Tx] Transaction started. Captured catalog snapshot.");
    }

    public void commit() {
        if (!active) throw new IllegalStateException("No active transaction to commit.");
        active = false;
        snapshots.clear();
        System.out.println("[Tx] Transaction committed successfully.");
    }

    public void rollback() {
        if (!active) throw new IllegalStateException("No active transaction to rollback.");
        active = false;
        for (Map.Entry<String, List<Map<String, Object>>> entry : snapshots.entrySet()) {
            Table t = db.getTable(entry.getKey());
            if (t != null) {
                t.restoreRows(entry.getValue());
            }
        }
        snapshots.clear();
        System.out.println("[Tx] Transaction rolled back. Restored original snapshots.");
    }
}

class Database {
    private final Map<String, Table> tables = new ConcurrentHashMap<>();

    public void createTable(String name) {
        tables.putIfAbsent(name.toUpperCase(), new Table(name));
    }

    public Table getTable(String name) {
        return tables.get(name.toUpperCase());
    }

    public Set<String> getTableNames() {
        return tables.keySet();
    }
}

// Complete Simulation
public class Main {
    public static void main(String[] args) {
        Database db = new Database();

        // 1. Create table and define schema
        db.createTable("Users");
        Table users = db.getTable("Users");
        users.addColumn("id", ColumnType.INTEGER, true);
        users.addColumn("name", ColumnType.STRING, false);
        users.addColumn("age", ColumnType.INTEGER, false);

        // 2. Perform safe type-checked insertions
        Map<String, Object> r1 = new HashMap<>();
        r1.put("id", 1); r1.put("name", "Alice"); r1.put("age", 25);
        users.insert(r1);

        Map<String, Object> r2 = new HashMap<>();
        r2.put("id", 2); r2.put("name", "Bob"); r2.put("age", 30);
        users.insert(r2);

        Map<String, Object> r3 = new HashMap<>();
        r3.put("id", 3); r3.put("name", "Charlie"); r3.put("age", 22);
        users.insert(r3);

        // 3. Select with WHERE filter, ORDER BY, and LIMIT
        System.out.println("--- Querying Users: age >= 24 ---");
        List<Map<String, Object>> result = users.select(
            Arrays.asList("id", "name", "age"),
            row -> (Integer) row.get("age") >= 24,
            (rowA, rowB) -> ((Integer) rowB.get("age")).compareTo((Integer) rowA.get("age")), // Sort age DESC
            10
        );

        for (Map<String, Object> row : result) {
            System.out.println(row);
        }

        // 4. Transaction simulation with ROLLBACK
        Transaction tx = new Transaction(db);
        tx.begin();

        try {
            Map<String, Object> r4 = new HashMap<>();
            r4.put("id", 4); r4.put("name", "David"); r4.put("age", 28);
            users.insert(r4);

            System.out.println("Inserted row inside transaction.");
            System.out.println("Current count: " + users.getRawRows().size());

            // Force a rollback to test logic
            throw new RuntimeException("Simulating checkout cancellation");
        } catch (Exception e) {
            System.out.println("Encountered exception: " + e.getMessage() + ". Performing rollback.");
            tx.rollback();
        }

        System.out.println("Count after rollback: " + users.getRawRows().size());
    }
}

Help Us Improve

How helpful was this walkthrough?

Click a star to rate. We actively use this feedback to refine and update our system design content.

Placeholder

Discussion

Share your thoughts, ask questions, or help others.

Loading comments...

✦Requirements & System Scope

Functional Scope (In-Scope)

Explicit Boundaries (Out-of-Scope)

◫Class Diagram & Entity Relationships

⬡Design Patterns & SOLID Principles

{ }Core Execution Workflows