database | Atomic Spin

DevOps & System Admin.

A Simple Approach to Complicated Database Defaults

Database tables rarely stay the same over the course of a project. Here is a simple pattern for migrating data into non-nullable columns.

TypeScript

Helper Functions for Testing a PostgreSQL Database

Writing tests for a node.js server? You should test the database too. Here are a few helper functions for testing a postgres database, using knex.

Development Practices

Why You Should Avoid Writing Complex SQL Queries – and What to Do Instead

SQL queries dangerously couple software to the persistence layer. And as they grow in size, they capture more complexity—I've even found myself embedding key business rules.

Platforms & Languages

Level up your PostgreSQL Skill with Lateral Joins

Referencing calculated columns in Postgres can be quite tricky. But lateral joins are a convenient way to reuse calculations within a query.

Platforms & Languages

Finding Records that Don’t Exist in PostgreSQL

Using PostgreSQL value lists and set operations to find records that do not actually exist in our database and identify problems in our dataset.

Developer Tools

A Simple Approach to Comparing Database Structures

We run our tool nightly to detect mismatches in records between the two systems. On the surface, this seems like a simple task. But the data structures between the two systems are

Development Practices

Mocking React/Apollo Data Tables for UI Development

This method helped me quickly determine the minimum data required to drive the front-end. That has directly driven the back-end code, reducing refactoring time and preventing unnec

Software Science

An Introduction to PostgreSQL Materialized Views

What materialized views are, when you should consider using them, and what we learned from bringing them into our Postgres database project.

.NET / WPF

Faking Named Parameters in .NET when Using ODBC

Here's some handy code for emulating named parameters when using ODBC drivers in C# or .net.

Development Practices

A Case for Learning J

Observations on dynamism, compositionality, regularity, and concision in J.

Ruby on Rails

Using ActiveRecord to Abstract “Greatest N Per Group” Queries

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.