SQLight

A lightweight SQLite clone with a modern web interface and CLI

Features • Installation • Usage • Documentation • Contributing • License

📋 Overview

SQLight is a lightweight SQLite clone implemented in Go that provides a simple yet functional database system with persistent storage. It features both a modern web interface and a traditional command-line interface, making it versatile for different use cases.

This project demonstrates core database concepts including SQL parsing, query execution, transaction management, and persistent storage while maintaining a clean, user-friendly interface.

✨ Features

🖥️ Dual Interface Support

Modern Web Interface with real-time query execution and interactive table browsing
Traditional Command Line Interface for script-based and terminal operations

📊 SQL Command Support

CREATE TABLE - Create tables with specified columns and data types
INSERT INTO - Insert records into tables
SELECT - Query records with support for WHERE clauses and column selection
DELETE - Remove records with WHERE clause filtering
More commands coming soon!

🔄 Data Types

INTEGER - Whole numbers
TEXT - String values
More types coming soon!

🛠️ Advanced Features

Transaction Support for atomic operations
Case-insensitive SQL command and table/column name handling
WHERE Clause Support with multiple conditions using AND
String Value Handling with support for both single and double quotes
Persistent Storage using JSON
Data Type Validation for integrity
Error Handling for non-existent tables/columns
B-tree Implementation for efficient data storage and retrieval

🎨 Web Interface Features

Clean, modern UI with dark/light mode support
Real-time query execution
Interactive table list sidebar
Success/Error messages with detailed feedback
Keyboard shortcuts (Ctrl+Enter/Cmd+Enter to run queries)
Responsive design for desktop and tablet use

Demo

🚀 Installation

Prerequisites

Go 1.16 or later
Modern web browser for web interface
Git (for cloning the repository)

Quick Start

Clone the repository:

git clone https://github.com/venkat1017/sqlight.git
cd sqlight

Build the project:

# Build CLI version
go build -o sqlight ./cmd/main.go

# Build web version
go build -o sqlightweb ./web/main.go

Run directly with Go (alternative to building):

# Run CLI version
go run cmd/main.go

# Run web version
go run web/main.go

🖱️ Usage

Web Interface

Start the web server:

./sqlightweb
# Or run directly with Go
go run web/main.go

Open your browser and visit:

http://localhost:8081

Use the web interface to:

Write and execute SQL queries
View table list in the sidebar
Click on tables to auto-fill SELECT queries
See detailed success/error messages
View query results in a formatted table

Command Line Interface

Run the CLI version:

# Basic usage
./sqlight

# With custom database file
./sqlight -db mydb.json

📝 Example SQL Commands

Create a Table

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

Insert Records

INSERT INTO users (id, name, email) VALUES (1, 'John Doe', 'john@example.com');
INSERT INTO users (id, name, email) VALUES (2, 'Jane Smith', 'jane@example.com');

Query Records

-- Select all records
SELECT * FROM users;

-- Select specific columns
SELECT id, name FROM users;

-- Select with WHERE clause
SELECT * FROM users WHERE id = 1;

-- Select with multiple conditions
SELECT * FROM users WHERE id > 0 AND name = 'John Doe';

Delete Records

-- Delete specific records
DELETE FROM users WHERE id = 1;

-- Delete with multiple conditions
DELETE FROM users WHERE id > 5 AND name = 'Test User';

-- Delete all records from a table
DELETE FROM users;

📁 Project Structure

sqlight/
├── cmd/                  # Command-line application
│   └── main.go           # CLI entry point
├── web/                  # Web server application
│   ├── main.go           # Web server entry point
│   └── static/           # Web interface files
│       ├── index.html    # Main HTML page
│       ├── styles.css    # CSS styles
│       └── script.js     # Frontend JavaScript
├── pkg/                  # Core packages
│   ├── db/               # Database implementation
│   │   ├── database.go   # Database operations
│   │   ├── table.go      # Table operations
│   │   ├── btree.go      # B-tree implementation
│   │   └── cursor.go     # Record cursor
│   ├── sql/              # SQL parsing
│   │   └── parser.go     # SQL parser
│   └── interfaces/       # Core interfaces
│       └── interfaces.go # Interface definitions
├── examples/             # Example usage and demos
├── tests/                # Test suite
├── go.mod                # Go module definition
└── README.md             # Project documentation

📚 Documentation

Architecture

SQLight follows a layered architecture:

Interface Layer - Web UI and CLI for user interaction
SQL Parser - Converts SQL strings into structured statements
Query Executor - Processes statements and performs operations
Storage Engine - Manages data persistence and retrieval
B-tree Implementation - Provides efficient data storage and access

Performance Considerations

SQLight uses a B-tree implementation for efficient data access
JSON-based persistence provides a balance of simplicity and performance
In-memory operations for speed with periodic persistence for durability

🧪 Development

Running Tests

go test ./...

Debugging

# Run with verbose logging
go run cmd/main.go -v

Browser Support

The web interface works best with:

Chrome/Edge (latest versions)
Firefox (latest version)
Safari (latest version)

👥 Contributing

We welcome contributions from the community! Here's how you can help:

Fork the repository
Create your feature branch (git checkout -b feature/amazing-feature)
Commit your changes (git commit -m 'Add some amazing feature')
Push to the branch (git push origin feature/amazing-feature)
Open a Pull Request

Areas for Contribution

Additional SQL command support (UPDATE, JOIN operations)
More data types (FLOAT, DATETIME, BOOLEAN, etc.)
Improved SQL parsing and validation
Query optimization and execution planning
Additional indexing strategies
UI/UX improvements
Documentation enhancements
Test coverage expansion

📄 License

This project is licensed under the MIT License - see the LICENSE file for details.

🙏 Acknowledgments

Inspired by SQLite
Built using Go's standard library
Modern web interface using vanilla JavaScript
Thanks to all contributors who have helped shape this project

⬆ Back to Top

Made with ❤️ by the SQLight team

0xack13/SQLight