You can use PostgreSQL's window function capabilities to partition data over a specific number of consecutive rows preceding any given row.
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.
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.
Database tables rarely stay the same over the course of a project. Here is a simple pattern for migrating data into non-nullable columns.
Referencing calculated columns in Postgres can be quite tricky. But lateral joins are a convenient way to reuse calculations within a query.
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.
Here's some handy code for emulating named parameters when using ODBC drivers in C# or .net.
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.