xxsql

README

XxSql

A lightweight SQL database implemented in pure Go, featuring a B+ tree storage engine and MySQL-compatible protocol.

Goal

XxSql aims to provide a lightweight SQL database with better concurrency than SQLite while maintaining similar ease of deployment.

Aspect	SQLite	XxSql
Deployment	Single file	Single binary
Concurrency	Database-level locking	Multi-granularity locking (table/page/row)
Connections	Single writer or multiple readers	100+ simultaneous read/write connections
Protocol	Embedded library	MySQL-compatible network protocol
Language	C	Pure Go (no CGO)

Why XxSql?

SQLite is excellent for embedded scenarios but has concurrency limitations - writes block all reads, and only one writer at a time. XxSql addresses this with:

• Fine-grained locking - Multi-level locks (global, catalog, table, page, row) allow concurrent operations
• Multiple writers - Concurrent write transactions with deadlock detection
• Connection pooling - Handle 100+ simultaneous client connections
• Network protocol - MySQL compatibility means no driver changes needed for existing applications

If you need:

• A lightweight database that handles concurrent access better than SQLite
• MySQL protocol compatibility without MySQL's resource overhead
• Pure Go implementation for easy cross-platform deployment

Then XxSql might be the right choice.

Features

Core Features

• Pure Go - No CGO dependencies, easy cross-compilation
• Single-binary deployment - Simple installation and distribution
• MySQL-compatible protocol - Works with existing MySQL clients and drivers
• B+ tree storage engine - Efficient indexing and range queries
• High concurrency - Supports 100+ simultaneous connections
• ACID transactions - WAL-based durability with ARIES-style crash recovery

Storage & Concurrency

• B+ Tree Index - Primary and secondary indexes with efficient range scans
• Buffer Pool - LRU-based page cache with configurable size
• WAL (Write-Ahead Log) - Durability with configurable sync intervals
• Checkpoints - Periodic checkpoints for faster recovery
• Lock Manager - Multi-granularity locking (global, table, page, row)
• Deadlock Detection - Wait-for graph algorithm
• Sequence Manager - Atomic auto-increment with persistence

SQL Support

• DDL - CREATE/ALTER/DROP TABLE, CREATE/DROP INDEX
• DML - SELECT, INSERT, UPDATE, DELETE, TRUNCATE
• JOINs - INNER, LEFT, RIGHT, CROSS JOIN with multiple table support
• UNION - UNION and UNION ALL with duplicate elimination
• Aggregates - COUNT, SUM, AVG, MIN, MAX
• Subqueries - WHERE clause subqueries
• Constraints - PRIMARY KEY, UNIQUE, NOT NULL, DEFAULT, CHECK

Security

• Authentication - MySQL native password authentication (SHA1)
• Role-based Access Control - Admin and User roles
• Table-level Permissions - GRANT/REVOKE at global, database, and table level
• Audit Logging - Track security events with file rotation
• Rate Limiting - Prevent brute force attacks
• IP Access Control - Whitelist/blacklist with CIDR support
• Password Policy - Configurable length, complexity, expiration
• TLS/SSL - Encrypted connections (optional)

Tools & Interfaces

• Go SQL Driver - Native Go database/sql driver
• CLI Client - Interactive REPL with readline support
• Web Management - Browser-based admin interface
• Backup/Recovery - Full backup with compression support

Quick Start

Installation

Download Pre-built Binaries

Download from GitHub Releases:

Platform	Architecture	Download
Linux	amd64	xxsql-vX.X.X-linux-amd64.tar.gz
Linux	arm64	xxsql-vX.X.X-linux-arm64.tar.gz
macOS	amd64 (Intel)	xxsql-vX.X.X-darwin-amd64.tar.gz
macOS	arm64 (Apple Silicon)	xxsql-vX.X.X-darwin-arm64.tar.gz
Windows	amd64	xxsql-vX.X.X-windows-amd64.zip

# Linux/macOS example
tar -xzf xxsql-v0.0.4-linux-amd64.tar.gz
./xxsqls -data-dir ./data

Build from Source

# Clone the repository
git clone https://github.com/topxeq/xxsql.git
cd xxsql

# Build the server
go build -o xxsqls ./cmd/xxsqls

# Build the CLI client (optional)
go build -o xxsqlc ./cmd/xxsqlc

Running the Server

# Create a data directory
mkdir -p data

# Start with default settings
./xxsqls -data-dir ./data

# Or with a configuration file
./xxsqls -config configs/xxsql.json

# Command-line options
./xxsqls -data-dir ./data -mysql-port 3306 -http-port 8080 -log-level INFO

Connecting to XxSql

Using MySQL Client:

mysql -h 127.0.0.1 -P 3306 -u admin -p
# Default password: admin

Using CLI Client:

./xxsqlc -h localhost -P 3306 -u admin -d testdb

Using Go Application:

import "github.com/topxeq/xxsql/pkg/xxsql"

db, err := sql.Open("xxsql", "admin:password@tcp(localhost:3306)/testdb")

Using Web Interface:

http://localhost:8080

SQL Support

Data Types

Type	Description
SEQ	Auto-increment integer (like MySQL AUTO_INCREMENT)
TINYINT, SMALLINT, INT, INTEGER, BIGINT	Integer types (64-bit)
FLOAT, DOUBLE	Floating point types
DECIMAL(p,s), NUMERIC(p,s)	Exact numeric with precision and scale
CHAR(n)	Fixed-length string
VARCHAR(n)	Variable-length string
TEXT	Large text
BLOB	Binary large object
BOOL, BOOLEAN	Boolean (TRUE/FALSE)
DATE	Date (YYYY-MM-DD)
TIME	Time (HH:MM:SS)
DATETIME, TIMESTAMP	Date and time

DECIMAL Example:

CREATE TABLE products (
    id SEQ PRIMARY KEY,
    name VARCHAR(100),
    price DECIMAL(10,2)    -- 10 total digits, 2 after decimal
);

INSERT INTO products (id, name, price) VALUES (1, 'Widget', 99.99);
INSERT INTO products (id, name, price) VALUES (2, 'Gadget', -1234.56);

DDL Statements

-- Create table with constraints
CREATE TABLE users (
    id SEQ PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    email VARCHAR(100) UNIQUE,
    age INT DEFAULT 0,
    CHECK (age >= 0)
);

-- Create indexes
CREATE INDEX idx_name ON users (name);
CREATE UNIQUE INDEX idx_email ON users (email);

-- Alter table
ALTER TABLE users ADD COLUMN created_at DATETIME;
ALTER TABLE users DROP COLUMN age;
ALTER TABLE users MODIFY COLUMN name VARCHAR(200);
ALTER TABLE users RENAME COLUMN name TO username;
ALTER TABLE users RENAME TO customers;

-- Drop
DROP TABLE users;
DROP INDEX idx_name ON users;

DML Statements

-- Insert
INSERT INTO users (id, name, email) VALUES (1, 'Alice', 'alice@example.com');

-- Select
SELECT * FROM users;
SELECT name, email FROM users WHERE id = 1;
SELECT * FROM users ORDER BY name LIMIT 10 OFFSET 5;

-- Update
UPDATE users SET email = 'new@example.com' WHERE id = 1;

-- Delete
DELETE FROM users WHERE id = 1;

-- Truncate
TRUNCATE TABLE users;

JOIN Support

-- Inner join
SELECT u.name, o.order_id
FROM users u
INNER JOIN orders o ON u.id = o.user_id;

-- Left join
SELECT u.name, o.order_id
FROM users u
LEFT JOIN orders o ON u.id = o.user_id;

-- Right join
SELECT u.name, o.order_id
FROM users u
RIGHT JOIN orders o ON u.id = o.user_id;

-- Cross join
SELECT u.name, p.product_name
FROM users u
CROSS JOIN products p;

-- Multiple joins
SELECT u.name, o.order_id, p.product_name
FROM users u
INNER JOIN orders o ON u.id = o.user_id
INNER JOIN products p ON o.product_id = p.id;

UNION Support

SELECT name FROM users
UNION
SELECT name FROM customers;

SELECT name FROM users
UNION ALL
SELECT name FROM customers;

User Management

-- Create user
CREATE USER 'myuser' IDENTIFIED BY 'password';

-- Grant permissions
GRANT ALL ON *.* TO 'myuser';
GRANT SELECT, INSERT ON mydb.users TO 'myuser';

-- Revoke permissions
REVOKE INSERT ON mydb.users FROM 'myuser';

-- Show grants
SHOW GRANTS FOR 'myuser';

-- Drop user
DROP USER 'myuser';

Backup & Recovery

-- Create backup
BACKUP DATABASE TO '/path/to/backup.xbak' WITH COMPRESS;

-- Restore from backup
RESTORE DATABASE FROM '/path/to/backup.xbak';

Go SQL Driver

XxSql provides a native Go driver compatible with the database/sql package.

Installation

go get github.com/topxeq/xxsql/pkg/xxsql

Usage

package main

import (
    "database/sql"
    "fmt"
    "log"

    _ "github.com/topxeq/xxsql/pkg/xxsql"
)

func main() {
    // Open connection
    db, err := sql.Open("xxsql", "admin:password@tcp(localhost:3306)/testdb")
    if err != nil {
        log.Fatal(err)
    }
    defer db.Close()

    // Create table
    _, err = db.Exec(`CREATE TABLE IF NOT EXISTS users (
        id SEQ PRIMARY KEY,
        name VARCHAR(100)
    )`)
    if err != nil {
        log.Fatal(err)
    }

    // Insert
    result, err := db.Exec("INSERT INTO users (name) VALUES (?)", "Alice")
    if err != nil {
        log.Fatal(err)
    }
    id, _ := result.LastInsertId()
    fmt.Printf("Inserted ID: %d\n", id)

    // Query
    rows, err := db.Query("SELECT id, name FROM users")
    if err != nil {
        log.Fatal(err)
    }
    defer rows.Close()

    for rows.Next() {
        var id int64
        var name string
        rows.Scan(&id, &name)
        fmt.Printf("ID: %d, Name: %s\n", id, name)
    }
}

DSN Format

XxSql driver supports two DSN formats:

1. MySQL-style DSN:

[username[:password]@][protocol[(address)]]/dbname[?options]

Examples:

• admin:password@tcp(localhost:3306)/testdb
• root@tcp(127.0.0.1:3306)/mydb
• /testdb

2. URL-style DSN:

xxsql://[username[:password]@]host[:port]/dbname[?options]

Examples:

• xxsql://admin:password@localhost:3306/testdb
• xxsql://root@127.0.0.1:3306/mydb
• xxsql://localhost/testdb

Supported Options:

Parameter	Description	Default
timeout	Connection timeout	10s
readTimeout	Read timeout	30s
writeTimeout	Write timeout	30s
charset	Character set	utf8mb4
parseTime	Parse DATE/DATETIME to time.Time	false

CLI Client

The xxsqlc CLI client provides an interactive REPL for SQL execution.

Usage

# Basic connection
./xxsqlc -u admin -host localhost -port 3306 -d testdb

# Using DSN
./xxsqlc -dsn "xxsql://admin:password@localhost:3306/testdb"

# Execute SQL from command line
./xxsqlc -u admin -d testdb -e "SELECT * FROM users"

# Execute SQL from file
./xxsqlc -u admin -d testdb -f script.sql -progress

# Specify output format
./xxsqlc -u admin -d testdb -format json -e "SELECT * FROM users"

Command-Line Options

Flag	Description
-host	Server host (default: localhost)
-port	Server port (default: 3306)
-u	Username
-p	Password
-d	Database name
-dsn	Connection string (URL format)
-e	Execute command and exit
-f	Execute SQL from file and exit
-format	Output format: table, vertical, json, tsv
-progress	Show progress when executing SQL file
-q	Suppress welcome message
-version	Print version information

Features

• Multi-line SQL input (continue until ;)
• Command history (up/down arrows)
• Tab completion for SQL keywords
• Query timing display
• Multiple output formats

Meta Commands

Command	Description
\h, \?	Show help
\q	Quit
\c	Clear screen / Clear current query
\v	Show version
\l	List databases
\d [table]	Describe table or list tables
\u <db>	Use database
\conninfo	Show connection info
\timing	Toggle query timing
\g, \vertical	Switch to vertical output format
\j, \json	Switch to JSON output format
\t, \tsv	Switch to TSV output format
\table	Switch to table output format (default)
\format	Show current output format

Web Management Interface

XxSql includes a built-in web interface for database administration.

Access

Navigate to http://localhost:8080 (default HTTP port).

Features

• Dashboard - Server status, uptime, metrics
• Query Editor - Execute SQL with Ctrl+Enter, view results
• Table Browser - View tables, schemas, and data
• User Management - Create, edit, delete users
• Backup/Restore - Create and restore backups with compression
• Log Viewer - View server and audit logs with auto-refresh
• Configuration - View server configuration

Authentication

Web interface uses cookie-based sessions with 24-hour expiry. Login with database user credentials.

Architecture

┌─────────────────────────────────────────────────────────────────┐
│                         Client Interfaces                        │
│  ┌──────────┐  ┌──────────┐  ┌──────────┐  ┌──────────────┐   │
│  │  MySQL   │  │   CLI    │  │  Go Driver│  │  Web Browser │   │
│  │  Client  │  │  Client  │  │           │  │              │   │
│  └────┬─────┘  └────┬─────┘  └─────┬─────┘  └──────┬───────┘   │
├───────┴─────────────┴──────────────┴────────────────┴───────────┤
│                      Protocol Layer                              │
│  ┌──────────────────┐  ┌──────────────────┐  ┌──────────────┐  │
│  │ MySQL Protocol   │  │ Private Protocol │  │ HTTP/REST API│  │
│  └──────────────────┘  └──────────────────┘  └──────────────┘  │
├─────────────────────────────────────────────────────────────────┤
│                      Query Executor                              │
│  ┌─────────┐ ┌─────────┐ ┌─────────┐ ┌─────────┐ ┌──────────┐ │
│  │  Parser │ │ Planner │ │Executor │ │  Auth   │ │ Security │ │
│  └─────────┘ └─────────┘ └─────────┘ └─────────┘ └──────────┘ │
├─────────────────────────────────────────────────────────────────┤
│                      Storage Engine                              │
│  ┌─────────┐ ┌─────────┐ ┌─────────┐ ┌─────────┐              │
│  │ B+ Tree │ │ Buffer  │ │   WAL   │ │  Lock   │              │
│  │ Index   │ │  Pool   │ │         │ │ Manager │              │
│  └─────────┘ └─────────┘ └─────────┘ └─────────┘              │
│  ┌─────────┐ ┌─────────┐ ┌─────────┐ ┌─────────┐              │
│  │Sequence │ │Checkpoint│ │ Recovery│ │  Backup │              │
│  │ Manager │ │          │ │         │ │ Manager │              │
│  └─────────┘ └─────────┘ └─────────┘ └─────────┘              │
├─────────────────────────────────────────────────────────────────┤
│                         Data Files                               │
│  .xdb (data)  .xmeta (metadata)  .xwal (WAL)  .xidx (index)    │
│  .xbak (backup)  users.json  grants.json  audit.log            │
└─────────────────────────────────────────────────────────────────┘

Configuration

Configuration file example (configs/xxsql.json):

{
  "server": {
    "name": "xxsql",
    "data_dir": "./data",
    "pid_file": "./xxsql.pid"
  },
  "network": {
    "private_port": 9527,
    "mysql_port": 3306,
    "http_port": 8080,
    "bind": "0.0.0.0"
  },
  "storage": {
    "page_size": 4096,
    "buffer_pool_size": 1000,
    "wal_max_size_mb": 100,
    "wal_sync_interval": 100,
    "checkpoint_pages": 1000,
    "checkpoint_int_sec": 300
  },
  "worker": {
    "pool_size": 32,
    "max_connection": 200
  },
  "worker_pool": {
    "worker_count": 32,
    "task_queue_size": 1000,
    "task_timeout": "30s",
    "strategy": "round_robin"
  },
  "connection": {
    "max_connections": 200,
    "wait_timeout": 30,
    "idle_timeout": 28800,
    "strategy": "fifo"
  },
  "auth": {
    "enabled": true,
    "admin_user": "admin",
    "admin_password": "admin",
    "session_timeout_sec": 3600
  },
  "security": {
    "audit_enabled": true,
    "audit_file": "audit.log",
    "audit_max_size_mb": 100,
    "audit_max_backups": 10,
    "rate_limit_enabled": true,
    "rate_limit_max_attempts": 5,
    "rate_limit_window_min": 15,
    "rate_limit_block_min": 30,
    "password_min_length": 8,
    "password_require_upper": true,
    "password_require_lower": true,
    "password_require_digit": true,
    "tls_enabled": false,
    "tls_mode": "optional",
    "ip_access_mode": "allow_all"
  },
  "backup": {
    "auto_interval_hours": 24,
    "keep_count": 7,
    "backup_dir": "./backup"
  },
  "recovery": {
    "wal_sync_interval_ms": 100,
    "checkpoint_interval_sec": 300,
    "wal_retention_sec": 86400
  },
  "safety": {
    "enable_checksum": true,
    "max_recovery_attempts": 3
  },
  "log": {
    "level": "INFO",
    "file": "",
    "max_size_mb": 100,
    "max_backups": 5,
    "max_age_days": 30,
    "compress": false
  }
}

Command-Line Options

Flag	Description
-config	Path to configuration file
-data-dir	Data directory path
-mysql-port	MySQL protocol port
-http-port	HTTP/Web interface port
-private-port	Private protocol port
-bind	Bind address
-log-level	Log level (DEBUG, INFO, WARN, ERROR)
-version	Print version information
-init-config	Print example configuration

Development

Project Structure

xxsql/
├── cmd/
│   ├── xxsqls/          # Server executable
│   └── xxsqlc/          # CLI client executable
├── internal/
│   ├── auth/            # Authentication and authorization
│   ├── backup/          # Backup and recovery
│   ├── config/          # Configuration management
│   ├── executor/        # SQL query execution
│   ├── log/             # Logging
│   ├── mysql/           # MySQL protocol handler
│   ├── protocol/        # Private network protocol
│   ├── security/        # Security features
│   ├── server/          # Server management
│   ├── sql/             # SQL parser and AST
│   ├── storage/         # Storage engine
│   │   ├── btree/       # B+ tree index
│   │   ├── buffer/      # Buffer pool
│   │   ├── catalog/     # Table catalog
│   │   ├── checkpoint/  # Checkpoint management
│   │   ├── lock/        # Lock manager
│   │   ├── page/        # Page management
│   │   ├── recovery/    # Crash recovery
│   │   ├── row/         # Row serialization
│   │   ├── sequence/    # Sequence manager
│   │   ├── table/       # Table operations
│   │   ├── types/       # Data types
│   │   └── wal/         # Write-ahead log
│   └── web/             # Web management interface
│       ├── static/      # CSS, JS assets
│       └── templates/   # HTML templates
├── pkg/
│   └── xxsql/           # Go SQL driver
└── configs/             # Configuration files

Running Tests

# Run all tests
make test
# or
go test ./...

# Run with race detector
make test-race

# Run with coverage
make test-coverage

# Run integration tests
make test-integration

# View coverage report
make coverage-report

# Run benchmarks
make bench

# Run linter
make lint

Test Coverage

Package	Coverage
Pkg/Errors	98.0%
Storage/Page	100.0%
Config	96.7%
Auth	93.9%
Storage/Catalog	90.5%
Storage/Storage	89.4%
Storage/Row	89.1%
Storage/BTree	89.0%
Storage/Checkpoint	88.9%
Storage/Types	88.1%
Security	88.8%
Protocol	86.4%
Storage/Buffer	86.2%
Web	86.1%
Storage/Table	85.9%
Cmd/Xxsqls	85.7%
Storage/Lock	84.4%
Storage/WAL	84.8%
MySQL	84.8%
Storage/Sequence	85.1%
Storage/Recovery	83.3%
Backup	83.1%
Pkg/Xxsql	83.5%
Server	83.8%
Log	82.0%
Executor	81.5%
Cmd/Xxsqlc	77.9%
Average	87.5%

See docs/TESTING.md for testing guidelines.

Roadmap

v0.0.4 (Current Release) ✅

• All v0.0.1 features
• Enhanced Go SQL Driver with DSN parsing
• Multi-platform binary releases (Linux, macOS, Windows)
• Automated GitHub Actions release workflow
• Improved SQL executor
• BLOB type support with hex notation (X'...', 0x...)
• CAST expressions (INT, FLOAT, VARCHAR, BLOB, BOOL)
• BLOB functions (HEX, UNHEX, LENGTH, OCTET_LENGTH)
• IS NULL / IS NOT NULL syntax support
• Improved WHERE clause handling (AND, OR operators)

v0.0.1 ✅

• Basic framework + Config + Logging
• SQL Parser
• Storage Engine with B+ Tree
• Buffer Pool + WAL + Checkpoint
• Concurrency Control (Lock Manager, Deadlock Detection)
• Sequence/Auto-increment
• Crash Recovery (ARIES-style)
• MySQL Protocol
• Authentication & Permissions
• Security Features (Audit, Rate Limiting, IP Access, TLS)
• JOIN Support (INNER, LEFT, RIGHT, CROSS)
• UNION Support
• DDL Enhancement (Constraints, ALTER TABLE)
• Backup/Recovery
• Go SQL Driver
• CLI Client with REPL
• Web Management Interface
• Comprehensive test suite (87.5% average coverage)

Future Plans

• Query optimization (index hints, query planner)
• Replication support (master-slave)
• Connection pooling improvements
• More SQL functions
• Performance benchmarks
• Subquery optimization
• Transaction isolation levels

Contributing

Contributions are welcome! Please feel free to submit issues and pull requests.

License

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

Acknowledgments

• Inspired by SQLite and MySQL
• B+ tree implementation based on classic database literature
• MySQL protocol implementation follows MySQL documentation
• ARIES recovery algorithm from "Transaction Processing" by Mohan et al.