Databases

Game Databases

Implement efficient data persistence for players, matches, and leaderboards.

Database Selection

PostgreSQL Schema

-- Player profiles
CREATE TABLE players (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    username VARCHAR(32) UNIQUE NOT NULL,
    email VARCHAR(255) UNIQUE NOT NULL,
    mmr INTEGER DEFAULT 1000,
    level INTEGER DEFAULT 1,
    xp INTEGER DEFAULT 0,
    coins BIGINT DEFAULT 0,
    created_at TIMESTAMP DEFAULT NOW(),
    last_login TIMESTAMP DEFAULT NOW()
);

-- Indexes for common queries
CREATE INDEX idx_players_mmr ON players(mmr DESC);
CREATE INDEX idx_players_level ON players(level DESC);

-- Inventory system
CREATE TABLE inventory (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    player_id UUID REFERENCES players(id) ON DELETE CASCADE,
    item_type VARCHAR(50) NOT NULL,
    item_id VARCHAR(100) NOT NULL,
    quantity INTEGER DEFAULT 1,
    metadata JSONB DEFAULT '{}',
    acquired_at TIMESTAMP DEFAULT NOW(),
    UNIQUE(player_id, item_type, item_id)
);

-- Match history
CREATE TABLE matches (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    game_mode VARCHAR(32) NOT NULL,
    started_at TIMESTAMP NOT NULL,
    ended_at TIMESTAMP,
    winner_team INTEGER,
    metadata JSONB DEFAULT '{}'
);

CREATE TABLE match_players (
    match_id UUID REFERENCES matches(id),
    player_id UUID REFERENCES players(id),
    team INTEGER NOT NULL,
    score INTEGER DEFAULT 0,
    kills INTEGER DEFAULT 0,
    deaths INTEGER DEFAULT 0,
    mmr_change INTEGER DEFAULT 0,
    PRIMARY KEY (match_id, player_id)
);

Redis Leaderboard

const Redis = require('ioredis');
const redis = new Redis({ host: 'localhost', port: 6379 });

class Leaderboard {
    constructor(name) {
        this.key = `leaderboard:${name}`;
    }

    async setScore(playerId, score) {
        return redis.zadd(this.key, score, playerId);
    }

    async getScore(playerId) {
        return redis.zscore(this.key, playerId);
    }

    async getRank(playerId) {
        // 0-indexed, null if not found
        const rank = await redis.zrevrank(this.key, playerId);
        return rank !== null ? rank + 1 : null;
    }

    async getTop(count = 100) {
        const results = await redis.zrevrange(
            this.key, 0, count - 1, 'WITHSCORES'
        );

        // Convert to [{playerId, score, rank}]
        const leaderboard = [];
        for (let i = 0; i < results.length; i += 2) {
            leaderboard.push({
                playerId: results[i],
                score: parseInt(results[i + 1]),
                rank: i / 2 + 1
            });
        }
        return leaderboard;
    }

    async getAroundPlayer(playerId, range = 5) {
        const rank = await this.getRank(playerId);
        if (!rank) return [];

        const start = Math.max(0, rank - range - 1);
        const end = rank + range - 1;

        return redis.zrevrange(this.key, start, end, 'WITHSCORES');
    }
}

Connection Pooling

const { Pool } = require('pg');

const pool = new Pool({
    host: process.env.DB_HOST,
    database: 'game',
    max: 20,                    // Max connections
    idleTimeoutMillis: 30000,   // Close idle connections
    connectionTimeoutMillis: 2000
});

// Transaction wrapper
async function withTransaction(callback) {
    const client = await pool.connect();
    try {
        await client.query('BEGIN');
        const result = await callback(client);
        await client.query('COMMIT');
        return result;
    } catch (e) {
        await client.query('ROLLBACK');
        throw e;
    } finally {
        client.release();
    }
}

// Usage: Atomic currency transfer
await withTransaction(async (client) => {
    await client.query(
        'UPDATE players SET coins = coins - $1 WHERE id = $2',
        [amount, senderId]
    );
    await client.query(
        'UPDATE players SET coins = coins + $1 WHERE id = $2',
        [amount, receiverId]
    );
});

Caching Strategy

class CachedPlayerService {
    constructor(db, redis) {
        this.db = db;
        this.redis = redis;
        this.ttl = 3600; // 1 hour
    }

    async getPlayer(playerId) {
        const cacheKey = `player:${playerId}`;

        // Try cache first
        const cached = await this.redis.get(cacheKey);
        if (cached) {
            return JSON.parse(cached);
        }

        // Cache miss - load from DB
        const player = await this.db.query(
            'SELECT * FROM players WHERE id = $1',
            [playerId]
        );

        if (player.rows[0]) {
            // Cache for future requests
            await this.redis.setex(
                cacheKey,
                this.ttl,
                JSON.stringify(player.rows[0])
            );
        }

        return player.rows[0];
    }

    async updatePlayer(playerId, updates) {
        // Update DB
        await this.db.query(
            'UPDATE players SET mmr = $1 WHERE id = $2',
            [updates.mmr, playerId]
        );

        // Invalidate cache
        await this.redis.del(`player:${playerId}`);
    }
}

Troubleshooting

Common Failure Modes

Debug Checklist

-- Check active connections
SELECT count(*) FROM pg_stat_activity WHERE datname = 'game';

-- Find slow queries
SELECT query, mean_time, calls
FROM pg_stat_statements
ORDER BY mean_time DESC LIMIT 10;

-- Check index usage
SELECT relname, seq_scan, idx_scan
FROM pg_stat_user_tables
WHERE seq_scan > idx_scan;

-- Table sizes
SELECT relname, pg_size_pretty(pg_total_relation_size(relid))
FROM pg_stat_user_tables ORDER BY pg_total_relation_size(relid) DESC;

# Redis diagnostics
redis-cli INFO stats
redis-cli SLOWLOG GET 10
redis-cli MEMORY DOCTOR

Unit Test Template

const { Pool } = require('pg');

describe('Database Operations', () => {
    let pool;

    beforeAll(async () => {
        pool = new Pool({ database: 'game_test' });
    });

    afterAll(async () => {
        await pool.end();
    });

    test('creates player with default values', async () => {
        const result = await pool.query(
            `INSERT INTO players (username, email)
             VALUES ($1, $2) RETURNING *`,
            ['testuser', 'test@example.com']
        );

        expect(result.rows[0].mmr).toBe(1000);
        expect(result.rows[0].level).toBe(1);
    });

    test('leaderboard returns correct rank', async () => {
        const lb = new Leaderboard('test');

        await lb.setScore('player1', 1000);
        await lb.setScore('player2', 2000);
        await lb.setScore('player3', 1500);

        expect(await lb.getRank('player2')).toBe(1);
        expect(await lb.getRank('player3')).toBe(2);
        expect(await lb.getRank('player1')).toBe(3);
    });

    test('transaction rollback on error', async () => {
        const initialCoins = 1000;

        try {
            await withTransaction(async (client) => {
                await client.query(
                    'UPDATE players SET coins = coins - 500 WHERE id = $1',
                    [playerId]
                );
                throw new Error('Simulated failure');
            });
        } catch (e) {
            // Expected
        }

        // Verify rollback
        const result = await pool.query(
            'SELECT coins FROM players WHERE id = $1',
            [playerId]
        );
        expect(result.rows[0].coins).toBe(initialCoins);
    });
});

Resources

• assets/ - Schema templates
• references/ - Database optimization guides