Stoolap

https://stoolap.io/docs
Admin
Stoolap is a high-performance embedded SQL database written in Rust, featuring ACID transactions...
Tokens:185,206
Snippets:2,252
Trust Score:4.3
Update:4 months ago
Show doc for...
Tokens:
Raw
### Quick Start Example

Source: https://stoolap.io/docs/drivers/csharp

A basic example demonstrating how to open an in-memory database, create a table, insert data, and query it using the Stoolap .NET driver.

```csharp
using Stoolap;

using var db = Database.OpenInMemory();

db.Execute(@"\n    CREATE TABLE users (\n        id INTEGER PRIMARY KEY,\n        name TEXT NOT NULL,\n        email TEXT\n    )\n");

db.Execute("INSERT INTO users (id, name, email) VALUES (?, ?, ?)",
    1, "Alice", "alice@example.com");

var result = db.Query("SELECT id, name, email FROM users ORDER BY id");
foreach (var row in result.Rows)
{
    long id = (long)row[0]!;
    string name = (string)row[1]!;
    string? email = row[2] as string;
    Console.WriteLine($"{id} {name} {email}");
}

```

--------------------------------

### Quick Start: Database Operations

Source: https://stoolap.io/docs/drivers/swift

A quick start guide demonstrating basic database operations including opening an in-memory database, creating a table, inserting data, and querying records by name and ID.

```swift
import Stoolap

let db = try Database.open(":memory:")

try db.exec("""
    CREATE TABLE users (
        id INTEGER PRIMARY KEY,
        name TEXT NOT NULL,
        email TEXT
    )
""")

try db.execute(
    "INSERT INTO users (id, name, email) VALUES ($1, $2, $3)",
    [.integer(1), .text("Alice"), .text("alice@example.com")]
)

let users = try db.query("SELECT * FROM users ORDER BY id")
for user in users {
    print(user["name"]?.stringValue ?? "")
}

let one = try db.queryOne("SELECT * FROM users WHERE id = $1", [.integer(1)])
print(one?["email"]?.stringValue ?? "")

```

--------------------------------

### Complete Example: Multiple Aggregates and Data Setup

Source: https://stoolap.io/docs/functions/aggregate-functions

This example demonstrates creating sample data and performing multiple aggregate calculations in a single query, including counts, sums, averages, and string aggregation.

```sql
-- Create sample data
CREATE TABLE orders (
    id INTEGER PRIMARY KEY,
    customer TEXT,
    category TEXT,
    amount FLOAT,
    order_date TEXT
);

INSERT INTO orders VALUES
(1, 'Alice', 'Electronics', 1200.00, '2024-01-15'),
(2, 'Bob', 'Electronics', 800.00, '2024-01-16'),
(3, 'Alice', 'Clothing', 150.00, '2024-01-17'),
(4, 'Charlie', 'Electronics', 450.00, '2024-01-18'),
(5, 'Bob', 'Clothing', 75.00, '2024-01-19');

-- Multiple aggregates in one query
SELECT
    category,
    COUNT(*) as order_count,
    COUNT(DISTINCT customer) as unique_customers,
    SUM(amount) as total_sales,
    AVG(amount) as avg_order,
    MIN(amount) as smallest_order,
    MAX(amount) as largest_order,
    STRING_AGG(customer, ', ') as customers
FROM orders
GROUP BY category
ORDER BY total_sales DESC;

```

--------------------------------

### Quick Start: Create, Insert, and Query Data

Source: https://stoolap.io/docs/drivers/java

A basic example demonstrating how to open an in-memory database, create a table, insert data using positional parameters, and query the results.

```java
import io.stoolap.StoolapDB;
import io.stoolap.internal.BulkDecoder;

try (StoolapDB db = StoolapDB.openInMemory()) {
    db.execute("""
        CREATE TABLE users (
            id INTEGER PRIMARY KEY,
            name TEXT NOT NULL,
            email TEXT
        )
    """);

    // Insert with positional parameters ($1, $2, ...)
    db.execute(
        "INSERT INTO users (id, name, email) VALUES ($1, $2, $3)",
        1L, "Alice", "alice@example.com"
    );

    // Query rows — result contains column names and row data
    BulkDecoder.Result users = db.query("SELECT * FROM users ORDER BY id");
    for (Object[] row : users.rows()) {
        long id = (Long) row[0];
        String name = (String) row[1];
        String email = (String) row[2];
        System.out.println(id + " " + name + " " + email);
    }
}

```

--------------------------------

### Quick Start: Stoolap C API Example

Source: https://stoolap.io/docs/drivers/c

A basic C program demonstrating opening an in-memory database, creating a table, inserting data, querying, and iterating through results using the Stoolap C API.

```c
#include "stoolap.h"
#include <stdio.h>

int main(void) {
    StoolapDB* db = NULL;
    StoolapRows* rows = NULL;

    /* Open an in-memory database */
    if (stoolap_open_in_memory(&db) != STOOLAP_OK) {
        fprintf(stderr, "Open failed: %s\n", stoolap_errmsg(NULL));
        return 1;
    }

    /* Create a table and insert data */
    stoolap_exec(db, "CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT, age INTEGER)", NULL);
    stoolap_exec(db, "INSERT INTO users VALUES (1, 'Alice', 30), (2, 'Bob', 25)", NULL);

    /* Query */
    if (stoolap_query(db, "SELECT id, name, age FROM users ORDER BY id", &rows) != STOOLAP_OK) {
        fprintf(stderr, "Query failed: %s\n", stoolap_errmsg(db));
        stoolap_close(db);
        return 1;
    }

    /* Iterate rows */
    while (stoolap_rows_next(rows) == STOOLAP_ROW) {
        int64_t id  = stoolap_rows_column_int64(rows, 0);
        const char* name = stoolap_rows_column_text(rows, 1, NULL);
        int64_t age = stoolap_rows_column_int64(rows, 2);
        printf("id=%lld name=%s age=%lld\n", (long long)id, name, (long long)age);
    }

    stoolap_rows_close(rows);
    stoolap_close(db);
    return 0;
}

```

--------------------------------

### Install Go Driver

Source: https://stoolap.io/docs/drivers/go

Install the Stoolap Go driver using go get. Prebuilt shared libraries are bundled for common platforms.

```go
go get github.com/stoolap/stoolap-go

```

--------------------------------

### Example: Creating a Table and Showing Schema Information

Source: https://stoolap.io/docs/sql-commands/show-commands

This example demonstrates creating a table with constraints, adding indexes, and then using SHOW CREATE TABLE and SHOW INDEXES to inspect the schema.

```sql
CREATE TABLE orders (
    id INTEGER PRIMARY KEY AUTO_INCREMENT,
    customer_id INTEGER NOT NULL,
    amount FLOAT DEFAULT 0,
    status TEXT CHECK (status IN ('pending', 'shipped', 'delivered'))
);

CREATE INDEX idx_customer ON orders(customer_id);
CREATE INDEX idx_status ON orders(status);

-- View the table structure
SHOW CREATE TABLE orders;

-- List all indexes
SHOW INDEXES FROM orders;
```

--------------------------------

### Stoolap PHP Driver Quick Start

Source: https://stoolap.io/docs/drivers/php

A comprehensive example demonstrating the basic usage of the Stoolap PHP driver. It covers opening an in-memory database, creating a table, inserting data using both positional and named parameters, querying rows, fetching a single row, and retrieving raw columnar data.

```php
use Stoolap\Database;

$db = Database::open(':memory:');

$db->exec('CREATE TABLE users (
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL,
    email TEXT
)');

// Insert with positional parameters ($1, $2, ...)
$db->execute(
    'INSERT INTO users (id, name, email) VALUES ($1, $2, $3)',
    [1, 'Alice', 'alice@example.com']
);

// Insert with named parameters (:key)
$db->execute(
    'INSERT INTO users (id, name, email) VALUES (:id, :name, :email)',
    ['id' => 2, 'name' => 'Bob', 'email' => 'bob@example.com']
);

// Query rows as associative arrays
$users = $db->query('SELECT * FROM users ORDER BY id');
// [['id' => 1, 'name' => 'Alice', 'email' => 'alice@example.com'], ...]

// Query single row
$user = $db->queryOne('SELECT * FROM users WHERE id = $1', [1]);
// ['id' => 1, 'name' => 'Alice', 'email' => 'alice@example.com']

// Query in raw columnar format (faster, no per-row key creation)
$raw = $db->queryRaw('SELECT id, name FROM users ORDER BY id');
// ['columns' => ['id', 'name'], 'rows' => [[1, 'Alice'], [2, 'Bob']]]

$db->close();

```

--------------------------------

### Install Stoolap Studio

Source: https://stoolap.io/docs/getting-started/stoolap-studio

Clone the repository, navigate to the directory, and install dependencies using npm. Requires Node.js 18 or later.

```bash
git clone https://github.com/stoolap/stoolap-studio.git
cd stoolap-studio
npm install

```

--------------------------------

### Complete Stoolap Example: CRUD and More

Source: https://stoolap.io/docs/getting-started/api-reference

A comprehensive example demonstrating schema creation, batch inserts with cached plans, querying data into structs, performing aggregations, and using window functions.

```rust
use stoolap::{Database, FromRow, ResultRow, Result, named_params, params};

struct User {
    id: i64,
    name: String,
    email: Option<String>,
}

impl FromRow for User {
    fn from_row(row: &ResultRow) -> Result<Self> {
        Ok(User {
            id: row.get(0)?,
            name: row.get(1)?,
            email: row.get(2)?,
        })
    }
}

fn main() -> Result<()> {
    let db = Database::open("file:///tmp/myapp.db")?;

    // Schema
    db.execute("\n        CREATE TABLE IF NOT EXISTS users (\n            id INTEGER PRIMARY KEY AUTO_INCREMENT,\n            name TEXT NOT NULL,\n            email TEXT,\n            created_at TIMESTAMP DEFAULT NOW()\n        )\n    ", ())?;

    db.execute("CREATE INDEX IF NOT EXISTS idx_email ON users(email)", ())?;

    // Batch insert with cached plan
    let plan = db.cached_plan("INSERT INTO users (name, email) VALUES ($1, $2)")?;
    db.execute_plan(&plan, ("Alice", "alice@example.com"))?;
    db.execute_plan(&plan, ("Bob", "bob@example.com"))?;

    // Query with struct mapping
    let users: Vec<User> = db.query_as("SELECT id, name, email FROM users", ())?;
    for user in &users {
        println!("{}: {} ({:?})", user.id, user.name, user.email);
    }

    // Aggregation
    let count: i64 = db.query_one("SELECT COUNT(*) FROM users", ())?;
    println!("Total users: {}", count);

    // Window function
    for row in db.query("\n        SELECT name, ROW_NUMBER() OVER (ORDER BY created_at) as row_num\n        FROM users\n    ", ())? {
        let row = row?;
        let name: String = row.get_by_name("name")?;
        let num: i64 = row.get_by_name("row_num")?;
        println!("{}: {}", num, name);
    }

    db.close()?;
    Ok(())
}


```

--------------------------------

### LEAD() Function Examples

Source: https://stoolap.io/docs/functions/window-functions

Demonstrates the LEAD function to access values from following rows. The first example gets the next row's salary, and the second retrieves the salary from two rows ahead with a default value of 0.

```sql
-- Get next row's salary
SELECT name, salary,
       LEAD(salary) OVER (ORDER BY salary) as next_salary
FROM employees;

```

```sql
-- Get salary from 2 rows ahead, with default of 0
SELECT name, salary,
       LEAD(salary, 2, 0) OVER (ORDER BY salary) as next2_salary
FROM employees;

```

--------------------------------

### Build and Install to Local Maven Repo

Source: https://stoolap.io/docs/drivers/java

Exports the path to the compiled library and then runs the Maven verify goal to build, test, and install the project to the local Maven repository.

```bash
export STOOLAP_LIB=$(pwd)/jni/target/release/libstoolap_jni.dylib  # or .so / .dll
mvn verify
```

--------------------------------

### v0.3.7 DSN Example

Source: https://stoolap.io/docs/development/migration-v037-to-v040

Example of a connection string for Stoolap v0.3.7 using snapshot-based storage.

```sql
file:///data/mydb?sync=normal&snapshot_interval=300&keep_snapshots=5

```

--------------------------------

### Integer Series Examples for GENERATE_SERIES

Source: https://stoolap.io/docs/functions/table-valued-functions

Demonstrates generating integer sequences with various start, stop, and step values, including ascending, descending, and negative ranges.

```sql
-- Basic ascending series
SELECT * FROM generate_series(1, 5);
-- Returns: 1, 2, 3, 4, 5
```

```sql
-- With explicit step
SELECT * FROM generate_series(0, 10, 2);
-- Returns: 0, 2, 4, 6, 8, 10
```

```sql
-- Descending with negative step
SELECT * FROM generate_series(5, 1, -1);
-- Returns: 5, 4, 3, 2, 1
```

```sql
-- Auto-detect descending (no step needed)
SELECT * FROM generate_series(5, 1);
-- Returns: 5, 4, 3, 2, 1
```

```sql
-- Negative range
SELECT * FROM generate_series(-3, 3);
-- Returns: -3, -2, -1, 0, 1, 2, 3
```

```sql
-- Single value (start equals stop)
SELECT * FROM generate_series(3, 3);
-- Returns: 3
```

--------------------------------

### Build and Run Stoolap Studio in Production

Source: https://stoolap.io/docs/getting-started/stoolap-studio

Builds the production-ready application and starts the production server.

```bash
npm run build
npm start

```

--------------------------------

### Install and Test Node.js Driver

Source: https://stoolap.io/docs/drivers/nodejs

Clone the repository, install dependencies, and run tests for the Node.js driver.

```bash
git clone https://github.com/stoolap/stoolap-node.git
cd stoolap-node
npm install
npm test

```

--------------------------------

### v0.4.0 DSN Example

Source: https://stoolap.io/docs/development/migration-v037-to-v040

Example of a connection string for Stoolap v0.4.0 using volume-based storage. New parameter names are recommended.

```sql
file:///data/mydb?sync_mode=normal&checkpoint_interval=60&compact_threshold=4

```

--------------------------------

### Build and Install Stoolap Java Driver

Source: https://stoolap.io/docs/drivers/java

Instructions to build the native library and Java artifact from source, then install it to the local Maven repository. Requires Rust and Maven.

```bash
git clone https://github.com/stoolap/stoolap-java.git
cd stoolap-java

# 1. Build the native library (requires Rust)
cd jni && cargo build --release && cd ..

# 2. Build the Java artifact
mvn package

# 3. Install to your local Maven repo
mvn install

```

--------------------------------

### Persistence Example

Source: https://stoolap.io/docs/drivers/ruby

Demonstrates how to create and persist data in a file-based Stoolap database and then reopen it to retrieve the data.

```ruby
Stoolap::Database.open("./mydata") do |db|
  db.exec("CREATE TABLE kv (key TEXT PRIMARY KEY, value TEXT)")
  db.execute("INSERT INTO kv VALUES ($1, $2)", ["hello", "world"])
end

# Reopen: data is still there
Stoolap::Database.open("./mydata") do |db|
  db.query_one("SELECT value FROM kv WHERE key = $1", ["hello"])
  # => {"value" => "world"}
end
```

--------------------------------

### Expression Pushdown Example

Source: https://stoolap.io/docs/performance/optimization

This example demonstrates how filters and projections can be pushed down to the storage layer for efficient data processing. Only necessary data is retrieved.

```sql
-- Filter and projection will be pushed down to the storage layer
SELECT name, price FROM products WHERE price > 100;
```

--------------------------------

### EXPLAIN: Sorting Example

Source: https://stoolap.io/docs/sql-features/explain

Demonstrates the EXPLAIN plan for a query that includes an ORDER BY clause.

```sql
EXPLAIN SELECT * FROM products ORDER BY value DESC;

```

--------------------------------

### SQL INSERT Statement Examples

Source: https://stoolap.io/docs/sql-commands/sql-commands

Demonstrates practical examples of the INSERT statement, including basic insertion, multiple rows, using the RETURNING clause, and various upsert scenarios.

```sql
-- Basic insertion
INSERT INTO customers (id, name, email)
VALUES (1, 'John Doe', 'john@example.com');

-- Multiple rows
INSERT INTO products (id, name, price) VALUES
(1, 'Laptop', 1200.00),
(2, 'Smartphone', 800.00),
(3, 'Tablet', 500.00);

-- With RETURNING clause
INSERT INTO users (name, email)
VALUES ('Alice', 'alice@example.com')
RETURNING id, name;

-- Upsert with ON CONFLICT (PostgreSQL-style)
INSERT INTO inventory (product_id, quantity)
VALUES (101, 50)
ON CONFLICT (product_id) DO UPDATE SET
  quantity = quantity + EXCLUDED.quantity;

-- Skip duplicates
INSERT INTO items VALUES (1, 'apple')
ON CONFLICT DO NOTHING;

-- Bulk upsert from another table
INSERT INTO metrics (host, metric, value)
SELECT host, metric, value FROM staging
ON CONFLICT (host, metric) DO UPDATE SET value = EXCLUDED.value;
```

--------------------------------

### Install PHP Extension from Prebuilt Binaries

Source: https://stoolap.io/docs/drivers/php

Steps to install the Stoolap PHP extension and shared library from a downloaded archive. Ensure the shared library is copied to the system's library path and the extension is placed in the PHP extension directory.

```bash
tar xzf stoolap-v0.4.0-php8.4-linux-x86_64.tar.gz
cd stoolap-v0.4.0-php8.4-linux-x86_64

# Install the shared library
sudo cp libstoolap.so /usr/local/lib/
sudo ldconfig  # Linux only

# Install the PHP extension
sudo cp stoolap.so $(php-config --extension-dir)/

```

--------------------------------

### SQL COPY FROM CSV Examples

Source: https://stoolap.io/docs/sql-commands/sql-commands

Shows how to import data from CSV files into a table. Examples include importing with headers, using pipe delimiters, importing into specific columns, and handling custom NULL markers.

```sql
-- Import with header row (columns matched by name)
COPY users FROM '/data/users.csv' WITH (FORMAT CSV, HEADER true);

```

```sql
-- Pipe-delimited file
COPY logs FROM '/data/logs.txt' WITH (FORMAT CSV, DELIMITER '|');

```

```sql
-- No header, import into specific columns (positional)
COPY products (id, name, price) FROM '/data/products.csv'
WITH (FORMAT CSV, HEADER false);

```

```sql
-- Custom NULL marker
COPY sensors FROM '/data/readings.csv'
WITH (FORMAT CSV, HEADER true, NULL 'NA');

```

--------------------------------

### WAL File Structure Example

Source: https://stoolap.io/docs/architecture/persistence

Illustrates the directory structure and naming convention for WAL log files.

```string
/path/to/database/
  wal/
    wal_000001.log
    wal_000002.log
    ...

```

--------------------------------

### Install the WASM driver

Source: https://stoolap.io/docs/drivers/go-wasm

Install the pure Go WASM driver for Stoolap using the go get command.

```go
go get github.com/stoolap/stoolap-go/wasm

```

--------------------------------

### NTILE(n) Example

Source: https://stoolap.io/docs/functions/window-functions

Distributes rows into a specified number of buckets (n) based on the ordering within the partition. This example divides employees into 3 salary groups.

```sql
SELECT name, salary,
       NTILE(3) OVER (ORDER BY salary DESC) as tertile
FROM employees;

```

--------------------------------

### File Persistence Setup

Source: https://stoolap.io/docs/drivers/go-wasm

Demonstrates how to create a Stoolap WASM engine with filesystem access for persistent databases and open a file-based database connection.

```go
engine, _ := wasm.NewEngineWithFS(ctx, wasmBytes, "/path/to/data")
db, _ := engine.Open(ctx, "file:///data/mydb")

```

--------------------------------

### Persistent Storage Examples

Source: https://stoolap.io/docs/getting-started/connection-strings

Illustrates various file paths for persistent storage.

```plaintext
file:///data/mydb
```

```plaintext
file:///Users/username/stoolap/data
```

```plaintext
file:///tmp/test_db
```

--------------------------------

### Creating a Table and Describing its Structure

Source: https://stoolap.io/docs/sql-commands/describe

Shows how to create a sample 'products' table and then use the DESCRIBE command to view its schema, including column details like type, nullability, and primary key.

```sql
CREATE TABLE products (
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL,
    price FLOAT,
    active BOOLEAN DEFAULT TRUE
);

DESCRIBE products;
```

--------------------------------

### Direct API Transaction

Source: https://stoolap.io/docs/drivers/go

Example of starting a transaction, executing a statement, and committing it using the Direct API.

```go
tx, err := db.Begin(ctx)
if err != nil {
    panic(err)
}

tx.ExecParams(ctx, "INSERT INTO users VALUES ($1, $2)",
    []any{int64(1), "Alice"})

if err := tx.Commit(); err != nil {
    panic(err)
}

```

--------------------------------

### Install PHP Extension from Source

Source: https://stoolap.io/docs/drivers/php

Compile and install the Stoolap PHP extension from source. This requires a C compiler and the stoolap shared library. Ensure you configure with the correct path to the stoolap library.

```bash
cd ext
phpize
./configure --with-stoolap=/path/to/libstoolap
make
sudo make install

```

--------------------------------

### SQL Example: Creating and Populating Sales Table

Source: https://stoolap.io/docs/sql-features/rollup-cube

Sets up a sample sales table with data for demonstrating aggregation functions.

```sql
CREATE TABLE sales (
    id INTEGER PRIMARY KEY,
    region TEXT,
    category TEXT,
    amount FLOAT
);

INSERT INTO sales VALUES
(1, 'East', 'Electronics', 100),
(2, 'East', 'Electronics', 150),
(3, 'East', 'Clothing', 50),
(4, 'West', 'Electronics', 200),
(5, 'West', 'Clothing', 75),
(6, 'West', 'Clothing', 60);
```

--------------------------------

### SQL COPY FROM JSON Examples

Source: https://stoolap.io/docs/sql-commands/sql-commands

Demonstrates importing data from JSON files into a table, supporting both JSON arrays and JSON Lines formats. Examples show importing specific columns and handling unmatched keys.

```sql
-- JSON array: [{"id": 1, "name": "Alice"}, {"id": 2, "name": "Bob"}]
COPY users FROM '/data/users.json' WITH (FORMAT JSON);

```

```sql
-- JSON Lines: {"id": 1, "name": "Alice"}
--             {"id": 2, "name": "Bob"}
COPY users FROM '/data/users.jsonl' WITH (FORMAT JSON);

```

```sql
-- Only import specific columns (unmatched JSON keys are ignored)
COPY users (id, name) FROM '/data/users.json' WITH (FORMAT JSON);

```

--------------------------------

### Setup and Join Tables

Source: https://stoolap.io/docs/getting-started/quickstart

Creates a 'categories' table, inserts sample data, modifies the 'products' table to include a foreign key, and then joins 'products' with 'categories' to retrieve combined information.

```sql
-- Create categories table
CREATE TABLE categories (
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL,
    description TEXT
);

-- Add some categories
INSERT INTO categories (id, name, description) VALUES
(1, 'Electronics', 'Electronic devices and gadgets'),
(2, 'Accessories', 'Peripherals and accessories for devices');

-- Update products to use category ids
ALTER TABLE products ADD COLUMN category_id INTEGER;
UPDATE products SET category_id = 1 WHERE category = 'Electronics';
UPDATE products SET category_id = 2 WHERE category = 'Accessories';

-- Join tables to get category information
SELECT p.id, p.name, p.price, c.name AS category_name, c.description AS category_description
FROM products p
JOIN categories c ON p.category_id = c.id;

```

--------------------------------

### LAG() Function Examples

Source: https://stoolap.io/docs/functions/window-functions

Demonstrates the LAG function to access values from previous rows. The first example gets the previous row's salary, and the second retrieves the salary from two rows back with a default value of 0.

```sql
-- Get previous row's salary
SELECT name, salary,
       LAG(salary) OVER (ORDER BY salary) as prev_salary
FROM employees;

```

```sql
-- Get salary from 2 rows back, with default of 0
SELECT name, salary,
       LAG(salary, 2, 0) OVER (ORDER BY salary) as prev2_salary
FROM employees;

```

--------------------------------

### EXPLAIN: Aggregation Example

Source: https://stoolap.io/docs/sql-features/explain

Shows the EXPLAIN output for a query that uses the GROUP BY clause for aggregation.

```sql
EXPLAIN SELECT category, SUM(value) FROM products GROUP BY category;

```

--------------------------------

### Vector Utility Functions

Source: https://stoolap.io/docs/data-types/vector-search

Examples of using utility functions to get vector dimensions, compute L2 norm (magnitude), and convert vectors to text representation.

```sql
-- Check dimensions
SELECT VEC_DIMS(embedding) FROM embeddings WHERE id = 1;
-- Returns: 384

-- Compute vector magnitude
SELECT VEC_NORM(embedding) FROM embeddings WHERE id = 1;
-- Returns: 1.0 (for normalized vectors)

-- Display vector as text
SELECT VEC_TO_TEXT(embedding) FROM embeddings WHERE id = 1;
-- Returns: [0.1, 0.2, 0.3, ...]
```

--------------------------------

### Build from Source

Source: https://stoolap.io/docs/drivers/go

Instructions for cloning the repository and running tests to build the stoolap Go driver from source.

```bash
git clone https://github.com/stoolap/stoolap-go.git
cd stoolap-go
go test -v ./...
```

--------------------------------

### Quick Start: database/sql Driver

Source: https://stoolap.io/docs/drivers/go

Illustrates using the Stoolap Go driver with the standard database/sql package for database operations.

```go
package main

import (
    "context"
    "database/sql"
    "fmt"

    _ "github.com/stoolap/stoolap-go"
)

func main() {
    db, err := sql.Open("stoolap", "memory://")
    if err != nil {
        panic(err)
    }
    defer db.Close()

    ctx := context.Background()

    db.ExecContext(ctx, "CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT, age INTEGER)")
    db.ExecContext(ctx, "INSERT INTO users VALUES (1, 'Alice', 30), (2, 'Bob', 25)")

    rows, _ := db.QueryContext(ctx, "SELECT id, name, age FROM users ORDER BY id")
    defer rows.Close()

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

```

--------------------------------

### Build and Run MCP Server

Source: https://stoolap.io/docs/drivers/mcp

Instructions for cloning the repository, installing dependencies, building the project, and running the server.

```bash
git clone https://github.com/stoolap/stoolap-mcp.git
cd stoolap-mcp
npm install
npm run build
node build/index.js --path ./mydata

```

--------------------------------

### Install wasm-pack for WebAssembly Builds

Source: https://stoolap.io/docs/development/building

Installs the wasm-pack tool, which is required for building Stoolap to WebAssembly. Can be installed via Cargo or npm.

```shell
# Install wasm-pack
cargo install wasm-pack

# Or via npm
npm install -g wasm-pack

```

--------------------------------

### Quick Start: Direct API

Source: https://stoolap.io/docs/drivers/go

Demonstrates basic usage of the Stoolap Go driver's Direct API for creating tables, inserting data, and querying records.

```go
package main

import (
    "context"
    "fmt"

    "github.com/stoolap/stoolap-go"
)

func main() {
    db, err := stoolap.Open("memory://")
    if err != nil {
        panic(err)
    }
    defer db.Close()

    ctx := context.Background()

    db.Exec(ctx, "CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT, age INTEGER)")
    db.Exec(ctx, "INSERT INTO users VALUES (1, 'Alice', 30), (2, 'Bob', 25)")

    rows, _ := db.Query(ctx, "SELECT id, name, age FROM users ORDER BY id")
    defer rows.Close()

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

```

--------------------------------

### Install Stoolap using Cargo

Source: https://stoolap.io/docs/getting-started/installation

Use this command to install the Stoolap CLI via Cargo. It downloads, compiles, and installs the binary to your system's Cargo bin directory.

```bash
cargo install stoolap

```

--------------------------------

### Install PHP Extension using PIE

Source: https://stoolap.io/docs/drivers/php

Install the Stoolap PHP extension using the PIE package manager. This method requires the stoolap shared library to be already installed on your system.

```bash
pie install stoolap/stoolap-php

```

--------------------------------

### Install Stoolap Gem

Source: https://stoolap.io/docs/drivers/ruby

Install the Stoolap gem using the RubyGems package manager.

```bash
gem install stoolap
```

--------------------------------

### Run Stoolap Studio in Development

Source: https://stoolap.io/docs/getting-started/stoolap-studio

Starts the development server for Stoolap Studio. Access the application at http://localhost:3000.

```bash
npm run dev

```

--------------------------------

### Install stoolap-python

Source: https://stoolap.io/docs/drivers/python

Install the stoolap-python driver using pip. Requires Python 3.9 or higher.

```bash
pip install stoolap-python

```

--------------------------------

### Build Stoolap with Feature Flags

Source: https://stoolap.io/docs/development/building

Demonstrates building Stoolap with various feature flags. Use '--release' for optimized builds. Examples include default builds, library-only builds, enabling mimalloc, SQLite support, FFI, and minimal builds.

```shell
# Default build (CLI + parallel)
cargo build --release

# Library only (no CLI)
cargo build --release --no-default-features --features parallel

# With mimalloc allocator
cargo build --release --features mimalloc

# With SQLite comparison support
cargo build --release --features sqlite

# C FFI shared library (libstoolap.so / .dylib / .dll)
cargo build --release --features ffi

# Minimal build (no CLI, no parallel)
cargo build --release --no-default-features

```

--------------------------------

### Build Stoolap Python Driver from Source

Source: https://stoolap.io/docs/drivers/python

Instructions for cloning the repository, setting up a virtual environment, installing dependencies, and developing the driver locally using maturin.

```bash
git clone https://github.com/stoolap/stoolap-python.git
cd stoolap-python
python -m venv .venv && source .venv/bin/activate
pip install maturin pytest pytest-asyncio
maturin develop --release
pytest
```

--------------------------------

### Verify Stoolap CLI Installation

Source: https://stoolap.io/docs/getting-started/installation

Run this command in your terminal to verify that the Stoolap CLI has been installed correctly by checking its version.

```bash
stoolap --version

```

--------------------------------

### Database Configuration with Query Parameters

Source: https://stoolap.io/docs/drivers/python

Illustrates how to configure database connection options by appending query parameters to the database path. Examples include setting sync modes, buffer sizes, and compression settings.

```python
# Maximum durability (fsync on every WAL write)
db = Database.open('./mydata?sync=full')

# High throughput (no fsync, larger buffers)
db = Database.open('./mydata?sync=none&wal_buffer_size=131072')

# Custom checkpoint interval with compression
db = Database.open('./mydata?checkpoint_interval=60&compression=on')

# Multiple options
db = Database.open(
    './mydata?sync=full&checkpoint_interval=120&compact_threshold=8&wal_max_size=134217728'
)

```

--------------------------------

### Quick Start: Database Operations

Source: https://stoolap.io/docs/drivers/nodejs

Demonstrates basic database operations including opening an in-memory database, creating a table, inserting data using positional and named parameters, querying rows, querying a single row, and querying in raw columnar format. Remember to close the database when done.

```javascript
const db = await Database.open(':memory:');

await db.exec(`
  CREATE TABLE users (
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL,
    email TEXT
  )
`);

// Insert with positional parameters ($1, $2, ...)
await db.execute(
  'INSERT INTO users (id, name, email) VALUES ($1, $2, $3)',
  [1, 'Alice', 'alice@example.com']
);

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

// Query rows as objects
const users = await db.query('SELECT * FROM users ORDER BY id');
// [{ id: 1, name: 'Alice', email: 'alice@example.com' }, ...]

// Query single row
const user = await db.queryOne('SELECT * FROM users WHERE id = $1', [1]);
// { id: 1, name: 'Alice', email: 'alice@example.com' }

// Query in raw columnar format (faster, no per-row object creation)
const raw = await db.queryRaw('SELECT id, name FROM users ORDER BY id');
// { columns: ['id', 'name'], rows: [[1, 'Alice'], [2, 'Bob']] }

await db.close();


```

--------------------------------

### Install Node.js Driver

Source: https://stoolap.io/docs/drivers/nodejs

Install the Stoolap Node.js driver using npm. A C compiler is required for building the N-API addon.

```bash
npm install @stoolap/node

```

--------------------------------

### Aggregation Optimization Example

Source: https://stoolap.io/docs/performance/query-execution

Shows an example of aggregation optimization. Stoolap uses optimized algorithms for GROUP BY operations to improve performance.

```sql
-- Uses optimized aggregation algorithms
SELECT department, AVG(salary), COUNT(*)
FROM employees
GROUP BY department;

```

--------------------------------

### Query Optimizer Examples

Source: https://stoolap.io/docs/architecture/indexing

Demonstrates how to view query execution plans that include index usage and how to collect statistics for the query optimizer.

```sql
-- View query plan including index usage
EXPLAIN SELECT * FROM orders WHERE amount > 100;

-- Collect statistics for optimizer
ANALYZE orders;
```

--------------------------------

### VECTOR Data Type Definition and Examples

Source: https://stoolap.io/docs/data-types/data-types

Defines a VECTOR column with a specified dimension and provides examples for inserting vector data.

```SQL
-- Column definition with dimension count
CREATE TABLE embeddings (
    id INTEGER PRIMARY KEY,
    content TEXT,
    embedding VECTOR(384)
);

-- Example values (bracket-delimited float arrays)
INSERT INTO embeddings VALUES (1, 'Hello', '[0.1, 0.2, 0.3, ...]');
INSERT INTO embeddings VALUES (2, 'World', '[0.4, 0.5, 0.6, ...]');

```

--------------------------------

### Opening Database and Recovery Example

Source: https://stoolap.io/docs/architecture/persistence

Demonstrates how to open a Stoolap database, which automatically triggers the recovery process if necessary. The database is then ready for operations.

```rust
// Opening automatically triggers recovery if needed
let db = Database::open("file:///path/to/database")?;

// Database is ready with all committed data restored
let results = db.query("SELECT * FROM users", ())?;

```

--------------------------------

### BOOLEAN Data Type Definition and Examples

Source: https://stoolap.io/docs/data-types/data-types

Defines a BOOLEAN column and provides examples for inserting true/false values, demonstrating case-insensitivity.

```SQL
-- Column definition
CREATE TABLE example (
    id INTEGER PRIMARY KEY,
    is_active BOOLEAN,
    is_deleted BOOLEAN
);

-- Example values
INSERT INTO example VALUES (1, true, false);
INSERT INTO example VALUES (2, FALSE, TRUE);  -- Case-insensitive

```

--------------------------------

### Install Rust Toolchain

Source: https://stoolap.io/docs/development/building

Installs the Rust toolchain using rustup. Ensure you have the latest stable version (edition 2021, Rust 1.56+).

```shell
curl --proto '=https' --tlsv1.2 -sSf https://sh.rustup.rs | sh

```

--------------------------------

### Connection String Parameters

Source: https://stoolap.io/docs/sql-commands/pragma-commands

Example of setting PRAGMA values via connection string parameters for automatic configuration on connection.

```url
file:///path/to/db?checkpoint_interval=60&compact_threshold=4&keep_snapshots=3&sync_mode=normal
```

--------------------------------

### Predicate Optimization Example

Source: https://stoolap.io/docs/performance/query-execution

Shows how predicates are rewritten and simplified for better performance. This example demonstrates automatic simplification to a single range scan.

```sql
-- Automatically simplified to a single range scan
SELECT * FROM products WHERE price >= 10 AND price <= 20;

```

--------------------------------

### Browser Quick Start (ES Module)

Source: https://stoolap.io/docs/drivers/wasm

Import and initialize the WASM module, then create and use a StoolapDB instance directly in the browser.

```html
<script type="module">
  // Import the WASM module and the init function
  const wasm = await import('./assets/wasm/stoolap.js');

  // Initialize the WASM binary (fetches and compiles stoolap_bg.wasm)
  await wasm.default();

  // Create a database instance
  const db = new wasm.StoolapDB();

  db.execute("CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT)");
  db.execute("INSERT INTO users VALUES (1, 'Alice')");

  const result = JSON.parse(db.execute("SELECT * FROM users"));
  console.log(result.columns); // ["id", "name"]
  console.log(result.rows);    // [[1, "Alice"]]
</script>

```

--------------------------------

### Persistence Example: Create, Insert, and Query File-Based DB

Source: https://stoolap.io/docs/drivers/java

Demonstrates creating a table, inserting data, and querying it from a file-based Stoolap database, showing data persistence across restarts.

```java
try (StoolapDB db = StoolapDB.open("file:///tmp/mydata")) {
    db.execute("CREATE TABLE kv (key TEXT PRIMARY KEY, value TEXT)");
    db.execute("INSERT INTO kv VALUES ($1, $2)", "hello", "world");
}

// Reopen: data is still there
try (StoolapDB db = StoolapDB.open("file:///tmp/mydata")) {
    BulkDecoder.Result result = db.query("SELECT * FROM kv WHERE key = $1", "hello");
    System.out.println(result.rows().get(0)[1]); // "world"
}

```

--------------------------------

### NTH_VALUE() Example

Source: https://stoolap.io/docs/functions/window-functions

Returns the nth value within the partition. This example retrieves the second highest salary within the entire employee set.

```sql
SELECT name, salary,
       NTH_VALUE(salary, 2) OVER (ORDER BY salary DESC) as second_highest
FROM employees;

```

--------------------------------

### JSON Data Type Definition and Examples

Source: https://stoolap.io/docs/data-types/data-types

Defines a JSON column and provides examples for inserting JSON objects, arrays, and nested structures.

```SQL
-- Column definition
CREATE TABLE example (
    id INTEGER PRIMARY KEY,
    data JSON
);

-- Example values
INSERT INTO example VALUES (1, '{"name": "John", "age": 30}');
INSERT INTO example VALUES (2, '[1, 2, 3, 4, 5]');
INSERT INTO example VALUES (3, '{"nested": {"a": 1, "b": 2}, "array": [1, 2, 3]}');

```

--------------------------------

### Connection String with Configuration Options

Source: https://stoolap.io/docs/getting-started/connection-strings

Demonstrates how to set configuration options like sync mode and checkpoint interval directly within the connection string.

```plaintext
file:///path/to/data?sync_mode=normal&checkpoint_interval=60
```

--------------------------------

### ON CONFLICT DO NOTHING Example

Source: https://stoolap.io/docs/sql-features/on-duplicate-key-update

An example demonstrating the ON CONFLICT DO NOTHING clause to prevent duplicate entries in an items table. A duplicate insert is silently skipped.

```sql
CREATE TABLE items (id INTEGER PRIMARY KEY, name TEXT);

INSERT INTO items VALUES (1, 'apple');
INSERT INTO items VALUES (2, 'banana');

-- Silently skip the duplicate
INSERT INTO items VALUES (1, 'cherry')
ON CONFLICT DO NOTHING;
-- Result: 2 rows (apple, banana), cherry was skipped
```

--------------------------------

### Building Stoolap PHP Extension from Source

Source: https://stoolap.io/docs/drivers/php

Provides the commands to clone the Stoolap PHP repository, navigate to the extension directory, prepare the build environment with phpize, configure the build with the Stoolap library path, and compile the extension.

```bash
git clone https://github.com/stoolap/stoolap-php.git
cd stoolap-php/ext
phpize
./configure --with-stoolap=/path/to/libstoolap
make

```

--------------------------------

### Window Function Example

Source: https://stoolap.io/docs/performance/query-execution

Illustrates the use of window functions for analytical queries. This example calculates the rank of employees within their departments based on salary.

```sql
SELECT name, department, salary,
       RANK() OVER (PARTITION BY department ORDER BY salary DESC) as rank
FROM employees;

```

--------------------------------

### Using Parameters with Direct API and database/sql

Source: https://stoolap.io/docs/drivers/go-wasm

Illustrates how to use both positional parameters ($1, $2, etc.) with the direct API and database/sql driver for executing and querying data.

```go
// Direct API
db.ExecParams(ctx, "INSERT INTO users VALUES ($1, $2)", []any{int64(1), "Alice"})
rows, _ := db.QueryParams(ctx, "SELECT * FROM users WHERE id = $1", []any{int64(1)})

// database/sql
db.ExecContext(ctx, "INSERT INTO users VALUES ($1, $2)", 1, "Alice")
rows, _ := db.QueryContext(ctx, "SELECT * FROM users WHERE id = $1", 1)

```

--------------------------------

### FIRST_VALUE() Example

Source: https://stoolap.io/docs/functions/window-functions

Returns the first value within the partition, ordered by salary in descending order. This example finds the maximum salary within each department.

```sql
SELECT name, dept, salary,
       FIRST_VALUE(salary) OVER (PARTITION BY dept ORDER BY salary DESC) as max_in_dept
FROM employees;

```

--------------------------------

### UNIQUE Constraint Definition and Example

Source: https://stoolap.io/docs/data-types/data-types

Illustrates defining a UNIQUE constraint to ensure distinct values in a column and shows an example of a duplicate value rejection.

```SQL
CREATE TABLE users (
    id INTEGER PRIMARY KEY,
    email TEXT UNIQUE,
    username TEXT UNIQUE
);

-- Duplicate values will be rejected
INSERT INTO users VALUES (1, 'alice@test.com', 'alice');
INSERT INTO users VALUES (2, 'alice@test.com', 'bob');  -- Error: unique constraint failed

```

--------------------------------

### Build and test the Stoolap C# driver from source

Source: https://stoolap.io/docs/drivers/csharp

Steps to clone the repository, build the native library, and then build and test the C# driver. Requires Rust and the .NET 8 SDK or newer.

```bash
git clone https://github.com/stoolap/stoolap-csharp.git
cd stoolap-csharp

# Build the native library for the host platform.
# Auto-clones the stoolap engine at the pinned ref if no source is found.
./build/build-native.sh

# Build and test everything
dotnet build -c Release
dotnet test  -c Release

```

--------------------------------

### TEXT Data Type Definition and Examples

Source: https://stoolap.io/docs/data-types/data-types

Defines a TEXT column and provides examples for inserting simple text, Unicode characters, and special characters.

```SQL
-- Column definition
CREATE TABLE example (
    id INTEGER PRIMARY KEY,
    name TEXT,
    description TEXT
);

-- Example values
INSERT INTO example VALUES (1, 'Simple text', 'This is a longer description');
INSERT INTO example VALUES (2, 'Unicode: こんにちは', 'Special chars: !@#$%^&*()');

```

--------------------------------

### Basic SQL Savepoint Example

Source: https://stoolap.io/docs/sql-features/savepoints

Demonstrates creating a savepoint after an insert, rolling back to it after an incorrect update, and then applying the correct update before committing.

```sql
BEGIN TRANSACTION;

INSERT INTO accounts (id, balance) VALUES (1, 1000);
SAVEPOINT after_insert;

UPDATE accounts SET balance = 500 WHERE id = 1;
-- Oops, wrong update!

ROLLBACK TO SAVEPOINT after_insert;
-- Balance is back to 1000

UPDATE accounts SET balance = 900 WHERE id = 1;
-- Correct update

COMMIT;
-- Final balance: 900
```

--------------------------------

### Running the Benchmark

Source: https://stoolap.io/docs/drivers/csharp

Execute the Stoolap driver benchmark suite using the .NET CLI. Ensure you are in the project directory.

```bash
dotnet run --project benchmark/Stoolap.Benchmark.csproj -c Release

```

--------------------------------

### INTEGER Data Type Definition and Examples

Source: https://stoolap.io/docs/data-types/data-types

Defines an INTEGER column and provides examples for inserting maximum and minimum 64-bit signed integer values.

```SQL
-- Column definition
CREATE TABLE example (
    id INTEGER PRIMARY KEY,
    count INTEGER,
    large_number INTEGER
);

-- Example values
INSERT INTO example VALUES (1, 42, 9223372036854775807);  -- Max int64
INSERT INTO example VALUES (2, -100, -9223372036854775808);  -- Min int64

```

--------------------------------

### Partially Pushable Expressions Example

Source: https://stoolap.io/docs/architecture/expression-pushdown

Shows a query where a date function can be partially pushed down, improving efficiency. This example uses DATE_TRUNC to compare months.

```sql
SELECT * FROM orders WHERE 
    DATE_TRUNC('month', order_date) = DATE_TRUNC('month', NOW());
```

--------------------------------

### Quick Start: database/sql Driver Usage

Source: https://stoolap.io/docs/drivers/go-wasm

Shows how to register and use the Stoolap WASM driver with Go's standard database/sql package for interacting with an in-memory database.

```go
package main

import (
    "context"
    "database/sql"
    "fmt"
    "os"

    "github.com/stoolap/stoolap-go/wasm"
)

func main() {
    ctx := context.Background()
    wasmBytes, _ := os.ReadFile("stoolap.wasm")
    wasm.SetWASM(ctx, wasmBytes)

    db, _ := sql.Open("stoolap-wasm", "memory://")
    defer db.Close()

    db.ExecContext(ctx, "CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT)")
    db.ExecContext(ctx, "INSERT INTO users VALUES (1, 'Alice'), (2, 'Bob')")

    rows, _ := db.QueryContext(ctx, "SELECT id, name FROM users ORDER BY id")
    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)
    }
}

```

--------------------------------

### FLOAT Data Type Definition and Examples

Source: https://stoolap.io/docs/data-types/data-types

Defines a FLOAT column and provides examples for inserting floating-point numbers, including the maximum representable float64 value.

```SQL
-- Column definition
CREATE TABLE example (
    id INTEGER PRIMARY KEY,
    price FLOAT,
    temperature FLOAT
);

-- Example values
INSERT INTO example VALUES (1, 99.99, -273.15);
INSERT INTO example VALUES (2, 3.14159265359, 1.7976931348623157e+308);  -- Max float64

```

--------------------------------

### Named Windows Example

Source: https://stoolap.io/docs/functions/sql-functions-reference

Demonstrates the use of named windows to define a window specification once and reuse it across multiple window functions. This improves readability and maintainability.

```sql
SELECT
    ROW_NUMBER() OVER w,
    SUM(amount) OVER w
FROM orders
WINDOW w AS (PARTITION BY customer_id ORDER BY order_date);

```
Tokens:
Raw