Database Access

Direct access via the db object.

• db:get(coll, key) -> table|nil
  Retrieve a document by key.
• db:insert(coll, doc) -> string (key)
  Insert a new document. Generates UUID if _key is missing.
• db:update(coll, key, updates) -> bool
  Merge updates into existing document.
• db:replace(coll, key, doc) -> bool
  Completely replace a document.
• db:delete(coll, key) -> bool
  Delete a document.

Execute complex queries using SDBQL (SoliDB Query Language).

local results = db:query("FOR u IN users FILTER u.age > @age RETURN u", {
  age = 18
})

for _, user in ipairs(results) do
  solidb.log("User: " .. user.name)
end

Perform atomic operations across multiple collections using db:transaction. Transactions use row-level locking - each document is independently locked.

local success, err = db:transaction(function(tx)
  -- Get collection handle within transaction
  local accounts = tx:collection("accounts")
  
  -- Read operations acquire SHARED locks (multiple readers allowed)
  local sender = accounts:get("A")
  local receiver = accounts:get("B")

  if sender.balance < 100 then
    error("Insufficient funds") -- Will rollback automatically
  end

  -- Write operations acquire EXCLUSIVE locks
  accounts:update("A", { balance = sender.balance - 100 })
  accounts:update("B", { balance = receiver.balance + 100 })

  return "Transfer complete"
end)

if not success then
  solidb.log("Tx failed: " .. err)
end

Transaction API

• tx:collection(name) -> table
  Get a transactional collection handle.

Collection Methods within Transaction

• collection:get(key) -> table|nil
  Shared lock - Read a document. Multiple transactions can read simultaneously.
• collection:insert(doc) -> string (key)
  Exclusive lock - Insert new document.
• collection:update(key, doc) -> table
  Exclusive lock - Update existing document.
• collection:delete(key) -> bool
  Exclusive lock - Delete document.

Access collections directly via db:collection("name") for cleaner, scoped operations.

local users = db:collection("users")

-- CRUD operations
local user = users:get("alice")              -- Get by _key
local newKey = users:insert({name = "Bob"})    -- Insert, returns _key
users:update("alice", {age = 30})       -- Update existing
users:delete("alice")                     -- Delete document

-- Querying
local adults = users:find({age = {"$gte" = 18}})   -- Find with filter
local bob = users:find_one({name = "Bob"})       -- Find single document
local count = users:count()                   -- Count all documents

-- Bulk operations
users:bulk_insert({                         -- Insert multiple
  {name = "User1"},
  {name = "User2"},
  {name = "User3"}
})

users:upsert("user-key", {name = "Updated"})  -- Insert or update

Collection Methods

• collection:get(key) -> table|nil
  Get document by _key (not _id). Returns nil if not found.
• collection:insert(doc) -> string (key)
  Insert document. Uses _key if provided, or generates UUID v7.
• collection:update(key, doc) -> bool
  Update document by key. Merges fields with existing document.
• collection:delete(key) -> bool
  Delete document by key. Returns true on success.
• collection:count([filter]) -> number
  Count documents. Optional filter object for conditional counting.
• collection:find(filter) -> table[]
  Find all documents matching filter criteria.
• collection:find_one(filter) -> table|nil
  Find first document matching filter. Returns nil if not found.
• collection:bulk_insert(docs) -> table[]
  Insert array of documents efficiently. Returns array of inserted documents.
• collection:upsert(key_or_filter, doc) -> table
  Insert if not exists, update if exists. Accepts key string or filter table.