The FiveM Database Layer: Async Queries, Indexes, and Safe Migrations

Why async isn't optional

A synchronous database call blocks the calling thread until the database responds. On a FiveM server, that thread is doing other work too — a slow query becomes a frame hitch, and a stalled connection becomes a frozen resource. oxmysql, which documents itself as a replacement for the older mysql-async and ghmattimysql connectors, exists specifically to make every call non-blocking by default, so a query in flight never stops the rest of the server from running.

The frame-cost side of this — what a blocking call actually costs in resmon terms — is covered in full in our resmon guide and our lag-diagnosis guide; this guide is about the database layer's own discipline, not about measuring its cost.

Parameterized queries and prepared statements

A query built by concatenating a value straight into a SQL string carries two separate problems: it's an injection risk if the value ever comes from user input, and it's the same client-trust failure our server-authority guide describes, just at the database layer instead of the event layer. A parameterized query keeps the value out of the query text entirely, passed alongside it instead.

The following is a generic, illustrative contrast — not code from any specific product — showing the shape of the difference:

• Unsafe: building a query string by concatenating a variable directly into it, e.g. an UPDATE statement with the balance and id spliced into the SQL text itself.
• Safe: a parameterized call with placeholders, e.g. UPDATE accounts SET balance = ? WHERE id = ?, with the values passed as arguments rather than text.
• oxmysql documents a prepared-statement upsert pattern for the common "insert or update" case — a single query with an ON DUPLICATE KEY UPDATE clause — which is preferred over checking whether a row exists and then choosing between an insert and an update, and preserves the row instead of deleting and recreating it the way a REPLACE INTO would.

Connection handling, in practice

oxmysql (like its predecessors) is configured through a single connection string set once, typically before any resource that depends on it starts — which is itself a start-order requirement, covered in our start-order guide. The practical thing to check as a buyer isn't pool size; it's whether a resource shares the server's configured connector at all, or tries to open its own separate connection — a pattern more common in older or poorly integrated resources, and one extra thing that can silently fail on its own.

Indexing the columns that get hit every interaction

An index lets the database jump straight to matching rows instead of scanning the whole table — the difference matters most on columns queried constantly during normal play: identifier or account-lookup columns, and any column that shows up in a WHERE clause on a hot path (a plate lookup, an inventory-owner lookup). oxmysql exposes a slow-query warning convar specifically so an unindexed query that's fine on a small table gets flagged before it becomes a real problem on a large one, rather than after players start noticing.

Migration and backup discipline

Schema changes should ship as versioned, ordered migration files rather than a single ALTER statement run once and forgotten — the difference matters the day you need to apply the same change to a second server, or roll it back. Before running any migration against a database that matters: read it fully for anything that alters or drops existing data, take a backup immediately before running it, and — where the change is non-trivial — dry-run it against a copy first. Our staging-server guide covers exactly how to do that dry run without touching production data.