NewSQL That Starts Simple! Stoolap is a high-performance, columnar SQL database written in pure Go with zero dependencies. It combines OLTP (transaction) and OLAP (analytical) capabilities in a single engine, making it suitable for hybrid transactional/analytical processing (HTAP) workloads with MVCC support.
- Pure Go Implementation: Zero external dependencies for maximum portability
- ACID Transactions: Full transaction support with true MVCC (Multi-Version Concurrency Control)
- Lock-Free Design: No table-level locks, even for SNAPSHOT isolation
- Fast Analytical Processing: Columnar indexes optimized for analytical queries
- HTAP Capabilities: Hybrid transactional/analytical processing in one engine
- Memory-First Design: Optimized for in-memory performance with optional persistence
- Vectorized Execution: SIMD-accelerated operations for high throughput
- SQL Support: Rich SQL functionality including JOINs, aggregations, CTEs, and subqueries
- JSON Support: Native JSON data type with optimized storage
- Go SQL Driver: Standard database/sql compatible driver
- PostgreSQL Compatibility: Wire protocol server for PostgreSQL client compatibility
go get github.com/stoolap/stoolapOr clone the repository and build from source:
git clone https://github.com/stoolap/stoolap.git
cd stoolap
go build -o stoolap ./cmd/stoolap
go build -o stoolap-pgserver ./cmd/stoolap-pgserverStoolap comes with a built-in CLI for interactive SQL queries:
# Start CLI with in-memory database
./stoolap -db memory://
# Start CLI with persistent storage
./stoolap -db file:///path/to/dataThe CLI provides a familiar SQL interface with command history, tab completion, and formatted output.
Stoolap can be used in Go applications using the standard database/sql interface:
package main
import (
"database/sql"
"fmt"
"log"
_ "github.com/stoolap/stoolap/pkg/driver"
)
func main() {
// Connect to an in-memory database
db, err := sql.Open("stoolap", "memory://")
if err != nil {
log.Fatal(err)
}
defer db.Close()
// Create a table
_, err = db.Exec(`
CREATE TABLE users (
id INTEGER PRIMARY KEY,
name TEXT NOT NULL,
email TEXT,
created_at TIMESTAMP
)
`)
if err != nil {
log.Fatal(err)
}
// Insert data
_, err = db.Exec(`
INSERT INTO users (id, name, email, created_at)
VALUES (1, 'John Doe', 'john@example.com', NOW())
`)
if err != nil {
log.Fatal(err)
}
// Query data
rows, err := db.Query("SELECT id, name, email FROM users")
if err != nil {
log.Fatal(err)
}
defer rows.Close()
// Process results
for rows.Next() {
var id int
var name, email string
if err := rows.Scan(&id, &name, &email); err != nil {
log.Fatal(err)
}
fmt.Printf("User: %d, %s, %s\n", id, name, email)
}
}Stoolap includes a PostgreSQL wire protocol server that allows any PostgreSQL client or driver to connect:
# Start server with in-memory database (default port 5432)
./stoolap-pgserver
# Start server with persistent database
./stoolap-pgserver -d file:///path/to/database.db
# Start server on custom port
./stoolap-pgserver -b :5433Connect using any PostgreSQL client:
# Using psql
psql -h localhost -p 5432 -d stoolap
# Using Python (psycopg2)
import psycopg2
conn = psycopg2.connect(host="localhost", port=5432, database="stoolap")
# Using Node.js (pg)
const { Client } = require('pg')
const client = new Client({ host: 'localhost', port: 5432, database: 'stoolap' })The PostgreSQL server supports:
- Full transaction support (BEGIN/COMMIT/ROLLBACK)
- READ COMMITTED and SNAPSHOT isolation levels
- Standard PostgreSQL clients and ORMs
- Prepared statements (basic support)
Stoolap supports two storage modes:
- In-Memory:
memory://- Fast, non-persistent storage for maximum performance - File-Based:
file:///path/to/data- Durable storage with persistence
INTEGER: 64-bit signed integersFLOAT: 64-bit floating C183 point numbersTEXT: UTF-8 encoded stringsBOOLEAN: TRUE/FALSE valuesTIMESTAMP: Date and time valuesJSON: JSON-formatted data
- DDL: CREATE/ALTER/DROP TABLE, CREATE/DROP INDEX, CREATE/DROP VIEW
- DML: SELECT, INSERT, UPDATE, DELETE, MERGE
- Queries: JOINs, GROUP BY, ORDER BY, LIMIT, OFFSET, DISTINCT
- Subqueries: Scalar subqueries, IN/NOT IN, EXISTS/NOT EXISTS, correlated subqueries
- Common Table Expressions (CTEs): WITH clause for recursive and non-recursive CTEs
- Single and multiple CTEs in one query
- CTEs with column aliases
- CTEs in JOINs and subqueries
- CTEs with aggregations and HAVING clauses
- Nested CTEs (CTEs referencing other CTEs)
- Temporal Queries: AS OF TRANSACTION/TIMESTAMP for time travel queries
- Transactions: BEGIN/COMMIT/ROLLBACK with isolation levels
- Indexing: CREATE INDEX, unique constraints, primary keys, multi-column indexes
- Functions: Aggregate (SUM, AVG, COUNT, MIN, MAX) and scalar functions
Stoolap supports two isolation levels:
- READ COMMITTED (default): High concurrency, sees committed changes immediately
- SNAPSHOT: Consistent view from transaction start, with write-write conflict detection
-- Start a transaction with SNAPSHOT isolation
BEGIN TRANSACTION ISOLATION LEVEL SNAPSHOT;
SELECT * FROM accounts WHERE id = 1;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
COMMIT;Stoolap supports SQL:2011 temporal queries for accessing historical data:
-- Query data as of a specific transaction
SELECT * FROM users AS OF TRANSACTION 42;
-- Query data as of a specific timestamp
SELECT * FROM orders AS OF TIMESTAMP '2025-06-10 14:30:00';
-- Compare current and historical data
SELECT
current.balance as current_balance,
historical.balance as yesterday_balance
FROM accounts current
CROSS JOIN (
SELECT balance FROM accounts
AS OF TIMESTAMP '2025-06-09 23:59:59'
WHERE id = 1
) historical
WHERE current.id = 1;Stoolap supports CTEs for complex queries with improved readability:
-- Basic CTE
WITH sales_summary AS (
SELECT product_id, SUM(amount) as total_sales
FROM sales
WHERE year = 2024
GROUP BY product_id
)
SELECT p.name, s.total_sales
FROM products p
JOIN sales_summary s ON p.id = s.product_id
WHERE s.total_sales > 10000;
-- Multiple CTEs
WITH
regional_sales AS (
SELECT region, SUM(amount) as total
FROM sales
GROUP BY region
),
top_regions AS (
SELECT region
FROM regional_sales
WHERE total > (SELECT AVG(total) FROM regional_sales)
)
SELECT * FROM sales
WHERE region IN (SELECT region FROM top_regions);Full support for various types of subqueries:
-- IN subqueries
DELETE FROM users WHERE id IN (
SELECT user_id FROM inactive_users
);
-- Scalar subqueries
SELECT name, salary,
(SELECT AVG(salary) FROM employees) as avg_salary
FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees);
-- EXISTS subqueries
SELECT * FROM customers c
WHERE EXISTS (
SELECT 1 FROM orders o
WHERE o.customer_id = c.id
AND o.total > 1000
);Stoolap includes numerous performance optimizations:
- Lock-Free MVCC: Concurrent writers without serialization bottlenecks
- Columnar Storage: Column-oriented storage for CTEs and analytical queries (99.90% memory reduction)
- SIMD Operations: Vectorized execution for arithmetic, comparisons, and functions
- Hash-Based IN Subqueries: O(1) lookups for IN/NOT IN expressions (up to 2048x speedup)
- Array-Based Row Storage: Eliminated map allocations throughout query execution
- Specialized Data Structures: Custom concurrent maps and B-trees for high throughput
- Expression Pushdown: Filters pushed to storage layer for faster execution
- Type-Specific Optimization: Optimized operations for different data types
- Version Chain Management: Automatic garbage collection of old versions
Stoolap is under active development. While it provides ACID compliance and a rich feature set, it should be considered experimental for production use.
Comprehensive documentation is available at:
- Official Documentation: Complete documentation with architecture, features, and usage examples
Contributions are welcome! Please feel free to submit a Pull Request.
This project is licensed under the Apache License, Version 2.0 - see the LICENSE file for details.
Copyright 2025 Stoolap Contributors
Licensed under the Apache License, Version 2.0 (the "License");
you may not use this file except in compliance with the License.
You may obtain a copy of the License at
http://www.apache.org/licenses/LICENSE-2.0
Unless required by applicable law or agreed to in writing, software
distributed under the License is distributed on an "AS IS" BASIS,
WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
See the License for the specific language governing permissions and
limitations under the License.