gregory RDBMS: Custom Relational Database Management System

live deployed website available at: https://rdbms-test-by-gregory.netlify.app/

A relational database management system (RDBMS) built from scratch in TypeScript. This project demonstrates core database concepts including SQL parsing, query execution, indexing, constraints, and a complete web-based interface.

Challenge Requirements

This project fulfills all requirements from the RDBMS implementation challenge:

✅ Table Declaration: Support for multiple data types (INT, FLOAT, TEXT, BOOLEAN)
✅ CRUD Operations: Full Create, Read, Update, Delete functionality
✅ Indexing: Automatic indexing on primary and unique keys with O(1) lookups
✅ Primary & Unique Keys: Full constraint enforcement with validation
✅ JOIN Operations: INNER JOIN support across multiple tables
✅ SQL Interface: Custom SQL parser supporting standard DDL/DML statements
✅ Interactive REPL: Command-line interface for direct database operations
✅ Web Application: React-based UI demonstrating CRUD operations on the custom RDBMS

Overview

it includes:

Custom SQL Parser - Parses SQL-like syntax without external parser libraries
Query Execution Engine - Executes parsed queries with full constraint validation
Indexing System - Hash-based indexes for optimized lookup performance
Storage Layer - File-based persistence using JSON
Interactive Interfaces - Both web UI and command-line REPL
Web Application - React frontend demonstrating real-world usage

Core Features

Database Engine

Feature	Implementation
SQL Parsing	Custom tokenizer and parser (no external parser libs)
Data Types	INT, FLOAT, TEXT, BOOLEAN
Constraints	PRIMARY KEY, UNIQUE, NOT NULL
Indexes	Automatic hash-based indexing on constrained columns
Queries	SELECT, INSERT, UPDATE, DELETE, DROP TABLE
Joins	INNER JOIN across multiple tables
Storage	File-based with JSON serialization

Web Interface

SQL Console: Execute queries with syntax highlighting and results
Table Explorer: Browse schema, columns, constraints, and indexes
CRUD Panel: Visual management of table data
Query Log: Real-time performance metrics and execution history
Green-600 Theme: Clean, professional design with good contrast

REPL Console

Interactive command-line interface
Query execution with formatted output
Schema introspection (DESCRIBE, SHOW TABLES)
Help system and command history

Get started

Installation

npm install

Local Development

Terminal 1 - Start Backend Server:

npm run dev:server
# Starts on http://localhost:3001

Terminal 2 - Start Frontend:

npm run dev
# Opens http://localhost:5173

Interactive REPL Mode

npm run repl

Try these commands:

mydb> CREATE TABLE users (id INT PRIMARY KEY, name TEXT, email TEXT UNIQUE);
mydb> INSERT INTO users VALUES (1, 'kipngeno gregory', 'kipngenogregory@gmail.com');
mydb> SELECT * FROM users;
mydb> describe users
mydb> show tables

SQL Syntax Examples

Creating Tables

CREATE TABLE users (
  id INT PRIMARY KEY,
  name TEXT NOT NULL,
  email TEXT UNIQUE,
  active BOOLEAN
);

CREATE TABLE orders (
  id INT PRIMARY KEY,
  user_id INT NOT NULL,
  amount FLOAT NOT NULL,
  status TEXT
);

CRUD Operations

-- Insert
INSERT INTO users VALUES (1, 'Kipngeno', 'kipngenogregory@gmail.com', true);
INSERT INTO users VALUES (2, 'Maron', 'maron@gmail.com', true);

-- Select
SELECT * FROM users;
SELECT name, email FROM users WHERE id = 1;
SELECT * FROM users WHERE email = 'kipngenogregory@gmail.com';

-- Update
UPDATE users SET name = 'kipngeno' WHERE id = 1;
UPDATE orders SET status = 'shipped' WHERE id = 1;

-- Delete
DELETE FROM users WHERE id = 2;
DELETE FROM orders WHERE status = 'cancelled';

Advanced Queries

-- JOINs
SELECT users.name, orders.amount, orders.status
FROM users
JOIN orders ON users.id = orders.user_id;

-- Schema introspection
SELECT * FROM users WHERE active = true;

Architecture

Backend Structure

server/
├── database.ts       # Main query executor and table manager
├── table.ts          # Table storage, constraints, and indexing
├── storage.ts        # File-based persistence layer
├── parser.ts         # SQL parser and tokenizer
├── repl.ts           # Command-line interface
├── index.ts          # Express API server
└── types.ts          # TypeScript type definitions

Key Implementation Details:

Parser (server/parser.ts): Custom regex-based SQL parser that tokenizes and parses SQL statements into an AST
Table (server/table.ts): Manages rows, maintains indexes, validates constraints, enforces primary/unique keys
Storage (server/storage.ts): Persists tables to JSON files in /data directory with index files
Indexes (server/table.ts): Hash maps for O(1) lookups on primary keys and unique columns
API (server/index.ts): Express REST endpoints for query execution and schema introspection

Frontend Structure

src/
├── components/
│   ├── SQLConsole.tsx      # Query editor and executor
│   ├── TableExplorer.tsx   # Schema browser
│   ├── CRUDPanel.tsx       # Data management UI
│   └── QueryLog.tsx        # Performance metrics
├── App.tsx                 # Main application layout
├── api.ts                  # Backend API client
└── types.ts                # Frontend type definitions

Performance Features

Index Usage

Queries automatically use indexes when available:

Primary Key lookups: SELECT * FROM users WHERE id = 1 → O(1) with index
Unique column lookups: SELECT * FROM users WHERE email = 'alice@example.com' → O(1) with index
Full table scans: Queries without indexed columns scan all rows

Query Metrics

The system tracks and displays:

Execution Time: Millisecond precision
Rows Scanned: Total rows evaluated
Index Used: Shows which index was applied (if any)

Example output:

✓ Query executed successfully
⏱  Execution time: 2ms
📊 Rows scanned: 1
🔍 Index used: users_email_idx

Data Types & Constraints

Supported Data Types

INT - Integer numbers (-2^31 to 2^31-1)
FLOAT - Floating-point decimals
TEXT - String values
BOOLEAN - True/false values

Constraints

PRIMARY KEY - Unique identifier (automatically indexed)
UNIQUE - Column uniqueness constraint (automatically indexed)
NOT NULL - Prevents NULL values

Type validation occurs on:

INSERT: Validates types and constraints before adding rows
UPDATE: Validates new values before modification
DELETE: Removes entries while maintaining index integrity

Deployment

Backend Deployment (Render)

Push your code to GitHub

git add .
git commit -m "Deploy MiniRDBMS backend"
git push origin main

Create a Render Account
- Visit https://render.com and sign up
Create a New Web Service
- Click "New +" → "Web Service"
- Connect your GitHub repository
- Configure the service:
  - Name: mini-rdbms-api
  - Environment: Node
  - Build Command: npm install && npm run build:server
  - Start Command: npm run dev:server
  - Node Version: 18
Set Environment Variables (if needed)
- PORT: 3001
Deploy
- Click "Create Web Service"
- Render will automatically deploy on every push

my api available at: https://rdbms-1.onrender.com

Frontend Deployment (Netlify)

Build the Frontend
```
npm run build
```
Create a Netlify Account
- Visit https://netlify.com and sign up with GitHub
Connect Your Repository
- Click "Add new site" → "Import an existing project"
- Select your GitHub repository
Configure Build Settings
- Build command: npm run build
- Publish directory: dist
Set Environment Variables
- Add a VITE_API_URL variable pointing to your Render backend:
```
https://your-service-name.onrender.com
```
Update API Configuration
- Modify src/api.ts to use the environment variable:
```
const API_URL = import.meta.env.VITE_API_URL || '/api';
```
Deploy
- Netlify automatically deploys on every push
- Your site will be available at a .netlify.app domain

Full Stack Architecture

┌─────────────────────────────────────────────────────────────┐
│                     Netlify (Frontend)                      │
│  React + TypeScript + Tailwind                              │
│  https://your-app.netlify.app                               │
└────────────────────┬────────────────────────────────────────┘
                     │ HTTP/REST
                     │
┌────────────────────▼────────────────────────────────────────┐
│                   Render (Backend)                          │
│  Express + Node.js + TypeScript                             │
│  https://your-service.onrender.com/api/query                │
└────────────────────┬────────────────────────────────────────┘
                     │
┌────────────────────▼────────────────────────────────────────┐
│               File-Based Database Storage                   │
│  /data/*.table.json + /data/indexes/                        │
└─────────────────────────────────────────────────────────────┘

Project Structure

mini-rdbms/
├── server/                     # Backend (Node.js + Express)
│   ├── database.ts            # Query execution engine
│   ├── table.ts               # Table and index management
│   ├── storage.ts             # File persistence
│   ├── parser.ts              # SQL parser
│   ├── repl.ts                # REPL interface
│   ├── index.ts               # API endpoints
│   └── types.ts               # Type definitions
├── src/                        # Frontend (React)
│   ├── components/
│   │   ├── SQLConsole.tsx      # Query editor
│   │   ├── TableExplorer.tsx   # Schema browser
│   │   ├── CRUDPanel.tsx       # Data management
│   │   └── QueryLog.tsx        # Query history
│   ├── App.tsx                # Main application
│   ├── api.ts                 # API client
│   ├── types.ts               # Type definitions
│   └── main.tsx
├── data/                       # Database storage (created at runtime)
│   ├── users.table.json
│   ├── orders.table.json
│   └── indexes/
├── package.json
├── tsconfig.json
├── vite.config.ts
└── README.md

Implementation Details

Custom SQL Parser

The parser handles:

Tokenization: Splits SQL into keywords, identifiers, values
Syntax Validation: Checks for required clauses and proper structure
Type Coercion: Converts string values to appropriate types
Quote Handling: Supports both single and double quoted strings

Example parsing flow:

SQL Input: "SELECT name FROM users WHERE id = 1"
    ↓
Tokenizer: [SELECT, name, FROM, users, WHERE, id, =, 1]
    ↓
Parser: {type: 'SELECT', selectColumns: ['name'], tableName: 'users', whereClause: {...}}
    ↓
Executor: Query execution with index optimization

Constraint Enforcement

All constraints are validated at write time:

// Primary key uniqueness check
if (existingRows.length > 0) {
  return { success: false, error: `Duplicate primary key` };
}

// Unique key validation
for (const uniqueKey of schema.uniqueKeys) {
  const existingRows = findByIndex(uniqueKey, value);
  if (existingRows.length > 0) {
    return { success: false, error: `Duplicate unique key` };
  }
}

// NOT NULL validation
if (col.notNull && value === null) {
  return { success: false, error: `Column cannot be null` };
}

Index Management

Indexes are automatically created and maintained:

// Auto-index primary key
if (schema.primaryKey) {
  createIndex(schema.primaryKey);
}

// Auto-index unique columns
schema.uniqueKeys.forEach(colName => {
  createIndex(colName);
});

// Maintain during updates/deletes
updateIndexes(rowId, oldRow, newRow);

Limitations & Design Decisions

Current Limitations

Single WHERE condition (no AND/OR logic)
INNER JOIN only (no LEFT/RIGHT/FULL OUTER)
No transactions or ACID compliance
File-based storage (not suitable for production scale)
No concurrent access control
In-memory indexes rebuilt on startup

Intentional Design Decisions

Custom Parser: No external parser library to demonstrate deep understanding
File-Based Storage: Simple persistence for demonstration
Hash Indexes: Simple O(1) lookups sufficient for core requirements
Synchronous API: Simplified implementation for clarity

Future Enhancements

Advanced Querying
- Complex WHERE clauses with AND/OR operators
- ORDER BY and GROUP BY support
- LIMIT and OFFSET clauses
- Aggregate functions (COUNT, SUM, AVG, MIN, MAX)
Join Operations
- LEFT JOIN, RIGHT JOIN, FULL OUTER JOIN
- Multiple join conditions
Performance
- B-Tree indexes for range queries
- Query optimization and execution planning
- Connection pooling
Advanced Features
- Transactions with ACID guarantees
- Multi-user concurrency control
- Views and stored procedures
- Foreign key constraints
Production Readiness
- Database migration system
- Backup and recovery
- Query logging and monitoring
- Authentication and authorization

Technical Stack

Component	Technology	Version
Backend Runtime	Node.js	Latest
Backend Framework	Express	^4.18.2
Language	TypeScript	^5.5.3
Frontend	React	^18.3.1
Build Tool	Vite	^5.4.2
Styling	Tailwind CSS	^3.4.1
Icons	Lucide React	^0.344.0
Database	Custom JSON-based	Built from scratch

Name		Name	Last commit message	Last commit date
Latest commit History 20 Commits
data		data
dist-server		dist-server
server		server
src		src
.gitignore		.gitignore
QUICKSTART.md		QUICKSTART.md
RD.jpeg		RD.jpeg
RD2.jpeg		RD2.jpeg
RD3.jpeg		RD3.jpeg
README.md		README.md
demo-queries.sql		demo-queries.sql
eslint.config.js		eslint.config.js
index.html		index.html
package-lock.json		package-lock.json
package.json		package.json
postcss.config.js		postcss.config.js
tailwind.config.js		tailwind.config.js
tsconfig.app.json		tsconfig.app.json
tsconfig.json		tsconfig.json
tsconfig.node.json		tsconfig.node.json
tsconfig.server.json		tsconfig.server.json
vite.config.ts		vite.config.ts

gregory-bot/rdbms

Folders and files

Latest commit

History

Repository files navigation

gregory RDBMS: Custom Relational Database Management System

Challenge Requirements

Overview

Core Features

Database Engine

Web Interface

REPL Console

Get started

Installation

Local Development

Interactive REPL Mode

SQL Syntax Examples

Creating Tables

CRUD Operations

Advanced Queries

Architecture

Backend Structure

Frontend Structure

Performance Features

Index Usage

Query Metrics

Data Types & Constraints

Supported Data Types

Constraints

Deployment

Backend Deployment (Render)

Frontend Deployment (Netlify)

Full Stack Architecture

Project Structure

Implementation Details

Custom SQL Parser

Constraint Enforcement

Index Management

Limitations & Design Decisions

Current Limitations

Intentional Design Decisions

Future Enhancements

Technical Stack

About

Resources

Uh oh!

Stars

Watchers

Forks

Releases

Packages 0

Uh oh!

Contributors

Uh oh!

Languages

Packages