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

# Database

> Persistent, connection-pooled SQL storage with a dialect-aware repository layer.

`xyz.refineryteam.refinerycore.api.database` provides persistent SQL storage backed by [HikariCP](https://github.com/brettwooldridge/HikariCP) connection pooling, plus a repository layer that generates dialect-correct SQL for whichever backend you choose — so your plugin's data-access code doesn't need to branch on database type.

<Note>
  Looking for data that **doesn't** need to survive a restart? See [Temporary Storage](/refinerycore/temporary-storage) instead — persistent storage in RefineryCore always means this database layer.
</Note>

## Supported databases

| `DatabaseType` | Notes                                                                    |
| -------------- | ------------------------------------------------------------------------ |
| `MYSQL`        | Standard MySQL                                                           |
| `MARIADB`      | MariaDB — syntax-compatible with MySQL, but uses its own JDBC driver/URL |
| `POSTGRESQL`   | PostgreSQL                                                               |
| `SQLITE`       | Single-file, embedded — good default for smaller plugins                 |
| `H2`           | Single-file, embedded, run in MySQL-compatibility mode                   |

## Opening a database

`RefineryDatabase` has one static factory method per supported type:

```java PluginDatabase.java icon="java" theme={"system"}
// Embedded, file-based
RefineryDatabase db = RefineryDatabase.sqlite(this, "data.db");
RefineryDatabase db = RefineryDatabase.h2(this, "data.h2");

// Networked
RefineryDatabase db = RefineryDatabase.mysql(this, "localhost", 3306, "mydb", "user", "pass");
RefineryDatabase db = RefineryDatabase.mariadb(this, "localhost", 3306, "mydb", "user", "pass");
RefineryDatabase db = RefineryDatabase.postgresql(this, "localhost", 5432, "mydb", "user", "pass");
```

File-based databases (`sqlite`, `h2`) resolve their file relative to the plugin's data folder and create parent directories automatically. Networked databases (`mysql`, `mariadb`, `postgresql`) are pre-tuned with reasonable pool sizing, timeouts, and prepared-statement caching.

Always close the database on plugin disable:

```java Plugin.java icon="java" theme={"system"}
@Override
public void onDisable() {
    db.close();
}
```

## The repository pattern

Rather than writing raw SQL per-entity, extend `AbstractRepository<T, ID>` and describe your entity's shape — table creation, upserts, and lookups are generated for you in a dialect-correct way.

### Defining an entity and repository

```java PlayerRepository.java icon="java" theme={"system"}
public record PlayerRecord(UUID id, String username, long firstJoined, int coins) {}

public final class PlayerRepository extends AbstractRepository<PlayerRecord, UUID> {

    public PlayerRepository(RefineryDatabase database) {
        super(database, "players", "id", List.of(
                ColumnDefinition.of("username", "VARCHAR(16) NOT NULL"),
                ColumnDefinition.of("first_joined", "BIGINT NOT NULL"),
                ColumnDefinition.of("coins", "INTEGER NOT NULL DEFAULT 0")
        ));
    }

    @Override
    protected String idColumnDefinition() {
        // UUIDs are stored as their string form for portability across MySQL/SQLite/H2/PostgreSQL.
        return "VARCHAR(36) PRIMARY KEY";
    }

    @Override
    protected PlayerRecord mapRow(ResultSet rs) throws SQLException {
        return new PlayerRecord(
                UUID.fromString(rs.getString("id")),
                rs.getString("username"),
                rs.getLong("first_joined"),
                rs.getInt("coins")
        );
    }

    @Override
    protected void bindAll(PreparedStatement stmt, PlayerRecord entity) throws SQLException {
        stmt.setString(1, entity.id().toString());
        stmt.setString(2, entity.username());
        stmt.setLong(3, entity.firstJoined());
        stmt.setInt(4, entity.coins());
    }

    @Override
    protected void bindId(PreparedStatement stmt, int index, UUID id) throws SQLException {
        stmt.setString(index, id.toString());
    }
}
```

Four methods to implement:

| Method                                        | Purpose                                                                                                                                |
| --------------------------------------------- | -------------------------------------------------------------------------------------------------------------------------------------- |
| `idColumnDefinition()`                        | Raw SQL type/constraints for the primary key column, e.g. `"VARCHAR(36) PRIMARY KEY"`                                                  |
| `mapRow(ResultSet)`                           | Builds an entity instance from a result row                                                                                            |
| `bindAll(PreparedStatement, T)`               | Binds every column (id first, then each `ColumnDefinition` in order) starting at parameter index 1 — used for both inserts and upserts |
| `bindId(PreparedStatement, int index, ID id)` | Binds just the id, for `WHERE`/`DELETE` clauses                                                                                        |

### Registering repositories

`RepositoryRegistry` tracks repositories against a database, creating each one's table on registration:

```java Plugin.java icon="java" theme={"system"}
RefineryDatabase db = RefineryDatabase.sqlite(this, "data.db");
RepositoryRegistry registry = new RepositoryRegistry(db);
PlayerRepository players = registry.register(new PlayerRepository(db));
```

`register()` returns the same instance you passed in, so you can assign it directly for convenient access elsewhere in your plugin. Call `registry.close()` (which closes the underlying database) from `onDisable()`.

### Using a repository

Every `Repository<T, ID>` exposes standard CRUD, each with a blocking and an async (`CompletableFuture`-based) variant:

```java PlayerManager.java icon="java" theme={"system"}
Optional<PlayerRecord> found = players.findById(uuid);
List<PlayerRecord> all = players.findAll();
players.save(new PlayerRecord(uuid, "Steve", System.currentTimeMillis(), 100)); // insert or update
players.deleteById(uuid);
boolean exists = players.existsById(uuid);
long total = players.count();

// Async variants run on the common ForkJoinPool — prefer these off the main thread:
players.findByIdAsync(uuid).thenAccept(result -> { /* ... */ });
players.saveAsync(record);
```

`save()` is always an **upsert** — insert if the primary key doesn't exist, update if it does — using each dialect's native syntax (`ON DUPLICATE KEY UPDATE` for MySQL/MariaDB/H2, `ON CONFLICT ... DO UPDATE` for SQLite/PostgreSQL) so you never need to check existence yourself before saving.

`AbstractRepository` also exposes `findPage(limit, offset)` for pagination without hand-writing `LIMIT`/`OFFSET`, and a protected `withConnection(ConnectionAction)` escape hatch for custom queries (joins across repositories sharing a database, complex `WHERE` clauses) that the standard CRUD surface doesn't cover.

## Dialect handling — `SqlDialect`

`SqlDialect` (`xyz.refineryteam.refinerycore.api.database.dialect`) encapsulates the small syntax differences between backends that `AbstractRepository` needs to generate correct SQL:

| Concern            | MySQL / MariaDB / H2                                   | SQLite / PostgreSQL                                                                                           |
| ------------------ | ------------------------------------------------------ | ------------------------------------------------------------------------------------------------------------- |
| Upsert             | `INSERT ... ON DUPLICATE KEY UPDATE col = VALUES(col)` | `INSERT ... ON CONFLICT(pk) DO UPDATE SET col = excluded.col`                                                 |
| Identity column    | `BIGINT AUTO_INCREMENT PRIMARY KEY` (MySQL/MariaDB/H2) | `INTEGER PRIMARY KEY AUTOINCREMENT` (SQLite) / `BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY` (PostgreSQL) |
| Identifier quoting | `` `backtick` ``                                       | `"double quote"`                                                                                              |

You generally don't need to touch `SqlDialect` directly — `AbstractRepository` resolves it automatically from the `RefineryDatabase`'s `DatabaseType` via `SqlDialect.of(type)`. It's useful to know it exists if you're writing custom SQL via `withConnection` and want dialect-correct quoting/upserts without duplicating branching logic.

## Low-level access

For anything the repository layer doesn't cover, `RefineryDatabase` still exposes raw SQL helpers:

```java PlayerManager.java icon="java" theme={"system"}
db.execute("UPDATE players SET coins = coins + ? WHERE id = ?", stmt -> {
    stmt.setInt(1, 50);
    stmt.setString(2, uuid.toString());
});

List<String> names = db.query(
    "SELECT username FROM players WHERE coins > ?",
    stmt -> stmt.setInt(1, 1000),
    rs -> rs.getString("username")
);

// Async equivalents:
db.executeAsync(sql, prepare);
db.queryAsync(sql, prepare, mapper);
```

`db.getConnection()` returns a raw `java.sql.Connection` from the pool for anything even lower-level.

## Required dependencies

Add the JDBC driver(s) for whichever database(s) you use:

```kotlin theme={"system"}
dependencies {
    compileOnly("com.zaxxer:HikariCP:7.0.2")

    // Only what you actually use:
    compileOnly("org.mariadb.jdbc:mariadb-java-client:3.5.3")
    compileOnly("org.postgresql:postgresql:42.7.4")
    // MySQL, SQLite, and H2 drivers as needed for your setup
}
```

List them under `libraries.addAll(...)` in `bukkitPluginYaml` (rather than shading them) so Paper's library loader resolves them at runtime:

```kotlin theme={"system"}
bukkitPluginYaml {
    libraries.addAll(
        "com.zaxxer:HikariCP:7.0.2",
        "org.mariadb.jdbc:mariadb-java-client:3.5.3",
        "org.postgresql:postgresql:42.7.4"
    )
}
```