The "Sql" API

The Sql object provides a powerful interface for interacting with SQL databases from your scripts. It supports SQLite out of the box and can be configured to work with MySQL, PostgreSQL, or any JDBC-compatible database.

Database operations are automatically handled with connection pooling, prepared statements, and transaction support, making it safe and efficient to store persistent data.

Configuration

Before using the Sql API, you need to configure your database connection in the PixelScript configuration file.
See the Configuration Documentation for details on setting up SQLite, MySQL, PostgreSQL, or custom JDBC drivers.

Checking Database Availability

Check if database is connected with Sql.isAvailable()

Always check if the database connection is active before executing queries.

if (Sql.isAvailable()) {
    log("Database connection is active");
} else {
    throw new Error("Database connection is not available");
}

Check connection status with Sql.isConnected()

Alternative method to verify database connectivity.

if (! Sql.isConnected()) {
    console.warn("Database is not connected - skipping data save");
    return;
}

Creating and Managing Tables

Create a table if it doesn't exist with Sql.createTableIfNotExists(tableName, createTableSql, resultHandler)

Safely creates a table only if it doesn't already exist in the database.

Parameters:

• tableName - The name of the table to check/create
• createTableSql - The full SQL CREATE TABLE statement
• resultHandler - Callback function that receives true if the table was created, false if it already existed

Sql.createTableIfNotExists(
    "player_data",
    `CREATE TABLE player_data (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        username VARCHAR(32) NOT NULL UNIQUE,
        coins INT DEFAULT 0,
        last_login TIMESTAMP DEFAULT CURRENT_TIMESTAMP
    )`,
    (created) => {
        if (created) {
            log("Created player_data table");
        } else {
            log("Table already exists");
        }
    }
);

Add a column if it doesn't exist with Sql.addColumnIfNotExists(tableName, columnName, columnDefinition, resultHandler)

Safely adds a new column to an existing table without failing if the column already exists.

Parameters:

• tableName - The name of the table to modify
• columnName - The name of the column to add
• columnDefinition - The column type and constraints (e.g., VARCHAR(255) DEFAULT '')
• resultHandler - Callback that receives true if the column was added, false if it already existed

Sql.addColumnIfNotExists(
    "player_data",
    "level",
    "INT DEFAULT 1",
    (added) => {
        if (added) {
            log("Added 'level' column to player_data table");
        } else {
            log("Column 'level' already exists");
        }
    }
);

Executing Queries

Query data with Sql.makeQuery(query, preparer, resultHandler)

Executes a SELECT query and processes the results.

Parameters:

• query - The SQL query string (use ? for parameters)
• preparer - Function to set prepared statement parameters
• resultHandler - Function to process the ResultSet

Sql.makeQuery(
    "SELECT username, coins FROM player_data WHERE username = ?",
    (statement) => {
        statement.setString(1, "Notch");
    },
    (resultSet) => {
        if (resultSet === null) {
            error("Query failed");
            return;
        }
        
        if (resultSet. next()) {
            const username = resultSet.getString("username");
            const coins = resultSet. getInt("coins");
            log(`Player ${username} has ${coins} coins`);
        } else {
            log("Player not found");
        }
    }
);

Update data with Sql.makeUpdate(query, preparer, resultHandler)

Executes an INSERT, UPDATE, or DELETE query.

Parameters:

• query - The SQL query string (use ? for parameters)
• preparer - Function to set prepared statement parameters
• resultHandler - Function that receives the number of affected rows (or -1 on error)

Sql.makeUpdate(
    "UPDATE player_data SET coins = coins + ?  WHERE username = ?",
    (statement) => {
        statement. setInt(1, 100);
        statement.setString(2, "Notch");
    },
    (rowsAffected) => {
        if (rowsAffected > 0) {
            log(`Updated ${rowsAffected} rows`);
        } else if (rowsAffected === -1) {
            error("Update failed");
        } else {
            log("No rows were updated");
        }
    }
);

Using Transactions

Execute multiple queries atomically with Sql.transaction(transactionHandler)

Transactions ensure that a group of operations either all succeed or all fail together, maintaining data integrity.

The transaction is automatically committed if all operations succeed, or rolled back if any operation fails.

Sql.transaction((ctx) => {
    // Deduct coins from one player
    ctx.update(
        "UPDATE player_data SET coins = coins - ?  WHERE username = ?",
        (statement) => {
            statement. setInt(1, 50);
            statement.setString(2, "PlayerA");
        }
    );
    
    // Add coins to another player
    ctx.update(
        "UPDATE player_data SET coins = coins + ?  WHERE username = ?",
        (statement) => {
            statement. setInt(1, 50);
            statement.setString(2, "PlayerB");
        }
    );
    
    // Both updates succeed together, or both fail together
    log("Transaction completed successfully");
});

Transaction context methods

Inside a transaction handler, you have access to a TransactionContext object with the following methods:

ctx.update(query, preparer)

Execute an UPDATE, INSERT, or DELETE query within the transaction.

ctx.execute(query, preparer)

Alias for ctx.update() - executes a query within the transaction.

ctx.query(query, preparer, resultHandler)

Execute a SELECT query within the transaction and process results.

Sql.transaction((ctx) => {
    let currentCoins = 0;
    
    // Query current balance
    ctx.query(
        "SELECT coins FROM player_data WHERE username = ?",
        (statement) => {
            statement.setString(1, "Notch");
        },
        (resultSet) => {
            if (resultSet. next()) {
                currentCoins = resultSet.getInt("coins");
            }
        }
    );
    
    // Only update if player has enough coins
    if (currentCoins >= 100) {
        ctx.update(
            "UPDATE player_data SET coins = coins - 100 WHERE username = ?",
            (statement) => {
                statement.setString(1, "Notch");
            }
        );
        log("Purchase successful");
    } else {
        log("Insufficient funds");
        // Transaction will still commit, but no update was made
    }
});

Practical Examples

Player visit tracking system

const area = registerArea(new ProjectArea(
    [0, 10],
    [0, 10],
    'visit_tracker.js',
    'Visit Tracking Area'
));

// Initialize database
if (! Sql.isAvailable()) {
    throw new Error("Database not available");
}

Sql.createTableIfNotExists(
    "player_visits",
    `CREATE TABLE player_visits (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        username VARCHAR(32) NOT NULL UNIQUE,
        times_visited INT DEFAULT 0,
        last_visit TIMESTAMP DEFAULT CURRENT_TIMESTAMP
    )`,
    (created) => {
        log(created ? "Created visits table" : "Visits table exists");
    }
);

area.onPlayerEnter((player) => {
    Scheduler.runAsync(() => {
        const playerName = player.getName();
        
        // Upsert:  Insert if new, ignore if exists
        Sql.makeUpdate(
            "INSERT OR IGNORE INTO player_visits (username, times_visited) VALUES (?, 0)",
            (stmt) => stmt.setString(1, playerName),
            () => {
                // Increment visit count
                Sql.makeUpdate(
                    "UPDATE player_visits SET times_visited = times_visited + 1, last_visit = CURRENT_TIMESTAMP WHERE username = ?",
                    (stmt) => stmt.setString(1, playerName),
                    () => {
                        // Query updated data
                        Sql.makeQuery(
                            "SELECT times_visited, last_visit FROM player_visits WHERE username = ?",
                            (stmt) => stmt.setString(1, playerName),
                            (rs) => {
                                if (rs !== null && rs.next()) {
                                    const visits = rs.getInt("times_visited");
                                    const lastVisit = rs.getTimestamp("last_visit");
                                    
                                    player.sendMessage(`§eYou have visited ${visits} times`);
                                    if (visits > 1) {
                                        player.sendMessage(`§7Last visit: ${lastVisit}`);
                                    }
                                }
                            }
                        );
                    }
                );
            }
        );
    });
});

Player economy system

// Initialize economy table
Sql.createTableIfNotExists(
    "player_economy",
    `CREATE TABLE player_economy (
        uuid VARCHAR(36) PRIMARY KEY,
        username VARCHAR(32) NOT NULL,
        balance DECIMAL(10,2) DEFAULT 0.00,
        last_updated TIMESTAMP DEFAULT CURRENT_TIMESTAMP
    )`,
    (created) => log(created ? "Economy table created" : "Economy table exists")
);

function getBalance(playerUUID, callback) {
    Sql.makeQuery(
        "SELECT balance FROM player_economy WHERE uuid = ? ",
        (stmt) => stmt.setString(1, playerUUID),
        (rs) => {
            if (rs !== null && rs.next()) {
                callback(rs.getDouble("balance"));
            } else {
                callback(0.0);
            }
        }
    );
}

function addMoney(playerUUID, playerName, amount, callback) {
    Sql.makeUpdate(
        "INSERT INTO player_economy (uuid, username, balance) VALUES (?, ?, ?) ON CONFLICT(uuid) DO UPDATE SET balance = balance + ?, last_updated = CURRENT_TIMESTAMP",
        (stmt) => {
            stmt.setString(1, playerUUID);
            stmt.setString(2, playerName);
            stmt.setDouble(3, amount);
            stmt.setDouble(4, amount);
        },
        (rows) => {
            callback(rows > 0);
        }
    );
}

function transferMoney(fromUUID, toUUID, amount, callback) {
    Sql.transaction((ctx) => {
        // Check sender balance
        let senderBalance = 0;
        ctx.query(
            "SELECT balance FROM player_economy WHERE uuid = ?",
            (stmt) => stmt.setString(1, fromUUID),
            (rs) => {
                if (rs. next()) {
                    senderBalance = rs.getDouble("balance");
                }
            }
        );
        
        if (senderBalance < amount) {
            callback(false, "Insufficient funds");
            return;
        }
        
        // Deduct from sender
        ctx. update(
            "UPDATE player_economy SET balance = balance - ?, last_updated = CURRENT_TIMESTAMP WHERE uuid = ?",
            (stmt) => {
                stmt.setDouble(1, amount);
                stmt.setString(2, fromUUID);
            }
        );
        
        // Add to receiver
        ctx.update(
            "UPDATE player_economy SET balance = balance + ?, last_updated = CURRENT_TIMESTAMP WHERE uuid = ?",
            (stmt) => {
                stmt. setDouble(1, amount);
                stmt.setString(2, toUUID);
            }
        );
        
        callback(true, "Transfer successful");
    });
}

Leaderboard system

function getTopPlayers(limit, callback) {
    Sql.makeQuery(
        "SELECT username, coins FROM player_data ORDER BY coins DESC LIMIT ?",
        (stmt) => stmt.setInt(1, limit),
        (rs) => {
            if (rs === null) {
                callback([]);
                return;
            }
            
            const leaderboard = [];
            while (rs.next()) {
                leaderboard.push({
                    username: rs.getString("username"),
                    coins: rs. getInt("coins")
                });
            }
            callback(leaderboard);
        }
    );
}

// Display leaderboard to player
function showLeaderboard(player) {
    Scheduler.runAsync(() => {
        getTopPlayers(10, (leaderboard) => {
            Scheduler.runSync(() => {
                player.sendMessage("§6§l=== TOP PLAYERS ===");
                leaderboard.forEach((entry, index) => {
                    player.sendMessage(`§e${index + 1}. §f${entry.username} §7- §e${entry.coins} coins`);
                });
            });
        });
    });
}

Batch data migration

function migrateOldDataFormat() {
    Sql.transaction((ctx) => {
        // Add new column
        ctx.execute(
            "ALTER TABLE player_data ADD COLUMN uuid VARCHAR(36)",
            null
        );
        
        // Migrate data from old format
        ctx.query(
            "SELECT id, username FROM player_data WHERE uuid IS NULL",
            null,
            (rs) => {
                while (rs.next()) {
                    const id = rs.getInt("id");
                    const username = rs.getString("username");
                    
                    // Look up UUID (simplified example)
                    const offlinePlayer = Bukkit.getOfflinePlayer(username);
                    const uuid = offlinePlayer.getUniqueId().toString();
                    
                    ctx.update(
                        "UPDATE player_data SET uuid = ?  WHERE id = ?",
                        (stmt) => {
                            stmt.setString(1, uuid);
                            stmt.setInt(2, id);
                        }
                    );
                }
            }
        );
        
        log("Migration completed successfully");
    });
}

Prepared Statement Parameter Types

When setting parameters in the preparer function, use the appropriate method for your data type:

statement.setString(index, "text");      // String/VARCHAR
statement.setInt(index, 42);             // Integer
statement.setLong(index, 1234567890);    // Long
statement. setDouble(index, 3.14);        // Double/Decimal
statement.setBoolean(index, true);       // Boolean
statement.setTimestamp(index, timestamp); // Timestamp
statement.setNull(index, java.sql.Types.VARCHAR); // NULL value

Parameter indices start at 1, not 0.

ResultSet Data Retrieval

When processing query results, use the appropriate getter method:

resultSet.getString("column_name");      // String
resultSet.getInt("column_name");         // Integer
resultSet.getLong("column_name");        // Long
resultSet.getDouble("column_name");      // Double
resultSet.getBoolean("column_name");     // Boolean
resultSet.getTimestamp("column_name");   // Timestamp
resultSet.getDate("column_name");        // Date

You can also access columns by index: resultSet.getString(1) (indices start at 1).

Best Practices

✅ DO:

• Always run database operations asynchronously using Scheduler.runAsync()
• Use prepared statements with parameter binding (never concatenate user input into SQL)
• Check for null result sets before processing query results
• Use transactions for operations that must succeed or fail together
• Close resources properly (handled automatically by the API)
• Use Sql.isAvailable() before executing queries

❌ DON'T:

• Concatenate user input directly into SQL queries (SQL injection risk)
• Run long-running queries on the main thread (will freeze the server)
• Forget to handle null/error cases in result handlers
• Assume a query succeeded without checking the results
• Store sensitive data without encryption

Thread Safety

Database operations should always be executed asynchronously to avoid blocking the main server thread:

// ✅ CORRECT:  Run database operations asynchronously
Scheduler. runAsync(() => {
    Sql.makeQuery(
        "SELECT * FROM player_data WHERE username = ?",
        (stmt) => stmt.setString(1, playerName),
        (rs) => {
            // Process results asynchronously
            
            // Switch to sync thread to update game state
            Scheduler.runSync(() => {
                player.sendMessage("Data loaded!");
            });
        }
    );
});

// ❌ WRONG: Don't run queries on the main thread
Sql.makeQuery(... ); // This blocks the server!

Error Handling

All errors are automatically logged with formatted stack traces. Result handlers receive null or -1 to indicate failures:

Sql.makeQuery(
    "SELECT * FROM player_data WHERE username = ?",
    (stmt) => stmt.setString(1, playerName),
    (resultSet) => {
        if (resultSet === null) {
            error("Query failed - check server logs");
            return;
        }
        
        // Process results safely
    }
);

Sql.makeUpdate(
    "UPDATE player_data SET coins = ? ",
    (stmt) => stmt.setInt(1, 100),
    (rowsAffected) => {
        if (rowsAffected === -1) {
            error("Update failed - check server logs");
            return;
        }
        
        log(`Updated ${rowsAffected} rows`);
    }
);

Database-Specific SQL

While the Sql API works with any JDBC-compatible database, some SQL syntax varies between databases:

SQLite:

-- Auto-increment primary key
id INTEGER PRIMARY KEY AUTOINCREMENT

-- Upsert
INSERT OR IGNORE INTO table (col) VALUES (?)
INSERT OR REPLACE INTO table (col) VALUES (?)

MySQL:

-- Auto-increment primary key
id INT PRIMARY KEY AUTO_INCREMENT

-- Upsert
INSERT IGNORE INTO table (col) VALUES (?)
INSERT INTO table (col) VALUES (?) ON DUPLICATE KEY UPDATE col = ? 

PostgreSQL:

-- Auto-increment primary key (serial)
id SERIAL PRIMARY KEY

-- Upsert
INSERT INTO table (col) VALUES (?) ON CONFLICT (col) DO NOTHING
INSERT INTO table (col) VALUES (?) ON CONFLICT (col) DO UPDATE SET col = ?

Consult your database documentation for specific SQL syntax when writing portable scripts.