sql

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 to update and maintain. Additionally, the query is inefficient, because it performs the calculations more than once. Read more on Level up your PostgreSQL Skill with Lateral Joins…

Timing Your Queries in Knex.js for Node.js

While developing web applications, I keep a close eye on performance issues, particularly in database queries. In my latest project, I’ve been using Knex.js, a SQL query builder for Node.js.

I developed a method of logging the queries executed by Knex.js as well as the execution times for each query. This method can be applied to nearly any application that uses Knex.js, and it uses a few features of Knex.js that I didn’t notice immediately, so I thought I’d share this small but useful bit of code.
Read more on Timing Your Queries in Knex.js for Node.js…

Using ActiveRecord to Abstract “Greatest N Per Group” Queries

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 the SQL, I worked it into a form that fit my needs and moved it into my ActiveRecord model.

My approach allowed me to specify the group based on a set of columns and to abstract the required join logic behind an ActiveRecord scope. The following gist is a complete script that demonstrates the approach.

Read more on Using ActiveRecord to Abstract “Greatest N Per Group” Queries…