A Simple Approach to Complicated Database Defaults

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 […]

Using SQL to Look Through All of Your iMessage Text Messages

iMessage is one of my favorite things about having an iPhone and a Mac. It’s also one of my least favorite things. I love being able to send messages from my computer, and the integration between the two devices is seamless. What frustrates me is the inability to efficiently search through old messages. iOS 13 […]

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 […]

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 […]

Faking Named Parameters in .NET when Using ODBC

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.

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 […]

Trailing Backslash Problem & Fix for Rails and SQLServer 2005

I came across a problem on project where trying to create a new record with a string attribute that ends in \ (backslash) was causing an OLE error to pop up through the DBI driver. For example, if you had a model with a string attribute called name and you tried to do Model.create! :name […]

Atomic Spin

Atomic Object’s blog on everything we find fascinating.

sql