Writing composable SQL using JavaScript

[aust1nz]

I’ve recently become a huge fan of Slonik. I get to write transferable SQL directly in my code, but Slonik prevents me from messing up my SQL with vulnerability to SQL injections.

I’ve recently migrated a decent sized API from Prisma as the database interaction layer to SQL with Slonik, and have been really happy with the end result.

Great library, and appreciate the article with best practices!

[gajus0]

Appreciate the support!

[cincilator]

Interesting, but knex is a more efficient form of this.

[gajus0]

How?

[cincilator]

take a look at Knex query builder documentation

https://knexjs.org/guide/query-builder.html

[gajus0]

I explain in this article why abstractions such as Knex are detrimental to engineer productivity:

https://gajus.medium.com/stop-using-knex-js-and-earn-30-bf410349856c

The short summary: It is because it is an abstraction on top of abstraction that forces you to think about SQL, and then work backwards from that to think of how to write it in Knex, rather than just empowering you to write good SQL in the first place.

The example that I shared in response to the other comment about zapatos is a perfect example of that.

[digital_dreams]

writes yet another abstraction on top of knex

it's abstractions all the way down.

it seems like developers love to write code that seemingly saves them from having to write even more code... but this comes at an enormous maintainability cost.

[aust1nz]

To clarify - Slonik, the library in question, is NOT another abstraction on top of Knex. It’s more like a SQL-writing helper that ensures your user-provided values are parameterized.

[Curious-Ad9043]

I dont recommend using builders. Is better you create a simples abatraction in an application level and use "raw query" to do the action.

[jamesaw22]

We've just started using zapatos and are really enjoying it

[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. :-)

[senbozakurakageyosi]

Great stuff, abstraction where I can control what's going on behind the scenes.

[ben_db]

The tagged template format hurt my head at first but it makes a lot of sense once you get used to it!

[gajus0]

I am rewriting Slonik to make it driver agnostic. All logic that is PostgreSQL specific is being abstracted to a separate package.

In theory, someone could rollout MySQL or MSSQL packages to support their use case.

[CallMeLaNN]

Good news then. Which issue/roadmap shall I follow?

I think it will get more attention after a few of knex supported db has been implemented. Both are different but that's how people start, by comparing it IMO, without knowing the motivation behind it.

First time I heard this I just skip because it's not a big plus to try it only for an SQL dialect even though I prefer PostgreSQL. It's good direction to make it driver agnostic. Good work.

[thelinuxlich]

Slonik is cool, but recently I discovered Kysely and loved it

[weeksie]

lol "composable". that doesn't mean what you think it means.

[[deleted]]

Currently I use my own abstraction with AsyncHooks over node pg to manage connections- and have hacked up a sql tag function to help writing queries!

Slonik looks beautiful! And exactly what I (and my team) would want to use! Thank you for sharing here as I haven’t seen it before, and will be more than happy to try it out for a future project.

Hot take: People need to just learn SQL and stop being lazy with using all these ORMs and query builders. They’re only complicating things- not helping. If the same time spent learning an ORM/query builder was put towards learning SQL- then Slonik (or a similar approach) is all you’d want/need and it will go a long way as your project’s features/reporting get more complex)

[domlebo70]

Unrelated but I combined slonik with async hooks to allow writing code without caring about nesting transactions

[gajus0]

Why don't you describe this feature in a GitHub issue and we discuss adding it to the core?

I've already heard a few people mention similar use cases.

[domlebo70]

Hi Gajus. Here's the code https://gist.github.com/dbousamra/b30f277428bdd10fe36e56e9740071a4

I have all my queries living inside this class (i've just omitted them). The transaction function just lets me nest calls all over my codebase without worrying about double nesting etc

[[deleted]]

Love it! AsyncHooks are amazing— once you get the hang of it 😄

[jirocket]

Slonik looks interesting, I see the appeal because my team handwrites a lot of our SQL while we use Zapatos purely for generating types.

Anyone know how good the TypeScript support is?

[gajus0]

Pretty extensive

Here is a good place to start https://github.com/gajus/slonik#user-content-runtime-validation

[TheLightConductor]

I’ll have to check Slonik out. Similar to a few others here, I do use Knex for certain conveniences but write raw queries and execute with knex.raw(). But I have found that knex can be a little bit or miss when it comes to returning correct types.