Database Architecture

Mythic Framework uses a dual-database architecture: MongoDB as the primary database for game data, and MySQL for compatibility and relational data. This approach combines the flexibility of NoSQL with the structure of SQL.

Why Two Databases?

MongoDB (Primary)

Best for:

Flexible schemas
Nested data (inventory, character stats)
Fast reads/writes
JSON-like documents
Scalability

MySQL (Secondary)

Best for:

Structured data
Complex queries
Relationships/joins
Compatibility with other resources
SQL familiarity

Database Usage

MongoDB Databases

Mythic Framework uses two MongoDB databases:

// Auth Database - User authentication and permissions
{
    database: "auth",
    collections: [
        "users",          // User accounts
        "roles",          // Permission roles
        "bans",           // Ban records
        "whitelist"       // Whitelist entries
    ]
}

// Game Database - All game-related data
{
    database: "fivem",
    collections: [
        "characters",     // Player characters
        "inventory",      // Character inventories
        "vehicles",       // Player vehicles
        "properties",     // Owned properties
        "jobs",           // Job data
        "businesses",     // Business data
        "logs",           // Game logs
        // ... many more
    ]
}

MySQL Database

-- Single database: MythicFramework_345AE9
-- Tables:
- persistent_data      -- Long-term persistent data
- vehicle_ownership    -- Vehicle records
- property_ownership   -- Property records
- transaction_logs     -- Financial transactions
-- And more based on resource needs

MongoDB Usage

Accessing MongoDB

MongoDB operations are handled through the Node.js wrapper in mythic-base using asynchronous callback patterns. Location: mythic-base/core/sv_database.js

Database Architecture: Mythic uses two separate MongoDB databases:

Database.Game - Game data (characters, inventory, vehicles, etc.)
Database.Auth - Authentication data (users, roles, bans)

-- Find one document (with callback)
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)

-- Find multiple documents
Database.Game:find({
    collection = 'vehicles',
    query = {
        owner = characterId,
        stored = false
    },
    options = {
        limit = 10,
        sort = { purchaseDate = -1 }
    }
}, function(success, vehicles)
    if success and vehicles then
        for _, vehicle in ipairs(vehicles) do
            print('Vehicle:', vehicle.model)
        end
    end
end)

-- Insert document
Database.Game:insertOne({
    collection = 'logs',
    document = {
        type = 'admin_action',
        action = 'teleport',
        admin = adminId,
        target = targetId,
        timestamp = os.time()
    }
}, function(success, result)
    if success then
        print('Log inserted with ID:', result._id)
    end
end)

-- Update document
Database.Game:updateOne({
    collection = 'characters',
    query = {
        SID = characterId
    },
    update = {
        ['$set'] = {
            Cash = newMoney
        }
    }
}, function(success, result)
    if success then
        print('Character updated')
    end
end)

-- Delete document (Auth database example)
Database.Auth:deleteOne({
    collection = 'bans',
    query = {
        identifier = 'steam:110000...'
    }
}, function(success, result)
    if success then
        print('Ban record deleted')
    end
end)

MongoDB Methods

findOne(params, callback)

Find a single document matching query.

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

Callback receives: (success, document)

find(params, callback)

Find multiple documents.

Database.Game:find({
    collection = 'characters',
    query = {
        User = accountId
    },
    options = {
        limit = 5,
        sort = { created = -1 }
    }
}, function(success, chars)
    if success and chars then
        for _, char in ipairs(chars) do
            print(char.First, char.Last)
        end
    end
end)

Callback receives: (success, documents)

insertOne(params, callback)

Insert a single document.

Database.Game:insertOne({
    collection = 'characters',
    document = {
        SID = GetNextSID(),
        User = accountId,
        First = 'John',
        Last = 'Doe',
        Gender = 0,
        DOB = '1990-01-01',
        Phone = 5551234,
        Created = os.time()
    }
}, function(success, result)
    if success then
        print('Character created with ID:', result._id)
    end
end)

Callback receives: (success, insertedDocument)

updateOne(params, callback)

Update a single document.

-- Set fields
Database.Game:updateOne({
    collection = 'characters',
    query = { SID = 1 },
    update = {
        ['$set'] = {
            Phone = 5559999,
            LastSeen = os.time()
        }
    }
}, function(success, result)
    if success then
        print('Character updated')
    end
end)

-- Increment field
Database.Game:updateOne({
    collection = 'characters',
    query = { SID = 1 },
    update = {
        ['$inc'] = {
            Cash = 500
        }
    }
}, function(success, result)
    print('Added $500 to character')
end)

-- Push to array
Database.Game:updateOne({
    collection = 'characters',
    query = { SID = 1 },
    update = {
        ['$push'] = {
            ['Jobs'] = jobObject
        }
    }
}, function(success, result)
    print('Job added to character')
end)

Callback receives: (success, updateResult)

deleteOne(params, callback)

Delete a single document.

Database.Game:deleteOne({
    collection = 'vehicles',
    query = { _id = vehicleId }
}, function(success, result)
    if success then
        print('Vehicle deleted')
    end
end)

Callback receives: (success, deleteResult)

count(params, callback)

Count documents matching query.

Database.Game:count({
    collection = 'vehicles',
    query = { owner = characterId }
}, function(success, count)
    if success then
        print('Player owns', count, 'vehicles')
    end
end)

Callback receives: (success, count)

aggregate(params, callback)

Advanced aggregation queries.

Database.Game:aggregate({
    collection = 'characters',
    aggregate = {
        {
            ['$group'] = {
                _id = '$Gender',
                count = { ['$sum'] = 1 },
                avgCash = { ['$avg'] = '$Cash' }
            }
        }
    }
}, function(success, stats)
    if success and stats then
        for _, stat in ipairs(stats) do
            print('Gender', stat._id, 'Count:', stat.count)
        end
    end
end)

Callback receives: (success, results)

MongoDB Query Operators

-- Comparison
{ cash = { ['$gt'] = 1000 } }              -- Greater than
{ cash = { ['$gte'] = 1000 } }             -- Greater than or equal
{ cash = { ['$lt'] = 1000 } }              -- Less than
{ cash = { ['$lte'] = 1000 } }             -- Less than or equal
{ cash = { ['$ne'] = 0 } }                 -- Not equal

-- Logical
{ ['$or'] = {                               -- OR
    { cash = { ['$gt'] = 10000 } },
    { bank = { ['$gt'] = 50000 } }
}}

{ ['$and'] = {...} }                        -- AND
{ ['$not'] = {...} }                        -- NOT

-- Array
{ licenses = { ['$in'] = { 'driver', 'weapon' } } }  -- In array
{ licenses = 'weapon' }                     -- Has element

-- Update operators
{ ['$set'] = { cash = 5000 } }             -- Set field
{ ['$inc'] = { cash = 100 } }              -- Increment
{ ['$push'] = { licenses = 'fishing' } }   -- Add to array
{ ['$pull'] = { licenses = 'weapon' } }    -- Remove from array

MySQL Usage

Accessing MySQL

MySQL operations use oxmysql resource:

-- Execute query
exports.oxmysql:execute('UPDATE users SET money = ? WHERE id = ?', {
    newMoney,
    userId
})

-- Fetch single row
local user = exports.oxmysql:single('SELECT * FROM users WHERE id = ?', { userId })

-- Fetch multiple rows
local users = exports.oxmysql:query('SELECT * FROM users WHERE banned = ?', { false })

-- Insert and get ID
local insertId = exports.oxmysql:insert('INSERT INTO logs (action, player) VALUES (?, ?)', {
    'login',
    playerId
})

-- Prepared statements (safer)
local result = exports.oxmysql:prepare('SELECT * FROM vehicles WHERE owner = ?', { characterId })

Common MySQL Queries

SELECT Queries

-- Select all
local vehicles = exports.oxmysql:query('SELECT * FROM vehicles WHERE owner = ?', {
    characterId
})

-- Select specific columns
local names = exports.oxmysql:query('SELECT first_name, last_name FROM characters WHERE id = ?', {
    charId
})

-- With JOIN
local data = exports.oxmysql:query([[
    SELECT c.*, v.plate, v.model
    FROM characters c
    LEFT JOIN vehicles v ON v.owner = c.id
    WHERE c.id = ?
]], { charId })

INSERT Queries

-- Simple insert
exports.oxmysql:insert('INSERT INTO logs (action, player, timestamp) VALUES (?, ?, ?)', {
    'purchase',
    playerId,
    os.time()
})

-- Insert and get ID
local vehicleId = exports.oxmysql:insert([[
    INSERT INTO vehicles (owner, model, plate)
    VALUES (?, ?, ?)
]], {
    characterId,
    'adder',
    'ABC123'
})

UPDATE Queries

-- Update single field
exports.oxmysql:execute('UPDATE characters SET cash = ? WHERE id = ?', {
    newCash,
    charId
})

-- Update multiple fields
exports.oxmysql:execute([[
    UPDATE characters
    SET cash = ?, bank = ?, last_login = ?
    WHERE id = ?
]], {
    cash,
    bank,
    os.time(),
    charId
})

-- Increment field
exports.oxmysql:execute('UPDATE characters SET cash = cash + ? WHERE id = ?', {
    amount,
    charId
})

DELETE Queries

-- Delete record
exports.oxmysql:execute('DELETE FROM vehicles WHERE id = ?', { vehicleId })

-- Delete with condition
exports.oxmysql:execute('DELETE FROM logs WHERE timestamp < ?', {
    os.time() - (30 * 24 * 60 * 60)  -- Older than 30 days
})

Data Models

Character Document (MongoDB)

{
    _id: ObjectId("..."),
    SID: 1,                              // Character ID (State ID)
    User: 12345,                         // Account ID of player who owns character
    First: "John",
    Last: "Doe",
    Phone: 5551234,                      // Phone number (numeric)
    Gender: 0,                           // 0 = Male, 1 = Female
    DOB: "1990-01-01",
    Bio: "Character biography",
    Origin: 1,
    Cash: 5000,                          // Note: Capital C
    Bank: 25000,                         // Note: Capital B

    // Jobs array (can have multiple jobs)
    Jobs: [
        {
            Id: "police",
            Name: "Los Santos Police Department",
            Grade: {
                Id: "officer",
                Name: "Officer",
                Level: 1
            },
            Workplace: {
                Id: "lspd",
                Name: "LSPD Mission Row"
            }
        }
    ],

    // Licenses
    Licenses: {
        Drivers: {
            Active: true,
            Points: 0,
            Suspended: false
        },
        Weapons: {
            Active: false,
            Suspended: false
        },
        Hunting: {
            Active: false,
            Suspended: false
        },
        Fishing: {
            Active: false,
            Suspended: false
        },
        Pilot: {
            Active: false,
            Suspended: false
        }
    },

    metadata: {
        hunger: 80,
        thirst: 60,
        stress: 20
    },
    appearance: {
        model: "mp_m_freemode_01",
        customization: {...}
    },
    position: {
        x: 215.3,
        y: -810.1,
        z: 30.7,
        heading: 180.0
    },
    LastPlayed: 1234567890
}

Inventory Document (MongoDB)

{
    _id: ObjectId("..."),
    owner: 1,                            // Character SID
    type: "player",                      // player, vehicle, property, etc.
    maxSlots: 50,
    maxWeight: 100.0,
    items: [
        {
            slot: 1,
            name: "water_bottle",
            label: "Water Bottle",
            count: 5,
            weight: 0.5,
            quality: 100,
            metadata: {
                durability: 100
            }
        },
        {
            slot: 2,
            name: "weapon_pistol",
            label: "Pistol",
            count: 1,
            weight: 2.0,
            quality: 95,
            metadata: {
                serial: "ABC123",
                ammo: 12,
                attachments: ["suppressor", "flashlight"]
            }
        }
    ]
}

Vehicle Record (MySQL)

CREATE TABLE vehicles (
    id INT PRIMARY KEY AUTO_INCREMENT,
    owner INT NOT NULL,                  -- Character SID
    model VARCHAR(50) NOT NULL,
    plate VARCHAR(8) UNIQUE NOT NULL,
    garage VARCHAR(50) DEFAULT 'pillbox',
    state INT DEFAULT 0,                 -- 0=out, 1=garaged
    fuel INT DEFAULT 100,
    engine FLOAT DEFAULT 1000.0,
    body FLOAT DEFAULT 1000.0,
    mods TEXT,                           -- JSON customization
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_owner (owner),
    INDEX idx_plate (plate)
);

Best Practices

1. Use MongoDB for Game Data

Store dynamic, frequently-changing data in MongoDB:

// ✅ GOOD - MongoDB
- Character stats
- Inventory items
- Player positions
- Logs and events

// ❌ BAD - MySQL for this
- Constantly updating data requires many UPDATE queries

2. Use MySQL for Relational Data

Use MySQL when you need complex relationships:

// ✅ GOOD - MySQL
- Vehicle ownership with joins to characters
- Transaction history requiring aggregation
- Property relationships

// Use JOIN queries
SELECT v.*, c.first_name, c.last_name
FROM vehicles v
JOIN characters c ON v.owner = c.id
WHERE v.plate = ?

3. Index Your Collections

Add indexes for frequently queried fields:

// MongoDB - Create indexes
db.characters.createIndex({ Owner: 1 })
db.characters.createIndex({ SID: 1 }, { unique: true })
db.inventory.createIndex({ owner: 1 })
db.vehicles.createIndex({ owner: 1, stored: 1 })

-- MySQL - Create indexes
CREATE INDEX idx_owner ON vehicles(owner);
CREATE INDEX idx_character ON inventory(character_id);

4. Sanitize User Input

Always use parameterized queries:

-- ✅ GOOD - Parameterized (MongoDB)
Database.Game:findOne({
    collection = 'characters',
    query = { SID = userInput }
}, callback)

-- ✅ GOOD - Parameterized (MySQL)
exports.oxmysql:single('SELECT * FROM users WHERE id = ?', { userInput })

-- ❌ BAD - SQL injection risk
exports.oxmysql:single('SELECT * FROM users WHERE id = ' .. userInput)

5. Handle Errors

Database operations can fail. Always handle errors in callbacks:

Database.Game:findOne({
    collection = 'characters',
    query = { SID = charId }
}, function(success, character)
    if not success then
        COMPONENTS.Logger:Error('Database', 'Failed to fetch character', {
            charId = charId
        })
        return
    end

    if not character then
        print('Character not found')
        return
    end

    -- Process character
    print('Found:', character.First, character.Last)
end)

6. Batch Operations

When possible, batch database operations:

-- ❌ BAD - Multiple queries in loop
for i = 1, 100 do
    Database.Game:insertOne({
        collection = 'logs',
        document = { action = 'test_' .. i }
    }, function() end)
end

-- ✅ GOOD - Single batch insert
local documents = {}
for i = 1, 100 do
    table.insert(documents, { action = 'test_' .. i })
end

Database.Game:insertMany({
    collection = 'logs',
    documents = documents
}, function(success, result)
    print('Inserted', #documents, 'documents')
end)

7. Cache When Appropriate

Cache frequently accessed, rarely changing data:

-- Cache example
local itemCache = {}

function GetItem(itemId, callback)
    if itemCache[itemId] then
        callback(itemCache[itemId])
        return
    end

    Database.Game:findOne({
        collection = 'items',
        query = { id = itemId }
    }, function(success, item)
        if success and item then
            itemCache[itemId] = item
            callback(item)
        else
            callback(nil)
        end
    end)
end

Performance Optimization

MongoDB Optimization

-- 1. Use projection to limit returned fields
Database.Game:findOne({
    collection = 'characters',
    query = { SID = 1 },
    options = {
        projection = { First = 1, Last = 1, Cash = 1 }
    }
}, callback)

-- 2. Limit results
Database.Game:find({
    collection = 'logs',
    query = { player = playerId },
    options = {
        limit = 100,
        sort = { timestamp = -1 }
    }
}, callback)

-- 3. Use indexes (create once in MongoDB shell)
-- db.characters.createIndex({ SID: 1 }, { unique: true })
-- db.inventory.createIndex({ owner: 1 })

-- 4. Use aggregation for complex queries
Database.Game:aggregate({
    collection = 'vehicles',
    aggregate = {
        {
            ['$group'] = {
                _id = '$model',
                count = { ['$sum'] = 1 }
            }
        },
        {
            ['$sort'] = { count = -1 }
        },
        {
            ['$limit'] = 10
        }
    }
}, function(success, results)
    if success then
        for _, result in ipairs(results) do
            print(result._id, result.count)
        end
    end
end)

MySQL Optimization

-- 1. Use prepared statements
exports.oxmysql:prepare('SELECT * FROM vehicles WHERE owner = ?', { charId })

-- 2. Add indexes
CREATE INDEX idx_owner ON vehicles(owner);
CREATE INDEX idx_compound ON vehicles(owner, state);

-- 3. Optimize queries
-- Use EXPLAIN to check query performance
EXPLAIN SELECT * FROM vehicles WHERE owner = 1;

-- 4. Limit results
SELECT * FROM logs WHERE player = ? ORDER BY timestamp DESC LIMIT 100;

Database Migration

When updating schemas:

-- MongoDB migration example
function MigrateCharacterSchema()
    Database.Game:find({
        collection = 'characters',
        query = {}
    }, function(success, chars)
        if not success or not chars then
            print('Migration failed')
            return
        end

        local count = 0
        for _, char in ipairs(chars) do
            if not char.metadata then
                Database.Game:updateOne({
                    collection = 'characters',
                    query = { _id = char._id },
                    update = {
                        ['$set'] = {
                            metadata = {
                                hunger = 100,
                                thirst = 100,
                                stress = 0
                            }
                        }
                    }
                }, function(success)
                    if success then
                        count = count + 1
                    end
                end)
            end
        end

        print('Migration complete:', count, 'characters updated')
    end)
end

Next Steps

Database Setup

Initial database configuration

Data Models

Complete data model reference

MongoDB Guide

MongoDB development guide

MySQL Guide

MySQL development guide

When in doubt, use MongoDB for game data. It’s more flexible and handles nested/changing data better. Use MySQL when you specifically need relational queries or compatibility with other resources.

Getting Started

Installation

Core Concepts

Configuration

Why Two Databases?

MongoDB (Primary)

MySQL (Secondary)

Database Usage

MongoDB Databases

MySQL Database

MongoDB Usage

Accessing MongoDB

MongoDB Methods

MongoDB Query Operators

MySQL Usage

Accessing MySQL

Common MySQL Queries

Data Models

Character Document (MongoDB)

Inventory Document (MongoDB)

Vehicle Record (MySQL)

Best Practices

Performance Optimization

MongoDB Optimization

MySQL Optimization

Database Migration

Next Steps

Database Setup

Data Models

MongoDB Guide

MySQL Guide

Getting Started

Installation

Core Concepts

Configuration

​Why Two Databases?

MongoDB (Primary)

MySQL (Secondary)

​Database Usage

​MongoDB Databases

​MySQL Database

​MongoDB Usage

​Accessing MongoDB

​MongoDB Methods

​MongoDB Query Operators

​MySQL Usage

​Accessing MySQL

​Common MySQL Queries

​Data Models

​Character Document (MongoDB)

​Inventory Document (MongoDB)

​Vehicle Record (MySQL)

​Best Practices

​Performance Optimization

​MongoDB Optimization

​MySQL Optimization

​Database Migration

​Next Steps

Database Setup

Data Models

MongoDB Guide

MySQL Guide

Why Two Databases?

Database Usage

MongoDB Databases

MySQL Database

MongoDB Usage

Accessing MongoDB

MongoDB Methods

MongoDB Query Operators

MySQL Usage

Accessing MySQL

Common MySQL Queries

Data Models

Character Document (MongoDB)

Inventory Document (MongoDB)

Vehicle Record (MySQL)

Best Practices

Performance Optimization

MongoDB Optimization

MySQL Optimization

Database Migration

Next Steps