SQLite 2 FlatBuffers
A converter tool that transforms structured SQLite databases into binary FlatBuffers for zero-copy, allocation-free runtime reads in Unity. FlatBuffers schemas are hand-written to take advantage of nested objects instead of ID references, and the converter populates the binary from the SQL data.
Problem
Game projects often need large amounts of structured data: codexes, item databases, quest logs. SQLite is excellent for authoring and modifying this data externally, but querying it directly at runtime carries heavy overhead. JSON and CSV both require parsing and allocation on load.
I needed a format that gave me the flexibility of SQLite for editing with the performance of a binary buffer at runtime, and a schema design that could nest related data directly rather than resolving IDs at runtime.
Key Technical Decisions
- FlatBuffers for zero-copy access. Once the binary is loaded, the game reads data straight from the buffer. No per-frame allocation, no parsing overhead, no garbage collection pressure.
- Hand-written schemas with nested objects. The FlatBuffers schema is authored by hand so it can use nested tables instead of ID references. This mirrors the relational structure of SQLite but produces a tree layout that's traversed without joins at runtime.
- Direct nesting instead of ID references. The hand-written schema nests child data directly within parent tables rather than referencing them by ID. Faster traversal, simpler mental model for game code.
- Test case: Mass Effect 2 codex. Hundreds of entries organised into categories and subcategories, with primary entries (audio + text) and secondary entries (text only), all unlockable as the player progresses. Demands both large volume and fast traversal.
Next Steps
- Add incremental conversion so only changed tables rebuild, cutting iteration time on large databases.
- Build a Unity Editor window for one-click conversion with schema preview and validation.