Database API

NexAuth provides a database abstraction layer supporting multiple database backends with a unified API.

Supported Databases

MySQL/MariaDB

Production-ready with excellent performance.

PostgreSQL

Advanced features and great scalability.

H2

Testing and development only.

Database Interface

Basic Operations

DatabaseManager dbManager = NexAuthAPI.getInstance().getDatabaseManager();

// Execute query
dbManager.executeQuery("SELECT * FROM players WHERE username = ?", (resultSet) -> {
    while (resultSet.next()) {
        String username = resultSet.getString("username");
        // Process results
    }
}, username);

// Execute update
int affected = dbManager.executeUpdate(
    "UPDATE players SET last_login = ? WHERE username = ?",
    System.currentTimeMillis(), username
);

// Execute batch
dbManager.executeBatch((batch) -> {
    batch.addStatement("INSERT INTO players (...) VALUES (...)");
    batch.addStatement("UPDATE players SET ...");
});

Player Account Operations

// Load account
Optional<PlayerAccount> account = dbManager.loadAccount(username);

if (account.isPresent()) {
    // Access account data
    UUID uuid = account.get().getUUID();
    long registeredAt = account.get().getRegisteredAt();
}

// Save account
dbManager.saveAccount(account);

// Delete account
dbManager.deleteAccount(username);

// Check account exists
boolean exists = dbManager.accountExists(username);

Session Operations

// Create session
Session session = new Session(player, Duration.ofHours(24));
dbManager.saveSession(session);

// Load session
Optional<Session> session = dbManager.loadSession(sessionId);

// Update session
session.setLastActive(System.currentTimeMillis());
dbManager.updateSession(session);

// Delete session
dbManager.deleteSession(sessionId);

// Clean expired sessions
int cleaned = dbManager.cleanExpiredSessions();

Custom Tables

Creating Custom Tables

// Create custom table
dbManager.executeUpdate("""
    CREATE TABLE IF NOT EXISTS custom_player_data (
        id INT AUTO_INCREMENT PRIMARY KEY,
        username VARCHAR(16) NOT NULL,
        data_key VARCHAR(64) NOT NULL,
        data_value TEXT,
        created_at BIGINT NOT NULL,
        INDEX idx_username (username),
        INDEX idx_data_key (data_key)
    )
""");

Custom Queries

// Insert custom data
dbManager.executeUpdate(
    "INSERT INTO custom_player_data (username, data_key, data_value, created_at) VALUES (?, ?, ?, ?)",
    username, "custom_key", "custom_value", System.currentTimeMillis()
);

// Query custom data
dbManager.executeQuery(
    "SELECT * FROM custom_player_data WHERE username = ?",
    (resultSet) -> {
        while (resultSet.next()) {
            String key = resultSet.getString("data_key");
            String value = resultSet.getString("data_value");
            // Process data
        }
    },
    username
);

Transactions

Transaction Management

// Execute transaction
dbManager.executeTransaction(() -> {
    // Multiple related operations
    dbManager.saveAccount(account);
    dbManager.saveSession(session);
    dbManager.executeUpdate("INSERT INTO logs (...) VALUES (...)");
    
    // All operations will be committed or rolled back together
});

Transaction with Result

// Transaction with return value
boolean success = dbManager.executeTransactionWithResult(() -> {
    dbManager.saveAccount(account);
    dbManager.saveSession(session);
    return true; // Return result
});

Async Operations

Async Database Calls

// Async query
CompletableFuture<List<PlayerAccount>> future = dbManager
    .executeQueryAsync(
        "SELECT * FROM players WHERE last_login > ?",
        (resultSet) -> {
            List<PlayerAccount> accounts = new ArrayList<>();
            while (resultSet.next()) {
                accounts.add(mapToAccount(resultSet));
            }
            return accounts;
        },
        timestamp
    );

// Handle result
future.thenAccept(accounts -> {
    // Process accounts
}).exceptionally(throwable -> {
    // Handle error
    logger.error("Database error", throwable);
    return null;
});

Batch Async Operations

// Async batch operation
List<CompletableFuture<Void>> futures = new ArrayList<>();

for (PlayerAccount account : accounts) {
    CompletableFuture<Void> future = dbManager
        .saveAccountAsync(account)
        .thenAccept(saved -> {
            // Account saved
        });
    
    futures.add(future);
}

// Wait for all to complete
CompletableFuture.allOf(futures.toArray(new CompletableFuture[0]))
    .thenAccept(v -> {
        logger.info("All accounts saved");
    });

Connection Pooling

Pool Configuration

database {
    type=nexauth-mysql
    properties {
        mysql {
            host="localhost"
            port=3306
            database="nexauth"
            user="nexauth"
            password="yourpassword"
            max-life-time=600000
        }
    }
    transient-retries=3
    transient-retry-delay-ms=250
}

Programmatic Configuration

HikariConfig config = new HikariConfig();
config.setJdbcUrl("jdbc:mysql://localhost:3306/nexauth");
config.setUsername("nexauth");
config.setPassword("password");
config.setMaximumPoolSize(20);
config.setMinimumIdle(5);
config.setConnectionTimeout(30000);
config.setIdleTimeout(600000);
config.setMaxLifetime(1800000);

HikariDataSource dataSource = new HikariDataSource(config);
DatabaseManager dbManager = new DatabaseManager(dataSource);

Database Migration

Schema Migration

// Create migration
public class AddLastLoginColumn implements Migration {
    @Override
    public String getName() {
        return "add_last_login_column";
    }
    
    @Override
    public void up(DatabaseManager db) {
        db.executeUpdate("ALTER TABLE players ADD COLUMN last_login BIGINT");
    }
    
    @Override
    public void down(DatabaseManager db) {
        db.executeUpdate("ALTER TABLE players DROP COLUMN last_login");
    }
}

// Register migration
dbManager.registerMigration(new AddLastLoginColumn());

// Run migrations
dbManager.runMigrations();

Migration System

// Check migration status
MigrationStatus status = dbManager.getMigrationStatus();

// Run pending migrations
if (status.hasPendingMigrations()) {
    dbManager.runMigrations();
}

// Rollback migration
dbManager.rollbackMigration("add_last_login_column");

Performance Optimization

Query Optimization

// Use prepared statements
dbManager.executeQuery(
    "SELECT * FROM players WHERE username = ? AND uuid = ?",
    resultSet -> { /* process */ },
    username, uuid
);

// Use indexes in schema
dbManager.executeUpdate(
    "CREATE INDEX idx_username ON players(username)"
);

// Limit result sets
dbManager.executeQuery(
    "SELECT * FROM players LIMIT 100",
    resultSet -> { /* process */ }
);

Caching Strategy

// Cache frequently accessed data
LoadingCache<String, PlayerAccount> cache = Caffeine.newBuilder()
    .maximumSize(1000)
    .expireAfterWrite(5, TimeUnit.MINUTES)
    .build(key -> {
        return dbManager.loadAccount(key).orElse(null);
    });

// Use cache
PlayerAccount account = cache.get(username);

Error Handling

Database Exceptions

try {
    dbManager.executeUpdate("INSERT INTO players (...) VALUES (...)");
} catch (DatabaseException e) {
    if (e.isDuplicateKey()) {
        // Handle duplicate key
    } else if (e.isConnectionError()) {
        // Handle connection error
    } else {
        // Handle other errors
        logger.error("Database error", e);
    }
}

Retry Logic

// Retry with exponential backoff
int maxRetries = 3;
int retryCount = 0;
boolean success = false;

while (retryCount < maxRetries && !success) {
    try {
        dbManager.executeUpdate("INSERT INTO players (...) VALUES (...)");
        success = true;
    } catch (DatabaseException e) {
        retryCount++;
        if (retryCount < maxRetries) {
            Thread.sleep(1000L * retryCount); // Exponential backoff
        } else {
            throw e;
        }
    }
}

Best Practices

Next Steps

Crypto API

Cryptography utilities for secure data handling.

Documentation Index

​Database API

​Supported Databases

MySQL/MariaDB

PostgreSQL

H2

​Database Interface

​Basic Operations

​Player Account Operations

​Session Operations

​Custom Tables

​Creating Custom Tables

​Custom Queries

​Transactions

​Transaction Management

​Transaction with Result

​Async Operations

​Async Database Calls

​Batch Async Operations

​Connection Pooling

​Pool Configuration

​Programmatic Configuration

​Database Migration

​Schema Migration

​Migration System

​Performance Optimization

​Query Optimization

​Caching Strategy

​Error Handling

​Database Exceptions

​Retry Logic

​Best Practices

​Next Steps