7 ms·

Ask HN: Raw SQL vs. ORM?

• svennek 8y ago
  Raw SQL is always better, IMHO. There are no new abstractions (for example extra session/trransaction-like behaviour). You can write good efficient SQL and not use an iteration-protocol. ORMs tend to make slow and clumsy SQL. It might not matter if you only update one record, but if you need to fix something in 1 billion records, you are going to feel the pain.. If you need to learn real sql (and not clumsy beginner-sql), look at use-the-index-luke.com and modern-sql.com (both from the same, fantastic knowlegdeable guy) and read Joe Celkos "smarties" books... If needed, a small library can help write CRUD style SQL. I have implemented them myself multiple times in an afternoon. Only tricky thing.. Handling of strings so that you have no SQL-injection.. (for that, serverside bind-parameters are a god-given).
  • pschlump 8y ago
    I have had a number of contracts where my entire job was to fix the performance problems introduced by using a ORM. ORMs add a layer of complexity to your code and a huge risk of really bad SQL.
    • giancarlostoro 8y ago
      ORMs can be used poorly sure, but anything I can design using raw SQL I can typically do with an ORM. The question is, are people using ORMs as a way to avoid having to figure out proper database design? If so, they should not be using an ORM and should really learn how the database they will be using works. Heck! There's micro ORMs like Dapper which let you run raw SQL and match it to an actual object for you, to be fair almost all ORM's support doing raw SQL and getting back an object which can be handy for performance bottlenecks. Badly coded software will usually have performance issues, whether using raw SQL or not. Update: Fixed typos.
    • dagw 8y ago
      Do you think the performance would have been better if the same programmers who introduced them using the ORM had been forced to use raw SQL? I've seen far too many cases where programmers get around their lack of SQL knowledge by writing a bunch of basic queries and then post-processing the results client side.
  • acutesoftware 8y ago
    Yes, if you already know SQL and by using bind-parameters, then I [personally] can't see a tangible benefit for using an ORM. They can certainly make for cleaner looking code, and are useful for people who don't really understand the database schema, but not worth the added complexity.
    • timrichard 8y ago
      Just to mention another alternative, I've been using a QueryBuilder library on the last two projects. The code's prettier than it would be with raw SQL, plus there aren't ORM downsides.
      • pserwylo 8y ago
        Good point. We had great success doing this with our PHP code base. For us it wasn't about pretty or not-pretty code, but rather about type safety. The idea that you can have a method which accepts "WhereClause $constraints" instead of "string $constraints" means you can be much more confident that the code is correct. We actually wrote a custom query builder because none of the available ones (e.g. Doctrine) had the type safety that we desired. A direct parallel to what we used is the Java library JOOQ, in terms of its querybuilder API [0]. [0] https://www.jooq.org/doc/3.10/manual/sql-building/sql-statements/dsl-and-non-dsl/ https://www.jooq.org/doc/3.10/manual/sql-building/sql-statem...
      • Allstar 8y ago
        Same here, we use Knex.js Querybuilder to build queries and fallback to `knex.raw()` for very specific SQL queries.
        • timrichard 8y ago
          Ah, coincidence... Knex is the one I've been using too :-)
• shujutech 8y ago
  Most current ORM out in the market do not make things easier but instead otherwise. We need ORM to make it easy to build application that all agrees on OO principal meaning inheritance, one or more instant variable, polymorphism and etc. My product have done all the above, at http://shujutech.mywire.org/corporation?goto=ormj http://shujutech.mywire.org/corporation?goto=ormj and me personally have felt the abstraction have been successfully achieved ever since because I never need to switch back to the ORM layer to build my applications. But, there're more to goes still............
• LaGrange 8y ago
  Uh, neither? A good query builder library, though not one that tries to excuse you from knowing actual SQL. If you try writing SQL by hand, you _will_ eventually mess up and introduce SQL injection somewhere.
  • djd20 8y ago
    This - you really want the best of both worlds. Use an ORM that lets you mix raw sql in where needed, or generate it directly - sqlalchemy.org is a great example of being able to mix and match as needed using the same core library.
    • LaGrange 8y ago
      SQL Alchemy is pretty good at helping you build up complex and dynamic queries. I actually grew into mostly disliking Python, but other languages don't have SQL Alchemy. There are some tools for that in other languages (Squirrel in Go, Arel - the thing backing Active Record), but I've found SQL Alchemy the most comfortable. Access to schema data is something that even I, mostly sceptical of "proper" ORMs, find really useful.
• VeejayRampay 8y ago
  The trade-off is that of every higher-level VS lower-level anything when it comes to computers: Higher-level: Dependent on the quality of the "bridge", but generally you will benefit from good-practice security measures, less repetition and more idiomatic code with regard to your language of choice. What you lose is the cost of the bridge and the fact that the bridge itself has to be maintained over time, requiring you to keep up with any API changes. Lower-level: Generally less approachable for beginners, highly dependent on your capacity to understand all the implications of the choices you will make, will yield more performance if used correctly because the layer between you and the system is smaller but with power comes responsability.
  • drinchev 8y ago
    > good-practice security measures +1 for that. Even for super small projects, I use an ORM, because there is always some user input that needs to go to the database query string. Life is too short to maintain every single query.
    • pllbnk 8y ago
      I would like to add that this is not a binary choice. Depending on the complexity of the requirements its possible to differentiate between and even mix the levels of abstraction. I personally like to avoid using ORM by its true meaning (mapping object and relational models) but I like using certain facilities an ORM library might provide, such as session management, user input validation, etc.
  • lukaseder 8y ago
    The biggest fallacy in this particular debate is the idea that ORMs are somewhat "higher level" whereas SQL is "lower level". SQL is a 4GL, and as such, a much more higher level language than anything you can do with an ORM, which leads to much more "lower level", imperative data interaction patterns. On the other hand, a lot of people put their database in lower layers often confused with or used as synonym for levels. So, the level-ness depends on what you're trying to abstract. Algorithms? SQL will be higher level. Layers? ORMs will be higher level.
• asah 8y ago
  (in between compiles...) Raw SQL for simple stuff, of course - easier to debug, transportable to multiple languages. For non-trivial stuff... For read-write access, no choice but ORM imho - are you really gonna create stored procedures for every type of update? For read-only access, raw SQL is an option, but it gets tricky with layers of VIEWs. Must be at least as powerful as Postgres e.g. partial and function indices to hide the underlying physical structure without paying some horrendous performance penalty. (experience from trying to avoid ORMs in 3 startups... someday I'd love to add SELECT * MINUS <columns> to PostgreSQL to make VIEW authoring more scalable...)
• olavgg 8y ago
  I use both raw SQL and Hibernate in my applications. For simple CRUD (Create, Read, Update, Delete) operations Hibernate is excellent and does the job damn well! But as your data model gets more complex, you need to write more complex queries. For example if you need to build a tree with child/parent nodes. This can only be done with CTE(Common Table Expressions) https://www.postgresql.org/docs/10/static/queries-with.html https://www.postgresql.org/docs/10/static/queries-with.html Hibernate and "all" other ORM's out there doesn't support CTE's and this is where you want to use raw SQL's instead. There are also other examples where I want to use specific function, for example the array_to_json function in PostgreSQL.
  • chrisgoman 8y ago
    +1 ORM for simple CRUD and maybe the first version of getting lists (SELECTs). You will then quickly need raw SQL. I usually like to create "fake" ORM-like objects from raw SQL so that other code doesn't have to care too much where the data is coming from
• m0skit0 8y ago
  For simple queries, I totally prefer ORM because the code that converts the results to objects is more work than learning/writing the ORM API. For complicated queries involving joins and/or complicated conditions, in my experience writing the raw SQLs directly is simpler and more readable than ORM. However most ORMs let you use raw queries and you still get the benefit of not having to write the conversion code.
• zv 8y ago
  Raw SQL queries will perform fast if you write correctly. No ORM will stand close. Stored procedures help a lot. It's close to C/C++ in a sense that is easy to shoot yourself in the foot. On other hand, ORM helps to write code that can be unit tested and you can test that some data retrieval conforms to certain principles.
  • olavk 8y ago
    Hand-written SQL is not intrinsically faster than ORM generated SQL. The instances where ORM's have worse performance is typically due to application errors like n+1 queries or forgetting to batch updates. If you actually know what you are doing you can typically do the same optimization on the ORM level.
• JulianMorrison 8y ago
  There was a weirdly named thing called "mybatis" around awhile ago, that had a nice balance where the SQL was contained in one place and communication into and out of it was objects, but it was actual SQL (amenable to DBA optimization) and not some library doing things for you cleverly. Not sure if it's still a thing but I'm surprised that model isn't more common.
• mavidser 8y ago
  For those who don't use ORMs in large projects: What's your preferred approach to store table definitions and migrations? Raw SQL queries there too? Doesn't it make them more susceptible to mistakes?
  • manyxcxi 8y ago
    Since we use a lot of Java we use Flyway. It’s got different ways to use it, one of them being straight command line- so you don’t have to be a Java shop to use it. I like Flyway over some others I’ve tried (like Liquibase) because it supports straight up, plain ol’ SQL code for migrations and uses very simple file naming conventions to define versions and repeat operations. The advantage we have being a Java shop is that we can also write our migrations in Java code, or mix and match. I’ve only ever done Java based migrations sparingly, all involved a big migration that involved sorting and sanity checking a lot of existing data. For PHP/Laravel I’ve really liked Blueprint. Although I like Flyway better than Liquibase, Liquibase can do a lot that Flyway can’t and is certainly worth looking at. I like the control of migrating my DDL by hand instead of the code first approach, and then having some ORM randomly changing data on boot. We use Spring Boot w/ Hibernate and JPA a fair bit. We always turn off the code that would have the code manipulate our DDL, and we do write a lot of queries by hand, but we still get a lot of auto-magical stuff for free.
    • sancha_ 8y ago
      Having used both Liquibase and Flyway, I prefer Liquibase. For what it's worth, Liquibase does support raw sql migration too: http://www.liquibase.org/documentation/index.html http://www.liquibase.org/documentation/index.html
      • schalla 8y ago
        Yes Liquibase is very flexible in how you can author changesets; it's certainly nice too that you can format it in XML, YAML, JSON, or SQL. All that said, what makes you say you prefer Liquibase to Flyway?
• legostormtroopr 8y ago
  Short answer: First one, then the other. A good ORM that abstracts away simple access, querying and database creation can be a godsend during many early iterations of a project. Eventually however you may start to hit boundary cases where an ORM is unsuited or generates slow queries. Once you identify slow queries, then drop down to raw SQL to optimize only when you know where to optimize.
  • kyrra 8y ago
    This is my answer as well (mostly). Earlier in development when you are iterating on your data model and just trying to write a lot of code, an ORM can help move things along quicker. But once your project is more established, well designed tables and well written SQL could have large impact on performance.
  • matt_s 8y ago
    When moving from Java to Rails, I tend to think in SQL first, then translate that to Rails' ORM. I didn't really use ORM's in Java, too heavyweight and the stack we had was old. I would go with this approach too - use the ORM until it gets in your way. When the ORM is trying to be 'smart' but ends up having slow queries or N+1 types of things, refactor the code or use raw SQL. Even better is if your ORM lets you use raw SQL in a way where your results are cast into objects.
  • majewsky 8y ago
    Seconded. I like combining a rather small ORM (for simple access patterns like "get object by ID" or "list all related objects") with hand-crafted SQL for complex reports that join many tables together.
• inertiatic 8y ago
  ORMs are fun to play with, and are probably better for productivity in small apps. However, for anything more significant than such, I find that writing SQL is not only more performant but also allows for greater productivity (do exactly what you want the way you want it without excessive digging around to see if it can been done/has been done).
• laci27 8y ago
  RAW SQL of course. * Create the database for the queries you will use, then, for the admin panel, you can use an ORM (but why would you mix the 2?), but for front-end I always use RAW SQL (PDO, to be exact).
• snarfy 8y ago
  I use ORMs as a programmatic way to generate SQL for me. I do NOT use ORMs as a replacement for knowing SQL or using SQL when it's appropriate. I think this is where a lot people get into trouble. They assume they can use the ORM and not need to know SQL.
• sethammons 8y ago
  I'm firmly on the side of raw queries with binding params to avoid SQL injection. This is doubly true with complex queries. I can't count how many times I've crafted my SQL query very quickly, and then fought the ORM query builder. Often, ORM proponents will say that is why any true Scotsman, erm, I mean ORM, will let you drop into raw mode. But that can have problems with testing. If you test against your ORM one way, it might just not work to test it against raw mode. And, thinking of testing, I'm also a fan of testing your SQL or ORM against a real DB (one that is set up and tore down per test). I know there are some unit testing purests that don't like that and thus prefer an ORM. Many who like ORMs claim that they are so much faster for the programmer. Maybe for basic CRUD and composing simple conditional WHERE statements. And I contend that really is a "maybe." But I've been bit by poorly formed ORM queries (either does not do what you planned or is too slow, or one of my favorites: it returns ALL records and the ORM filters that app side to give you the ONE matching result) and I've lost enough time trying to force the ORM to make the query the way I want it, that I opt to skip that whole class of problem whenever I can.
  • mosselman 8y ago
    Funny, calling people out on ‘ no true Scotsman’ in your straw man reasoning.
    • sethammons 8y ago
      Is it a straw man when you bring up regular arguments you've heard? I was under the impression that a straw man happened when you side stepped an opposing view by presenting a different argument. Since I wasn't side stepping an argument of the op's, is it still a straw man? If so, how does one present their opinions in comparison to others without automatically falling into a straw man situation?
• alex_duf 8y ago
  I've only had bad experiences with ORMs. You get implicit behaviours and performance nightmares. SQL isn't hard to write, unless the model is poorly conceived, in which case throwing an ORM at it will only makes things worse. It really depends on how many tables you have, and how you generate their definition. Personally I once had to start a fresh project with a large amount of tables, I generated the few CRUD requests I needed on all the tables, and commit them in my VCS. Then when iterating I would update and tweak them manually, either for performance tuning or new features / joins / schema changes etc... This is not to say ORMs are to throw in the bin, I'm sure they solve some problems, but I've never fund myself in a situation where they would help me more than slow me down.
  • grillorafael 8y ago
    I'm on your side on this. For websites that are completely CRUD based, I believe ORM can help a lot. But if you can about performance/scalability, writing your own queries (Even with some sort of library like knex for example that doesn't hide much/do magic) I believe performs/scale much better.
  • sancha_ 8y ago
    Yeah there is learning curve for ORM too. Never blindly use ORM without checking the queries it does to your DB. Enable the logs to see what it does, evaluate them, enhance the slow queries, if you don't know how, change those to raw sql. I would never give up the development speed that using ORM gives me. And from experience there are at most half a dozen queries that I convert to raw sql in a huge project.
• realusername 8y ago
  Depends the quality of the ORM really. For simple queries (basic insert, basic select), pretty much every ORM will do the job and in a more readable way than SQL. For something more complex, if the ORM isn't great, I prefer writing SQL directly.
• gaius 8y ago
  People are so afraid of SQL they end up putting in 10x the effort trying to do it with an ORM. You just don’t need it, just a day of studying SQL is an investment that will pay off 100-fold.
  • icebraining 8y ago
    I'm not saying the solution is an ORM, but "fear" is not the only reason people avoid SQL: https://news.ycombinator.com/item?id=1510177 https://news.ycombinator.com/item?id=1510177
    • gaius 8y ago
      Not sure I understand your point about composition, why not use a CTE? https://www.postgresql.org/docs/current/static/queries-with.html https://www.postgresql.org/docs/current/static/queries-with....
      • icebraining 8y ago
        CTEs are such a limited composition tool. In our case, our ORM automatically applies user-configurable (ie, not hardcoded) complex access-control rules to each table depending on the roles of the current user. Doing this in the ORM is simple, because we can manipulate a query as a first-class entity. Doing it in SQL would be a massive pain.
        • gaius 8y ago
          Access control should be in the DB itself, or anyone can connect directly bypassing the ORM and have full access!
          • icebraining 8y ago
            I'm not talking about access control to the DB, I'm talking about application users. Say you're writing a CRM, and you want a certain group of salespeople to not be able to view clients from Malta. In our platform, the CRM manager can create a dynamic rule like: Role: group1 Record Type: Clients Rule: Country != 'Malta' Then our ORM will dynamically apply that to any query that accesses the Clients table when the logged-in user belongs to group1. For example, when the user searches for clients with a certain name, the SQL-equivalent query is: SELECT * FROM clients WHERE name LIKE '%John%'; But before sending it to the DB, the ORM see that the current user belongs to group1, and so will transform it into: SELECT * FROM clients WHERE name LIKE '%John%' AND country != 'Malta';
• laci27 8y ago
  The biggest advantage of using RAW SQL is that you learn it 'quicker'. You will make mistakes, but hopefully those will be found before they make it to production. If you get used to ORMs, you probably won't know how to optimize your queries..
• jpalomaki 8y ago
  One thing I have found out useful is having a setup where you can easily write test cases for ORM queries using a real database and see a log file of generated SQL. Most of the "strange ORM errors" are much easier to figure out when you can see the DDL (like foreign keys) and the queries/updates that ORM is generating.
• mrweasel 8y ago
  I really like the concept of sqlx for Go. You write the SQL, but sqlx returns populates structs for you. For really simple stuff, CRUD application, an ORM usually fine though.
• fredley 8y ago
  As a Django developer, I wouldn't give up the Django ORM for the World. It's very expressive (albeit a bit verbose if you want to do non-trivial things — but you can do them), extensible, and all around a complete joy to work with. It's the perfect balance of getting out of your way for trivial things, and letting you write your own SQL where it's required. https://docs.djangoproject.com/en/2.0/ref/models/querysets/ https://docs.djangoproject.com/en/2.0/ref/models/querysets/
• protomyth 8y ago
  if you mean embedding actual SELECTs in your code then no. Calling stores procedures is a much better more maintainable solution. Your app shouldn’t have to know anything about the schema just from a separation of concerns view.
• thom 8y ago
  ORM, mapped to views for more complex stuff. That said, mostly I use Clojure and there’s no actual mapping to do. I use a library to create dynamic, composable SQL queries, but mostly have macros to generate crud statements. Everything gets returned in namespace qualified, idiomatic Clojure maps. It’s all then covered by Clojure Spec so you know you’re forming and passing everything around correctly if you need to.
• generalk 8y ago
  Use an ORM. Use an industry-standard ORM -- Hibernate, ActiveRecord, SQLAlchemy, whatever is the done thing in your language. There's just no reason not to. Onboarding new developers becomes easier. Your common queries (fetch a record by PK, fetch some associated records by their FK, etc) require little to no thought, and complex joins between multiple tables become relatively simple to represent. I don't know of a single ORM that doesn't also allow you to execute raw SQL and get back objects, so in the case where you really _do_ need to do that, you can. Yes, there exist counterexamples where you're doing very cutting-edge stuff with your DB that most ORMs can't handle. If you're doing that you aren't asking "should I use an ORM," you've already made that call and skipped this thread.
  • zie 8y ago
    I don't understand how you think using an ORM == easier onboarding, because then they have to also know the ORM. With straight SQL you just have to know SQL, which anyone messing with databases should learn anyway and is a way more portable skill than $ORM. That said, I do agree if you are going to use an ORM, definitely use an industry-standard one for your language.
• agotterer 8y ago
  I prefer a hybrid approach. I like to use an ORM for simple object manipulation and access. So for creating/updating basic models and simple object retrieval. When I say simple I mean queries like “where id =“ and other very basic where statements. Anything more advanced I prefer to use raw SQL. SQL is a language unto itself and it doesn’t really need a layer of abstraction. I’ve found a lot of people use an ORM as a crutch instead of really learning the true power of SQL. I’ve spent too much time fighting against the ORM trying to get it to replicate what I want to do with SQL. I find it much harder to maintain complex queries that are in an ORM. My personal workflow is to develop queries directly interfacing with the database and then more or less copy and paste the solution into code. If you use the query builder in the ORM you have to make the conversion from SQL to code, which I find to be pointless. SQL is very expressive, easy to read (once you learn it), and portable. Every ORM is unique and has its own syntax and style. My one exception to where I prefer the ORM query builder over raw sql is when I’m building SQL that is heavily machine manipulated and has a lot of logic paths. Like for example a custom csv generator or an advanced query form. In those cases I find wrapping code logic around raw SQL to be quite messy and error prone. It flows much nicer when the builder can generate the SQL around your logic. In either case people should learn SQL. Even when you use an ORM the end result is SQL. Understanding SQL makes it’s easier to debug problems, improve performance, and answer the hard questions. I also think it just makes someone a more well rounded developer.
• freeman478 8y ago
  I like using what is sometimes called Micro-ORM. Basically it does the mapping of query results to objects and sometimes has a basic query builder but you mostly use raw sql. Dapper (https://github.com/StackExchange/Dapper https://github.com/StackExchange/Dapper) is a great one for .NET.
  • signal11 8y ago
    JDBCTemplate would probably also qualify, it can be useful especially if you use Spring already. JDBI can be an alternative if you don't use Spring.
• Dowwie 8y ago
  Use the best tools available to you for the job at hand. For instance, if you're using Python, there is SQL Alchemy, which gives you everything you will ever need in any situation, from ORM to parameter-bound raw sql, from a very feature-rich library. Parameter-bound raw SQL is a fine option as long as you are comfortable with taking responsibility for testing and auditing for risks of sql injection. Don't use this approach unless you understand what the risks are and know how to manage them. Further, raw SQL is more challenging to debug in that you don't know problems until you vet issues at run-time. You're not entirely on your own with syntax checking, though-- there are sql syntax verification libraries that can help vet raw sql for you.
• kiliancs 8y ago
  The main advantage for ORM is security. The main disadvantage is that they often abstract away things that can impact performance heavely even if you are careful. I love Ecto (for Elixir) as a middle ground: closer to SQL, safe, still able to leverage your client models and well integrated into the language. Examples: https://elixirschool.com/en/lessons/specifics/ecto/#querying https://elixirschool.com/en/lessons/specifics/ecto/#querying
• krapp 8y ago
  If you do use an ORM, you should still know SQL and know what raw queries it actually generates, and you should make sure it doesn't do anything cute like falling back to concatenating strings or some other unsafe practice for the sake of having a convenient API. Everyone assumes that using an ORM means better security than raw SQL, and maybe in the general case that's true, but the more abstract and complex a framework is, the more room there is for unknown and unwanted behavior. The one thing you probably should never do is use an ORM because you don't want to learn SQL or you just can't be bothered to care.
• jeswin 8y ago
  1. The Programming Language is factor in how well ORMs integrate into your app. If you're using a language which is expressive enough or has native support for querying, ORMs are an excellent fit. An example of a language in which ORMs work well is C#, on account of LINQ. But if you're on Java, it isn't as appealing. Hibernate still has value and is a very mature solution, but is nothing close to the expressiveness you get in LINQ. 2. Performance is good enough, but with caveats. Some ORMs (especially newer ones) don't handle entity relationships very well, and sometimes do 1+N queries to fetch a single object. For eg, if you have a customer with an orders property, a naive ORM might SELECT the customer first, and issue separate SELECTs for loading each of the Orders. Entity Framework had this problem earlier, which they resolved later. 3. Always watch the actual queries with a database tool. 4. Be careful about Lazy Loading. Lazy loading defers the actual load until you use it. If the code always uses a property (that needs to be loaded from the DB), always eager load. eg: if you have 100 customers, and you usually need customer.creditCard, eager load "creditCard" (resulting in a join) to avoid 1+100 queries. 5. You probably don't need an ORM with NoSQL. 6. Inheritance relationships can be tricky, and have performance consequences. You can choose to have a (1) Table for the entire class hierarchy, or (2) a table for each Class. With (1), you get an ugly wide table with lots of fields and faster performance. With (2), if you were to select a list of Animals, and have Cat, Dog, Rabbit tables, you'll get cleaner tables - but poor performance because of joins. Add: generally avoid mapping inheritance via ORMs. 7. Built-in caching, which you can find in some ORMs is probably not worth it. 8. ORMs need not replace 100% of your queries. Some functionality will work better with SQL or even Stored Procedures - let it be. 9. ORMs let you compose queries. I'll not go into details, but you could compose getCustomersByCountry() and getCustomersByAgeGroup() to get getCustomersByCountry_and_Age(). 10. Depending on the size of your project, see if patterns like Repository make sense (even if you're using ORMs). 11. Last, the most important detail. It is not actually about saving lines of code - as much as it is about the ability to refactor. The biggest win from ORMs (in a statically typed language) is that if you edit a property, it changes the property across all files including your queries. Without an ORM, the code degrades quicker - because developers are reluctant to change.
  • mping 8y ago
    Good answer. Also be careful with how you define OO object identity. Some ORMs cache in-memory objects (eg: lookup by id) and determine if an object is the same through equals() implementation and hashCode(). IMHO ORMs are nice if you use a OO heavy language, and want to reuse ORM classes in your domain and/or as DTOs. The biggest gotcha is that you will have to understand the quirks of the ORM (N+1, lazy, object equality, eager vs. lazy, types of inheritance, pk generation, integration with legacy DBs, polymorphism, etc). Some legacy DBs make it very hard to integrate with an ORM (eg: no PK, Composite PKs with weird PK generation, etc).
• voltagex_ 8y ago
  I got bitten by ORMs in my current toy project, https://github.com/voltagex/YAMS https://github.com/voltagex/YAMS. For the life of me, I couldn't get Dapper to join correctly. Now I'm running into trouble keeping the POCOs and SQL in sync. I may try to use some kind of code generation based on schema - I think there's a middle ground.
• UK-AL 8y ago
  MicroORMS are a good trade off, they do the tedious mapping between results and tables but you write the sql.
• aussieguy1234 8y ago
  For an MVP, use an ORM. Faster development. For scaling to millions, start strategically using raw SQL for alot of things. You'll save alot of money on processing power and infrastructure costs.
• djpowell 8y ago
  Clojure has several libraries (such as https://github.com/jkk/honeysql https://github.com/jkk/honeysql) which basically let you build a representation of an SQL query as a Clojure data structure, and then programatically transform it. It is great when you need to do things like add joins and subselects programatically to a query based on selections from a UI, and is extensible enough to support many advanced SQL features.
• lukaseder 8y ago
  1. For static SQL, write views or procedures (i.e. raw SQL). 2. For dynamic SQL, use jOOQ or something similar in your language. 3. For the rare case of really needing object graph persistence (loading a graph of entities, manipulating it, and storing the changes back to the database), or the less rare case of doing boring single-record CRUD, use an ORM. You don't want to do that with SQL.
• tscs37 8y ago
  Start with an ORM, for 99% of projects that is the good first choice. Once you start growing and you can't solve your problems with the ORM or performance on the database starts to suck, start handcrafting where it hurts and gradually move to Raw SQL.
• krylon 8y ago
  Personally, I prefer raw SQL, because I like SQL. It is one of the few languages where you can be really clever without making maintenance a nightmare, IMHO. OTOH, I tend to encapsulate the direct interaction with the database into a separate class/data type in my applications, that offers an interface to the rest of the code that is more in line with the application domain. So one could argue that I tend to write special-purpose ORMs over and over again. ;-) It has the advantage, though, that switching out the underlying DBMS is less of a pain, when it is required. Full disclosure: All the projects I have worked on were fairly modest in size and complexity. The most "out there" thing I have done was to write and/or maintain a couple of SQL views and triggers to hook up our ERP system to other applications. Because our ERP system sucks. But on the plus side, I learnt a lot about SQL, which tends to feed back into my preference of raw SQL over ORMs. ;-)
• BenoitP 8y ago
  Both ORM for OLTP SQL for OLAP Expressed nearby to each other when there is shared business rules, for maintenance reasons: public static String PREDICATE_NO_GUMMY_BEARS = "user_preferred_candy <> 'gummy bears'"; public static Predicate noGummyBears(Root<User> user, CriteriaBuilder cb) { return cb.notEqual(user.get("preferredCandy"), "gummy bears"); }