Making a pr to SQLx to add Postgres lquery arrays. This took less time than I expected to try and fix. More time was spent wrangling my various projects to use a local sqlx
dependency.
Postgres ltree
has a wonderful ?
operator that will check an array of lquery
s. I plan to use this to allow filtering multiple labels in my expense tracker.
ltree ? lquery[] → boolean
lquery[] ? ltree → boolean
Does ltree match any lquery in array?
I love sqlx and type checked queries but always found it annoying to duplicate a bunch of queries vs using the dynamic query builder (which isn't type checked). Using Option
and NULL
s seems to improve things though!
// Postgres version
let ids = sqlx::query_as!(
Uuid,
"SELECT id FROM users \
WHERE ($1::timestamptz IS NULL OR updated_at < $1) \
AND ($2::timestamptz IS NULL OR updated_at > $2) \
AND ($3::boolean IS NULL OR is_guest = $3)",
updated_before_option,
updated_after_option,
is_guest_option,
)
.fetch_all(&pool)
.await;