SQKal - A Simple SQL Database Engine

SQKal is an educational SQL database engine built from scratch in TypeScript, running on Node.js. It is designed to demonstrate the core principles of database architecture with zero external dependencies.

Core Features

SQKal implements several key components of a traditional database management system, making it a valuable tool for learning and experimentation.

Strengths & Architecture

• Layered Design: The engine features a clean separation between the storage, catalog, and query processing layers.

• Efficient Storage Engine:

  • Page-Based: The database file is organized into fixed-size pages for efficient I/O.
  • Data Compression: Data pages are compressed on disk using Brotli to minimize file size, with a PageDirectory to manage variable-sized pages.
  • Slotted Pages: Implements a slotted page structure, allowing for efficient row insertion, deletion, and space management without fragmentation.

• Intelligent Buffer Management:

  • A BufferPoolManager caches frequently accessed pages in memory to minimize disk reads.
  • Implements the LRU (Least Recently Used) page replacement policy to intelligently manage the cache.
  • Uses a pinning mechanism to prevent critical, in-use pages from being evicted.

• Advanced Query Processor:

  • Features a hand-written, predictive parser with a stateful scanner that can be guided by the parser, enabling robust and extensible SQL parsing.
  • Supports CREATE TABLE, INSERT (with multi-row syntax), SELECT, UPDATE, and DELETE commands.
  • Includes a powerful WHERE clause that supports AND/OR conjunctions, parentheses for grouping, and a full range of operators (=, <>, <, >, <=, >=, LIKE, IS NULL, IS NOT NULL).
  • Supports ORDER BY and LIMIT/OFFSET for SELECT statements.

• Self-Contained Metadata:

  • Table schemas, including NOT NULL constraints, are stored in a compact, binary format within the database itself in a dedicated Catalog.

Usage Example

import { Database } from './dist/classes/database.js';
import { join } from 'node:path';

// Get a singleton instance of the database
const db = Database.getInstance(join(import.meta.dirname, '../db/main.db'));
await db.open();

// Create a table with constraints
await db.exec`CREATE TABLE users (id INT, name VARCHAR NOT NULL)`;

// Insert multiple rows at once
await db.exec`INSERT INTO users VALUES (1, 'Alice'), (2, 'Bob'), (3, 'Charlie')`;

// Execute a complex query
const users = await db.exec`SELECT name FROM users WHERE (id > 1 AND name LIKE 'B%') OR name = 'Charlie' ORDER BY name DESC`;

console.log(users); // Output: [{ name: 'Charlie' }, { name: 'Bob' }]

await db.close();

⚠️ Not for Production Use

This project is an educational tool and is not intended for production environments. It lacks many features of a production-grade database, such as concurrency control, transactions, advanced indexing, and comprehensive error handling.

License

MIT License - Free for educational use.

Disclaimer

This README.md file was generated by an AI assistant to provide a comprehensive overview of the project. While it aims to be accurate, the underlying source code was written by a human developer and stands as the ground truth.

Acknowledgments

Built using core Node.js modules only.