Agreed. Helpers (and indeed types) can make working with SQL an actual pleasure. You do need to learn the SQL, though. (My TypeScript/Postgres solution, in this vein: https://github.com/jawj/mostly-ormless/blob/master/README.md).
I love how it let's you use SQL, while taking full advantage of TypeScript's wonderful typing system to give you intellisense and compile-time checking. Reminds me a bit of the SQL type provider for F# (which I was amazed by when I first saw it in action).
I really like the way the readme has been written too - it gives a real insight into the thought processes that led to the final result.
Good news. :)
Maybe we can agree that typegen smells less than codegen?
Reminds me a bit of the SQL type provider for F# (which I was amazed by when I first saw it in action).
I should look that up — sounds interesting.
I really think typescript would benefit from a good solution to this.
I am old fashioned. I like to start with the database schema and generate code from that. I make a change in the schema I regen the code. Thanks for partial classes in C# I can persist customizations between code-gens if necessary.
What just blew me away is the thing with the `JOIN` and the `to_jsonb(authors)`, all with complete typing support for the nested author object. I was actually looking to use a classical, attribute driven query generator (with the sort of chaining API everyone is used to: `tableName.select(...coumns)` etc.) for my next project involving to maybe replace/wrap/rewrite a Rails app and its ORM with Typescript and Node. Maybe I'm trying this instead I'm already half sold. Just worried about forcing colleagues having to learn SQL instead of using a fancy wrapper.
> Just worried about forcing colleagues having to learn SQL instead of using a fancy wrapper.
My current team is pretty junior, and I don't see any problem with this. Simple SQL queries are really easy to learn, and complex queries are harder to understand with ORMs than in raw SQL.
Moreover, knowing SQL is a useful, marketable skill that will stay relevant for many years to come. If there's some resistance, I can easily convince the team that going this route will benefit them personally.
Back to the README, there are two questions I'd like to see addressed:
1. Whether `Selectable[]` can be used to query for a subset of fields and how.
2. In the `to_jsonb(authors)` example, what would you get back in the `author` field if there were multiple authors with the same `author.id` value? An array of `author.Selectable` objects? This part is awesome but brittle, isn't it?
I would love to see this move forward! I will definitely play with it and consider it for my next project.
Your second case, if I recall this correctly (ActiveRecord made my SQL skills fade away), this plain JOIN would just return a row with the same book but a different author. `to_jsonb(authors.*)` is just operating on a single row. But what you want is possible (aggregating rows into a JSON object) by using `jsonb_agg`. Whether the lib supports inferring the correct typings for that is another question though.
:)
1. Whether `Selectable[]` can be used to query for a subset of fields and how.
Right — this is not (currently) supported. I guess if you had wide tables of large values, this could be an important optimisation, but it hasn't been a need for me as yet.
2. In the `to_jsonb(authors)` example, what would you get back in the `author` field if there were multiple authors with the same `author.id` value? An array of `author.Selectable` objects? This part is awesome but brittle, isn't it?
Multiple authors with the same id isn't going to happen, since id is intended as a primary key, so I'd argue that the example as given isn't brittle. On the other hand, there's a fair question about what happens for many-to-many joins, and since my use case hasn't yet required this I haven't given it much thought.
type authorBookSQL = s.authors.SQL | s.books.SQL;
type authorBookSelectable = s.authors.Selectable & { books: s.books.Selectable };
const
query = db.sql<authorBookSQL>`
SELECT ${"authors"}.*, jsonb_agg(${"books"}.*) AS ${"books"}
FROM ${"books"} JOIN ${"authors"}
ON ${"authors"}.${"id"} = ${"books"}.${"authorId"}
GROUP BY ${"authors"}.${"id"}`,
authorBooks: authorBookSelectable[] = await query.run(db.pool);
I'll update demo.ts and README shortly.
I'd argue that learning SQL is essential for any developer.
It's also a "reusable" skill that will stand them in good stead for decades - whereas learning how to use the fancy wrapper is only useful until the next new shiny comes along.
The long-standing ORMs do a pretty decent job of writing efficient queries these days though. You can go pretty far without knowing much and that’s not a bad thing either.
That lastpost you can map a half dozen Java frameworks to each of the acts.
Personally I never found an orm that tracked which attrs in objects were actually mutated so that only mutated columns would be updated/inserted, but again I never did a lot of orm.
const existingBooks = await pool.exe(select("books", { authorId }));
or
const existingBooks = await pool(select("books", { authorId }));