Database API

The Database component provides a unified interface for both MongoDB (primary) and MySQL (compatibility) database operations.

Overview

Access the Database component via Database.Game (for game data) or Database.Auth (for authentication data) after the Core:Shared:Ready event.

Dual Database Architecture

Database.Game    -- Primary database for game data (characters, inventory, vehicles, etc.)
Database.Auth    -- Separate database for authentication (users, roles, permissions)

MongoDB Primary

Document database for game data, characters, inventory

MySQL Support

Relational database via oxmysql for compatibility

Promise-Based

Async/await patterns with callbacks

Type-Safe

Automatic data type handling

Server-Side Only: Database operations are only available on the server. Never attempt database queries from client-side code.

MongoDB Operations

MongoDB is the primary database for Mythic Framework, storing characters, inventory, vehicles, and most game data.

findOne

Find a single document matching the query.

Database.Game:findOne(params, callback)

params

table

required

Parameter table with the following fields:

collection (string) - Collection name
query (table) - Query filter
options (table, optional) - Query options (projection, etc.)

callback

function

required

Callback function receiving (success, result)

success (boolean) - Whether query succeeded
result (table|nil) - Document found, or nil if not found

Examples:

-- Basic findOne
Database.Game:findOne({
    collection = 'characters',
    query = {
        SID = 1
    }
}, function(success, character)
    if success and character then
        print('Found character:', character.First, character.Last)
    else
        print('Character not found')
    end
end)

-- Query by multiple fields
Database.Game:findOne({
    collection = 'inventory',
    query = {
        owner = characterId,
        type = 'main'
    }
}, function(success, inventory)
    if success and inventory then
        -- Process inventory
    end
end)

-- With projection (only specific fields)
Database.Game:findOne({
    collection = 'characters',
    query = { SID = 1 },
    options = {
        projection = {
            First = 1,
            Last = 1,
            Cash = 1
        }
    }
}, function(success, character)
    -- character only contains First, Last, Cash fields
end)

find

Find multiple documents matching the query.

Database.Game:find(params, callback)

params

table

required

Parameter table with the following fields:

collection (string) - Collection name
query (table) - Query filter
options (table, optional) - Query options:
- limit (number) - Maximum documents to return
- skip (number) - Skip first N documents
- sort (table) - Sort order { field = 1 } (asc) or { field = -1 } (desc)
- projection (table) - Fields to include/exclude

callback

function

required

Callback function receiving (success, results)

success (boolean) - Whether query succeeded
results (table) - Array of documents (empty array if none found)

Examples:

-- Find all documents
Database.Game:find({
    collection = 'characters',
    query = {}
}, function(success, allCharacters)
    if success then
        print('Found', #allCharacters, 'characters')
    end
end)

-- Find with filter
Database.Game:find({
    collection = 'characters',
    query = {
        ['Jobs.Id'] = 'police'  -- Query array fields
    }
}, function(success, policeOfficers)
    -- Process results
end)

-- Find with limit and sort
Database.Game:find({
    collection = 'characters',
    query = {},
    options = {
        limit = 10,
        sort = { createdAt = -1 }  -- Newest first
    }
}, function(success, recentCharacters)
    -- Process results
end)

-- Find with pagination
Database.Game:find({
    collection = 'characters',
    query = {},
    options = {
        skip = 20,
        limit = 20  -- Page 2 (20 per page)
    }
}, function(success, page2)
    -- Process results
end)

-- Find with projection
Database.Game:find({
    collection = 'characters',
    query = {},
    options = {
        projection = {
            First = 1,
            Last = 1,
            _id = 0  -- Exclude _id
        }
    }
}, function(success, characterNames)
    -- Results only contain First and Last fields
end)

-- Complex query
local activePolice = COMPONENTS.Database:find('characters', {
    job = 'police',
    onDuty = true
}, {
    limit = 50,
    sort = { rank = -1 }
})

insertOne

Insert a single document into a collection.

COMPONENTS.Database:insertOne(collection, document, callback)

collection

string

required

MongoDB collection name

document

table

required

Document to insert as key-value pairs

callback

function

Callback function receiving (success, insertedId)

Examples:

-- Insert character
COMPONENTS.Database:insertOne('characters', {
    SID = 1,
    First = 'John',
    Last = 'Doe',
    DOB = '1990-01-15',
    Gender = 'Male',
    job = 'unemployed',
    createdAt = os.time()
}, function(success, id)
    if success then
        print('Character created with ID:', id)
    else
        print('Failed to create character')
    end
end)

-- Insert inventory
COMPONENTS.Database:insertOne('inventory', {
    owner = characterId,
    type = 'main',
    items = {},
    maxSlots = 50,
    maxWeight = 100
})

-- Insert log entry
COMPONENTS.Database:insertOne('logs', {
    type = 'admin_action',
    admin = adminId,
    action = 'teleport',
    target = targetId,
    timestamp = os.time()
})

insertMany

Insert multiple documents at once.

COMPONENTS.Database:insertMany(collection, documents, callback)

collection

string

required

MongoDB collection name

documents

table

required

Array of documents to insert

callback

function

Callback function receiving (success, insertedIds)

Example:

-- Insert multiple items
local items = {
    { name = 'water', label = 'Water Bottle', weight = 0.5 },
    { name = 'sandwich', label = 'Sandwich', weight = 0.3 },
    { name = 'phone', label = 'Phone', weight = 0.2 }
}

COMPONENTS.Database:insertMany('items', items, function(success, ids)
    if success then
        print('Inserted', #ids, 'items')
    end
end)

updateOne

Update a single document matching the query.

COMPONENTS.Database:updateOne(collection, query, update, callback)

collection

string

required

MongoDB collection name

query

table

required

Query filter to find document to update

update

table

required

Update operations using MongoDB update operators ($set, $inc, $push, etc.)

callback

function

Callback function receiving (success, modifiedCount)

Examples:

-- Update character job
COMPONENTS.Database:updateOne('characters',
    { SID = 1 },
    {
        ['$set'] = {
            job = 'police',
            grade = 'officer',
            onDuty = true
        }
    }
)

-- Increment money
COMPONENTS.Database:updateOne('characters',
    { SID = characterId },
    {
        ['$inc'] = {
            cash = 500  -- Add $500
        }
    }
)

-- Push to array
COMPONENTS.Database:updateOne('characters',
    { SID = characterId },
    {
        ['$push'] = {
            licenses = 'drivers_license'
        }
    }
)

-- Update nested field
COMPONENTS.Database:updateOne('inventory',
    { owner = characterId },
    {
        ['$set'] = {
            ['items.1.count'] = 5  -- Update item count in slot 1
        }
    }
)

-- Multiple operations
COMPONENTS.Database:updateOne('characters',
    { SID = characterId },
    {
        ['$set'] = {
            job = 'police',
            lastUpdated = os.time()
        },
        ['$inc'] = {
            arrests = 1
        }
    }
)

updateMany

Update all documents matching the query.

COMPONENTS.Database:updateMany(collection, query, update, callback)

Example:

-- Clock out all police officers
COMPONENTS.Database:updateMany('characters',
    { job = 'police', onDuty = true },
    {
        ['$set'] = {
            onDuty = false
        }
    },
    function(success, count)
        print('Clocked out', count, 'officers')
    end
)

-- Reset all character positions (server restart)
COMPONENTS.Database:updateMany('characters',
    {},
    {
        ['$set'] = {
            lastPosition = { x = 0, y = 0, z = 0 }
        }
    }
)

deleteOne

Delete a single document matching the query.

COMPONENTS.Database:deleteOne(collection, query, callback)

collection

string

required

MongoDB collection name

query

table

required

Query filter to find document to delete

callback

function

Callback function receiving (success, deletedCount)

Examples:

-- Delete character
COMPONENTS.Database:deleteOne('characters', { SID = 1 }, function(success, count)
    if success and count > 0 then
        print('Character deleted')
    else
        print('Character not found')
    end
end)

-- Delete inventory
COMPONENTS.Database:deleteOne('inventory', { owner = characterId })

-- Delete old log entry
COMPONENTS.Database:deleteOne('logs', { _id = logId })

deleteMany

Delete all documents matching the query.

COMPONENTS.Database:deleteMany(collection, query, callback)

Example:

-- Delete old logs (older than 30 days)
local thirtyDaysAgo = os.time() - (30 * 24 * 60 * 60)

COMPONENTS.Database:deleteMany('logs',
    { timestamp = { ['$lt'] = thirtyDaysAgo } },
    function(success, count)
        print('Deleted', count, 'old logs')
    end
)

-- Delete all vehicles for a character
COMPONENTS.Database:deleteMany('vehicles', { owner = characterId })

count

Count documents matching the query.

COMPONENTS.Database:count(collection, query, callback)

collection

string

required

MongoDB collection name

query

table

required

Query filter

callback

function

Callback function receiving (success, count)

Examples:

-- Count all characters
local totalCharacters = COMPONENTS.Database:count('characters', {})
print('Total characters:', totalCharacters)

-- Count online police
local onlinePolice = COMPONENTS.Database:count('characters', {
    job = 'police',
    onDuty = true
})

-- Count vehicles by type
local sportsCars = COMPONENTS.Database:count('vehicles', {
    class = 'Sports'
})

aggregate

Perform aggregation operations (advanced queries).

COMPONENTS.Database:aggregate(collection, pipeline, callback)

collection

string

required

MongoDB collection name

pipeline

table

required

Array of aggregation stages

callback

function

Callback function receiving (success, results)

Examples:

-- Count characters by job
COMPONENTS.Database:aggregate('characters', {
    {
        ['$group'] = {
            _id = '$job',
            count = { ['$sum'] = 1 }
        }
    },
    {
        ['$sort'] = { count = -1 }
    }
}, function(success, results)
    for _, result in ipairs(results) do
        print(result._id, 'has', result.count, 'members')
    end
end)

-- Get richest players
COMPONENTS.Database:aggregate('characters', {
    {
        ['$project'] = {
            name = { ['$concat'] = { '$First', ' ', '$Last' } },
            totalMoney = { ['$add'] = { '$cash', '$bank' } }
        }
    },
    {
        ['$sort'] = { totalMoney = -1 }
    },
    {
        ['$limit'] = 10
    }
})

MySQL Operations

MySQL operations use oxmysql for compatibility with existing FiveM resources.

fetchAll

Execute a SELECT query and return all results.

COMPONENTS.Database.MySQL:fetchAll(query, parameters, callback)

query

string

required

SQL SELECT query (use ? for parameters)

parameters

table

Array of parameter values to replace ? in query

callback

function

Callback function receiving results

Examples:

-- Fetch all banned players
local banned = COMPONENTS.Database.MySQL:fetchAll('SELECT * FROM bans WHERE active = ?', { 1 })

for _, ban in ipairs(banned) do
    print('Banned:', ban.player_name, 'Reason:', ban.reason)
end

-- Fetch characters for player
COMPONENTS.Database.MySQL:fetchAll(
    'SELECT * FROM characters WHERE owner = ? ORDER BY created_at DESC',
    { playerId },
    function(characters)
        print('Found', #characters, 'characters')
    end
)

-- Join query
local vehicles = COMPONENTS.Database.MySQL:fetchAll([[
    SELECT v.*, c.First, c.Last
    FROM vehicles v
    JOIN characters c ON v.owner = c.SID
    WHERE v.type = ?
]], { 'car' })

fetchScalar

Execute a query and return a single value.

COMPONENTS.Database.MySQL:fetchScalar(query, parameters, callback)

Examples:

-- Count total characters
local count = COMPONENTS.Database.MySQL:fetchScalar('SELECT COUNT(*) FROM characters')
print('Total characters:', count)

-- Get specific value
local cash = COMPONENTS.Database.MySQL:fetchScalar(
    'SELECT cash FROM characters WHERE SID = ?',
    { characterId }
)

execute

Execute an INSERT, UPDATE, or DELETE query.

COMPONENTS.Database.MySQL:execute(query, parameters, callback)

query

string

required

SQL query (INSERT, UPDATE, DELETE)

parameters

table

Array of parameter values

callback

function

Callback receiving affected rows or insert ID

Examples:

-- Insert
COMPONENTS.Database.MySQL:execute(
    'INSERT INTO logs (type, player, action, timestamp) VALUES (?, ?, ?, ?)',
    { 'admin', playerId, 'teleport', os.time() },
    function(insertId)
        print('Log created with ID:', insertId)
    end
)

-- Update
COMPONENTS.Database.MySQL:execute(
    'UPDATE characters SET cash = cash + ? WHERE SID = ?',
    { 500, characterId }
)

-- Delete
COMPONENTS.Database.MySQL:execute(
    'DELETE FROM logs WHERE timestamp < ?',
    { thirtyDaysAgo },
    function(affectedRows)
        print('Deleted', affectedRows, 'old logs')
    end
)

transaction

Execute multiple queries in a transaction.

COMPONENTS.Database.MySQL:transaction(queries, callback)

Example:

-- Transfer money between characters
COMPONENTS.Database.MySQL:transaction({
    {
        query = 'UPDATE characters SET cash = cash - ? WHERE SID = ?',
        parameters = { amount, senderId }
    },
    {
        query = 'UPDATE characters SET cash = cash + ? WHERE SID = ?',
        parameters = { amount, receiverId }
    },
    {
        query = 'INSERT INTO transactions (from, to, amount, timestamp) VALUES (?, ?, ?, ?)',
        parameters = { senderId, receiverId, amount, os.time() }
    }
}, function(success)
    if success then
        print('Transaction completed')
    else
        print('Transaction failed (rolled back)')
    end
end)

Best Practices

Use MongoDB for Game Data

MongoDB is preferred for:

Characters, inventory, vehicles (flexible schema)
Nested data structures
Arrays and embedded documents
Rapidly changing data

MySQL is better for:

Transactional data (banking, trades)
Relations between entities
Legacy compatibility
Complex joins

-- ✅ Good: Store character in MongoDB
COMPONENTS.Database:insertOne('characters', {
    SID = 1,
    First = 'John',
    Last = 'Doe',
    metadata = {  -- Nested data
        appearance = { ... },
        tattoos = { ... }
    },
    licenses = { 'drivers', 'weapon' }  -- Array
})

Always Use Parameters

❌ Dangerous (SQL Injection):

local query = 'SELECT * FROM users WHERE name = "' .. playerName .. '"'
COMPONENTS.Database.MySQL:fetchAll(query)  -- Vulnerable!

✅ Safe (Parameterized):

COMPONENTS.Database.MySQL:fetchAll(
    'SELECT * FROM users WHERE name = ?',
    { playerName }  -- Safe from injection
)

Handle Errors Gracefully

-- ❌ Bad: Assume success
local character = COMPONENTS.Database:findOne('characters', { SID = id })
print(character.First)  -- May error if not found

-- ✅ Good: Check result
local character = COMPONENTS.Database:findOne('characters', { SID = id })
if character then
    print(character.First)
else
    COMPONENTS.Logger:Warn('Database', 'Character not found', { SID = id })
end

-- ✅ Better: Use callback with error handling
COMPONENTS.Database:findOne('characters', { SID = id }, function(success, character)
    if success and character then
        print(character.First)
    else
        COMPONENTS.Logger:Error('Database', 'Failed to fetch character')
    end
end)

Index Your Collections

Create indexes for frequently queried fields:

// MongoDB shell
db.characters.createIndex({ SID: 1 }, { unique: true })
db.inventory.createIndex({ owner: 1 })
db.vehicles.createIndex({ owner: 1, plate: 1 })
db.logs.createIndex({ timestamp: -1 })

-- MySQL
CREATE INDEX idx_owner ON vehicles(owner);
CREATE INDEX idx_timestamp ON logs(timestamp);

Optimize Queries

❌ Bad: Fetch everything

local allCharacters = COMPONENTS.Database:find('characters', {})
for _, char in ipairs(allCharacters) do
    if char.job == 'police' then
        -- Process
    end
end

✅ Good: Filter at database

local police = COMPONENTS.Database:find('characters', { job = 'police' })
for _, char in ipairs(police) do
    -- Process
end

✅ Better: Use projection

local police = COMPONENTS.Database:find('characters',
    { job = 'police' },
    {
        projection = { First = 1, Last = 1, rank = 1 }  -- Only needed fields
    }
)

Next Steps

Base API

Core framework exports

Logger API

Logging component

Database Architecture

Understanding the database system

Database Setup

Configure databases

Performance Note: Database queries are I/O operations and can be slow. Always use indexes, limit results, and avoid queries in tight loops or every frame.

Core API

Characters

Inventory

Jobs

Vehicles

HUD & Status

Overview

Dual Database Architecture

MongoDB Primary

MySQL Support

Promise-Based

Type-Safe

MongoDB Operations

findOne

find

insertOne

insertMany

updateOne

updateMany

deleteOne

deleteMany

count

aggregate

MySQL Operations

fetchAll

fetchScalar

execute

transaction

Best Practices

Next Steps

Base API

Logger API

Database Architecture

Database Setup

Core API

Characters

Inventory

Jobs

Vehicles

HUD & Status

​Overview

​Dual Database Architecture

MongoDB Primary

MySQL Support

Promise-Based

Type-Safe

​MongoDB Operations

​findOne

​find

​insertOne

​insertMany

​updateOne

​updateMany

​deleteOne

​deleteMany

​count

​aggregate

​MySQL Operations

​fetchAll

​fetchScalar

​execute

​transaction

​Best Practices

​Next Steps

Base API

Logger API

Database Architecture

Database Setup

Overview

Dual Database Architecture

MongoDB Operations

findOne

find

insertOne

insertMany

updateOne

updateMany

deleteOne

deleteMany

count

aggregate

MySQL Operations

fetchAll

fetchScalar

execute

transaction

Best Practices

Next Steps