From Advanced SQL to Kysely: Migrating Queries with CTEs, Window Functions, and Type Safety

Migrating advanced SQL to Kysely with CTEs, window functions, LAG, and TypeScript while validating result parity in analytics backend APIs.

Migrating advanced SQL to a typed query builder should not mean losing control over the query. In systems where calculations affect reports, costs, or business metrics, the goal is not to “hide SQL,” but to make it more maintainable without sacrificing precision.

In a real energy-analytics project, I worked with PostgreSQL, TypeScript, Kysely, Cloudflare Workers, Zod, ETL processes, and data-oriented backend APIs. An important part of the work involved migrating complex SQL queries to Kysely, including CTEs, window functions, LAG, PARTITION BY, DATE_TRUNC, and time-based aggregations.

The challenge was not simply translating syntax. The challenge was preserving the same analytical logic, validating that the results remained equivalent, and avoiding subtle errors in aliases, types, partitions, or ordering. In this article, I explain how I approached that migration, which patterns worked well, and which mistakes I learned to avoid.

The problem: SQL queries that grow in complexity

Many backend APIs start with simple queries: retrieve records, filter by date, group by day or user. But once a product needs real analytics, the queries grow.

In my case, the domain was energy. We had meter readings over time, cumulative data, consumption and demand calculations, costs, and simulations with or without BESS systems. That meant working with time series and one important rule: many readings do not represent direct consumption, but cumulative values.

For example, if a meter records:

Plain Text•••

10:00 -> 100000 Wh
11:00 -> 104500 Wh
12:00 -> 109000 Wh

The consumption from 10:00 to 11:00 is not 104500 Wh, but the difference from the previous reading:

Plain Text•••

104500 - 100000 = 4500 Wh = 4.5 kWh

This sounds simple, but production introduces important details:

Missing readings.

Meter resets.

Negative values caused by resets or inconsistent data.

Multiple meters.

Dynamic date ranges.

Aggregation by day, month, or tariff period.

Comparison between energy with and without a battery.

That is where advanced SQL becomes necessary.

Why I did not want to lose explicit SQL

When migrating to a query builder, there is a risk of turning everything into abstractions that are too generic. In my experience, that can be counterproductive.

SQL is still the natural language for expressing analytical calculations in relational databases. If a query needs WITH, LAG, PARTITION BY, DATE_TRUNC, GREATEST, or aggregations, I do not want to hide that intent behind helpers that are harder to understand.

My goal in using Kysely was not to replace SQL. It was to preserve SQL's expressiveness while gaining additional benefits:

Type safety in TypeScript.

Better query composition.

Less manual interpolation.

Better integration with backend services.

Safer refactors.

Fewer errors when column names or structures change.

For me, a good SQL-to-Kysely migration should preserve the mental model of the original query.

Why use Kysely

Kysely was useful because it lets you write queries with a structure that remains close to SQL while providing a typed model. Instead of moving to a heavy ORM, I could continue thinking in terms of tables, columns, joins, CTEs, and SQL expressions.

This is especially important in backend engineering when queries are not just CRUD. In data analytics, SQL often contains some of the most sensitive business logic.

Kysely adds value when:

A query needs to be composed from TypeScript.

An endpoint receives dynamic filters.

Type safety is required for columns and results.

CTEs or fragments need to be reused.

Manual SQL-string concatenation should be avoided.

Explicit SQL with sql<T> is needed for advanced expressions that the query builder does not model directly.

The key is accepting that Kysely and SQL are not competitors. Kysely helps structure and type the code. SQL remains the conceptual foundation.

Case context: energy analytics and time series

The most representative case involved calculating real energy consumption from cumulative readings.

Consider a simplified table:

SQL•••

CREATE TABLE meter_usage (
  id UUID PRIMARY KEY,
  meter_id INTEGER NOT NULL,
  timestamp TIMESTAMP NOT NULL,
  usage NUMERIC NOT NULL,
  battery NUMERIC DEFAULT 0
);

In this domain:

usage represents a cumulative reading.

battery can represent energy supplied by a storage system.

usage + battery can be used to estimate consumption without the BESS effect.

To obtain actual consumption in kWh, deltas between consecutive readings must be calculated.

To compare scenarios, metrics with and without BESS are calculated.

The critical logic is based on this idea:

SQL•••

LAG(value) OVER (
  PARTITION BY meter_id
  ORDER BY timestamp
)

If the ORDER BY is wrong, the deltas are wrong. If PARTITION BY is missing, readings from different meters get mixed. If the wrong alias is used, the calculation may reference the wrong value. If resets are not handled, a negative delta can distort the entire report.

Base example in raw SQL

A first version in raw SQL can look like this:

SQL•••

WITH readings AS (
  SELECT
    meter_id,
    timestamp,
    usage,
    battery,
    usage + battery AS usage_total
  FROM meter_usage
  WHERE timestamp >= $1
    AND timestamp < $2
),
lagged AS (
  SELECT
    meter_id,
    timestamp,
    usage,
    usage_total,
    LAG(usage) OVER (
      PARTITION BY meter_id
      ORDER BY timestamp
    ) AS prev_usage,
    LAG(usage_total) OVER (
      PARTITION BY meter_id
      ORDER BY timestamp
    ) AS prev_usage_total
  FROM readings
),
deltas AS (
  SELECT
    meter_id,
    timestamp,
    GREATEST(usage - prev_usage, 0) / 1000.0 AS delta_kwh_with_bess,
    GREATEST(usage_total - prev_usage_total, 0) / 1000.0 AS delta_kwh_without_bess
  FROM lagged
  WHERE prev_usage IS NOT NULL
    AND prev_usage_total IS NOT NULL
)
SELECT
  meter_id,
  ROUND(SUM(delta_kwh_with_bess), 2) AS total_kwh_with_bess,
  ROUND(SUM(delta_kwh_without_bess), 2) AS total_kwh_without_bess
FROM deltas
GROUP BY meter_id;

This query already contains several important decisions:

Using WITH to separate steps.

Calculating usage_total.

Using LAG to compare against the previous reading.

Using PARTITION BY meter_id so time series are not mixed.

Using GREATEST(..., 0) to prevent negative deltas.

Converting Wh to kWh by dividing by 1000.0.

Final aggregation by meter.

It is not a huge query, but it contains business logic. Migrating it carelessly can change the results.

Using CTEs to organize calculations

CTEs are useful because they break an analytical query into stages. For me, each CTE should answer one clear question.

In the example above:

readings: defines the base dataset.

lagged: adds temporal context with the previous reading.

deltas: calculates real consumption.

Final query: aggregates the results.

This makes the query easier to review. It also makes migration to Kysely easier because each .with(...) can represent one mental stage from the original query.

A common bad practice is trying to fit everything into one SELECT. It may work, but it makes intermediate results harder to validate. When the calculations matter, I prefer being able to isolate every step.

Window functions: LAG and PARTITION BY

LAG gives access to a previous row's value without manually writing a self join. For time series, this is extremely useful for calculating differences between consecutive readings.

Example:

SQL•••

SELECT
  meter_id,
  timestamp,
  usage,
  LAG(usage) OVER (
    PARTITION BY meter_id
    ORDER BY timestamp
  ) AS prev_usage
FROM meter_usage;

The important detail is that LAG depends entirely on the context defined by OVER.

PARTITION BY meter_id means: calculate the previous reading within each meter.

ORDER BY timestamp means: order chronologically before looking for the previous row.

If PARTITION BY is omitted, the first reading from one meter could be compared with the last reading from another. SQL would still execute, but the result would be wrong.

That kind of bug is dangerous because it does not always break the API. It simply returns bad numbers.

Monthly aggregation with DATE_TRUNC

For monthly reports, a common pattern is to group deltas using DATE_TRUNC.

SQL•••

WITH lagged AS (
  SELECT
    meter_id,
    timestamp,
    usage,
    LAG(usage) OVER (
      PARTITION BY meter_id
      ORDER BY timestamp
    ) AS prev_usage
  FROM meter_usage
  WHERE timestamp >= $1
    AND timestamp < $2
),
deltas AS (
  SELECT
    meter_id,
    timestamp,
    GREATEST(usage - prev_usage, 0) / 1000.0 AS delta_kwh
  FROM lagged
  WHERE prev_usage IS NOT NULL
)
SELECT
  meter_id,
  DATE_TRUNC('month', timestamp) AS month,
  ROUND(SUM(delta_kwh), 2) AS total_kwh
FROM deltas
GROUP BY meter_id, DATE_TRUNC('month', timestamp)
ORDER BY month;

This is a common data-analytics API pattern: calculate deltas first, aggregate afterward. Changing that order can produce different results.

For example, summing cumulative readings by month is not the same as summing monthly deltas. In the energy domain, that difference is critical.

Migrating the query to Kysely step by step

An equivalent Kysely version can preserve the same CTE structure.

First, simplified types:

TypeScript•••

import { Kysely, sql } from 'kysely';

interface Database {
  meter_usage: {
    id: string;
    meter_id: number;
    timestamp: Date;
    usage: number;
    battery: number | null;
  };
}

type EnergyRow = {
  meter_id: number;
  total_kwh_with_bess: number;
  total_kwh_without_bess: number;
};

Now the query:

TypeScript•••

async function getEnergyTotals(
  db: Kysely<Database>,
  from: Date,
  to: Date
): Promise<EnergyRow[]> {
  return db
    .with('readings', (qb) =>
      qb
        .selectFrom('meter_usage')
        .select([
          'meter_id',
          'timestamp',
          'usage',
          sql<number>`COALESCE(battery, 0)`.as('battery'),
          sql<number>`usage + COALESCE(battery, 0)`.as('usage_total'),
        ])
        .where('timestamp', '>=', from)
        .where('timestamp', '<', to)
    )
    .with('lagged', (qb) =>
      qb
        .selectFrom('readings')
        .select([
          'meter_id',
          'timestamp',
          'usage',
          'usage_total',
          sql<number>`
            LAG(usage) OVER (
              PARTITION BY meter_id
              ORDER BY timestamp
            )
          `.as('prev_usage'),
          sql<number>`
            LAG(usage_total) OVER (
              PARTITION BY meter_id
              ORDER BY timestamp
            )
          `.as('prev_usage_total'),
        ])
    )
    .with('deltas', (qb) =>
      qb
        .selectFrom('lagged')
        .select([
          'meter_id',
          sql<number>`
            GREATEST(usage - prev_usage, 0) / 1000.0
          `.as('delta_kwh_with_bess'),
          sql<number>`
            GREATEST(usage_total - prev_usage_total, 0) / 1000.0
          `.as('delta_kwh_without_bess'),
        ])
        .where('prev_usage', 'is not', null)
        .where('prev_usage_total', 'is not', null)
    )
    .selectFrom('deltas')
    .select([
      'meter_id',
      sql<number>`ROUND(SUM(delta_kwh_with_bess), 2)`.as(
        'total_kwh_with_bess'
      ),
      sql<number>`ROUND(SUM(delta_kwh_without_bess), 2)`.as(
        'total_kwh_without_bess'
      ),
    ])
    .groupBy('meter_id')
    .execute();
}

The important point is not that the query becomes “shorter.” In fact, it may be just as long as the original SQL. The value is that it now lives inside TypeScript with types, composition, and better integration with the rest of the backend.

Handling aliases and types

One of the most common mistakes when migrating analytical SQL to Kysely is assuming aliases behave exactly the same at every query level.

For example:

TypeScript•••

sql<number>`usage + COALESCE(battery, 0)`.as('usage_total')

Then, in another CTE:

TypeScript•••

sql<number>`LAG(usage_total) OVER (...)`.as('prev_usage_total')

This works if usage_total belongs to the previous CTE and the next selectFrom points to that CTE. But trying to reuse an alias in the same level where it was created is not always accepted by PostgreSQL depending on the context.

My practical rules are:

If an alias is needed for another complex operation, move it to an earlier CTE.

Do not mix too many derived calculations in the same SELECT.

Use explicit and consistent names.

Validate results stage by stage.

In analytical queries, aliases are part of the query's mental contract. If they become inconsistent, the logic becomes much harder to follow.

When to use sql<T> inside Kysely

Kysely covers many common operations, but in advanced SQL it is normal to use sql<T> for specific expressions.

I use it mainly for:

Window functions.

PostgreSQL functions such as DATE_TRUNC.

Mathematical expressions.

Aggregations not conveniently represented by the builder.

Database-specific syntax where explicit SQL is easier to understand.

The important caveat is that sql<number> does not magically convert a runtime value into a JavaScript number. It is a type signal for TypeScript. PostgreSQL types such as numeric may still arrive as strings depending on the driver configuration.

TypeScript helps, but it does not replace runtime validation.

Validating parity between raw SQL and Kysely

The migration is not finished when the query compiles. It is finished when the results are equivalent.

A simple way to validate parity is to run both implementations against the same range and compare the results with a decimal tolerance.

TypeScript•••

type EnergyResult = {
  meter_id: number;
  total_kwh_with_bess: number | string;
  total_kwh_without_bess: number | string;
};

function toNumber(value: number | string) {
  return typeof value === 'number' ? value : Number(value)
}

function assertClose(
  label: string,
  actual: number,
  expected: number,
  tolerance = 0.01,
) {
  const diff = Math.abs(actual - expected)

  if (diff > tolerance) {
    throw new Error(
      `${label} mismatch. Expected ${expected}, received ${actual}, diff ${diff}`
    )
  }
}

function validateParity(
  rawSqlRows: EnergyResult[],
  kyselyRows: EnergyResult[]
) {
  const byMeter = new Map(
    rawSqlRows.map((row) => [row.meter_id, row])
  )

  for (const row of kyselyRows) {
    const rawRow = byMeter.get(row.meter_id)
    if (!rawRow) throw new Error(`Meter ${row.meter_id} not found`)

    assertClose(
      'with BESS',
      toNumber(row.total_kwh_with_bess),
      toNumber(rawRow.total_kwh_with_bess)
    )

    assertClose(
      'without BESS',
      toNumber(row.total_kwh_without_bess),
      toNumber(rawRow.total_kwh_without_bess)
    )
  }
}

In energy calculations, this validation is essential. A tiny difference caused by rounding may be acceptable. A systematic error caused by incorrect window partitioning is not.

For complex queries, I also recommend comparing intermediate CTEs:

Total rows in readings.

Total rows in lagged.

Number of valid deltas.

First and last timestamp per meter.

Total sum before rounding.

This makes it easier to identify exactly where logic diverged.

Common mistakes I found

1. Partitioning a window function incorrectly

This is one of the most dangerous mistakes:

SQL•••

LAG(usage) OVER (ORDER BY timestamp)

If there is more than one meter, this mixes time series. It should be:

SQL•••

LAG(usage) OVER (
  PARTITION BY meter_id
  ORDER BY timestamp
)

2. Ordering by the wrong column

In time series, ORDER BY defines what “previous reading” means. Ordering by created_at instead of timestamp can produce incorrect deltas.

3. Summing cumulative readings instead of deltas

This inflates the result. For consumption, I calculate differences first and aggregate afterward.

4. Trusting sql<number> too much

sql<number> helps the compiler but does not guarantee the database driver returns a JavaScript number. PostgreSQL numeric values may arrive as strings.

5. Reusing aliases too early

When one calculation depends on an alias, I prefer moving that alias into a previous CTE.

6. Rounding too early

Rounding every delta before summing can create accumulated differences. I normally sum first and round at the end.

7. Not testing real ranges

A query may work for one day with a few readings but fail conceptually over a month containing resets, missing data, or multiple meters.

Good practices

These are the rules that worked best for me when migrating advanced SQL to Kysely:

Keep the original SQL implementation as a reference during the migration.

Migrate incrementally, one CTE at a time.

Use explicit and consistent alias names.

Keep window functions visible with sql<T>.

Validate database-returned runtime types.

Validate results using real date ranges.

Compare intermediate results, not only the final output.

Avoid premature rounding.

Normalize PostgreSQL values when needed.

Inspect generated SQL whenever something does not match expectations.

I also consider separation of responsibilities important. The query should calculate data. The endpoint should validate input, apply permissions, and return a consistent response. Tools such as Zod help with that.

A simplified example:

TypeScript•••

import { z } from 'zod';

const energyQuerySchema = z.object({
  from: z.coerce.date(),
  to: z.coerce.date(),
  meterId: z.coerce.number().int().positive(),
});

async function energyHandler(request: {
  query: unknown;
  db: Kysely<Database>;
}) {
  const { from, to, meterId } = energyQuerySchema.parse(request.query);

  if (from >= to) {
    throw new Error('Invalid range: from must be before to');
  }

  return getMonthlyEnergy(request.db, {
    from,
    to,
    meterId,
  });
}

Input validation does not replace result validation, but it reduces failures before a request reaches the database.

Lessons learned

The main lesson is that advanced SQL does not disappear when you use a query builder. It remains there, and that is a good thing.

Kysely allowed me to move complex queries into a more maintainable TypeScript codebase, but the important reasoning remained SQL reasoning: understanding CTEs, window functions, time-series aggregation, and the meaning of the domain data.

I also learned that a correct migration is not simply one that compiles. In analytics systems, the real question is: does the number mean the same thing as before?

When working with energy metrics, reports, or costs, a query error can become a wrong business decision. That is why I prefer gradual migrations, explicit comparisons, and queries that can be read in stages.

Kysely provides a lot of value when used with this mindset: typing where it helps, explicit SQL where it matters, and validation where the business needs it.

Signals for recruiters

Migrated advanced SQL to typed queries with Kysely.

Used PostgreSQL for analytical processing and time series.

Implemented CTEs, window functions, LAG, PARTITION BY, and DATE_TRUNC.

Calculated energy deltas from cumulative readings.

Designed backend APIs with TypeScript, Zod, and input validation.

Integrated analytical queries into serverless APIs.

Validated parity between raw SQL and migrated logic.

Balanced explicit SQL, type-safe queries, and maintainability.

Handled common errors involving aliases, types, ordering, and aggregations.

Built data-oriented backend systems focused on reliable, precise calculations.