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 […]
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.
On my current project, we wanted to use named parameters in our queries. Unfortunately, we are using the MySQL ODBC driver for .NET, and named parameters are not supported. Positional parameters are supported, so we created a little utility to help.
There are few things as humbling as learning a programming language of a radically different paradigm from any you’ve used before. Recently, I’ve been feeling a need to learn a language with which I could easily manipulate data – usually collections of numbers. I thought about learning R or Julia, but while I’m sure those […]
Some database-related projects require access to information based around maximum and minimum values. Recently, I was trying to figure out the best way to perform a “greatest N per group” query in an ActiveRecord model with a SQL database backend. Eventually, I settled on the SQL proposed by Bill Karwin on StackOverflow. Once I had […]