github reylosql/reylo v0.8.1

one hour ago

Reylo 0.8.1 types Turso Cloud's sqlean functions, runs on Drizzle's Postgres driver, plans MySQL grants in migrations and halves the median Postgres analysis on a remote server.

Important

Three changes can surface in an existing project:

  • Postgres: the type check refuses Supabase's transaction pooler on port 6543 and names the session pooler (#334)
  • MySQL: a BIT that a view, derived table or WITH query computes with coalesce, ifnull, greatest, least or any_value is unknown, where it was bytes (#329)
  • SQLite: soundex(x) of a nullable x is string | null, where it was string (#327)

Turso Cloud's sqlean functions

The type check now types the functions of the sqlean extensions Turso Cloud loads, which it refused with no such function (#327).

select id, name, levenshtein(name, :name) as distance
from users
where regexp_like(name, '^[A-Z]')
order by distance
limit 5

The functions it types, by extension:

  • uuid: uuid4, uuid7, gen_random_uuid, uuid_str, uuid_blob
  • crypto: md5, sha1, sha256, sha384, sha512, blake3, encode, decode
  • fuzzy: levenshtein, dlevenshtein, edit_distance, hamming, jaro_winkler, caverphone, translit
  • stats: median, percentile, percentile_90, stddev, variance, var_samp
  • text: left, right, lpad, rpad, repeat, reverse, split_part, starts_with
  • regexp: regexp_like, regexp_substr, regexp_capture, regexp_replace
  • series: generate_series and stats_seq

The SQLite inside @libsql/client has none of them, so a query that calls one fails on a local file: database.

Drizzle's Postgres driver

Queries now run on the Postgres driver drizzle-orm ships from 1.0.0-rc.6 (#338).

import { Pool } from "drizzle-orm/postgres/client";
import getUser from "./get_user.sql";

const db = new Pool({ url: process.env.DATABASE_URL });
const user = await getUser.run(db, { id: 1 });

What it runs on:

  • Clients: its Pool, a connection, a transaction or savepoint, and a drizzle() database over any of them
  • Neon: drizzle-orm/postgres/client/neon-ws, over a WebSocket
  • Refused: the neon-http, http and aurora clients, whose results don't name each column's type

MySQL grants in migrations

Migrations now read, compare and plan MySQL grants, which they refused (#328).

-grant select on users to 'reader'@'%';
+grant select, insert on users to 'reader'@'%' with grant option;
+grant update (full_name, code) on users to 'app_role';

The migration planned for that change:

grant update (code), update (full_name) on users to `app_role`@`%`;
grant insert on users to `reader`@`%` with grant option;

What it covers:

  • Objects: grants on the database's tables, views, columns and routines, each with or without with grant option
  • Drops and renames: a grant is revoked before what it's on is dropped or renamed, since MySQL keeps it, and granted again after
  • Introspection: reylo db introspect and reylo migrate init write the database's grants into schema.sql
  • Refused: a grant on every database, the server, another database, a role or a proxy, and set password or set default role in a migration
  • Permissions: an account that can't read MySQL's privilege tables is refused grants, with the statements that let it read them

Faster Postgres analysis

Each round of a Postgres analysis now sends its queries together, where each waited for the one before (#335). Over 2,320 statements on a server about 100 ms away:

  • Median: 514 ms → 242 ms
  • 90th percentile: 2.7 s → 0.9 s
  • Slowest: 18.5 s → 2.7 s

What's Changed

  • fix(engine): plan MySQL grants and what the readers left out by @colinhacks in #328
  • feat(sqlite)!: type the sqlean functions turso cloud loads by @colinhacks in #327
  • fix(migrate): leave Turso's internal tables to its engine by @colinhacks in #332
  • feat(cli): give reylo migrate and reylo db their own help by @colinhacks in #333
  • fix(postgres): handle transaction poolers in the type check and bun.sql by @colinhacks in #334
  • fix: close gaps in bit typing, sqlite rebuilds and the oracle by @colinhacks in #329
  • fix(release): make the channel publish script executable by @colinhacks in #336
  • feat(postgres): run queries on drizzle's postgres driver by @colinhacks in #338
  • fix(mapper): write the schema of deeply nested json by @colinhacks in #340
  • perf(engine): ask a postgres round's needs together by @colinhacks in #335
  • chore(release): 0.8.1 by @colinhacks in #345

Full Changelog: v0.8.0...v0.8.1

Don't miss a new reylo release

NewReleases is sending notifications on new releases.