Database Setup

The database is the brain of your server. While MariaDB is standard, PostgreSQL is gaining popularity.

Resource: oxmysql

oxmysql  is the standard SQL driver for FiveM. It supports both MySQL/MariaDB.

Configuration (server.cfg)

# Basic connection
set mysql_connection_string "mysql://user:password@localhost/fivem_db?charset=utf8mb4"
 
# Slow query warning (warns if query takes > 200ms)
set mysql_slow_query_warning 200
 
# Debug mode (prints all queries - dev only)
set mysql_debug 0

MariaDB (Recommended)

Standard for ESX/QBCore.

Docker Compose Setup

Run a clean database instance with Docker:

version: '3.8'
services:
  mariadb:
    image: mariadb:10.11
    restart: always
    environment:
      MYSQL_ROOT_PASSWORD: root_secure_password
      MYSQL_DATABASE: fivem_prod
      MYSQL_USER: fivem
      MYSQL_PASSWORD: user_secure_password
    ports:
      - '3306:3306'
    volumes:
      - ./mysql_data:/var/lib/mysql

PostgreSQL

Qbox and some modern resources support PostgreSQL.

Connection String

set mysql_connection_string "postgres://user:password@localhost/fivem_db"

Why Postgres?

• Better JSON support: Great for storing player metadata.
• Strict typing: Prevents some data corruption issues.
• Performance: Better handling of complex queries.

Note: Ensure your resources explicitly support PostgreSQL before switching. Many legacy scripts use MySQL-specific syntax (like backticks `table` instead of quotes “table”).

Management Tools

• HeidiSQL: Free, lightweight Windows client. Great for quick edits.
• DBeaver: Cross-platform, supports both MySQL and Postgres. Best for power users.
• TablePlus: Modern UI, great for macOS/Windows.

Optimization Tips

1. Indexes: Ensure columns used in WHERE clauses (like identifier, license, vehicle_plate) are indexed.

   CREATE INDEX idx_users_identifier ON users(identifier);

2. Foreign Keys: Use them to prevent orphaned data (e.g., deleting a user should delete their vehicles).
3. Connection Pool: oxmysql handles this automatically, but ensure your database max_connections is high enough (default 151 is usually fine).