SQL queries over raw FlatBuffer storage - zero conversion, real-time indexing, pure streaming
npm install flatsql
SQL queries over raw FlatBuffer storage — A streaming query engine that keeps data in native FlatBuffer format while providing SQL access via SQLite virtual tables.
Try FlatSQL in your browser: https://digitalarsenal.github.io/flatsql/
``bash`
npm install flatsql
`javascript
import { initFlatSQL } from 'flatsql/wasm';
// Initialize
const flatsql = await initFlatSQL();
// Create database with schema
const db = flatsql.createDatabase(
table User {
id: int (id);
name: string;
email: string (key);
age: int;
}, 'myapp');
// Register file identifier for routing
db.registerFileId('USER', 'User');
// Ingest FlatBuffer data (streaming)
db.ingest(flatbufferStream);
// Query with SQL
const result = db.query('SELECT * FROM User WHERE age > 25');
console.log(result.columns, result.rows);
`
FlatSQL bridges two technologies:
- FlatBuffers — Google's efficient cross-platform serialization library. Data is stored in binary format with zero-copy access (no parsing/unpacking needed).
- SQLite — The most widely deployed SQL database engine, used here only for SQL parsing and query execution.
The key insight: instead of converting FlatBuffers to SQLite rows (expensive), FlatSQL uses SQLite virtual tables to query FlatBuffer data directly. Your data stays in portable FlatBuffer format, readable by any FlatBuffer tooling, while you get SQL query capabilities.
Traditional approach:
`
FlatBuffer → Deserialize → SQLite rows → Query → Serialize → FlatBuffer
FlatSQL approach:
`
FlatBuffer → Query (via virtual table) → FlatBuffer
Benefits:
- Zero conversion overhead — Data stays in FlatBuffer format
- Streaming ingestion — Indexes built during data arrival, not after
- Portable output — Exported data is standard FlatBuffers, readable by any tooling
- Multi-source federation — Query across multiple FlatBuffer sources with automatic source tagging
| Repository | Description |
|------------|-------------|
| digitalarsenal/flatsql | This project — FlatSQL query engine |
| digitalarsenal/flatbuffers | Fork of Google FlatBuffers with WASM support |
| flatc-wasm | FlatBuffer compiler running in WebAssembly |
The C++ engine compiles to WebAssembly for cross-platform deployment:
`javascript
import { initFlatSQL } from 'flatsql/wasm';
const flatsql = await initFlatSQL();
// Create database with schema
const db = flatsql.createDatabase(
table User {
id: int (id);
name: string;
email: string (key);
age: int;
}, 'myapp');
// Register file identifier routing
db.registerFileId('USER', 'User');
// Enable demo field extractors (for testing)
db.enableDemoExtractors();
// Ingest FlatBuffer stream
// Format: [4-byte size LE][FlatBuffer][4-byte size LE][FlatBuffer]...
db.ingest(streamData);
// Query with SQL
const result = db.query('SELECT id, name, email FROM User WHERE age > 25');
console.log(result.columns); // ['id', 'name', 'email']
console.log(result.rows); // [[1, 'Alice', 'alice@example.com'], ...]
// Export database
const exported = db.exportData();
// Cleanup
db.destroy();
`
A TypeScript implementation for environments where WASM isn't available:
`typescript
import { FlatSQLDatabase, FlatcAccessor } from 'flatsql';
import { FlatcRunner } from 'flatc-wasm';
const flatc = await FlatcRunner.init();
const schema =
namespace App;
table User {
id: int (key);
name: string (required);
email: string;
age: int;
};
const accessor = new FlatcAccessor(flatc, schema);
const db = FlatSQLDatabase.fromSchema(schema, accessor, 'myapp');
// Insert records
db.insert('User', { id: 1, name: 'Alice', email: 'alice@example.com', age: 30 });
db.insert('User', { id: 2, name: 'Bob', email: 'bob@example.com', age: 25 });
// Query
const result = db.query('SELECT name, email FROM User WHERE age > 20');
console.log(result.rows);
// Export as standard FlatBuffers
const exported = db.exportData();
`
For performance-critical applications, link the C++ library directly:
`cpp
#include
auto db = flatsql::FlatSQLDatabase::fromSchema(schema, "mydb");
// Register file ID routing
db.registerFileId("USER", "User");
// Set field extractor
db.setFieldExtractor("User", extractUserField);
// Ingest streaming data
size_t recordsIngested = 0;
db.ingest(data, length, &recordsIngested);
// Query
auto result = db.query("SELECT * FROM User WHERE id = 5");
for (size_t i = 0; i < result.rowCount(); i++) {
std::cout << result.getString(i, "name") << std::endl;
}
`
`
┌─────────────────────────────────────────────────────────────┐
│ FlatSQLDatabase │
├─────────────────────────────────────────────────────────────┤
│ SchemaParser │ SQLiteEngine │
│ (FlatBuffers IDL) │ (Virtual Tables) │
├─────────────────────────────────────────────────────────────┤
│ TableStore (per table) │
│ ┌────────────────────────────────────────────────────────┐ │
│ │ SQLite Indexes Field Extractors │ │
│ │ (id, email, timestamp) (getField callbacks) │ │
│ └────────────────────────────────────────────────────────┘ │
├─────────────────────────────────────────────────────────────┤
│ StackedFlatBufferStore (append-only) │
│ ┌──────────────────────────────────────────────────────┐ │
│ │ [Header][FB₁][FB₂][FB₃]... │ │
│ └──────────────────────────────────────────────────────┘ │
└─────────────────────────────────────────────────────────────┘
FlatSQL ingests size-prefixed FlatBuffer streams:
`
[4-byte size LE][FlatBuffer with file_id][4-byte size LE][FlatBuffer]...
The 4-byte file identifier in each FlatBuffer determines which table receives the record.
- SELECT with column selectionWHERE
- with =, <, >, <=, >=, BETWEEN, LIKE, AND, OR
- (ASC/DESC)LIMIT
- and OFFSET
- aggregate(id)
- Index-accelerated queries on and (key) columns
- JOIN (query one table at a time)GROUP BY
- , HAVING, most aggregatesINSERT
- , UPDATE, DELETE (use API methods instead)
- Subqueries, CTEs, window functions
FlatSQL outperforms traditional SQLite on query operations:
| Operation | FlatSQL | SQLite | Speedup |
|-----------|---------|--------|---------|
| Point query (by id) | 3.50 ms | 3.93 ms | 1.1x |
| Point query (by key) | 5.23 ms | 6.94 ms | 1.3x |
| Direct index lookup | 1.56 ms | 3.93 ms | 2.5x |
| Full scan | 0.84 ms | 1.25 ms | 1.5x |
| Direct iteration | 0.05 ms | 1.25 ms | 25x |
Benchmarks: 10,000 records, 10,000 query iterations, Apple M3 Ultra
FlatSQL uses SQLite's virtual table (VTable) API to expose FlatBuffer data as queryable tables. This architecture enables SQL queries over raw binary data, but comes with fundamental trade-offs that affect performance characteristics.
FlatSQL provides three access paths with different performance profiles:
| Access Path | Latency | Throughput | Use Case |
|-------------|---------|------------|----------|
| Zero-Copy API | 1.7 µs | 580K ops/sec | Direct index lookup, returns raw FlatBuffer pointer |
| VTable SQL | 12.9 µs | 78K ops/sec | Full SQL queries via SQLite |
| Pure SQLite | 2.5 µs | 400K ops/sec | Baseline comparison |
SQLite's VTable API has fundamental limitations that prevent FlatSQL from matching pure SQLite query performance:
1. Per-Column Extraction — The xColumn() callback is invoked once per column per row. There is no batch API to extract multiple fields at once, meaning each field access has function call overhead.
2. Mandatory Value Conversion — All values must be converted to SQLite's internal format via sqlite3_result_*() functions. Even though FlatBuffers already store data in an efficient binary format, we must convert strings to SQLite strings, integers to SQLite integers, etc.
3. Row-by-Row Processing — The xNext() callback advances one row at a time with no vectorized or batch iteration. This prevents SIMD optimizations or processing multiple records per call.
4. No Direct Memory Access — SQLite cannot read FlatBuffer memory directly; all data must flow through the VTable callback interface, adding overhead for every field access.
Use the Zero-Copy API when:
- You need maximum throughput for point lookups
- You're building hot paths that query by indexed keys
- You can work directly with FlatBuffer data structures
`cpp`
// Zero-copy: returns pointer to raw FlatBuffer (1.7 µs)
const uint8_t* data = db.findRawByIndex("User", "email", email, &len);
auto user = GetUser(data); // Direct FlatBuffer access
Use VTable SQL when:
- You need complex queries (filtering, sorting, aggregation)
- Query flexibility is more important than raw speed
- You're doing ad-hoc exploration of the data
`cpp`
// VTable SQL: full query capability (12.9 µs)
auto result = db.query("SELECT * FROM User WHERE age > 25 ORDER BY name");
FlatSQL uses SQLite's highly optimized B-tree for indexing (not a custom implementation). This provides:
- Battle-tested performance — SQLite's B-tree is used by billions of devices
- Consistent behavior — Same indexing code path as pure SQLite
- Fast path optimization — Type-specific lookups bypass std::variant overhead
The index stores (key, sequence) → (offset, length) mappings, allowing O(log n) lookups that return pointers directly into the FlatBuffer storage.
| Aspect | FlatSQL | Pure SQLite |
|--------|---------|-------------|
| Point lookup (indexed) | 1.4x faster (zero-copy API) | Baseline |
| SQL queries | 5-7x slower (VTable overhead) | Baseline |
| Storage format | FlatBuffers (portable, zero-copy) | SQLite pages |
| Data conversion | None (zero-copy) or on-demand | Always required |
| Streaming ingest | Append-only, real-time indexing | Row-by-row inserts |
Bottom line: FlatSQL excels when you need streaming ingestion of FlatBuffer data with SQL query capability. Use the zero-copy API for performance-critical lookups; use SQL for complex queries where flexibility matters more than speed.
- Node.js 18+
- CMake 3.20+ (for WASM builds)
- Emscripten (for WASM builds)
`bash`
npm install
npm run build
npm test
`bashClone DA-FlatBuffers (required dependency)
git clone https://github.com/DigitalArsenal/flatbuffers.git ../flatbuffers
Output:
and wasm/flatsql.wasm$3
bash
npm run serve
Open http://localhost:8081
`API Reference
$3
typescript
import { initFlatSQL } from 'flatsql/wasm';const flatsql = await initFlatSQL();
`$3
typescript
const db = flatsql.createDatabase(schemaString, 'dbname');
`$3
typescript
db.registerFileId('USER', 'User'); // Route "USER" FlatBuffers to User table
`$3
typescript
const bytesConsumed = db.ingest(uint8ArrayStream);
// Or with source tagging (requires registerSource first):
const bytesConsumed = db.ingest(uint8ArrayStream, 'satellite-1');
`$3
typescript
const result = db.query('SELECT * FROM User WHERE age > 25');
// result.columns: string[]
// result.rows: any[][]
`$3
typescript
const data = db.exportData(); // Returns Uint8Array
`$3
typescript
db.registerSource('satellite-1'); // Creates User@satellite-1, Post@satellite-1, etc.
`$3
typescript
db.createUnifiedViews(); // Creates unified views with _source column
`$3
typescript
const sources = db.listSources(); // ['satellite-1', 'satellite-2', ...]
`Multi-Source Queries
FlatSQL supports federating multiple data sources with the same schema. Each source gets its own set of tables, and you can query them individually or across all sources.
$3
Imagine you have multiple satellites streaming telemetry data with the same schema:
javascript
// Register sources
db.registerSource('satellite-1');
db.registerSource('satellite-2');
db.registerSource('ground-station');// Register file IDs and extractors (must be done after registerSource)
db.registerFileId('TELE', 'Telemetry');
db.enableDemoExtractors();
// Create unified views (call once after all sources registered)
db.createUnifiedViews();
// Ingest from different sources
db.ingest(satellite1Stream, 'satellite-1');
db.ingest(satellite2Stream, 'satellite-2');
db.ingest(groundStream, 'ground-station');
// Query a specific source
db.query('SELECT * FROM "Telemetry@satellite-1" WHERE signal > 50');
// Query across all sources (unified view)
db.query('SELECT * FROM Telemetry WHERE timestamp > 1000');
`$3
- Source-specific tables:
(e.g., User@siteA, Telemetry@satellite-1)
- Unified views: TableName (e.g., User, Telemetry) - combines all source tables with a _source column$3
Unified views include a
_source column that identifies which source each row came from:`sql
-- See source for each record
SELECT _source, id, name FROM User LIMIT 10;-- Count records by source
SELECT _source, COUNT(*) as count FROM User GROUP BY _source;
-- Filter by source in unified view
SELECT * FROM User WHERE _source = 'satellite-1';
``Apache 2.0
Contributions welcome. Please open an issue first to discuss significant changes.
For questions, licensing inquiries, or commercial support: tj@digitalarsenal.io
---
Built on DA-FlatBuffers and SQLite.