MySQL and MariaDB

MySQL & MariaDB

What this page covers: creating a MySQL/MariaDB-backed Storage with Storages.createSQL, configuring the HikariCP connection pool via PoolTuning, and how entities are stored in a native JSON column. This is the default, full-featured SQL dialect — it supports every optional capability EveryDatabase offers.

📌 Note — MariaDB and MySQL share one driver path and one dialect here (backtick identifier quoting, JSON data column, ON DUPLICATE KEY UPDATE upsert). Pick the right JDBC URL prefix and you're done. See Choosing a Backend for the cross-backend comparison.

---

The smallest runnable example

import br.com.finalcraft.everydatabase.*;
import br.com.finalcraft.everydatabase.codec.JacksonJsonCodec;
import br.com.finalcraft.everydatabase.modules.sql.SqlConfig;
import br.com.finalcraft.everydatabase.modules.sql.SqlStorage;

EntityDescriptor<UUID, PlayerData> PLAYERS = EntityDescriptor.builder(UUID.class, PlayerData.class)
        .collection("players")
        .keyExtractor(PlayerData::getUuid)
        .codec(new JacksonJsonCodec<>(PlayerData.class))
        .build();

SqlStorage sql = Storages.createSQL(
        new SqlConfig("jdbc:mariadb://localhost:3306/mydb", "root", "root"));
sql.init().join();

Repository<UUID, PlayerData> repo = sql.repository(PLAYERS);
repo.save(new PlayerData(id, "Alice", 100)).join();
Optional<PlayerData> alice = repo.find(id).join();

sql.close().join();

📌 Note — createSQL returns the concrete SqlStorage, so the optional capability interfaces are reachable without a cast (SqlStorage implements TransactionalStorage, SchemaAwareStorage). Every call returns a CompletableFuture; .join() is shown for brevity — compose with thenApply/thenCompose in real code. There are no blocking variants. See The Async API.

---

JDBC URLs

The driver and dialect are inferred from the URL prefix. For this dialect use either:

new SqlConfig("jdbc:mariadb://localhost:3306/mydb", "root", "root")   // MariaDB driver
new SqlConfig("jdbc:mysql://db.internal:3306/app",  "user", "pass")   // MySQL driver

The entity table is auto-created on first repository(...) (idempotent — it also reconciles declared indexes), so you don't write DDL for entities yourself. Schema migrations cover everything else; see Schema Migrations.

⚠️ Gotcha — with the Distribution Flavors the MySQL driver is not bundled (it's GPL). Add com.mysql:mysql-connector-j yourself for the standalone flavor; the core and libby flavors include it. MariaDB's own driver is bundled in neither by default — use the version that matches your URL prefix.

---

Tuning the connection pool — PoolTuning

SqlConfig has a four-arg overload that accepts a PoolTuning controlling the underlying HikariCP pool. There are two PoolTuning constructors and a defaults() factory:

import br.com.finalcraft.everydatabase.modules.sql.PoolTuning;
import java.time.Duration;

// 4-arg: minIdle, maxSize, connectTimeout, idleTimeout (maxLifetime defaults to 30 min)
PoolTuning pool = new PoolTuning(2, 10, Duration.ofSeconds(30), Duration.ofMinutes(10));

// 5-arg: also sets maxLifetime explicitly
PoolTuning full = new PoolTuning(
        2,                          // minIdle  — idle connections kept warm
        10,                         // maxSize  — hard cap on simultaneous connections
        Duration.ofSeconds(30),     // connectTimeout — wait for a free connection
        Duration.ofMinutes(10),     // idleTimeout — evict a connection idle this long
        Duration.ofMinutes(30));    // maxLifetime — total lifetime of a pooled connection

SqlStorage tuned = Storages.createSQL(new SqlConfig(
        "jdbc:mysql://db.internal:3306/app", "user", "pass", full));

PoolTuning.defaults() (used by the three-arg SqlConfig constructor) is new PoolTuning(2, 10, Duration.ofSeconds(30), Duration.ofMinutes(10)) — i.e. 2 idle / 10 max / 30 s connect / 10 min idle / 30 min lifetime — conservative values suited to a single game server.

Parameter	Default	Meaning
minIdle	2	minimum idle connections kept alive
maxSize	10	maximum pool size (hard cap on simultaneous connections)
connectTimeout	30 s	max wait for a connection to become available
idleTimeout	10 min	how long a connection may sit idle before eviction
maxLifetime	30 min	total lifetime of a pooled connection (HikariCP maxLifetime)

💡 Tip — keep maxLifetime a few seconds shorter than any server-side connection timeout (e.g. MySQL wait_timeout) so the pool retires connections before the server drops them out from under you.

---

How data is stored

Each entity collection becomes a table whose data column is a native MySQL/MariaDB JSON column — not an escaped string — so rows stay queryable and readable in standard SQL clients:

SELECT storage_key, storage_data FROM players;   -- storage_data is real JSON

Declared indexes (Indexing & Queries) are materialized into a real _idx_<field> column (dots → underscores) with a B-tree behind it, populated at save time. Adding a new @Indexed field to an existing table auto-ALTERs the column in and backfills it from existing rows; removing one drops it — all reconciled the next time the repository is opened.

📌 Note — the codec must be a JSON codec here (isJsonCodec()), like all SQL/Mongo/memory backends. A JacksonYamlCodec is rejected with IllegalArgumentException; only Local Files accepts YAML. See Codecs.

---

Capabilities this backend supports

MySQL/MariaDB implements every optional capability, all enforced:

• ✅ Transactions — inTransaction(scope -> ...) over a single shared connection.
• ✅ Schema Migrations — register(new SqlMigration(){...}).migrate(); applied versions tracked in _schema_migrations. Write migrations to be idempotent — DDL implicitly commits here.
• ✅ Indexing & Queries — native column + B-tree per declared index.
• ✅ Optimistic Locking — the version check is enforced (a stale write throws OptimisticLockException).

---

See also

• Choosing a Backend — the full capability matrix and decision guide.
• PostgreSQL · H2 — the other SQL dialects (different quoting / column type / upsert).
• Transactions · Schema Migrations · Indexing & Queries · Optimistic Locking — the capabilities this backend supports.
• The Async API — the CompletableFuture model.
• Codecs — why a JSON codec is required here.
• Dependency Versions & Overrides — driver, HikariCP, and Java-floor versions.
• Distribution Flavors — why the MySQL driver isn't in the standalone jar.