DEV Community: Rob Conery

Postgres For Those Who Can’t Even, Part 2 – Working with Node and JSON

Rob Conery — Wed, 05 Feb 2020 21:06:32 +0000

This is part 2 of a series of posts I’m doing for a friend who’s a JavaScript developer that, according to him, knows next to nothing about Postgres. You can read part 1 right here.

I write a lot about Postgres, but I don’t think I’ve written enough about how to get started from the absolute beginning, so that’s what we’re doing here.

In this post, I’m continuing with his questions to me – but this time it has less to do with the database side of things and more to do with Node and how you can use Postgres for fun and profit. Let’s roll.

How should I structure my code?

This question has more to do with your preferences or what your company/boss have set up. I can show you how I do things, but your situation is probably a lot different.

OK, enough prevaricating. Here’s what I’ve done in the past with super simple projects that where I’m just musing around.

Give PG It’s Own Module

I like putting all my code inside of a lib directory, and then inside there I’ll create a a pg directory with specific connection things etc for Postgres. It looks like this:

You’ll also notice I have a .env file, which is something that goes into every single project of mine. It’s a file that holds environmental variables that I’ll be using in my project. In this case, I do not want my connection string hardcoded anywhere – so I pop it into a .env file where it’s loaded automatically by my shell (zshell and, for those interested, I use the dotenv plugin with Oh-My-Zsh).

There’s a single file inside of the lib/pg directory called runner.js, and it has one job: run the raw SQL queries using pg-promise:

const pgp = require('pg-promise')({});
const db = pgp(process.env.DATABASE_URL);

exports.query = async function(sql, args){
  const res = await db.any(sql, args);
  return res;
}
exports.one = async function(sql, args){
  const res = await db.oneOrNone(sql, args);
  return res;
}
exports.execute = async function(sql, args){
  const res = await db.none(sql, args);
  return res;
}
exports.close = async function(){
  await db.$pool.end();
  return true;
}

I usually have 3 flavors of query runners:

One that will return 0 to n records
One that will return a single record
One that executes a “passthrough” query that doesn’t return a result

I also like to have one that closes the connections down. Normally you wouldn’t call this in your code because the driver (which is pg-promise in this case) manages this for you and you want to be sure you draw on its pool of connections – don’t spin your own. That said, sometimes you might want to run a script or two, maybe some integration tests might hit the DB – either way a graceful shutdown is nice to have.

We can use this code in the rest of our app:

const pg = require("./lib/pg/runner");

pg.query("select * from master_plan limit 10")
  .then(console.log)
  .catch(console.error)
  .finally(pg.close)

Neat! It works well but yes, we’ll end up with SQL all over our code so let’s fix that.

A Little Bit of Abstraction

The nice thing about Node is that your modules can be single files, or you can expand them to be quite complex – without breaking the code that depends on them. I don’t want my app code to think about the SQL that needs to be written – I’d rather just offer a method that gives the data I want. In that case, I’ll create an index.js file for my pg module, which returns a single method for my query called masterPlan:

const runner = require("./runner");
exports.masterPlan = function(limit=10){
  return runner.query(`select * from master_plan limit ${limit}`)
}
exports.shutDown = function(){
  runner.close();
}

The runner here is the same runner that I used before, this time it’s in the same directory as the calling code. I’ve exposed two methods on the index as that’s all I need for right now. This is kind of like a Repository Pattern, which comes with a few warnings attached.

People have been arguing about data access for decades. What patterns to use, how those patterns fit into the larger app you’re building, etc, etc, etc. It’s really annoying.

Applications always start small and then grow. That’s where the issues come in. The Repository Pattern looks nice and seems wonderful until you find yourself writing Orders.getByCustomer and Customer.getOrders, wondering if this is really what you wanted to do with your life.

This is a rabbit hole I don’t want to go down further so, I’ll kindly suggest that if you have a simple app with 10-20 total queries, this level of control and simplicity of approach might work really well. If your app will grow (which I’m sure it will whether you think so or not), it’s probably a good idea to use some kind of library or relational mapper (ORM), which I’ll get to in just a minute.

How do I put JSON in it?

One of the fun things about Node is that you can work with JSON everywhere. It’s fun, I think, to not worry about data types, migrations, and relational theory when you’re trying to get your app off the ground.

The neat thing about Postgres is that it supports this and it’s blazing fast. Let’s see how you can set this up with Postgres.

Saving a JSONB Document

Postgres has native support for binary JSON using a datatype called “JSONB”. It behaves just like JSON but you can’t have duplicate keys. It’s also super fast because you can index it in a variety of ways.

Since we’re going to store our data in a JSONB field, we can create a “meta” table in Postgres that will hold that data. All we need is a primary key, a timestamp and the field to hold the JSON:

create table my_document_table(
  id serial primary key,
  doc jsonb not null,
  created_at timestamp not null default now()
);

We can now save data to it using a query like this:

insert into my_document_table(doc)
values('{"name":"Burke Holland"}');

And yuck. Why would anyone want to do something like this? Writing delimited JSON by hand is gross, let’s be good programmers and wrap this in a function:

const runner = require("./runner");
//in pg/index.js
exports.saveDocument = async function(doc){
  const sql = "insert into my_document_table (doc) values ($1)";
  const res = await runner.one(sql, [doc]);
  return res;
}

This works really well, primarily because our Node driver (pg-promise) understands how to translate JavaScript objects into something Postgres can deal with. We just pass that in as an argument.

But we can do better than this, don’t you think?

Sprinkling Some Magical Abstraction

One of the cool things about using a NoSQL system is that you can create a document table on the fly. We can do that easily with Postgres but we just need to tweak our saveDocument function a bit. In fact we need to tweak a lot of things.

Let’s be good programmers and create a brand new file called jsonb.js inside our pg directory, right next to our runner.js file. The first thing we’ll do is to create a way to save any document and, if we get an error about a table not existing, we’ll create it on the fly!

exports.save = async function(tableName, doc){
  const sql = `insert into ${tableName} (doc) values ($1) returning *`;
  try{
    const newDoc = await runner.one(sql, [doc]);
    doc.id = newDoc.id;
    return doc;
  }catch(err){
    if(err.message.indexOf("does not exist") > 0){
      //create the table on the fly
      await this.createDocTable(tableName);
      return this.save(tableName,doc);
    }
  }
}

exports.createDocTable = async function(tableName){
  await runner.query(`
    create table ${tableName}(
    id serial primary key,
    doc jsonb not null,
    created_at timestamp not null default now()
  )`);
  await runner.query(`
    create index idx_json_${tableName} 
    on ${tableName} 
    USING GIN (doc jsonb_path_ops)
  `);
}

We have two groovy functions that we can use to save a document to Postgres with the sweetness of a typical NoSQL, friction-free experience. A few things to note about this code:

We’re catching a specific error when a table doesn’t exist in the database. There’s probably a better way to do that, so feel free to play around. If there’s an error, we’re creating the table and then calling the save function one more time.
The createDocTable function also pops an index on the table which uses jsonb_path_ops. That argument tells Postgres to index every key in the document. This might not be what you want, but indexing is a good thing for smaller documents.
We’re using a fun clause at the end of our insert SQL statement, specifically returning * which will return the entire, newly-created record, which we can then pass on to our calling code.

Let’s see if it works!

//index.js of our project
docs.save("customers", {name: "Mavis", email: "mavis@test.com"})
  .then(console.log)
  .catch(console.err)
  .finally(pg.shutDown);

Well look at that would ya! It works a treat.

But what about updates and deletes? Deleting a document is a simple SQL statement:

exports.delete = async function(id) {
  const sql = `delete from ${tableName} where id=$1`;
  await runner.execute(sql, [id]);
  return true;
};

You can decide what to return from here if you want, I’m just returning true. Updating is a different matter, however.

Updating an existing JSONB document

One of the problems with JSONB and Postgres in the past (< 9.5) was that in order to update a document you had to wholesale update it – a “partial” update wasn’t possible. With Postgres 9.5 that changed with the jsonb_set method, which requires a key and a JSONB element.

So, if we wanted to change Mavis’s email address, we could use this SQL statement:

update customers 
set doc = jsonb_set(doc, '{"email"}', '"mavis@example.com"')
where id = 1;

That syntax is weird, don’t you think? I do. It’s just not very intuitive as you need to pass an “array literal” to define the key and a string value as the new value.

To me it’s simpler to just concatenate a new value and do a wholesale save. It’s nice to know that a partial update is possible if you need it, but overall I’ve never had a problem just running a complete update like this:

exports.modify = async function(tableName, id = 0, update = {}) {
  if (!tableName) return;
  const sql = `update customers SET
              doc = (doc || $1) 
              where id = $2 returning *; `;
  const res = await runner.one(sql, [update, id]);
  return res;
};

The || operator that you see there is the JSONB concatenation operator which will update an existing key in a document or add one if it’s not there. Give it a shot! See if it updates as you expect.

Querying a JSONB document by ID

This is the nice thing about using a relational system like Postgres: querying by id is just a simple SQL statement. Let’s create a new function for our jsonb module called get, which will return a document by ID:

exports.get = async function(tableName, id=0){
  const sql = `select * from ${tableName} where id=$1`;
  const record = await runner.one(sql, [id]);
  const doc = record.doc;
  doc.id = record.id;
  return doc;
}

Simple enough! You’ll notice that i’m adding the id of the row in Postgres to the document itself. I could drop that into the document itself, if I wanted, but it’s simple enough to tack it on as you see. In fact, I think I’d like to ensure the created_at timestamp is on too, so let’s formalize this with some transformations:

const transformRecord = function(record){
  if(record){
    const doc = record.doc;
    doc.createdAt = record.created_at;
    doc.id = record.id;
    return doc;
  }else{
    return null;
  }
}
const transformSet = function(res){
  if(res === null || res === []) return res;
  const out = [];
  for(let record of res){
    const doc = transformRecord(record);
    out.push(doc)
  }
  return out;
}

This will take the raw record from Postgres and turn it into something a bit more usable.

Querying a document using criteria

We can pull data out of our database using an id, but we need another way to query if we’re going to use this properly.

You can query documents in Postgres using a special operator: @>. There are other operators, but this is the one we’ll need for 1) querying specific keys and 2) making sure we use an index. There are all kinds of operators and functions for JSONB within Postgres and you can read more about them here.

To query a document for a given key, you can do something like this:

select * from customers
where doc @> '{"name":"Burke Holland"}';

This query is simply for documents where the key/value {name: "Burke Holland"} exists. That critieria is simply JSON, which means we can pass that right through to our driver… and behold:

exports.find = async function(tableName, criteria){
  const sql = `select * from ${tableName} where doc @> $1`;
  const record = await runner.query(sql, [criteria]);
  return transformSet(record);
}

Let’s run this and see if it works:

docs.find("customers", {email: "mavis@test.com"})
  .then(console.log)
  .catch(console.err)
  .finally(pg.shutDown);

Hey that’s pretty swell! You don’t need to use dedicated JSON operators to query a JSONB document in Postgres. If you’re comfortable with SQL, you can just execute a regular old query and it works just fine:

select * from customers
where (doc ->> 'name') ilike 'Mav%'

Here, we’re pulling the name key from the document using the JSON text selector (->>), and then doing a fuzzy comparison using ilike (case-insensitive comparison). This works pretty well but it can’t use the index we setup and that might make your DBA mad.

That doesn’t mean you can’t index it – you can!

create index idx_customer_name 
on customers((doc ->> 'name'));

Works just like any other index!

Play around, have some fun…

I made a gist out of all of this if you want to goof around. There are things to add, like updates/partial updates, and I encourage you to play and have a good time.

If you’re wondering, however, if someone, somewhere, might have baked this stuff into a toolset… indeed! They did…

Are there any ORM-like tools in it? What do you recommend?

So here’s the thing: if you’re coming to this post from a Java/C#/Enterprise-y background, the “ORM” tools in the Node world are going to look … well a bit different. I don’t know the reason why and I could pontificate about Node in the enterprise or how Node’s moduling system pushes the idea of isolation… but… let’s just skip all of that OK?

The bottom line is this: you can do data access with Node, but if you’re looking for an industrial strength thing to rival Entity Framework you might be dissapointed. With that said – let’s have a look…

My favorite: MassiveJS

I am 100% completely biased when it comes to MassiveJS because… well I created it along with my friend Karl Seguin back in 2011 or so. The idea was to build a simple data access tool that would help you avoid writing too much SQL. It morphed into something much, much fun.

With version 2 I devoted Massive to Postgres completely and was joined by the current owner of the project, Dian Fay. I can’t say enough good things about Dian – she’s amazing at every level and has turned this little project into something quite amazing. Devoting Massive 100% to Postgres freed us up to do all kinds of cool things – including one of the things I love most: document storage.

The code you read above was inspired by the work we did with JSONB and Massive. You can have a fully-functioning document storage solution that kicks MongoDB in the face in terms of speed, fuzzy searches, full-text indexing, ACID guarantees and a whole lot more. Massive gives you the same, simple document API and frictionless experience you get with Mongo with a much better database engine underneath.

To work with Massive, you create an instance of your database which reads in all of your tables and then allows you to query them as if they were properties (the examples below are taken from the documentation):

const massive = require('massive');

const db = await massive({
  host: 'localhost',
  port: 5432,
  database: 'appdb',
  user: 'appuser',
  password: 'apppwd',
  ssl: false,
  poolSize: 10
});

//save will update or insert based on the presence of an
//ID field
let test = await db.tests.save({
  version: 1,
  name: 'homepage'
});

// retrieve active tests 21-30
const tests = await db.tests.find({is_active: true}, {
  offset: 20,
  limit: 10
});

Working with documents looks much the same as the relational stuff above, but it’s stored as JSON:

const report = await db.saveDoc('reports', {
  title: 'Week 12 Throughput',
  lines: [{
    name: '1 East',
    numbers: [5, 4, 6, 6, 4]
  }, {
    name: '2 East',
    numbers: [4, 4, 4, 3, 7]
  }]
});

Finally, the thing I love most about the project is what Dian has done with the documentation (linked above). She goes into detail about every aspect of the tool – even how to use it with popular web frameworks.

Sequelize

One of the more popular data access tools – let’s call it a full on ORM – is Sequelize. This tool is a traditional ORM in every sense in that it allows you create classes and save them to multiple different storage engines, including Postgres, MySQL/MariaDB SQLite and SQL Server. It’s kind of not an ORM though because there is no mapping (the "M") that you can do aside from a direct 1:1, ActiveRecord style. For that, you can project what you need using map and I’ll just leave that discussion right there.

If you’ve used ActiveRecord (Rails or the pattern itself) before then you’ll probably feel really comfortable with Sequelize. I used it once on a project and found its use straightforward and simple to understand. Getting started was also straightforward, as with any ORM, and the only question is how well an ActiveRecord pattern fits your project’s needs both now and into the future. That’s for you to decide and this is where I hit the architectural eject button again (even though I did once before which didn’t seem to work).

Let’s have a look at some of the examples that come from the documentation.

Connecting is straightforward:

const Sequelize = require('sequelize');
const sequelize = new Sequelize('postgres://user:pass@example.com:5432/dbname');

Declaring a “model” in Sequelize is matter of creating a class and extending from Sequelize.Model or using a built-in definition method. I prefer the latter:

const User = sequelize.define('user', {
  // attributes
  firstName: {
    type: Sequelize.STRING,
    allowNull: false
  },
  lastName: {
    type: Sequelize.STRING
    // allowNull defaults to true
  }
}, {
  // options
});

Sequelize is capable of using this model definition and generating, or "sychronizing" your database just like Django’s ORM does. That’s really helpful in the early days of your project or if you just hate migrations as much as I do.

Sequelize is an outstanding data tool that allows you to work with your database in a seamless way. It has powerful queries and can handle some pretty intense filtering:

Project.findOne({
  where: {
    name: 'a project',
    [Op.not]: [
      { id: [1,2,3] },
      { array: { [Op.contains]: [3,4,5] } }
    ]
  }
});

If you’ve worked with Rails and ActiveRecord Sequelize should feel familiar when it comes to associations, hooks and scopes:

class User extends Model { }
User.init({
  name: Sequelize.STRING,
  email: Sequelize.STRING
},
  {
    hooks: {
      beforeValidate: (user, options) => {
        user.mood = 'happy';
      },
      afterValidate: (user, options) => {
        user.username = 'Toni';
      }
    },
  sequelize,
  modelName: 'user'
});
class Project extends Model { }
Project.init({name: Sequelize.STRING}, {
  scopes: {
    deleted: {
      where: {
        deleted: true
      }
    },
    sequelize,
    modelName: 'project'
  }
});
User.hasOne(Project);

And there you have it. The documentation for Sequelize is very complete as well, with examples and SQL translations so you know what query will be produced for every call.

But what about…?

There are so many tools out there that can help you with Node and data access and I’m sure I’ve left a few off, so feel free to add your favorite in the comments. Please be sure it works with Postgres AND please be sure to indicate why you like it!

Postgres is neat and all but how do I deploy my database?

Great question! That will have to be a topic for Part 3, unfortunately as this post is quite long and I have a lot of ideas. We’ll go simple and low fidelity with a simple docker container push, and then look at some of the hosted, industrial strength solutions out there – including Azure’s Managed Postgres offering!

PostgreSQL For Those Who Can’t Even, Part 1

Rob Conery — Fri, 24 Jan 2020 19:03:50 +0000

Just yesterday I was talking to a friend about Postgres (not uncommon) and he said something that I found shocking:

I can’t even with Postgres, I know JACK SQUAT

This person calls themself my friend too! I just don’t even know what’s real anymore.

So, Friendo is a Node person who enjoys using a document database. Can’t blame him – it’s easy to setup, easy to run and you don’t need to stress out about SQL and relational theory. That said, there are benefits to wrapping structure and rules around your data – it is the lifeblood of your business after all.

If you’re like Friendo and you want to start from the very beginning with Postgres, read on! I’ll use his questions to me for the rest of this post. He has a lot of questions, so I’m going to break this up into parts:

Part 1 (this post) is for people who’ve never thought about a database before, let alone set one up and run a query
Part 2 (next post) will be for Node people wondering what/why/how they could work with Postgres

I encourage you to play along if you’re curious. If you’re having fun and want to do more, I wrote a really fun book about Postgres and the data from the Cassini mission (which you’ll see below) that you’re welcome to check out too!

Where is Postgres? How do I get it and run it?

The easiest possible thing you can do is to run a docker image, which you can do by executing:

docker run -p 5432:5432 postgres:12.1

That will download and run a Postgres image, exposing the default Postgres port of 5432.

If you’re not a Docker person and are on a Mac, you can also head over to postgresapp.com where you can download a free executable app.

How do I manage it with a tool?

Tooling for Postgres is both abundant and wanting. There is no clear cut answer to this question other than to offer the following options for a given context.

Just playing around: Mac If you’re on a Mac go get yourself a free copy of Postico. It’s easy and you can quickly connect and start playing.

Just playing around: Windows (and Mac)

There’s the free Azure Data Studio which uses the same interface as VS Code. There are extensions and all kinds of goodies you can download if you want as well.

To hook up to Postgres, make sure you grab the Postgres extension. You can install it right from the IDE by clicking on the square thingies in the bottom left of the left-most pane.

Something substantial and you’re willing to pay for it (Windows and Mac)My go-to tool for working with Postgres is Navicat. It’s a bit on the spendy side but you can do all kinds of cool things, including reports, charting, import/export, data modeling and more. I love this thing.

Don’t know what to choose? Just download Azure Data Studio and let’s get to work!

Our first login Let’s connect to our new shiny Postgres server. Open up Azure Data Studio and make sure you have the Postgres extension installed. You’ll know if you do because you’ll see the option to connect to PostgreSQL in the connection dialog:

The server name is “localhost” and the Docker image comes with the login preset – “postgres” as the user name and “postgres” as the password.

We’ll go with the default database and, finally, name our connection “Local Docker”. Click “Connect” and you’re good to go.

Our first database Most GUI tools have some way of creating a database right through the UI. Azure Data Studio doesn’t (for Postgres at least) but that’s OK, we’ll create one for ourselves.

If you’ve connected already, you might be wondering “what, exactly, am I connected to”? Good question Friendo! You’re connected to the default database, “postgres”:

This is the admin playground, where you can do DBA stuff and feel rad. We’re going to use our connection to this database to create another one, where we’re going to drop some data. To do that, we need to write a new query. Click that button that says “New Query”:

In the new query window add the following:

create database cassini;

Now hit “F5” to run the query. You should see a success message like so:

If you see a syntax error, check your SQL code and make sure there are no errors. You’ll also notice that nothing changed in the left information pane – there’s no “cassini” database! What gives!

Ease up Friendo! Just right click on the “Databases” folder and refresh – you should see your new database. Once you see, double-click it and in we go!

Our first table Our database is going to hold some fun information from the Cassini Mission, the probe that we sent to Saturn back in 1997. All of the data generated by the project is public domain, and it’s pretty fun to use that data rather then some silly blog posts don’t ya think?

There’s a whole lot of data you can download, but let’s keep things reasonable and go with the “Master Plan” – the dates, times and descriptions of everything Cassini did during it’s 20 year mission to Saturn. I trimmed it just a bit to bring the file size down, so if you want to play along you can download the CSV from here.

We’ll load this gorgeous data in just one second. We have to create a table for it first! Let’s do that now by opening a new query window in Azure Data Explorer (which I hope you remember how to do). Make sure you’re connected to the “cassini” database, and then enter the following SQL:

create table master_plan(
  date text,
  team text,
  target text,
  title text,
  description text
);

This command will, as you might be able to guess, create a table called “master_plan”. A few things to note:

Postgres likes things in lower case and will do it for you unless you force it to do otherwise, which we won’t.
We don’t have a primary key defined, this is intentional and you’ll see why in a second.
There are a number of ways to store strings in Postgres, but the simplest is text, without a length description. This is counterintuitive for people coming from other databases who think this will take up space. It won’t, Postgres is much smarter than that.
Why are we storing a field called “date” as text? For a very good reason which I’ll go over in just a minute.

OK, run this and we should have a table. Let’s load some data!

How do I load data into it?

We’re going to load data directly from a CSV, which Postgres can do using the COPY command. For this to work properly, however, we need to be sure of a few things:

We need to have the absolute path to the CSV file.
The structure of the file needs to match the structure of our table.
The data types need to match, in terms of format, the data types of our table.

That last bit is the toughest part. CSV (and spreadsheets in general) tend to be a minefield of poorly chewed data-droppings, mostly because spreadsheet programs suck at enforcing data rules.

We have two ways to get around this: suffer the pain and correct the data when we import it or make sure all the import columns in our database table are text. The latter is the easiest because correcting the data using database queries tends to be easier than editing a CSV file, so that’s what we’ll do. Also: it’s a good idea not to edit the source of an import.

Right – let’s get to it! If you’re running Docker you’ll need to copy the master_plan CSV file into your running container. I put my file in my home directory on my host. If you’ve done the same, you can use this command to copy the file into your container:

docker cp ~/master_plan.csv [CONTAINER ID]:master_plan.csv

Once it’s there, you can execute the COPY command to push data into the master_plan table:

COPY master_plan
FROM '/master_plan.csv'
WITH DELIMITER ',' HEADER CSV;

This command will grab the CSV file from our container’s root directory (as that’s where we copied it) and pop the data in positionally into our table. We just have to be sure that the columns align, which they do!

The last line specifies our delimiter (which is a comma) and that there are column headers. The final bit tells Postgres this is a CSV file.

Let’s make sure the data is there and looks right. Right-click on the table and select “Select top 1000 rows” and you should see something like this:

Yay data! Before we do anything else, let’s add a primary key so I don’t freak out:

alter table master_plan
add id serial primary key;

Great! Now we’re ready to connect from Node.

How do I connect to it from Node?

Let’s keep this as simple as possible, for now. Start by creating a directory for the code we’re about to write and then initializing a Node project. Feel free to use Yarn or NPM or whatever!

Open up a terminal and:

mkdir pg_demo
cd pg_demo
npm init -y
npm install pg-promise
touch index.js

These commands should work in Powershell on Windows just fine.

We’ll be using the promise-based Postgres driver from Vitaly Tomalev called pg-promise, one of my favorites. The default Node driver for Postgres works with standard callbacks, and we want promises! There are also a few enhancements that Vitaly thew in which are quite nice, but I’ll leave that for you to explore.

The first step is to require the library and connect:

const pgp = require('pg-promise')({});
const db = pgp("postgres://postgres:postgres@localhost/cassini");

I’m connecting to Postgres using a URL-based connection string that has the format:

postgres://user:password@server/db_name

Since we’re using Docker, our default username and password is “postgres”. You can, of course, change that as needed.

Once we’ve set up the connection, let’s execute a query using some very simple SQL:

const query = async () => {
  const res = await db.any("select * from master_plan limit 10");
  return res;
}

Because pg-promise is promise-based, I can use the async and await keywords to run a simple query. db.any will return a list of results and all I need to do is to pass in a SQL string, as you see i did. I made sure to limit the results to 10 because I don’t want all 60,000 records bounding back at me.

To execute the query, I call the method and handle the returned promise. I’ll pop the result out to the console:

query().then(res => {
  console.log(res)
})
.catch(err => {
  console.error(err)
})
.finally(() => {
  db.$pool.end()
})

The last line in the finally block closes off the default connection pool, which isn’t required but the Node process won’t terminate unless you do (you’ll have to ctrl-c to stop it otherwise).

You can run the file using node index.js from the terminal, and you should see something like this:

Glorious data! Notice it all comes back in lovely, formatted JSON, just as we like.

There’s a lot more we can do, but this post is already quite long and I think Friendo might have a few more questions for me. I’ll see if he does and I’ll follow up next time!

Fine Tuning Full Text Search with PostgreSQL 12

Rob Conery — Tue, 29 Oct 2019 21:17:30 +0000

I’ve written about Full Text Indexing in PostgreSQL before but I was a bit more focused on speed and general use. Today I want to focus on something a lot more useful: _ relevance _.

If you want to play along and have some fun, the SQL for what I’m about to do can be downloaded from here (11K zipped SQL file).

Make Those Results Meaningful!

The data I’m working with is from NDC Sydney and is a list of speakers, their talks, keywords and the time/day of the talk. Simple stuff, but it does present an interesting question:

How would you implement a full text index for this body of data?

Turning the speaker’s name, the title and the keywords into a blob of text and then indexing it will work, but it’s simply not enough if we expect the results to actually mean something to our users. This is where things get complicated – which also means they get FUN so strap yourself in, let’s get TEXTUAL.

What Are You Looking For Anyway?

There’s no way we can do this without fully understanding what our users want out of our search functionality, so let’s come up with some scenarios:

Jane has been to 5 conferences already this year and just wants to know what’s new with DevOps and Azure. She takes out her phone and, while walking, enters the words as they come to her mind: “ devops azure “
This is Kunjan’s first conference and he doesn’t know where to start – all he knows is that Heather Downing is speaking and he really wants to be sure he can see her talks so he searches exactly on that: “ Heather Downing”.
Nenne is excited about Blazor and knows the dev team is here, showing it off. She can’t remember their names – just the project name – so she searches on that: “ blazor “.

The Problems

We have three difference kinds of searches here:

The first is contextual, which means that Jane knows the topics she’s interested in and wants to throw a list of words at our search, hoping for a ranked match.
The second is specific, Kunjan wants to see a specific speaker’s talk – that means we need to be sure that we can return a hit on exact part of a first or last name.
Finally, Nenne’s query is relative, which means she knows a term (the project name) and wants to see results relative to it.

If we’re to show these people something meaningful we’ll need to come up with a strategy for building our full text index. Thankfully, Postgres has the tools we need.

Let’s take a quick second to (quickly) understand what goes on behind the scenes as our full text index is being created – it’s really helpful when trying to debug things. Then we’ll move on and create solutions for each of these problems.

Behind the Scenes

A full text index is actually a data type in Postgres called tsvector. It’s a weird name, but what it does is pretty simple:

select to_tsvector('english', 'nothing too tricky here');
     to_tsvector     
---------------------
 'noth':1 'tricki':3
(1 row)

I’m using Postgres’s built-in to_tsvector function to tokenize my string of words into lexemes. What’s a “lexeme” you ask? Hey, good question!

A lexeme is a unit of lexical meaning that underlies a set of words that are related through inflection. It is a basic abstract unit of meaning, a unit of morphological analysis in linguistics that roughly corresponds to a set of forms taken by a single root word.

Wikipedia

You can apply various stems to a lexeme to create a set of different words. So noth in this case could be stemmed to “nothing” or “nothingness”. The integers that you see in the results above are the position within the text body. The first word is “nothing” so we have a 1 and tricky is the third word. This comes in handy later on when we want to know positional information (which we will!).

Finally, you’ll notice that too and here have been stripped. These are “stop words” (or noise words) and aren’t indexed.

But how does all of this tokenization happen?

Postgres ships with a number of dictionaries that parse a given blob of text. If you want to raise the hood on this, you can run the ts_parse function to see what happens:

select * from ts_parse('default', 'nothing too tricky here');

 tokid | token  
-------+---------
     1 | nothing
    12 |  
     1 | too
    12 |  
     1 | tricky
    12 |  
     1 | here
(7 rows)

The first argument to this function is the search configuration, which I’m setting to default as I don’t want to break anything. What I get back is a list of tokens and their id. 1, for instance, is an ascii word and 12 is blank space.

You can see a lot more information if you use the ts_debug function, which is designed to help you if you’re fiddling with the search config stuff:

I think this is interesting, but it’s also academic for our needs. Let’s get back on track and setup our search index.

Task 1: No Stems for Names!

Before we index anything, we need to consider what the thing is and also what it is not. A little vague, but let’s start with names.

Names are specific. While one could make the argument that some names might be more common in a given language, I think we can agree that’s problematic. In that sense, tokenizing a name as if its English words doesn’t make sense.

Heather’s last name is “Downing”, which could refer to what she might do to a glass of cold water after a long run or what she did to enemy planes during the war. Neither of those is the case, yet that’s exactly how the tokenizer will treat her name.

That’s how full text queries work in Postgres: matching lexemes. The to_tsquery function you see here simply tokenized the term given to it, applying the rules of the dictionary you specify, which is english in my case:

select to_tsquery('english', 'downing');
 to_tsquery 
------------
 'down'
(1 row)

We can fix this problem by _ using a different dictionary . This makes perfect sense since we’re don’t consider names part of a language! For this, Postgres gives us the _simple dictionary:

select to_tsquery('simple', 'downing');
 to_tsquery 
------------
 'downing'
(1 row)

The simple dictionary doesn’t create a lexeme from the token given to it – it just returns the raw word (unless it’s a noise word) lower-cased. This will work perfect for indexing our names:

select to_tsvector('simple', body ->> 'name') from ndc;

    to_tsvector                                   
-----------------------
 'alex':1 'mackey':2
 'adam':1 'furmanek':2
 'kristy':1 'sachse':2
 'downing':2 'heather':1
 'passos':2 'thiago':1
 'arif':1 'wider':2
 'preece':2 'ryan':1

Perfect. We’ll use this when building our overall index in just a minute.

Applying Weights to Keywords

Proper tagging is difficult to do. I’m not going to spend time on how to do that – let’s just assume that you and your app have a cool set of tags you’re happy with. Now comes the big question: are those tags words?

On one hand, it seems like the answer should be yes. Tags are contextual and tend to be things like “database”, “career”, “azure” etc. But what about the tags “virtual-machines” or “virtual-network”?

select to_tsvector('virtual-network');
                 to_tsvector                 
---------------------------------------------
 'network':3 'virtual':2 'virtual-network':1

select to_tsvector('virtual-machines');
                to_tsvector                
-------------------------------------------
 'machin':3 'virtual':2 'virtual-machin':1

Both of these tags will match on the term “virtual”, no matter what it’s followed by. That means we’ll get a hit on “virtual-conference”, “virtual-meeting”, and “virtually everything” since the word “virtually” will turn into the lexeme “virtual”. That might be OK, it really depends on your tagging strategy. For me, I’ll be using the simple dictionary once again because tags are specific, simple terms for this conference.

OK – now let’s address the weighting. We can apply weights to our tags by using the setweight function in Postgres:

select 
setweight(
  to_tsvector('simple', (body ->> 'tags')), 
'A')
from ndc;
                   setweight                                                    
-------------------------------------------
 'cloud':1A 'fun':2A
 'microsoft':2A 'net':1A
 'agile':1A 'design':2A 'devops':8A 'methodology':9A 'people':3A 'skills':6A 'soft':5A 'soft-skills':4A 'ux':7A
 'agile':1A 'ethics':6A 'people':2A 'skills':5A 'soft':4A 'soft-skills':3A
 'cloud':2A 'database':3A 'microsoft':4A 'net':1A

Weighting is simply a matter of applying a letter suffix to the positional integer. As you can see, cloud:1A has replaced cloud:1. That will be used when we run our query later on.

Oh yeah – something neat to note here is that Postgres is smart enough to take a JSONB array value and turn it into a text array for us, on the fly, and then apply indexing :).

Weighting Considerations

At this point we need to figure out relative weighting for the information we’ll be searching. If you have only text blob your indexing, then it doesn’t make sense to apply weighting – but that’s rarely the case in an online app.

The thing you need to consider when weighting is what “hits” are valued more than others? Weighting doesn’t affect which records will be recognized, it simply lifts those records to the top depending on how you weighted them (A through G).

I’m going to make the choice that if someone enters a tag, that should be raised to the top. Next would be someone’s name (though you could argue it should be the other way around) and finally whatever was found in the title:

Given this, we can build our entire search index with something like:

select 
setweight(to_tsvector('english', (body ->> 'tags')), 'A') || ' ' ||
setweight(to_tsvector('simple', (body ->> 'name')), 'B') || ' ' || 
setweight(to_tsvector('english', (body ->> 'title')), 'C')::tsvector
as search_index
from ndc;

_ Note : you’ll notice that I’m using the || operator to concatenate the values together, including a space between them. If you don’t do this you’ll get words jammed together and crappy results._

We’ve applied the top weight, A, totags and B to name with title coming in last with C. This is just relative ranking, which means that terms found in the keywords are ranked higher than the title, for instance. That will help Jane find her DevOps at Azure talks.

Kunjan will find Heather’s talk as we’re not stemming – so he won’t get confused with bad results. And finally Nenne will easily find her “Blazor” talk as the name appears in the title.

The only tricky part to this is if a speaker’s name appears in the title of a talk – so “Juana Blazor” might throw off the result – but there’s simply no way we can know which our user might want. We can, however, make the decision that hits in the names should be counted higher! Which is what we did.

Let’s add a generated column to our ndc table and test it out!

alter table ndc
add search tsvector
generated always as (
   (
   setweight(to_tsvector('english', (body ->> 'tags')), 'A') || ' ' ||
   setweight(to_tsvector('simple', (body ->> 'name')), 'B') || ' ' || 
   setweight(to_tsvector('english', (body ->> 'title')), 'C'))::tsvector
) stored;

This is a new feature in Postgres 12 – generated columns. They’re virtual columns that are (for now) stored on disk and completely managed by Postgres. Whenever our record is updated our search index will be too!

We’re now ready to start querying.

Constructing a Proper Query

Let’s start with the 3rd example first: “blazor”, which in Nenne’s query. This isn’t a keyword match because it’s not part of our tags, but it is a project title which will, hopefully, appear in a title somewhere. In that case, we can run the following query just fine:

select 
body ->> 'title' as title, 
body ->> 'name' as name
from ndc
where search @@ 
to_tsquery('english', 'blazor');

-[RECORD 1]--------------------------------
title | Blazor, a new framework for browser-based .NET apps
name | Steve Sanderson
-[RECORD 2]--------------------------------
title | Blazor in more depth
name | Steve Sanderson Ryan Nowak

Groovy! We’re using our tsvector field, search, and running a comparison with @@ to the to_tsquery function. We get back some results and we can see that we have “Blazor” in the title. Great!

At that point Nenne remembers that Steve Sanderson is one of her favorite speakers, so she decides to search both “blazor” and “Sanderson”:

ERROR: syntax error in tsquery: "blazor sanderson"

Oh no! What happened? The short answer is that to_tsquery expects a single word as an argument, which seems really weird at first! I mean… this is a full text search dude! WTF?

The problem is that Postgres doesn’t know what you want to do with more than one word. Is it just a collection of words? Or is it a phrase which has some structure to it. The query “blazor Sanderson” doesn’t mean anything to you or me, but Jane’s query “Azure DevOps” could be considered a phrase, where the term “Azure” needs to come before “DevOps”.

For that, we can modify our query using plainto_tsquery:

select 
body ->> 'title' as title, 
body ->> 'name' as name
from ndc
where search @@ 
plainto_tsquery('english', 'blazor sanderson');

-[RECORD 1]------------------------------------
title | Blazor, a new framework for browser-based .NET apps
name | Steve Sanderson
-[RECORD 2]------------------------------------
title | Blazor in more depth
name | Steve Sanderson Ryan Nowak

Yes! boom! That works really well. The function plainto_tsquery takes a plain text blob and treats it just like a bunch of words. In fact you can see exactly what it does by asking Postgres:

 select plainto_tsquery('blazor sanderson');

    plainto_tsquery     
------------------------
 'blazor' & 'sanderson'
(1 row)

The text gets parsed into individual words, tokenized and turned into lexemes and then placed into a logical AND condition. In other words: both “blazor” and “sanderson” must be in the search index.

But what about Jane’s query? She wants to know what’s knew with Azure DevOps:

select 
body ->> 'title' as title, 
body ->> 'name' as name
from ndc
where search @@ 
plainto_tsquery('english', 'azure devops');

-[RECORD 1]-----------------------
title | Static Sites, Dynamic microservices, & Azure: How we built Microsoft Docs and Learn
name | Dan Fernandez
-[RECORD 2]-----------------------
title | DataDevOps for the Modern Data Warehouse on Microsoft Azure
name | Lace Lofranco

Hmmm. Well that sort of worked in that we have two talks about Azure that also have the term “devops” in the title… however there’s nothing there about the Azure DevOps product. One way that we can fix this is to send in a phrase rather than a blob of words using phraseto_tsquery:

select body ->> 'title' as title, body ->> 'name' as name
from ndc
where search @@ phraseto_tsquery('english', 'azure devops');

(0 rows)

This is a bit more accurate: there aren’t any talks specifically about Azure DevOps. The phraseto_tsquery function leverages the positional argument that’s stored with tsvector, making sure that one word will appear before another. You can see this if you ask Postgres what’s going on:

select phraseto_tsquery('azure devops');

  phraseto_tsquery  
--------------------
 'azur' <-> 'devop'

The words are tokenized into lexemes once again, but this time there’s the positional <-> operator, indicating that “azure” must appear before “devops” in the string (the inclusive AND is implied).

OK, let’s make sure that Kunjan can find Heather’s talk and then we’ll be done! I’ll use the regular plainto_tsquery here since I want to be sure we match properly on name:

select 
body ->> 'title' as title, 
body ->> 'name' as name
from ndc
where search @@ 
plainto_tsquery('Downing');

(0 rows)

Good grief – no results!?!?! What the heck?

Using the Right Dictionary

The problem we’re having is matching dictionaries. When we use to_tsquery or, in this case, plainto_tsquery, the words we pass in will be tokenized according to some kind of dictionary. The default has to do with the location of the server and the default configuration – but it’s typically set to the language of the region of the server.

In the case of our name tokens, however, we used the simple dictionary which means that lexemes didn’t get generated and therefore will cause a match problem.

To see what I mean, take a look at our plainto_tsquery for “Downing” using the default dictionary (which is “english” in my case):

select plainto_tsquery('Downing');

 plainto_tsquery 
-----------------
 'down'
(1 row)

We’re trying to match a literal term to a lexeme , so of course we’re going to have problems. We can get over this by using the simple dictionary with plainto_tsquery:

select 
body ->> 'title' as title, 
body ->> 'name' as name from ndc where search @@ 
plainto_tsquery('simple','Downing');

-[RECORD 1]------------------------------
title | Keynote: The Care and Feeding of Software Engineers
name | Heather Downing

Much better! But this raises another question…

How Do You Query With Two Dictionaries?

I want to be able to query with both the English and simple dictionaries – but how can I do that and still get reasonable results?

The simplest way to do this with an OR query:

select 
body ->> 'name' as name, 
body ->> 'title' as title, 
body ->> 'tags' as tags
from ndc where 
search @@ plainto_tsquery('english', 'heather keynote') OR
search @@ plainto_tsquery('simple', 'heather keynote');

-[RECORD 1]-------------------------------
name | Heather Downing
title | Keynote: The Care and Feeding of Software Engineers
tags | ["agile", "people", "soft-skills", "ethics"]

It’s a bit on the verbose side, but as you can see we were able to find Heather’s keynote just fine. Note also that I’m using plainto_tsquery here because I’m expecting a word salad, I can change that, however, in the case of names.

We’re almost done! Now let’s sort our results in a meaningful way.

Ranking The Result Using Our Weighting

Weighting doesn’t do much good unless we can apply it, so for that we’ll need to make sure there’s some form of “score” we can use when querying. For that, we have Yet Another Postgres Function: ts_rank.

There are actually two of these functions. The first is ts_rank which is a score based on word frequency and the second is ts_rank_cd, which is based on frequency but also coverage distance – which is basically how far words are apart in a query. For us, ts_rank will do fine.

To use these functions you have to pass in the tsvector value as well as the tsquery:

select 
ts_rank(search,q_eng) + ts_rank(search,q_simple) as rank,
body ->> 'name' as name, 
body ->> 'title' as title,
body ->> 'tags' as tags
from ndc, 
plainto_tsquery('english', 'devops') q_eng,
plainto_tsquery('simple', 'devops') q_simple
where 
search @@ q_eng OR
search @@ q_simple
order by rank desc
limit 5;

-[RECORD 1]---------------------------------
rank | 0.9074664
name | Ashley Noble
title | DevOps Trials and Tribulations of a DevOps transformation in a large Company
tags | ["devops"]
-[RECORD 2]---------------------------------
rank | 0.6383234
name | Damian Brady
title | Pragmatic DevOps - How and Why
tags | ["devops"]
-[RECORD 3]---------------------------------
rank | 0.6079271
name | Enrico Campidoglio
title | Understanding Git — Behind the Command Line
tags | ["t", "devops"]
-[RECORD 4]---------------------------------
rank | 0.6079271
name | Pooja BhaumikNick Randolph
title | Using Flutter to develop cloud enabled mobile applications
tags | ["cross-pl", "mobile", "devops"]
-[RECORD 5]---------------------------------
rank | 0.6079271
name | Klee Thomas
title | Picking up the pieces - A look at how to run post incident reviews
tags | ["agile", "devops"]

A few things to note about this code:

I’m adding the ts_rank results together because each tsquery is going to have its own score. I’ll get into this in a bit.
I’ve aliased the tsquery functions for readability because I need to use them in the select statement as well as the where clause.
I limited the results, because there are a lot.

The OR query works great and we’re able to query by names, tags and titles and we’re almost done – but as you can see the scoring is … weird.

Postgres does some voodoo math behind the scenes and honestly it doesn’t really matter what those scores are all about – what does matter is that some are scored higher than others and we need to make sure our scoring scheme works as we want.

Looking at the top 2 it’s easy to see it does: they have the term “devops” as tags as well as the title. This is a classic SEO rule for the web, and we should feel good about our search strategy, don’t you think? I guess it can be abused, however, if we pretend it’s 1998 and load our title and speaker’s name with keywords:

select
ts_rank(search,q_eng) + ts_rank(search,q_simple) as rank,
body ->> 'name' as name, 
body ->> 'title' as title,
body ->> 'tags' as tags
from ndc, 
plainto_tsquery('english', 'devops') q_eng,
plainto_tsquery('simple', 'devops') q_simple
where 
search @@ q_eng OR
search @@ q_simple
order by rank desc
limit 5;

-[RECORD 1]-------------------------------------------------------------------------------------
rank | 0.9074664
name | Ashley DevOps Noble
title | DevOps Trials and DevOps Tribulations of a DevOps transformation in a large DevOps Company
tags | ["devops"]
-[RECORD 2]-------------------------------------------------------------------------------------
rank | 0.6383234
name | Damian Brady
title | Pragmatic DevOps - How and Why
tags | ["devops"]
-[RECORD 3]-------------------------------------------------------------------------------------
rank | 0.6079271
name | Enrico Campidoglio
title | Understanding Git — Behind the Command Line
tags | ["t", "devops"]
-[RECORD 4]-------------------------------------------------------------------------------------
rank | 0.6079271
name | Pooja BhaumikNick Randolph
title | Using Flutter to develop cloud enabled mobile applications
tags | ["cross-pl", "mobile", "devops"]
-[RECORD 5]-------------------------------------------------------------------------------------
rank | 0.6079271
name | Klee Thomas
title | Picking up the pieces - A look at how to run post incident reviews
tags | ["agile", "devops"]

OK it’s not perfect, but it’s much better than indexing a blob of text because:

We can recognize speaker names
We’re weighting tag recognition over title
We’re weighting tags and names over the loose text of a title

I think for most web applications this will work really well!

Flexing Postgres 12

Trying to decide between to_tsquery, plainto_tsquery and phraseto_tsquery can be difficult. It was kind of straightforward in our case – we’re not searching on any phrases really.

The Postgres team decided to be helpful in this regard, especially when it comes to web applications, so they created websearch_to_tsquery. It basically treats the input as if it were entered into a Google search. To be dead honest I have no idea what’s happening under the covers here, but it’s supposed to be a bit more intelligent than plainto_tsquery and a little less strict than phraseto_tsquery.

I’ve played with it a few times and haven’t noticed much of a difference – it is worth noting however!

Phew! Long post – hope it was helpful!

PostgreSQL Tools for the Visually Inclined

Rob Conery — Mon, 04 Mar 2019 23:10:28 +0000

I started my career on the Microsoft stack building forms and websites using drag and drop tools. Over time that became a punchline, which is unfortunate because honestly, the productivity was insane.

In 2008 I made the jump to the Linux world and I was completely disoriented. Everything was a damn text file. Yes, you could use a Mac or Ubuntu or whatever Unix Desktop du Jour seemed fun but there simply was no getting around the need to know your commands, which I did.

Just like learning SQL, learning your text commands makes you more efficient. I promise you that I’m not about to flip the l33t bit. I’m not here to convince anyone of anything – what I do want to do is to share how I embraced the command line with respect to PostgreSQL and was damn happy for it.

Friendly vs. Friendly

I’ve been meaning to write this post for years but it was this post from Ryan Booz that made me fire up the editor. Ryan is a SQL Server DBA that is writing a series on how he’s learning PostgreSQL after a 15 year (!) career as a SQL Server DBA. I can’t imagine that change is an easy one.

Basically, Ryan has concerns (which I understand):

In the case of PostgreSQL, I’ve quickly come to the conclusion that bad tooling is one of the main reasons the uptake is so much more difficult and convoluted coming from the SQL Server community. Even the devs I’m currently working with that have no specific affinity for databases at all recognize that PostgreSQL just feels like more of a black box then the limited experience they had previously with SQL Server.

I can’t say he’s wrong on this, although I will say the term “bad” is a bit subjective.

Let me get right to it: jumping from SQL Server to PostgreSQL is much more than changing a tool. PostgreSQL was built on Unix , with Unix in mind as the platform of choice, and typically runs best when it’s sitting on some type of Unix box. The Unix world has a pretty specific idiom for how to go about things and it certainly isn’t visual!

As someone who learned to code visually, I had to learn what each icon meant and the visual cues for what happens where. I came to understand property pains, the lines under the text of a button that described shortcuts, and the idiomatic layout of each form. Executing a command meant pressing a button.

In the Unix world you write out that command. The check boxes and dialogs are replaced by option flags and arguments. You install the tools you need and then look for the binaries that help you do a thing, then you interrogate them for help, typically using a --helpcommand (or just plain help).

The same is true for PostgreSQL. This is the thing that I think was stumping Ryan. He’s searching for visual tooling in a world that embraces a completely different idiom. It’s like going to Paris and disliking it (and France) because the barbecue is horrible.

Let’s walk through some common PostgreSQL DBA “stuff” to show what I mean.

Your Best Friend: psql

When you encounter a new Unix tool for the first time (and yes, I’m labeling PostgreSQL that) you figure out the binaries for that tool. PostgreSQL has a number of them that you’ll want to get to know, including pg_dump and pg_restoreamong others. The one we want right now is psql, the interactive terminal for PostgreSQL that gets installed along with the server. Let’s open it and ask it what the hell is going on:

Hello psql

I’m using Mac’s Terminal app but you can use any shell you like, including Powershell and the Windows command line. I would strongly urge you, however, to crack open a Linux VM or Docker to get the “flavor” of working with PostgreSQL. You can, indeed, find barbecue in Paris but it might help to explore the local cuisine.

Reading through this list of options and commands will take some patience the first time – but it’s worth it! At the top of the list are the common options, like using -cfor running a command and -dfor the database to run the command in. There’s a key statement, however, at the very end of this help screen:

psql: it’s interactive!

The psql tool is interactive! This will help us – so let’s log in to a database and have a look around. But which database? We’ll create one by running this on the command line:

createdb redfour

The createdbbinary has one job, in typically Unix fashion: create a database on the local server. It has a counterpart binary as well: dropdb. How do I know this? It’s one of those things you get used to as you work with Unix systems – figure out the binaries and what they do.

How do you do that? We know about one binary so far, psql, so let’s figure out where that lives and hopefully the other binaries live there too:

Using which and ls to tell us more

This is one of those things you learn over time: asking Unix which version of a tool/binary/runtime it’s using and where it’s located. The result of that command is telling me that psql lives in the /Applications/.../bin directory, which is pretty standard for binary tools. I copy/paste the result to the ls command (list contents) and we can see the binary tools at our disposal.

Yay. Let’s log in and play around.

What the Hell Is Happening?

Right now I’m at an interactive terminal within my database… and have no idea what to do next. This is the major upside of visual tooling: you have cues that you can follow which inform you as to what’s happening. It’s the difference between Halo on the Xbox and an old school MUD – it feels outdated and silly.

Let’s keep going and see if that’s true. When we ran the --help command before, it told us to use \? to figure out commands within psql, so let’s try that first:

Hello sea of text crashing over me!

There is so much to absorb here. All of these cryptic little commands do something but what they do, at first, will likely be opaque to you. This is Yet Another Patient Deep Breath point, because pretty soon we’re about to light this shit on fire (in a good way). What you have, right here, is a lot of raw power right at your fingertips. It just takes a few hours to understand the cadence of these commands as well as their modifiers. I’ll show you exactly what I mean in just a few minutes, for now let’s ground ourselves.

Scroll down (using down arrow or your mouse) to the Informational command section. This is your bread and butter – here you can see what’s in your database at a quick glance. We can do that by using \d (press Q to get out of the text view of the help page):

Our database is empty. Let’s fix that by creating a quick table for our users:

When you write a SQL command within psql you can wrap the lines. Notice that the prompt changes as well, telling you that you currently have an open paren. To finish the command add a semi-colon and we’re done. Note: I’m not going to get into SQL but it’s really worth your time to learn.

Now let’s list out our relations again:

Lovely. We have our table and the thing that handles the id generation for that table, called a sequence. Let’s ask psql more about this table using \d users:

The structure of our table is laid out in glorious ASCII, heavy with information and completely bereft of anything resembling prettiness. For visual people, this is a turn off as it’s completely different than what they’re used to (which I understand). For people used to working in a text-based idiom, this is heavenly.

Why? It’s the speed of the thing. Let’s put a clock to the problem. One of your appdevs just did something ill-advised with their ORM and they think they might have broken the users table. You decide to investigate:

psql redfour
\dt users

When you’re just starting out with PostgreSQL (and psql), you’ll need to squeeze your memory a bit for the commands to inspect a table. After a while your fingers will be done typing before your next breath.

This is the power you want as a DBA.

At this point I could go off on all of the psql commands available to you, however I would encourage you to explore these for yourself and see what’s possible, and how blazingly fast you can get it done. My coworker (ha ha so fun to say that now) Craig Kerstiens has written extensively on PostgreSQL, and this post is extremely helpful for people getting used to the command line aspect of it.

I want to get into why this kind of thing matters.

Text is a Helluva Drug

If I asked you to move data from one server to another using your favorite visual tool, how would you do it? If you do it often then the process would be a simple one and likely involve some right-clicking, traversing a menu, and kicking off a process in your tool of choice.

In Unix land (and therefore Postgres land) it’s a matter of remembering a few commands. But this is where it gets interesting because everything in Unix is a text file. Almost every task you can think of in Unix can be done using a text-based command. It would be like trying to find barbecue in Paris when every building is made of meat and the Seine is a river of hot coals.

To show you what I mean, here’s how you might pull your production database down to your local server:

pg_dump postgres://user:password@server/db > redfour.sql
createdb redfour
psql redfour < redfour.sql

The pg_dump binary has the singular task of turning a database into a SQL file. You can, of course, tweak and configure how this works and to find out all of the options you would use… can you guess? pg_dump --help . So we’re dumping the structure and data to a SQL file, creating our database and then pushing that SQL file as a command into our new database.

This entire process will execute in < 5 seconds on a smaller sized database (~20mb). This is why we like text and text-based interfaces – SPEED!

There’s Always a Way

As you might be able to tell, I’ve had this conversation more than a few times. Visuals are very important, to be sure! But they have their place when it comes to your daily workflow as a DBA. I would argue that double-clicking, right-clicking, and drag/drop are much slower than taking the time to memorize some common commands.

One place that psql sucks, however, are visuals. Executing a query on a large table can look horrible:

Yuckity Yuck

This is DVD Rental sample database, running a select * from film; query. It looks like crap! The good news is that we should be able to fix this. Let’s ask psql what’s going on using \? :

There are two things to notice here. The first is \x which allows for expanded output, or vertical alignment of data. That looks like this:

Using expanded output

The other thing you can do is to set HTML as the output using \H. This will execute the query, returning the results in HTML:

This is interesting but I want this saved to a file. To do that, I can use \o (which you can see in the help menu) and specify which file:

The file produced isn’t terribly exciting, but it’s somewhat useful:

This is where we can embrace the texty nature of Unix and see what’s possible if we start jamming binaries together with some core Unix commands, which are all based on text.

Let’s use psql to execute a query, but this time we’ll format things using Bootstrap:

echo "<link rel='stylesheet' href='https://stackpath.bootstrapcdn.com/bootstrap/4.3.1/css/bootstrap.min.css'>" > report.html && psql chinook -c "select * from film" -H >> report.html && open report.html

OK it’s certainly not crazy amazing but for a quick report it’s not so bad. You can alter the SQL statement to output only the columns you want, and you could formalize the call using a bash function to make it all pretty.

Yeah But It’s Not Management Studio!

Very true. You can’t double-click a table and edit the rows, for instance, and there are no spiff icons. Altering data is done with INSERT and UPDATE commands, deleting is done with DELETE. This is something that you do have to get used to, for sure, and if this is a common task for you than you might want to focus on a tool that allows that (such as Postico, which is free).

If there’s one reason to use psql it’s speed. I would also argue power but for now I’ll go with speed as the primary reason. You’re done before you know what happened and, if you have repetitive tasks, you can save your commands as text files to run as you need, when you need.

Change isn’t easy, but the people I know that have made the change use psql on a daily basis and absolutely love it. They also flip into a visual tool as needed. One thing they all agree on, however, is that they don’t miss the visual stuff at all.

Creating a Massively Scalable WordPress Site on Azure’s Hosted Bits

Rob Conery — Wed, 09 Jan 2019 22:11:04 +0000

I had yet another discussion about WordPress the other day with a friend here at my co-work space (Impact Hub in Honolulu). I told them I was working on a deployment script for spinning up WordPress and Ghost on Azure’s infrastructure and their response was what I’m used to:

WordPress? Dude… is this new gig warping your mind?

It might be. My response was a variation of the same one I always give:

WordPress powers 30% of the entire web. I think supporting it well on Azure is a no-brainer.

I use WordPress. I like it and yes there are things that, as a developer, I wish could be different. The simplicity of the thing, however, is what really attracts me to it. So, if you’re a person who uses WordPress and you also use Azure, this post might prove useful!

_ Disclosure : I work at Microsoft in Developer Relations. My job is to figure things like this out and then 1) tell the product teams what could work better and 2) report to you how I managed to get it all to work. If this is helpful, hooray!_

Step 0: Get Comfortable with the CLI

There are other ways to do this, but I like “scripted recipes”. There are ARM (Azure Resource Management) templates as well that allow you to click a button and “deploy to Azure” which I think is great, but I’m a bit of a control freak and dig my shell scripts (which may not be the best so sound off if you see improvements).

The other reason I like shell scripts is that you can add comments and use them as a bit of a learning tool. At least that’s what I’m hoping to do here – learning this Azure stuff for me hasn’t been easy and hopefully my notes help some of you.

If you want to jump right to it, here’s a gist you can read through. Yes, I know, I could just use a Docker Compose file and be done. I wanted a better database solution and I also wanted the challenge to… back under the bridge with you!

Finally – if you want to play along, make sure you have the Azure CLI installed and that you’re logged in with a default subscription set.

The first thing we need to do is declare our variables, etc:

RG="[YOUR RESOURCE GROUP]"
APPNAME=$RG-wordpress #Name what you want
LOCATION="Central US" #put where you like

#Recommend to keep these random, but if you need to change go for it
USER=admin_$RANDOM #set this to whatever you like but it's not something that should be easy
PASS=$(uuidgen) #Again - whatever you like but keep it safe! Better to make it random
SERVERNAME=server$RANDOM #this has to be unique across azure

#accepted values for the service plan: B1, B2, B3, D1, F1, FREE, P1, P1V2, P2, P2V2, P3, P3V2, PC2, PC3, PC4, S1, S2, S3, SHARED
#B2 is good for getting started - read up on the different levels and their associated cost.
PLAN=B2

#Kick it off by creating the Resource Group
echo "Creating resource group"
az group create -n $RG -l $LOCATION

At the very top I’m setting the resource group as RGbecause it’s used everywhere. I’m then setting the APPNAMEand LOCATION, the latter with a default of “Central US”.

When you create things in Azure, you use “resource groups” for logical groupings. This is extremely helpful when you want to wholesale remove everything when you’re playing around, which I am. It also keeps project-based stuff nice and tidy.

The name of your website needs to be unique across Azure as well. I usually try to name mine with the resource group appended to it somehow. I’ll talk about the user name and password stuff later.

The final step is to create the resource group. Now let’s create MySQL.

Step 1: Spinning Up a Managed MySQL Instance

This, to me, is the main benefit of using Azure. You get a super-scalable MySQL instance to back your fully-managed website. Most one-click installs you see online give you a single VM with MySQL installed right next to WordPress. This usually works fine (such as with this blog you’re reading) but if you’re planning on ramping up, it can be a bit scary.

The first thing to decide is the size of our database. You can ramp this thing to the moon if you want, but I suggest keeping it small to start. I’ve listed out the SKU structure in the script:

#The sku-name parameter value follows the convention {pricing tier}_{compute generation}_{vCores} as in the examples below:
# --sku-name B_Gen5_2 maps to Basic, Gen 5, and 2 vCores.
# --sku-name GP_Gen5_32 maps to General Purpose, Gen 5, and 32 vCores.
# --sku-name MO_Gen5_2 maps to Memory Optimized, Gen 5, and 2 vCores.

#WARNING - this might error out if your region doesn't support the SKU you set here. If it does, execute:
#az group delete -g [resource group] to drop everything and try again
#The SKU below is reasonable for a WP blog, but if you're going to host something more, consider more RAM/Cores
SKU=B_Gen5_1 #this is the cheapest one

echo "Spinning up MySQL $SERVERNAME in group $RG Admin is $USER"

# Create the MySQL service
az mysql server create --resource-group $RG \
    --name $SERVERNAME --admin-user $USER \
    --admin-password $PASS --sku-name $SKU \
    --ssl-enforcement Disabled \
    --location $LOCATION

This code creates a smaller MySQL instance – in fact it’s the smallest one you can create. I ran into some issues with certain SKUs not being supported in certain regions, but if you get an error just drop the resource group and try again.

We then spin up MySQL. The one big thing to notice here is --ssl-enforcement Disabledwhich caused me some headaches. WordPress doesn’t use SSL out of the box, which you can change with a plugin or config changes (surprise surprise). If you’re behind a firewall or part of a VPN, this might not be a huge deal. You can change this later on, however, when the plugin is installed.

The final thing to point out is the admin-user and admin-passwordsettings. I am not a fan of leaving the root level stuff to the user, but right now that’s the way it is. To softly get around this, the script creates a random user name and generates a GUID for the password, which you can see in the first script above.

We’re now ready to open a firewall and create our WordPress database.

Step 2: Firewall and WordPress Database

Our server is up and running but is locked down completely, which I think is marvy. We need to be explicit about who/how/what can access our server, so we need to open a few holes in the firewall. The first will be for us, the second will be for our website later on:

echo "Guessing your external IP address from ipinfo.io"
IP=$(curl -s ipinfo.io/ip)
echo "Your IP is $IP"

# Open up the firewall so we can access
echo "Popping a hole in firewall for IP address $IP (that's you)"
az mysql server firewall-rule create --resource-group $RG \
        --server $SERVERNAME --name AllowMyIP \
        --start-ip-address $IP --end-ip-address $IP

# Open up the firewall so wordpress can access - this is internal IP only
echo "Popping a hole in firewall for IP address $IP (that's you)"
az mysql server firewall-rule create --resource-group $RG \
        --server $SERVERNAME --name AllowAzureIP \
        --start-ip-address 0.0.0.0 --end-ip-address 0.0.0.0

echo "Creating WordPress Database"
#IMPORTANT: you have to have the client installed locally. If you don't, you'll get an error right about here.
mysql --host=$SERVERNAME.mysql.database.azure.com \
      --user=$USER@$SERVERNAME --password=$PASS \
      -e 'create database wordpress;' mysql

The first hole is for my local IP address, which I’m grabbing using curl. This is a subshell that pings ipinfo.io and reads the response, setting it to a variable. I use that response to create the first firewall rule: AllowMyIP.

The next rule looks a bit scary. I’m creating a firewall rule that allows any service within Azure to ping my server. This is what the Azure docs have to say about it (emphasis mine):

To allow applications from Azure to connect to your Azure SQL server, Azure connections must be enabled. When an application from Azure attempts to connect to your database server, the firewall verifies that Azure connections are allowed. A firewall setting with starting and ending address equal to 0.0.0.0 indicates these connections are allowed. If the connection attempt is not allowed, the request does not reach the Azure SQL Database server.

Important

This option configures the firewall to allow all connections from Azure including connections from the subscriptions of other customers. When selecting this option, make sure your login and user permissions limit access to only authorized users.

Azure SQL Database and SQL Data Warehouse firewall rules

This might seem suboptimal when it comes to security because it is. However, this is how every service works within any data center. We can, if we want, further lock this thing down later on when we know the IP of our site (assuming it won’t change).

The final step in the code above is to use the mysqlclient to create the remote database. I couldn’t find a way around this. I know that most people don’t have MySQL installed locally, so this could be a problem for you. I’m hoping to find a better solution someday.

Optional Step 2a: Convenience Stuff

I’m a control freak and I like to have access to my database. I also hate trying to remember login information so to help myself out I’ll setup a .envfile with the relevant bits:

echo "Setting ENV variables locally"
MYSQL_SERVER=$SERVERNAME.mysql.database.azure.com
MYSQL_USER=$USER@$SERVERNAME
MYSQL_PASSWORD=$PASS
MYSQL_PORT=3306
MYSQL_DB=$DATABASE
echo "MYSQL_SERVER=$MYSQL_SERVER\nMYSQL_USER=$USER\nMYSQL_PASSWORD=$PASS\nMYSQL_PORT=3306" >> .env
echo "alias prod=\"mysql --host=$SERVERNAME.mysql.database.azure.com --user=$USER@$SERVERNAME --password=$PASS\" wordpress"
echo "MySQL ENV vars added to .env. You can printenv to see them, or cat .env."
echo "To access your MySQL Instance just run `prod` as an alias. You can rename this in .env."

If you don’t know, a .envfile typically contains environment variables and other things intended to help you with development. They might contain local database configuration, etc. Here, I’m outputting some environment variables and I’m also setting up an alias so I can connect to prodas needed.

Side note: if you use zshell and oh-my-zsh! there’s a plugincalled “dotenv” that will automatically load any .env file in a directory when you navigate to it.

You don’t want this checked into source control!

Step 3: Creating The Web App

We want our WordPress site to be fully managed, from the database all the way down to the website itself. This will allow us to flip a few bits in the future to add more cores to either the site or the database.

This is not a VM. Well, at least as far as you’re concerned. All of that is abstracted into Azure’s “service fabric”. That said, the first thing you need to do is to pick the size of your VM in the form of a plan :p:

#create the plan
#accepted values: B1, B2, B3, D1, F1, FREE, P1, P1V2, P2, P2V2, P3, P3V2, PC2, PC3, PC4, S1, S2, S3, SHARED
#B2 is good for getting started - read up on the different levels and their associated cost.
PLAN=B2

echo "Creating AppService Plan"
az appservice plan create --name $RG \
                          --resource-group $RG \
                          --sku $PLAN \
                          --is-linux

You can think about this plan in terms of its capacity and compute power. B1 is the lowest but won’t work if you’re using Linux. If your WordPress site is going to power some serious traffic, you might consider a plan with a bit more horsepower to it. It’s not the most straightforward thing, but you can have a read about plans here.

Now we get to spin up our WordPress site. We could pull down the WordPress source (PHP) and then send it up to Azure, specifying deployment and runtime settings etc. Or we could simply specify the image on DockerHub:

echo "Creating Web app"
az webapp create --resource-group $RG \
                  --plan $RG --name $APPNAME \
                  --deployment-container-image-name wordpress

When you create a webappon Azure, it needs to know 1) how to run it and 2) how to deploy it. You can specify a Python runtime, for instance, and “local Git” as the deployment option. This would allow you to push from your local machine and have your Flask app “just run”.

If you’re using Docker, however, the runtime is built into the image. All that Azure needs to do is to know where to get it. It’ll use your plan to create and run a container. In the code above, I’m telling Azure to use the wordpress image, which Azure knows to pull down from DockerHub.

Having a look at the image description on DockerHub, you can see there are a number of environment variables that I can set for the image, namely the database connection bits. This is perfect for our needs, but how do you pass this through from Azure?

The good news is that Azure will pass whatever app settings you have for your webapp directly to your container:

echo "Adding app settings"
#add the settings for the new MYSQL bits
az webapp config appsettings set --name $APPNAME \
                                 --resource-group $RG \
                                 --settings WORDPRESS_DB_HOST=$MYSQL_SERVER \
                                 WORDPRESS_DB_USER=$MYSQL_USER \
                                 WORDPRESS_DB_PASSWORD=$MYSQL_PASSWORD \
                                 WEBSITES_PORT=80

The environment variables are expected by the WordPress container, and I’m setting them to the MySQL database that I created above.

The last setting, WEBSITES_PORT, tells Azure which port to forward to in the container. I think the default is port 80, but I’m setting it here just for clarity.

The final thing we need to do is a bit of a bugger for me: we need to explicitly turn on logging. I wish this just happened by default, like with Heroku, but it doesn’t so we need to:

#turn on logging
echo "Setting up logging"
#setup logging and monitoring
az webapp log config --application-logging true \
                    --detailed-error-messages true \
                    --web-server-logging filesystem \
                    --level information \
                    --name $APPNAME \
                    --resource-group $RG

I’m outputting everything here because I want to know exactly what happens when Azure tries to spin up this container. I’ll be honest: debugging the container stuff has been difficult.

Step 3a: More Convenience Bits

As I mention, I’m a huge fan of laziness and I like to add things to .env files when I can. Let’s do that now so we can tail our application logs with a simple command:

echo "Adding logs alias to .env. Invoking this will allow you to see the application logs realtime-ish."

#set an alias for convenience - add to .env
alias logs="az webapp log tail --name $APPNAME --resource-group $RG"
echo "alias logs='az webapp log tail -n $APPNAME -g $RG'" >> .env

I’m setting the alias for the current shell and I’m also appending it to .envso I can access it later on.

Step 4: Run it!

We’re ready to go! The only thing left to do is to navigate to our new site. The first request will take a while as Azure is pulling down our image and running it for the first time. You can see this if you tail the logs while the site is loading, which is the very last step of our script:

echo "Opening site and viewing logs"
open https://$APPNAME.azurewebsites.net
source .env
logs

I’m opening our new URL, loading our new .env file into the shell and then calling our new alias, logs. This will allows us to see what’s happening at Azure as WordPress spins up. Hopefully, after a minute or so, you see something like this:

Our new WordPress site

Seriously, Why Would You Do All of This?

It’s a good question, one I’ve asked myself repeatedly over the last few days. The answer is simple: this WordPress site is massively scalable. That’s kind of a big deal. It’s running right on the Azure “metal”, so to speak and I don’t need to worry about upscaling a VM.

As I mention: I really like WordPress and I run my business on it. Having it all on Azure would be a nice piece of mind.

As for costs, you’ll have to take a look at your subscription and decide if something like this is right for you. I have a regular subscription that I pay for and I can tell you that this setup is comparable with what I’ve seen for WordPress hosting.

Feedback, On Its Way

Every time I do stuff like this I end up with a big writeup for the product teams to look over. It’s important to note that this isn’t a situation where I “just send an email” or file a bug. My team works closely with the product teams and they like (I think) that we’re beating on their stuff.

I’ll be letting them know my thoughts on the MySQL stuff (admin user/pass, executing a remote command, etc) and a few more things that I need to get clarity on first. That’s a bit of a problem I have right now as I step into all of this: learning it is not straightforward.

I suppose that will be my biggest point of feedback. There’s so much to learn! It’s hard to get going and often I find myself doing things the “old way”. It’s also my job to help get this stuff in order, which I like.

Imposter’s Handbook, Season 2 is Released

Rob Conery — Thu, 03 Jan 2019 21:53:25 +0000

Over the winter holiday break (on Christmas Eve, to be precise), Scott Hanselman and I released the next volume in theImposter’s Handbookseries. It took us just over 18 months to put this thing together and I couldn’t be happier with it.

Shannon’s Second Theorem, Illustrated via Deathstar

From Logic to Boolean Algebra, Binary to Circuits

There was so much content I had to ax from the first volume of The Imposter’s Handbook because I ran out of time and space. For instance: I knew very, very little about binary operations and even less about encryption and I desperately wanted to change that. Unfortunately, it had to wait until I had the time.

That time came over the last 18 months. Scott and I dove into things like binary addition and subtraction, logic gates, and Boolean Algebra.

Something I could never remember: XOR

It was fun to dive into these subjects but I was not expecting what came next. Even writing this now – I’m struggling to come up with a way to accurately capture the singular importance of one person’s work. He’s been compared to Einstein, Edison, Newton – all rolled into one.

Claude Shannon and Information Theory

Claude Shannon created Information Theory with a single paper written in 1948. In it, Shannon detailed a way to describe information_ digitally_, that is with 1s and 0s. He detailed how this information might be transmitted in a virtually lossless way and, as if that wasn’t enough, he also described just how much information that digital signal could contain.

We take this kind of thing for granted today, but keep in mind that in Shannon’s day, the only way to communicate over great distances was with telegraph or telephone!

But wait, there’s more!

Prior to inventing Information Theory, Shannon wrote a master’s thesis that many people regard as the most important master’s thesis ever written.

The Creation of Digital Circuits

In the late 1930s, Claude Shannon was working on his master’s degree at MIT. He was also working with Vannevar Bush on the Differential Analyzer, a room-sized mechanical computer that would calculate differential equations typically for military use.

The Twin Cambridge University Differential Analyzer (Public Domain)

This machine had to be programmed by hand, which meant breaking it down and rebuilding it, using rods, wheels, and pullies as variables in a complex ballistic equation. Eventually, many of the mechanical bits were replaced with electrical switches that moved levers here and there, reducing the time it took to break the machine down and rebuild it.

These switches sparked something in Shannon, who recalled a class he took at the University of Michigan called Boolean Algebra. Turns out, this chap named George Boole figured out a way to apply mathematical principles to simple logical propositions. Shannon extended those ideas and figured out how the entire room-sized computer could be replaced with a series of electrical circuits.

Claude Shannon invented the digital circuit.

Encryption, Hashing and Blockchain

The story kind of wrote itself from that point on. Digital circuits led to digital computers which led to more efficient ways of calculating things which led to the need to transmit that information which led to the need to keep it a secret which led to where we are today.

Scott and I dove into all of this.

Cracking a simple asymmetric cipher

We explore SSH keys and how RSA works. We dive into hashes, discussing the goods and bads of each – including how Rainbow Tables can be used to quickly and easily crack credit card information that hasn’t been salted.

Simplified doodle of SHA256

We eventually end up with a discussion of cryptocurrency and blockchain, detailing why some people love it and others absolutely hate it. Both sides have some pretty good points…

It was a ton of fun writing this book – made even moreso because I got to do it with a friend! I really hope you enjoy it!

Transactional Data Operations in PostgreSQL Using Common Table Expressions

Rob Conery — Mon, 13 Aug 2018 21:28:50 +0000

PostgreSQL has a ton of amazing features, often overlooked by developers who prefer to use abstractions to work with SQL and their database. This is a big topic and obviously can spark a ton of debate. If you’ve read my blog before you know I dislike ORMs tremendously … aside from LLBLGenPro because Frans is my friend and he doesn’t like it when I trash ORMs.

One of the great features of PostgreSQL is Common Table Expressions, or CTEs, otherwise known as “WITH queries”. These are simply chained SQL expressions that allow you to pass the result of one query into another, functional style.

I use them a lot for reporting, but I also use them for creating orders when someone buys something from me. Let’s see how.

Setting Up The Database

Let’s create the core of my database. These tables are simplified, but the core of what they’re supposed to do is present:

create extension if not exists pgcrypto;

create table orders(
  id serial primary key, 
  key uuid unique default gen_random_uuid(),
  email text not null, 
  total decimal(10,2),
  created_at timestamptz default now()  
);

create table order_items(
  id serial primary key,
  order_id int not null references orders(id) on delete cascade,
  sku text not null,
  price decimal(10,2) not null,
  quantity int not null default 1,
  discount decimal(10,2) not null default 0
);

create table downloads(
  id serial primary key,
  key uuid unique not null default gen_random_uuid(),
  order_id int not null references orders(id) on delete cascade,
  order_item_id int not null references order_items(id) on delete cascade,
  times_downloaded int not null default 0
);

create table products(
  id serial primary key not null,
  sku text unique not null,
  name text not null,
  price decimal(10,2) not null,
  created_at timestamptz not null default now()
);

I’m showing you this code for a few reasons:

If you want to play along (which I encourage), you can
The defaults and structure make working with CTEs much simpler
SQL is straightforward and easy if you take the time to learn it

All of that said, there are a few things I’d love to call out about this design:

I’m using on delete cascade for the foreign keys to ensure that I don’t have orphans
I’m ensuring that null values don’t creep into my database
Whenever I have a not null constraint, I try to make sure I set a default
Finally, gen_random_uuid comes from the pgcrypto extension

OK, let’s add some data to our products table:

insert into products(sku, name, price)
values
('imposter-single','The Imposter''s Handbook', 30.00),
('mission-interview','Mission:Interview',49.00);

Great. Now let’s get to the good stuff.

Problem 1: Saving Order Data Transactionally

When a new order comes in, I need to create an order record and then an order_items record. This must be done in a transaction or Bad Things will happen. This is simple to do with a CTE, because CTEs execute within a single transaction :

with new_order as(
  insert into orders(email, total) 
  values ('rob@bigmachine.io',100.00) 
  returning *
), new_items as (
  insert into order_items(order_id, sku, price)
  select new_order.id, 'imposter-single',30.00
  from new_order
  returning *
)
select * from new_items;

When you insert a new record with PostgreSQL, you can ask for it right back with returning *. If you just want the id, you can add returning id. The first query inserts the new order and then returns it, which I can then use in the second query. Obviously: hard-coding values isn’t a good idea, but I’ll fix that in a moment.

The result:

id | order_id | sku | price | quantity | discount 
----+----------+-----------------+-------+----------+----------
  1 | 1 | imposter-single | 30.00 | 1 | 0.00
(1 row)

Perfect.

Problem 2: Creating Downloads From Our New Order

I’m starting simple, making sure things work as intended, then moving forward. My next step is to create downloads so that users can download what they’ve bought immediately. For that, I can chain a new query:

with new_order as(
  insert into orders(email, total) 
  values ('rob@bigmachine.io',100.00) 
  returning *
), new_items as (
  insert into order_items(order_id, sku, price)
  select new_order.id, 'imposter-single',30.00
  from new_order
  returning *
), new_downloads as (
  insert into downloads(order_id, order_item_id)
  select new_order.id, new_items.id 
  from new_order, new_items
  returning *
)

select * from new_downloads;

I tack on a returning * from my insert statement for order_items and then I can use that to generate the downloads in a third query, this time using a select for the insert.

The result:

id | key | order_id | order_item_id | times_downloaded 
----+--------------------------------------+----------+---------------+------------------
  1 | 1fa7c369-94c4-4220-83ba-78e35cfc7377 | 1 | 1 | 0
(1 row)

Great! The best part of all of this, so far, is that I can feel good about the data going into my database because of my constraints and design, and I can have faith that it’s been added correctly because a CTE is wrapped in a single transaction.

Problem 3: Inserting Multiple Order Items

One of the minor drawbacks of a CTE is that you can only execute a single statement with each clause. If you think of this in functional programming terms, it’s a bit like currying in that you have a single argument (the result of the previous query) and a single function body that returns a single value.

How, then, would you insert multiple order_items? This is where things could get a little tricky, especially if you’re not a fan of SQL. I like using it, so what you’re about to see doesn’t freak me out:

with new_order as(
  insert into orders(email, total) 
  values ('rob@bigmachine.io',100.00) 
  returning *
), new_items as (
  insert into order_items(order_id, sku, price)
  (
    select new_order.id, sku,price
    from products, new_order
    where sku in('imposter-single','mission-interview')
  )
  returning *
), new_downloads as (
  insert into downloads(order_id, order_item_id)
  select new_order.id, new_items.id 
  from new_order, new_items
  returning *
)

select * from new_downloads;

I’m getting around the problem by using a simple select statement for the insert. It’s going to the products table to insert whatever SKUs are given to it. Let’s run the query and see what happens:

id | key | order_id | order_item_id | times_downloaded 
----+--------------------------------------+----------+---------------+------------------
  1 | 6e695533-dd8d-407d-bdca-d71f81c666fb | 1 | 1 | 0
  2 | 31f81049-d08a-4f4c-b30c-565169178268 | 1 | 2 | 0
(2 rows)

It works! Sort of. We have one last problem…

Hard Coding, Data Integrity, and Validation?

I’m hard-coding the email address as well as the SKUs, which isn’t a Good Thing, obviously. This is where we brush up against what your ORM wants to do for your vs. what you might want to do as a coder. Put another way: would you really write this SQL in your code?. I certainly wouldn’t.

Here are some possible solutions to these issues.

The SKU Problem

What if a SKU is passed to this SQL that is not in our product’s table? The short answer is non-compelling: nothing. If a SKU isn’t found in the products table, it will simply be ignored. This is sub-optimal because we can end up adding crap data to our system!

How can we fix this? My first inclination would be to wrap this routine in a function, passing in the email, SKUs and everything else in a jsonb variable called cart. In my function, I would make sure the cart totals matched and that the SKUs were present in the database.

This is where you rip me apart for putting business logic in a database. I can understand why people think that, however I can also understand why I do it anyway. The answer is simple: I’m more likely to change my platform/ORM than I am PostgreSQL. To me, this kind of thing belongs as close to your data as possible. It’s a simple data operation that’s not exactly unique to my business, is it?

The other solution is to make sure the cart is validated before it gets pushed to this query. If we can trust the inputs, then we’re good to go.

Blobs of SQL In Your Code

I think SQL is a beautiful thing, but that’s my problem. Yours is trying to figure out where to put this stuff! One thing you could do is to store this as a prepared statement, which offers quite a few benefits. Let’s see the code, then we’ll dive into the benefits:

prepare new_order(text, decimal(10,2), text[]) as
with new_order as(
  insert into orders(email, total) 
  values ($1,$2) 
  returning *
), new_items as (
  insert into order_items(order_id, sku, price)
  (
    select new_order.id, sku,price
    from products, new_order
    where sku = any($3)
  )
  returning *
), new_downloads as (
  insert into downloads(order_id, order_item_id)
  select new_order.id, new_items.id 
  from new_order, new_items
  returning *
)
select * from new_downloads;

Whenever you write a SQL statement for PostgreSQL, the engine needs to parse the SQL, analyze it, and then optimize/rewrite it for execution. You can skip a number of those steps if you tell PostgreSQL the query you plan on running ahead of time, so it can parse and analyze it once. You can do this with the prepare statement.

The downside is that you need to use positional arguments, as you see I’m doing here with $1, $2 etc, which means you lose a little readability. If you can get over that, executing this statement means that you can call it by name using execute and some parameters:

execute new_order('rob@bigmachine.io',100.00, '{imposter-single,mission-interview}')

You’ll notice the funky {imposter-single} syntax – that’s how you work with arrays in PostgreSQL. Since I’ve switched to working with arrays, I’ve opted to use the any keyword, which works like in but is specifically for array values.

Summary

Long post, but I encourage you to explore and see what’s possible with your database, even if it’s not PostgreSQL. The SQL I wrote above would likely replace 100s of (total) lines of ORM code and orchestration, but yes there is a learning curve.