Add a function to get a SQLite database that is shared

[KallynGowdy]

A major limitation in the system is the inability to support common database features like filtering, sorting, and joining. One of the major roadblocks to this end is the difficulty in securely isolating databases (Untrusted SQL might be able to exploit the database system). In order to solve it, we would effectively have to implement a hypervisor. For this reason, we haven't made full server-side databases available to users.

One idea that came up recently is the idea of utilizing SQLite (or some other DB) to allow people to be able to run the database processing logic on their local machine. This would mean that query planning and execution is run on the user's browser (or webhook), while the data can be permanently stored and shared via the records system. Generally, database systems are designed to allow a single writer and multiple readers. Reads can execute concurrently, but writes must execute synchronously. SQLite is designed like this: only one process at a time can write to the database but multiple processes can read from it at the same time. However, because multiple-process writing is so common, SQLite implements mechanisms that enforce synchronization of writes.

When we break the database out into a user's browser, now we have a problem with how we communicate the writes back to the data store. Usually, SQLite takes care of this process, but we can't take advantage of SQLite's powerful filesystem capabilities since we don't have a filesystem that is able to synchronize and ensure consistency. So, we have to go with an alternate synchronization mechanism.

This is where cr-sqlite comes into the picture. cr-sqlite is a SQLite extension that adds CRDT capabilities. This means that we can allow users to execute writes on their own system on their own database while still supporting synchronization!

Lots of testing is still needed, since this technology is fairly new and potentially unreliable, but it is a very interesting idea since it gives us lots of benefits and minimizes many of the downsides.

Here's a basic pro/con list:

Traditional Server-Hosted DB

Pros

• Large database support
• Fully synchronized
• Most any DB vendor
• Build size

Cons

• Untrusted SQL
• Cost
• More integration work
• No offline support

SQLite CRDT DB

Pros

• Runs on user's device
• Easier to integrate
• Cost
• Offline support

Cons

• Copies entire database to user
• Only SQLite
• Build size

PouchDB

Pros

• Runs on user's device
• Offline support
• Build size

Cons

• Limited query support
• Database controls authentication
• Requires running extra CouchDB instance(s)