kysely 0.28.20 on Node.js NPM

Hey 👋

A small batch of bug fixes. Please report any issues. 🤞😰🤞

This maintenance release backports the fixes from 0.29.9.

Kysely's migrator no longer records timestamps that go backwards when the wall clock jumps back during a migration run (WSL2/VM time sync, an NTP step, an unsmeared leap second). Such timestamps made every later migrateToLatest() fail with "corrupted migrations", even though the migrations ran in the correct order. The migrator now reads the latest recorded timestamp while holding the migration lock, and records each new migration at max(now, latest + 1ms), so the order holds across runs and hosts. Executed migrations with an invalid timestamp now fail with "corrupted migrations" instead of being sorted arbitrarily.

Migration tables that already have out-of-order timestamps need a one-time repair. Run the query for your dialect below. It goes through executed migrations in name order, which is the order the migrator runs them in by default, and moves every timestamp that isn't later than the previous one to 1 millisecond after it. Running it again changes nothing.

Replace kysely_migration with your migration table's name, prefixed with the schema if you set migrationTableSchema. Run it while no migrations are running. Skip this if you use allowUnorderedMigrations: true. Name order isn't the execution order in that mode, and out-of-order timestamps don't block migrations there.

PostgreSQL:

with ordered as (
  select
    name,
    (extract(epoch from "timestamp"::timestamptz) * 1000)::bigint as executed_at,
    row_number() over (order by name collate "C") as position
  from kysely_migration
),
repaired as (
  select
    name,
    executed_at,
    position + max(executed_at - position) over (order by position) as repaired_executed_at
  from ordered
)
update kysely_migration
set "timestamp" = to_char(
  (timestamptz 'epoch' + repaired_executed_at * interval '1 millisecond') at time zone 'UTC',
  'YYYY-MM-DD"T"HH24:MI:SS.MS"Z"'
)
from repaired
where kysely_migration.name = repaired.name
  and repaired.repaired_executed_at <> repaired.executed_at;

MySQL 8.0+:

update kysely_migration
join (
  select
    name,
    executed_at,
    position + max(executed_at - position) over (order by position) as repaired_executed_at
  from (
    select
      name,
      timestampdiff(
        microsecond,
        '1970-01-01',
        str_to_date(`timestamp`, '%Y-%m-%dT%H:%i:%s.%fZ')
      ) div 1000 as executed_at,
      row_number() over (order by cast(name as binary)) as position
    from kysely_migration
  ) as ordered
) as repaired using (name)
set kysely_migration.`timestamp` = concat(
  date_format(
    timestamp '1970-01-01 00:00:00' + interval repaired.repaired_executed_at * 1000 microsecond,
    '%Y-%m-%dT%H:%i:%s.'
  ),
  lpad(repaired.repaired_executed_at mod 1000, 3, '0'),
  'Z'
)
where repaired.repaired_executed_at <> repaired.executed_at;

MS SQL Server 2016+:

with ordered as (
  select
    name,
    datediff_big(millisecond, '1970-01-01', convert(datetime2(3), [timestamp], 127)) as executed_at,
    row_number() over (order by name collate Latin1_General_BIN2) as position
  from kysely_migration
),
repaired as (
  select
    name,
    executed_at,
    position + max(executed_at - position) over (order by position rows unbounded preceding) as repaired_executed_at
  from ordered
)
update kysely_migration
set [timestamp] = convert(
    varchar(19),
    dateadd(
      millisecond,
      repaired.repaired_executed_at % 86400000,
      dateadd(day, repaired.repaired_executed_at / 86400000, cast('1970-01-01' as datetime2(3)))
    ),
    126
  ) + '.' + right('00' + cast(repaired.repaired_executed_at % 1000 as varchar(3)), 3) + 'Z'
from kysely_migration
join repaired on repaired.name = kysely_migration.name
where repaired.repaired_executed_at <> repaired.executed_at;

SQLite 3.33+:

with ordered as (
  select
    name,
    cast(round((julianday("timestamp") - 2440587.5) * 86400000) as integer) as executed_at,
    row_number() over (order by name collate binary) as position
  from kysely_migration
),
repaired as (
  select
    name,
    executed_at,
    position + max(executed_at - position) over (order by position) as repaired_executed_at
  from ordered
)
update kysely_migration
set "timestamp" = strftime('%Y-%m-%dT%H:%M:%S', repaired_executed_at / 1000, 'unixepoch')
  || printf('.%03dZ', repaired_executed_at % 1000)
from repaired
where kysely_migration.name = repaired.name
  and repaired.repaired_executed_at <> repaired.executed_at;

The binary collations (collate "C", cast(name as binary), etc.) make the database sort names the same way the migrator does in JavaScript.

🚀 Features

🐞 Bugfixes

fix: withSchema crashes on for ... of and json_agg/to_json tables in subqueries. by @igalklebanov in #2227
fix: inline key()/at() JSON path references infer unknown in jsonBuildObject. by @igalklebanov in #2228
fix: migration timestamps go backwards when the clock jumps back. by @igalklebanov in #2248

📖 Documentation

📦 CICD & Tooling

⚠️ Breaking Changes

key() and at() no longer accept a 2nd type argument for the output type (e.g. .key<'url', number>('url')). It was never documented. Use $castTo<T>() to override the output type. (#2228)

🐤 New Contributors

What's Changed

Full Changelog: v0.28.19...v0.28.20