Hacker News Reader

Comment by beders | Hacker News Reader

If you want to run SQL, write SQL.

The majority of your SQL will not require building dynamic where clauses or (dog forbid) dynamic joins.

Having your SQL as plain statements with simple placeholders (to create safe prepared statements) is the saner approach.

Not only can you pluck them into your favorite SQL tools and analyzers, but you will not be surprised by terribly performing queries, because you created them dynamically without understanding their complexity.

We've learned the hard lessons decades ago by misusing ORMs. While using alternative SQL syntax builders is avoiding many of those pitfalls, you will still inherit complexity by translating a SQL dialect to the builder pattern.

It is not worth it IMHO.

View on HN

Kysely is not an ORM, its a query builder with strong emphasis on type-safety and 1:1* mapping ("What You See Is What You Get") to SQL.

Projects writing raw SQL eventually end up implementing their own query building functionality. Which adds another thing to maintain and understand - that's also lacking in features and is not as type-safe.

Just use a well adopted query builder.

What sort of platonic ideal project do you work on that the majority of SQL you write doesn't need dynamic WHERE clauses? You're positing a majority use case I haven't encountered in 20 years of professional and personal projects.

In NodeJS land this library is pretty close to this: https://github.com/porsager/postgres

postgres.js is dope! I'm maintaining a Kysely community dialect for it. https://github.com/igalklebanov/kysely-postgres-js

Because writing SQL is a drudge, you will write some helpers here and there. Before you know it, you have your own buggy non type safe SQL builder. This is vastly superior.

For me, Rusts SQLx has been a good option for this. Write plain SQL with a macro and have it verified against a dev database at build time, optionally to a typed anonymous struct to match your query results. The performance isn't incredible though.

I've been curious about sqlx for a while. About the non-incredible performance: Do you mean the performance of the verification in development, or the performance of sqlx execution in a live setting?

I can't think of a single project I've worked on that did not need dynamic where clauses almost immediately.

Any patterns you'd recommend? (pseudo code)

1) db.execute("select name, tel from person where id={?}",personId);

2) const sqlText="select name, tel from person where id={?}";

db.execute(sqlText, personId);

3) as 2), but put sqlText in another file,

Maybe not the majority, but the most critical, like filter-based searches, will require some dynamic SQL. Type safety is a big win.

continues to be productive in django

Reply on news.ycombinator.com