Hacker News Reader

Comment by the_duke | Hacker News Reader

There are some similar projects, like sqlx [1] for Rust. My problem with these is that they don't help to solve the actually hard problems.

While nice to have, preventing bugs with static SQL is usually easy to do by writing a few tests. Most of the SQL related bugs I have encountered were due to queries with dynamic/conditional joins, filters and sorting - and almost every project using a database needs those.

Approaches like this don't help there. That requires heavy-weight solutions that are more cumbersome to use and need a strong type system, like diesel [2] (Rust), Slick [3] (Scala) and some similar Haskell projects.

[1] https://github.com/launchbadge/sqlx

[2] https://github.com/diesel-rs/diesel

[3] https://scala-slick.org/

View on HN

> preventing bugs with static SQL is usually easy to do by writing a few tests

I've heard the same argument about TypeScript vs JavaScript and it's something dynamic typing proponents often say but in practice I find immense value in having the types autocompleted and checked in the editor - and I've worked plenty on both sides, current project is substantial RoR codebase, I've worked with Python and node.js backends on mature codebases. Eventually all these languages have some sort of static type hinting efforts to improve tooling - typescript being most successful.

The best thing I saw in this space was F# type providers which didn't require a pre-build step - the language had a mechanism for writing custom type providers that would look up the data source during compilation - unfortunately I didn't get to use it on any real world projects.

F# also has support for analyzers that can achieve similar functionality in case you don't want to take a dependency on a type provider.

https://github.com/Zaid-Ajaj/Npgsql.FSharp.Analyzer

https://github.com/aaronpowell/FSharp.CosmosDb#fsharpcosmosd...

ORMs like Diesel are definitely very useful. The problem I have with them is that their ORM abstraction often leaks. Fixing these abstraction leaks is a hard problem [1]. Ofc, there has been attempts to reconcile relational DBs with OOP like languages, but they are not very popular. [2]

PgTyped and some similar libs try to solve a simpler problem (typing static queries) and can be used to build more complex solutions when needed.

Writing query result/param type assertions by hand and using tests to guarantee type synchronization between DB and code wasn't maintainable on most projects I have seen.

[1] https://en.wikipedia.org/wiki/Object-relational_impedance_mi...

[2] https://en.wikipedia.org/wiki/The_Third_Manifesto

I may just not have enough context on the issue, but i've not had significant issues with SqlAlchemy's hybrid ORM/Query Builder approach.

While I like SQLAlchemy a lot, it has to be said that it leaks like a sieve.

Personally I think the problem is not that ORMs are leaky abstractions, it's that they often pretend not to be. SQLAlchemy just leaks unapologetically, if you struggle with ORM you can drop right down to using an DSL to construct SQL or just write SQL directly without feeling like you have to fight the ORM and mess with undocumented internals you shouldn't be touching.

Actually, after my quick scan of the readme, I think pgTyped takes a different approach than the other tools listed. It is a YeSQL-style tool with build-time code generation. The code generation is based on prepared statement metadata rather than table metadata. Like other YeSQL tools, the function name comes from a DocComment annotation in the .sql but the query params and the result set columns come from the prepared statement.

I'd be curious to hear more about the issues with joins, and dynamic conditionals. I've been working on a type provider lib for sql in kotlin[1].

The join problems I've seen are either the joined table has changed, or altered. The return type of a field may change.

The hard problem I encountered was doing things like json aggs, multiple joins, etc. I'm trying to address this by doing type safe aggregate/join functions. Secondly is query compilation. Compiling the output record of advanced queries into an automatic data class.

1: https://gitlab.com/AnimusDesign/kotlin-frm

While the scenario you present is a legitimate reason for using a query builder, it doesn't justify the investment in using a query builder for the vast majority of db calls. The edge case is not an appropriate goal to impose the cost of using a DSL for the majority of cases. Instead, custom roll an implementation for those complicated calls and stick with a parameterized sql library.

> While nice to have, preventing bugs with static SQL is usually easy to do by writing a few tests

Why not both?

Reply on news.ycombinator.com