SQL Interface

TinyMUX provides an optional SQL interface that allows the game to communicate with an external MySQL or MariaDB database. This lets game administrators move beyond the limits of attribute-based storage, enabling structured queries against relational data for use cases such as web integration, persistent logging, and complex data retrieval.

Compile-Time Options

The SQL interface is not enabled by default. There are two compile-time options, each with different trade-offs:

• –enable-inlinesql enables the sql() function, which executes queries synchronously within the main game loop. This is compatible with PennMUSH and TinyMUSH 3.x. Because the game blocks while the query runs, it is best suited for fast, simple queries.
• –enable-stubslave enables the @query command, which performs queries asynchronously through a separate slave process. Results are delivered via a callback trigger when the query completes, so the game does not block while waiting.

Both options use the MySQL client library (libmysqlclient) and support MySQL and MariaDB servers.

Configuration

Four configuration parameters in the game’s .conf file control the database connection. All are static parameters (set at startup, not changeable at runtime):

Example configuration:

sql_server    localhost
sql_database  mygame
sql_user      muxuser
sql_password  secretpass

The sql() Function (Inline SQL)

The sql() function provides synchronous, inline access to the database from softcode. It requires the INLINESQL compile-time option and is restricted to Wizards (CA_WIZARD).

sql(<query>[, <row-delim>[, <col-delim>]])

The query string is passed directly to the MySQL server. The row delimiter defaults to a space, and the column delimiter defaults to the row delimiter. Commas within the query must be escaped with %, since the function parser treats commas as argument separators.

Example:

> think sql(select name%, level from players where level > 5,~,|)
Alice|10~Bob|7~Carol|12

If the database connection is unavailable, the function returns #-1 SQL UNAVAILABLE. If the query itself fails, it returns #-1 QUERY ERROR. If no database handle exists at all, it returns #-1 NO DATABASE.

Because sql() is synchronous, the game process blocks until the query returns. For simple SELECT statements on indexed tables, this is usually imperceptible. However, running many large or slow queries in sequence will cause noticeable lag for all connected players.

The @query Command (Asynchronous SQL)

The @query command provides asynchronous database access through the stub slave process. It requires the --enable-stubslave compile-time option and is restricted to Wizards.

@query/sql <dbref>/<attribute>=[<dbname>]/<query>

When the query completes, the specified attribute on the given object is triggered (as with @trigger). Within that triggered attribute, a set of result-set functions become available for navigating the returned data.

Example:

@query/sql me/QUERY_DONE=mydb/SELECT name, level FROM players

When the query finishes, me/QUERY_DONE fires and can use the result-set functions to process the rows.

Result Set Navigation Functions

These functions are only valid within a @query completion trigger:

A typical pattern for iterating over results:

&QUERY_DONE me=[think Rows: [rsrows()]][iter(lnum(rsrows()), think [rsrecnext(|)])]

Security Considerations

Both sql() and @query are restricted to Wizard-level access (CA_WIZARD). Ordinary players cannot execute SQL queries directly.

However, Wizards who build softcode wrappers around SQL calls should be cautious about SQL injection. If player-supplied input is interpolated into a query string without sanitization, a malicious player could alter the query. TinyMUX does not provide built-in parameterized queries or escaping functions, so any wrapper that accepts user input must sanitize it in softcode before passing it to sql() or @query.

The sql_password configuration parameter is a static, read-only setting. It cannot be viewed or changed at runtime through normal game commands, but it is stored in plaintext in the configuration file on disk.

Connection Management and Error Handling

For inline SQL, the sql() function calls mysql_ping() before each query to verify the connection is alive. If the connection has dropped, the function returns #-1 SQL UNAVAILABLE rather than attempting an automatic reconnect.

The implementation also drains any extra result sets that may be returned by stored procedures, preventing leftover results from interfering with subsequent queries.

For @query, the stub slave manages the connection independently. If the query server process is unavailable, the command reports “Query server is not available.”

Practical Use Cases

• Web integration: A web application can write data into a MySQL table, and game softcode can query it via sql() to reflect external events in-game (forum posts, wiki edits, registration data).
• Persistent structured storage: Attributes are limited in size and structure. A SQL database allows normalized, queryable storage for complex game systems such as economies, crafting inventories, or faction standings.
• Logging and auditing: Game events can be written to SQL tables for later analysis, reporting, or display on a web dashboard.
• Cross-game data sharing: Multiple game instances can read and write to the same database, enabling shared player data or messaging systems.

Performance Considerations

The synchronous sql() function blocks the entire game loop. Queries should be kept fast: use indexed columns in WHERE clauses, avoid full table scans, and never run bulk operations in a tight softcode loop. The help text explicitly warns that hundreds of large queries in sequence will cause the game to lag.

The asynchronous @query command avoids this problem by offloading work to the stub slave, making it the better choice for queries that might be slow or that return large result sets. The trade-off is more complex softcode, since results must be processed in a callback trigger rather than inline.

For either approach, the database server should ideally run on the same host or local network as the game server to minimize query latency.