Skip to content

Latest commit

Β 

History

History
1071 lines (763 loc) Β· 32.4 KB

API.md

File metadata and controls

1071 lines (763 loc) Β· 32.4 KB

sqlite β€” Starlark API Reference

The complete reference for every script-facing builtin, object method, and configuration accessor exposed by the sqlite module. For an overview, installation, and a quickstart, see the README.

The module exposes three top-level builtins via load("sqlite", …) β€” connect, connect_remote, and register_function β€” plus a set of configuration accessors (get_<key> / set_<key>) generated from the module's options. A connection object then carries the database methods (query, execute, insert, transactions, …); libSQL remote connections expose the same object surface as local ones.

Contents

Connection management

connect(database?, timeout?, busy_timeout?, foreign_keys?, journal_mode?, synchronous?, cache_size?)

Creates a new local database connection with optional configuration. Any parameter omitted falls back to the corresponding module config option.

Parameters:

  • database (string): Database path or :memory: for in-memory (default: module config)
  • timeout (float): Per-operation deadline in seconds β€” each query/statement is bounded by it and cancels with the script thread. Omit or pass 0 to use the module default; set the module timeout config (or SQLITE_TIMEOUT) to 0 to disable the deadline.
  • busy_timeout (float): Busy timeout in seconds (default: module config)
  • foreign_keys (bool): Enable foreign key constraints (default: module config)
  • journal_mode (string): Journal mode (default: module config)
  • synchronous (string): Synchronous mode (default: module config)
  • cache_size (int): Cache size in pages (default: module config)

Returns: Database object for performing operations.

Example:

# Connect with defaults
db = connect()

# Connect to a file database with custom settings
db = connect(
    database="myapp.db",
    foreign_keys=True,
    journal_mode="WAL",
    synchronous="NORMAL",
    busy_timeout=10.0
)

connect_remote(url, auth_token?)

Connects to a remote libSQL server β€” a self-hosted sqld or Turso Cloud β€” over a pure-Go client (no cgo). The returned object exposes the same methods as a local connection (query, execute, insert, transactions, …), because libSQL speaks the SQLite dialect.

Parameters:

  • url (string): Server URL, e.g. libsql://my-db.turso.io, https://my-db.turso.io, or http://localhost:8080 for a local sqld.
  • auth_token (string, optional): Auth token for the server (omit for an unauthenticated local sqld).

Returns: Database object (same API as connect).

Errors: Fails if url is empty, if the connector cannot be created, or if the initial connection (ping) fails.

Example:

# Self-hosted sqld β€” e.g. docker run -p 8080:8080 ghcr.io/tursodatabase/libsql-server
db = connect_remote("http://localhost:8080")

# Turso Cloud (or any authenticated libSQL server)
db = connect_remote("libsql://my-db.turso.io", auth_token="...")

db.execute("CREATE TABLE IF NOT EXISTS notes (id INTEGER PRIMARY KEY, body TEXT)")
db.execute("INSERT INTO notes (body) VALUES (?)", ["hello from a remote db"])
rows = db.query("SELECT * FROM notes")
db.close()

A file: URL is a local connection and follows connect's file/function restrictions, module defaults and connection PRAGMAs. auth_token is unused for local files. HTTP(S), libSQL and WebSocket endpoints remain remote; gate their access at the network/credential layer.

close()

Closes the database connection.

Parameters: None

Returns: None

Example:

db.close()

Low-level SQL execution

execute(query, params?)

Executes a SQL statement with optional parameters.

Parameters:

  • query (string): SQL statement to execute
  • params (list): Optional list of parameters for the query

Returns: Number of affected rows (int).

Example:

# Create a table
rows_affected = db.execute("""
    CREATE TABLE users (
        id INTEGER PRIMARY KEY,
        name TEXT NOT NULL,
        email TEXT UNIQUE
    )
""")

# Insert with parameters
rows_affected = db.execute(
    "INSERT INTO users (name, email) VALUES (?, ?)",
    ["Alice", "alice@example.com"]
)

batch(queries)

Executes multiple SQL statements in a single transaction. If any operation fails, the entire batch is rolled back.

Parameters:

  • queries (list): List of queries to execute. Each item can be:
    • A string (SQL statement without parameters)
    • A list/tuple of [query, params] (SQL statement with parameters)

Returns: List of integers representing affected rows for each query.

Example:

# Simple batch with string queries
results = db.batch([
    "INSERT INTO users (name, email) VALUES ('Alice', 'alice@example.com')",
    "INSERT INTO users (name, email) VALUES ('Bob', 'bob@example.com')",
    "UPDATE users SET active = 1"
])

# Batch with parameterized queries
results = db.batch([
    ["INSERT INTO users (name, email) VALUES (?, ?)", ["Charlie", "charlie@example.com"]],
    ["UPDATE users SET last_login = ? WHERE id = ?", ["2023-08-15", 1]],
    ["DELETE FROM users WHERE active = ?", [0]]
])

# Mixed batch (some with params, some without)
results = db.batch([
    "CREATE INDEX idx_users_email ON users(email)",
    ["INSERT INTO users (name, email) VALUES (?, ?)", ["David", "david@example.com"]],
    "VACUUM"
])

query(query, params?)

Executes a SQL query and returns all results.

Parameters:

  • query (string): SQL query to execute
  • params (list): Optional list of parameters for the query

Returns: List of dictionaries representing rows. (Capped by max_rows when the host sets that limit β€” see Configuration.)

Example:

# Query all users
users = db.query("SELECT * FROM users")
for user in users:
    print(user["id"], user["name"], user["email"])

# Query with parameters
adult_users = db.query("SELECT * FROM users WHERE age >= ?", [18])

query_one(query, params?)

Executes a SQL query and returns the first row, or None if no rows are found.

Parameters:

  • query (string): SQL query to execute
  • params (list): Optional list of parameters for the query

Returns: Dictionary representing the first row, or None.

Example:

# Get a specific user
user = db.query_one("SELECT * FROM users WHERE id = ?", [1])
if user:
    print("Found user:", user["name"])
else:
    print("User not found")

Prepared statements

prepare(query)

Creates a prepared statement for repeated execution.

Parameters:

  • query (string): SQL statement to prepare

Returns: Prepared statement object.

Example:

# Create a prepared statement
stmt = db.prepare("INSERT INTO users (name, email) VALUES (?, ?)")

# Execute multiple times
stmt.execute(["Alice", "alice@example.com"])
stmt.execute(["Bob", "bob@example.com"])
stmt.execute(["Charlie", "charlie@example.com"])

# Close when done
stmt.close()

prepare_query(query)

Creates a prepared query statement for repeated querying.

Parameters:

  • query (string): SQL query to prepare

Returns: Prepared query statement object.

Example:

# Create a prepared query
query_stmt = db.prepare_query("SELECT * FROM users WHERE age > ?")

# Execute with different parameters
young_adults = query_stmt.query([18])
seniors = query_stmt.query([65])

# Close when done
query_stmt.close()

Prepared statement object methods

A prepared statement / prepared query object exposes the following methods.

execute(params?)

Executes a prepared statement with optional parameters.

  • params (list): Optional list of parameters
  • Returns: Number of affected rows (int).

query(params?)

Executes a prepared query statement with optional parameters.

  • params (list): Optional list of parameters
  • Returns: List of dictionaries representing rows.

query_one(params?)

Executes a prepared query statement and returns the first row.

  • params (list): Optional list of parameters
  • Returns: Dictionary representing the first row, or None.

close()

Closes the prepared statement.

  • Parameters: None
  • Returns: None

Transaction management

begin()

Begins a new transaction.

Parameters: None

Returns: Transaction object.

Example:

# Begin a transaction
tx = db.begin()

# Execute operations within the transaction with error handling
update1 = tx.execute("UPDATE accounts SET balance = balance - ? WHERE id = ?", [100, 1])
update2 = tx.execute("UPDATE accounts SET balance = balance + ? WHERE id = ?", [100, 2])

# Check if operations succeeded
if not update1.ok or not update2.ok:
    print("Transaction operations failed:")
    if not update1.ok:
        print("- Update 1 error:", update1.error)
    if not update2.ok:
        print("- Update 2 error:", update2.error)
    tx.rollback()
    fail("Transaction failed")

# Commit the transaction
commit_result = tx.commit()
if not commit_result.ok:
    print("Failed to commit transaction:", commit_result.error)
    fail("Commit failed")

print("Transfer successful")

Transaction object methods

Transaction methods return OperationResult objects for graceful error handling without script termination. Each result exposes:

  • result.ok (bool): Whether the operation succeeded
  • result.error (string): Error message if the operation failed
  • result.value: The actual result value if the operation succeeded

execute(query, params?)

Executes a SQL statement within the transaction.

  • query (string): SQL statement to execute
  • params (list): Optional list of parameters
  • Returns: OperationResult with the number of affected rows in .value.

query(query, params?)

Executes a SQL query within the transaction.

  • query (string): SQL query to execute
  • params (list): Optional list of parameters
  • Returns: OperationResult with the list of row dictionaries in .value.

query_one(query, params?)

Executes a SQL query within the transaction and returns the first row.

  • query (string): SQL query to execute
  • params (list): Optional list of parameters
  • Returns: OperationResult with the first row dictionary (or None) in .value.

commit()

Commits the transaction.

  • Parameters: None
  • Returns: OperationResult indicating success or failure.

rollback()

Rolls back the transaction.

  • Parameters: None
  • Returns: None

High-level table operations

create_table(table, columns, constraints?, indexes?, exist_ok?)

Creates a new table with specified column definitions, optional table constraints, and indexes.

Parameters:

  • table (string): Name of the table to create
  • columns (dict): Dictionary mapping column names to their definitions
  • constraints (list, optional): List of table-level constraint SQL strings
  • indexes (list, optional): List of indexes to create
  • exist_ok (bool, optional): If True, do not raise an error if the table already exists (default: False)

Column definitions can be expressed two ways:

  1. Simple string definition (backward compatible):

    "column_name": "DATA_TYPE CONSTRAINTS"
  2. Structured dictionary definition:

    "column_name": {
        "type": "DATA_TYPE",           # Required: SQLite data type
        "primary_key": True,           # Optional: PRIMARY KEY constraint
        "autoincrement": True,         # Optional: AUTOINCREMENT (INTEGER PRIMARY KEY only)
        "not_null": True,              # Optional: NOT NULL constraint
        "unique": True,                # Optional: UNIQUE constraint
        "default": "value"             # Optional: DEFAULT value
    }

Table constraints β€” optional list of table-level constraints as SQL strings:

  • "FOREIGN KEY (column) REFERENCES table(column) ON DELETE CASCADE"
  • "CHECK (condition)"
  • "UNIQUE (column1, column2)"

Indexes β€” optional list of indexes. Each index can be:

  • String: Single column name (e.g. "column_name")
  • List: Multiple column names for a composite index (e.g. ["col1", "col2"])

Index names are auto-generated as idx_table_column or idx_table_col1_col2.

Returns: None

Examples:

# Simple string definitions (backward compatible)
db.create_table("users", {
    "id": "INTEGER PRIMARY KEY",
    "name": "TEXT NOT NULL",
    "email": "TEXT UNIQUE"
})

# Structured column definitions
db.create_table("users", {
    "id": {"type": "INTEGER", "primary_key": True, "autoincrement": True},
    "username": {"type": "TEXT", "not_null": True, "unique": True},
    "email": {"type": "TEXT", "not_null": True},
    "age": {"type": "INTEGER", "default": 0},
    "is_active": {"type": "BOOLEAN", "default": True}
})

# With table constraints and indexes
db.create_table("posts", {
    "id": "INTEGER PRIMARY KEY",
    "user_id": "INTEGER NOT NULL",
    "title": "TEXT NOT NULL",
    "content": "TEXT",
    "created_at": "TEXT DEFAULT CURRENT_TIMESTAMP"
}, constraints=[
    "FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE",
    "CHECK (length(title) > 0)"
], indexes=[
    "user_id",                    # Single column index
    "created_at",                 # Another single column index
    ["user_id", "created_at"]     # Composite index
])

# Idempotent setup scripts β€” create the table only if it does not exist
db.create_table("settings", {
    "key": "TEXT PRIMARY KEY",
    "value": "TEXT"
}, constraints=["CHECK (length(key) > 0)"], indexes=["key"], exist_ok=True)

drop_table(table)

Drops (deletes) a table.

Parameters:

  • table (string): Name of the table to drop

Returns: None

Example:

db.drop_table("old_products")

table_exists(table)

Checks if a table exists in the database.

Parameters:

  • table (string): Name of the table to check

Returns: Boolean indicating if the table exists.

Example:

if db.table_exists("users"):
    print("Users table exists")
else:
    print("Users table does not exist")

truncate_table(table)

Removes all rows from a table (equivalent to DELETE FROM table).

Parameters:

  • table (string): Name of the table to truncate

Returns: Number of rows deleted (int).

Example:

deleted_rows = db.truncate_table("temporary_data")
print("Deleted {} rows".format(deleted_rows))

Record operations

insert(table, values)

Inserts a single record into a table.

Parameters:

  • table (string): Name of the table
  • values (dict): Dictionary mapping column names to values

Returns: Last insert ID (int), or the number of affected rows if the last insert ID is not available.

Example:

user_id = db.insert("users", {
    "name": "Alice",
    "email": "alice@example.com",
    "age": 30
})
print("Inserted user with ID:", user_id)

insert_many(table, values_list)

Inserts multiple records into a table in a single transaction.

Parameters:

  • table (string): Name of the table
  • values_list (list): List of dictionaries, each representing a record to insert

Returns: Number of rows inserted (int).

Example:

rows_inserted = db.insert_many("users", [
    {"name": "Bob", "email": "bob@example.com", "age": 25},
    {"name": "Charlie", "email": "charlie@example.com", "age": 35},
    {"name": "Diana", "email": "diana@example.com", "age": 28}
])
print("Inserted {} users".format(rows_inserted))

update(table, values, where?)

Updates records in a table.

Parameters:

  • table (string): Name of the table
  • values (dict): Dictionary mapping column names to new values
  • where (string, list, or None): Optional where clause. Can be:
    • None: Update all records (use with caution!)
    • String: Simple where clause with no parameters (e.g. "age > 18")
    • List: Where clause with parameters as [condition, param1, param2, …]

Returns: Number of rows updated (int).

Example:

# Update with simple string condition
db.update("users", {"status": "inactive"}, "age < 18")

# Update with parameterized condition (recommended for user input)
rows_updated = db.update("users", {"age": 31}, ["name = ?", "Alice"])

# Update multiple conditions
rows_updated = db.update("products",
    {"price": 19.99, "on_sale": True},
    ["category = ? AND price > ?", "electronics", 20.0]
)

upsert(table, values, conflict_columns)

Inserts a record or updates it if it already exists (based on conflict columns).

Parameters:

  • table (string): Name of the table
  • values (dict): Dictionary mapping column names to values
  • conflict_columns (list): List of column names that determine conflicts

Returns: Number of rows affected (int).

Example:

db.upsert("users",
    {"email": "alice@example.com", "name": "Alice Smith", "age": 31},
    ["email"]
)

delete(table, where?)

Deletes records from a table.

Parameters:

  • table (string): Name of the table
  • where (string, list, or None): Optional where clause. Can be:
    • None: Delete all records (use with extreme caution!)
    • String: Simple where clause with no parameters (e.g. "age < 18")
    • List: Where clause with parameters as [condition, param1, param2, …]

Returns: Number of rows deleted (int).

Example:

# Delete with simple string condition
rows_deleted = db.delete("users", "age < 18")

# Delete with parameterized condition (recommended for user input)
rows_deleted = db.delete("users", ["name = ?", "Bob"])

# Delete with multiple conditions
rows_deleted = db.delete("products", ["category = ? AND price < ?", "electronics", 10.0])

select(table, columns?, where?, order_by?, limit?, offset?)

Selects records from a table with flexible filtering and sorting options.

Parameters:

  • table (string): Name of the table
  • columns (string or list): Column names to select, "*" for all, or a list of column names
  • where (string, list, or None): Optional where clause. Can be:
    • None: No filtering
    • String: Simple where clause with no parameters (e.g. "age > 18")
    • List: Where clause with parameters as [condition, param1, param2, …]
  • order_by (string): Optional ORDER BY clause (e.g. "name ASC", "age DESC"). ORDER BY cannot be parameterized, so this value is validated against a strict whitelist before use: a comma-separated list of column identifiers (bare, dotted table.col, or double-quoted/backtick-quoted), each with an optional ASC/DESC and optional NULLS FIRST/NULLS LAST. Anything else (raw SQL, semicolons, parentheses, comments, other keywords) raises invalid order_by clause.
  • limit (int): Optional maximum number of rows to return
  • offset (int): Optional number of rows to skip

Returns: List of dictionaries representing the selected rows.

Example:

# Select all users
users = db.select("users")

# Select with simple string condition
adult_users = db.select("users",
    ["name", "email"],
    "age >= 18",
    order_by="name ASC",
    limit=10
)

# Select with parameterized conditions (recommended for user input)
active_users = db.select("users",
    "*",
    ["active = ?", True],
    order_by="created_at DESC",
    limit=20,
    offset=20
)

count(table, where?)

Counts records in a table with optional filtering.

Parameters:

  • table (string): Name of the table
  • where (string, list, or None): Optional where clause. Can be:
    • None: Count all records
    • String: Simple where clause with no parameters (e.g. "age > 18")
    • List: Where clause with parameters as [condition, param1, param2, …]

Returns: Number of matching records (int).

Example:

# Count all users
total_users = db.count("users")

# Count with simple string condition (no parameters)
adult_users = db.count("users", "age >= 18")

# Count with parameterized condition (recommended for user input)
active_users = db.count("users", ["status = ?", "active"])

Multi-database operations

attach(database, alias)

Attaches another database with a specified alias. (When the host restricts file access, only in-memory or the configured database may be attached.)

Parameters:

  • database (string): Path to the database file to attach
  • alias (string): Alias name for the attached database

Returns: None

Example:

# Attach an archive database
db.attach("archive.db", "archive")

# Now you can query from the attached database
old_users = db.query("SELECT * FROM archive.old_users")

detach(alias)

Detaches a previously attached database.

Parameters:

  • alias (string): Alias name of the database to detach

Returns: None

Example:

db.detach("archive")

Schema information

tables()

Returns a list of all tables in the database.

Parameters: None

Returns: List of table names (list of strings).

Example:

tables = db.tables()
for table in tables:
    print("- {}".format(table))

table_info(table)

Returns detailed information about a table's columns.

Parameters:

  • table (string): Name of the table

Returns: List of dictionaries containing column information. Each dictionary contains:

  • cid: Column ID (int)
  • name: Column name (string)
  • type: Column type (string)
  • notnull: 1 if the column is NOT NULL, else 0 (int)
  • dflt_value: Default value as a string (or None)
  • pk: 0 if the column is not part of the primary key, otherwise its 1-based position in the primary key (int)

Example:

columns = db.table_info("users")
for col in columns:
    null_str = "NOT NULL" if col["notnull"] else "NULL"
    pk_str = " (PRIMARY KEY)" if col["pk"] else ""
    print("- {} {} {}{}".format(col["name"], col["type"], null_str, pk_str))

indices(table)

Returns information about indices on a table.

Parameters:

  • table (string): Name of the table

Returns: List of dictionaries containing index information.

Example:

indices = db.indices("users")
for idx in indices:
    print("- {}".format(idx["name"]))

Custom SQL functions

The module supports registering custom SQL functions written in Starlark that can be called from SQL queries, extending SQLite with domain-specific logic.

Critical requirements:

  • Custom functions MUST be registered BEFORE opening any database connection. Functions are registered globally with the SQLite driver and affect all connections opened after registration.
  • Use unique function names to avoid conflicts when multiple modules or tests register functions. Consider prefixes like APP_, MODULE_, etc.

register_function(name, func, num_args=None, deterministic=False)

Registers a custom SQL function that can be called from SQL queries.

Parameters:

  • name (string): Name of the SQL function to register (case-insensitive in SQL)
  • func (callable): A Starlark function or lambda implementing the logic
  • num_args (int, optional): Number of arguments the function accepts
    • None or unspecified: Function is variadic (accepts any number of arguments)
    • A specific value: Function accepts exactly that many arguments
    • -1: Explicitly variadic
  • deterministic (bool, optional): Whether the function is deterministic (default: False)
    • True: Always returns the same result for identical inputs (enables SQLite optimizations such as result caching, constant folding, and functional indexes)
    • False: May return different results (e.g. uses randomness or current time)

Returns: None on success.

Raises: Error on failure (empty name, non-callable, num_args < -1, duplicate registration, etc.).

Return values: A function's return value is converted to a SQLite value the same way bind parameters are. An integer return that does not fit a signed 64-bit value raises int value too large for SQLite rather than being silently truncated.

Example:

load("sqlite", "connect", "register_function")

def main():
    # Register before opening connections
    register_function("MY_TRIM", lambda s: s.strip() if s else "")
    register_function("SQUARE", lambda x: x * x if x else 0, num_args=1, deterministic=True)
    register_function("ADD_TAX", lambda price, rate: price * (1.0 + rate), num_args=2)

    def greatest(*args):
        valid = [a for a in args if a != None]
        return max(valid) if valid else None
    register_function("GREATEST", greatest)  # variadic by default

    db = connect(":memory:")
    db.execute("CREATE TABLE users (name TEXT)")
    db.execute("INSERT INTO users VALUES ('  John Doe  ')")
    result = db.query("SELECT MY_TRIM(name) as clean_name FROM users")
    print(result)  # [{"clean_name": "John Doe"}]
    db.close()

main()

Error handling for custom functions

  • Registration errors (empty name, non-callable, invalid num_args, duplicate name) halt script execution at the register_function call.
  • Runtime errors raised inside a custom function (e.g. fail(...)) are propagated as a SQL error during query execution.
  • Non-existent functions referenced in SQL produce a SQL error (no such function: …).

Since Starlark has no try/except, prefer validating inputs inside the function and returning None for invalid inputs rather than calling fail():

def safe_divide(a, b):
    if a == None or b == None:
        return None
    if b == 0:
        return None  # return None instead of failing
    return a / b

register_function("SAFE_DIVIDE", safe_divide, num_args=2)

Best practices

  1. Register functions at startup before opening any connection.
  2. Use unique, prefixed function names (APP_, MODULE_) to avoid conflicts.
  3. Mark pure/mathematical functions as deterministic=True for optimization.
  4. Handle None values gracefully and return None for invalid inputs.
  5. Keep functions simple; use an appropriate num_args for validation.

Type conversion

The module automatically handles type conversion between SQLite and Starlark, both for query results and for arguments/returns of custom SQL functions.

SQLite to Starlark

SQLite Type Starlark Type
NULL None
INTEGER int
REAL float
TEXT string
BLOB bytes
timestamp time

Starlark to SQLite

Starlark Type SQLite Type Notes
None NULL
int INTEGER
float REAL
string TEXT
bytes BLOB
bool INTEGER True to 1, False to 0
time timestamp go.starlark.net/lib/time value
dict TEXT JSON encoded
list TEXT JSON encoded

Configuration

Each module configuration option is exposed to scripts as a pair of generated accessor builtins (loaded from the sqlite module alongside the functions above):

  • get_<key>() β€” returns the current value of the option.
  • set_<key>(value) β€” sets the option (returns None).

An option's value resolves in priority order: an explicit set_<key> value, the environment variable, then the default. These options serve as defaults used by connect / connect_remote when the corresponding argument is not provided.

None of the sqlite options are secret, so every option exposes both get_<key> and set_<key>. (A secret option would expose only its set_<key> accessor β€” never a getter β€” but this module has none.)

Option Getter Setter Type Env var Default Description
database get_database set_database (unrestricted modules only) string SQLITE_DATABASE :memory: Path to the SQLite database (use :memory: for in-memory)
timeout get_timeout set_timeout float SQLITE_TIMEOUT 30.0 Per-operation deadline in seconds (per-query; cancels with the script thread; 0 = none)
busy_timeout get_busy_timeout set_busy_timeout float SQLITE_BUSY_TIMEOUT 5.0 Busy timeout in seconds
foreign_keys get_foreign_keys set_foreign_keys bool SQLITE_FOREIGN_KEYS true Enable foreign key constraints
journal_mode get_journal_mode set_journal_mode string SQLITE_JOURNAL_MODE DELETE Journal mode (WAL, DELETE, TRUNCATE, PERSIST, MEMORY, OFF)
synchronous get_synchronous set_synchronous string SQLITE_SYNCHRONOUS FULL Synchronous mode (FULL, NORMAL, OFF)
cache_size get_cache_size set_cache_size int SQLITE_CACHE_SIZE -2000 Cache size in number of pages (negative = SQLite default)
max_rows get_max_rows set_max_rows int SQLITE_MAX_ROWS 0 Max rows a query helper returns; 0 = unlimited (see Host hardening)

Example:

load(
    "sqlite",
    "connect",
    # getters
    "get_database", "get_timeout", "get_busy_timeout", "get_foreign_keys",
    "get_journal_mode", "get_synchronous", "get_cache_size", "get_max_rows",
    # setters
    "set_database", "set_timeout", "set_busy_timeout", "set_foreign_keys",
    "set_journal_mode", "set_synchronous", "set_cache_size", "set_max_rows",
)

set_journal_mode("WAL")
print(get_journal_mode())  # "WAL"

db = connect("app.db")  # opens with journal_mode=WAL

Host hardening (opt-in, host side)

File and function restrictions are opt-in, fixed by the host in Go. The default constructors continue to allow custom functions and local files.

  • Bound result size β€” max_rows. A query helper materializes every returned row into memory. Set max_rows (the config option above, or SQLITE_MAX_ROWS) to cap the number of rows any single query returns; exceeding the cap raises a script error instead of allocating without bound. 0 (the default) means unlimited. This configuration is script-settable; it is not an immutable host policy in the default module.

  • Restrict file access β€” NewModuleWithFileAccess. By default a script may connect(path) to any file path (or attach any database). NewModuleWithFileAccess(false) locks this down: scripts may only open in-memory databases (:memory:, file::memory:…) or the exact database configured on the module β€” any other path, and any attach of an on-disk database, is rejected. NewModule() equals NewModuleWithFileAccess(true). In restricted mode, database is captured at construction, has no set_database, and ignores subsequent environment changes. This also applies to connect_remote with a file: URL. Paths are compared as configured DSNs; this is not a filesystem or symlink sandbox.

  • Disable custom functions β€” NewModuleWithHostPolicy. Set HostPolicy{DisableCustomFunctions: true} to reject register_function and exclude process-wide functions from every local connection, including pooled replacements and local file: URLs passed to connect_remote. Functions registered before or after construction by another module are also excluded. The independent driver does not inherit custom collations or connection hooks installed on the default driver. Ordinary SQLite built-ins, queries, writes, transactions, attached databases and prepared statements remain available. Combine with RestrictFileAccess: true for the file restriction above. Policy flags have no script getters/setters or environment-variable controls.

    A zero HostPolicy preserves NewModule() behavior. When custom functions remain enabled, registrations are process-wide and permanent, and script UDF callbacks use a separate thread without the caller's step budget/cancellation. Disabling UDFs avoids that callback path; it does not provide isolated or budgeted execution for UDFs that remain enabled.

Network libSQL endpoints retain their existing behavior. Server-side functions and network authorization are controlled separately; the local UDF policy does not claim to restrict a remote server. All connections are explicit script calls.