Dependency Management Data's web application can now be deployed as a single static binary

Since the first iteration of the dependency-management-data web application, I've offloaded the experience of writing and querying SQL querying to the excellent Datesette tool.

I'm a huge fan of Datasette, and recommend it as a great web-based SQLite database browser for querying and exploring your SQLite databases.

However, it makes deployments of the dmd-web CLI a little more difficult, as dmd-web is a static Go binary, but to run the Datasette application we need the Python runtime.

For organisations looking to reduce the CVE attack surface as well as simplifying their deployment runtime, this can make the dmd-web application a little less appealing.

As of Friday's v0.97.0 release, it's now possible to omit the dependency on Datasette, and instead use an embedded database browser.

This means that for a query such as:

select
  distinct
  renovate.platform,
  renovate.organisation,
  renovate.repo,
  owner
from
  renovate left join owners
    on  renovate.platform = owners.platform
    and renovate.organisation = owners.organisation
    and renovate.repo = owners.repo
;

We can then compare what this looks like using Datasette:

And then the same query using dmd-web's embedded database browser:

Note that the embedded web browser for dmd-web isn't as fully featured as Datasette, which is an active design decision. The expectation is that dmd-web's embedded SQL browser is meant to be used for known queries, but for exploratory use-cases, it's still recommended to use Datasette.

In the future, the embedded browser will be made more user friendly to give a setup similar to my preferred local workflow, providing autocompletion for the database's fields.

If you're already using dmd-web and disable the Datasette functionality, you'll land on fallback pages on dmd-web, which will handle queries, but not browsing to tables.

Finally, the embedded browser follows the same model as Datasette by embedding query information in the URL to make it easier to share queries with others, but unlike Datasette, dmd-web uses base64 URL encodes the query parameter, which allows avoiding issues with i.e. Cloudflare and other protection systems that try to avoid SQL injection, even though this is an explicit feature 😅 And if you provide an unencoded format i.e. ?sql=select..., dmd-web will still respond to that, too.

I'm looking to continue investing in the embedded query browser, and would be interested to hear feedback!