Convincing a File System to Behave Like a Database: Inside adapter-json

In my review of Datrix's core package I mentioned the three-layer responsibility model: the query builder validates structure, the executor validates data, and the adapter only translates — under the "never validate data" rule. This time I looked at the place where that rule is tested in the most interesting way: @datrix/adapter-json, which has no real database engine underneath and runs on top of a file system instead.

This package positions itself clearly in its own README: development, testing, prototyping, static-site build-time data. Not for high-traffic production. That honesty is a good starting point — because the rest of the code was written taking that boundary seriously too.

Each table is a single file under {root}/{tableName}.json. The file's contents are simple:

{
  "meta": { "version": 1, "name": "users", "lastInsertId": 3, "updatedAt": "..." },
  "data": [{ "id": 1, "name": "Alice" }, { "id": 2, "name": "Bob" }]
}

When a SELECT comes in, the adapter reads the file (or pulls it from cache), filters the data array, sorts it, paginates it, projects it — all in memory, in JavaScript. When an INSERT comes in, it pushes to the array and rewrites the file. Conceptually this isn't a "database" — it's an "in-memory-array-SQL-like-operations simulator + sync-to-file." That's important to understand, because nearly every design decision in the code derives from that reality.

The schema itself is stored as JSON in a special table called _datrix, exactly as core expects — which lines up one-to-one with core's migration-diff mechanism (comparing two JSON blobs is far simpler than getting tangled in dialect-specific introspection quirks). Learning once how adapter-postgres or adapter-mysql stores things and finding the same schema here too is what makes switching between adapters genuinely practical.

The match()/matchOperators() functions in runner.ts are effectively a small query engine. $eq, $gt, $in, $like, $regex, nested $and/$or/$not, even nested WHERE through relation fields ({ author: { verified: { $eq: true } } }) — all of it simulated by hand, with regex and type coercion.

The most notable thing I found here is that type coercion is taken seriously: compareValues and coerceForComparison look at a field's schema type (number, string, date, boolean) and normalize values before comparing them. It's a design that tries to prevent, at the architectural level, the class of bug where "the value read from the file is a string, the value from the query is a number, and the two never match" — a class of bug that's nearly guaranteed in a JSON-based system. date fields are a particularly fragile spot: a fresh row in cache might carry a Date object, while the same row freshly read from disk carries an ISO string; the adapter reduces both to the same epoch-millis value before comparing. Miss that distinction (easy to miss) and date-range queries silently return empty — one of the engine's sneakiest failure modes, because it doesn't throw, it just returns the wrong (empty) result.

$like/$ilike take the approach of converting SQL wildcards (%, _) into regex. The subtlety to watch for here is that the user's pattern may contain regex special characters (., (, +...) that need escaping before being handed to the regex engine. The same care is needed for operators like $startsWith/$contains, where the adapter itself adds the % — a literal % character in the user's value must not be reinterpreted as a "wildcard." These kinds of details are small but concrete proof that "imitating SQL" demands far more attention than it appears to.

The Postgres adapter offered three strategies for populate (JSON aggregation, LATERAL join, batched IN); here there's exactly one strategy, because there's no SQL — separate mapping logic is hand-written for each relation type. belongsTo builds an FK→record mapping with a Map, hasMany/hasOne group by source IDs, manyToMany reads the junction table and builds a two-sided mapping.

The real engineering problem here is manually rebuilding things that come free in SQL: when a where/orderBy/limit is requested at the populate level, the adapter has to filter a subset of the related table — in SQL that's a JOIN + WHERE; here it means wrapping the related rows back into a JsonQueryRunner and running the same filter/sort logic a second time. Doing that N times (once per candidate row) versus doing it once in bulk and reducing the result to a Set makes a serious performance difference — unnoticeable on a small table, real on a relation with a few thousand rows. The code has a character in these spots that swings between "correct but naive" and "correct and efficient"; maturity isn't uniform across relation types.

This is where the package is forced to be most honest, because the word "transaction" evokes a real database in the user's mind, but here a transaction means queuing up behind a single global lock within a single process.

SimpleLock creates an atomic lock file on the file system using the wx flag (fail if the file exists) — Node's one true "atomic create" primitive, and it's used correctly. The lock carrying its own identity (whose acquire this lock belongs to) and being refreshed at regular intervals is necessary to distinguish "the process holding the lock is slow but alive" from "the process holding the lock has crashed" — without that distinction, you either lose the lock mid-way through a long operation, or a crashed process blocks everyone forever. Both feel terrible to a user; one looks like "random data corruption," the other like "the adapter is frozen."

The interesting part on the transaction side is how, within a single process, the information of "which call belongs to this transaction" gets carried around. A naive approach would be passing a flag into every function — but this package has a chain that passes through dozens of call sites (runner → populate → table-utils → adapter), and manually threading a flag through all of it would make "forgotten in one spot, silently reads the wrong cache" a permanent risk. Instead, the call context itself (JavaScript's async call stack) answers the "is this me" question. It's a good small-scale example of the classic engineering trade-off between "add a parameter" and "carry context" — less code, but a bit more abstract to follow.

The real philosophical question that remains: within one process, transactions can be serialized by waiting on each other, but this package also coordinates multiple processes (multiple Node instances) writing to the same file. The file lock resolves that across processes too, but the commit operation itself isn't atomic — a transaction that modifies multiple tables, if it fails while writing the Nth table, leaves tables 1 through N-1 already written to disk, with no way to roll back. This is where the difference between "real ACID" and "ACID-like behavior" becomes concrete — no difference for single-table operations, a real difference if there's an interruption in the middle of a multi-table operation.

Calling write on a file is not the same thing as changing a file safely. If a process crashes mid-write, or a lockless read happens to coincide with a write under default settings, the file could end up half old content, half new content — in which case JSON.parse either blows up or silently produces a nonsensical object. The "write to a temp file first, then move it over the target" pattern (rename is considered atomic within the same file system) eliminates this risk — a reader either sees the fully old content or the fully new content, never a mixture of the two.

The import/export flow is built on similar reasoning: write all the data to a separate staging directory first, and only move it into the real directory after EVERYTHING has been written successfully. The naive approach (delete existing tables first, then write the new data) technically requires less code, but the risk is obvious: any error midway through the import permanently loses all the previous data. The staging approach costs a bit more disk I/O and a bit more code, but it buys the guarantee that "if something fails, worst case, nothing changes." In a data-migration tool, that trade-off is almost always the right direction.

The mtime-based cache mechanism is simple and effective: if a file's last-modified time hasn't changed, the cached parsed content is still valid. But holding an array/object reference in cache has a hidden danger: if that reference is handed directly to the caller, and the caller (or the adapter's own populate logic) writes new fields onto that object, the change now lives inside the cache itself — a subsequent write can silently persist this "contaminated" data to disk. This kind of bug is especially insidious because it never throws anywhere; fields that shouldn't exist just start appearing in the file after a while.

The fix is conceptually simple but demands discipline: copy a row or table before exposing it to mutation, and don't touch the cache's original state during a write until the disk write has succeeded. The difference between "copy, then mutate" and "mutate, then hope" determines whether the cache stays consistent when a write fails.

The README already draws its own boundaries clearly, and the code takes those boundaries seriously: small datasets, low-concurrency writes, development/testing/prototyping, static-site build-time data. In these scenarios, the promise the adapter makes (the same QueryObject contract as the SQL adapters, the same relation behavior, the same error types) genuinely holds — switching from a Postgres adapter to the JSON adapter and having tests behave the same way is concrete proof of the three-layer architecture.

But some facts don't change: this is an engine that loads the entire table into memory and filters it with JavaScript. The slowdown as row count grows isn't non-linear, but there's definitely a linear cost — every query scans every row on a table with thousands of rows. Multi-table transactions don't provide full ACID guarantees. And the file lock can coordinate multiple processes on a single machine, but is useless in a distributed system (multiple machines, without a shared file system).

The question someone choosing this adapter should ask isn't "will this work in production" — the README already answers that. The real question is: in your dev loop, in your test suite, or in a small static project, do you need something that speaks (behaviorally) the same contract as your production adapter, without the operational overhead of standing up a real database? If the answer is yes, the code I saw here genuinely worked hard to keep that promise.