Database

Availability: Server Only

MAEngine uses SurrealDB for persistent storage with a comprehensive Lua API. The DB table provides a synchronous-first API with transparent caching, automatic batching, and real-time synchronization via live queries.

Architecture Overview

Caching System

The cache is transparent - you don't need to manage it manually. Reads check cache first, writes update cache immediately, and live queries keep it synchronized with external changes.

How Caching Works

Operation	Cache Behavior
`DB.Get()`	Returns from cache instantly if present; fetches from DB on miss and caches result
`DB.Set()`	Updates cache immediately, then blocks until DB confirms
`DB.Create()`	Blocks until DB returns new ID, then caches with ID
`DB.Delete()`	Removes from cache immediately, then blocks until DB confirms
`DB.Find()`	Fetches from DB, caches all returned records
`DB.Preload()`	Loads entire table into cache at startup

Cache Structure

Cache (HashMap<String, HashMap<String, JsonValue>>)
├── "players"
│   ├── "player:abc123" → { name: "Alice", score: 100 }
│   └── "player:def456" → { name: "Bob", score: 75 }
├── "items"
│   ├── "item:sword01" → { name: "Iron Sword", damage: 25 }
│   └── "item:shield01" → { name: "Wooden Shield", defense: 10 }
└── "inventories"
    └── ...

Preloading for Instant Access

Call DB.Preload() during server start to warm the cache:

Server.Subscribe("Start", function()
    local player_count = DB.Preload("players")
    local item_count = DB.Preload("items")
    Log("Preloaded " .. player_count .. " players, " .. item_count .. " items")
end)

Only preload tables you'll access frequently. Large tables consume memory and increase startup time.

Live Query Synchronization

When you access a table, MAEngine automatically subscribes to SurrealDB's live queries. This means:

External changes are synced - If another server instance or admin tool modifies data, your cache updates automatically
No stale data - The cache stays consistent with the database
Zero configuration - Happens transparently on first table access

API Reference

Synchronous API (Default)

Use these for most operations. They use cache for instant reads and block only when necessary.

Asynchronous API

Use when you explicitly want non-blocking behavior. Perfect for operations during player disconnects.

Write Batching System

Fire-and-forget operations (DB.SetAsync without callback) are automatically batched for optimal performance.

Manual Batch Control

-- Flush all pending writes immediately
local count = DB.FlushWrites()
Log("Flushed " .. count .. " pending writes")

-- Check pending write count
local pending = DB.PendingWriteCount()
Log("Pending writes: " .. pending)

Batching is automatic. Use DB.FlushWrites() only when you need immediate persistence (e.g., before server shutdown).

Handling Write Failures

Fire-and-forget writes can fail silently (network issues, schema violations, etc.). Register a callback to handle failures:

-- Subscribe to write failures
local unsubscribe = DB.OnWriteFailure(function(failure)
    Warn("DB write failed: " .. failure.operation .. " on " .. failure.table .. ":" .. failure.id)
    Warn("Error: " .. failure.error)

    -- failure.data contains the original data (for set/update operations)
    if failure.data then
        -- Retry, log, or handle as needed
        DB.SetAsync(failure.table, failure.id, failure.data)  -- Retry
    end
end)

-- Later, if needed:
unsubscribe()

Prop

Type

Write failures are rare but can happen during DB connection issues or schema violations. For critical data, use synchronous writes (DB.Set) which return success/failure immediately.

Graph Relations

SurrealDB supports graph-style relations between records. Use these for complex data relationships.

Transactions

Execute multiple operations atomically. If any operation fails, all are rolled back.

local results = DB.Begin()
    :Update("players", "1", { gold = "gold - 100" })
    :Update("players", "2", { gold = "gold + 100" })
    :Create("trade_log", {
        from = "player:1",
        to = "player:2",
        amount = 100,
        timestamp = GetUnixTime()
    })
    :Commit()

Transaction Methods

Method	Description
`:Create(table, data)`	Add a new record
`:Update(table, id, data)`	Merge with existing (supports SurrealQL expressions)
`:Set(table, id, data)`	Replace entire record
`:Merge(table, id, data)`	Deep merge with existing
`:Delete(table, id)`	Delete a record
`:Relate(from, rel, to, data?)`	Create a relation
`:Commit()`	Execute all operations atomically

Transaction :Update() supports SurrealQL expressions like "gold - 100" for atomic arithmetic operations.

Live Subscriptions

Subscribe to real-time database changes. Callbacks fire when records are created, updated, or deleted by external sources (other server instances, admin tools).

local sub_id = DB.Subscribe("players", "Create", function(record)
    Log("New player joined: " .. record.name)
end)

DB.Subscribe("players", "Update", function(record)
    Log("Player updated: " .. record.name)
end)

DB.Subscribe("players", "Delete", function(record)
    Log("Player left: " .. record.id)
end)

-- Unsubscribe when done
DB.Unsubscribe(sub_id)

Local Write Filtering

By default, subscriptions ignore your own writes - only external changes trigger callbacks. This prevents infinite loops and unnecessary processing.

-- Default: only fires for external changes
DB.Subscribe("players", "Update", function(record)
    Log("External update to: " .. record.id)
end)

-- Opt-in: also fires for your own writes
DB.Subscribe("players", "Update", function(record)
    Log("Any update to: " .. record.id)
end, true)  -- includeLocal = true

Prop

Type

Use subscriptions for reacting to external changes (multi-server sync, admin tools). For your own writes, you already know when they happen - just handle it in the same code that makes the write.

Schema Definition

Define table structure for validation, default values, and indexing.

Utility Methods

Method	Returns	Description
`DB.Count(table, filter?)`	integer	Count matching records
`DB.Exists(table, id)`	boolean	Check if record exists
`DB.DeleteAll(table)`	integer	Delete all records, return count
`DB.Info()`	table	Get database information
`DB.FlushWrites()`	integer	Flush pending async writes
`DB.PendingWriteCount()`	integer	Get pending write count
`DB.OnWriteFailure(callback)`	function	Subscribe to write failures, returns unsubscribe fn

Type Preservation

MAEngine automatically preserves Vec3 and Quat types when storing to the database:

-- Storing Vec3
DB.Set("waypoints", "spawn", {
    position = Vec3(100, 200, 50),
    rotation = Quat(0, 0, 0, 1)
})

-- Retrieved as proper types
local waypoint = DB.Get("waypoints", "spawn")
local pos = waypoint.position  -- Returns Vec3 userdata
Log("X: " .. pos.x)            -- Works!

Internally stored as:

{
    "position": { "x": 100, "y": 200, "z": 50, "__type": "Vec3" },
    "rotation": { "x": 0, "y": 0, "z": 0, "w": 1, "__type": "Quat" }
}

Best Practices

Preload at Startup

Load frequently accessed tables into cache during server start:

Server.Subscribe("Start", function()
    DB.Preload("players")
    DB.Preload("items")
    DB.Preload("config")
end)

Use Fire-and-Forget for Disconnects

When players disconnect, use async without callback for optimal batching:

Player.Subscribe("Destroy", function(player)
    -- Fire-and-forget: updates cache, batches DB write
    DB.SetAsync("players", tostring(player:GetId()), {
        score = player:GetValue("score"),
        last_seen = GetUnixTime()
    })
end)

Use Transactions for Atomic Operations

When multiple records must change together:

-- Trading: both updates succeed or both fail
DB.Begin()
    :Update("players", seller_id, { gold = "gold + " .. price })
    :Update("players", buyer_id, { gold = "gold - " .. price })
    :Update("items", item_id, { owner = buyer_id })
    :Commit()

Index Frequently Queried Fields

Server.Subscribe("Start", function()
    DB.DefineIndex("players", "idx_score", { "score" })
    DB.DefineIndex("items", "idx_owner", { "owner_id" })
end)

Complete Example: Player Inventory System

-- Schema setup
Server.Subscribe("Start", function()
    DB.DefineTable("inventories", "schemafull")
    DB.DefineField("inventories", "player_id", "string")
    DB.DefineField("inventories", "items", "array", { default = "[]" })
    DB.DefineField("inventories", "gold", "int", { default = 0 })
    DB.DefineIndex("inventories", "idx_player", { "player_id" }, "unique")

    DB.Preload("inventories")
    Log("Inventory system ready")
end)

-- Load inventory
function LoadInventory(player)
    local id = tostring(player:GetId())
    local inv = DB.FindOne("inventories", { player_id = id })

    if not inv then
        -- Create new inventory
        local inv_id = DB.Create("inventories", {
            player_id = id,
            items = {},
            gold = 100  -- Starting gold
        })
        inv = DB.Get("inventories", inv_id)
    end

    return inv
end

-- Save inventory (non-blocking)
function SaveInventory(player, inventory)
    DB.SetAsync("inventories", inventory.id, inventory)
end

-- Add item with validation
function AddItem(player, item_id)
    local inv = player:GetPrivateValue("inventory")
    if #inv.items >= 50 then
        return false, "Inventory full"
    end

    table.insert(inv.items, item_id)
    player:SetPrivateValue("inventory", inv)
    SaveInventory(player, inv)
    return true
end

-- Player spawn
Player.Subscribe("Spawn", function(player)
    local inv = LoadInventory(player)
    player:SetPrivateValue("inventory", inv)
end)

-- Player disconnect (fire-and-forget save)
Player.Subscribe("Destroy", function(player)
    local inv = player:GetPrivateValue("inventory")
    if inv then
        SaveInventory(player, inv)
    end
end)

Consistency Contract

Understanding the exact behavior of each method prevents common bugs:

Method	Cache Behavior	Blocking	On Failure
`DB.Get`	Cache-first, DB on miss	Yes	Returns `nil`
`DB.Set`	Optimistic update	Yes	Rollback cache, returns `false`
`DB.Update`	Optimistic merge	Yes	Rollback cache, returns `false`
`DB.Delete`	Optimistic remove	Yes	Rollback cache, returns `false`
`DB.Create`	Cache on success	Yes	Returns `nil`
`DB.SetAsync` (no callback)	Optimistic update	No	Fires `DB.OnWriteFailure` callback
`DB.Query`	Bypasses cache	Yes	Returns empty array
`DB.Find`	Caches results	Yes	Returns empty array

Optimistic + Rollback: Synchronous writes update the cache immediately for instant reads. If the database rejects the write (constraint violation, connection issue), the cache is rolled back to its previous state. This ensures cache never gets out of sync with the database.

Live Query Sync: Tables you access are automatically subscribed to live queries. External changes (from other server instances or admin tools) update your cache in real-time.

Database

DB.Create(table, data) → id | nil

DB.Get(table, id) → data | nil

DB.Set(table, id, data) → id | nil

DB.Update(table, id, data) → bool

DB.Delete(table, id) → bool

DB.Find(table, filter?, options?) → array

DB.FindOne(table, filter?) → data | nil

DB.Query(sql, bindings?) → array

DB.Preload(table) → count

DB.SetAsync(table, id, data, callback?)

DB.GetAsync(table, id, callback)

DB.CreateAsync(table, data, callback)

DB.DeleteAsync(table, id, callback?)

DB.QueryAsync(sql, callback) / DB.QueryAsync(sql, bindings, callback)

DB.FindAsync(table, filter?, callback)

DB.Relate(from, rel, to, data?) → id

DB.Unrelate(from, rel, to) → bool

DB.GetRelated(record, rel, direction?) → array

DB.GetRelation(from, rel, to) → data | nil

DB.DefineTable(table, mode?)

DB.DefineField(table, field, type, options?)

DB.DefineIndex(table, name, fields, type?)

DB.DefineEvent(table, name, when, then)

On this page