Why did you make SMPI?
App reliability on different device types
Releasing a React Native application to many platforms using current RN SQLite libraries made me uneasy. Sure my code worked on the iOS simulator, but it would be very difficult to debug issues on remote devices in production.
To make better apps in less time by leveraging SQL
I was surprised that SQLite is not included in the React Native standard library seeing as it was designed for devices like phones and is one of the most deployed software libraries in the world.
- A query engine.
- The ability to index, join and filter data that SQL brings to the table (no pun intended).
- ACID properties.
- Atomicity, Consistency, Isolation
- Read/write transactions that can span over many async functions.
- File based persistence.
- Atomicity, Consistency, Isolation
- Cross platform file format.
This is not surprising, as React traditionally runs in the browser, which has neither file system access or the ability to run native code.
The browser JS environment only gives you
JSON.parse(). The SMPI JS client expects only these functions, which means it can run in any JS environment.
Firestore took too much developer and device CPU time to query
Firestore is a document store with a very basic query language.
The issue is that it forces you to choose a single tree-like representation. If your query does not match the structure, the advice from the Firestore team is to download entire collections and write/execute your query in application code over local “in memory” data.
By comparison, I rewrote the same app using SQLite by starting with the design of the normalized schema. It became much easier add features to my application over time as SQLite provides a full query engine over the data.
Its easy to add new queries that were unknown at schema design time.
New queries in SQLite are simple SQL strings. With Firestore they require full table scans of in memory data and writing custom code.
One of the uses of computers is to compute things which would take humans much longer to do with paper. SQL gives you a great foundation to leverage this ability, passing those benefits on to your users in the form of UI and app features.
Note Im not saying that SQLite and Firestore are comparable. Im saying that as part of the app solution I was building, the local only subset of Firestore for React Native are limited compared to directly using SQLite, and this had an effect on feature quality, development speed, and code conciseness.
Principle: Atomic units, composite structures
I think the essence of the usefulness of SQL when building applications is a principle I call “atomic units, composite structures”.
SQL encourages you to store your data as “atomic units” (tabular columns and rows), and map the database to many “composite structures” (tabular result sets) using SQL:
A -> C
By comparison, a document store encourages you to store your data as a tree-like composite structure to begin with. To get to other composite structures your application needs, you first need to break it down into atomic units and then map those to the new composite structure.
C -> A -> C
The SQL engine optimizes in going from
A -> C, where as document stores (and general programming languages) leave this to your application to do which means writing specific non-general code for every query (and spending extra developer and CPU time to get there).
An objection to this may be “but now you need an ORM to map those tabular rows to tree-like documents”. I think this is a good trade off to make in order to use SQL as:
- Its harder to re-create SQL in your application than it is to map tabular rows to tree-like documents.
- You can use multiple SQL queries to construct your tree-like data.
- SQLite has tree-like JSON support.
- Some UI views do not require the full document.
- Some UI views are naturally expressed as tables, not tree structures.
- Basic key value store with no query language.
- Not suitable for persisting large data sets.
- Unclear how to export data from the devices filesystem.
- Its not possible to query the dataset from your development machine during development (E.g. by using a db GUI).
- Firestore seemed like a good fit, but does not have official RN support.
- Its not possible to tell when state has been synced to the remote server.
- It does not have a expressive local query engine.
- Its mainly a remote data store, with limited local features.
- Has the same issues as async storage above.
- The Expo SQLite API seemed to work, but despite having
transactionin their JS API it does not actually start a transaction.
- Other bugs in basic functionality that the Expo team will not fix.
- This was the library I chose before creating SMPI, and did not have any immediate issues, although I did not test thoroughly.
- Although this worked, I wanted to make improvements for the areas defined in what-problem-does-smpi-solve