5 ms·
I increasingly lean towards plain SQL over ORMs. It requires greater familiarity with SQL but I prefer that over greater familiarity with ORM-specific syntax th
by divbzero 6y ago
I increasingly lean towards plain SQL over ORMs. It requires greater familiarity with SQL but I prefer that over greater familiarity with ORM-specific syntax that doesn’t translate across frameworks or languages. In addition, you can prototype new queries and profile existing queries in the database and copy-paste directly into your code.
- jsmeaton 6y agohttps://github.com/charettes/django-seal https://github.com/charettes/django-seal Can help with these kinds of issues though I haven’t yet used it myself (I should!)
- zem 6y agofor me the value of an ORM is not so much query synthesis as serialisation of object fields to db columns and vice versa
- jtolmar 6y agoSame here. I'd love to have a tool that just generates an object type for a given SQL query's result rows, and a function signature for its query parameters.
- branko_d 6y agoThis! We don't need ORM, we need OQM (object-query mapping)!
- reader_mode 6y agoF# has this + static SQL query string checking at compile time using type providers
- grosskur 6y agosqlc can do this: https://sqlc.dev/ https://sqlc.dev/
- jtolmar 6y agoThis is the nicest one of these from the ones posted, I think it's the model to copy.
- PeterisP 6y ago"just generates an object type for a given SQL query's result rows" For a similar purpose in Python I generally use psycopg with namedtuple resultsets; the namedtuple 'records' do what I need for the returned data and are reasonably efficient.
- aabbcc1241 6y agoJust hack that for Typescript over this weekend (: https://news.ycombinator.com/item?id=26211286 https://news.ycombinator.com/item?id=26211286
- jtolmar 6y agoWow, that was fast! I'm impressed. If you want to keep going forward with it, I'd recommend working on making it clearer what the examples do, and looking at sqlc for Go for inspiration. Of the existing versions of this people replied with, that one had the clearest API, or at least clearest explanation on its landing page.
- jtdev 6y agoThat’s a huge dependency to take on just for mapping and serializing, something that most languages provide in a standard library or base functionality of the language itself.
- acdha 6y agoI favor a hybrid approach: use the Django ORM to define models, do migrations, auto generate the admin, etc. but don’t be shy about using the extension points (extra, raw, cursors) to put in an optimized query for a hotspot. You can get pretty far using the ORM but it’s really valuable to be able to be comfortable dropping down for things like reports or bulk processing.
- Jaxtek 6y agoI use ORM for Django's auth system alone.I write all other REST API queries in plain SQL.
- fn1 6y agoIn the java-world there are libraries like JDBI, which makes it possible to write interfaces with SQL-annotations and have serialization, connection setup etc. done for you: public interface UserDao { @SqlUpdate("CREATE TABLE user (id INTEGER PRIMARY KEY, name VARCHAR)") void createTable(); @SqlUpdate("INSERT INTO user(id, name) VALUES (?, ?)") void insertPositional(int id, String name); @SqlQuery("SELECT * FROM user ORDER BY name") @RegisterBeanMapper(User.class) List<User> listUsers(); } This is great because it's explicit. No hidden queries.
- shock-value 6y agoAnother great option in Java is jOOQ, which lets you write type-safe and potentially composable queries such as: context .update(User.USER) .set(User.USER.NAME, userName) .where(User.USER.ID.eq(userId)) .execute()
- alex-lx 6y agojOOQ and its DSL is good, however IMO it's more readable using raw SQL (by using `context.fetchInto` and its variants) than to using DSL when deal with complex query.
- jwdunne 6y agoI’ve noticed this in other contexts too. For small queries with straight forward joins, a query builder is nice and readable. But for larger, more complex queries, I found putting the query into its own file was best for readability.
- lukaseder 6y agoWhy not just create views and query those with jOOQ, then?
- alex-lx 6y agohi, @lukaseder, Thank you for creating jOOQ. I don't have much experience on views, and some called `Best Practice` forbid to use that because it's hard to maintain. What do you think about that.
- paulmd 6y agoif that's your bag then you can still totally do that with an ORM, hibernate for example lets you just write a whole query in raw SQL while still getting all the benefits of eliding a bunch of boilerplate field copying, having "active" objects with ORM-level update/transaction management, etc. plus it means you don't have to write all the dumb "select * from books where bookName = :bookName" code that obviously can be handled trivially by the ORM. You just use SQL the places where it makes sense. the ORM hate always strikes me as a little misplaced because of this - you can always write SQL where it's appropriate, in any decent ORM. And you can write bad queries in SQL too. Obviously very complex queries are maybe better reserved for raw SQL but it seems like a lot of the hate comes from maybe less experienced engineers getting in over their head with complex work items because ORMs "make it easy", and that is going to happen with raw SQL too if you throw those same engineers at those work items. ORMs aren't inherently that heavyweight, I see people complaining here about SQLAlchemy and as a Java developer I don't have performance concerns about hibernate. That sounds to me like a Python problem and a "this specific ORM isn't performant" problem, not ORMs being bad as a whole. And if you really want a "just load the data for me and do nothing else that incurs a performance hit" approach then you can use stateless objects and it's just a wrapper around the DB to load and transform the data for you and/or do a raw, whole-object update back to the DB.
- alexchamberlain 6y agoWe're gradually removing our ORM usage to avoid active objects. They look too much like VSTs, but subtly make foreign network calls when you least expect it, which makes it very hard to write effective, isolated tests or replace the database layer by something else, like a service call or a cache lookup.
- kaba0 6y agoI’m sorry if it sounds harsh, but that is the mistake of the developers than. I think you would not allow anyone to write production code without knowing the language; it should be the same way with most libraries, especially ones having a big reach, like ORMs. Nonetheless, I have seen it countless of times in the team I worked in — so it is unfortunately really frequent.
- lordnacho 6y agoIMO both are required. I'm lucky in that I did a lot of plain SQL early on, and found ORMs later, but I think ORMs do cut out a lot of time for quick-and-dirty queries that end up not being the bottleneck. The problem arises once you find a bottleneck, you won't know how to optimize it if you haven't done a bit of SQL mucking about earlier. Also, the big thing is you won't know how to translate to other ORMs if you don't know SQL. Eg once you know that you want an index, it's a matter of a web search to find out what the syntax is in your ORM. But if you just started with ORM, you might not realize that kind of thing is part of how it works.
- kaba0 6y agoYeah, ORMs are specifically made for insert-heavy operations or very basic mapping of rows to objects. Analytical queries and the rest should be done with SQL.
- jtdev 6y agoI keep seeing some variation of: “ORMs are really intended for x” or “ORMs work best when y is true”. At some point we should take an honest look to determine if ORMs provide enough benefit to justify their existence. “I can talk to the database without knowing SQL” doesn’t cut it.
- kaba0 6y agoYes they do? They are basically available in every language, in some ecosystems there are multiple alternatives as well and there would not be that big of a market unless it is useful.
- stiray 6y agoI agree with you. ORMs are at the end "converting" complexity of SQL capabilities into objects. It is perfectly normal that in best situation they will get at least as complex as SQLs but with features hidden behind annotations or be less efficient. SQL is readable (at least far more than 20m lasigna of boilerplate objects decorated with tons of annotations googled from internet where no one really know what they do) and you KNOW that if you have optimized the database structure (and filesystem, and network,... :D) and SQL statement you will get peak performance while with ORMs you are on constant hunt what else can you turn on while they are far too huge to read their code. And they are becoming quite absurd after they "mature" and begin adding corner cases that no one has thought about when they were a starting project.
- deleted 6y ago[deleted]
- girvo 6y agoPlain SQL with a “hydration” system to objects is quite powerful. DataMapper, I believe is the term of art?
- specialp 6y agoI like ORMs for doing the things for me like making objects and converting dates and other data to DB ready form and vice versa. I do not like them querying. The premise with ORMs is that you should be able to query pretty much anything easily. The reality is that almost all of the time you are doing something you don't have many distinct queries. You have a few reads and writes that you have to do. Things that are easily done in SQL with full expressiveness and efficiency. So now my pattern is to use for example ActiveModel in Ruby for the models, but not ActiveRecord for the persistence part.
- kevstev 6y agoYeah in my last job, I used my manager power to overrule on using an ORM. In every company I have ever been in, some major outage has occurred due to a seemingly innocuous change causing a DB operation to go from O(N) to O(N^2)+ and its really hard to track down because things seem to work fine in a dev environment, but then can't scale. And to me, SQL is the easiest language to learn and read of all, and while I understand doing basic marshalling of SQL records into objects is tedious, its really not hard at all, and it just saves so so much heartache down the road. The exception might be very basic CRUD apps that are meant to be used by third parties and want to support multiple backends like mysql/postgres/whatever. There might be other exceptions as well where you are just trying to prototype/find market fit, but for a typical project that you know is going to be used in a real way, the risks just don't outweigh the benefits IMHO. Some of the team griped a bit, but I absolutely think it was the right move in hindsight. At least once a year I would hear about an outage due to an ORM gone wild, and these outages were usually prolonged by the fact that things function, and then there is finger pointing between the DB/DBAs and the app developers, etc.
- wirrbel 6y agoSQLAlchemy expression language is cool stuff. not an ORM but also no string-templating ...