A recent project of mine was potentially in need of a WebSocket API, and I wanted to investigate how to set up a WebSocket API in the AWS ecosystem. Extending the infrastructure from my previous DynamoDB setup to stream updates to a client via WebSockets was a straightforward problem to solve. I updated the Github […]
We’ve all hit that point on a project: features are working, and you’re starting to really expand the application’s capabilities, but the expansion is steadily leading to slower performance. It’s time for some refactoring around your database interactions Maybe the queries run by the application have become more and more complex. Maybe there are things […]
Database tables rarely stay the same over the course of a project. We often need to add tables, add or rename columns, or break up a table to support evolving project demands or updated domain modeling. Migrating existing data correctly is not always trivial. Here are two scenarios I ran into recently on a project […]
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.
If you’ve worked with a piece of software that talks to a database, chances are you’ve come across a long SQL query. Slowly, your eyes glaze over as you attempt to reason through the many joins and subqueries, trying to figure out if this query is the source of the bug. You debate between the […]
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 […]
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.
On my current project, we are developing a replacement for a legacy system written in Visual FoxPro that our customer has used for the last couple of decades. Since there is a lot of risk in switching over, we have developed a bi-directional syncing tool that attempts to keep each system up-to-date with changes made […]
Recently, I was tasked with creating a new screen containing a table of data for a project using React and Apollo. In the past, we would typically start by defining the database table, other access layers in between that and a GraphQL query schema, and finally the query on the front end using Apollo. This […]