PesaPal Developer Challenge 2026

This challenge involves creating a custom database and a CRUD application that uses the database. Part 1 contains the database while Part 2 contains the CRUD app.

Part 1. The Database (nanoDB) - Summary of its working

nanoDB is built using Python. I didn't choose faster lower-level languages like C/C++ because of Python's simplicity in creating scripts. I based the storage logic on files, more specifically JSON files because of their ease of parsing.

It's basic and supports these statements in their very basic form:

• CREATE
• INSERT
• UPDATE
• DELETE

Four files were used in creating the database:

• repl.py

This is for capturing the user input from the CLI/terminal using REPL.

• parser.py

After the user input is captured, it is parsed primarily by breaking the inputs into tokens for processing the queries. For example, to get the table name from the statement: SELECT * FROM users;, the parser uses something like token[3].

It has six dedicated functions for creating, inserting, selecting, joins, updating, and deleting. Each of them returns abstract syntax trees (ASTs) that will be used by the engine in execution.

• engine.py

This is where the heavy load of the database work is broken down bit by bit. It uses the trees returned by the parser to read data from the JSON storage files with the help of the last file, storage.py.

Example queries

This section is used to demonstrate the database in action.

To run it, you first need a Python interpreter. Clone the project and cd into it like so:

git clone https://github.com/Agusioma/nanoDB.git
cd nanoDB

Then run the repl file.

python repl.py

You should see something like:

The Database is ready for use!
Type SQL commands or type 'exit' to return to close.

----------------------------------------------
$nanoDB>

1. Creating tables

Run this command to create a table named users.

CREATE TABLE users (id INT PRIMARY KEY, email TEXT UNIQUE, name TEXT);

Run this too to create another table named, orders.

CREATE TABLE orders (id INT PRIMARY KEY, user_id INT, amount INT);

You should see OK after each command.

2. Inserting records

Run these commands (line by line) to insert some records into the tables.

INSERT INTO users VALUES (1, 'tyron@tyron.com', 'Tyron');
INSERT INTO users VALUES (2, 'henry@henry.com', 'Henry');
INSERT INTO users VALUES (3, 'rizz@rizz.com', 'Rizz');
INSERT INTO users VALUES (4, 'Leakey@leaks.com', 'Leakey');

INSERT INTO orders VALUES (1, 1, 500);
INSERT INTO orders VALUES (2, 1, 300);
INSERT INTO orders VALUES (3, 4, 560);
INSERT INTO orders VALUES (4, 3, 3090);
INSERT INTO orders VALUES (5, 1, 5090);
INSERT INTO orders VALUES (6, 2, 3020);

Note how it fails if you try inserting records with duplicate IDs or email addresses.

$nanoDB> INSERT INTO users VALUES (4, 'Leakey@leaks.com', 'Leakey');
Error: Duplicate value for id
$nanoDB> INSERT INTO users VALUES (5, 'Leakey@leaks.com', 'Leakey');                
Error: Duplicate value for email

In the root directory, you will find a folder named db that is autogenerated by the storage.py file upon running the commands above. The db folder tree structure is shown below:

.
├── orders
│   ├── data.jsonl
│   ├── indexes
│   │   └── id.idx
│   └── schema.json
└── users
    ├── data.jsonl
    ├── indexes
    │   ├── email.idx
    │   └── id.idx
    └── schema.json

It has two folders housing the orders and the users tables. Under each table folder, you will find:

• A JSON list file containing the records
• A JSON file containing the table's schema
• An index file under a folder named indexes that contains the table's indices for ease of mapping.

3. Reading records

You can read the records by using SELECT statements.

SELECT * FROM orders;

And it returns:

[
{'id': '1', 'user_id': '1', 'amount': '500'},
{'id': '2', 'user_id': '1', 'amount': '300'},
{'id': '3', 'user_id': '4', 'amount': '560'},
{'id': '4', 'user_id': '3', 'amount': '3090'},
{'id': '5', 'user_id': '1', 'amount': '5090'},
{'id': '6', 'user_id': '2', 'amount': '3020'}
]

For joins, you can create an INNER JOIN using:

SELECT * FROM users INNER JOIN orders ON id = user_id;

Returning:

[
{'id': '1', 'email': 'tyron@tyron.com', 'name': 'Tyron', 'user_id': '1', 'amount': '500'},
{'id': '2', 'email': 'tyron@tyron.com', 'name': 'Tyron', 'user_id': '1', 'amount': '300'},
{'id': '5', 'email': 'tyron@tyron.com', 'name': 'Tyron', 'user_id': '1', 'amount': '5090'}, 
{'id': '6', 'email': 'henry@henry.com', 'name': 'Henry', 'user_id': '2', 'amount': '3020'}, 
{'id': '4', 'email': 'rizz@rizz.com', 'name': 'Rizz', 'user_id': '3', 'amount': '3090'}, 
{'id': '3', 'email': 'Leakey@leaks.com', 'name': 'Leakey', 'user_id': '4', 'amount': '560'}
]

For left, full outer, and right joins, you can use the following queries line by line.

SELECT * FROM users LEFT JOIN orders ON id = user_id;

SELECT * FROM users RIGHT JOIN orders ON id = user_id;

SELECT * FROM users FULL OUTER JOIN orders ON id = user_id;

4. Updates

nanoDB currently supports updating one column at a time. E.g:

UPDATE users SET email="leakey@leakeyworks.com" WHERE id = 4;

That will return:

1 row(s) updated

Confirm the update by selecting the records.

[
{'id': '1', 'email': 'tyron@tyron.com', 'name': 'Tyron'},
{'id': '2', 'email': 'henry@henry.com', 'name': 'Henry'},
{'id': '3', 'email': 'rizz@rizz.com', 'name': 'Rizz'},
{'id': '4', 'email': '"leakey@leakeyworks.com"', 'name': 'Leakey'}
]

4. Deletions

Finally, you can delete the records using the DELETE command.

DELETE FROM users WHERE id = 3;

This will return:

1 row(s) deleted

Part 2: The CRUD App

Head on to the CRUD app repository using this link.