4 ms·
There is a big difference between just writing helper functions to construct SQL and convert data types, and OO-style magical auto-persisted objects. The latter
by kccqzy 7y ago
There is a big difference between just writing helper functions to construct SQL and convert data types, and OO-style magical auto-persisted objects. The latter is what I don't like about ORMs but the former is fine. I feel that this is an important distinction to make.
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.
[0]: https://docs.sqlalchemy.org/en/13/ https://docs.sqlalchemy.org/en/13/
- moring 7y agoQueryDSL (http://www.querydsl.com/ http://www.querydsl.com/) does something like this for Java. It can generate classes from tables, but even with those, all queries / statements that hit the database are manually built using a query builder to avoid syntax and type errors. I.e. no caching or automatic database updates.
- nikolasburk 7y agoknex.js is another example of such a query builder library in the Node.js ecosystem
- gmac 7y agoAgreed. 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 https://github.com/jawj/mostly-ormless/blob/master/README.md).
- timmy-turner 7y agoWow this is great! Very well written README. 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.
- kilburn 7y agoI'm also awed by this! > 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.
- timmy-turner 7y agoRight, only querying a few fields seems not to be a builtin feature. Looks like you have to create the partial selectable type yourself and there is no support to typecheck that the correct columns in the select are included. 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.
- gmac 7y agoI'm also awed by this! :) 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.
- GordonS 7y agoI've never been a fan of codegen, but I think I could get past it for this library - it looks great! 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.
- gmac 7y agoI've never been a fan of codegen, but I think I could get past it for this library - it looks great! 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.
- victor106 7y agoThis looks great. It looks similar to JooQ.
- Attained 7y agoI worked for a bit on a code gen based typescript postgres builder, but haven't had time lately to build it out - https://github.com/Sammons/morbid https://github.com/Sammons/morbid I really think typescript would benefit from a good solution to this.
- ThinkBeat 7y agoI do like the code-gen solution a lot. You can create code that is far less bloated than a generic framework. 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.
- AtlasBarfed 7y agoI'm not even someone that has used multiple orm styles extensively, but it is disturbing/darkly humorous how many orm libs there are. 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.
- deleted 7y ago[deleted]
- liquidify 7y agoI really like this. There is only one thing that bothered me. I'd rather pass the job to the pool than pass the pool to the job... something like... const existingBooks = await pool.exe(select("books", { authorId })); or const existingBooks = await pool(select("books", { authorId }));
- nikolasburk 7y agoFully agree! Some mapping code is _required_ in your application, otherwise it wouldn't be able to talk to your database at all. 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).
- thymanl23 7y agoYeah, ORMs have grown to mean more than mapping relational data to objects. An example of "just" this mapping can be seen in PureORM[0]. [0]: https://github.com/craigmichaelmartin/pure-orm https://github.com/craigmichaelmartin/pure-orm
- runeks 7y ago> There is a big difference between just writing helper functions to construct SQL and convert data types, and OO-style magical auto-persisted objects. The latter is what I don't like about ORMs but the former is fine. I feel that this is an important distinction to make. 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?