sql | Atomic Spin

Development Practices

Give Me Every Nth Row: Reproducible Sampling in SQL Server and EF Core

For a recent project, I needed a small, representative sample of a large table. Here's the sampling approach I landed on.

Developer Tools

Kysely: Type-Safe SQL Without ORM Overhead

Kysely is a compelling choice for query building with type safety and SQL-shaped expressiveness without adopting a full ORM.

The Entity Framework: WHERE IN vs. OPENJSON Problem

Let's look at mitigations for slow queries caused by Entity Framework using OPENJSON instead of a basic WHERE IN clause for collections.

LINQ Expressions: LINQuistics of C#

Let's reduce those many lines of code for a single operation into something concise, useful, and extremely powerful: LINQ Expressions in C#.

The Software Life

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.