Databases

Lua on Beans supports multiple database engines out of the box. Configure your database in config/database.json.

hub
ArangoDB

Multi-model graph database
storage
SQLite

Embedded SQL database
cloud
SurrealDB

NewSQL cloud database
analytics
CrateDB

Distributed SQL database
api
PostgREST

PostgreSQL REST API
swap_horiz
DB2REST

Any RDBMS via REST

settings Configuration

Database connections are configured in config/database.json. You can define multiple connections per environment. 

{
  "system": {
    "db_name": "_system",
    "url": "http://127.0.0.1:8529",
    "username": "root",
    "password": "password"
  },
  "development": [
    {
      "name": "primary",
      "engine": "arangodb",
      "db_name": "myapp_dev",
      "url": "http://127.0.0.1:8529",
      "username": "root",
      "password": "password"
    }
  ],
  "production": [
    {
      "name": "primary",
      "engine": "arangodb",
      "db_name": "myapp_prod",
      "url": "http://127.0.0.1:8529",
      "username": "root",
      "password": "password"
    }
  ],
  "test": [
    {
      "name": "primary",
      "engine": "arangodb",
      "db_name": "myapp_test",
      "url": "http://127.0.0.1:8529",
      "username": "root",
      "password": "password"
    }
  ]
}

info Initializing the Database

In your .init.lua, load the configuration and initialize: 

local db_config = DecodeJson(LoadAsset("config/database.json"))
InitDB(db_config)

hub ArangoDB

ArangoDB is a native multi-model database with flexible data models for documents, graphs, and key-values. It's the recommended database for Lua on Beans.

description

Configuration

config/database.json

{
  "name": "primary",
  "engine": "arangodb",
  "db_name": "myapp_dev",
  "url": "http://127.0.0.1:8529",
  "username": "root",
  "password": "password"
}

code Usage 

-- Access via Adb.primary (or your connection name)

-- Run AQL queries
local result = Adb.primary:Aql("FOR u IN users RETURN u")

-- With bind variables
local result = Adb.primary:Aql(
  "FOR u IN users FILTER u.email == @email RETURN u",
  { email = "john@example.com" }
)

-- Document operations
Adb.primary:CreateDocument("users", { name = "John", email = "john@example.com" })
Adb.primary:GetDocument("users/12345")
Adb.primary:UpdateDocument("users/12345", { name = "John Doe" })
Adb.primary:DeleteDocument("users/12345")

-- Collection operations
Adb.primary:CreateCollection("products")
Adb.primary:CreateCollectionWithTimestamps("orders")  -- Adds c_at, u_at fields
Adb.primary:GetCollection("users")
Adb.primary:RenameCollection("users", { name = "customers" })
Adb.primary:TruncateCollection("temp_data")
Adb.primary:DeleteCollection("temp_data")

-- Index operations
Adb.primary:GetAllIndexes("users")
Adb.primary:CreateIndex("users", { type = "hash", fields = { "email" }, unique = true })
Adb.primary:DeleteIndex("users/12345")

-- Transactions
local tx = Adb.primary:BeginTransaction({ collections = { write = { "users" } } })
-- ... perform operations with transaction_id ...
Adb.primary:CommitTransaction(tx.result.id)
-- or: Adb.primary:AbortTransaction(tx.result.id)

functions ArangoDB Methods

Method Description
Query
Aql(query, bindvars, options)Execute AQL query with bind variables
Raw_aql(stm)Execute raw AQL statement object
Documents
CreateDocument(collection, data, options)Create a document
GetDocument(handle)Get document by handle (collection/key)
UpdateDocument(handle, data, options)Update a document (PATCH)
DeleteDocument(handle)Delete a document
Collections
CreateCollection(name, options)Create a collection
CreateCollectionWithTimestamps(name, options)Create collection with c_at/u_at computed values
GetCollection(name)Get collection info
UpdateCollection(name, params)Update collection properties
RenameCollection(name, params)Rename a collection
TruncateCollection(name, params)Remove all documents from collection
DeleteCollection(name)Delete a collection
Databases
CreateDatabase(name, options, users)Create a new database
DeleteDatabase(name)Delete a database
Indexes
GetAllIndexes(collection)Get all indexes for a collection
CreateIndex(collection, params)Create an index
DeleteIndex(handle)Delete an index
Transactions
BeginTransaction(params)Begin a stream transaction
CommitTransaction(transaction_id)Commit a stream transaction
AbortTransaction(transaction_id)Abort a stream transaction
Transaction(params)Execute a JavaScript transaction
Query Cache
GetQueryCacheEntries()Get cached query entries
GetQueryCacheConfiguration()Get cache configuration
UpdateCacheConfiguration(params)Update cache configuration
DeleteQueryCache()Clear the query cache
User Defined Functions
CreateFunction(params)Create a custom AQL function
DeleteFunction(name)Delete a custom AQL function
ListFunctions()List all custom AQL functions
Authentication
Auth()Authenticate and get JWT token
RefreshToken()Refresh JWT token (auto-refreshes after 10min)

storage SQLite

SQLite is a C library that provides a lightweight, disk-based database. Perfect for development, testing, or small-scale applications.

description

Configuration

config/database.json

{
  "name": "primary",
  "engine": "sqlite",
  "db_name": "myapp_dev"
}

Creates myapp_dev.sqlite3 file in your project root.

code Usage 

-- Access via Sqlite.primary (or your connection name)

-- Execute SQL
Sqlite.primary:exec([[
  CREATE TABLE IF NOT EXISTS users (
    id INTEGER PRIMARY KEY,
    name TEXT,
    email TEXT UNIQUE
  )
]])

-- Query with callback
local users = {}
Sqlite.primary:exec("SELECT * FROM users", function(udata, cols, values, names)
  local user = {}
  for i = 1, cols do
    user[names[i]] = values[i]
  end
  table.insert(users, user)
  return 0
end)

-- Prepared statements
local stmt = Sqlite.primary:prepare("INSERT INTO users (name, email) VALUES (?, ?)")
stmt:bind_values("John", "john@example.com")
stmt:step()
stmt:finalize()

info SQLite Performance

Lua on Beans automatically configures SQLite with WAL journal mode and NORMAL synchronous for better performance.

cloud SurrealDB

SurrealDB is a NewSQL cloud database, suitable for serverless applications, jamstack sites, and traditional backends.

description

Configuration

config/database.json

{
  "name": "primary",
  "engine": "surrealdb",
  "db_name": "myapp_dev",
  "ns": "myapp",
  "db": "main",
  "url": "http://127.0.0.1:8000",
  "username": "root",
  "password": "root"
}

code Usage 

-- Access via Surreal.primary (or your connection name)

-- Execute SurrealQL
local result = Surreal.primary:surreal_sql("SELECT * FROM users")

-- Create records
Surreal.primary:surreal_sql("CREATE users SET name = 'John', email = 'john@example.com'")

-- Query with filters
local users = Surreal.primary:surreal_sql("SELECT * FROM users WHERE email = 'john@example.com'")

-- Don't forget to refresh token for long-running apps
Surreal.primary:refresh_token()

analytics CrateDB

CrateDB is a distributed SQL database built on top of a NoSQL foundation. Great for time-series, IoT, and analytics workloads.

description

Configuration

config/database.json

{
  "name": "primary",
  "engine": "crate",
  "url": "http://127.0.0.1:4200"
}

code Usage 

-- Access via Crate.primary (or your connection name)

-- Execute SQL via HTTP API
local result = Crate.primary:sql("SELECT * FROM users")

api PostgREST

PostgREST serves a fully RESTful API from any existing PostgreSQL database. Use it when you already have a PostgreSQL database.

description

Configuration

config/database.json

{
  "name": "primary",
  "engine": "postgrest",
  "url": "http://127.0.0.1:3000"
}

code Usage 

-- Access via PGRest.primary (or your connection name)

-- GET request to fetch data
local users = PGRest.primary:get("/users")

-- POST to create
PGRest.primary:post("/users", { name = "John", email = "john@example.com" })

-- PATCH to update
PGRest.primary:patch("/users?id=eq.1", { name = "John Doe" })

-- DELETE
PGRest.primary:delete("/users?id=eq.1")

swap_horiz DB2REST

DB2REST provides a REST API for any relational database (MySQL, PostgreSQL, MariaDB, SQL Server, Oracle, etc.).

description

Configuration

config/database.json

{
  "name": "primary",
  "engine": "db2rest",
  "db_name": "db",
  "url": "http://127.0.0.1:7070/v1/rdbms/"
}

code Usage 

-- Access via Rest.primary (or your connection name)

-- REST operations on your RDBMS tables
local users = Rest.primary:get("/users")
Rest.primary:post("/users", { name = "John" })
Rest.primary:patch("/users/1", { name = "John Doe" })
Rest.primary:delete("/users/1")

memory Redis (Cache)

Redis is supported as a caching layer. See the Features documentation for Redis usage.

lightbulb

Pro Tips

Multiple Connections: You can define multiple database connections with different names and access them via Adb.secondary, Sqlite.analytics, etc.

Environment-Specific: Configure different databases per environment (development, production, test) in database.json.

Token Refresh: For long-running applications, call :RefreshToken() or :refresh_token() in OnHttpRequest() to maintain database connections.

Migrations: Use beans db:migrate to run database migrations. Migrations live in the migrations/ folder.

arrow_back AQL Pages CLI Commands arrow_forward