This week, Richard Hipp returns to catch us up on all things SQLite, his single file webserver written in C called Althttpd, and Fossil – the source code manager he wrote and uses to manage SQLite development instead of Git.
Where by “on the web” James means “in the browser”:
SQL is a great way to build apps. Especially small local web apps. Key/value stores may have their place in large distributed systems, but wow wouldn’t it be great if we could use SQLite on the web?
I’m excited to announce absurd-sql which makes this possible. absurd-sql is a filesystem backend for sql.js that allows SQLite to read/write from IndexedDB in small blocks, just like it would a disk. I ported my app to use and you can try it here.
A very cool project that was inspired by phiresky’s Hosting SQLite databases on Github Pages.
A proof-of-concept inspired and enabled by Hosting SQLite Databases on GitHub Pages and the ensuing Hacker News post. The compiled single-page app supports autocomplete for titles, automatic redirecting & other MediaWiki datasets like WikiQuote or Chinese Wikipedia. It makes no external API calls except to get Wikipedia’s images.
2MB of CSS, JS, and WASM. One 43GB SQLite file.
DenoDB has a fully-typed API (which is great for editor integration) and supports a whole host of backends: MySQL/Maria, SQLite, Postgres, and MongoDB.
Broad database support is great for library adoption, but as a user I’d prefer something that leans in to a specific ecosystem, which usually lets you squeeze more out of it.
Regardless of that, it’s great to see the Deno community building foundational tools like this.
Althttpd is a simple webserver that has run the sqlite.org website since 2004. Althttpd strives for simplicity, security, and low resource usage.
As of 2018, the althttpd instance for sqlite.org answers about 500,000 HTTP requests per day (about 5 or 6 per second) delivering about 50GB of content per day (about 4.6 megabits/second) on a $40/month Linode. The load average on this machine normally stays around 0.1 or 0.2. About 19% of the HTTP requests are CGI to various Fossil source-code repositories.
Richard has a knack for creating simple, high quality tools. When we did our (now legendary) show with him back in 2016, he was quite keen on coming back at some point to discuss Fossil. Should we make that happen?
This is a project in Go that compiles to a SQLite runtime loadable extension, which brings Redis commands into a SQL context. This allows you to write SQL queries against data in a Redis instance, using Redis commands like
LRANGEas SQL functions.
Experimental for now. But why? Patrick says:
In general, Redis is fairly accessible from many programming languages, and any query using reqlite could probably be implemented in a language of your choice using a Redis client. However, sometimes declarative SQL can be a better choice to express what you’re looking for, and that’s where this project may be most useful.
The benefits of such a setup are numerous, especially for small sites and side projects:
Hosting a static website is much easier than a “real” server - there’s many free and reliable options (like GitHub, GitLab Pages, Netlify, etc), and it scales to basically infinity without any effort.
The how is also super interesting:
So how do you use a database on a static file hoster? Firstly, SQLite (written in C) is compiled to WebAssembly. SQLite can be compiled with emscripten without any modifications, and the sql.js library is a thin JS wrapper around the wasm code.
There’s more to the story, and the resulting solution is also open source.
Even if you start out small and later need to upscale, as long as your web application can run on the same machine as the database, which it can in 99% of the time, you can just upgrade the hardware to a beefier machine and keep business as usual.
The only time you need to consider a client-server setup is…
Anton Zhiyanov lays out why he thinks SQLite is awesome:
- SQLite is the most common DBMS in the world, shipped with all popular operating systems.
- SQLite is serverless.
- For developers, SQLite is embedded directly into the app.
- For everyone else, there is a convenient database console (REPL), provided as a single file (sqlite3.exe on Windows, sqlite3 on Linux / macOS).
This echoes Ben Johnson’s sentiments in our recent conversation with him about Litestream. If you consider SQLite as merely a good databse for things that don’t matter all that much, maybe it’s time to reconsider…
This week we’re talking with Ben Johnson. Ben is known for his work on BoltDB, his work in open source, and as a freelance Go developer. Late January when Ben open sourced his newest project Litestream in the readme he shared how the project was open source, but not open for contribution. His reason was to protect his mental health and the long term viability of the project. On this episode we talk with Ben about what that means, his thoughts on mental health and burnout in open source, choosing a license, and the details behind Litestream - a standalone streaming replication tool for SQLite.
helps you to quickly provision WordPress with SQLite and serve the site using PHP’s builtin webserver. No external WebServer like Apache or Nginx and Database Server like MySQL or MariaDB is required. WPSQLite can give you a completely portable installation of WordPress which you can install even in your pendrive and run on *nix based operating systems, or even on Windows.
This looks like a great option for getting a WP dev environment bootstrapped without much hassle. I didn’t even know you could run WordPress on SQLite…
There’s a lot of information that you are ignoring from your VCS. SQL is a great way to access it.
gitqliteis a tool for running SQL queries on git repositories. It implements SQLite virtual tables and uses go-git. It’s meant for ad-hoc querying of git repositories on disk through a common interface (SQL), as an alternative to patching together various shell commands.
Mine your repo’s history for goodies. Here’s how to get commit count by author email:
SELECT author_email, count(*) FROM commits GROUP BY author_email ORDER BY count(*) DESC
This is like JS Fiddle, but for SQL. Pick from MySQL, Postgres, or SQLite. Then load up some queries, run them, and share with others. Super cool 👍
LGTM, but why?
Mostly because I wanted to dig deeper into node web server code, but also because I haven’t jumped onto the NoSQL bandwagon and think that web APIs are extremely useful. The result is a modest attempt at automating the CRUD boilerplate that every developer hates, while following the specs to make API consumption intuitive. I chose sqlite to keep the database side of things simple, with the intent that the API isn’t serving heavy loads.
How does a database work? What format is data saved in? How are indexes formatted? When and how does a full table scan happen? Join Connor Stack on his journey to answer these questions and more…
I’m building a clone of SQLite from scratch in C in order to understand, and I’m going to document my process as I go.
An alternative command-line client for SQLite. Provides intuitive auto-completion against table names and columns. It’s written in Python, so you can get it with a
pip install litecli
Imagine being able to have many connections to the same database, each one reading a separate branch or commit at the same time. Or even writing to separate branches.
This is a good idea and a testament to the versatility of SQLite. Click through to read how it works and how they’ve managed to 2x the performance of SQLite on Linux and macOS.
This episode is part of our remastered greatest hits collection and features Richard Hipp, the creator of SQLite, talking with us about its history, where it came from, why it has succeeded as a database, how its development has been sustainably funded, and the how and why of it being the most widely deployed database engine in the world.
Induction is an ambitious new project from Matt Thompson to explore, query, and visualize data from SQL and NoSQL sources including PostgreSQL, MySQL, SQLite, Redis, and MongoDB. While in early alpha, the project shows promise.