Database storage

Database is a deliberately small JDBC wrapper for SQLite and MySQL. It provides a connection, parameter binding, row mapping, transactions and forward-only schema migrations. It is not an ORM.

Database database = Database.sqlite(this, "example.db");

// Or:
Database database = Database.mysql(
    this, host, port, databaseName, username, password
);

keystone.onShutdown(database::close);

Every call blocks

keystone.scheduler().async(() -> {
    try {
        Optional<PlayerRecord> record = database.queryOne(
            "SELECT uuid, points FROM players WHERE uuid = ?",
            row -> new PlayerRecord(
                UUID.fromString(row.getString("uuid")),
                row.getInt("points")
            ),
            playerId.toString()
        );
        keystone.scheduler().atEntity(player, () -> showRecord(player, record));
    } catch (SQLException exception) {
        getLogger().log(Level.SEVERE, "Could not load player data", exception);
    }
});

Parameters are bound through PreparedStatement; keep values out of SQL strings. query maps all rows and closes the result set. queryOne returns the first row or Optional.empty().

Migrations

List<Migration> migrations = List.of(
    Migration.of(1, "create players", (connection, dialect) -> {
        try (Statement statement = connection.createStatement()) {
            statement.executeUpdate("CREATE TABLE players ("
                + "uuid " + dialect.textType() + " PRIMARY KEY, "
                + "points INTEGER NOT NULL DEFAULT 0)");
        }
    }),
    Migration.of(2, "add last_seen", (connection, dialect) -> {
        try (Statement statement = connection.createStatement()) {
            statement.executeUpdate("ALTER TABLE players ADD COLUMN last_seen BIGINT");
        }
    })
);

database.migrate(migrations);

Keystone creates keystone_schema_version, runs unseen migrations in numeric order and records a version only after its transaction commits. A failed migration rolls back and stops the sequence.

Migration rules are intentionally strict:

1. Versions increase and are never reused.
2. A migration that has shipped is never edited or reordered.
3. Add a new migration to correct old schema; history must remain reproducible.
4. Use SqlDialect helpers where SQLite and MySQL syntax differs.

Transactions

database.transaction(connection -> {
    debit(connection, senderId, amount);
    credit(connection, recipientId, amount);
    return null;
});

transaction commits when the function returns and rolls back on a runtime exception. JDBC calls inside the function should use the supplied connection so every statement participates in the same transaction.

Choosing a backend

Do not put database passwords in source control or log the configured JDBC URL with credentials.

Database maintains one reconnecting JDBC connection; it is not a connection pool. Serialize a plugin's database operations on one executor, or provide higher-level coordination when several async tasks could overlap. Run migrations before accepting gameplay work, fail enable if they do not complete, and always register database::close with the Keystone handle.