20 ms·
To ORM or Not to ORM
- slaymaker1907 8y agoAn ORM is a technology that has the problem of trying to make easy things easier (CRUD) while making difficult things more difficult (complex joins, SQL lock management, indices, etc.). Why spend so much $$$$$ on a fancy database if you are going to make 70-90% of its features impossible to use?
- Johnny555 8y agoWhy spend so much $$$$$ on a fancy database You need to answer that question first. What feature do you really need from that fancy database?
- collyw 8y agoA large proportion of devs seem to struggle with basic joins.
- slaymaker1907 8y agoFine grained control over locks, transaction levels, JSON indexing, etc. In fact, some databases (older versions of Postgres, Oracle, etc.) do not actually have true serializability so explicit locks may be the only way to implement certain logic correctly. I picked on explicit locks since it is quite useful as well as being generally poorly supported by ORMs.
- kasey_junk 8y agoOne thing I really miss from the JVM world was something like jOOQ. I don't want an ORM (like specifically the object relational mapping stuff) for most of my use cases but I do want an abstraction above text for interacting with SQL. gorm's sql builder is alright but something better would be really nice.
- kodablah 8y agoAgreed. Really, just an open/transparent SQL builder part and an uncoupled row scanner that can populate structs reflectively is the perfect middleground to work around the impedance mismatch that inevitably occurs using these magic ORMs.
- cryptonector 8y agoI don't even want an abstraction for building SQL unless I need to build queries dynamically from some other (presumably simpler) query language. Put all the SQL in .sql files and make all queries parametrized. Or wrap all queries in VIEWs or functions at the RDBMS. This makes maintenance much easier.
- dragonwriter 8y ago> Or wrap all queries in VIEWs or functions at the RDBMS. This is like the oldest best practice for RDBMS use, for security, and for decoupling consuming apps from each other and the DBs low level implementation (so that app views and base tables can evolve independently to the extent possible), and for maintainability: all app access to the DB should be through views adapted to the apps needs.
- cryptonector 8y agoExactly. And it works very well. I recommend PostgREST to export a RESTful interface to a single PG schema's VIEWs and functions, and RLS and INSTEAD OF triggers as needed.
- chatmasta 8y agopsycopg2 is good for this in python.
- luhn 8y agopsycopg2 is a Postgres driver, it is not a query builder like jOOQ. Python's equivalent would be SQLAlchemy Core.
- ordinaryperson 8y agoSample size one 1 but ORM drives me crazy. Why can't we just use SQL? How does it save time when I have to learn the ORM language, which probably has a lot less support and users? The OP here says it "reduces boilerplate" -- rarely have I created an application and thought its biggest problem was too much boilerplate. But everyone at work loves them so I must be wrong somehow.
- toast0 8y agoAll the time allegedly saved is also more than compensated for when the ORM makes bad queries that are roughly impossible to fix.
- topspin 8y agoFrequently the easiest path to perform a complex query with an ORM results in N+1 performance disasters. The inevitable retort is something like "but the ORM has better ways to do that." Of course it does; ORM implementers aren't incompetent. The key word is "easiest," meaning the ORM user doesn't have to spend time learning anything beyond simply chaining method calls. In production such work goes haywire when resolving some related entity causes tens of millions of round trips. Without an ORM the easiest path is to write a single join, giving the database optimizer a fair shot at correctly optimizing the query, including over time as the database evolves. Again, yes an ORM can encapsulate the equivalent query. Guess what; for every time these ORM capabilities are actually utilized there are probably hundreds of expediently written N+1 queries because the programmer either didn't know better or was indifferent. To a pragmatic mind this is the inevitable outcome when a tool makes the inefficient solution the easiest thing to do.
- ethbro 8y agoI look at this as not playing to core competencies. SQL's strength is that it's declarative. By wrapping an ORM around it, suddenly you have a DB engine talking to an ORM engine through a declarative interface. There simply isn't enough information and context passed across the interface to properly optimize. So in the end, it's like trying to run two optimizing compilers in series, where either knows the specs of the other.
- syntheticcdo 8y agoSomewhere in the middle is best for me. Not quite ORM, not quite raw SQL, but a query builder. In the Node world knexjs.org fits the bill.
- frosted-flakes 8y agoI agree with that sentiment. Concatenating strings is miserable and error-prone, especially for highly dynamic queries. But there's still another step between a full-blown ORM and a query builder, where you still define the models and the relations between them, but fall back to a plain query builder for constructing queries. That way, you're not dealing with plain arrays, etc., but you're still essentially writing SQL. Like Objection.js[0], which is built on Knex. [0] https://vincit.github.io/objection.js/ https://vincit.github.io/objection.js/
- simplify 8y agoWhat's wrong with working with plain arrays? Genuine question, they seem to work fine for me so far.
- hebrox 8y agoShameless plug: if you're using TypeScript, checkout https://www.npmjs.com/package/@wwwouter/typed-knex https://www.npmjs.com/package/@wwwouter/typed-knex I love Knex, but was missing the type safety, so I created a TypeScript wrapper around it. If you look at the code, you can see I started out as a C# developer :) It's being used in production for a few projects, but it would be great if more people started using it. Feedback is more than welcome!
- ccarse 8y agoI want to write sql but have my library map between my languages built-in types and my sql types. I feel like something like Dapper is the correct abstraction for communicating with a database.
- jp_sc 8y agoRelevant Keynote by DHH https://www.youtube.com/watch?v=zKyv-IGvgGE https://www.youtube.com/watch?v=zKyv-IGvgGE (tl;dr: ORMs are the best)
- vbsteven 8y agoBoth... I prefer a hybrid approach when it comes to ORM's. In most projects I will use an ORM to avoid boilerplate for simple entity queries (save, findByid, findByX, delete) and converting a database row to a class instance but I avoid mapping complex relations. An ORM like Spring Data with JPA/Hibernate in the Java/Kotlin world works well for this. I write my db schema by hand, create an entity class with the same fields and an empty repository interface extending CrudRepository. This gives me simple CRUD access to the table with almost no code. When I need complex queries I inject a JdbcRepository which allows me to query the DB using standard SQL and a RowMapper lambda. Best of both worlds.
- mindcrime 8y agoYep, the Spring Data CrudRepository interface based stuff is an amazing time saver. Just extend an interface, and BOOM, you've got the basic CRUD operations ready to go. And then you just use HQL in annotations for more specialized queries. Other people's mileage may vary, but I've found this to be a tremendous boon.
- vbsteven 8y agoAnd there is more: * you don't have to use HQL, if you pass `nativeQuery=true` in the @Query annotations you can write standard SQL instead of HQL. * You can add an AccountRepositoryImpl Bean so you have a class in which you can inject dependencies like JdbdTemplate for full JDBC database access.
- hugi 8y agoI believe that People who hate ORMs haven't used a good ORM, so what they really hate is the tool, not the concept. I like using my ORM of choice when it's appropriate, I like just using SQL statements when it's appropriate. Choose the tool appropriate for the task.
- mindcrime 8y agoAgreed. And FWIW, I've written some moderately complex apps over the years, and routinely use Hibernate / JPA (or GORM when using Grails) and I've never, or very rarely, encountered most of the problems cited by critics of ORMs. And Hibernate makes it easy enough to call raw SQL when you need to (and yes, I have needed that facility a few times). By and large, for the vast majority of projects that I can imagine working on, my default starting assumption is almost always going to be "use an ORM" -- unless something about the situation strongly dictates otherwise.
- strictfp 8y agoI've used many ORMs. I don't hate them, but I think their net utility is negative. While you initially might think that interfacing with the db is going to be very tedious and labor intensive, it usually turns out to not be that bad. Battling the ORM to make it do what you want can on the other hand be very tedious. When you have the SQL in the code it's much more obvious what the performance profile and potential concurrency problems are. That's very important to solve real problems which arise in almost all projects with some scale. If I were to choose my tools, I would pick some sql-based schema versioning system, and potentially a simple data mapper for moving data back and forth between db records and entities. But I would be very reluctant to use a full-blown ORM nowadays.
- hugi 8y agoI tend to disagree. To me a good ORM totally (ok, not totally, but as totally as possible) abstracts the database away from your code. That's it's purpose. You just want to work with data. You want to fetch data, persist data and delete data - you don't really want to care about how that's done. That's where a good ORM can really help you out. However, if the ORM gets in your way, either because of API complexity, performance issues or other reasons, and you start to "battle" it as you said, you immediately move out of that context and start thinking in a more data oriented context. That's where SQL comes in. As I said before, I think it's a question of using the appropriate tool. I've written hundreds of applications using multiple different ORMs and just plain SQL—and I'll tell you I'll pick a good ORM over raw SQL for basic CRUD operations every time. However, I'll give you that I'll pick plain SQL over a bad ORM any day.
- mekane8 8y agoLearning and using an ORM is not an excuse for not learning and knowing SQL + Database concepts. (I'm not saying the author argued that, I just want to sweep away an straw men that say not having to learn SQL is an argument in favor of using an ORM). For me the biggest benefit of using an ORM is separation of concerns. I truly hate it when SQL and query building get mixed into controllers and other parts of the application. However, I also hate the layered complexity that the author talked about, which definitely means ORM's are not appropriate for all use cases! But if you don't use an ORM you need to be diligent with using some other pattern and code organization to keep your code clean and your concerns separated. I worked at a consulting company for a number of years and we got immense value out of Symfony, which includes the Doctrine ORM. Many of our projects boiled down to custom Content Management Systems, so we got the huge benefit of being able to define the data model in code and not worry about any of the SQL while also not having very complex queries. So we got pretty much maximum benefit and minimal exposure to the down-sides. I will say that these systems almost always wanted some detailed reporting, and that I usually got tasked with writing them. They always had some complicated queries and I almost always made use of the "raw sql" option to just write my own queries and spit out CSV. Which was always a much different use case than the rest of the application.
- fareesh 8y agoIt's nice to have an interface that runs my query and puts the results into a nice typed structure. If the query itself is an object, I can chain it with other query objects and have a cleaner syntax. Those are my usual reasons for liking ORMs.
- henning 8y agoI wish people did not think the choice was solely between "write raw SQL with raw strings" and "try to pretend the database is object-oriented when it is not." The third approach is to safely wrap the database and its columns with code in a way that is composable. In Python, SQLAlchemy has an ORM, but it is optional, and you can just work with tables and columns if you want.
- wvenable 8y agoThe real third approach is that you can safely pretend the database is object-oriented for manipulation and simple lists and still use SQL for complex queries. Most ORMs let you safely mix and match both methods easily. This ORM or not ORM is the wrong question. Use an ORM to save you headaches where it's appropriate and use direct SQL when it's not.
- mattwad 8y agoI use this approach with Mongoose in Node.JS. If you want to update a user document, we always query the entire document, set the field, and call .save(). This triggers all kinds of very useful validation hooks and is easy to think about. But if you want to find a series of users, or build an API for a specific front-end form, I've found the ORM just gets in the way, and we have not had any trouble writing db queries inline.
- wyattjoh 8y agoWe used to use Mongoose in our project[0], but found that at the volumes of interactions we were dealing with, it was orders of magnitude faster to just use the raw Node.JS driver. [0]: https://github.com/coralproject/talk/tree/next https://github.com/coralproject/talk/tree/next
- jweir 8y agoYou are absolutely correct. Using both is a very valid options. We use ActiveRecord a lot, and then have custom SQL queries using `find_by_sql` for very complex, optimized joins. It works very well. Rails gets out of the way when we need it to.
- 0x70dd 8y agoI've wanted to like ORMs but they always get in the away as requirements start to get more complex. On the other hand, I like writing in plain SQL, but that's often hard to comprehend by other people - you cannot just glance through to know what's happening under hood. I find Ecto to be the perfect balance of expressiveness, flexibility and clarity. In fact it's the single reason to have picked up Elixir.
- jtfairbank 8y agoHave to agree. been really enjoying Elixir / Ecto / Phoenix. I find Ecto lets me be as expressive as I need to with Queries, and handles the mapping into models very well for the 90% of easy cases that I need it to.
- localhostdotdev 8y agorails g model Post published_at:datetime title content:text rails g model Comment post:references author published_at:datetime content:text rails g model Tag name rails g model PostTag post:references tag:references class Post < ApplicationRecord has_many :comments has_many :post_tags has_many :tags, through: :post_tags end class Tag < ApplicationRecord has_many :post_tags has_many :posts, through: :post_tags end from there it's just regular rails: Tag.find_by(name: "something").posts Post.joins(:tags).where(tags: { name: "something" }) Tag.create(name: "something") Post.create(...) Tag.first.posts << Post.all.sample(2) made a little repo if people want to play with it: https://github.com/localhostdotdev/bug/tree/orm-or-not-orm https://github.com/localhostdotdev/bug/tree/orm-or-not-orm
- davidcuddeback 8y agoYou forgot to add an index on tags.name and null constraints on the rest of the columns. > from there it's just regular rails That's the problem. Examples like this focus on the first few minutes of development. Not the subsequent years of maintenance.
- localhostdotdev 8y agoI stayed close to what the author did. adding an index: add_index :tags, :name adding a null constraint: change_column :tags, :name, :string, null: false validates :name, presence: true # for proper validations https://github.com/localhostdotdev/bug/commit/3765237008c36c539b30cbc8ad4127c14b0e546b https://github.com/localhostdotdev/bug/commit/3765237008c36c...
- davidcuddeback 8y ago> I stayed close to what the author did. Fair enough. I’ll concede that wasn’t a fair comparison to the OP. My point remains that examples focusing on the initial setup are uninteresting (to me) because that’s not where the bulk of the work is.
- 8y ago
- ilovetux 8y agoI like the Django ORM. This is likely for two reasons: 1. I only use Django for small use cases where I rarely see any sort of scope creep. There was no real conscious decision about this, just kind of the way it happens. 2. The Django ORM is fairly mature and makes it quite easy to get a small project out the door. I regularly use SQL directly at work and wouldn't want to try to replace any of it with an ORM even on new projects because we are all just used to working with it and we wouldn't want to add any complexity to our workflow without real, tangible benefits. I would not, however, be opposed to working on a larger Django project (ORM and all) if the opportunity presented itself.
- marcosdumay 8y agoFor me, the best feature of the Django ORM is that it integrates with everything all the way to the frontend forms. In fact, missing the database integration wouldn't even be that large loss. Sometimes I wonder if it isn't even holding the framework back.
- ilovetux 8y agoI agree the ModelForm class is great.
- lukifer 8y agoAs true now as it was 12 years ago: ORMs are the Vietnam of computer science. http://blogs.tedneward.com/post/the-vietnam-of-computer-science/ http://blogs.tedneward.com/post/the-vietnam-of-computer-scie... (tl;dr: there is an impedance mismatch / leaky abstraction between relational data and OOP for all but the most trivial use cases, guaranteeing that whatever problems an ORM might solve will be traded for new ORM problems instead)
- zzzeek 8y agoI wonder how people of Vietnamese heritage would feel that you are characterizing their country as synonymous with war and disaster? IIUC Vietnam had been a relatively peaceful country for almost 50 years. Not coincidentally ORMs have been doing great since that silly article was written so many years ago.
- bkq 8y agoPersonally I think an active record style ORM for Go like gorm is a poor fit for a language that doesn't come across as inherently OOP. Going through some of the documentation for gorm, it seems to rely heavily on method chaining which for Go seems wrong considering how errors are handled in that language. In my opinion, an ORM should be as idiomatic to the language as possible. I've used sqlx[1] before, and it feels pretty idiomatic to Go. You tag your structs with their respective database columns, write up a query, and hand it to sqlx to perform the deserialisation of the data. I've also come across squirrel[2] too, though I haven't used it, it does look rather interesting. [1] - https://github.com/jmoiron/sqlx https://github.com/jmoiron/sqlx [2] - https://github.com/masterminds/squirrel https://github.com/masterminds/squirrel
- gen220 8y agoFor my 2¢, We use squirrel at work (I haven't touched it myself though). People here seem to like it. It's not an ORM per se, but it seems to occupy the sweet spot you're describing. It takes away some of the more tedious parts of using SQL, but allows you to still reason about what's happening under the hood without committing tons of documentation to memory. Suits the language quite well, I think!
- d0100 8y agosqlx + squirrel: func dbAllPostsInTag(db *sqlx.DB, tagID int64) ([]post, error) { posts := []post{} query := squirrel.Select("Post.postID, Post.published, Post.title, Post.content"). From("Post"). Join("PostTag using(postID)"). Where(sq.Eq{"tagID ": tagID}) qSQL, args, err := query.ToSql() if err != nil { return nil, errors.Wrap(err, "Error generating dbAllPostsInTag sql") } err = db.Select(&posts, qSQL, args...) if err != nil { return nil, errors.Wrap(err, "Error listing dbAllPostsInTag") } return posts, nil }
- podliy16 8y agoActually, what you have done are both ORM. So the question is "To ORM with an external library or hand made ORM".
- khana 8y agono orm. just jooq it.
- deleted 8y ago[deleted]
- philjackson 8y agoMy preference has been this sort of tool: https://github.com/krisajenkins/yesql https://github.com/krisajenkins/yesql
- dkersten 8y agoOr HugSQL, which is similar, but actively maintained. Coupled with Clojure's data-centric focus, there's no need for a traditional ORM and you get to work directly in SQL. Yesql and Hugsql made me enjoy using databases for the first time ever.
- rspeele 8y agoI put a ton of work into an ORM that statically typed SQL a couple years ago. I always thought this would be a cool way to go. https://github.com/rspeele/Rezoom.SQL/ https://github.com/rspeele/Rezoom.SQL/ But I never got to use it at work, and thus lost interest. The biggest thing missing with it was that you lost type safety if you had to dynamically build a query. These days its main problem is lack of compatibility with .NET Core, which somebody else was working on for a while. I think the flat row model for querying data from related tables is just not very good. I'd MUCH rather work with an object model like LINQ, where I get a list of Foos and each one has foo.Bars nested within it. As opposed to the output of a join where I get one row for each Bar, and parent Foo's columns are duplicated across each row. Entity Framework does an excellent job of mapping the LINQ model to the SQL one, but since they really are different under the covers, it's easy to produce hefty queries. For example a LINQ "group by" frequently cannot be translated to a SQL "group by". I tried to keep it simple and not deviate too far from straight SQL. But I still didn't want to force processing those damn row-based outputs onto the programmer, so I at least had to add notation for breaking out the columns of a top-level result set into an object hierarchy: https://rspeele.gitbooks.io/rezoom-sql/doc/Language/NavigationProperties.html https://rspeele.gitbooks.io/rezoom-sql/doc/Language/Navigati...
- chusk3 8y agoFor my money your type provider was the single coolest one I've seen before. The ahead of time query execution and planning is a killer feature, and for us the only gaps were either process related, meaning we did not have a good out-of-band migration pipeline, or some constructs that were not understood by your type provider.
- default-kramer 8y agoI really like Rezoom.SQL even though I haven't done a deep dive yet. But that Navigation Properties syntax is enlightening... I just realized that my idealized query language (WIP) needs something similar.
- Risord 8y agoGreat work. I used rezoom in couple of minor projects and like most that I could really trust that query runs if it compiles. Unlike in Entity Framework where you have unlimited possiblities write code which compiles just fine but crash runtime. In principle I don't like an idea that your primary language get compiled to SQL due it's very leaky abstraction. Instead I like expressing queries as data (ideally compile time checked). In rezoom query composition part wasn't the strongest part. When for example some model needed additional join you had to modify N queries. This kind of composition could be done of course with dynamics but like you say, you lose biggest selling point: compile-time safety. However these compositions are usually all known at compile time so you should be able to just build them before rezoom checks them. But out of the box compile time programming in F# is not there yet...
- kevin_thibedeau 8y agoI wouldn't have pegged Eli as the gormless type.
- hardwaresofton 8y agoWhile I learn towards not using an ORM, the productivity gains (at the very least early on in development) are undeniable. What I've always looked for are frameworks that give you an ORM but also make lower level queries very easy, normally via a query builder, allowing you to go back and forth between levels of abstraction. If I had to choose, I prefer libraries that give you the lower level of abstractions first and then build upon those to offer an OOP-based ORM approach (which is what I believe most people think of when they say "ORM"). It's a spectrum to me: raw (parametrized) queries ------ query builder + serialiation/deseralization ---------------- OOP-based ORM One of the best libraries I've ever seen get this right was TypeORM[0]. It was easy to get started with, includes consideration for migrations, allows you to use both the query building and annotated-class approaches where appropriate, also allowing for use of the repository pattern if you're comfortable with that, and has pretty great support for lots of different backends (I've used postgres the most though). All of this from a F/OSS project (~2 years ago I was also involved in a C# project during the switch from .NET 4.x to .NET core/standard and was very very annoyed that things I could easily do with TypeORM weren't available/worked out yet in EF core at the time). [0]: https://github.com/typeorm/typeorm https://github.com/typeorm/typeorm
- agotterer 8y agoHave you looked at the DataMapper pattern? Not identical to what you are describing, but it sounds closer than a tradition ORM.
- hardwaresofton 8y agoThe DataMapper pattern is very much included in the normal discussion of what an ORM is expected to provide -- so much so that almost most larger ORMs (that I know of at least) is listed on the Wiki[0], including TypeORM. That said, you're right, the DataMapper pattern isn't quite identical, and it is a piece of fully OOP-based ORMs that I think are on the right side of the spectrum. I'd say that the DataMapper pattern fits in the middle -- I call it Serialization/Deserialization, and assuming the syncing of data is not constant (as in triggering action on another thread or something), then it can be anything from a function (ex. db::insert_user(user), user.write_to_db(db) or Adapter.save(obj)) to a dedicated object (Adapter.update(db, obj)). If I had to try and point to a difference between the two, I'd say that DataMapper-ish or DataMapper-and-below levels of abstraction expose more internals by default (which is obvious) -- I find that ORMs on the right side of the spectrum I sketched up top try to never expose their internals, which quickly falls apart as soon as you step off the paved/well-traveled path. [EDIT] I want to note that dealing with this syncing behavior in EF (Entity Framework) was really annoying. The constant management of a pool of objects that "reflected" the objects in the DB and had to be saved all together/flushed before they were manipulated or whatever was really annoying. That said, I am now aware that C# is one of my greatest weaknesses, so take this opinion with a grain of salt, it's entirely possible that I just didn't have the skill to do it right and keep it in my mind the right way. Just to make sure I'm not unfairly bashing EF I went and found a relevant SO post to show the kind of stuff I didn't like having to look up/deal with [1]. I'm picking on EF but this is likely an issue with any ORMs that handle it this way (w/ the object pooling & syncing). [0]: https://en.wikipedia.org/wiki/Data_mapper_pattern https://en.wikipedia.org/wiki/Data_mapper_pattern [1]: https://stackoverflow.com/questions/5462620/entity-framework-data-context-not-in-sync-with-database https://stackoverflow.com/questions/5462620/entity-framework...
- viach 8y agoWhen you are restricted with only 2 choices, probably they both suck, if applied separately.
- hyperman1 8y agoI recently put a lot of refactoring work in a java app. The enterprise architects made a list of decisions that amounted to forbidding an ORM, so a lot of JDBC/ResultSet boilerplate was included. The program had a tendency to UPDATE a few of the columns, then SELECT the same data right back a few lines later. Plenty of inconsistencies in the logic. This means refactoring went like this: First step: Create for each table generic methods to insert, update,select all fields. Now at least memory and DB are consistent. Next step: All these methods are mostly identical and there is a lot of commons-beanutils in there. So add an annotation to the relevant getters and generate most SQL statements on the fly. Next step: Replace the incoherent commit/rollback mess with clear boundaries. Either it succeeds or it fails. Next step: To stop the never ending reloads of data already in memory, SELECT data only when it's not already there. Flush all caches on commit. All of this gives great results. Batch run time went down from 20+ minutes to a few seconds. Most weird crashes disappear. The need for manual data fixup after crashes evaporates. And then it hits me: I just wrote a custom ORM.
- jandrewrogers 8y agoI don't have any issue with ORMs in principle, and even wrote an ORM once. However, in almost every place I've seen them used they've become a way for developers to avoid understanding how databases work, inevitably leading to inexplicable data models and poor performance. In practice, ORMs tend to end up creating crippling technical debt that is difficult to fix. If ORMs were typically used by developers that fully understood the implications for the underlying database, there would be few issues with them and they would be a valuable tool. Unfortunately, they seem to be most popular with developers that are the opposite of that description.
- Daishiman 8y agoFrankly that says more about the people you've worked with than the underlying technology. I work with devs that have over a decade of Django experience. We use the ORM because it's just ridiculously easier to write queries on it and because SQL is impossible to compose without substantial problems. 90% of the code we write is CRUD and API endpoints. There's no reason to write SQL by hand except for the complex aggregations that comprise 5% of our queries, with luck.
- bpyne 8y ago"and because SQL is impossible to compose without substantial problems." Would you mind providing an example of what you mean?
- marcosdumay 8y agoA few features that I miss on SQL that hinder composition: - A good module system - View-like variables - Scoped singletons (related to the module system) - First class functions - First class symbols (that can be used as object names) - Composable queries (related to the view-like variables and first class symbols) If I wasn't on vacation I would have a few more fresh on memory. Different ones each day.
- bpyne 8y ago
- ChicagoDave 8y agoYou should decide on the type of data storage for a given domain and then decide on the patterns required to support that domain. The danger of using an ORM is that you can easily hide critical business functionality.
- 34r45sdg 8y agoI am quite disappointed that both the article and the comments dont mention application security. USE AN ORM. Unless you are a hotshot SQL dev who knows the intricacies of every RDBMS, you should be delegating this to the ORM. Its there to prevent you from blowing your foot off by introducing SQLi vectors. Use an ORM, avoid SQLi.
- Rapzid 8y agoAlways be preparing(statements).
- abraae 8y agoUsing an ORM to enforce security is like wearing your motorbike helmet down to the store in case a bird shits on your head. Yes, it will help prevent bad developers from introducing sql injection vectors, but with a whole lot of extra baggage coming along for the ride. And there are other application security scenarios involving database where the ORM gives you nothing, such as always forcing a "tenant = " filter in a SaaS scenario (which some database engines do support).
- dliff 8y agoI currently work on a project that is based on Django ORM. Before this, I almost exclusively hand-wrote all SQL. I think you can quickly outgrow the limits of ORM... at least Django's. Whether it's needlessly fighting with ORM to get joins correct, ORM deciding it's going to loop through n records instead of joining on DB server, simply doing complex aggregates that ORM won't support, or doing DB-specific stuff. Postgres has some amazingly powerful features that many don't know about because they only learn ORM. My experience: It can end up being double work because you spend an hour getting the query to work with ORM. Then, a week later, the requirements change and you have to add 1 thing for which it's just not feasible to use ORM. Then you're rewriting it completely with hand written SQL. ORM seems really popular with web/mobile api now, and hand-in-hand a trend of not learning SQL. I think it's too bad as it creates another layer or separation to knowing how your app truly works. It's a layer that is still important to be familiar with, especially as a project grows. Using custom SQL with Django has been a mixed bag for me. If you dump JSON from your query, some fields won't be consistently formatted with endpoints that still use the ORM. You can "load" the result into a Django model, but it can be difficult to make it work if you have custom fields you're returning from your custom SQL since Django doesn't know how to format them. (maybe there is a solution to this I haven't found?). I like the approach of using a basic query-builder for routine insert/update and maybe even select.
- wolf550e 8y agoI'm a former Oracle DBA and I love the Django ORM. After you learn it, it does almost anything I want, and when I want custom SQL I just write custom SQL. Learning it well is important though, and inspecting the SQL that was generated for your test cases after you've implemented a feature is also important. It can do multiple joins with same table when needed, it can do filtered prefetch, it can do subqueries, it can do exists(), it can do group by, it lets you use SQL functions Django doesn't know about out of the box, etc. But I think the documentation is not intuitive, and in the complex cases the ORM code looks complicated and I think I prefer writing SQL in that case because there is greater chance that the next developer will know SQL than that of them knowing advanced Django ORM features. Also, more chance of getting stack-overflow help for SQL.
- PopeDotNinja 8y ago
- bunderbunder 8y agoI've come to a couple conclusions, over the years. First, when you get down to it, the most-valued feature of ORMs is not the "writing queries in some language other than SQL" feature, it's the "not having to write a mess of mapping code" feature. Second, the biggest drawbacks to ORMs all derive from the "writing queries in some language other than SQL" feature. Fortunately, there are tools out there that solve the "mapping tables to objects" problem without trying to control all access to the database: So-called lightweight ORMs like Dapper and Sql2o. To me, these hit the sweet spot: They give you most the increase in productivity while steering clear of most the increase in complexity (and, by extension, decrease in maintainability).
- ozim 8y agoActually "writing queries in some language other than SQL" which has static typing and catching issues in compile time is quite big for me. Add automatic database migrations that are also keeping types in line with code and whole bunch of "mess of mapping code" goes away. Though I use .NET EntityFramework which by now is really mature and heavily invested into by MS. Not sure how it is with other environments but I think I have it too easy.
- JamesBarney 8y agoNot to mention code reuse. Say for instance we have one query that finds eligible bachelors near me and another that finds newly eligible bachelors. We need two queries but they both rely on the same underlying domain concept of "eligible bachelors". In entity framework it's easy to save that as an expression that can be reused in multiple places. It's hard to do that in sql in a maintainable way. Not to mention refactoring issues. If I decide to change a datetime field called time to split it into a datetime and offset that change is far safer in c# than it is SQL.
- polygotdomain 8y ago> It's hard to do that in sql in a maintainable way <cough>Views</cough>
- return1 8y agoGuys, stop commenting. It's a rhetorical question.
- philwelch 8y agoThe ORM discussion reminds me of something Rich Hickey said in his talk, “Simple Made Easy”. He distinguished between “simple” (when a system is inherently low in complexity) and “easy” (when a system is made more complex so it is theoretically easier to use). ORM’s are easy, but not simple. If your system uses a relational database but not an ORM, you have to understand your particular database and also SQL to understand your data layer. If you add an ORM, you aren’t actually saved from having to understand those things, you just also have to understand your ORM on top of all that. There is some positive tradeoff you get in return, since you don’t necessarily have to grapple with the added complexity all of the time. The main saving grace of ORM (or at least query builders) seems to be that the alternatives aren’t well-supported in tooling, so the “raw SQL” alternatives often end up implemented as string concatenation hell, which is admittedly terrible. Installing parameterized SQL on the DB itself in stored procedures works great, but you have to go out of your way to do it; it never really comes across as a plug-and-play option even though in principle it easily could be.
- idoubtit 8y ago> ORM's are easy but not simple. ORM libraries may not be simple, but what matters is the code that uses them. My experience was that the main code was often easier and simpler. Easier because small tasks are made easy. Simpler because it makes the code more consistent: for instance, once you know the ORM, you don't have to deal with hundreds of specific cases that insert or update various records. Promoting stored procedures as an alternative to the complexity of an ORM seems strange to me. They still have to be carefully written like any raw SQL. There is no canonical way to keep them in sync with the code. They are vendor-specific. As far as I know, you need to learn specific tools to debug them or to analyze their performance.
- yawaramin 8y ago> Simpler because it makes the code more consistent: for instance, once you know the ORM, you don't have to deal with hundreds of specific cases that insert or update various records. That's not what 'simpler' means in this context. Simpler means conceptually independent things aren't coupled together. For example, ORMs couple together object-oriented code and relational objects. That's the essential complexity that they introduce. > There is no canonical way to keep them in sync with the code. There's still the old-fashioned way of checking them into the repo as part of migrations. > They are vendor-specific. Most people will almost certainly stay with a single vendor anyway. If you never take advantage of your platform for fear that you'll migrate away from it, you're throwing away a lot of potential benefits for some perceived future risk. > As far as I know, you need to learn specific tools to debug them or to analyze their performance. No more than you would with an ORM–in fact, with an ORM you'd need to learn two different stacks to tune performance or troubleshoot.
- out_of_protocol 8y agoFor everyone complaining about orm-something - take a look at elixir's Ecto. It basically allows writing sql via native code. Good for composability, type casting, stuff like that. Account |> where(active: true) |> join(:left, [a], p in Post, on: p.account_id == a.id) |> where([a, p], a.foo == "bar" or p.bar == "foo") |> group_by([a, p], a.id) |> select(...) |> limit(10) ... |> Repo.all() Plus there are changesets for data validations (can e.g. catch and return uniquness errors via unique index) P.S. For update/insert queries it doesn't make sense to write UPDATE statement by hand UPDATE accounts SET ... 50 fields ... WHERE id = 1 P.P.S. changeset example: def changeset(record, attrs) do record |> cast(attrs, [:number, :note]) |> validate_required([:number]) |> validate_format(:number, number_format()) |> unique_constraint(:number, name: "foo_bar_index") end will return nice error if number is missing (_before_ running sql query) or already taken (_after_ running a query, on DB error)
- kyllo 8y agoThis is my preferred approach--a fluent, composable query builder API that uses pipes. When I do data analysis with R, this is what I use--same concept: https://dbplyr.tidyverse.org/ https://dbplyr.tidyverse.org/ The somewhat tricky part can be converting the tabular result set into a graph of objects, but I think it's better to handle this in your application code as needed, rather than delegate it to a library.
- kiliancs 8y agoThis is the best solution I've found so far. Bring sql to the language instead of abstracting it. Plus changesets, migrations, etc.
- udfalkso 8y agoCame here to say this. Ecto is solid. Also, the results come back as elixir structs and not bloated objects so it’s much faster than ORMs in many other languages. Additionally, the functional approach makes everything much less magical. Changesets can take a little while to grok, but they work.
- campl3r 8y ago
- he0001 8y agoI think the most common problem with ORMs is that people doesn’t know when to use the ORMs functionality or when to write their own query. This always leads to N+1 problems and unmaintainable code, because when you eventually finds those problems it’s usually quick fixed because there’s no time for fixing all of it. It leads to ad hoc code for the next guy trying to change. This is generally not the ORMs fault, but always the programmer and that they don’t understand those limitations. Just replacing the same code with “raw” sql would produce the same thing but you need to write it all instead.
- alkonaut 8y agoI quite like the middle ground of micro orm. Gets rid of the trivial boilerplate like populating fields from query results, and still offers any custom queries.
- beders 8y agoJust say no to ORM. Apart from the usual problems with impedance mismatch, there's one thing that is rarely talked about: Waste. Most ORMs work like that: You get a request, you start a transaction, you begin instantiating lots of objects because you need those to do anything useful. You then either serialize parts of that object tree into JSON or whatever, or you change a few objects, which then create UPSERT statements, the transaction commits (or not) and then...you are throwing it all away again! You might save some state in some second level cache, but other than that, you just created a partially populated object graph, probably loaded way too much stuff, and then you do that AGAIN for the next transaction. I've used JDO, Hibernate etc. before and nowadays it feels like extremely wasteful with limited productivity gains that quickly disappear if your objects become more complex.
- asdfman123 8y agoWe should band together and form the NoORM movement.
- gigatexal 8y agoGo with an ORM when your schema is still in flux but transition to raw SQL when you can the tech debt and performance overhead is just insane otherwise.
- collyw 8y agoOnly if you don't know how to use your ORM properly.
- gigatexal 8y agoPerhaps there are performant ORMs but in my experience ones like SQLAlchemy are terribly slow in that they make it very easy to have not so optimized queries. I’d wager though that hand crafted queries tuned with the right indexes and the like are going to be much quicker than any abstraction like an ORM. If you’re using it as just a query builder then that’s something else.
- dkersten 8y agoIn my experience, this is (almost) everyone I've ever worked with, in multiple teams, with multiple ORM's, in multiple industries.
- gigatexal 8y agoI’d love to hear any war stories.
- dkersten 8y agoIn the project I'm currently working on, objects are fetched from the database using ORM queries and then the results are iterated over, filtered further and additional queries are made to fetch more data. This is then all sent to the frontend, where only about two or three of the couple dozen fields are actually used. I found this code because I was wanted to fix an issue where if you sort the list in the UI, it only sorted the current page (because the sorting happened in the frontend only). So I wanted to change the query so that sorting and paging are both done in the database. What a rabbit-hole that was! I was able to refactor the code to not over-fetch quite as badly, and to move the filtering into the initial query (so only additional data fetching happens after the initial query now, but it does still over-fetch fields and that's not easy to change since this function is called by many endpoints and the set of fields they all want varies). I had to log all of the queries so that I could construct a new query and then convert that back to ORM code. It was a very tedious task, but I managed to get the query from taking 9 seconds to under a second. In a previous project, I was tasked with optimising the database, because requests were incredibly slow. The issue was that no care had been taken 1) with indexes -- indexed fields were too large, and 2) with locking. The locking was the big one as there were some requests that caused entire tables or large amounts of rows to become locked, which in turn would prevent other transactions from completing, so otherwise fast queries were now also slow, which in turn... you get the idea. This was in a large well established codebase, so it was not easy to fix and it still wasn't fully fixed when I left that team. Many of these issues could be avoided by considering the data and access patterns when designing your database model (which doesn't have to be identical to your application model -- database should IMHO be modeled around query patterns and application model around domain features), with some care taken for indexing and locking. You want to make sure that different users usage patterns overlap as little as possible to avoid locking, that your indexes are small, that your queries fetch only what you need (both in terms of columns and rows). None of these issues are the ORM's fault per se, but the ORM encourages developers to think in terms of their application model and primary programming languages OO facilities, so people (in my personal experience, at least) tend to mix database queries and application logic too much (like what I described above: pull data from DB, do some filtering that maybe should have been in the query, do more queries that maybe or maybe not should have been joins), tend to overfetch (often the ORM pulls in too many columns unless you make it not do so, which often is not done), using indexes as an afterthought and not considering locking at all. I think because ORM make it look like "just some more application code", these things are often overlooked, while with SQL queries, its a little more obvious that it's executed differently. I would have said its down to junior developers or whatever who just haven't learned to take care when writing queries, but I've only ever worked with two people who actually did this and I can't believe that the rest of all of the teams were just inexperienced. I'm no database expert, but I've managed big wins with small amounts of care and by understanding what queries I'm running. PS: The book SQL Performance Explained is quite good, in my opinion, and if you use Postgres, then Mastering PostgreSQL 11 is great (and it starts off talking about transactions and locking).
- Grollicus 8y agoI'm surprised with so many mentions of Django nobody mentioned the migration system. I've hand-written sql (the good old days of mysql_real_escape_string) and I've used some ORMs. Django stands out because of their code first-approach for models: You define models in Python and they generate migrations based on that. That makes updating very easy and also quite robust, as a simple makemigrations call in the CI ensures your DB is not out of sync.
- dinkleberg 8y agoI'm in total agreement with you. I've tried different ORMs in different languages and I am always disappointed in how lackluster they feel in comparison to the Django ORM. There are several things that bother me about using Django, but because of the ORM and it's tight integration with the rest of the system I don't think there is a more productive environment for me. That being said I'm not a great developer. As a business-minded person who codes out of necessity, I find the Django tooling to be the right way to work most of the time. Not having to deal with migrations is a thing of beauty.
- talkingtab 8y agoThe number one reason to use SQL is not a reason not to use an ORM. SQL is extremely powerful - it can make your data sit up, roll over, play dead and even speak. Learning how to harness that power is worth while. So maybe use an ORM now, while you are learning SQL.
- thymanl23 8y agoOr use a "pure orm" (mapping to plain business objects) without a query builder - thus writing native sql and receiving nicely structured objects from the results (eg https://github.com/craigmichaelmartin/pure-orm https://github.com/craigmichaelmartin/pure-orm)
- electrotype 8y agoMy opinion is to stick to plain SQL, but to use tools around if you like: query builders, transactions utilities, mappers. But no ORM for me, thank you. I have already given.
- tabtab 8y agoI tend to agree, but in static languages, reflection engines are usually needed to automate or simplify mapping, and reflection can get messy. Static languages are not designed for CRUD, in my opinion (without a revolution in frameworks, at least). Look at the square-bracket "annotations" or "attributes" in C#. Isn't that kind of info that classes or object instances were supposed to define, such as fieldx.maxLength=30;? Square brackets are a kludge.
- rodmena 8y agoOf course I will never use ORM again. Sqlalchemy made my life so miserable that I felt I am the only stupid person in the entire planet. What ORM guys don't think is that SQL IS NOT OBJECT ORIENTED. Why the hell you want to pretend something completely fake? The problem does not end here, Using ORM make you think object orient which the worst corrosive idea since the dawn of computer science. It made you code like spaghetti. You read your code 90% of the time and you write code only 10% of the time, that's why clean functional code will result to better software.
- dvliman 8y agoI personally prefer tools like https://github.com/jkk/honeysql https://github.com/jkk/honeysql It helps build queries programmatically but doesn't abstract / get too much in your way. On the "mapping" side, the resultset is just a map. This is where languages that expose data-first shines
- git-pull 8y agoI wrote an opinion on ORMs here in 2017: https://news.ycombinator.com/item?id=14661391 https://news.ycombinator.com/item?id=14661391 Most of the stuff I said still sticks for me (after reading article, and after using ORM this time). I haven't programmed much golang. But with Python + Django I can use `./manage.py shell_plus` and can drop immediately into a shell with history, readline support, syntax highlighting, and tab completion, with all model objects in local scope. I can then do lookups and annotations (Yes, they're expensive, not for production, but save lots of time during the day in a pinch): Lookup: Library.objects.filter(books__author__name='Book author') Annotation: Author.objects.annotate(total_books=Count('books')).filter(total_books__gt=5) When the data model of a project gets bigger, writing queries by hand gets harder to think about. Perhaps its due to my own overreliance on ORM's: but I can't fathom how I'd manage without them. Because in practice the data models are far more complex than above, and it'd take a lot of time (not to mention mental energy that could be put to use elsewhere) Also, there are ways around performance problems: For one, it may entail rejecting features offered by the ORM. Such as django's content types, multi-table inheritance to instead use abstract inheritance (basically just reusing common fields) and even plain one-to-one relations. They're just too complicated and slow at scale. Same goes for extending the ORM with stuff like django-polymorphic (and django-model-utils). I've gotten nice performance out of django-polymorphic (and cleaner code), but it's hiding a ton of metaprogramming, there's still a penalty "upcasting" naive models, and they're very burdensome to maintain if APIs fall out of date. Next is trimming down queries with prefetching, only(), and doing direct ID lookups. This prevents multiple queries from piling up by doing the join ahead of time, only getting fields asked for, and doing the fastest lookup for objects after a more expensive "filtering" query has ran. Here's an example of the query earlier, with .only(): Author.objects.annotate(total_books=Count('books')).filter(total_books__gt=5).only('id') And to print the SQL query out: print(Author.objects.annotate(total_books=Count('books')).filter(total_books__gt=5).only('id').query) That would then be used in something like: BookSet.objects.filter(book__in=Author.objects.annotate(total_books=Count('books')).filter(total_books__gt=5).values_list('id', flat=True)) And finally, for debugging certain types of query performance, django-debug-toolbar is nice. If its API calls django-silk can "look back" on background requests. The article was fair IMO, but I'd wager ORM's payoff depends on the ecosystem. Software being about tradeoffs: the convenience outweighs the downsides everytime for me. Maybe I'm to be humbled and find I'm not getting the big picture, and it could be just plain learnt-dependence, but it'd be a step back in productivity for me not to have an ORM.
- pnathan 8y agoOne of the classic bikesheds. I've wound up writing a low key system in, at the time, Scala, that did something to the effect of: class Select { fields: Either[List[String], All] table: String wheres: List[Clauses] orderBy: Option[String] def render(): String } The render() method generated SQL; the Select class allowed a _fairly typed_ input; with some evolution, you can get shared queries quite nicely. This works _reasonably well_. Part of _why_ I went this route is that I tend to use Postgres in a very serious way: it's not just a fancy set of spreadsheets with FK linking: I have indexes, check constraints, enums, pg-specific types, triggers, etc. So being able to directly interact with the database gives you a rich control surface that ORMs tend to exclude you from. By the way, this was an evolution in concept from a different database oriented system I wrote in Python. Both systems used Postgres rich types, the class/render system was implemented in Scala and was Very Nice.
- halis 8y agoI was pretty excited when I originally learned about ORMs. But I quickly found that their usefulness is very limited in the enterprise, where most of my career has been spent. You will likely find, as I did, that enterprises literally have thousands of databases of all kinds and many crazy data models. They have tomes of huge SQL procedures that are still to this day running their business processes. Maybe even a nice older gent that is the only one that really understands how any of that shit works? If this sounds familiar to you, then don't waste too much time on ORMs. In my experience, they're only good for mapping out your POJO or POCO objects and performing simple CRUD operations. Use them to stand up simple REST routes that do CRUD on your entities. That can be the API that you use to do development and testing, and possibly even use those CRUD routes to do larger business operations. But in reality, the business "requirement" is going to come down to you, the developer, in the form of some old terrible blob of shit SQL. Two thousand lines. Ten thousand lines. You're probably not going to bother deciphering all that and re-writing it. The ORM is going to barf all over itself if you even try to use its API to execute that query. Even if you could express all those joins and case statements via the ORM, it is going to pick an execution plan where the query finishes sometime after the next Hanukkah. At this point, don't bother with the ORM. Drop down to some driver where you can just run raw SQL and run the raw SQL. Create a REST route that just calls this whack query directly. This all may sound cynical and it is. But I know what I'm talking about. Do yourself a favor and remember what I said.
- rambojazz 8y agoYes this is my experience as well. ORMs are really nice for simple queries, not so much when queries start to get more complex.
- imtringued 8y agoI have written several queries that span hundreds of lines, primarily for "advanced" search functionality that lets you search by a huge array of potential parameters. Without using the query builder provided by the ORM the code would become intractable to mortals quickly. Having several dozen different parameters that are only set under certain special conditions is much easier if you can use your regular programming language to evaluate those conditions as opposed to writing everything in one big SQL statement even with named parameters. Writing code like this here really gets old: q = query(SELECT a FROM table WHERE (:PARAMETER_1_DISABLED OR table.field < :PARAMETER_1), ... 20 other parameters) q.setParameter(PARAMETER_1_DISABLED, condition) q.setParameter(PARAMETER_1, parameter) .. 20 other parameters compared to just Type.list() { if(condition) { field < parameter } .. 20 other parameters } Also please don't tell me to avoid this problem by concatenating the SQL conditions to dynamically create the query... The end result is still ugly and might even introduce security problems.
- keithnz 8y agoIn F#, you get type providers like https://fsprojects.github.io/SQLProvider/ https://fsprojects.github.io/SQLProvider/ or https://fsprojects.github.io/FSharp.Data.SqlClient/ https://fsprojects.github.io/FSharp.Data.SqlClient/ What is not obvious if you have never seen this before, is that this dynamically connects your database WHILE you are coding and automatically gives you auto completion, fully understands the types, and will give compile errors if your code doesn't match the database. Feels like Magic (or LinqPad if you have ever used that ) The only downside is you need a DB available for your CI build tools ( which is not too difficult to do ). Some F# people will also opt for a light weight mapper like Dapper.
- dicroce 8y agonot. there that was simple.
- cjdell 8y agoI fell out with a lot of the popular ORMs and decided to create a very lightweight query library with the features I really wanted, namely an ENTIRELY statically typed query interface. Link to fully working sample: https://github.com/cjdell/ts-sql https://github.com/cjdell/ts-sql The idea is that it should be impossible to write an invalid query, and also to ensure the returning data type is exactly representative of what has been queried. Currently using knex.js to actually generate the SQL behind the scenes. It's experimental but I'm already using it in a couple of projects. I enjoy the fact the my queries have so little boilerplate now. :-)
- echelon 8y agoIn my experience ORM always shoots you in the foot when your use case exceeds what an ORM can provide. ActiveRecord has been a terrible mess for us because the facade masks the underlying behavior (transactional behavior) or often doesn't do what it advertises (commit hooks, timestamps, auto-increment). ORM is great for startups trying to build out an MVP, but beyond that scale it's an anti-pattern. The best solution I've ever seen is jOOQ, which is essentially typified SQL. Your queries have to pass compilation, but the DSL is essentially SQL itself. It's an added layer of security yet still manages to feel like it's easier to write than raw SQL. More languages need a jOOQ. It's so good that I'd choose Java just for the ability to leverage jOOQ.
- cies 8y agoI came here to say that any article discussing the benefits/downsides of ORMs should include JOOQ. It takes such an interesting spot in the design space: not an ORM, but a super flexible, type-safe query builder with lots of helpers so you dont write more code than necessary.
- mikeryan 8y agoIn JavaScript there’s Knex which is a query builder that’s pretty good and has a few ORMs built on top of it.
- Noumenon72 8y agoI work at a place with sort of a homebuilt ActiveRecord system made of generated code. JooQ did all the same mapping and object creation just by inspecting my database with a bunch of boilerplate Maven XML, so I wouldn't have to rely on the homebuilt version of SQL join.
- alexnewman 8y agoEverytime I show up at a company with flask-sqlalchemy, step #1 is to remove it. Raw sqlalchemy is better, but we always end up with SQL. It's probably my personal bias of writing 4 SQL parsers, but I find it better than the alternative. Although it's very nice to have something which can safely map the types
- taffer 8y agoFor a toy project I used Postgres to generate JSON objects for me and a helper function in ruby (db_helper) that parses the JSON into ruby data structures so that the OP example can be written with much less boilerplate code as: def db_all_posts_in_tag(tag_id) db_helper(" SELECT json_agg(r) FROM ( SELECT post.post_id, post.published, post.title, post.content FROM post INNER JOIN post_tag ON post.post_id = post_tag.post_id WHERE post_tag.tag_id = $1 ) AS r; ", [tag_id]) end
- barking 8y agoSorry for being off topic but is there an ORM or any other tool that can rewrite messy SQL, ideally replacing right joins with left ones at the same time? I have several hundred of these that I am afraid to touch.
- thymanl23 8y agoThe desire for the object mapping (to pure objects, not db-aware objects), while having an aversion to query building APIs led me to use https://github.com/craigmichaelmartin/pure-orm https://github.com/craigmichaelmartin/pure-orm - where native, unobstructed SQL is written in a "data access layer", which returns pure "business objects" to be used in the app's business layer.
- richarddddd 8y agoHacking is not just about having access to things of other people, it essentially means seeking a solution to a problem, The deep web has been solving these problems for people over decades and putting up smiles on their faces. We offer range and quick hacking services, with the help of our expert tech agents who we employed from various regions because of the good quality of works they give. are you interested in hiring us for your next hacking service we do a list of wide and range hacking services; contact our agent/officer on cipherhacker1@gmail.com for any of the services listed below or more Criminal case clearing{we clear case from police station and also reduce court sentence by hacking court database}{we also clear past criminal records} mac computers hacking- we give full strength recovery of data"s on the system * We do school grade upgrading- we do it a way that your supervisor would not notice and we would change your grade permanently and help you monitor your next grades. Western union Car hacking and unlocking, we also do car tracing to get location of your stolen cars Wifi password cracking We also do device protection._ we help you protect all your phones and laptops to stop any hacker from gaining access. DDOS attacking Ethical hacking learning Instagram hacking Whatsapp {we can get past whatsapp chats and media of the person and also direct future messages to you} snapchat Facebook Hotmail Email and Gmail {we also do mail protection,to protect your mail from being hacked in future} Mobile phones- we network tracing and stalking to help you stalk your spouse icloud and iPhone- we original data uploaded to the account database Blogger hacking Youtube channel hacking Wordpress hacking Skype Software Penetration test CCTV hacking Phone hacking and tracing *Gaming and betting site hacking >>>> You can contact us for other services not listed here. contact on cipherhacker1@gmail.com your satisfaction is our goal
- sandreas 8y agoAnother advantage that i've come across and that has not been mentioned so far is, that an ORM can help you a LOT, when implementing role based access control (RBAC). Mapping the objects and having all the metadata had huge advantages over pure SQL, if convention over configuration is used to control access to the given objects with a User/Role Entity - and object inheritance gives you the ability to add a "created_by", "modified_by" to each table for controlling even access to the row level...
- rplst8 8y agoI find it amusing how many devs accept climate change science but deny the inordinate amount of work done on SQL.
- pezo1919 8y agoMy favourite orm is Peewee for python. I really like how clean it is. grandma = Person.get(Person.name == 'Grandma L.') or query = Pet.select().where(Pet.animal_type == 'cat') for pet in query: print(pet.name, pet.owner.name) I am looking for something similar for javascript and lately for Typescript, but I could not find anything with similar readibility and logic. Do you have any idea? http://docs.peewee-orm.com/en/latest/peewee/quickstart.html#quickstart http://docs.peewee-orm.com/en/latest/peewee/quickstart.html#...
- ketzo 8y agoI really like Sequelize after using it in a couple of small projects. Not quite that level of concise, but I find it to be a really nice balance of concision and flexibility. Person.findOne({where: {name: "Grandma L."}}) Person.findAll({where: {age: 30}}) Particularly, I really like how associations are handled. If I have a one-to-many association between Posts and Comments, I can do something like this: Post.findOne({where: {title: "My Article}}) .then( article => { article.getComments() .then( comments => { // do something with all comments of Post "My Article" }); }); http://docs.sequelizejs.com/manual/ http://docs.sequelizejs.com/manual/
- coleifer 8y agoHa! Glad to see Peewee mentioned. In two bullet points: predictable apis, compostable pieces...or, learn once, apply anywhere. Or jOOQ but python. Take your pick! If code is your thing, Peewee has quite a few examples: https://github.com/coleifer/peewee/tree/master/examples https://github.com/coleifer/peewee/tree/master/examples
- lowbloodsugar 8y agoThe ORM discussion is really just a proxy for "Have you had lots of experience managing data in production" (in which case against ORM), and also "Have you had lots of experience managing data with a team of green engineers" (who most all want to use ORM because it's cool, but also, hell no). Now, about the only way for a developer to understand how awful they are is to really spend some time going all out with them. So my advice would be to embrace ORMs whole heartedly, as soon in your career as possible. As you debug it, you'll learn lots of wonderful things.
- victor106 8y agoFrom the creator of Hibernate Gavin King "Just because you're using Hibernate, doesn't mean you have to use it for everything.” https://mobile.twitter.com/javaooq/status/504184043765002240 https://mobile.twitter.com/javaooq/status/504184043765002240 This is how I use it 1. Use Hibernate for most updates. 2. Use JOOQ for reads. Gives you everything from compiler checks to type safety. ORM is a tool. Just like any other tool in CS or non-CS you have to know when to use it and when not to use it and what other options exist.
- rawoke083600 8y agoApart from the well thought out and "proper arguments" like "You don't know what queries it does underneath...". Its usually the "insane-funny-sub-language" you have to learn to work the said ORM which only works for 50% of the queries before you really have to get weird with it. My not correct and sub optimal solution over the years is to have some sort of "god-class" that has a big collection of sql-related queries with some query-reuse(error checking etc) like "student_insert_update(namedList,namedValues)" or "student_get_all_subjects(student_id,semester)" I know its not perfect :/
- pjmlp 8y agoNot to ORM. Something like myBatis, jOOQ or Dapper are more than good enough for the boilerplate of building queries and data mapping. No need to have needless generated SQL queries, with data going around the pipe, when it should be processed at the server side, taking advantage of the RDMS capabilities.
- audiometry 8y agoIs there a solution in Python/Postgres where I don't have to have ORM'd tables, but that much of the boilerplate SQL CRUD-type things can be reduced? Basically just beyond writing raw sql.
- nikolasburk 8y agoWhat a great article, this paragraph really reasonated with me: > Any situation where complex functionality is wrapped in another layer runs the risk of increasing the overall complexity when the wrapping layer is itself complicated. This often comes along with leaky abstractions - wherin the wrapping layer can't do a perfect job wrapping the underlying functionality, and forces programmers to fight with both layers simultaneously. I think we can all agree that a mapping layer from a relational DB to your language is needed in almost every application, so the question is whether to build it yourself or use an off-the-shelf solution. I work at prisma.io and we've set out to make database workflows as easy as possible for developers, this includes database access (= ORM) and schema migrations. With polyglot persistence becoming a standard pattern in modern applications, today's ORMs fall short since they only map from relational databases. Our goal at Prisma is to provide a type-safe mapping layer to all your databases at once. This will allow developers to focus on their application logic and not worry about how to access the data from the various data sources.
- adrianlmm 8y agoTo me is simple, to never ORM, it saves lots of wasted hours in the future.
- leowoo91 8y agoLiking to write SQL can easily make you think ORM is tend to have more mistakes or become out-of-control, but in fact, you can fix certain bottlenecks later and combine with raw queries without having to get non-ORM overall.
- gremlinsinc 8y agoI like to mix orm/non-orm.. for anything involving multiple joins and more complex stuff I'll just write raw sql... Using laravel as an example I'll usually make a $sql var to hold, then just run DB::select(DB::raw($sql))); It still keeps things nice and succinct, I let DB handle some of the setup/connection boilerplate, and I just figure out the sql myself, but for 80% of the time I rely on Eloquent.. The benefit of relying on Eloquent is I tend to like to 'tie' into the event system, so for example you have to explicitly be aware when adding/deleting a user that might have other related tables created at the same time as 'setup', you can tap into the 'creating/created/saving/saved/etc' events and when a new user is 'created' you could create a profile for example. For me I think the killer feature is keeping an event cycle when it matters, sure you can explicitly remember to update/map everything when you code your sql, but is everyone on the team going to do that? If you for example update profile>fullname whenever user->firstname or user->lastname is updated, it's easy using an orm. not so much when 15 different devs are updating things in different places in different ways using sql only. Everytime ->save or ->create( is called it handles the events you put in play. (This is just an example, I'd probably just do a setter or getter to grab the first/lastname and combine them instead of having duplicate data, but again this would only work if you're using eloquent to grab the data in the first place, because it's model related)