Database API

Creating Tables

Addons share XCore's connection pool and never open their own. The builder writes the right dialect for SQLite, MySQL and PostgreSQL, so an addon has no reason to care which one is in use.

api().tableManager().createTable("myaddon_homes")
    .column("id", ColumnType.SERIAL)
    .column("player_uuid", ColumnType.CHAR, 36).notNull()
    .column("name", ColumnType.VARCHAR, 32).notNull()
    .column("world", ColumnType.VARCHAR, 64).notNull()
    .index("player_uuid")
    .uniqueIndex("player_uuid", "name")
    .build();

Columns can also be added to the shared players table, which is the right place for a single value per player:

api().columnBuilder()
    .addColumn("last_kit", ColumnType.VARCHAR).length(32).defaultValue("").notNull()
    .apply();

That table is read and pushed to Redis in full on every write, by every addon. A value that grows — a history, a list — belongs in its own table. /xcore diag measures each column and names the heavy ones.

Queries

api().query("myaddon_homes").select("*").where("player_uuid", uuid).executeAsync();
api().query("myaddon_homes").select("*").whereIn("player_uuid", uuids).executeAsync();
api().query("a").leftJoin("b", "a.id", "b.a_id").select("a.name", "b.score").executeAsync();
api().query("stats").update().setRelative("kills", 1).where("uuid", id).executeUpdateAsync();
api().query("logs").insert().addRow(row1).addRow(row2).executeBatchAsync();

setRelative does the arithmetic inside the database, so two servers cannot both read the old value and write back a total that loses the other's change.

Row counts

executeUpdateAsync() completes with the number of rows affected, which is what makes a conditional write usable: an update guarded on the stock or the balance touching one row is a sale, touching none is a refusal.

execute(), executeUpdate() and executeCount() are the same queries run on the calling thread, for code already off the main one — a PagedGui loading its list, a task on the database pool. They save a join(), and a join() from the database pool onto the database pool is exactly what deadlocks. From a tick thread they block the server, and the debug watchdog names the call site.

orderBy can be called more than once; the clauses stack in the order given.

Upsert

api().query("myaddon_homes").insert()
    .set("player_uuid", uuid).set("name", name).set("world", world)
    .upsert("player_uuid", "name")
    .executeUpdateAsync();

Write the row, or update it if it is already there. Replaces the usual select-then-insert-or-update, which costs three round trips and races between the read and the write. The key columns need a unique index.

Transactions & Migrations

Either everything in the body is written, or none of it is. Throw from the body to roll back on purpose.

api().tableManager().transaction(conn -> {
    // take the money, then hand the item over
    return true;
});

Schema changes that must run exactly once, in order, on servers that may be several versions behind:

api().tableManager().migrator("MyAddon")
    .version(1, conn -> { /* create the new table */ })
    .version(2, conn -> { /* copy the old column into it */ })
    .run();

The version reached is recorded per addon, and steps below it are skipped on the next start. Each step runs inside a transaction, so a failure leaves the database as it was.