sql Archives

PostgreSQL

How to Partition Consecutive Rows with PostgreSQL

You can use PostgreSQL's window function capabilities to partition data over a specific number of consecutive rows preceding any given row.

TypeScript

Representing a Discriminated Union in SQL

You can represent types with both shared and type-specific attributes as a discriminated union in TypeScript. Let's model that relationship in a database.

Prisma

Build an SQL Query from Variables with prisma.$queryRaw

Using prisma.$queryRaw, I was able to write a basic query that returned the data I needed. However, I also needed the query to filter and order the data.

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.

Extracurricular Activities

Using SQL to Look Through All of Your iMessage Text Messages

If you use iMessage, you can access and query the database where all of the messages are stored. All you need is a Mac and some basic SQL skills to begin!

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.

Web Apps

Timing Your Queries in Knex.js for Node.js

I developed a method of logging the queries executed by Knex.js and the execution times for each query. It can be applied to nearly any app that uses Knex.

.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.

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.

Ruby on Rails