Category: SQLite

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