sql Archives

ChatGPT

Generate CTEs for a Legacy Database Using an LLM

Here's how to use the power of LLMs to generate Common Table Expressions, making a legacy database easier to work with for ad-hoc queries.

.NET / WPF

Understand Entity Framework LINQ-to-SQL Translations

LINQ seems quite simple, but once you take this easy-to-understand concept and apply it to entities in Entity Framework, things get dicey.

Extracurricular Activities

Using SQL and Your Chat.db File to Look Through All of Your iMessages

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!

Prisma

Build an SQL Query from Variables with a Prisma Raw Query (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.

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.

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.

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