> ## Documentation Index
> Fetch the complete documentation index at: https://mythicframework.mintlify.site/llms.txt
> Use this file to discover all available pages before exploring further.

# Database Architecture

> Understanding Mythic Framework's dual-database system

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?

<CardGroup cols={2}>
  <Card title="MongoDB (Primary)" icon="leaf">
    **Best for:**

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

  <Card title="MySQL (Secondary)" icon="table">
    **Best for:**

    * Structured data
    * Complex queries
    * Relationships/joins
    * Compatibility with other resources
    * SQL familiarity
  </Card>
</CardGroup>

## Database Usage

### MongoDB Databases

Mythic Framework uses two MongoDB databases:

```javascript theme={null}
// 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

```sql theme={null}
-- 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`

<Note>
  **Database Architecture:** Mythic uses two separate MongoDB databases accessed via the `Database` component:

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

  After `RetrieveComponents()`, use `Database.Game:method()` directly. The full path is `Database.Game` but the local shorthand is standard.
</Note>

```lua theme={null}
-- 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

<AccordionGroup>
  <Accordion title="findOne(params, callback)" icon="magnifying-glass">
    Find a single document matching query.

    ```lua theme={null}
    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)`
  </Accordion>

  <Accordion title="find(params, callback)" icon="magnifying-glass-plus">
    Find multiple documents.

    ```lua theme={null}
    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)`
  </Accordion>

  <Accordion title="insertOne(params, callback)" icon="plus">
    Insert a single document.

    ```lua theme={null}
    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)`
  </Accordion>

  <Accordion title="updateOne(params, callback)" icon="pen">
    Update a single document.

    ```lua theme={null}
    -- 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)`
  </Accordion>

  <Accordion title="deleteOne(params, callback)" icon="trash">
    Delete a single document.

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

    **Callback receives:** `(success, deleteResult)`
  </Accordion>

  <Accordion title="count(params, callback)" icon="hashtag">
    Count documents matching query.

    ```lua theme={null}
    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)`
  </Accordion>

  <Accordion title="aggregate(params, callback)" icon="filter">
    Advanced aggregation queries.

    ```lua theme={null}
    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)`
  </Accordion>
</AccordionGroup>

### MongoDB Query Operators

```lua theme={null}
-- 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:

```lua theme={null}
-- 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

<AccordionGroup>
  <Accordion title="SELECT Queries" icon="magnifying-glass">
    ```lua theme={null}
    -- 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 })
    ```
  </Accordion>

  <Accordion title="INSERT Queries" icon="plus">
    ```lua theme={null}
    -- 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'
    })
    ```
  </Accordion>

  <Accordion title="UPDATE Queries" icon="pen">
    ```lua theme={null}
    -- 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
    })
    ```
  </Accordion>

  <Accordion title="DELETE Queries" icon="trash">
    ```lua theme={null}
    -- 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
    })
    ```
  </Accordion>
</AccordionGroup>

## Data Models

### Character Document (MongoDB)

```javascript theme={null}
{
    _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)

```javascript theme={null}
{
    _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)

```sql theme={null}
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

<AccordionGroup>
  <Accordion title="1. Use MongoDB for Game Data" icon="leaf">
    Store dynamic, frequently-changing data in MongoDB:

    ```lua theme={null}
    -- Good fit for MongoDB
    -- Character stats
    -- Inventory items
    -- Player positions
    -- Logs and events

    -- Bad fit for MySQL
    -- Constantly updating data requires many UPDATE queries
    ```
  </Accordion>

  <Accordion title="2. Use MySQL for Relational Data" icon="link">
    Use MySQL when you need complex relationships:

    ```lua theme={null}
    -- Good fit for 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 = ?
    ```
  </Accordion>

  <Accordion title="3. Index Your Collections" icon="magnifying-glass-chart">
    Add indexes for frequently queried fields:

    ```javascript theme={null}
    // 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 })
    ```

    ```sql theme={null}
    -- MySQL - Create indexes
    CREATE INDEX idx_owner ON vehicles(owner);
    CREATE INDEX idx_character ON inventory(character_id);
    ```
  </Accordion>

  <Accordion title="4. Sanitize User Input" icon="shield">
    Always use parameterized queries:

    ```lua theme={null}
    -- ✅ 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)
    ```
  </Accordion>

  <Accordion title="5. Handle Errors" icon="triangle-exclamation">
    Database operations can fail. Always handle errors in callbacks:

    ```lua theme={null}
    Database.Game:findOne({
        collection = 'characters',
        query = { SID = charId }
    }, function(success, character)
        if not success then
            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)
    ```
  </Accordion>

  <Accordion title="6. Batch Operations" icon="layer-group">
    When possible, batch database operations:

    ```lua theme={null}
    -- ❌ 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)
    ```
  </Accordion>

  <Accordion title="7. Cache When Appropriate" icon="database">
    Cache frequently accessed, rarely changing data:

    ```lua theme={null}
    -- 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
    ```
  </Accordion>
</AccordionGroup>

## Performance Optimization

### MongoDB Optimization

```lua theme={null}
-- 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

```sql theme={null}
-- 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:

```lua theme={null}
-- 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

<CardGroup cols={2}>
  <Card title="Database Setup" icon="database" href="/installation/database-setup">
    Initial database configuration
  </Card>

  <Card title="Database API" icon="code" href="/api/core/database">
    Database API reference
  </Card>

  <Card title="Architecture" icon="diagram-project" href="/concepts/architecture">
    Framework architecture overview
  </Card>

  <Card title="Configuration" icon="gear" href="/configuration/database-connections">
    Database connection settings
  </Card>
</CardGroup>

<Tip>
  **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.
</Tip>