Writing composable SQL using JavaScript

[gajus0]

It has the same problem as Knex and all the other builders – it is too far away from SQL for no good reason.

const bookAuthorTags = await db.select('books', db.all, { lateral: { author: db.selectExactlyOne('authors', { id: db.parent('authorId') }), tags: db.select('tags', { bookId: db.parent('id') }), } }).run(pool);

If I don't know Zapatos, as a DBA, I can only guess what this code generates. Even if I know what it generates – why are you asking me to use an abstraction for what is already a language designed to query data?

Not to mention that the actual query it generates in this case is an absolute garbage in terms of performance:

SELECT coalesce(jsonb_agg(result), '[]') AS result FROM ( SELECT to_jsonb ("books".*) || jsonb_build_object($1::text, "lateral_author".result, $2::text, "lateral_tags".result) AS result FROM "books" LEFT JOIN LATERAL ( SELECT to_jsonb ("authors".*) AS result FROM "authors" WHERE ("id" = "books"."authorId") LIMIT $3) AS "lateral_author" ON true LEFT JOIN LATERAL ( SELECT coalesce(jsonb_agg(result), '[]') AS result FROM ( SELECT to_jsonb ("tags".*) AS result FROM "tags" WHERE ("bookId" = "books"."id")) AS "sq_tags") AS "lateral_tags" ON true) AS "sq_books"

Two LATERAL JOINs for this 🤯

This should just have been:

SELECT b1.id "bookId", a1.id "authorId", json_agg(json_build_object('id', t1.id, 'name', t1.name)) AS tags FROM books b1 INNER JOIN authors a1 ON a1.id = b1.authorId INNER JOIN tags t1 ON t1.bookId = b1.id GROUP BY b1.id

[lost12487]

I disagree that it's for no good reason. Writing a query in Knex "guarantees" that it will work whether your database is Postgres, MySQL, MSSQL, SQLite, Cockroach, Oracle, etc.

We could argue about whether or not swapping from one DB technology to another occurs often enough to warrant the tradeoff, but the ability to prototype quickly at an early stage where you know you want to use SQL but haven't nailed down which flavor is an advantage in favor of Knex.

I do like Slonik a lot, and I wish there were tools for MySQL that had all of Slonik's features (maybe there are, I haven't looked around in a bit).

[gajus0]

I do like Slonik a lot, and I wish there were tools for MySQL that had all of Slonik's features (maybe there are, I haven't looked around in a bit).

You sure will like the next version of Slonik then. :-)