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	Change feed	Persistence
MySQL / MariaDB	createSQL	✅	✅	✅ native column + B-tree	✅ enforced	❌ (poll)	Durable
PostgreSQL	createPostgreSQL	✅	✅	✅ native column + B-tree	✅ enforced	✅ push	Durable
H2 (mem / file / tcp)	createH2	✅	✅	✅ native column + B-tree	❌ (by design)	❌ (poll)	Durable / ephemeral
MongoDB	createMongo	✅ (replica set)	✅	✅ native index	✅ enforced	✅ push	Durable
Local files	createLocalFile	❌	✅	⚠️ full scan (no real index)	❌	❌ (poll)	Durable (one file per entity)
In-memory	createInMemory	✅ (no isolation)	❌	✅ in-memory map	❌	✅ push (per-process)	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.
• Change feed (Cross-Process Cache Sync) is the changefeed.ChangeFeedStorage capability — a backend-native push of "entity X changed" so the manager's CacheSync can invalidate other instances' caches. Mongo (Change Streams), PostgreSQL (LISTEN/NOTIFY), and InMemory push; MySQL/MariaDB, H2, and local files have no push feed and use version polling instead — same CacheSync API, just a poll interval. This is independent of (and complementary to) optimistic locking: locking guards writes, the change feed cures read staleness.

---

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 · Cross-Process Cache Sync — 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.