OxMySQL Complete Function Reference

This documentation covers all available OxMySQL functions with a focus on using exports for optimal compatibility and performance. OxMySQL is a modern MySQL wrapper for FiveM that provides both Promise-based and Callback-based database operations.

Table of Contents

1. insert
2. query
3. single
4. scalar
5. prepare
6. rawExecute
7. transaction
8. Best Practices
9. Error Handling
10. Performance Considerations

Insert > Description

Inserts a new entry into the database and returns the insert ID for the row, if valid.

Insert > Export Syntax (Recommended) > Promise-based (Async)

local id = exports.oxmysql:insert_async('INSERT INTO `users` (identifier, firstname, lastname) VALUES (?, ?, ?)', {
    identifier, firstName, lastName
})
print(id)

Insert > Export Syntax (Recommended) > Callback-based

exports.oxmysql:insert('INSERT INTO `users` (identifier, firstname, lastname) VALUES (?, ?, ?)', {
    identifier, firstName, lastName
}, function(id)
    print(id)
end)

Insert > Alternative Syntax > Promise (MySQL.insert.await)

local id = MySQL.insert.await('INSERT INTO `users` (identifier, firstname, lastname) VALUES (?, ?, ?)', {
    identifier, firstName, lastName
})
print(id)

Insert > Alternative Syntax > Callback (MySQL.insert)

MySQL.insert('INSERT INTO `users` (identifier, firstname, lastname) VALUES (?, ?, ?)', {
    identifier, firstName, lastName
}, function(id)
    print(id)
end)

Insert > Available Aliases

• MySQL.Sync.insert
• exports.ghmattimysql.executeSync
• MySQL.Async.insert
• exports.ghmattimysql.execute

Insert > Use Cases

• Creating new user accounts
• Adding new records to any table
• Logging system events
• Inventory item creation

Insert > Return Value

• Success: Returns the auto-increment ID of the inserted row
• Failure: Returns nil or false

Query > Description

When selecting data, returns all matching rows and columns; otherwise, returns data like insertId, affectedRows, etc. This is the most commonly used function for SELECT operations.

Query > Export Syntax (Recommended) > Promise-based (Async)

local response = exports.oxmysql:query_async('SELECT `firstname`, `lastname` FROM `users` WHERE `identifier` = ?', {
    identifier
})
if response then
    for i = 1, #response do
        local row = response[i]
        print(row.firstname, row.lastname)
    end
end

Query > Export Syntax (Recommended) > Callback-based

exports.oxmysql:query('SELECT `firstname`, `lastname` FROM `users` WHERE `identifier` = ?', {
    identifier
}, function(response)
    if response then
        for i = 1, #response do
            local row = response[i]
            print(row.firstname, row.lastname)
        end
    end
end)

Query > Alternative Syntax > Promise (MySQL.query.await)

local response = MySQL.query.await('SELECT `firstname`, `lastname` FROM `users` WHERE `identifier` = ?', {
    identifier
})
if response then
    for i = 1, #response do
        local row = response[i]
        print(row.firstname, row.lastname)
    end
end

Query > Alternative Syntax > Callback (MySQL.query)

MySQL.query('SELECT `firstname`, `lastname` FROM `users` WHERE `identifier` = ?', {
    identifier
}, function(response)
    if response then
        for i = 1, #response do
            local row = response[i]
            print(row.firstname, row.lastname)
        end
    end
end)

Query > Available Aliases

• MySQL.Sync.fetchAll
• exports.ghmattimysql.execute
• MySQL.Async.fetchAll

Query > Use Cases

• Fetching multiple user records
• Getting all items in inventory
• Retrieving lists of vehicles, properties, etc.
• Complex JOIN operations returning multiple rows

Query > Return Value

• Success: Array of row objects, each containing column key-value pairs
• Empty Result: Empty array {}
• Failure: nil or false

Single > Description

Returns all selected columns for a single row. Ideal when you expect exactly one result.

Single > Export Syntax (Recommended) > Promise-based (Async)

local row = exports.oxmysql:single_async('SELECT `firstname`, `lastname` FROM `users` WHERE `identifier` = ? LIMIT 1', {
    identifier
})
if not row then return end
print(row.firstname, row.lastname)

Single > Export Syntax (Recommended) > Callback-based

exports.oxmysql:single('SELECT `firstname`, `lastname` FROM `users` WHERE `identifier` = ? LIMIT 1', {
    identifier
}, function(row)
    if not row then return end
    print(row.firstname, row.lastname)
end)

Single > Alternative Syntax > Promise (MySQL.single.await)

local row = MySQL.single.await('SELECT `firstname`, `lastname` FROM `users` WHERE `identifier` = ? LIMIT 1', {
    identifier
})
if not row then return end
print(row.firstname, row.lastname)

Single > Alternative Syntax > Callback (MySQL.single)

MySQL.single('SELECT `firstname`, `lastname` FROM `users` WHERE `identifier` = ? LIMIT 1', {
    identifier
}, function(row)
    if not row then return end
    print(row.firstname, row.lastname)
end)

Single > Use Cases

• Getting specific user data by unique identifier
• Fetching a single vehicle by plate number
• Retrieving player statistics
• Login validation queries

Single > Return Value

• Success: Single row object with column key-value pairs
• No Result: nil
• Failure: nil

Scalar > Description

Returns the first column for a single row. Perfect when you need just one value.

Scalar > Export Syntax (Recommended) > Promise-based (Async)

local firstName = exports.oxmysql:scalar_async('SELECT `firstname` FROM `users` WHERE `identifier` = ? LIMIT 1', {
    identifier
})
print(firstName)

Scalar > Export Syntax (Recommended) > Callback-based

exports.oxmysql:scalar('SELECT `firstname` FROM `users` WHERE `identifier` = ? LIMIT 1', {
    identifier
}, function(firstName)
    print(firstName)
end)

Scalar > Alternative Syntax > Promise (MySQL.scalar.await)

local firstName = MySQL.scalar.await('SELECT `firstname` FROM `users` WHERE `identifier` = ? LIMIT 1', {
    identifier
})
print(firstName)

Scalar > Alternative Syntax > Callback (MySQL.scalar)

MySQL.scalar('SELECT `firstname` FROM `users` WHERE `identifier` = ? LIMIT 1', {
    identifier
}, function(firstName)
    print(firstName)
end)

Scalar > Available Aliases

• MySQL.Sync.fetchScalar
• exports.ghmattimysql.scalar
• MySQL.Async.fetchScalar

Scalar > Use Cases

• Getting player count: SELECT COUNT(*) FROM users
• Checking if record exists: SELECT 1 FROM users WHERE id = ?
• Getting single values like money, level, etc.
• Simple validation queries

Scalar > Return Value

• Success: The actual value of the first column
• No Result: nil
• Failure: nil

Prepare > Description

Prepare can be used to execute frequently called queries faster and accepts multiple sets of parameters. Unlike rawExecute, SELECT statements return columns, rows, or arrays depending on results.

Prepare > Export Syntax (Recommended) > Promise-based (Async)

local response = exports.oxmysql:prepare_async('SELECT `firstname`, `lastname` FROM `users` WHERE `identifier` = ?', {
    identifier
})
print(json.encode(response, { indent = true, sort_keys = true }))

Prepare > Export Syntax (Recommended) > Callback-based

exports.oxmysql:prepare('SELECT `firstname`, `lastname` FROM `users` WHERE `identifier` = ?', {
    identifier
}, function(response)
    print(json.encode(response, { indent = true, sort_keys = true }))
end)

Prepare > Alternative Syntax > Promise (MySQL.prepare.await)

local response = MySQL.prepare.await('SELECT `firstname`, `lastname` FROM `users` WHERE `identifier` = ?', {
    identifier
})
print(json.encode(response, { indent = true, sort_keys = true }))

Prepare > Alternative Syntax > Callback (MySQL.prepare)

MySQL.prepare('SELECT `firstname`, `lastname` FROM `users` WHERE `identifier` = ?', {
    identifier
}, function(response)
    print(json.encode(response, { indent = true, sort_keys = true }))
end)

Prepare > Important Notes

• Date Handling: Date will not return the datestring commonly used in FiveM
• Boolean Handling: TINYINT 1 and BIT will not return a boolean
• Placeholders: You can only use ? value placeholders; ?? column placeholders and named placeholders will throw an error

Prepare > Use Cases

• Frequently executed queries for performance optimization
• Batch operations with multiple parameter sets
• Complex queries that benefit from preparation
• High-performance database operations

Prepare > Performance Benefits

• Query compilation happens once
• Reduced parsing overhead
• Better performance for repeated execution

RawExecute > Description

rawExecute can be used to execute frequently called queries faster and accepts multiple sets of parameters. Unlike prepare, SELECT statements always return an array of rows.

RawExecute > Export Syntax (Recommended) > Promise-based (Async)

local response = exports.oxmysql:rawExecute_async('SELECT `firstname`, `lastname` FROM `users` WHERE `identifier` = ?', {
    identifier
})
print(json.encode(response, { indent = true, sort_keys = true }))

RawExecute > Export Syntax (Recommended) > Callback-based

exports.oxmysql:rawExecute('SELECT `firstname`, `lastname` FROM `users` WHERE `identifier` = ?', {
    identifier
}, function(response)
    print(json.encode(response, { indent = true, sort_keys = true }))
end)

RawExecute > Alternative Syntax > Promise (MySQL.rawExecute.await)

local response = MySQL.rawExecute.await('SELECT `firstname`, `lastname` FROM `users` WHERE `identifier` = ?', {
    identifier
})
print(json.encode(response, { indent = true, sort_keys = true }))

RawExecute > Alternative Syntax > Callback (MySQL.rawExecute)

MySQL.rawExecute('SELECT `firstname`, `lastname` FROM `users` WHERE `identifier` = ?', {
    identifier
}, function(response)
    print(json.encode(response, { indent = true, sort_keys = true }))
end)

RawExecute > Key Differences from Prepare

• Always returns an array of rows for SELECT statements
• When using SELECT, return value matches query, single, or scalar depending on columns/rows selected
• Same performance benefits as prepare
• Same placeholder restrictions apply

RawExecute > Important Notes

• Date Handling: Date will not return the datestring commonly used in FiveM
• Boolean Handling: TINYINT 1 and BIT will not return a boolean
• Placeholders: You can only use ? value placeholders

RawExecute > Use Cases

• When you always want array results regardless of row count
• High-performance batch operations
• Consistent return type requirements
• Complex query optimization

Transaction > Description

A transaction executes multiple queries and commits them only if all succeed. If one fails, none of the queries are committed. Returns a boolean indicating transaction success.

Transaction > Export Syntax (Recommended) > Promise-based (Async)

-- Specific format
local queries = {
    { query = 'INSERT INTO `test` (id) VALUES (?)', values = { 1 }},
    { query = 'INSERT INTO `test` (id, name) VALUES (?, ?)', values = { 2, 'bob' }},
}

local success = exports.oxmysql:transaction_async(queries)
print(success)

-- Shared format
local queries = {
    'INSERT INTO `test` (id, name) VALUES (@someid, @somename)',
    'SET `name` = @newname IN `test` WHERE `id` = @someid'
}
local values = {
    someid = 2,
    somename = 'John Doe',
    newname = 'John Notdoe'
}

local success = exports.oxmysql:transaction_async(queries, values)
print(success)

Transaction > Export Syntax (Recommended) > Callback-based

-- Specific format
exports.oxmysql:transaction(queries, function(success)
    print(success)
end)

-- Shared format
exports.oxmysql:transaction(queries, values, function(success)
    print(success)
end)

Transaction > Alternative Syntax > Promise (MySQL.transaction.await)

local success = MySQL.transaction.await(queries, values) -- leave values nil for specific format
print(success)

Transaction > Alternative Syntax > Callback (MySQL.transaction)

-- Specific format
MySQL.transaction(queries, function(success)
    print(success)
end)

-- Shared format
MySQL.transaction(queries, values, function(success)
    print(success)
end)

Transaction > Available Aliases

• MySQL.Sync.transaction
• exports.ghmattimysql.transaction
• MySQL.Async.transaction

Transaction > Transaction Formats > Specific Format

Each query has its own parameters:

local queries = {
    { query = 'INSERT INTO `test` (id) VALUES (?)', values = { 1 }},
    { query = 'INSERT INTO `test` (id, name) VALUES (?, ?)', values = { 2, 'bob' }},
}

-- Alternative array format
local queries = {
    { 'INSERT INTO `test` (id) VALUES (?)', { 1 } },
    { 'INSERT INTO `test` (id, name) VALUES (?, ?)', { 2, 'bob' } },
}

Transaction > Transaction Formats > Shared Format

All queries share the same named parameters:

local queries = {
    'INSERT INTO `test` (id, name) VALUES (@someid, @somename)',
    'UPDATE `test` SET `name` = @newname WHERE `id` = @someid'
}
local values = {
    someid = 2,
    somename = 'John Doe',
    newname = 'John Notdoe'
}

Transaction > Transaction Isolation Levels

Set through the convar mysql_transaction_isolation_level (1-4, default: 2):

Transaction > Use Cases

• Financial operations (money transfers, purchases)
• Complex data updates requiring consistency
• Batch operations that must all succeed
• Critical business logic requiring ACID properties

Transaction > Return Value

• Success: true - All queries executed successfully
• Failure: false - One or more queries failed, all rolled back

Best Practices > 1. Use Exports for Better Compatibility

-- Recommended: Using exports
local userData = exports.oxmysql:single_async('SELECT * FROM users WHERE id = ?', { userId })

-- Alternative: Direct MySQL calls
local userData = MySQL.single.await('SELECT * FROM users WHERE id = ?', { userId })

Best Practices > 2. Choose the Right Function for Your Use Case

-- Getting multiple rows
local allUsers = exports.oxmysql:query_async('SELECT * FROM users')

-- Getting one row
local user = exports.oxmysql:single_async('SELECT * FROM users WHERE id = ? LIMIT 1', { userId })

-- Getting one value
local userCount = exports.oxmysql:scalar_async('SELECT COUNT(*) FROM users')

-- Inserting data
local insertId = exports.oxmysql:insert_async('INSERT INTO users (name) VALUES (?)', { name })

Best Practices > 3. Always Validate Results

local user = exports.oxmysql:single_async('SELECT * FROM users WHERE id = ?', { userId })
if not user then
    print('User not found')
    return
end

-- Safe to use user data
print('Welcome, ' .. user.firstname)

Best Practices > 4. Use Transactions for Related Operations

-- Money transfer example
local queries = {
    { query = 'UPDATE accounts SET balance = balance - ? WHERE id = ?', values = { amount, fromAccount } },
    { query = 'UPDATE accounts SET balance = balance + ? WHERE id = ?', values = { amount, toAccount } },
    { query = 'INSERT INTO transactions (from_account, to_account, amount) VALUES (?, ?, ?)', values = { fromAccount, toAccount, amount } }
}

local success = exports.oxmysql:transaction_async(queries)
if success then
    print('Transfer completed successfully')
else
    print('Transfer failed - all changes rolled back')
end

Best Practices > 5. Use Parameterized Queries to Prevent SQL Injection

-- GOOD: Parameterized query
local user = exports.oxmysql:single_async('SELECT * FROM users WHERE name = ?', { playerName })

-- BAD: String concatenation (vulnerable to SQL injection)
-- local user = exports.oxmysql:single_async('SELECT * FROM users WHERE name = "' .. playerName .. '"')

Best Practices > 6. Handle Database Errors Gracefully

local function getUserSafely(userId)
    local success, result = pcall(function()
        return exports.oxmysql:single_async('SELECT * FROM users WHERE id = ?', { userId })
    end)
    
    if not success then
        print('Database error:', result)
        return nil
    end
    
    return result
end

Error Handling > Promise-based Error Handling

local function safeQuery()
    local success, result = pcall(function()
        return exports.oxmysql:query_async('SELECT * FROM users WHERE active = ?', { true })
    end)
    
    if not success then
        print('Query failed:', result)
        return {}
    end
    
    return result or {}
end

Error Handling > Callback-based Error Handling

exports.oxmysql:query('SELECT * FROM users WHERE active = ?', { true }, function(result)
    if not result then
        print('Query failed or returned no results')
        return
    end
    
    -- Process results
    for i = 1, #result do
        print('User:', result[i].name)
    end
end)

Error Handling > Transaction Error Handling

local queries = {
    { query = 'INSERT INTO logs (action) VALUES (?)', values = { 'user_login' } },
    { query = 'UPDATE users SET last_login = NOW() WHERE id = ?', values = { userId } }
}

local success = exports.oxmysql:transaction_async(queries)
if not success then
    print('Failed to log user login - transaction rolled back')
    -- Handle the error appropriately
    TriggerClientEvent('notify', source, 'Login logging failed', 'error')
end

Performance Considerations > 1. Connection Pooling

OxMySQL automatically handles connection pooling. Configure pool size in your server.cfg:

set mysql_connection_string "mysql://user:password@localhost/database?pool_size=10"

Performance Considerations > 2. Query Optimization

-- Use LIMIT when you only need one result
local user = exports.oxmysql:single_async('SELECT * FROM users WHERE email = ? LIMIT 1', { email })

-- Use specific columns instead of SELECT *
local userName = exports.oxmysql:scalar_async('SELECT name FROM users WHERE id = ?', { userId })

-- Use indexes for frequently queried columns
-- CREATE INDEX idx_user_email ON users(email);

Performance Considerations > 3. Batch Operations

-- Instead of multiple individual queries
for i = 1, #items do
    exports.oxmysql:insert_async('INSERT INTO inventory (item_id, quantity) VALUES (?, ?)', { items[i].id, items[i].quantity })
end

-- Use a transaction for better performance
local queries = {}
for i = 1, #items do
    queries[#queries + 1] = {
        query = 'INSERT INTO inventory (item_id, quantity) VALUES (?, ?)',
        values = { items[i].id, items[i].quantity }
    }
end
exports.oxmysql:transaction_async(queries)

Performance Considerations > 4. Prepare vs Regular Queries

Use prepare or rawExecute for frequently executed queries:

-- For queries executed many times
local getUserQuery = 'SELECT * FROM users WHERE id = ?'

-- Regular execution (less efficient for repeated use)
local user1 = exports.oxmysql:single_async(getUserQuery, { 1 })
local user2 = exports.oxmysql:single_async(getUserQuery, { 2 })

-- Prepared execution (more efficient for repeated use)
local user1 = exports.oxmysql:prepare_async(getUserQuery, { 1 })
local user2 = exports.oxmysql:prepare_async(getUserQuery, { 2 })

Complete Example: User Management System

-- User Management Class using OxMySQL exports
local UserManager = {}

-- Create a new user
function UserManager.CreateUser(identifier, firstname, lastname, email)
    local insertId = exports.oxmysql:insert_async(
        'INSERT INTO users (identifier, firstname, lastname, email, created_at) VALUES (?, ?, ?, ?, NOW())',
        { identifier, firstname, lastname, email }
    )
    
    if insertId then
        print('User created with ID:', insertId)
        return insertId
    else
        print('Failed to create user')
        return false
    end
end

-- Get user by identifier
function UserManager.GetUser(identifier)
    local user = exports.oxmysql:single_async(
        'SELECT * FROM users WHERE identifier = ? LIMIT 1',
        { identifier }
    )
    
    return user
end

-- Get all active users
function UserManager.GetActiveUsers()
    local users = exports.oxmysql:query_async(
        'SELECT id, firstname, lastname, email FROM users WHERE active = ? ORDER BY lastname',
        { true }
    )
    
    return users or {}
end

-- Update user email
function UserManager.UpdateEmail(userId, newEmail)
    local affectedRows = exports.oxmysql:scalar_async(
        'UPDATE users SET email = ?, updated_at = NOW() WHERE id = ?',
        { newEmail, userId }
    )
    
    return affectedRows > 0
end

-- Transfer money between users (using transaction)
function UserManager.TransferMoney(fromUserId, toUserId, amount)
    local queries = {
        {
            query = 'UPDATE users SET money = money - ? WHERE id = ? AND money >= ?',
            values = { amount, fromUserId, amount }
        },
        {
            query = 'UPDATE users SET money = money + ? WHERE id = ?',
            values = { amount, toUserId }
        },
        {
            query = 'INSERT INTO transactions (from_user, to_user, amount, created_at) VALUES (?, ?, ?, NOW())',
            values = { fromUserId, toUserId, amount }
        }
    }
    
    local success = exports.oxmysql:transaction_async(queries)
    
    if success then
        print(string.format('Successfully transferred $%d from user %d to user %d', amount, fromUserId, toUserId))
    else
        print('Transfer failed - insufficient funds or database error')
    end
    
    return success
end

-- Get user count
function UserManager.GetUserCount()
    local count = exports.oxmysql:scalar_async('SELECT COUNT(*) FROM users WHERE active = ?', { true })
    return count or 0
end

-- Delete user (soft delete)
function UserManager.DeleteUser(userId)
    local success = exports.oxmysql:scalar_async(
        'UPDATE users SET active = ?, deleted_at = NOW() WHERE id = ?',
        { false, userId }
    )
    
    return success > 0
end

return UserManager

Migration from mysql-async

If you're migrating from mysql-async, here are the equivalent OxMySQL export calls:

-- mysql-async -> OxMySQL exports
MySQL.Async.fetchAll() -> exports.oxmysql:query()
MySQL.Async.fetchScalar() -> exports.oxmysql:scalar()
MySQL.Async.insert() -> exports.oxmysql:insert()
MySQL.Async.execute() -> exports.oxmysql:query()

-- For async/await style
MySQL.Sync.fetchAll() -> exports.oxmysql:query_async()
MySQL.Sync.fetchScalar() -> exports.oxmysql:scalar_async()
MySQL.Sync.insert() -> exports.oxmysql:insert_async()

This documentation covers the complete OxMySQL function reference with emphasis on using exports for better compatibility and performance. Always prefer the export syntax for new projects and when updating existing code.