stoolap-ruby

gem "stoolap"

require "stoolap"

# In-memory database
db = Stoolap::Database.open(":memory:")

# exec runs one or more DDL/DML statements (no parameters)
db.exec(<<~SQL)
  CREATE TABLE users (
    id    INTEGER PRIMARY KEY,
    name  TEXT NOT NULL,
    email TEXT
  );
  CREATE INDEX idx_users_name ON users(name);
SQL

# execute runs a single statement with parameters, returns rows affected
db.execute(
  "INSERT INTO users (id, name, email) VALUES ($1, $2, $3)",
  [1, "Alice", "alice@example.com"]
)

# Named parameters (:key) with either Symbol or String keys
db.execute(
  "INSERT INTO users (id, name, email) VALUES (:id, :name, :email)",
  { id: 2, name: "Bob", email: "bob@example.com" }
)

# query returns an Array of Hashes with String keys
users = db.query("SELECT * FROM users ORDER BY id")
# => [{"id" => 1, "name" => "Alice", ...}, ...]

# query_one returns a single Hash or nil
user = db.query_one("SELECT * FROM users WHERE id = $1", [1])
# => {"id" => 1, "name" => "Alice", "email" => "alice@example.com"}

# query_raw returns columnar format (skip the per-row Hash allocation)
raw = db.query_raw("SELECT id, name FROM users ORDER BY id")
# => {"columns" => ["id", "name"], "rows" => [[1, "Alice"], [2, "Bob"]]}

db.close

Stoolap::Database.open(":memory:") do |db|
  db.exec("CREATE TABLE t (id INTEGER PRIMARY KEY)")
  db.execute("INSERT INTO t VALUES ($1)", [1])
end
# db.close already ran, even if the block raised

# In-memory (three equivalent forms)
Stoolap::Database.open(":memory:")
Stoolap::Database.open("")
Stoolap::Database.open("memory://")

# File-backed (data persists across restarts)
Stoolap::Database.open("./mydata")
Stoolap::Database.open("file:///absolute/path/to/db")

# Positional
db.query("SELECT * FROM users WHERE id = $1 AND name = $2", [1, "Alice"])

# Named with Symbol keys
db.query("SELECT * FROM users WHERE id = :id AND name = :name",
         { id: 1, name: "Alice" })

# Named with String keys
db.query("SELECT * FROM users WHERE id = :id", { "id" => 1 })

db.query("SELECT * FROM users WHERE id = :id", { ":id" => 1 })
db.query("SELECT * FROM users WHERE id = :id", { "@id" => 1 })
db.query("SELECT * FROM users WHERE id = :id", { "$id" => 1 })

raw = db.query_raw("SELECT id, name, email FROM users ORDER BY id")
raw["columns"]  # => ["id", "name", "email"]
raw["rows"]     # => [[1, "Alice", "alice@example.com"], ...]

changes = db.execute_batch(
  "INSERT INTO users (id, name, email) VALUES ($1, $2, $3)",
  [
    [1, "Alice",   "alice@example.com"],
    [2, "Bob",     "bob@example.com"],
    [3, "Charlie", "charlie@example.com"]
  ]
)
# changes => 3

insert = db.prepare("INSERT INTO users (id, name) VALUES ($1, $2)")
insert.execute([1, "Alice"])
insert.execute([2, "Bob"])

# Batch on a prepared statement (reuses the cached plan)
insert.execute_batch([
  [3, "Charlie"],
  [4, "Diana"]
])

# Prepared reads
lookup = db.prepare("SELECT * FROM users WHERE id = $1")
lookup.query_one([1])   # => {"id" => 1, "name" => "Alice"}
lookup.query([1])       # => [{"id" => 1, "name" => "Alice"}]
lookup.query_raw([1])   # => {"columns" => [...], "rows" => [[...]]}

# Named parameters work the same
lookup_named = db.prepare("SELECT * FROM users WHERE id = :id")
lookup_named.query_one({ id: 1 })

db.transaction do |tx|
  tx.execute("INSERT INTO users (id, name) VALUES ($1, $2)", [1, "Alice"])
  tx.execute("INSERT INTO users (id, name) VALUES ($1, $2)", [2, "Bob"])

  # Reads within the tx see its own uncommitted writes
  rows = tx.query("SELECT * FROM users")
  one  = tx.query_one("SELECT * FROM users WHERE id = $1", [1])
  raw  = tx.query_raw("SELECT id, name FROM users")
end
# commit on clean exit, rollback on any raise

total = db.transaction do |tx|
  tx.query_one("SELECT COUNT(*) AS c FROM users")["c"]
end

tx = db.begin_transaction
begin
  tx.execute("INSERT INTO users (id, name) VALUES ($1, $2)", [1, "Alice"])
  tx.commit
rescue StandardError
  tx.rollback
  raise
end

tx = db.begin_transaction
tx.with_rollback do |t|
  t.execute("INSERT INTO users (id, name) VALUES ($1, $2)", [1, "Alice"])
end
# commit on success, rollback on raise

db.exec(<<~SQL)
  CREATE TABLE documents (
    id        INTEGER PRIMARY KEY,
    title     TEXT,
    embedding VECTOR(3)
  );
  CREATE INDEX idx_emb ON documents(embedding) USING HNSW WITH (metric = 'cosine');
SQL

db.execute(
  "INSERT INTO documents VALUES ($1, $2, $3)",
  [1, "Hello world", Stoolap::Vector.new([0.1, 0.2, 0.3])]
)
db.execute(
  "INSERT INTO documents VALUES ($1, $2, $3)",
  [2, "Goodbye world", Stoolap::Vector.new([0.9, 0.1, 0.0])]
)

# k-NN search: 5 nearest neighbours by cosine distance
results = db.query(<<~SQL)
  SELECT id, title,
         VEC_DISTANCE_COSINE(embedding, '[0.1, 0.2, 0.3]') AS dist
  FROM documents
  ORDER BY dist
  LIMIT 5
SQL

# Read vectors back as Array<Float>
row = db.query_one("SELECT embedding FROM documents WHERE id = 1")
row["embedding"]  # => [0.1, 0.2, 0.3]

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

# Maximum durability (fsync on every WAL write)
Stoolap::Database.open("./mydata?sync=full")

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

# Custom checkpoint interval with compression
Stoolap::Database.open("./mydata?checkpoint_interval=60&compression=on")

begin
  db.execute("SELEXT * FROM nothing")
rescue Stoolap::Error => e
  warn "database error: #{e.message}"
end

begin
  db.execute("INSERT INTO t VALUES ($1)", [Object.new])
rescue TypeError => e
  warn e.message
end