Database module

VersatiaModule-Database gives every plugin its own database, injected and ready. Without any configuration a plugin gets a SQLite file in its data folder with the migrations from its JAR already applied; the server admin moves it to H2, MySQL, MariaDB or PostgreSQL by editing database.yml, and the module takes care of the pool, the SQL differences between engines, the threads, the reload and the shutdown. You write SQL; the module runs it where and when it is safe.

ExampleVersatiaExample-Modules-Database

Player coins with no setup: three migrations, a player store loaded at login and saved on quit, a transaction that moves coins between two players, and a leaderboard read in the background.

Open the repositoryStart with Profiles.kt

Set-up follows every module: VersatiaStarter brings the API, VersatiaModule-Database goes under depend:, and the module's JAR on the server. At its first start Paper downloads the libraries the module lists in its plugin.yml (HikariCP and the H2, MariaDB and PostgreSQL drivers) into the server's libraries/ folder; the SQLite and MySQL drivers are the server's own.

A build that depends on VersatiaAPI alone adds compileOnly("dev.versatia:VersatiaModule-Database:0.0.1") (libs.versatia.database).

For server admins

The server admin installs the module, and moves, configures and watches every plugin's database without code; the Database module part of the server admin guide is the page to send them to.

For plugin developers

The plugin's SQL goes into its JAR, under src/main/resources/database/: migrations/ for the tables (Migrations), queries/ for named queries, both written with {prefix} and the other tokens. The code injects Database and works through statements, stores and their events.

plugin.yml

name: Shop
main: com.example.shop.ShopPlugin
depend:
  - VersatiaCore
  - VersatiaModule-Database

The module gives your plugin one bean, and two packages hold what you write around it:

Inject	Package	What it is
Database	dev.versatia.database	your plugin's database: statements, transactions, background work, its engine, prefix and status
	dev.versatia.database.store	stores: KeyedStore, PlayerStore, StoreSchema, made by database.store(...) and database.playerStore(...)
	dev.versatia.database.mapping	Column and ColumnCodec, for columns whose name or type needs help

A first plugin

The table comes from a migration in the JAR. {prefix} and {uuid} are tokens: the table prefix of your plugin and the UUID column type of whatever engine the admin picks.

src/main/resources/database/migrations/V1__create_profiles.sql

CREATE TABLE {prefix}profiles (
    uuid      {uuid} NOT NULL PRIMARY KEY,
    name      VARCHAR(16) NOT NULL DEFAULT '',
    coins     BIGINT NOT NULL DEFAULT 0,
    last_seen {timestamp}
) {table_options};

A row is an immutable data class, mapped by its constructor parameter names (lastSeen is the column last_seen). A player store keeps one row per player: loaded before the player joins, changed in memory, saved on quit, by autosave and when the plugin stops.

import dev.versatia.database.Database
import dev.versatia.database.playerStore
import dev.versatia.database.store.PlayerStore
import dev.versatia.di.LocalBean

data class Profile(val uuid: UUID, val name: String = "", val coins: Long = 0, val lastSeen: Instant? = null)

@LocalBean
class Profiles(database: Database) : PlayerStore<Profile> by database.playerStore(
    "profiles", Profile::uuid,
    onJoin = { player, profile -> profile.copy(name = player.name) },
    onLeave = { _, profile -> profile.copy(lastSeen = Instant.now()) },
    create = { Profile(it) },
) {
    fun give(player: Player, coins: Long) = update(player) { it.copy(coins = it.coins + coins) }
}

Changing a value touches memory only, so it is safe in any event handler:

class Rewards(private val profiles: Profiles) : Listener {
    @EventHandler
    fun onKill(event: EntityDeathEvent) {
        event.entity.killer?.let { profiles.give(it, 1) }
    }
}

Anything that reaches the database blocks, so outside start, reload and stop it runs on the plugin's database threads and comes back to the server thread with thenSync:

import dev.versatia.database.list

data class TopEntry(val name: String, val coins: Long)

@LocalBean
class Leaderboard(private val database: Database) {
    fun show(sender: CommandSender) = database.async { sql("SELECT name, coins FROM {prefix}profiles ORDER BY coins DESC LIMIT 10").list<TopEntry>() }
        .thenSync { top -> top.forEachIndexed { i, entry -> sender.sendMessage("${i + 1}. ${entry.name}: ${entry.coins}") } }
}

At the first start the log shows the migrations and one summary line:

[Shop] Applying V1__create_profiles.sql
[Shop] SQLITE database.db, prefix shop_, schema V1 (applied V1 in 12 ms)

Every plugin's tables carry its own prefix, its friendly name in snake_case plus _, so the table here is shop_profiles. Later starts show schema V1 alone. The module has also written plugins/Shop/database.yml, three lines recording where the data is and its prefix, which the admin edits to move the plugin to another engine.

What the plugin did not write: loading each player at login with a refusal when that fails, the name and last_seen upkeep (players still online at shutdown included), the autosave that writes only the changed columns, the final save, the pool, the thread rules and the start-up check that profiles has a column for every property of Profile.

The pages of this module

Page	What it covers	Mostly for
Statements and mapping	database.sql, parameters, reading rows into classes, insert and upsert, types and codecs, batches	Plugin developer
Threads, background work and transactions	what blocks, the server-thread guard, database.async and its futures, failures, transactions	Plugin developer
Stores and player stores	write-behind caches of rows, the login of a player, saving, store transactions	Plugin developer
Migrations and named queries	the SQL files of the JAR, engine overrides, history, resuming, backups, named(...)	Plugin developer
Engines and tokens	the engines, the tokens, MySQL and MariaDB, the database.yml a plugin ships	Plugin developer
Opening, shutdown and errors	when the database opens, the shutdown, database.status, the exceptions	Plugin developer
Server admin guide: Database	database.yml, connections.yml, config.yml, moving data, backups, /versatiadb status, logs, troubleshooting	Server admin
Best practices	{prefix} everywhere, extension tables pointing to their row, memory-only state in beans	Plugin developer
Corner cases	the edge rules of every part	both

One instance per plugin

Database is a module prototype: the instance you inject is your plugin's own, with its own settings, pool (or share of a pool), threads, tables and stores. Two plugins never see each other's statements or caches. Two plugins sharing one MySQL database keep their own migration history there, and their table prefixes, the friendly names by default, keep their tables apart.

The database is a Reloadable bean of your plugin. At start it reads its settings and SQL files, opens and migrates after your beans are built and before your @AutoInvoke functions run, so a PLUGIN_START_ONLY or PLUGIN_RELOAD_OR_START function finds the tables ready. It closes after your PLUGIN_STOP functions, which may still use it. A bean constructor may only register stores and types (database.playerStore, database.store, database.registerType); anything else there fails with Plugin Shop: its database is used before the plugin started; use it from a PLUGIN_RELOAD_OR_START function or later.