Helper Functions for Testing a PostgreSQL Database
When writing tests for a Node.js server, it’s common to test the database as well. (At least, one hopes.) Here are a few helper functions for testing a PostgreSQL database using Knex.
postgres
BLOBs with Knex and PostgreSQL
My team and I recently decided that we should store user-generated binary files in our PostgreSQL database so that a user could download them at a later time. This is different from uploading those files to something like Amazon S3 (which we’ll likely consider migrating to at some point down the road).
Level up your PostgreSQL Skill with Lateral Joins
PostgreSQL’s lateral joins have a lot of uses. One of my favorites is the ability to reuse calculations in a query. Before I discovered lateral joins, I would either copy calculations throughout the query or use subqueries. Neither of these approaches is ideal because they make the query much more difficult to read—and nearly impossible […]
Finding Records that Don’t Exist in PostgreSQL
I was recently tasked with writing a bit of code to find delivery route stops where nothing has to be unloaded. This code is intended to prevent a delivery driver from stopping at a store when there’s nothing in the truck for that customer that day.
An Introduction to PostgreSQL Materialized Views
Our team recently found itself in a situation where we needed to write a database query to: Union several tables together Filter out some rows Sort the unioned set different ways This was going to be a very expensive and slow query.