As an example, the sqlalchemy docs[0] make this very clear: there's an ORM, but there's also just a core expression library that simply helps you connect to the database and construct queries.
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.
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.
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 }));
However, I've never understood why people write this mapping code manually. I believe in code generation tooling as a potential solution for this (where types and maybe a full data access API is auto-generated based on the database schema).
What's the big difference? Why do you like the former but not the latter? What are the characteristics of the former that makes it distinct from the latter?