Choosing a Backend

Choosing a Backend

What this page covers: the full capability matrix across all six backends (transactions, schema migrations, secondary indexes, optimistic locking, persistence), a decision guide for picking one, and how each backend stores your data at rest. The whole point of EveryDatabase is that this is a deployment choice, not an architectural one — your data-access code is identical across all of them.

📌 Note — every factory below returns a Storage whose API is identical. Switching backends is a one-line change at construction; everything from storage.repository(...) onward stays the same. See Quick Start for the full round-trip.

---

Pick one in 30 seconds

import br.com.finalcraft.everydatabase.Storages;
import br.com.finalcraft.everydatabase.modules.sql.SqlConfig;

// Production server, full SQL features:
var sql   = Storages.createSQL(new SqlConfig("jdbc:mariadb://localhost:3306/mydb", "root", "root"));
var pg    = Storages.createPostgreSQL(new SqlConfig("jdbc:postgresql://localhost:5432/mydb", "root", "root"));

// Embedded — zero-ops, single process:
var h2    = Storages.createH2(new SqlConfig("jdbc:h2:file:./data/storage", "", ""));

// Document store:
var mongo = Storages.createMongo(new MongoConfig("mongodb://localhost:27017", "mydb"));

// Human-readable on disk / no server:
var file  = Storages.createLocalFile(new LocalFileConfig(Paths.get("data")));

// Tests / CI — ephemeral:
var mem   = Storages.createInMemory();

⚠️ Gotcha — the generic Storages.create(StorageConfig) dispatches by config type, but any SqlConfig always picks the MySQL/MariaDB dialect. For PostgreSQL or H2 you must call createPostgreSQL / createH2 explicitly — they share the same SqlConfig class, so the generic factory can't tell them apart. See Gotchas & Pitfalls.

---

Capability matrix

Optional features are expressed as interfaces a Storage may implement, checked with instanceof — never as flags. A backend that can't do something simply doesn't implement the interface, so the compiler stops you from misusing it.

Backend	Factory	Transactions	Schema migrations	Secondary indexes	Optimistic locking	Persistence
MySQL / MariaDB	createSQL	✅	✅	✅ native column + B-tree	✅ enforced	Durable
PostgreSQL	createPostgreSQL	✅	✅	✅ native column + B-tree	✅ enforced	Durable
H2 (mem / file / tcp)	createH2	✅	✅	✅ native column + B-tree	❌ (by design)	Durable / ephemeral
MongoDB	createMongo	✅ (replica set)	✅	✅ native index	✅ enforced	Durable
Local files	createLocalFile	❌	✅	⚠️ full scan (no real index)	❌	Durable (one file per entity)
In-memory	createInMemory	✅ (no isolation)	❌	✅ in-memory map	❌	Ephemeral

A few rows deserve a footnote:

• Transactions (Transactions) come from tx.TransactionalStorage. Every SQL dialect (including H2), MongoDB, and in-memory implement it; local files do not. MongoDB needs a replica set — on a standalone server inTransaction(...) throws at runtime. In-memory is atomic but provides no isolation.
• Schema migrations (Schema Migrations) come from schema.SchemaAwareStorage. SQL, Mongo, and local files implement it (tracking applied versions in a reserved _schema_migrations table/collection/file). In-memory does not — there is nothing to migrate.
• Optimistic locking (Optimistic Locking) is opt-in per descriptor and enforced only by MySQL/MariaDB, PostgreSQL, and MongoDB. H2 deliberately opts out: a versioned descriptor on H2 silently degrades to plain upsert (never throws OptimisticLockException, never fails at startup). Local files and in-memory don't enforce it either.
• Indexes (Indexing & Queries) are declared, never implicit. Local files have no real index — they answer queries with a correct-but-slow full scan, yet they still reject an undeclared query field with IllegalArgumentException like every other backend, so a query that works on local files keeps working when you swap to SQL.

---

Data at rest

The entity is serialized by its Codecs and stored differently per backend — readable and queryable in standard DB tooling wherever the engine supports it:

Backend	Storage format	Notes
MySQL / MariaDB	native JSON column	queryable & readable in standard SQL tools
PostgreSQL	native JSON column	plain-text JSON (not JSONB)
H2	TEXT column	H2's JSON support is limited on 1.x; stored as text
MongoDB	native BSON sub-document	not an escaped string — a real document
Local files	one file per entity	JSON or YAML (the only backend that accepts YAML)
In-memory	live JVM objects (parsed JSON)	nothing on disk

📌 Note — SQL, Mongo, and in-memory require a JSON codec (codec.isJsonCodec()); they parse/store the payload as native JSON. Local files is the only backend that accepts a non-JSON codec such as JacksonYamlCodec. See Codecs.

Index values get their own materialized column/field where a real index exists: SQL and Mongo store a _idx_<field> column/field populated at save time with a real B-tree behind it; in-memory keeps a Map<value, Set<key>>; local files store nothing extra and scan. TIMESTAMP index fields are stored as native date types in SQL columns for readability but compared as epoch-millis everywhere.

---

🧭 Decision guide

🧭 Decision — start from your deployment constraints, not the feature list:

• Multiple app instances writing the same data, want every guarantee? → MySQL/MariaDB or PostgreSQL. Full transactions, enforced optimistic locking, real indexes, durable JSON columns. The default choice for a real server.
• Document-shaped data, already running Mongo, or want native BSON queries? → MongoDB. Same guarantees as SQL, but transactions need a replica set.
• Single process, zero ops, want a file you can back up? → H2 (file mode). Full SQL, transactions, indexes — but no optimistic-locking enforcement, so avoid it when concurrent writers matter.
• Want to read/diff your data by eye, or have no DB server at all? → Local files with JacksonYamlCodec. One human-readable file per entity. No transactions, no real index (full scan), no locking — fine for small/cold datasets.
• Tests, CI, prototyping? → In-memory. Instant, ephemeral, no setup. Transactions exist but without isolation; no migrations.

A common production pattern: ship on H2 or local files for small deployments, let operators flip to MariaDB/Mongo for large ones, and move the live data with Moving Data Between Backends — no code changes, source untouched.

---

Per-backend pages

Each backend page covers its config, construction modes, and dialect quirks:

• MySQL & MariaDB — SqlConfig, HikariCP PoolTuning, native JSON column.
• PostgreSQL — createPostgreSQL (not the generic create), JSON column.
• H2 — mem / file / tcp URLs, the no-optimistic-locking opt-out, the 1.x↔2.x file-format warning.
• MongoDB — MongoConfig, replica-set transactions, BSON storage.
• Local Files — one file per entity, YAML option, full-scan queries.
• In-Memory — ephemeral, tests/CI.

---

See also

• Quick Start — the minimal end-to-end with a chosen backend.
• The Async API — the CompletableFuture model every backend shares.
• Transactions · Schema Migrations · Indexing & Queries · Optimistic Locking — the capabilities the matrix references.
• Codecs — JSON-required vs YAML-capable backends.
• Moving Data Between Backends — migrate live data across any two backends.
• Gotchas & Pitfalls — the create() MySQL-dialect default, H2 no-versioning, JSON-codec rule.