Week Notes 24#24
16 June 2024 | 8:38 pm

  • A very busy week at work writing + demo'ing the design doc around Renovate, and doing various bits around that and some other work we've got in the planning phase, as well as demoing our hackathon week project (dependency-management-data updater)
  • After the release of oapi-codegen, and the follow-up release with the new module path, I started on making it possible for Renovate to do replacements for Go modules
  • Worked on adding an embedded SQL browser to dmd-web
    • This is exciting for a few reasons, but largely the fact that it can now be a single binary (if you want a slightly reduced feature set) and it has a pretty good fallback functionality
  • Played a lot of Ratchet and Clank: A Rift Apart - including some lunches - and completed both the story mode (~18h in) and then finished an additional challenge mode run through (~33h in) including all but a couple of weapons maxed out
    • I'm planning on writing a lil' review, but not got the brain energy to do it yet
  • Had a very good massage, but may have ended up falling asleep a little bit 🥱💆
  • Heard some pretty chilling screeching one night, as if an animal had got some birds 😅
  • A very chilled weekend, recovering from a busy few weeks, and a brain intensive week
  • My SQLite + Neovim workflow article got shared in HackerNewsletter, so I've had a fair bit more traffic for it
    • Also updated the version of the Asciinema player, after Marcin (the Asciinema maintainer) released a version with support for Neovim + Kitty colour scheme
    • And because it required an upgrade from v2 to v3, which changed from a Web Component, the updates led to a few of my posts being syndicated out to the Fediverse
  • Got approved by Open Collective for oapi-codegen (now we're on our community org) and so updated all the repos with sponsorship details, so it's now clearer how to sponsor
  • Had a Fly.io bill recently which had some additional charges due to bandwidth, and having a little executive function this weekend I looked into it and it turns out I've transmitted 290GB of bandwidth for the dependency-management-data example app 😅 Oof
  • Helped the Deliveroo folks with some dependency-management-data related debugging
  • Had a nice thank you email about one of my articles
  • Had some nice time with Morph in the living room a few times this week 😻
    • And he was very enthralled watching the sliding doors open and close
  • Tried cola bottles jaffa cakes which are pretty nice
  • While talking about making steps towards Principal Engineer at work, I've been re-sharing my "I don't want my next promo (yet)" post
  • Got some 😋 pizza from Asda, but there was some pretty bad rain on the way which luckily didn't stop me
  • The rain has been pretty bad this last week, meaning not going out very much
  • Cookie had fun chasing a fly yesterday
  • Cookie had a couple of good days of daycare this week - Monday, ahead of Anna being at Staff+, and Friday so we could have a chilled long weekend - and has been a very sleepy girl afterwards 🥰
  • Had a nice pedicure with Anna on Friday - complete with a massage chair and nice chatter - although we didn't get into town for lunch due to the weather being so awful
  • Also played some Apex Legends this afternoon
  • Had a nice walk with Carol and Juno at Bunny Old Wood

Watched:

  • How I Met Your Mother
    • The final season (😡)
  • (No The Acolyte)
  • (No Shogun)

Dependency Management Data's web application can now be deployed as a single static binary
16 June 2024 | 1:40 pm

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:

A rendering of the above link, showing the result of the Datasette SQL browser querying a list of repositories and owners, in a rendering on a light background

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

A rendering of the above link, showing the result of the embedded dependency-management-data SQL browser querying a list of repositories and owners, on a dark background

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!


Using Go's `database/sql` to query an arbitrary columns of unknown type(s)
13 June 2024 | 7:41 pm

As part of some work I'm doing on dependency-management-data to add a pure-Go database browser, I want to be able to accept arbitrary SQL and query it.

However, I found that trying to do this with Go's database/sql, this didn't work super nicely with the anys that get returned by database/sql, so we could follow something like this StackOverflow, but there are better things we can do.

Adapting this article instead, we can write code like so, taking advantage of sql.RawBytes to make the conversion easier:

package main

import (
 "database/sql"
 "fmt"
 "log"
 "strings"

 _ "modernc.org/sqlite"
)

func main() {
 sqlDB, err := sql.Open("sqlite", "dmd.db")
 if err != nil {
 log.Fatal(err)
 }

 res, err := sqlDB.Query(`select * from metadata`)
 if err != nil {
 log.Fatal(err)
 }

 rows, err := toRows(res)
 if err != nil {
 log.Fatal(err)
 }

 for _, row := range rows {
 fmt.Println(strings.Join(row, " "))
 }
 fmt.Println()

 res, err = sqlDB.Query(`select 'hi' as greeting, 1 as num_rows, NULL as owner, true as is_valid`)
 if err != nil {
 log.Fatal(err)
 }

 rows, err = toRows(res)
 if err != nil {
 log.Fatal(err)
 }

 for _, row := range rows {
 fmt.Println(strings.Join(row, " "))
 }
}

func toRows(res *sql.Rows) ([][]string, error) {
 var rows [][]string

 cols, err := res.Columns()
 if err != nil {
 return nil, err
 }

 rows = append(rows, cols)

 for res.Next() {
 items := make([]any, len(cols))
 for i := range items {
 // http://go-database-sql.org/varcols.html
 items[i] = new(sql.RawBytes)
 }
 if err := res.Scan(items...); err != nil {
 return nil, err
 }

 row := make([]string, len(cols))
 for i, v := range items {
 if sb, ok := v.(*sql.RawBytes); ok {
 row[i] = string(*sb)
 }
 }
 rows = append(rows, row)
 }

 return rows, nil
}

This gives the output of the following (which I've slightly modified for readability):

name value
dmd_version v0.94.3-next
compatible_since v0.94.0
greeting num_rows owner is_valid
hi 1 1
hi 1 1

We alternatively could simplify this to use anys, which seems less nice:

package main

import (
 "database/sql"
 "fmt"
 "log"
 "strings"

 _ "modernc.org/sqlite"
)

func main() {
 sqlDB, err := sql.Open("sqlite", ":memory:")
 if err != nil {
 log.Fatal(err)
 }

 res, err := sqlDB.Query(`select 'hi' as greeting, 1 as num_rows, NULL as owner, true as is_valid`)
 if err != nil {
 log.Fatal(err)
 }

 rows, err := toRows(res)
 if err != nil {
 log.Fatal(err)
 }

 for _, row := range rows {
 fmt.Println(strings.Join(row, " "))
 }
}

func toRows(res *sql.Rows) ([][]string, error) {
 var rows [][]string

 cols, err := res.Columns()
 if err != nil {
 return nil, err
 }

 rows = append(rows, cols)

 for res.Next() {
 items := make([]any, len(cols))
 for i := range items {
 // http://go-database-sql.org/varcols.html
 items[i] = new(any)
 }
 if err := res.Scan(items...); err != nil {
 return nil, err
 }

 row := make([]string, len(cols))
 for i, v := range items {
 vv := v.(*any)
 row[i] = fmt.Sprintf("%v", *vv)
 }
 rows = append(rows, row)
 }

 return rows, nil
}

This gives the output of the following (which I've slightly modified for readability):


greeting num_rows owner is_valid
hi 1 <nil> 1


More News from this Feed See Full Web Site