SQLite Icon

SQLite

The most used database engine in the world.
42 Stories
All Topics

SQLite unixsheikh.com

SQLite the only database you will ever need in most cases

Anybody following our news and/or podcast feeds for a bit already know of SQLite and have likely used it and continue to use it, but for those just joining us, here’s yet another dev with effusive praise for the little database engine that could:

SQLite is one of those projects that I wish I had known about long before I did. I had heard about it, but for many years I never thought about taking a serious look at it because I was under the false impression that is was a tiny database only useful for personal address books and small embedded devices.

I highly recommend you take a look at SQLite!

SQLite sqlite.org

An experimental high-concurrency backend for SQLite

This project is from the SQLite team themselves. Their reasoning for it:

SQLite is sometimes used as the core of a client/server database system. While it works reliably well in such cases, the database backend module that it uses to store b-tree structures in its database file was not designed with this case in mind and can be improved upon in several ways. The HC-tree (hctree) project is an attempt to develop a new database backend that improves upon regular SQLite as follows…

It improves concurrency, adds support for replication & removes database size limitations.

Tooling jeremiak.com

Datasette is my data hammer

Jeremia Kimelman:

Datasette is an open source tool that takes an SQLite database and gives you an out-of-the-box, web-based UI built specifically for exploring data. Need an example? Here’s a database of all of Motley Fool’s earning transcripts that I used to look for talk of their California campaign activity. And here’s a bunch of other examples of Datasette from the official site.

And the thing is: I love Datasette. It recently turned 5 years old and I wanted to write down the thing that makes it an absolutely delightful data hammer.

SQLite observablehq.com

A framework for building SQLite extensions in Rust

sqlite-loadable-rs is a new framework for writing fast, performant, and mostly-safe SQLite extensions in Rust. These extensions are nearly as fast those written in C, with the added benefit of Rust’s memory safety features, easy access to thousands of 3rd party crates and packages on crates.io, all while maintaining a splendid developer experience.

SQLite github.com

The ultimate set of SQLlite extensions

SQLite has few functions compared to other database management systems. SQLite authors see this as a feature rather than a problem, because SQLite has an extension mechanism in place.

There are a lot of SQLite extensions out there, but they are incomplete, inconsistent and scattered across the internet. sqlean brings them together, neatly packaged into domain modules, documented, tested, and built for Linux, Windows and macOS.

Think of it like an extended standard library for SQLite.

SQLite observablehq.com

A SQLite extension for making HTTP requests

You can think of this like fetch() or curl but entirely in SQL:

select request_url, response_status, response_headers 
  from http_get('http://httpbin.org/get');

The cool thing is you can save everything from the request: status code, headers, the body (of course), timestamps, and more. Great for archiving!

-- initialize a table
create table snapshots as
  select * from http_get('https://changelog.com');

-- To add more rows later on
insert into snapshots
  select * from http_get('https://changelog.com');

Ben Johnson github.com

A distributed SQLite replication system

Ben Johnson is at it again.

LiteFS is a FUSE-based file system for replicating SQLite databases across a cluster of machines. It works as a passthrough file system that intercepts writes to SQLite databases in order to detect transaction boundaries and record changes on a per-transction level in LTX files.

I believe Ben was alluding to this (then unreleased) tool on his recent Ship It! appearance

PostgreSQL twilio.com

SQLite or PostgreSQL? It's complicated!

Miguel Grinberg built a SQLite-backed web app for the blogging team at Twilio to track their effectiveness that started to respond slugishly when it grew much bigger than he expected (6.5 million individual daily traffic records, and with a user base that grew to over 200 employees).

He thought this might be a good time to switch to Postgres, but he wasn’t sure if the wins he expected would be realized:

Having publicly professed my dislike of performance benchmarks, I resisted the urge to look up any comparisons online, and instead embarked on a series of experiments to accurately measure the performance of these two databases for the specific use cases of this application.

In this in-depth article on Twilio’s blog (no doubt being tracked by Miguel’s web app as we speak) he goes into the details of his efforts with lots of interesting findings along the way (even a plot twist!). I’ll leave you with this statement from the end:

If you are going to take one thing away from this article, I hope it is that the only benchmarks that are valuable are those that run on your own platform, with your own stack, with your own data, and with your own software. And even then, you may need to add custom optimizations to get the best performance.

Bash til.simonwillison.net

One-liner for running queries against CSV files with SQLite

Simon Willison figured out how to run a SQL query directly against a CSV file using the sqlite3 CLI:

sqlite3 :memory: -cmd '.mode csv' -cmd '.import taxi.csv taxi' \
  'SELECT passenger_count, COUNT(*), AVG(total_amount) FROM taxi GROUP BY passenger_count'

Ie TL;DR’d the one-liner for ya, but you’ll have to go to Simon’s site for the explainer.

SQLite github.com

Web crawl data as SQLite databases

Many organizations such as Commoncrawl, WebRecorder, Archive.org and libraries around the world, use the warc format to archive and store web data.

The full datasets of these services range in the few pebibytes(PiB), making them impractical to query using non-distributed systems.

This project aims to make subsets such data easier to access and query using SQL.

Crawl a site with wget and import it into WarcDB:

wget --warc-file changelog "https://changelog.com"

warcdb import archive.warcdb changelog.warc.gz

Then you can query away using SQL, such as this one to get all response headers:

sqlite3 archive.warcdb <<SQL
select  json_extract(h.value, '$.header') as header, 
        json_extract(h.value, '$.value') as value
from response,
     json_each(http_headers) h
SQL

Jerod Santo changelog.com/posts

SQLite's web renaissance

I won’t call SQLite’s current moment a comeback, because the most used database engine in the world doesn’t have anything to come back from. I’m going with “renaissance”, because despite its already mass adoption, there has been something of a rebirth of interest from one software sector that had previously relegated it to dev & test environments: web apps

WebAssembly sqlite.org

Run the latest SQLite beta directly in your web browser

Richard Hipp, announcing SQLite version 3.39.0 in their forums:

If you prefer, you can experiment with the latest 3.39.0 code directly in your web-browser and without having to download or install anything by visiting sqlite.org/fiddle/. (The page name is by analogy to sites like “JSFiddle” and “SQLFiddle”.)

The page runs a WASM-compiled copy of a recent 3.39.0 build in a sandbox in your web browser. Using controls on that page, you can import database files from your local machine (assuming they aren’t too big), work with them, then export any changes back to your desktop.

WebAssembly FTW!

Ben Johnson github.com

A Postgres wire compatible SQLite proxy

Hot off the press from our friend Ben Johnson:

Postlite is a network proxy to allow access to remote SQLite databases over the Postgres wire protocol. This allows GUI tools to be used on remote SQLite databases which can make administration easier.

The proxy works by translating Postgres frontend wire messages into SQLite transactions and converting results back into Postgres response wire messages. Many Postgres clients also inspect the pg_catalog to determine system information so Postlite mirrors this catalog by using an attached in-memory database with virtual tables. The proxy also performs minor rewriting on these system queries to convert them to usable SQLite syntax.

It appears that Ben is taking code contributions this time around, but only for bug & documentation fixes.

SQLite sqlite.org

How SQLite is tested

This hit my radar a few times in the past year – this week, and then most recently a few months back when we had Richard Hipp on The Changelog (again) – but, I didn’t post it to the newsfeed.

Here’s what’s interesting about the SQLite test suite – it’s their secret sauce…the sustaining enablement of building a support business around SQLite. Here’s a direct quote from Richard Hipp in that episode.

Originally we thought we were gonna sell this and make money from it, and that’s how we were gonna support ongoing development. That didn’t really play out, nobody ever bought it. It does sort of become our business value, our intellectual property. I mean, you can take the SQLite code and fork it and start your own thing…but you don’t have the full test suite. You’ve got a lot of tests, but not all of them. So we’ve got a little bit of advantage over you there.

Click here to play that episode from this quote.

curl daniel.haxx.se

The most used software components in the world

Curl maintainer Daniel Stenberg:

We can’t know for sure which products are on the top list of the most widely deployed software components. There’s no method for us to count or estimate these numbers with a decent degree of certainty. We can only guess and make rough estimates – and it also depends on exactly what we count. And quite probably also depending on who‘s doing the counting.

He goes through his process of trying to determine contenders, and ends up with zlib, qlite, and libcurl. Other have added OpenSSL, expat and the Linux kernel to that list.

James Long jlongster.com

A future for SQL on the web

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.

SQLite static.wiki

Wikipedia using only static assets & no backend

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.

Player art
  0:00 / 0:00