Jeremy Chone
Mar 16 · 4 min read

First, good article, even if I differ more than I concur.

Where we probably agree:

1) ORM: The ORM dogma has been as pernicious as the OO-for-everything one and creates more problems than it solves as applications grow.

2) SQL: Developers must learn and use SQL effectively when building a database-backed application, and I would also agree that using SQL Builders for “reducing the SQL learning curve” is a wrong reason, but used right, writing SQL with SQL builders is still writing SQL.

3) Need for Middle Layer: While developers should avoid ORM abstraction at all cost, a “SQL Oriented middle layer,” a la Knex, JOOQ, or Slonik (v.s. using raw pg or JDBC) can add a lot of value without breaking the SQL orientation of the data access logic.

3) Migration: Database migration requirement is overrated.

4) Slonik: Slonik seems to be very well thought out, modern, and robust, and built on solid premises. Postgres nodejs developers might find it perfect for their need, and I would recommend it over using raw “pg” library if Knex.js has been ruled out. To name a few, the use of JS tag template literal approach is neat and appropriate when string templating is at the core of a library’s approach, as it is with Slonik, and the middleware/interceptor and transaction model seems to be well thought out, and I wish Knex.js would adopt some of that feature.

Where I differ:

5) SQL Orientation: SQL Builders, a la Knex.js or JOOQ do not hide SQL but empower it, and while the SQL building is function based differ from string template based approach, it does not make it more or less SQL.

6) Safety: Knex.js or other SQL builders allows developers to write safer SQL, but knex.js as others, allow developers to go, rogue, with the bad and good that come with it. Also, the ugly unsafe example string concatenation used in this article could also be done Slonik if the developer use string concatenation rather than ${} (this anti-pattern is independent from SQL builder vs. string templated executor).

7) raw() function: In SQL Builder, going raw is a great way for any middle layer to give extra flexibility to the developers while enforcing the developer to mark those “un-processed” parts explicitly. Rust has similar concepts with its “unsafe” marker.

8) Multi-Database: Database migration is overrated, but developer working with multiple databases is not. Developers often work with different databases, and relearning a new paradigm to write SQL for each DB is tedious and error-prone. SQL Builder, such as Knex.js, allows developers to take the best of each database by writing mostly SQL while using a common paradigm. It’s not about writing one SQL that works across multiple databases, it about using the same abstraction layer, no matter how thin-to-SQL it might be. That being said, I prefer a good single-database library that gives me 100% of my database, rather than a library that focus on the 50% lowest common denominator of all databases. Knex.js is extremely good in this respect as it exposes the best of Postgres while still allowing well marked raw…() for the corner cases.

9) SQL Builder’s use cases: Reducing SQL builders for “software designed to work with multiple database dialects.”, is a clear miss-understanding of why SQL Builders are used (see 8 and 10)

10) Composition: This is where I think there is the biggest miss-understanding of the value of SQL Builders. This topic would need its own post, but the reason that we are using SQL builder in all our projects is to decouple the building of the query from the SQL string serialization, while staying faithful to the SQL semantics. While simple sub-queries nesting composition use case could be done with a templated string approach, true composition goes way beyond concatenating a couple of string together in the right order.

To summarize,

I think Slonik is an interesting project, seems to be well thought out, and should be looked at for anybody wanting to write raw SQL and does not feel the need of the SQL Builder composition value. I might at some point play with it for simpler projects.

However, relegating SQL Builders “to work with multiple database dialects” is not understanding the core value of SQL Builders, which is composition, meaning decoupling the way a query get built from the way it gets serialized. Slonik has an interesting string templatized approach, which can work for simple composition, such as nesting SQL autonomous statements (as shown in the example of this article), but will quickly become unwieldy for more advanced compositions where decoupling SQL ordering and atomicity from the code becomes more important.

In fact, from a front-end point of view, the core difference is that Slonik cleverly leverages JS tag template literals to provide a templating approach for building the SQL string, whereas SQL Builders, such as Knex.js, offers an object/methods approach but has the advantage to give more flexibility on how the application logic build the final query. But beside this front-end differences, those approaches are much closer than they seem, and there are a couple of backend features from Slonik that I would love to see in Knex.js such as middleware/interceptor and better transaction model. I would even go as far as describing Slonik as a SQL Builder with a string templating front-end.

In short,

- DO NOT USE ORMs in any language. - DO USE Knex.js if you need advanced composition need and/or have different projects with different databases. - DO USE Slonik if you want a modern, sophisticated postgresql only templatized SQL approach, with robust transaction, monitoring support, and you do not have a strong composition need.

32

    32 claps
    Jeremy Chone

    Written by

    Building Scalable Cloud Applications for a living.