Forem: Dan Croak

Postgres concat_ws() function

Dan Croak — Fri, 28 Aug 2020 18:30:55 +0000

Postgres provides a concat_ws() string function with this signature:

concat_ws(sep text, str "any" [, str "any" [, ...] ])

Consider a schema where the projects.second_user_id and project.third_user_id columns can be NULL:

              Table "public.projects"
     Column     |            Type        | Nullable |
----------------+------------------------+-----------
 id             | bigint                 | not null |
 name           | character varying(255) | not null |
 lead_user_id   | bigint                 | not null |
 second_user_id | bigint                 |          |
 third_user_id  | bigint                 |          |

                Table "public.users"
     Column     |            Type        | Nullable |
----------------+------------------------+-----------
 id             | bigint                 | not null |
 initials       | character varying(255) | not null |

Consider a query to get the "team" for a project by each user's initials:

SELECT
  projects.name,
  concat_ws(' / ', u1.initials, u2.initials, u3.initials) AS team
FROM
  projects
  LEFT JOIN users u1 ON u1.id = projects.lead_user_id
  LEFT JOIN users u2 ON u2.id = projects.second_user_id
  LEFT JOIN users u3 ON u3.id = projects.third_user_id
GROUP BY
  projects.name,
  team
ORDER BY
  projects.name ASC;

Example output:

     name     |     team    
-------------------+--------------
Private Beta  | AB / CD
SLA           | EF / GH / IJ
Treasury Ops  | KL
(3 rows)

Time: 1 ms

Using concat_ws() instead of concat() avoids ugly output like AB / / CD.

Schedule Netlify Builds with GitHub Actions

Dan Croak — Wed, 05 Aug 2020 19:45:31 +0000

I use a custom static site generator to publish my blog. It automatically deploys to Netlify when I merge new articles into my Git repository's main branch.

To support a "scheduled article" feature, I have configured a GitHub Actions scheduled workflow:

name: daily publish

on:
  schedule:
    - cron: "0 0 * * *" # every day at midnight UTC

jobs:
  build:
    runs-on: ubuntu-latest

    steps:
      - name: Trigger Netlify build
        shell: bash
        env:
          NETLIFY_BUILD_HOOK: ${{ secrets.NETLIFY_BUILD_HOOK }}
        run: curl -X POST -d {} "$NETLIFY_BUILD_HOOK"

Every day at midnight UTC, GitHub runs the workflow, deploying the site using a Netlify build hook. The build hook is a URL which I've stored as a GitHub encrypted secret.

When there are articles whose scheduled date matches the new UTC date, they are automatically published by this workflow.

Postgres \set variable

Dan Croak — Tue, 04 Aug 2020 15:57:02 +0000

Within psql, you can \set variables and reference them with :'var-name'. For example:

\set query '%SaaS%'

SELECT
  'https://example.com/companies/' || companies.id AS url,
  companies.name
FROM
  companies
  JOIN notes ON notes.company_id = companies.id
WHERE
  companies.name ILIKE :'query'
  OR companies.description ILIKE :'query'
  OR notes.comments ILIKE :'query'
GROUP BY
  url,
  companies.name
ORDER BY
  companies.name ASC;

I like to run scripts from Vim through psql with this config.

SSH Key with Ed25519

Dan Croak — Tue, 04 Aug 2020 15:55:28 +0000

With my last team, at the request of Ryan, our CTO, I stopped using RSA for my public keys and started using Ed25519.

Ed25519 uses elliptic curve cryptography with good security and performance. That's the real reason to use it.

My favorite thing, though, is when the whole team uses Ed25519, a server's ~/.ssh/authorized_keys file looks 😍:

ssh-ed25519 AAAAC3NzaC1lZDI1NTE5AAAAIIAePksB5aPc6sww+RMzJwpVuDhRAgzOKP1Q/o3suIbw alice@home.local
ssh-ed25519 AAAAC3NzaC1lZDI1NTE5AAAAIEB/O/VwAvqWIV/EN9aHjHAg/9JYsX/Ce2yvr5wPI3gZ bob@work.local
ssh-ed25519 AAAAC3NzaC1lZDI1NTE5AAAAIAYG1rgF4YSSBwtinbhFLR/Qeah11jYcQpf6lX4yql60 carol@home.local

Create the key:

ssh-keygen -t ed25519 -f ~/.ssh/id_ed25519 -C "$(whoami)@$(hostname)"

Start the SSH agent:

eval "$(ssh-agent -s)"

Update ~/.ssh/config:

Host *
  AddKeysToAgent yes
  UseKeychain yes
  IdentityFile ~/.ssh/id_ed25519

Host github.com
  Hostname ssh.github.com
  Port 443

Add the private key to the SSH agent on macOS:

ssh-add -K ~/.ssh/id_ed25519

Format and run SQL on save in Vim

Dan Croak — Tue, 04 Aug 2020 02:11:03 +0000

Install and configure ALE in ~/.vimrc:

call plug#begin('~/.vim/plugged')
  Plug 'dense-analysis/ale' " :help ale
call plug#end()

In ~/.vim/ftplugin/sql.vim:

" Auto-fix
let b:ale_fixers = ['pgformatter']
let g:ale_fix_on_save = 1
let b:ale_sql_pgformatter_options = '--function-case 1 --keyword-case 2 --spaces 2'

" Run current file
nmap <buffer> <Leader>r :!clear && psql -d $(cat .db) -f %<CR>

In my laptop.sh, I have a variant of the following that is idempotent; it will install or update Homebrew and pgFormatter:

# pgformatter
brew install pgformatter

# Vim plugins
curl -fLo "$HOME/.vim/autoload/plug.vim" --create-dirs \
  https://raw.githubusercontent.com/junegunn/vim-plug/master/plug.vim
vim -u "$HOME/.vimrc" +PlugUpdate +PlugClean! +qa

In each of my projects, I have a .db file that contains only my database name:

example_development

When I save a .sql file in Vim, it auto-formats it with pgformatter using the flags from my ftplugin/sql.vim.

When I run <Leader>r from a .sql file in Vim, it runs the file against my .db Postgres database through psql.

caffeinate -u -d

Dan Croak — Sat, 01 Aug 2020 18:57:44 +0000

My MacBook Pro running Catalina has been crashing when:

attached to an external monitor (an LG UltraFine 5K Display),
in clamshell mode, and
it goes to sleep

This looks like a known bug according to forum posts like this and this.

My workaround for now has been:

manually put the computer to sleep when I step away or
not use the monitor or
when running a long-running process and I want to step away, running caffeinate in a shell to prevent idle sleep:

caffeinate -u -d

Heroku to Slack with AWS Lambda

Dan Croak — Thu, 30 Jul 2020 17:34:54 +0000

When my production app processes change state on Heroku, I want to be notified in Slack:

Other examples:

17:38:52 clock.1 `bundle exec ruby schedule/clock.rb` up
17:39:03 web.1 `bundle exec puma -p $PORT -C ./config/puma.rb` up
17:39:05 web.2 `bundle exec puma -p $PORT -C ./config/puma.rb` up

Pager-notifying events:

17:38:52 queuenote.1 `bundle exec ruby queue/note.rb` crashed

Heroku has webhooks for these events but their payloads aren't in the format needed for Slack incoming webhooks.

AWS Lambda is the perfect glue to transform the Heroku webhook's JSON payload into a useful JSON payload for Slack's incoming webhook.

Slack config

Create an incoming webhook. Copy the URL.

Lambda config

Create a Lambda function. AWS' supported runtimes include Node, Python, Ruby, and Go. You can alternatively implement a custom runtime.

Here's an example in Ruby:

require "json"
require "net/http"
require "time"
require "uri"

def lambda_handler(event:, context:)
  dyno = JSON.parse(event["body"])
  name = dyno["data"]["name"] || ""
  state = dyno["data"]["state"] || ""

  ignored_states = ["starting", "down"].include?(state)
  term_one_off = state == "crashed" && ["scheduler", "run"].any? { |p| name.include?(p) }

  if ignored_states || term_one_off || name.include?("release")
    return {
      statusCode: 200,
      headers: { "Content-Type": "application/json" },
      body: "ok"
    }
  end

  uri = URI.parse(ENV.fetch("SLACK_URL"))
  http = Net::HTTP.new(uri.host, uri.port)
  http.use_ssl = true
  req = Net::HTTP::Post.new(uri.request_uri)
  req["Content-Type"] = "application/json"

  req.body = {
    text: [
      Time.parse(dyno["created_at"]).getlocal("-07:00").strftime('%H:%M:%S'),
      name,
      "`#{dyno["data"]["command"]}`",
      state
    ].compact.join(" ")
  }.to_json

  res = http.request(req)

  return {
    statusCode: 200,
    headers: { "Content-Type": "application/json" },
    body: res.body
  }
end

Paste the Slack incoming webhook URL as an environment variable, which is encrypted at rest.

Create an API Gateway to make the Lambda function accessible in the Heroku web UI.

Heroku config

Go to:

https://dashboard.heroku.com/apps/YOUR-APP-NAME/webhooks

Create a webhook with event type "dyno". Paste the API Gateway URL as the Payload URL.

Modify to taste

Edit and save the code in Lambda's web-based text editor. Trigger a webhook to test the function. View the auto-created CloudWatch logs for each function call.