Category: SQLite

I reread Robin Sloan’s post about a “home-cooked” app, and it continues to resonate; there’s something special about writing software for very small audiences. In that vein, here’s something that my partner and I have been using since last year:

We needed a solution for shared notes that works well across Android, iOS, and the (desktop) web… so I made one. We use it a lot, mostly for things like our grocery list and cooking notes. It’s simple, fast, and it works for us.

Technology-wise, it’s just a website that works well on desktop and mobile. The back-end is written in Rust, it uses HTMX for some dynamic updates, and the note contents get saved to a SQLite database (backed up to S3 with Litestream). The whole thing compiles down to a single-file executable that gets run on a cheap VPS.

Many corners were cut during the development of this project! Proper collaborative text editing is hard, and I took a quick-and-dirty approach. Eventually I’ll get it working better with Automerge or whatever, but for now I have something good enough for a user base of 2.

And now it’s really easy to do stuff with our shared notes:

That’s a cheap 7" e-ink display with a Raspberry Pi on the back, showing a customized view of our grocery list plus enough information to answer the question “do I need an umbrella today?”. It’s like a little household dashboard that we can take a quick look at as we head out the door. Eventually I need to make a frame for it and mount it on the wall, but it’s pretty handy as-is.

Why do all this?

Shared notes are a solved problem, I’m sure I could have found an existing service for this. But:

  • I like having a project to tinker on for someone I care for, in the same way that I like cooking for them
  • This is permanent in a way that cloud services are not. It will never change unless we want it to, and it will will work for decades without much effort. We might be using this in a retirement home one day
  • It’s trivial to tweak and extend software I wrote myself. I didn’t need to figure out an API to get the the e-ink display, I just built a new HTML view on top of existing data
  • Software designed for an audience of 2 is able to be much simpler than software that anticipates the needs of a large+diverse user base

Bonus: Burninator.exe

Now that I’ve shown you something useful, here’s some much sillier home-cooked software:

A friend asked:

What is a program that I can run in the background to raise the temperature of a laptop? If my laptop gets too cold the screen starts to flicker…

I wasn’t aware of any, so I wrote one that queries WMI for the current CPU temperature and then does busy work until the temperature is high enough. Is it dumb? Yes. Does it solve this one person’s very specific problem? Also yes!

Recent Nushell/Rust Work

SQLite, file watcher, windows-rs

I’ve joined the Nushell core team. This doesn’t really change what I’m doing day-to-day, but it makes my work on Nu feel a little more official 🙂.

SQLite Support

This is the biggest feature I’ve implemented so far:

I’m pretty proud of how this turned out; it’s very convenient to be able to browse SQLite databases in your shell and interact with them the same way you would any other data source. Nu is often-but-not-always smart enough to avoid unnecessary work when loading things from the database; there’s still some work to do here and it will probably involve rearchitecting how Nushell queries data.

File watcher

I also implemented a watch command that runs arbitrary Nu code in response to file changes. Nothing groundbreaking, but I find myself needing this kind of low-key automation all the time: run tests when code changes, restart a development server, log changes in a directory, etc. I think the ability to respond to file changes should be a more widely available primitive, and now it is.

Rust for Windows

Against all odds, I somehow got sucked back into Windows development. I spent a solid week helping one of Nushell’s dependencies do a big upgrade of their Windows functionality. This required a deep dive into the current state of calling Windows APIs from Rust, and… it’s a mixed bag.

I used the windows crate which is maintained by Microsoft. It’s an automatically generated set of Rust bindings for Windows APIs, which is both good (very comprehensive, always kept up to date) and bad (some rough edges that might be solved in a handmade solution like winapi). The crate is actively being worked on and it frequently has breaking changes; this means documentation is a little scarce and often out of date. Overall I was impressed and I think the crate has a bright future . But until it settles down a bit, expect some growing pains.

I’m officially unemployed again 😎. In the interest of self-accountability, I’m going to try to document what I’m up to on my break; expect more frequent updates to this blog.

HYTRADBOI

I bought a ticket to Have You Tried Rubbing A Database On It?, which could loosely be described as a hipster database conference; lots of people using databases in unusual ways, not much in the way of enterprise RDBMSs. The speaker list is like a Who’s Who for offbeat database work, and I’m really looking forward to it.

Nushell

I’ve been using Nushell as my shell on both Windows and Linux, about half the time. Nushell is a fascinating project; it’s a shell that operates on structured data like PowerShell, but without PowerShell’s (many) pain points.

Nushell has recently seen some massive upgrades (the parsing and evaluation engine was completely rewritten) and it’s a very good time to give it a try. It’s still early days, but I’m hopeful Nushell will be able to displace POSIX shells; it’s liberating to work with much richer data types than plain text:

Nu is a way of saying “what if we didn’t need tools like awk so often?” Since you’re working with structured data, as we add more support for file types, it’s less often you need to reach for “awk”, “jq”, “grep”, and the array of other tools to open and work with common file types. In a way, it’s taking the original spirit of Unix — where you use pipelines to combine a set of tools — and imagining how that original spirit would work today, with what we know about programming languages and tools.

Building data-centric apps with a reactive relational database

This essay touches on a lot of my favourite things: SQLite! The intersection of native apps and web UI! iTunes clones! In a nutshell, it’s a very cool approach to building GUI applications in which all of the application’s state lives in a local database.

It’s more of a provocation than a fully finished system, but I think it shows promise. I’d like to see a bit more investigation of “escape hatches”; how hard would it be mix in a little imperative code when SQL/SQLite aren’t the right fit for a task? Also, this was a bit depressing:

One challenge has been inter-process communication. When the reactive graph is running in the UI thread and the SQLite database is on a web worker or native process, each query results in an asynchronous call that has to serialize and deserialize data.

This would have been a non-issue in traditional GUI code (just query SQLite on a background thread in the same process); one more thing we lose as web UI takes over, I guess 😞.

Databases as file formats

The .mbtiles way

I’m building an interactive online map of all properties in Vancouver, and along the way there have been a few pleasant surprises. Most recently: the .MBTiles tileset format is surprisingly cool.

Background

Mapbox is one of the biggest players in the open source mapping space (especially now that Mapzen and Carto have thrown in the towel – Mapzen is closing and Carto is now using Mapbox tech). One of the many nice things about Mapbox is that they developed an efficient open standard for vector map tiles, appropriately named Mapbox Vector Tiles (read this if you’re not sure why vector tiles are great).

Map tiles are often pre-computed for each zoom level, and once you’ve done that you need to store them somewhere. Enter the .MBTiles tileset format.

Poking around under the hood

My first encounter with this file format occurred when I used Eric Fischer’s excellent tippecanoe tool to simplify my data set at lower zoom levels. Tippecanoe generates .mbtiles files, which are easy to serve to clients either by uploading to Mapbox, using a third party tile server, or even by rolling your own server with something like the mbtiles Node.js package.

All great… but after setting up a server my Mapbox GL JS client refused to render the tiles. I tried a few things without much luck, and then as a last resort I decided to poke around in the .mbtiles file. I was expecting to need a hex editor or similar, but then I saw this beauty in the spec:

MBTiles is a specification for storing tiled map data in SQLite databases

The files themselves are just relational databases in a known schema – how cool is that? Emboldened, I grabbed a SQLite client and opened up my .mbtiles file:

headshot

Cities & Code

Top Categories

View all categories