This style is how I’ve long imagined a “perfect ORM” should look like.
Similar libraries in other languages:
Huh. At first glance, PugSQL looks very similar to my old dpdb module.
The compile time validation of SQL by validating queries against a live DB is an extremely cool idea. So far I’ve only seen this in Quill before. Most IDEs do this as well, they connect to a live DB giving runtime validation.
Seems hug/pugsql do code generation from SQL files, which is a different direction, but also really cool.
I don’t like ORMs, because they obscure the underlying SQL. I wouldn’t call the thing linked in the submission nor the libraries linked by you ORMs though. Here you write your own queries… I think? In the case of PugSQL it seems to map SQL queries onto Python’s object model? I really don’t understand what’s happening here.
In the case of the other two you seem to be left on your own with writing your SQL queries as strings and using string interpolation to fill in any values known dynamically as well as escaping them properly. Looks ugly and just begs for SQL injection.
I’ve been learning Lisp and stumbled upon SxQL which seems to strike a nice balance. The queries are written in the language you write your program, but still have an obvious translation to SQL (well, they look almost exactly like SQL). No string interpolation necessary.
The other two being SQLx and hugsql? I don’t know anything about hugsql but SQLx does the same “prepare statement, bind variable, execute statement” procedure you’re used to.
Then you get to refer to the fields of the returned struct as if you’d constructed the appropriate struct for the returned relation in a type safe fashion. Obviously there’s no string interpolation required anywhere.
From the examples in README.md:
let row = sqlx::query("SELECT is_active FROM users WHERE id = ?")
let countries = sqlx::query!(
"SELECT country, COUNT(*) FROM users GROUP BY country WHERE organization = ?",
So it looks like this library does the string interpolation which has the benefit that you (the user of the library) don’t have to bother yourself with properly escaping the values inserted into these strings. But it still looks ugly and foreign to the language you write your program in.
I’m only somewhat sure of this but this is the equivalent of doing PREPARE anon_statement(x,y,z) AS SELECT blahdiblah etc. etc. and then doing EXECUTE anon_statement(valx, valy, valz) i.e. it’s the DBMS doing the escaping for you not the library. The library just transmits it there.
PREPARE anon_statement(x,y,z) AS SELECT blahdiblah etc. etc.
EXECUTE anon_statement(valx, valy, valz)
Genuinely not upset about the SQL looking like SQL thing. I think that’s fine.
Cool, it understands Postgres’s parameterized queries, if I’m not mistaken.