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.

📌 Note — both flavors give you the MySQL driver: core references com.mysql:mysql-connector-j as a POM dependency, and libby downloads it at runtime. MariaDB's own driver (org.mariadb.jdbc) is included by neither — connect to MariaDB through the MySQL driver with a jdbc:mysql:// URL, or add the MariaDB driver yourself for a jdbc:mariadb:// URL.

---

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).
• ⚠️ Cross-Process Cache Sync — no native push feed (MySQL/MariaDB has no LISTEN/NOTIFY equivalent), so cache sync here uses version polling.

Cache sync via version polling

There's no change feed to subscribe to, so the manager's CacheSync falls back to polling — set a pollEvery(...) interval and it version-checks each cache's keys on a daemon thread:

CacheSync.attach(sqlStorage)
        .pollEvery(Duration.ofSeconds(10))   // required: this backend can't push
        .bind(playersManager)
        .start();

📌 Note — polling detects an in-place update only on a versioned descriptor (@OptimisticLock); without one it catches deletes only. Latency is one poll interval. The wiring is identical to a push backend — swapping to Mongo/Postgres later just makes it near-instant. See Cross-Process Cache Sync.

---

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.
• Cross-Process Cache Sync — version polling (this backend has no push feed).
• 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 — how the MySQL driver reaches your classpath (POM dep vs runtime download).