OxMySQL

OxMySQL

A replacement of mysql-async and ghmattimysql with expanded API and improved compatibility for MySQL 8.

MySQL or MariaDB?

Most resources for FiveM were designed to be used with MySQL 5.7 and may hit compatibility issues when using MySQL 8, i.e.

  • More reserved keywords, like 'stored' and 'group'.
  • Longtext / JSON fields do not support default values.

MariaDB (opens in a new tab) is highly recommended for compatibility, and improved performance (over all versions of MySQL).

Should I use XAMPP?

No. XAMPP is a webserver stack intended to be used for development, allowing easy local development and testing.
Do not setup XAMPP just to run your database, and install MariaDB (opens in a new tab) directly instead.

Installation

Download and install MariaDB (opens in a new tab)

Download the latest release (opens in a new tab) of oxmysql

Configure your server

# Use your preferred format. Make sure you only ever use set.
set mysql_connection_string "mysql://root:12345@localhost:3306/fivem"
set mysql_connection_string "user=root;password=12345;host=localhost;port=3306;database=fivem"

Certain special characters are reserved or unsupported depending on your connection string.
Avoid using these characters ; , / ? : @ & = + $ #, and try swapping connection string format.

Slow query warnings

You will receive warnings if a query took a long time to complete, configurable with a convar.

  • Query time may not be entirely accurate.
  • Slow queries may not indicate a database issue (e.g. server hitches).
  • Slow queries on server startup are not necessarily problematic.
set mysql_slow_query_warning 150

Debug

Enabling the debug option will print all queries in the server console; you can also use an array to only print from a list of resources instead.

set mysql_debug true
set mysql_debug [
  "ox_core",
  "ox_inventory"
]

You can temporarily modify the resource list with commands.

oxmysql_debug remove ox_core
oxmysql_debug add ox_core

Compatibility

You can delete the following resources and allow oxmysql to provide their functionality.

  • mysql-async
  • ghmattimysql

Usage

Resources can import oxmysql methods by including our library, granting some type-checking and minor performance improvements over raw export calls.

Lua

Modify fxmanifest.lua for your resource, and add the following above any other script files.

server_script '@oxmysql/lib/MySQL.lua'

JavaScript

You can use raw exports, or install our npm package (opens in a new tab) for intellisense and similar usage as Lua.

# With pnpm
pnpm add @overextended/oxmysql
 
# With Yarn
yarn add @overextended/oxmysql
 
# With npm
npm install @overextended/oxmysql

Import the oxmysql object into your resource.

import { oxmysql as MySQL } from '@overextended/oxmysql';

Upserting

When uncertain if a row should be inserted into the database, or an existing row should be updated, queries should check for duplicate keys.

MySQL.prepare('INSERT INTO ox_inventory (owner, name, data) VALUES (?, ?, ?) ON DUPLICATE KEY UPDATE data = VALUES(data)', { owner, dbId, inventory })

This is preferred over checking the existence of a row, then inserting or updating depending on the result.
Furthermore, unlike using 'REPLACE INTO', the row is not deleted and re-inserted.