I’m a big fan of Pulumi, and I’ve previously used it for cloud infrastructure. That’s their main selling point; Pulumi’s slogan is literally “Program the cloud.” Interestingly, Pulumi can also be used to manage on-premise infrastructure, including PostgreSQL databases. Let’s dive into the details.

Pulumi basics

Pulumi takes a declarative infrastructure definition and handles provisioning said infrastructure, just like Terraform, AWS CloudFormation, and Azure Resource Manager. The main difference is that in Pulumi you’re writing real code (Node, Python, or Go) to build that infrastructure definition instead of a YAML or JSON file, it’s great.

Pulumi/Terraform PostgreSQL Provider

After Pulumi has executed your code to build up an infrastructure definition, it needs to interact with external infrastructure resources (cloud providers, databases, etc.) to turn that definition into a reality. Some of Pulumi’s functionality for working with external resources is derived from Terraform providers. For example, Pulumi has a PostgreSQL provider which is derived from the Terraform PostgreSQL provider.

At a high level, the PostgreSQL provider lets you define databases, roles, schemas, permissions, and PostgreSQL extensions. It stops short of letting you manage DDL objects and data within the database (which is probably for the best given the complexities of schema+data migrations).

Defining a Postgres database with TypeScript

Pulumi can be used to instantiate entire Postgres clusters, but for this tutorial let’s assume you already have an existing cluster managed outside of Pulumi. It can be running anywhere: in the cloud, in a container, on your local machine, whatever.

First, install Pulumi then instantiate a new TypeScript project with pulumi new typescript. This scaffolds a bare-bones Node+TypeScript Pulumi project with the following files:

index.ts                package-lock.json       tsconfig.json
Pulumi.yaml             node_modules            package.json

Install the Pulumi PostgreSQL provider with npm i @pulumi/postgresql (or just edit your package.json directly).

Next, we need to tell Pulumi which Postgres cluster to connect to and how. The PostgreSQL provider’s configuration points are documented here. My Postgres cluster is running on a local VM with the hostname fedora-vm, so I set the postgresql:host variable like so:

pulumi config set postgresql:host fedora-vm

This creates a YAML configuration file for the current Pulumi stack, or modifies one if it already exists. For my dev stack, this creates a file named Pulumi.dev.yaml with the following contents:

config:
  postgresql:host: fedora-vm

You’ll want to do the same for postgresql:username and postgresql:password, to tell Pulumi which credentials to use. Sensitive configuration values like passwords can be encrypted using the --secret flag.

Finally, we’re ready to work with the fun stuff: configuring a database in real TypeScript code. Let’s say we want to instantiate a Pulumi-managed database, create a role with login permissions, and grant the role SELECT permission on tables in the public schema in the new database. This can all be done in the index.ts file like so:

import * as pulumi from "@pulumi/pulumi";
import * as postgresql from "@pulumi/postgresql";

const config = new pulumi.Config();

const managedDatabase = new postgresql.Database("managedDatabase", {
  name: "pulumi"
});

const publicReaderRole = new postgresql.Role("publicReaderRole", {
  login: true,
  name: "public_reader",
  password: config.require("publicReaderPassword")
});

const publicReaderSelectTablesGrant = new postgresql.Grant(
  "publicReaderSelectTablesGrant",
  {
    database: managedDatabase.name,
    objectType: "table",
    privileges: ["SELECT"],
    role: publicReaderRole.name,
    schema: "public"
  }
);

I spent a few weeks in August rewriting this website, and as promised here are the deets. The source code is available here under the MIT license.

Background

When I initially put this website together in January 2018, I used Julio Pescador’s Hugo port of the Future Imperfect theme. I’d heard good things about Hugo, I wanted to write blog posts in Markdown, and it was a good-looking theme that required minimal additional setup. It served me well for over a year, but a few things kept bothering me:

1. The styling was very difficult to modify – it was nearly 3000 lines of CSS in a single file, with many duplicated colours and styles.
2. The theme relied on a lot of JavaScript libraries: jQuery, highlight.js, Fancybox, Skel…
   1. My simple static website was serving up hundreds of kilobytes of largely unnecessary scripts. As someone who grew up using a dialup modem, this offended me.

My goal was to rewrite the Hugo theme for extensibility and performance, and I figured it would take maybe a week. Of course, it took about 3 times that.

I’ve been building web UIs in Angular and React for the last few years, and I’ve started to greatly prefer React. Extracting and using UI components is just easier and, for lack of a better word, more JavaScripty.

Extracting components in React

Say I notice that I’m creating multiple <span> elements with the same class and icon:

function Main() {
  return (
    <div>
      <span class="alert-tag"><i class="fa alert"></i>foo</span>
      <span class="alert-tag"><i class="fa alert"></i>bar</span>
      <span class="alert-tag"><i class="fa alert"></i>baz</span>
    </div>
  );
}

It’s trivial to refactor this repeated element into its own <Alert> component function within the same file:

function Main() {
  return (
    <div>
      <Alert>foo</Alert>
      <Alert>bar</Alert>
      <Alert>baz</Alert>
    </div>
  );
}

function Alert({ children }) {
  return (<span class="alert-tag"><i class="fa alert"></i>{children}</span>);
}

Super easy, uses JavaScript’s native language constructs, and I was able to do it all within the same file (but I can easily move Alert() elsewhere for wider re-use if needed). It’s just like extracting a function in a “regular” programming language, it’s something you do without even thinking about it.

Here’s something I’ve found useful in SQL Server, but it should apply in any DBMS with checksum functions. Next time you’re refactoring some database objects and you want to do some regression testing, give the CHECKSUM (MSDN) and CHECKSUM_AGG (MSDN) functions a try.

CHECKSUM and CHECKSUM_AGG

They behave pretty much as you would expect; CHECKSUM returns 1 checksum given 1 row, and CHECKSUM_AGG is an aggregate function that returns 1 checksum given multiple checksum rows. Between the two of them, you can get a checksum for any arbitrary collection of data:

-- Returns 2 rows with 2 columns
SELECT Field1, Field2
FROM (VALUES ('foo','bar'),('baz','foo')) TempTable(Field1,Field2)

-- Returns 2 rows with 1 checksum column
SELECT CHECKSUM(Field1,Field2)
FROM (VALUES ('foo','bar'),('baz','foo')) TempTable(Field1,Field2)
 
-- Returns 1 row with 1 checksum column
SELECT CHECKSUM_AGG(CHECKSUM(Field1,Field2))
FROM (VALUES ('foo','bar'),('baz','foo')) TempTable(Field1,Field2)

Regression testing

These help with a common problem: when you’re refactoring a database object, how can you be sure that you haven’t changed the existing functionality? Sometimes it’s enough to spot check a few inputs, but often the logic is complex enough that this does not provide a high level of confidence. With CHECKSUM and CHECKSUM_AGG, you can quickly check large numbers of test cases.

For example, say we have a TVF called MyDateTVF that takes in a date parameter and returns columns Column1 and Column2. I want to refactor this function and then test that the functionality is unchanged for every date in 2018. If we have a table called DateList which contains every date (more useful than you might expect in a DB with a lot of temporal aspects), I can simply run the following before and after my change:

SELECT CHECKSUM_AGG(CHECKSUM(MDT.Column1, MDT.Column2))
FROM DateList DL
CROSS APPLY (
    SELECT Column1, Column2 from dbo.MyDateTVF(DL.Date)
) MDT
WHERE DL.Date BETWEEN '2018-01-01' AND '2018-12-31'

If that query returns the same checksum before and after my change, we can rule out any regressions for those inputs. Nice and easy!

I recently overhauled the UI for my letter builder web app, switching from Bootstrap to a neat framework named Tailwind CSS. It’s been great so far.

I dabble in web development, but it’s not “my thing”. Most of my time is spent on back-end systems and the occasional native UI. When I’m building a web UI, I usually spend a lot of time on MDN or W3Schools looking up syntax details.

Tailwind

Enter Tailwind. It’s a utility-first CSS framework, which in practice means they give you a whole bunch of “utility” classes which are effectively aliases for common styles. Here’s an example from when I was styling a button:

<button class="bg-blue-dark text-sm text-white rounded py-2 px-4 my-2"/>

With these classes, I’m saying that I want a dark blue background, small white text, 2 units of padding on the top and bottom (py-2), 4 units of padding on the left and right (px-4), 2 units of margin on the top and bottom (my-2), and rounded corners. Whew!

Right away, we can notice a few advantages relative to the normal way of doing things:

1. This is fast to iterate on. I can add another utility class in-line without digging through my CSS file and reasoning about selectors.
2. The class names are concise yet informative. py-2 is clearly operating on the Y axis (top and bottom), which is much easier to remember than a padding style with multiple unlabeled values.
3. I have fewer choices to make. I didn’t need to worry about whether to use #0033CC or #0000FF for a dark blue, I just asked for dark blue. Same thing with padding+margins, I just chose from a small set of integers instead of wondering whether to do 0.5rem or 0.6rem.

Isn’t putting all your styles inline just asking for trouble?

Yes, this can get out of hand if you put too much stying inline – but Tailwind utility classes can be extracted into components as soon as you need some additional abstraction. The authors even recommend that:

Tailwind encourages a “utility-first” workflow, where new designs are initially implemented using only utility classes to avoid premature abstraction.

While we strongly believe you can get a lot further with just utilities than you might initially expect, we don’t believe that a dogmatic utility-only approach is the best way to write CSS.

Curation VS organic growth

I’m being a little hard on the “usual” way of writing styles in CSS. The web grew more-or-less organically, so it’s not fair to expect an overall design from the mess of styles available to modern browsers. Thankfully, that’s where projects like Tailwind come in.

It’s remarkable how much nicer it is to build web UIs when working with a thoughtfully curated and documented subset of styles. Sometimes a big step forward doesn’t need to come from a clever technical breakthrough – simply organizing existing information+symbols in a better way can reap massive benefits.