Stoolap Flutter SDK

The high-performance SQL database that ships with your app.

What's New in 2.0.0

• Batch Insert: 526K rows/sec with batchInsert() — prepared statement + single transaction.
• Prepared Statements: Parse once, execute many times with zero parse overhead.
• Named Parameters: Use :name syntax for readable, maintainable queries.
• Query Timeouts: Cancel queries that exceed time limits.
• Snapshot Isolation: Full MVCC transaction isolation levels.
• Semantic Search: Built-in EMBED() function for AI-powered search — no external APIs.
• Semantic Cache: Query result caching with stats and control.
• Performance: Fixed redundant FFI matching and wrapped batch execution in transactions.
• Full API Parity: 100% coverage of the Stoolap Rust API v0.4.0.

---

What is Stoolap?

Stoolap is a modern embedded SQL database that provides full ACID transactions with MVCC, a sophisticated cost-based query optimizer, and features that rival established databases like PostgreSQL and DuckDB. Built entirely in Rust with minimal unsafe code (only for FFI and performance-critical hot paths), Stoolap features multiple index types (B-tree, Hash, Bitmap, HNSW), native vector search with semantic search support, parallel query execution, and comprehensive SQL support including window functions, CTEs, and temporal queries.

Official Reference: https://stoolap.io/ Pub.dev: https://pub.dev/packages/stoolap_flutter

---

Features

• Pure Rust Core — Memory-safe and lightning-fast database engine.
• Native Vector Search — VECTOR type and HNSW indexes for sub-linear semantic search.
• Built-in Semantic Search — EMBED() function converts text to vectors inside Rust — no external APIs needed.
• Batch Insert — 526K rows/sec with batchInsert() using prepared statements.
• Parallel Execution — Automatically parallelizes joins, sorts, and aggregations across all CPU cores.
• MVCC Transactions — Full ACID compliance with snapshot isolation. Readers never block writers.
• Time-Travel Queries — Query your data exactly as it existed in the past using the AS OF syntax.
• Reactive API — Built-in support for "Live Queries" using Dart Streams.
• Advanced SQL — Supports CTEs (including Recursive), Window Functions, and standard SQL joins.
• Mobile Optimized — Optimized with SIMD for ARM64 (M1/M2 and modern Android chips).
• Prepared Statements — Parse SQL once, execute many times with zero parse overhead.
• Named Parameters — Use :name syntax for readable, maintainable queries.
• Query Timeouts — Cancel queries that exceed time limits.
• Isolation Levels — Choose between Read Committed and Snapshot isolation.

---

Quick Start

1. Installation

dependencies:
  stoolap_flutter: ^2.0.0

2. Initialization

import 'package:stoolap_flutter/stoolap_flutter.dart';

void main() async {
  WidgetsFlutterBinding.ensureInitialized();
  await StoolapDatabase.init();
  runApp(const MyApp());
}

3. Basic Usage

final db = StoolapDatabase();
await db.open('my_app_data.db');

// Execute DDL
await db.execute('CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT, email TEXT)');

// Insert with positional parameters
await db.execute('INSERT INTO users (name, email) VALUES (?, ?)', params: ['Alice', 'alice@example.com']);

// Insert with named parameters
await db.executeNamed(
  'INSERT INTO users (name, email) VALUES (:name, :email)',
  namedParams: {'name': 'Bob', 'email': 'bob@example.com'},
);

// Query
final rows = await db.query('SELECT * FROM users');
for (var row in rows) {
  print('User: ${row.values[1]}');
}

---

Advanced Features

Batch Insert (526K rows/sec)

The fastest way to insert many rows — prepared statement + single transaction:

final count = await db.batchInsert(
  'INSERT INTO users (name, age) VALUES (?, ?)',
  rows: ['Alice', 28, 'Bob', 34, 'Charlie', 22],
  columnsPerRow: 2,
);
// count = 3

Why it's fast:

1. SQL parsed once (prepared statement)
2. All inserts in one atomic transaction (one commit)
3. Minimal FFI overhead per row

Prepared Statements

Parse SQL once and execute many times:

final stmt = await db.prepare('INSERT INTO logs (level, message) VALUES (?, ?)');

for (var entry in logEntries) {
  await stmt.execute(params: [entry.level, entry.message]);
}

Named Parameters

Use readable :name placeholders:

await db.executeNamed(
  'INSERT INTO users (name, email, age) VALUES (:name, :email, :age)',
  namedParams: {'name': 'Charlie', 'email': 'charlie@example.com', 'age': 25},
);

Query Timeouts

Cancel queries that exceed time limits:

final results = await db.queryWithTimeout(
  'SELECT * FROM large_table WHERE complex_condition',
  timeout: Duration(seconds: 5),
);

Snapshot Isolation

Choose between isolation levels:

// Read Committed (default)
await db.begin();

// Snapshot — consistent view from BEGIN
await db.begin(isolation: 'snapshot');
await db.execute('SELECT * FROM accounts');
await db.commit();

Semantic Vector Search

Stoolap handles embeddings natively with built-in EMBED():

// Create table with vector support
await db.execute('''
  CREATE TABLE items (
    id INTEGER PRIMARY KEY,
    description TEXT,
    embedding VECTOR(384)
  )
''');

// Insert with auto-generated embedding
await db.execute(
  "INSERT INTO items (description, embedding) VALUES (?, EMBED(?))",
  params: ['high-performance database', 'high-performance database'],
);

// Semantic search
final results = await db.semanticSearch(
  table: 'items',
  embeddingColumn: 'embedding',
  query: 'fast SQL engine',
  limit: 5,
);

Reactive Live Queries

StreamBuilder<List<StoolapRow>>(
  stream: db.watch('SELECT * FROM users ORDER BY id DESC'),
  builder: (context, snapshot) {
    if (!snapshot.hasData) return const CircularProgressIndicator();
    final users = snapshot.data!;
    return ListView.builder(
      itemCount: users.length,
      itemBuilder: (context, index) => ListTile(
        title: Text(users[index].values[1].toString()),
      ),
    );
  },
);

Table Utilities

// Check if a table exists
if (await db.tableExists('users')) { /* ... */ }

// Create a point-in-time snapshot
await db.createSnapshot();

// Get engine version
final version = await StoolapDatabase.version();

// Semantic cache stats
final stats = await db.semanticCacheStats();
await db.clearSemanticCache();

---

Performance & Benchmarks

Batch Insert Benchmark

Method	10K Rows	Throughput
batchInsert()	19ms	526,316 rows/sec
Prepared stmt loop	119ms	8,403 rows/sec
Individual inserts	~2000ms	~5,000 rows/sec

Query Performance (vs SQLite & DuckDB)

Official benchmarks on Apple M1 Pro (16GB RAM, macOS 15). Source: Stoolap Benchmarks.

Operation	Stoolap	SQLite	DuckDB	Speedup
COUNT DISTINCT	0.37 us	105.98 us	219.91 us	286x
Subquery Compare	5.52 us	1424.07 us	293.51 us	258x
Aggregation (GROUP BY)	48.81 us	1403.39 us	104.32 us	29x
SELECT by ID	0.12 us	0.21 us	145.55 us	1.7x
Vector Search (k-NN)	4ms	N/A	N/A	Native

Mobile Performance Comparison

Feature	Stoolap (Rust)	SQLite (C)	Hive (Dart)	Isar (Dart)
Parallelism	Multi-core	Single	Single	Single
Concurrency	MVCC	Lock	Lock	Lock
Vector Index	Native HNSW	No	No	No
Optimizer	Cost-Based	Rule	No	No
Batch Insert	526K rows/sec	~5K	~5K	~5K
Prepared Statements	Yes	Yes	No	Yes
Named Parameters	Yes	No	No	No
Semantic Search	Built-in	No	No	No

---

Connection String Options

// File-based with options
await db.open('file:///data/app.db?sync=full&checkpoint_interval=60');

// In-memory (unique instance)
await db.open(':memory:');

// Named in-memory (shared engine)
await db.open('memory://mydb');

Parameter	Default	Description
sync	normal	Sync mode: none, normal, full
checkpoint_interval	60	Seconds between checkpoint cycles
wal_compression	on	LZ4 compression for WAL entries
compression	–	Sets both wal_compression and volume_compression
target_volume_rows	1048576	Target rows per cold volume

---

Documentation

• Rust Engine Docs — Full SQL reference and architecture.
• Vector Search Guide — Build semantic search.
• Semantic Search Guide — EMBED() function.
• Contributing — Help us build the future of mobile databases.

---

Join our community

We're building the most powerful embedded database for Flutter. Join us on GitHub to report issues, suggest features, or contribute code.

License

Licensed under the Apache License, Version 2.0. See LICENSE for details.