Why did you make SMPI?

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.

The JSON1, RTree and FTS extensions are useful to use from RN JS code as the foundation for app features.

The newer versions of JavaScript are like a JSON DSL, which makes it concise and fast for iterating over JSON structures and prototyping UI’s.

But JavaScript by itself (and by extension React based state management) is missing:

  • 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.
    • Durability
      • File based persistence.
  • 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 fetch() and 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.

App features with Firestore required thinking through both the initial schema of the data and how I would write every query for it; schema and native Firestore queries are tightly coupled. Most queries required me to write JavaScript to join and filter the data, which is costly in both human time and CPU time as there are no indexes.

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.

Solutions tried

AsyncStorage

https://facebook.github.io/react-native/docs/asyncstorage

  • 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).

Firebase Firestore

https://rnfirebase.io/docs/v5.x.x/firestore/reference/firestore

  • 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.

SQLite: Expo

https://docs.expo.io/versions/latest/sdk/sqlite/

  • The Expo SQLite API seemed to work, but despite having transaction in their JS API it does not actually start a transaction.
  • Other bugs in basic functionality that the Expo team will not fix.

SQLite: andpor/react-native-sqlite-storage

https://github.com/andpor/react-native-sqlite-storage

  • 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