FiveM Database Design for RP Servers

Your server has 80 players online, someone opens their inventory, and the whole city freezes for half a second. You check resmon — it’s not the inventory script. It’s your database. I’ve seen this exact scenario dozens of times, and it almost always comes down to bad schema design, missing indexes, or queries that were fine with 10 players but fall apart at scale.

Database design isn’t glamorous. Nobody joins your Discord to compliment your table structure. But a well-designed database is the difference between a server that handles 128 players without breaking a sweat and one that starts choking at 40.

Why Most FiveM Servers Have Database Problems

Here’s the thing — most server owners never touch their database schema directly. They install QBCore or ESX, add scripts, and those scripts create their own tables automatically. That works until it doesn’t.

The problem is that scripts are designed in isolation. Each developer creates tables that work for their resource, but nobody’s thinking about how 50+ scripts interact with the same database simultaneously. You end up with redundant data, inconsistent naming, and zero indexing strategy.

If you’ve ever run SHOW PROCESSLIST on your MySQL server during peak hours and seen a wall of queries in “Sending data” state, you already know what I’m talking about. If you haven’t done that yet, go do it right now. I’ll wait.

Choosing the Right MySQL Setup

Before we talk schema, let’s talk infrastructure. Your database choice matters more than you think.

MariaDB over MySQL. Most FiveM hosting providers default to MariaDB, and that’s fine — it’s a drop-in replacement that’s generally faster for the kind of read-heavy workloads RP servers generate. If you’re setting up your own VPS, use MariaDB 10.11+ or MySQL 8.0+. Don’t run MySQL 5.7 in 2026. It’s end-of-life and missing features you’ll want.

Local vs Remote. If your database is on the same machine as your FiveM server, you’re fine. If it’s on a separate host, latency matters. Every query round-trip adds up. A remote database with 5ms latency doesn’t sound bad until you realize a single player joining might trigger 15-20 queries. That’s 75-100ms of pure network wait before they even see the loading screen finish.

Connection pooling with oxmysql. If you’re not using oxmysql, switch now. It replaced mysql-async and ghmattimysql as the standard for good reason — better connection pooling, prepared statements, and proper error handling. If you’re still on mysql-async, check out our beginner Lua scripting guide for context on modernizing your stack.

Your server.cfg connection string should look something like this:

set mysql_connection_string "mysql://user:password@localhost/fivem_db?charset=utf8mb4&connectTimeout=30000&acquireTimeout=30000"

The charset=utf8mb4 part is important — it supports full Unicode including emoji, which players will inevitably try to put in their character names.

Core Schema Design Principles

Use Proper Data Types

I cannot stress this enough. The number one amateur mistake is using TEXT or LONGTEXT for everything. Here’s what I see constantly:

-- Bad: Everything is text
CREATE TABLE players (
    identifier TEXT,
    name TEXT,
    money TEXT,
    job TEXT,
    inventory LONGTEXT
);

This is terrible. TEXT columns can’t be efficiently indexed, they waste memory, and comparing text values is slower than comparing integers. Here’s what it should look like:

-- Good: Proper data types
CREATE TABLE players (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    citizenid VARCHAR(50) NOT NULL,
    license VARCHAR(60) NOT NULL,
    name VARCHAR(100) NOT NULL,
    money INT NOT NULL DEFAULT 0,
    bank INT NOT NULL DEFAULT 0,
    job VARCHAR(50) NOT NULL DEFAULT 'unemployed',
    job_grade TINYINT UNSIGNED NOT NULL DEFAULT 0,
    position JSON DEFAULT NULL,
    inventory JSON DEFAULT NULL,
    metadata JSON DEFAULT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    last_seen TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY idx_citizenid (citizenid),
    INDEX idx_license (license)
);

Notice the differences. Money is an INT, not text — you can do math on it without parsing. Job is a VARCHAR(50) with a length limit, not unbounded text. Position and inventory use JSON type, which MariaDB/MySQL can validate and query into if needed.

Normalize Where It Matters

Database normalization is a computer science concept that basically means “don’t repeat yourself.” In FiveM terms, it means don’t store the same data in five different tables.

A common example: player vehicles. I’ve seen servers where the vehicle’s owner name is stored in the vehicles table alongside the citizen ID. When a player changes their character name, now you have mismatched data everywhere.

-- Bad: Denormalized vehicle table
CREATE TABLE player_vehicles (
    id INT AUTO_INCREMENT PRIMARY KEY,
    owner_citizenid VARCHAR(50),
    owner_name VARCHAR(100),  -- This will go stale
    plate VARCHAR(8),
    vehicle VARCHAR(50),
    mods LONGTEXT,
    garage VARCHAR(50)
);

-- Good: Normalized — join to players table for the name
CREATE TABLE player_vehicles (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    citizenid VARCHAR(50) NOT NULL,
    plate VARCHAR(8) NOT NULL,
    vehicle VARCHAR(50) NOT NULL,
    mods JSON DEFAULT NULL,
    garage VARCHAR(50) NOT NULL DEFAULT 'pillboxgarage',
    state TINYINT NOT NULL DEFAULT 1,
    fuel TINYINT UNSIGNED NOT NULL DEFAULT 100,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_citizenid (citizenid),
    UNIQUE KEY idx_plate (plate),
    INDEX idx_garage_state (garage, state),
    FOREIGN KEY (citizenid) REFERENCES players(citizenid) ON DELETE CASCADE
);

The FOREIGN KEY with ON DELETE CASCADE is huge. When a character gets deleted, their vehicles go with them. No orphaned data sitting in your database forever.

But don’t over-normalize. FiveM is not a banking application. If denormalizing a field saves you a JOIN on a query that runs every time a player opens their phone, that’s a worthwhile trade-off. The goal is practical performance, not academic purity.

JSON Columns — Use Them Wisely

Both QBCore and ESX store a lot of data as JSON blobs — inventory, metadata, character info. This is fine for data that’s always read and written as a whole unit. Your inventory is a perfect example: you almost never need to query “find all players who have a lockpick” directly from SQL.

But JSON becomes a problem when you start needing to filter or search by values inside it. If you find yourself writing queries like this regularly:

SELECT * FROM players WHERE JSON_EXTRACT(metadata, '$.phone') = '555-0123';

That’s a sign that phone should be its own indexed column, not buried in a JSON blob. JSON extraction can’t use indexes efficiently, so these queries do full table scans. With 5,000 player records, that’s slow. With 50,000, it’s painful.

Rule of thumb: If you search or filter by it, it gets its own column. If you just store and retrieve it as a block, JSON is fine.

Indexing — The Single Biggest Performance Win

If you take one thing from this entire post, let it be this: index your lookup columns.

An index is like a phone book for your database. Without one, MySQL has to read every single row to find what it’s looking for. With an index, it jumps straight to the answer.

Here’s a real example. The default QBCore player_vehicles table has no index on citizenid in some older installations. Every time a player opens their garage, this query runs:

SELECT * FROM player_vehicles WHERE citizenid = 'ABC12345' AND garage = 'pillboxgarage' AND state = 1;

Without an index, MySQL scans every vehicle record in the table. On a server with 30,000 vehicle records, that’s noticeable. With a composite index on (citizenid, garage, state), it’s instant.

-- Add this index to your existing table
ALTER TABLE player_vehicles ADD INDEX idx_citizen_garage (citizenid, garage, state);

How to Find Missing Indexes

Run EXPLAIN before your most common queries:

EXPLAIN SELECT * FROM player_vehicles WHERE citizenid = 'ABC12345';

If you see type: ALL in the output, that means full table scan — no index is being used. You want to see type: ref or type: const.

Here’s a quick script to find the worst offenders. Run this during peak hours:

-- Find slow queries (requires slow query log enabled)
SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 0.1;  -- Log queries over 100ms

After running for an hour during peak, check the slow query log. You’ll probably find 3-4 queries that account for 80% of your database load. Index those first.

Don’t Over-Index Either

Every index speeds up reads but slows down writes. When a player’s inventory updates (which happens constantly), MySQL has to update every index that involves the inventory table. On a server where 80 players are all picking up items, dropping items, and transferring items simultaneously, excessive indexes on your inventory table will create write contention.

Index columns you search by. Don’t index columns you only read after finding the row.

Common Schema Patterns for RP Servers

Character System Tables

If you’re running QBCore multichar, your character system needs clean separation between the account (license-level) and the character (citizenid-level):

-- Account level (one per real player)
CREATE TABLE accounts (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    license VARCHAR(60) NOT NULL UNIQUE,
    discord VARCHAR(30) DEFAULT NULL,
    banned TINYINT NOT NULL DEFAULT 0,
    ban_reason VARCHAR(255) DEFAULT NULL,
    ban_expires TIMESTAMP NULL DEFAULT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Character level (multiple per account)
CREATE TABLE characters (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    account_id INT UNSIGNED NOT NULL,
    citizenid VARCHAR(50) NOT NULL UNIQUE,
    charinfo JSON NOT NULL,
    money JSON NOT NULL DEFAULT '{"cash":500,"bank":5000}',
    job VARCHAR(50) NOT NULL DEFAULT 'unemployed',
    gang VARCHAR(50) DEFAULT NULL,
    position JSON DEFAULT NULL,
    metadata JSON DEFAULT NULL,
    is_deleted TINYINT NOT NULL DEFAULT 0,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    last_played TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (account_id) REFERENCES accounts(id),
    INDEX idx_account (account_id),
    INDEX idx_job (job)
);

The is_deleted soft-delete flag is important. Don’t actually delete character data — mark it as deleted. Players will beg admins to restore characters they “accidentally” deleted, and you’ll be glad you kept the data.

Job and Economy Tables

If you’re setting up jobs on your server, keep job definitions in the database rather than hardcoding them in config files. This makes it possible to update job payouts without restarting the server:

CREATE TABLE jobs (
    name VARCHAR(50) PRIMARY KEY,
    label VARCHAR(100) NOT NULL,
    type ENUM('legal', 'illegal', 'whitelisted') NOT NULL DEFAULT 'legal',
    default_duty TINYINT NOT NULL DEFAULT 1
);

CREATE TABLE job_grades (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    job_name VARCHAR(50) NOT NULL,
    grade TINYINT UNSIGNED NOT NULL,
    name VARCHAR(50) NOT NULL,
    label VARCHAR(100) NOT NULL,
    salary INT UNSIGNED NOT NULL DEFAULT 0,
    FOREIGN KEY (job_name) REFERENCES jobs(name) ON DELETE CASCADE,
    UNIQUE KEY idx_job_grade (job_name, grade)
);

For tracking economic health — which ties directly into how you make money from your server — log transactions:

CREATE TABLE economy_log (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    citizenid VARCHAR(50) NOT NULL,
    type ENUM('deposit', 'withdraw', 'transfer', 'job_payment', 'purchase', 'sale') NOT NULL,
    amount INT NOT NULL,
    balance_after INT NOT NULL,
    source VARCHAR(100) DEFAULT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_citizen_date (citizenid, created_at),
    INDEX idx_type_date (type, created_at)
);

This table will grow fast. Partition it by month or set up a cron job to archive records older than 90 days. Don’t let it become a 10-million-row monster that slows down your backups.

Inventory Tables

If you’re using ox_inventory, it handles its own schema and does it well. But if you’re building custom inventory or extending the defaults, here’s the pattern:

CREATE TABLE inventories (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    owner VARCHAR(60) DEFAULT NULL,
    name VARCHAR(100) NOT NULL,
    type ENUM('player', 'stash', 'trunk', 'glovebox', 'drop') NOT NULL,
    slots TINYINT UNSIGNED NOT NULL DEFAULT 50,
    max_weight INT UNSIGNED NOT NULL DEFAULT 120000,
    items JSON DEFAULT NULL,
    last_accessed TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY idx_owner_name (owner, name),
    INDEX idx_type (type)
);

The last_accessed timestamp is useful for cleanup. Stashes and drops that haven’t been touched in 30 days can be safely pruned.

Query Optimization in Lua

Writing good SQL from your Lua scripts matters just as much as the schema itself. Here are patterns I see break servers regularly.

Batch Your Queries

The worst thing you can do is query inside a loop:

-- Terrible: N+1 query pattern
for _, player in pairs(QBCore.Functions.GetPlayers()) do
    local result = MySQL.query.await('SELECT * FROM characters WHERE citizenid = ?', {player.citizenid})
    -- process result
end

If you have 80 players, that’s 80 separate database round trips. Instead:

-- Good: Single query with IN clause
local citizenids = {}
for _, player in pairs(QBCore.Functions.GetPlayers()) do
    citizenids[#citizenids + 1] = player.citizenid
end

if #citizenids > 0 then
    local placeholders = string.rep('?,', #citizenids - 1) .. '?'
    local results = MySQL.query.await(
        'SELECT * FROM characters WHERE citizenid IN (' .. placeholders .. ')',
        citizenids
    )
    -- process all results at once
end

One query instead of 80. The difference is massive.

Use Prepared Statements

oxmysql uses prepared statements by default when you use parameterized queries (the ? placeholders). Never concatenate user input into SQL strings:

-- Dangerous: SQL injection vulnerability
MySQL.query.await('SELECT * FROM players WHERE name = "' .. playerName .. '"')

-- Safe: Parameterized query
MySQL.query.await('SELECT * FROM players WHERE name = ?', {playerName})

This isn’t just about security (though SQL injection on a live RP server is catastrophic). Prepared statements are also faster because MySQL can cache the query plan and reuse it.

Async vs Await

Use MySQL.query.await when you need the result before continuing. Use MySQL.query (callback-based) when you’re just writing data and don’t need to wait:

-- Saving position on disconnect — don't need to wait for it
MySQL.query('UPDATE characters SET position = ? WHERE citizenid = ?', {
    json.encode(coords), citizenid
})

-- Loading inventory on join — must wait for data
local inventory = MySQL.query.await('SELECT items FROM inventories WHERE owner = ?', {citizenid})

This alone can cut your script’s apparent lag. If you’re doing a bunch of writes on player disconnect, fire them all as async calls. The player is leaving anyway — they don’t need to wait for the database to confirm.

Maintenance and Monitoring

Regular Backups

If you’re not backing up your database daily, you’re gambling. One bad script update, one crashed migration, and months of player data is gone. Set up mysqldump on a cron:

# Daily backup at 5 AM server time
0 5 * * * mysqldump -u backup_user -p'securepassword' fivem_db | gzip > /backups/fivem_$(date +\%Y\%m\%d).sql.gz

Keep at least 7 days of backups. Test restoring from them periodically. A backup you’ve never tested restoring is not really a backup.

Table Optimization

After weeks of inserts, updates, and deletes, tables accumulate fragmented space. Run OPTIMIZE TABLE on your largest tables during off-peak hours:

OPTIMIZE TABLE characters, player_vehicles, inventories;

This reclaims disk space and rebuilds indexes. Don’t run it during peak hours — it locks the table briefly.

Monitor Table Sizes

Keep an eye on which tables are growing fastest:

SELECT table_name, 
       ROUND(data_length / 1024 / 1024, 2) AS data_mb,
       ROUND(index_length / 1024 / 1024, 2) AS index_mb,
       table_rows
FROM information_schema.tables 
WHERE table_schema = 'fivem_db'
ORDER BY data_length DESC
LIMIT 20;

If your economy log table is bigger than your player data table, it’s time to archive or prune.

What About Scripts That Create Their Own Tables?

This is the reality of FiveM development — you install a script and it auto-creates tables on first run. You can’t always control the schema. But you can audit it.

After installing any new script from the store or from free resources, check what tables it created:

SHOW TABLES LIKE '%newscriptname%';

Then inspect the structure:

DESCRIBE tablename;
SHOW INDEX FROM tablename;

If there are no indexes on columns that get queried frequently, add them yourself. Most script developers focus on functionality, not database performance at scale. You’re the one running a 128-player server — optimizing their tables is your job.

A Quick Performance Checklist

Before I wrap up, here’s what you should do this week. If your server has been running for more than a month with 50+ concurrent players, at least one of these will make a noticeable difference:

Run SHOW PROCESSLIST during peak to see what queries are running. Look for anything in “Sending data” state for more than a second.

Run EXPLAIN on your garage, inventory, and character loading queries. If you see type: ALL, add an index.

Check your oxmysql connection string for charset=utf8mb4. If it’s missing, add it.

Look at table sizes using the information_schema query above. If any table has more than 500,000 rows without an archival strategy, fix that.

Set up daily backups if you haven’t. If you have, test a restore.

Review any LONGTEXT columns in your schema. Most of them should be JSON or VARCHAR.

If you want to understand how these database improvements affect your overall server performance numbers, the resmon and performance guide covers the monitoring side of things. And if you’re still getting oriented with how scripts interact with your server in general, the script installation guide walks through the basics.

For questions or if you want to share your optimization results, drop by the YBN Scripts Discord. There’s always someone in there who’s dealt with the same database headaches you’re facing.