<story><title>Cloud Backed SQLite</title><url>https://sqlite.org/cloudsqlite/doc/trunk/www/index.wiki</url></story><parent_chain></parent_chain><comment><author>noman-land</author><text>I&amp;#x27;ve just been exploring serving large SQLite databases in chunks and querying them with http range requests to prevent downloading the entire database. It&amp;#x27;s pretty awesome!&lt;p&gt;I found a really interesting library called sql.js-httpvfs[0] that does pretty much all the work. I chunked up my 350Mb sqlite db into 43 x 8Mb pieces with the included script and uploaded them with my static files to GitHub, which gets deployed via GitHub Pages.[1]&lt;p&gt;It&amp;#x27;s in the very rough early stages but you can check it out here.&lt;p&gt;&lt;a href=&quot;https:&amp;#x2F;&amp;#x2F;transcript.fish&quot; rel=&quot;nofollow noreferrer&quot;&gt;https:&amp;#x2F;&amp;#x2F;transcript.fish&lt;/a&gt;&lt;p&gt;I recommend going into the console and network tab to see it in action. It&amp;#x27;s impressively quick and I haven&amp;#x27;t even fine-tuned it at all yet. SQLite rules.&lt;p&gt;[0] &lt;a href=&quot;https:&amp;#x2F;&amp;#x2F;github.com&amp;#x2F;phiresky&amp;#x2F;sql.js-httpvfs&quot;&gt;https:&amp;#x2F;&amp;#x2F;github.com&amp;#x2F;phiresky&amp;#x2F;sql.js-httpvfs&lt;/a&gt;&lt;p&gt;[1] &lt;a href=&quot;https:&amp;#x2F;&amp;#x2F;github.com&amp;#x2F;noman-land&amp;#x2F;transcript.fish&quot;&gt;https:&amp;#x2F;&amp;#x2F;github.com&amp;#x2F;noman-land&amp;#x2F;transcript.fish&lt;/a&gt;</text></comment>
<story><title>Cloud Backed SQLite</title><url>https://sqlite.org/cloudsqlite/doc/trunk/www/index.wiki</url></story><parent_chain></parent_chain><comment><author>0xbadcafebee</author><text>This adds more cache consistency issues, concurrency issues, network blocking issues, authn+z issues, daemon process supervision issues, ... for what? To store your SQL data in a remote data store? I would rather my app shell out to gsutil (or curl!) than deal with all of this.&lt;p&gt;Simple hack: mount a tmpfs filesystem and write your sqlite database there. Every 30 seconds, stop writing, make a copy of the old database to a new file, start writing to the new file, fork a process to copy the database to the object store and delete the old database file when it&amp;#x27;s done. Add a routine during every 30 second check to look for stale files&amp;#x2F;forked processes.&lt;p&gt;Why use that &amp;quot;crazy hack&amp;quot;, versus the impeccably programmed Cloud Backed SQLite solution?&lt;p&gt;- Easier to troubleshoot. The components involved are all loosely-coupled, well tested, highly stable, simple operations. Every step has a well known set of operations and failure modes that can be easily established by a relatively unskilled technician.&lt;p&gt;- File contents are kept in memory, where copies are cheap and fast.&lt;p&gt;- No daemon outside of the program to maintain&lt;p&gt;- Simple global locking semantics for the file copy, independent of the application&lt;p&gt;- Thread-safe&lt;p&gt;- No network blocking of the application&lt;p&gt;- Authentication is... well, whatever you want, but your application doesn&amp;#x27;t have to handle it, an external application can.&lt;p&gt;- It&amp;#x27;s (mostly) independent of your application, requiring less custom coding, allowing you to focus more on your app and less on the bizarre semantics of directly dealing with writes to a networked block object store.</text></comment>