Why Databases Matter in FiveM
Every serious FiveM server needs persistent data storage. Player inventories, bank accounts, vehicle ownership, phone contacts, and job records all need to survive server restarts. MySQL is the standard database system used across the FiveM ecosystem, with MariaDB being a popular fork that offers improved performance. Understanding how to set up, query, and optimize your database is essential for building reliable roleplay servers that can handle hundreds of concurrent players without data loss.
Setting Up oxmysql
The oxmysql resource is the modern standard for MySQL connectivity in FiveM, replacing the older mysql-async and ghmattimysql libraries. To set it up, download the latest release, place it in your resources folder, and configure the connection string in your server.cfg with set mysql_connection_string. The connection string follows the format mysql://user:password@host/database and supports additional parameters for connection pooling, timeouts, and SSL. Make sure oxmysql starts before any resource that depends on it by placing it early in your ensure order.
Writing Queries
oxmysql provides several query methods: MySQL.query for SELECT statements that return rows, MySQL.update for UPDATE and DELETE statements that return affected row counts, MySQL.insert for INSERT statements that return the last insert ID, and MySQL.scalar for queries that return a single value. Always use parameterized queries with the ? placeholder to prevent SQL injection attacks. Pass parameters as a table in the second argument rather than concatenating values directly into query strings.
Async Patterns and Promises
All oxmysql operations are asynchronous to prevent blocking the server thread during database communication. You can use callback-style syntax where you pass a function as the last argument, or use the promise-based syntax with MySQL.query.await inside a Citizen.CreateThread. The await syntax reads more cleanly and avoids callback nesting, but remember it can only be used inside coroutines. For server startup data loading, use the MySQL.ready function to ensure the database connection is established before running initial queries.
Schema Design Best Practices
Design your database tables with proper indexes on columns you query frequently, such as player identifiers. Use the appropriate data types for each column to minimize storage and improve query speed. Store JSON data in TEXT columns when you need flexible schemas, but avoid querying inside JSON structures frequently as it bypasses index optimization. Create foreign key relationships between related tables to maintain data integrity, and consider using transactions when multiple related inserts or updates need to succeed or fail together.
Optimization and Maintenance
Monitor your database performance by checking slow query logs and analyzing execution plans with EXPLAIN. Cache frequently accessed data in server memory using Lua tables to reduce database round trips. Implement connection pooling through oxmysql configuration to handle concurrent queries efficiently. Schedule regular database maintenance tasks like optimizing tables and cleaning up orphaned records from deleted players. Back up your database daily using mysqldump or automated backup solutions to prevent catastrophic data loss from hardware failures or corrupted tables.