Advanced Features

Go beyond basic CRUD with soft deletes, scopes, batch operations, transactions, and raw queries.

Soft Delete

Mark records as deleted without removing them from the database. Soft-deleted records are excluded from queries by default and can be restored at any time.

app/models/post.sl

class Post < Model
    soft_delete
end

# Delete sets deleted_at timestamp
post.delete

# Restore clears deleted_at
post.restore

# Query without deleted records (default behavior)
let posts = Post.all

# Include soft-deleted records
let all = Post.with_deleted.all

# Query only deleted records
let deleted = Post.only_deleted.all

How it works: When soft_delete is declared, calling .delete on an instance sets a deleted_at timestamp instead of removing the document. All standard queries automatically filter out soft-deleted records unless you explicitly opt in with .with_deleted or .only_deleted.

Scopes

Define reusable query scopes in your model class to encapsulate common filters:

app/models/user.sl

class User < Model
    scope("active", "active = @a", { "a": true })
    scope("recent", "1 = 1", {})  # no filter, just for chaining
end

# Use scopes
let active = User.scope("active").all
let recent = User.scope("active").limit(10).all

Chainable: Scopes return a query builder, so you can chain them with .where(), .order(), .limit(), .includes(), and any other query builder method.

Batch Operations

Insert or update multiple records efficiently in a single operation:

# Batch create
let result = User.create_many([
    { "name": "Alice", "email": "[email protected]" },
    { "name": "Bob", "email": "[email protected]" },
    { "name": "Charlie", "email": "[email protected]" }
])
# Returns: { "created": 3 }

# Upsert (insert or update by ID)
User.upsert("user123", { "name": "Updated Name" })
# Updates if exists, inserts with ID if not

Performance: create_many sends all documents in a single database request, making it significantly faster than calling create in a loop. Use it when inserting large datasets.

Transactions

Execute multiple operations atomically within a database transaction. Use a transaction handle for manual control, or execute SDBQL directly:

Transaction Handle (Recommended)

# Get transaction handle
let tx = User.transaction()

# Perform operations within the transaction
tx.create({ name: "Alice", age: 30 })
tx.create({ name: "Bob", age: 25 })

# Commit all changes
tx.commit()
# Or tx.rollback() to undo all changes

# Also available: tx.get(key), tx.update(key, doc), tx.delete(key)

Transaction handle methods: tx.create(doc), tx.get(key), tx.update(key, doc), tx.delete(key), tx.commit(), tx.rollback(). All operations are buffered until commit() is called.

Execute SDBQL in Transaction

# Execute SDBQL in a transaction (auto-commits)
let result = User.transaction("
    INSERT { name: 'Alice', age: 30 } INTO users;
    INSERT { name: 'Bob', age: 25 } INTO users;
    RETURN users
")

Auto-commit: When passing a SDBQL string to transaction(), all statements are executed atomically and committed automatically. If any statement fails, the entire transaction is rolled back.

Raw Queries

When the ORM doesn't cover your use case, you can run raw SDBQL queries directly.

Using db.query()

Use db.query() with @param bind parameters for safe, parameterized queries:

# Read with named parameters
let results = db.query("FOR doc IN users FILTER doc.age >= @age RETURN doc", {
    "age": 18
})

# Insert
db.query("INSERT { name: @name, email: @email } INTO users", {
    "name": "Bob",
    "email": "[email protected]"
})

# Update
db.query("UPDATE @key WITH { name: @name } IN users", {
    "key": user_id,
    "name": "Alice Smith"
})

# Delete
db.query("REMOVE @key IN users", { "key": user_id })

Using @sdql{} Query Block

The @sdql{} syntax provides inline interpolation with #{expression} for more readable multi-line queries:

# Simple query with interpolation
let users = @sdql{
    FOR u IN users
    FILTER u.age >= #{age}
    RETURN u
}

# Multiple interpolations
let results = @sdql{
    FOR u IN users
    FILTER u.age >= #{min_age} AND u.city == #{city}
    SORT u.name ASC
    LIMIT #{limit}
    RETURN u
}

When to Use Each

Approach	Use Case
`Model.where(...)`	Standard CRUD, relations, eager loading — use the ORM first
`db.query()`	Parameterized queries with `@param` bind variables
`@sdql{}`	Inline interpolation with `#{expr}` for readable multi-line queries