MergenDB

The Ultra-Compact, High-Performance Embedded Columnar Database Engine & Vectorized SQL Processor for Python and Node.js

MergenDB is an ultra-compact, high-performance embedded columnar database engine engineered to process massive analytical workloads and multi-million row table scans on resource-constrained hardware. It delivers strict zero external runtime dependencies -- requiring no C compilers, no native C++ binaries, and no bulky runtimes across both Python and Node.js.

Whether scanning a 100-million row table on a 500 MB RAM VPS, streaming telemetry on an edge device, running real-time analytics in Node.js/TypeScript, or managing hierarchical databases through Mergen Studio, MergenDB provides columnar speed with bounded memory guarantees (< 20 MB peak RAM).

Key Highlights

• Vectorized Columnar Predicate Pushdown: Point lookups and scalar filters (e.g. WHERE device_id = 5821049 or WHERE status = 'active') execute in ~1 second on 100M+ row tables, directly evaluating binary byte streams at C level (bytes.__contains__ Boyer-Moore-Horspool) without allocating individual string objects in memory.
• Zero External Dependencies: Built purely on standard library primitives (zlib, struct, mmap, json, http). Zero third-party runtime bloat in production environments.
• Strictly Bounded Memory (< 20 MB RAM): Data streams in configurable column blocks (1,024 to 16,384 rows). Peak memory never grows with database file size.
• Hierarchical Database Architecture: Organize data natively: Databases -> Tables -> Nested Sub-tables (e.g. enterprise.orders.shipments) with dot-notation SQL queries.
• Zero-Memory Streaming Engine: Stream multi-gigabyte CSV, JSON, JSONL, and SQL dumps directly to disk or HTTP sockets in 64 KB chunks without buffering datasets into memory.
• Dual Query Paradigm: Full standard SQL engine (joins, multi-column GROUP BY, HAVING, aggregations) alongside Pythonic and JavaScript document-style APIs (find, find_one, search, insert, update, delete, upsert).
• Adaptive Columnar Compression: Automatic per-column encoding pipeline (Bit-packed booleans, Delta/FoR integers, Block Dictionary, Run-Length Encoding, and secondary Zlib compaction) delivering up to 50:1 compression ratio.
• 1024-bit Block Bloom Filters & ZoneMaps: Skips irrelevant blocks during point lookups with zero disk reads.
• Fine-Grained Concurrency (RWLock): Concurrent lock-free readers execute simultaneously while atomic writers stage changes with automatic rollback safety.
• Mergen Studio Web UI: Visual database explorer, interactive SQL console, schema inspector, and streaming transfer manager.
• Strict Zero-Emoji Policy: Clean, professional interface built with deterministic status tags ([+], [-], [*], [!]).

Installation

Python SDK & Core Engine

pip install --upgrade mergendb

To include the optional web dashboard extension:

pip install --upgrade "mergendb[studio]"

Node.js & TypeScript SDK

npm install mergendb

Quickstart

Python Quickstart (Universal IoT Telemetry Example)

import mergendb

# 1. Connect to local table (auto-created if absent)
telemetry = mergendb.connect("iot_telemetry.mgdb")

# 2. Define schema if empty
if telemetry.row_count == 0:
    telemetry.create_schema([
        ("device_id", "INT64"),
        ("station_code", "STRING"),
        ("temperature", "DOUBLE"),
        ("humidity", "DOUBLE"),
        ("is_active", "BOOLEAN")
    ])

# 3. Insert universal records
telemetry.insert([
    {"device_id": 101, "station_code": "US-EAST-01", "temperature": 21.4, "humidity": 48.2, "is_active": True},
    {"device_id": 102, "station_code": "EU-WEST-02", "temperature": 18.9, "humidity": 55.0, "is_active": True},
    {"device_id": 103, "station_code": "AP-SOUTH-01", "temperature": 31.2, "humidity": 72.1, "is_active": False}
])

# 4. Analytical SQL aggregation
res = telemetry.sql("""
    SELECT station_code, AVG(temperature) AS avg_temp, MAX(humidity) AS max_hum
    FROM iot_telemetry
    WHERE is_active = true
    GROUP BY station_code
    ORDER BY avg_temp DESC;
""")
print(res.display())

# 5. Fluent Query Builder
active_stations = (
    telemetry.query()
             .select("station_code", "temperature")
             .where("temperature > 20.0")
             .order_by("temperature", desc=True)
             .limit(10)
             .to_dicts()
)
print("Active Stations:", active_stations)

Node.js & TypeScript Quickstart

import { connect } from 'mergendb';

const db = connect({
  host: '127.0.0.1',
  port: 8765,
  user: 'root',
  password: ''
});

async function main() {
  // 1. Create table
  const telemetry = await db.createTable('iot_telemetry', [
    { name: 'device_id', type: 'INT64' },
    { name: 'station_code', type: 'STRING' },
    { name: 'temperature', type: 'DOUBLE' },
    { name: 'humidity', type: 'DOUBLE' },
    { name: 'is_active', type: 'BOOLEAN' }
  ]);

  // 2. Insert records
  await telemetry.insert([
    { device_id: 101, station_code: 'US-EAST-01', temperature: 21.4, humidity: 48.2, is_active: true },
    { device_id: 102, station_code: 'EU-WEST-02', temperature: 18.9, humidity: 55.0, is_active: true }
  ]);

  // 3. Safe parameterized SQL via tagged template literal
  const minTemp = 20.0;
  const results = await db.sql`
    SELECT station_code, AVG(temperature) AS avg_temp
    FROM iot_telemetry
    WHERE temperature >= ${minTemp} AND is_active = true
    GROUP BY station_code;
  `;
  console.table(results.toObjects());

  // 4. Fluent Query Builder
  const highTemp = await telemetry.query()
    .select('device_id', 'station_code', 'temperature')
    .where('temperature > 20.0')
    .orderBy('temperature', true)
    .limit(5)
    .toObjects();
  console.log('High Temp Readings:', highTemp);
}

main().catch(console.error);

Complete Command & API Reference (Python vs JavaScript / TypeScript)

MergenDB maintains 100% feature symmetry across Python and Node.js/TypeScript:

1. Connection & Server Management

2. Database Containers & Schema Management

3. Data Mutation (CRUD & Optimization)

4. Querying & Analytics

5. Fluent Query Builder (query() / builder())

Method-chaining syntax for readable analytical queries:

# Python
orders = (
    table.query()
         .select("order_id", "customer_id", "total_amount", "status")
         .where("total_amount >= 150.00")
         .filter(status="completed")
         .order_by("total_amount", desc=True)
         .limit(20)
         .offset(40)
         .to_dicts()
)

// JavaScript / TypeScript
const orders = await table.query()
  .select('order_id', 'customer_id', 'total_amount', 'status')
  .where('total_amount >= 150.00')
  .filter({ status: 'completed' })
  .orderBy('total_amount', true)
  .limit(20)
  .offset(40)
  .toObjects();

6. Zero-Memory Streaming File Transfers

Stream multi-gigabyte files directly to disk or network sockets without memory buffering:

Interactive CLI REPL

Launch the interactive terminal shell:

mergen

mergen> SHOW TABLES;
mergen> USE iot_telemetry;                      -- Smart context: selects table 'iot_telemetry.mgdb'
mergen[iot_telemetry.mgdb]> WHERE device_id = 101;  -- Direct filter query on active table (ZoneMap pruned)
mergen[iot_telemetry.mgdb]> USE DATABASE analytics; -- Explicitly switch database container
mergen(analytics)> SHOW TABLES;
mergen(analytics)> USE TABLE metrics;          -- Explicitly select table inside database
mergen(analytics)[metrics.mgdb]> SELECT host, AVG(latency) FROM metrics GROUP BY host;
mergen(analytics)[metrics.mgdb]> EXPORT metrics TO CSV;

Mergen Studio Web Management Dashboard

Mergen Studio provides an interactive web-based graphical interface for database administration, visual table inspection, real-time query execution, and streaming file transfers.

# Launch server and access Studio
mergen serve 8765

Navigate to http://localhost:8765/studio in any browser:

• Hierarchical Sidebar: Expand and inspect databases, tables, and nested sub-tables.
• SQL Console: Syntax highlighting, query history, and execution benchmarks.
• Data & Structure Browser: Dynamic grid rendering column data types even for empty tables.
• Streaming Transfer Hub: Real-time upload/download progress counters with chunked memory safety.
• Authentication: Role-based access control (default credentials: root:).

Adaptive Columnar Compression

When persisting column blocks, MergenDB inspects data distributions and dynamically selects the optimal hardware encoding:

Performance Benchmarks

Measured on standard hardware with 100,000 mixed records (12 columns: integers, floats, timestamps, statuses, long strings):

• Vectorized Predicate Pushdown (100M+ Rows): ~1.0 second point lookups.
• Exact Filter Scan Throughput: ~50,000,000 rows/second (single CPU core).
• SQL Streaming Import Speed: ~70,000 - 120,000 rows/second on standard NVMe SSD.

Test Suite & Verification

MergenDB is verified with over 4,600 automated tests (2,600+ Python tests and 2,000+ Node.js tests) covering:

• Storage, block encoding, and adaptive compression roundtrips.
• Fault tolerance against ragged rows, corrupt headers, escaped SQL quotes, and zero-byte boundaries.
• Concurrency, thread safety, and RWLock staging verification.
• 100% pass rate across Windows, macOS, Linux, and Docker.

# Run Python test suite
python -m unittest discover -s tests

# Run Node.js SDK test suite
node sdks/nodejs/test.js

License

Distributed under the MIT License. See LICENSE for details.