HN Search

What is client-side SQLite?

It began with a seemingly-simple question: How can I query my database without having to run a database server on my server?

The most straightforward answer to the question is to make clients download the whole database and query it, which becomes impractical as the database grows.

My solution is, using sql.js (SQLite compiled to JavaScript through Emscripten), download the database as the database file is accessed. Laziness is achieved by HTTP Range requests.

Neat, but what else?

I think the real significance of this project is how it reduces the cost of running a service that requires a database. Given the prevalence of free static file hosting services (e.g. GitHub pages, Netlify), the need for renting a server for the database server is eliminated.

Decentralised networks such as IPFS can also benefit from client-side databases for the same reasons stated above. "Client"-side database access fits closer to the peer-to-peer toppology of such networks as opposed to a client-server model of the (traditional) Web.

When is this a bad idea?

While the solution presented here is much more efficient (in terms of data transfer) than pushing the whole database to the clients, it is still significantly less efficient than returning the results of a query only. Though it must be noted that subsequent queries often do not need more to be downloaded so on average it shall compensate.

File system access will still be faster, so if your intended use case is to retrieve rows by their unique identifiers, you might want to consider using a directory structure where rows are represented as files and are identified by the information embedded in their path.

sql.js is still not mature enough to be fully reliable, so its use is discouraged for critical services. You might also encounter problems with large databases (> 1 GiB, approximately) for reasons currently unknown.

Lastly, this offers a read-only access to the database so modifications would not be possible.

How can I use this?

I've already submitted a PR! =) See kripken/sql.js#278. Also feel free to take a peek at the source code of this.

Some Tips

• Bigger page sizes seem to result in faster queries.
• Make sure to optimize your FTS tables and VACUUM your database at the end.
• Content-Encoding: gzip won't work (see), but might want to try setting Transfer-Encoding: gzip (though it seems not as widely used as Content-Encoding so beware compatibility issues.)
• Understand the trade-off well: you are lowering your setup complexity & costs at the expense of increased bandwidth consumption (which affects both you and your visitors/users).

Feel free to contact me if you need further help: bora et boramalper dot org

Who did this?

Bora M. Alper, me, a computer science student in The University of Edinburgh. Developed in Bodrum using the excess sunlight.

Also see my GitHub: https://github.com/boramalper