11 ms·

You might not need an ORM

44 points by l5870uoo9y 4y ago

Zigurd 4y ago
Android bolted views and updates directly to the SQLite database classes. It was an elegant design for an OS meant to run (at first) on 200Mhz small memory configurations. It didn't need an ORM and it enabled apps to persist data asap.
qikInNdOutReply 4y ago
https://blog.codinghorror.com/object-relational-mapping-is-the-vietnam-of-computer-science/ https://blog.codinghorror.com/object-relational-mapping-is-t...
- marcus_holmes 4y ago
  Upvoting the classics :) This should be required reading in every coding bootcamp and software development course.
calvinmorrison 4y ago
ORMs do not exist because good developers can't write SQL, they exist to prevent Jr developers, offshore experts and general buffonery running amuck with security issues, database design and proper techniques.
- mrits 4y ago
  I won't argue why they exist but I would argue that they help with security, database design, or proper techniques. Security - You don't need an ORM to prevent SQL injections. Plenty of libraries handle safe string SQL formatting. Database design - I feel depending on an ORM for this instead of actually thinking about the solution would lead to just as many mistakes. Proper Techniques - It is proper until it isn't I guess. Trying to get the ORM to do things it doesn't support usually leads to unexpected side effects.
  - calvinmorrison 4y ago
    You don't need an ORM, it just helps immensely. Here's a recent error I found, and of course, this is a PHP issue, someone used if(empty()) and the value was a string "0" . This evaluated to true, causing a function, that builds up inserts from an associative array (functionally equivelant to say, eloquent's Model::create($data)->save()), to insert NULL instead of "0". Now, we can say that's a php ism, that's just a bug or mistake, but I have found repeated issues like this, especially with legacy PHP code. Going out the gate with something like Eloquent in Laravel, and showing people patterns, really does help. It helps isolate their mistakes, it gives them rails to ride on.
- commandlinefan 4y ago
  Hm - I've found that the security and performance issues are worse with ORMs than with direct SQL because the developers don't understand (and are actually separated from) what's actually going on.
  - KyeRussell 4y ago
    Performance, maybe. Security…I strongly disagree. The 2000s taught us what happens when you don’t make it really really really hard to ‘accidentally’ not parametrise your queries. You don’t need an ORM to accomplish that goal, sure, but every ORM I’ve used will stop you from doing that, pretty much inherently. My experience is that any half decent ORM’s query API will be at least as readable as SQL when it comes to looking for unintentional data access/manipulation.
    - mrits 4y ago
      ORM have their on inherent security issues. Accidentally bringing back all related tables to the user because of referential integrity settings, for example.
- lelanthran 4y ago
  > running amuck with security issues, database design and proper techniques. What does an ORM have to do with security? Or database design? Or proper techniques? In reality, the ORM leads to poor DB design and even poorer techniques.
  - dmux 4y ago
    >In reality, the ORM leads to poor DB design and even poorer techniques. This may be true of the code-to-db type of mapping (where your table definitions are in code and then used to forward-generate the actual database tables) but there is also the possibility of generating an ORM from your database and its relations. I think the real benefit of an ORM is that it's reducing the amount of boilerplate code needed to be manually written in a project.
    - lelanthran 4y ago
      > This may be true of the code-to-db type of mapping (where your table definitions are in code and then used to forward-generate the actual database tables) but there is also the possibility of generating an ORM from your database and its relations. I think the real benefit of an ORM is that it's reducing the amount of boilerplate code needed to be manually written in a project. I agree with this, but when people think of an ORM they aren't thinking of sqlc, which I quite like. The most-used ORMs perform square-peg-round-hole hammering to save the developer doing anything in SQL, they aren't simply saving the effort of writing the boilerplate (like sqlc).
  - thworp 4y ago
    The first part is correct, but if you do it properly (translate a proper db design into ORM) it's fine. You can just as well design a catastrophic data model with pure sql.
- thworp 4y ago
  > database design How does an ORM help with that in any way? Are there any ORMs that will normalize your data model and write apropriate constraints? If so I _have_ been under a rock.
Waterluvian 4y ago
I’ve done about ten years of multi-hat development without knowing SQL beyond the basics because an ORM fits my use case well: CRUD of a few million records across a dozen tables utilized internally only be a few hundred humans and thousands of robots, the entire backend written and maintained by 5% of one engineer. My lessons are: - you might not need an ORM, but it’s also fine if it’s the right tool. (Though I wonder if it should be inverted: you might not need SQL) - I wish I learned more SQL just to know it better. - don’t early optimize, but do read up on how you do optimizations with your ORM. That way you can identify when to get ahead of performance woes. - set up a profiling system asap. For example, Django Silk.
- matsemann 4y ago
  I don't mind an ORM per se, what I don't like is when the ORM bleeds into the whole system (like in Django). If there is an ORM between the database layer or not doesn't matter, but if you're passing fat objects around where touching any property accidentally does a query, or it's impossible to change a table because the details have been solidified in code all over the place, it's not very nice.
  - Waterluvian 4y ago
    For me it would be a red flag if the ORM objects exist in any context outside the views and serializers. It would suggest that I'm mixing business logic and database/API logic. In the case where you don't want to separate Database/API from your business logic, and you just want to use SQL to directly manipulate the database, then yeah, SQL seems fine. While a lot of things make me nervous about that prospect, it probably makes sense in certain contexts where the alternative feels far too heavy too soon. One key problem I have with using an ORM that way is that the ORM gives you an opinionated representation of of the database schema. And the shape of your database is rarely the ideal shape of your data structures for use in business logic. I'll inevitably wish I had a view layer in the middle.
    - KyeRussell 4y ago
      This approach is definitely swimming against the tide with Django. I’ve spent a lot of time with Django, and a lot of time trying to work out how to best build complex systems with it. I don’t think that for Django’s case the answer is anything close to being as simple as “don’t let the models go anywhere”, unfortunately. Though I am really intrigued to try it and see how it goes.
kyberias 4y ago
This is why Language Integrated Query (LINQ) is so powerful in .NET. You never need to learn any ORM specific query language or syntax since you've probably learned to use LINQ with objects already and then you just continue to use it with a database.
- rvdginste 4y ago
  True in the sense that you do not need to learn anything extra to build queries using an ORM. But it is not the complete picture: whenever you use an ORM, you should really know how the ORM works and what it does. For example, even if you use LINQ, you have to realize that the expressions you use in the C# code cannot always be translated to SQL. So, you need to know the ORM and its limitations to be aware of what is possible and what is not. An ORM cannot magically translate the code behind a derived property into SQL. ORM should definitely not be regarded as a technology that allows you to use SQL and RDBMS without knowing those technologies. ORM should be regarded as a technology that can make you much more efficient when using SQL and RDBMS, when you already have good knowledge on SQL and RDBMS. An ORM should help you with all the 'routine' SQL stuff. And a good ORM will support you using straight SQL whenever you want to do something that the ORM does not support well. I love LINQ, but I've noticed that there are people that think they know LINQ, and then think they can use an ORM because of that. And in the end, they do not really know LINQ, they do not really know how an ORM works, and their code generates SQL queries that have really poor performance. And then the obvious conclusion is that ORMs are bad. Well.... no, they are not, they are very powerful tools, but it requires knowledge to use them well.
- klysm 4y ago
  LINQ is nice, but EF Core has some significant limitations and forces you down some pretty sub optimal design paths.
  - alexarnesen 4y ago
    Please elaborate on the limitations and paths! I'm doing technology selection research and EF Core is currently on the table, would love to hear what you encountered.
MajimasEyepatch 4y ago
I've found that the problem with not using an ORM is that you can end up writing a lot of repetitive CRUD queries. Every model ends up with the same basic read, insert, update, etc. function, but because you're writing untested, un-typechecked SQL queries, they still need to be carefully reviewed, tested, etc. You can write some helper functions to abstract over the repetitive parts, but then you start creeping into home-grown ORM territory, which is much worse than using a standard battle-tested ORM. If I'm using a ORM, I can be pretty confident that the method to read my model using an ID is going to work without fuss. For a small service with just a few basic queries, skipping the ORM and going straight to SQL is pretty nice. And there are situations where you need custom SQL, like complex reports or dashboards. But for typical CRUD in a team setting, I've found that ORMs can be worth the overhead.
- Joker_vD 4y ago
  Well, even when you use ORM, you still need to write tests to check that you use it correctly: e.g., you didn't omit any needed fields, excluded all unneeded fields, etc.
  - KyeRussell 4y ago
    Are you arguing against this comment?
- goodoldneon 4y ago
  Plus some ORMs have nice schema migration tools. Engineers should still verify what the auto-generated migration is doing, but that's much better than writing the migration by hand
- deltarholamda 4y ago
  So much of workaday SQL stuff is handled nicely by ORMs it's hard to pass on the convenience. I've found that using the ORM in conjunction with stored procedures works well. For the basic stuff, the ORM handles it well enough. As soon as the ORM request starts to look like spaghetti, it's time to think about making a stored procedure and using it. Years ago I also went down the road of "use stored procedures for everything," which also works, but you have to get deep into the gnarly SQL weeds. And, if DB portability is one of your goals, you more or less have to give up on your dreams. It makes a lot of sense if your project has many fingers in the pie and you have access to a talented-if-grumpy DBA (beard optional). I liked it for the small project I did, but I will admit that it was more work than was optimal.
vlunkr 4y ago
After working with ORMs for a long time, all I really want is a nice API for building queries (that actually supports all underlying database features) and automatic mapping of the results to whatever objects/structs and primitives the language supports. Everything else is IMO a bad layer of abstraction that will eventually bite you when you need to get closer to SQL.
- jkaptur 4y ago
  > a nice API for building queries (that actually supports all underlying database features) Honestly asking - I'm not an expert in this space: is this not just parameterized queries? It seems like anything else would have to have exactly the same "shape" in order to support all underlying database features...
  - mbreese 4y ago
    The only part of ORMs I liked was the ability to dynamically build queries. You’re right that a query would have to return the same columns (for common downstream processing), but a query builder can make adding conditions much easier. For example, if you’re adding filtering to a product list. The query will always return the product id, name, image url and description. But you might have multiple filters (in stock, blue, etc). The query builder can make that process easier and less error prone. Same with pagination, depending on how you do it.
  - vlunkr 4y ago
    A query builder is much more flexible. For example, in some cases there might be an additional condition you want to add to the WHERE clause. You can't do that with parameterized queries, so you'd have to make a new query for every possible variation. That may be fine in some cases, but it could become a maintenance headache in large project.
- collinvandyck76 4y ago
  https://sqlc.dev/ https://sqlc.dev/ is nice in this regard.
- 4y ago
bayesian_horse 4y ago
The reasons you want an ORM or query builder: Reuseability and composeability. In Django for example, you can compose querysets and reuse them in Forms, Admin, Templates, wherever. You can also generate Forms and Admin out of nothing but the model class. Most frameworks are way behind Django/Django Rest Framework in that regard. I'm not saying there aren't any others doing something similar, NestJs for example. But I'm not aware on other solutions.
- bitexploder 4y ago
  You can achieve this without an ORM, but having composable query bits and abstractable querying to the programming language and not directly SQL is really nice. Objects map fine to relational hierarchies so I am not so worried about design constraint. Rust Diesel can go between pure ORM and a more composable query layer. SQLAlchemy also exposes such functionality when you don’t want to fully embrace ORM, but also don’t want to throw out some reuse and composability.
  - megaman821 4y ago
    If you have composable query bits, have you just not rolled your own query builder? As you pointed out most ORMs have a query builder layer anyway and usually a raw sql layer too. Why not use an ORM for the easy bits?
    - bitexploder 4y ago
      I would not roll it myself. That was my point about some ORM frameworks offering a middle ground, which is often appealing when adapting to an existing database schema.
- KyeRussell 4y ago
  At work, I’ve been using Django exclusively for almost a decade. I’ve just taken note of how much of a funk I’m in and have been branching out, especially in the JS space. I’m amazed at how far ahead Django’s ORM is. Hell, I’m amazed at how far ahead 5-years-ago Django’s ORM is. I’ve been enjoying playing with some of the typed JS ORMs / query builders out there. I’m certainly more skeptical of ORMs than I’ve been in previous years, but if an ORM is what you want, I have an increased appreciation of Django’s execution. I’ve honestly got no idea if DRF is comparatively better than what you can do with other frameworks. I’ve recently come out the other side of some work that’s led me to believe that DRF has some deep rooted design flaws that it’ll never be able to dig itself out of. I’m excited to see how the new generation of API authoring tools shakes this all up. None I’d this is to say that it’s any better anywhere else. I don’t think that things like FastAPI are the sort of like-for-like replacement that I’m interested in.
gigatexal 4y ago
Cross posted from a discussion on a different post but I thought it was relevant here as well: I mostly do work on the Python side of things and SQLAlchemy is the de facto ORM there. I hate it. It’s heavy. Opinionated. The happy path seems to be doing things in sessions where joins foreign keys and things are evaluated in the app instead of at the DB level (one can define relationships in models for the ORM to understand but not define explicit foreign key relationships at the DB table level, wtf? Thereby doing joins based on common columns…) and yet I can’t fault ORMs for their ability to get you going fast and help you iterate very quickly. I recently had the chance to start a new side project and trying to go the pure SQL route which I love was so slow in terms of productivity. When I could just model the tables via SQLAlchemy I was able to get to the meat of the thing I was making much quicker. What I didn’t like was all the additional cognitive overhead but I gained DB agnostic ways of querying my data and could use say SQLite for testing and then say Postgres for staging or production by just changing a config whereas if I write pure SQL I might get into issues where the dialects are different such that that flexibility is not there. In the end I am very conflicted. Oh, some context I began my professional life as a DBA and my first love was SQL. I like writing queries and optimizing them and knowing exactly what they’re doing etc.
kasey_junk 4y ago
In the olden days we’d distinguish between an ORM and other kinds of data access abstractions. An orm has a specific job, handling the impedance mismatch between oo and relational paradigms. I almost never use an orm because I think it’s almost always better to avoid mapping objects to relational stores. But! I’d almost never get rid of some of the other things data access abstractions can provide (eg security, typed sql, etc).
jchw 4y ago
Relatedly, I'd like to recommend people check out sqlc. It's a Go project, although it does technically support codegen to other languages. https://sqlc.dev/ https://sqlc.dev/ With sqlc, you write queries and get fully typed functions you can call. It's not completely perfect, but it's pretty good. As a concept, I think this is great. In practice, I've only been able to use it for Go projects using PostgreSQL so far. Definitely worth it though.
rubenfiszel 4y ago
I find that the best of both worlds is to use something like sqlx for rust [1], which does compile time checking (using postgres explain analyze!) of the queries and macros for static typing, but still let you write the query raw (and can be turned off completely by removing the trailing macro `!` as an escape hatch) We use it extensively for Windmill (e.g: [2]) and couldn't be happier. [1]: https://github.com/launchbadge/sqlx https://github.com/launchbadge/sqlx [2]: https://github.com/windmill-labs/windmill/blob/d60a770eb710ed3234c77e7da794fe3e0dc13ea4/backend/windmill-api/src/apps.rs#L318 https://github.com/windmill-labs/windmill/blob/d60a770eb710e...
- jchw 4y ago
  I mainly checked out sqlx as it seemed like compile-time checked queries gave some of the advantages of sqlc in Go. However, I was a bit disappointed. Compile time checking requires you to connect to a database at compile time, with the schema already set up. Sqlc doesn't even require this at codegen time in Go, at the cost of needing to emulate some of the underlying database behaviors. I'm sure it's not a deal breaker to everyone, but I find the premise of needing a database to make queries against for every single build to be way, way, way too much baggage. The idea of checking the queries against a schema is great. Even better if you can use it to infer arguments and return types.
  - rubenfiszel 4y ago
    I can see where this would be inconvenient. I write the migrations first so it doesn't bother me, but for prototyping it sure seems cooler to not even require the schema to exist.
  - norman784 4y ago
    There's an offline feature that > Enables building the macros in offline mode when a live database is not available (such as CI) [0] [0] https://github.com/launchbadge/sqlx/blob/76ae2865005cc79d56972d68d3be30805581103d/README.md?plain=1#L182-L183 https://github.com/launchbadge/sqlx/blob/76ae2865005cc79d569...
- moonchrome 4y ago
rr888 4y ago
I've been using Vert.x a lot and one of the great things is just throwing away the whole Spring magic and going back to basics with a sql query and processing the results without an ORM. Basically ignoring all the Java cruft that gives Java its bad reputation. Its so refreshing I love it.
- bezidejni 4y ago
  What Spring magic? If you're talking about spring-data, that is optional, you can use JdbcTemplate directly if you wanted instead.
  - rr888 4y ago
    All of it, the DI, the autowiring, the plugins, the attributes, the config files. Its so refreshing to have a call stack that isn't pages long.
    - p2detar 4y ago
      I’ve built a product doing exactly that. That being said, the Vert.x path has some bumps along the way, but boy, oh boy, I’m so happy I’m not doing any Spring related stuff.
danwee 4y ago
Post focuses on the "easy" part (building queries), but leaves as an exercise to the reader how to do the M in ORM: so, once I submit my SQL query and get one or more rows: how do I map them into my (potential) complex set of objects? Sure thing, I can do that manually (it's easy), but that's the whole point of using ORMs: to not to have to do it manually. I also like the idea of using plain SQL and do all the additional manual work myself (that's what I do in personal projects); I like the feeling that I'm in control. But when it comes to professional work, and there's a team behind, then handling raw SQL is a recipe for disaster. ORMs bring consistency to the table, ORMs are not the best technical solution, but are the best solution when it comes to working with people.
- thedanbob 4y ago
  That's one thing I like about Rail's ActiveRecord: it covers the most common queries with its own DSL, but also makes it easy to insert snippets of plain SQL while still returning ActiveRecord models.
- jjice 4y ago
  The mapping part is definitely the part of an ORM I like the most. I like both (although unrelated from my understanding) sqlx for Go and sqlx for Rust, which both handle the mapping problem given raw SQL.
- antipotoad 4y ago
  I’m inclined to agree. The article brushes over the type-safety provided by Prisma as being merely a ‘useful feature’ — but this is huge, especially working on a large project with lots of other developers. The discipline imposed by strict types is indispensable to my mind, how ever much I agree with the author that writing SQL directly feels good.
pirate787 4y ago
I've never fully trusted ORMs (building mostly multi-tenant apps where I want to enforce the tenant ID in every table) and the other issue is that my ORM of choice, Doctrine, has abandoned its API which is forcing re-writes of a decade of code. Would have been better with my own abstraction.
dagss 4y ago
The main problem with ORM is the "object" part. I feel object-oriented programming had its chance and it failed. Both event sourcing and relational models (="table modelling") are better models for programming systems. So by using an ORM, you are (at least in the name, and as traditionally done) taking a better programming model (relational models) and mapping it to an inferior one (OOP) -- also getting a ton of leaky abstractions involved too. I wish there were more "ORMs" that were made with the assumption that Object-Oriented Programming is BAD, and started out with assumptions of wanting to program either in an event sourcing model and/or a relational model, and see where that would take things. Related: Fred Brooks, The Mythical Man Month (1975): "Show me your flowchart and conceal your tables, and I shall continue to be mystified. Show me your tables, and I won't usually need your flowchart; it'll be obvious." Out of the tar pit paper: https://curtclifton.net/papers/MoseleyMarks06a.pdf https://curtclifton.net/papers/MoseleyMarks06a.pdf -- My take on the software industry today is that OOP failed to produce good and maintainable code, but instead of fixing that, one is adding the idea of micro-services to try to contain the damage done by OOP. If more people moved to event sourcing, functional code and focusing on data over code, there would be much less need for micro-services etc (or, they just become less relevant).
- robofanatic 4y ago
  > I feel object-oriented programming had its chance and it failed. > Object-Oriented Programming is BAD mind elaborating why OOPS is BAD and failure?
  - dagss 4y ago
    I guess I worded things a bit sloppily, OOP can mean a lot of different things too.. The main thing is that working with state is hard. "Objects" is often about representing something stateful and encourages thinking in terms of doing actions to state. It embraces a stateful world-view.. Functional programming I guess is the one popular alternative; but a lot of the benefits of functional programming can also be had in imperative languages, if one e.g. thinks in terms of event sourcing. My definitions; If your object is named UserNameChanged, and you write code to process such events and so on, that is event sourcing. While if you structure your code to say that a "User" has a "Name" property, that is OOP. Focusing on the events, state transitions etc as the first class thing gets you closer to a "stateless" programming model. The Out of the tar pit paper I linked to describes the problems of working with state. And I think Event Sourcing is a more real-world, pragmatic approach to the same problems described in that paper.
arcturus17 4y ago
Ah, the monthly “do you need an ORM" thread. Please organize yourselves in two equal-height piles around “yes” and “no”, and then a third minuscule one around “the hell do I know, it’s your use case and your code”. See you again in a few weeks!
- arp242 4y ago
  Last I heard the "yes" and "no" factions formed an alliance to fight against their common enemy, the "it depends" faction. I expect that sooner or later one of the factions will barbarossa the other though.
- super256 4y ago
  All I really want is inference for queries, like the JetBrains IDEs kinda do it. I can write something like this (pseudocode): username = '31337' result = db.query('SELECT username, email FROM users WHERE username = 1?', username) And it would automatically show annotations for `result`, straight from the database: result?: { username: string, email?: string }
  - GeorgeHoneywood 4y ago
    This is pretty much the idea behind sqlc, with some extra steps of course. You write your SQL queries in file, and it generates type safe bolierplate for you. https://github.com/kyleconroy/sqlc https://github.com/kyleconroy/sqlc
- poutine 4y ago
  LOL. Yup. Our next instalment will be around Progressive Web Apps Are Bad. Get sorting kids.
  - euroderf 4y ago
    Word on the street is, vi BAAAD emacs GOOOD
    - 4y ago
KingOfCoders 4y ago
From my experience ORMs are very good for fast MVPs and getting something done, but then quite some effort to scale (= replace with hand crafted SQL). Also when scaling, transaction problems show up b/c you misunderstood the ORM and it was not an issue with 100 users/day, but it is now with 10.000 users/day.
hprotagonist 4y ago
I've used https://pugsql.org/ https://pugsql.org/ a few times and generally liked the experience for personal code. ... and if it goes to hell, it's just sqla-core under the hood, so we're all good!
seveibar 4y ago
Kysely[1] and zapatos[2] are excellent solutions for type-safe typescript query builders. It’s hard to go back to the days of spending 20-30% of your time in the object mapping layer. [1] https://github.com/koskimas/kysely https://github.com/koskimas/kysely [2] https://github.com/jawj/zapatos https://github.com/jawj/zapatos
friedman23 4y ago
I'm a current ORM user, specifically Prisma. And it's nice that it gives me type checking but the CRUD actions and the programmatic interface are not the real reason I use it. I use it as a tool for declarative schema management, handling schema version control and migrations. If someone has a better solution to this that does not involve xml, I'm all ears. I'd personally much rather use https://pgtyped.vercel.app/ https://pgtyped.vercel.app/ than prisma. edit: Ok, I actually read the article... He briefly mentions the migration tool. Author: please go into more detail about how you do schema migrations.
sunflowerdeath 4y ago
I saw arguments against ORMs a lot of times, but what about the opposite - you might not need raw SQL and ORM might work just fine?
ac130kz 4y ago
Yeah, now imagine a system at the scale of Gitlab, it's essentially unmanageable without an ORM, which helps a big time figuring out issues, types, migrating schemas, rewriting and so on. Messing with SQL is suitable for manual query optimization or the simplest databases.
exagram 4y ago
[dead]
drbojingle 4y ago
ORM until you don't need to, then SQL. IF your green fielding. If you already have the SQL, just work with it. If all you're doing is building Crud, though maybe consider Hasura or something that gives you a performant API instead.
thih9 4y ago
This feels like an ad. The author created a tool that makes it easy to ditch ORMs. The tool is referenced 10 times in the article, including the article's first sentence and the last sentence. The article also includes examples on how to use the tool. I'd be fine if it was a "show hn" post. But I thought this is an objective article about ORMs and their pros and cons; this is not the case.
- tomhallett 4y ago
  I agree that this blog post is part "orm" and part "lead gen for my saas app which helps you write sql queries". I think a blog title which reflects this, ORM and AiHelperBot, would be more helpful. That being said - the faq's on https://aihelperbot.com/ https://aihelperbot.com/ don't answer the main questions I have: 1) I saw a banner somewhere, can't find it now, which said "your schema is only stored in the browser". While that is nice, is my schema transmitted over the wire to your cloud service's servers? 2) If your servers get it, do you send my schema off to OpenAI's ChatGPT? Or some other AI as a service? 3) Do I own the intellectual property of all sql queries written by your tool? 4) How do you verify the "validness" of a query? For example, do you take my schema, load it into a small postgres/etc database and have your code run the query your AI generates? Do you parse/lint the query? Or is the AI good enough that it always generates valid sql? 5) I'm also interested in the "correctness" of a query, from the perspective of "user with the name of john", will the resulting sql returns results with name = "John", "john", " John ", "Johnny", etc - but I am fine with that being left up to the user. 6) Is snowflake/duckdb/clickhouse on your roadmap? Rather than replacing ORM's, helping business users write analytics/BI queries seems like a home run use-case.
timcavel 4y ago
[dead]
Woodi 4y ago
Is it some AI ad or just parody ? Can't decide...
combatentropy 4y ago
If you're happy with your ORM (and, ahem, your users are happy with your app's speed), then you can disregard this post. An ORM is an abstraction over what already is a huge abstraction, which is SQL. Therefore it would feel to me like driving a bus by remote control, or something like that. SQL isn't a procedural language, like C, Fortran, Cobol, Java, JavaScript, Python, Ruby, etc. It is a "query language", I guess, where you tell it what you want, and it decides how it's actually going to find, filter, sort, etc., that data. It's also the only game in town. Is there any widespread alternative to SQL, at least for querying tabular data? The problem with it being so abstract doesn't rear its head with simple SELECT statements. They all seem to go fast enough. It isn't until you're joining tables together, or no longer getting data row for row but instead aggregating, summing, averaging, etc. Suddenly sometimes the whole thing can slow to a crawl. Thankfully databases like Postgres let you prepend a command called EXPLAIN to the problematic query, so that you can diagnose the slowness (if you can understand the output of EXPLAIN). But it was a long time before I got good at reading the EXPLAIN output and finding a way to get it to run faster. Even though SQL is so abstract, more just a description of what you want, there is more than one way to write it --- and the difference in speed can be hundreds or thousands of times. I have had the luxury of working mainly with Postgres for 17 years, and toward the end of it I finally feel supremely confident working directly with it, just using the psql command-line client, and hand-typed SQL in text files, to get exactly what I want, and as fast as I want it (which usually is less than a second). Mind you, I don't work with Big Data, just a variety of CRUD apps but they often have mindbending reports requirements. It should not have taken me years and years to master SQL (nor should it you). I think the reason is partly distraction, for those of us that are "full-stack developers", and also that there is so little education about how to master SQL. I mean, the books and posts are out there, but the attention to them from the community at large is small. But SQL is really not harder than the other languages and techniques you know so well. If developers spent as much time on SQL as they do on the ins and outs of React, containerization, CI/CD pipelines, or the quirks of their favorite programming language, I think they would find it easier than most of those things. And it pays rich dividends, because SQL is cross-platform, you can carry that knowledge with you from job to job for the rest of your life, and your inefficient database queries are making your app use quadruple the hardware and 10 times the latency than if you truly mastered SQL.

44 points by l5870uoo9y 4y ago

Android bolted views and updates directly to the SQLite database classes. It was an elegant design for an OS meant to run (at first) on 200Mhz small memory configurations. It didn't need an ORM and it enabled apps to persist data asap.

qikInNdOutReply 4y ago

https://blog.codinghorror.com/object-relational-mapping-is-the-vietnam-of-computer-science/ https://blog.codinghorror.com/object-relational-mapping-is-t...

marcus_holmes 4y ago
Upvoting the classics :) This should be required reading in every coding bootcamp and software development course.

calvinmorrison 4y ago

ORMs do not exist because good developers can't write SQL, they exist to prevent Jr developers, offshore experts and general buffonery running amuck with security issues, database design and proper techniques.

mrits 4y ago
I won't argue why they exist but I would argue that they help with security, database design, or proper techniques. Security - You don't need an ORM to prevent SQL injections. Plenty of libraries handle safe string SQL formatting. Database design - I feel depending on an ORM for this instead of actually thinking about the solution would lead to just as many mistakes. Proper Techniques - It is proper until it isn't I guess. Trying to get the ORM to do things it doesn't support usually leads to unexpected side effects.
- calvinmorrison 4y ago
  You don't need an ORM, it just helps immensely. Here's a recent error I found, and of course, this is a PHP issue, someone used if(empty()) and the value was a string "0" . This evaluated to true, causing a function, that builds up inserts from an associative array (functionally equivelant to say, eloquent's Model::create($data)->save()), to insert NULL instead of "0". Now, we can say that's a php ism, that's just a bug or mistake, but I have found repeated issues like this, especially with legacy PHP code. Going out the gate with something like Eloquent in Laravel, and showing people patterns, really does help. It helps isolate their mistakes, it gives them rails to ride on.
commandlinefan 4y ago
Hm - I've found that the security and performance issues are worse with ORMs than with direct SQL because the developers don't understand (and are actually separated from) what's actually going on.
- KyeRussell 4y ago
  Performance, maybe. Security…I strongly disagree. The 2000s taught us what happens when you don’t make it really really really hard to ‘accidentally’ not parametrise your queries. You don’t need an ORM to accomplish that goal, sure, but every ORM I’ve used will stop you from doing that, pretty much inherently. My experience is that any half decent ORM’s query API will be at least as readable as SQL when it comes to looking for unintentional data access/manipulation.
  - mrits 4y ago
    ORM have their on inherent security issues. Accidentally bringing back all related tables to the user because of referential integrity settings, for example.
lelanthran 4y ago
> running amuck with security issues, database design and proper techniques. What does an ORM have to do with security? Or database design? Or proper techniques? In reality, the ORM leads to poor DB design and even poorer techniques.
- dmux 4y ago
  >In reality, the ORM leads to poor DB design and even poorer techniques. This may be true of the code-to-db type of mapping (where your table definitions are in code and then used to forward-generate the actual database tables) but there is also the possibility of generating an ORM from your database and its relations. I think the real benefit of an ORM is that it's reducing the amount of boilerplate code needed to be manually written in a project.
  - lelanthran 4y ago
    > This may be true of the code-to-db type of mapping (where your table definitions are in code and then used to forward-generate the actual database tables) but there is also the possibility of generating an ORM from your database and its relations. I think the real benefit of an ORM is that it's reducing the amount of boilerplate code needed to be manually written in a project. I agree with this, but when people think of an ORM they aren't thinking of sqlc, which I quite like. The most-used ORMs perform square-peg-round-hole hammering to save the developer doing anything in SQL, they aren't simply saving the effort of writing the boilerplate (like sqlc).
- thworp 4y ago
  The first part is correct, but if you do it properly (translate a proper db design into ORM) it's fine. You can just as well design a catastrophic data model with pure sql.
thworp 4y ago
> database design How does an ORM help with that in any way? Are there any ORMs that will normalize your data model and write apropriate constraints? If so I _have_ been under a rock.

Waterluvian 4y ago

I’ve done about ten years of multi-hat development without knowing SQL beyond the basics because an ORM fits my use case well: CRUD of a few million records across a dozen tables utilized internally only be a few hundred humans and thousands of robots, the entire backend written and maintained by 5% of one engineer. My lessons are: - you might not need an ORM, but it’s also fine if it’s the right tool. (Though I wonder if it should be inverted: you might not need SQL) - I wish I learned more SQL just to know it better. - don’t early optimize, but do read up on how you do optimizations with your ORM. That way you can identify when to get ahead of performance woes. - set up a profiling system asap. For example, Django Silk.

matsemann 4y ago
I don't mind an ORM per se, what I don't like is when the ORM bleeds into the whole system (like in Django). If there is an ORM between the database layer or not doesn't matter, but if you're passing fat objects around where touching any property accidentally does a query, or it's impossible to change a table because the details have been solidified in code all over the place, it's not very nice.
- Waterluvian 4y ago
  For me it would be a red flag if the ORM objects exist in any context outside the views and serializers. It would suggest that I'm mixing business logic and database/API logic. In the case where you don't want to separate Database/API from your business logic, and you just want to use SQL to directly manipulate the database, then yeah, SQL seems fine. While a lot of things make me nervous about that prospect, it probably makes sense in certain contexts where the alternative feels far too heavy too soon. One key problem I have with using an ORM that way is that the ORM gives you an opinionated representation of of the database schema. And the shape of your database is rarely the ideal shape of your data structures for use in business logic. I'll inevitably wish I had a view layer in the middle.
  - KyeRussell 4y ago
    This approach is definitely swimming against the tide with Django. I’ve spent a lot of time with Django, and a lot of time trying to work out how to best build complex systems with it. I don’t think that for Django’s case the answer is anything close to being as simple as “don’t let the models go anywhere”, unfortunately. Though I am really intrigued to try it and see how it goes.

kyberias 4y ago

This is why Language Integrated Query (LINQ) is so powerful in .NET. You never need to learn any ORM specific query language or syntax since you've probably learned to use LINQ with objects already and then you just continue to use it with a database.

rvdginste 4y ago
True in the sense that you do not need to learn anything extra to build queries using an ORM. But it is not the complete picture: whenever you use an ORM, you should really know how the ORM works and what it does. For example, even if you use LINQ, you have to realize that the expressions you use in the C# code cannot always be translated to SQL. So, you need to know the ORM and its limitations to be aware of what is possible and what is not. An ORM cannot magically translate the code behind a derived property into SQL. ORM should definitely not be regarded as a technology that allows you to use SQL and RDBMS without knowing those technologies. ORM should be regarded as a technology that can make you much more efficient when using SQL and RDBMS, when you already have good knowledge on SQL and RDBMS. An ORM should help you with all the 'routine' SQL stuff. And a good ORM will support you using straight SQL whenever you want to do something that the ORM does not support well. I love LINQ, but I've noticed that there are people that think they know LINQ, and then think they can use an ORM because of that. And in the end, they do not really know LINQ, they do not really know how an ORM works, and their code generates SQL queries that have really poor performance. And then the obvious conclusion is that ORMs are bad. Well.... no, they are not, they are very powerful tools, but it requires knowledge to use them well.
klysm 4y ago
LINQ is nice, but EF Core has some significant limitations and forces you down some pretty sub optimal design paths.
- alexarnesen 4y ago
  Please elaborate on the limitations and paths! I'm doing technology selection research and EF Core is currently on the table, would love to hear what you encountered.

MajimasEyepatch 4y ago

I've found that the problem with not using an ORM is that you can end up writing a lot of repetitive CRUD queries. Every model ends up with the same basic read, insert, update, etc. function, but because you're writing untested, un-typechecked SQL queries, they still need to be carefully reviewed, tested, etc. You can write some helper functions to abstract over the repetitive parts, but then you start creeping into home-grown ORM territory, which is much worse than using a standard battle-tested ORM. If I'm using a ORM, I can be pretty confident that the method to read my model using an ID is going to work without fuss. For a small service with just a few basic queries, skipping the ORM and going straight to SQL is pretty nice. And there are situations where you need custom SQL, like complex reports or dashboards. But for typical CRUD in a team setting, I've found that ORMs can be worth the overhead.

Joker_vD 4y ago
Well, even when you use ORM, you still need to write tests to check that you use it correctly: e.g., you didn't omit any needed fields, excluded all unneeded fields, etc.
- KyeRussell 4y ago
  Are you arguing against this comment?
goodoldneon 4y ago
Plus some ORMs have nice schema migration tools. Engineers should still verify what the auto-generated migration is doing, but that's much better than writing the migration by hand
deltarholamda 4y ago
So much of workaday SQL stuff is handled nicely by ORMs it's hard to pass on the convenience. I've found that using the ORM in conjunction with stored procedures works well. For the basic stuff, the ORM handles it well enough. As soon as the ORM request starts to look like spaghetti, it's time to think about making a stored procedure and using it. Years ago I also went down the road of "use stored procedures for everything," which also works, but you have to get deep into the gnarly SQL weeds. And, if DB portability is one of your goals, you more or less have to give up on your dreams. It makes a lot of sense if your project has many fingers in the pie and you have access to a talented-if-grumpy DBA (beard optional). I liked it for the small project I did, but I will admit that it was more work than was optimal.

vlunkr 4y ago

After working with ORMs for a long time, all I really want is a nice API for building queries (that actually supports all underlying database features) and automatic mapping of the results to whatever objects/structs and primitives the language supports. Everything else is IMO a bad layer of abstraction that will eventually bite you when you need to get closer to SQL.

jkaptur 4y ago
> a nice API for building queries (that actually supports all underlying database features) Honestly asking - I'm not an expert in this space: is this not just parameterized queries? It seems like anything else would have to have exactly the same "shape" in order to support all underlying database features...
- mbreese 4y ago
  The only part of ORMs I liked was the ability to dynamically build queries. You’re right that a query would have to return the same columns (for common downstream processing), but a query builder can make adding conditions much easier. For example, if you’re adding filtering to a product list. The query will always return the product id, name, image url and description. But you might have multiple filters (in stock, blue, etc). The query builder can make that process easier and less error prone. Same with pagination, depending on how you do it.
- vlunkr 4y ago
  A query builder is much more flexible. For example, in some cases there might be an additional condition you want to add to the WHERE clause. You can't do that with parameterized queries, so you'd have to make a new query for every possible variation. That may be fine in some cases, but it could become a maintenance headache in large project.
collinvandyck76 4y ago
https://sqlc.dev/ https://sqlc.dev/ is nice in this regard.
4y ago

bayesian_horse 4y ago

The reasons you want an ORM or query builder: Reuseability and composeability. In Django for example, you can compose querysets and reuse them in Forms, Admin, Templates, wherever. You can also generate Forms and Admin out of nothing but the model class. Most frameworks are way behind Django/Django Rest Framework in that regard. I'm not saying there aren't any others doing something similar, NestJs for example. But I'm not aware on other solutions.

bitexploder 4y ago
You can achieve this without an ORM, but having composable query bits and abstractable querying to the programming language and not directly SQL is really nice. Objects map fine to relational hierarchies so I am not so worried about design constraint. Rust Diesel can go between pure ORM and a more composable query layer. SQLAlchemy also exposes such functionality when you don’t want to fully embrace ORM, but also don’t want to throw out some reuse and composability.
- megaman821 4y ago
  If you have composable query bits, have you just not rolled your own query builder? As you pointed out most ORMs have a query builder layer anyway and usually a raw sql layer too. Why not use an ORM for the easy bits?
  - bitexploder 4y ago
    I would not roll it myself. That was my point about some ORM frameworks offering a middle ground, which is often appealing when adapting to an existing database schema.
KyeRussell 4y ago
At work, I’ve been using Django exclusively for almost a decade. I’ve just taken note of how much of a funk I’m in and have been branching out, especially in the JS space. I’m amazed at how far ahead Django’s ORM is. Hell, I’m amazed at how far ahead 5-years-ago Django’s ORM is. I’ve been enjoying playing with some of the typed JS ORMs / query builders out there. I’m certainly more skeptical of ORMs than I’ve been in previous years, but if an ORM is what you want, I have an increased appreciation of Django’s execution. I’ve honestly got no idea if DRF is comparatively better than what you can do with other frameworks. I’ve recently come out the other side of some work that’s led me to believe that DRF has some deep rooted design flaws that it’ll never be able to dig itself out of. I’m excited to see how the new generation of API authoring tools shakes this all up. None I’d this is to say that it’s any better anywhere else. I don’t think that things like FastAPI are the sort of like-for-like replacement that I’m interested in.

gigatexal 4y ago

Cross posted from a discussion on a different post but I thought it was relevant here as well: I mostly do work on the Python side of things and SQLAlchemy is the de facto ORM there. I hate it. It’s heavy. Opinionated. The happy path seems to be doing things in sessions where joins foreign keys and things are evaluated in the app instead of at the DB level (one can define relationships in models for the ORM to understand but not define explicit foreign key relationships at the DB table level, wtf? Thereby doing joins based on common columns…) and yet I can’t fault ORMs for their ability to get you going fast and help you iterate very quickly. I recently had the chance to start a new side project and trying to go the pure SQL route which I love was so slow in terms of productivity. When I could just model the tables via SQLAlchemy I was able to get to the meat of the thing I was making much quicker. What I didn’t like was all the additional cognitive overhead but I gained DB agnostic ways of querying my data and could use say SQLite for testing and then say Postgres for staging or production by just changing a config whereas if I write pure SQL I might get into issues where the dialects are different such that that flexibility is not there. In the end I am very conflicted. Oh, some context I began my professional life as a DBA and my first love was SQL. I like writing queries and optimizing them and knowing exactly what they’re doing etc.

kasey_junk 4y ago

In the olden days we’d distinguish between an ORM and other kinds of data access abstractions. An orm has a specific job, handling the impedance mismatch between oo and relational paradigms. I almost never use an orm because I think it’s almost always better to avoid mapping objects to relational stores. But! I’d almost never get rid of some of the other things data access abstractions can provide (eg security, typed sql, etc).

jchw 4y ago

Relatedly, I'd like to recommend people check out sqlc. It's a Go project, although it does technically support codegen to other languages. https://sqlc.dev/ https://sqlc.dev/ With sqlc, you write queries and get fully typed functions you can call. It's not completely perfect, but it's pretty good. As a concept, I think this is great. In practice, I've only been able to use it for Go projects using PostgreSQL so far. Definitely worth it though.

rubenfiszel 4y ago

I find that the best of both worlds is to use something like sqlx for rust [1], which does compile time checking (using postgres explain analyze!) of the queries and macros for static typing, but still let you write the query raw (and can be turned off completely by removing the trailing macro `!` as an escape hatch) We use it extensively for Windmill (e.g: [2]) and couldn't be happier. [1]: https://github.com/launchbadge/sqlx https://github.com/launchbadge/sqlx [2]: https://github.com/windmill-labs/windmill/blob/d60a770eb710ed3234c77e7da794fe3e0dc13ea4/backend/windmill-api/src/apps.rs#L318 https://github.com/windmill-labs/windmill/blob/d60a770eb710e...

jchw 4y ago
I mainly checked out sqlx as it seemed like compile-time checked queries gave some of the advantages of sqlc in Go. However, I was a bit disappointed. Compile time checking requires you to connect to a database at compile time, with the schema already set up. Sqlc doesn't even require this at codegen time in Go, at the cost of needing to emulate some of the underlying database behaviors. I'm sure it's not a deal breaker to everyone, but I find the premise of needing a database to make queries against for every single build to be way, way, way too much baggage. The idea of checking the queries against a schema is great. Even better if you can use it to infer arguments and return types.
- rubenfiszel 4y ago
  I can see where this would be inconvenient. I write the migrations first so it doesn't bother me, but for prototyping it sure seems cooler to not even require the schema to exist.
- norman784 4y ago
  There's an offline feature that > Enables building the macros in offline mode when a live database is not available (such as CI) [0] [0] https://github.com/launchbadge/sqlx/blob/76ae2865005cc79d56972d68d3be30805581103d/README.md?plain=1#L182-L183 https://github.com/launchbadge/sqlx/blob/76ae2865005cc79d569...
moonchrome 4y ago

rr888 4y ago

I've been using Vert.x a lot and one of the great things is just throwing away the whole Spring magic and going back to basics with a sql query and processing the results without an ORM. Basically ignoring all the Java cruft that gives Java its bad reputation. Its so refreshing I love it.

bezidejni 4y ago
What Spring magic? If you're talking about spring-data, that is optional, you can use JdbcTemplate directly if you wanted instead.
- rr888 4y ago
  All of it, the DI, the autowiring, the plugins, the attributes, the config files. Its so refreshing to have a call stack that isn't pages long.
  - p2detar 4y ago
    I’ve built a product doing exactly that. That being said, the Vert.x path has some bumps along the way, but boy, oh boy, I’m so happy I’m not doing any Spring related stuff.

danwee 4y ago

Post focuses on the "easy" part (building queries), but leaves as an exercise to the reader how to do the M in ORM: so, once I submit my SQL query and get one or more rows: how do I map them into my (potential) complex set of objects? Sure thing, I can do that manually (it's easy), but that's the whole point of using ORMs: to not to have to do it manually. I also like the idea of using plain SQL and do all the additional manual work myself (that's what I do in personal projects); I like the feeling that I'm in control. But when it comes to professional work, and there's a team behind, then handling raw SQL is a recipe for disaster. ORMs bring consistency to the table, ORMs are not the best technical solution, but are the best solution when it comes to working with people.

thedanbob 4y ago
That's one thing I like about Rail's ActiveRecord: it covers the most common queries with its own DSL, but also makes it easy to insert snippets of plain SQL while still returning ActiveRecord models.
jjice 4y ago
The mapping part is definitely the part of an ORM I like the most. I like both (although unrelated from my understanding) sqlx for Go and sqlx for Rust, which both handle the mapping problem given raw SQL.
antipotoad 4y ago
I’m inclined to agree. The article brushes over the type-safety provided by Prisma as being merely a ‘useful feature’ — but this is huge, especially working on a large project with lots of other developers. The discipline imposed by strict types is indispensable to my mind, how ever much I agree with the author that writing SQL directly feels good.

pirate787 4y ago

I've never fully trusted ORMs (building mostly multi-tenant apps where I want to enforce the tenant ID in every table) and the other issue is that my ORM of choice, Doctrine, has abandoned its API which is forcing re-writes of a decade of code. Would have been better with my own abstraction.

dagss 4y ago

The main problem with ORM is the "object" part. I feel object-oriented programming had its chance and it failed. Both event sourcing and relational models (="table modelling") are better models for programming systems. So by using an ORM, you are (at least in the name, and as traditionally done) taking a better programming model (relational models) and mapping it to an inferior one (OOP) -- also getting a ton of leaky abstractions involved too. I wish there were more "ORMs" that were made with the assumption that Object-Oriented Programming is BAD, and started out with assumptions of wanting to program either in an event sourcing model and/or a relational model, and see where that would take things. Related: Fred Brooks, The Mythical Man Month (1975): "Show me your flowchart and conceal your tables, and I shall continue to be mystified. Show me your tables, and I won't usually need your flowchart; it'll be obvious." Out of the tar pit paper: https://curtclifton.net/papers/MoseleyMarks06a.pdf https://curtclifton.net/papers/MoseleyMarks06a.pdf -- My take on the software industry today is that OOP failed to produce good and maintainable code, but instead of fixing that, one is adding the idea of micro-services to try to contain the damage done by OOP. If more people moved to event sourcing, functional code and focusing on data over code, there would be much less need for micro-services etc (or, they just become less relevant).

robofanatic 4y ago
> I feel object-oriented programming had its chance and it failed. > Object-Oriented Programming is BAD mind elaborating why OOPS is BAD and failure?
- dagss 4y ago
  I guess I worded things a bit sloppily, OOP can mean a lot of different things too.. The main thing is that working with state is hard. "Objects" is often about representing something stateful and encourages thinking in terms of doing actions to state. It embraces a stateful world-view.. Functional programming I guess is the one popular alternative; but a lot of the benefits of functional programming can also be had in imperative languages, if one e.g. thinks in terms of event sourcing. My definitions; If your object is named UserNameChanged, and you write code to process such events and so on, that is event sourcing. While if you structure your code to say that a "User" has a "Name" property, that is OOP. Focusing on the events, state transitions etc as the first class thing gets you closer to a "stateless" programming model. The Out of the tar pit paper I linked to describes the problems of working with state. And I think Event Sourcing is a more real-world, pragmatic approach to the same problems described in that paper.

arcturus17 4y ago

Ah, the monthly “do you need an ORM" thread. Please organize yourselves in two equal-height piles around “yes” and “no”, and then a third minuscule one around “the hell do I know, it’s your use case and your code”. See you again in a few weeks!

arp242 4y ago
Last I heard the "yes" and "no" factions formed an alliance to fight against their common enemy, the "it depends" faction. I expect that sooner or later one of the factions will barbarossa the other though.
super256 4y ago
All I really want is inference for queries, like the JetBrains IDEs kinda do it. I can write something like this (pseudocode): username = '31337' result = db.query('SELECT username, email FROM users WHERE username = 1?', username) And it would automatically show annotations for `result`, straight from the database: result?: { username: string, email?: string }
- GeorgeHoneywood 4y ago
  This is pretty much the idea behind sqlc, with some extra steps of course. You write your SQL queries in file, and it generates type safe bolierplate for you. https://github.com/kyleconroy/sqlc https://github.com/kyleconroy/sqlc
poutine 4y ago
LOL. Yup. Our next instalment will be around Progressive Web Apps Are Bad. Get sorting kids.
- euroderf 4y ago
  Word on the street is, vi BAAAD emacs GOOOD
  - 4y ago

KingOfCoders 4y ago

From my experience ORMs are very good for fast MVPs and getting something done, but then quite some effort to scale (= replace with hand crafted SQL). Also when scaling, transaction problems show up b/c you misunderstood the ORM and it was not an issue with 100 users/day, but it is now with 10.000 users/day.

hprotagonist 4y ago

I've used https://pugsql.org/ https://pugsql.org/ a few times and generally liked the experience for personal code. ... and if it goes to hell, it's just sqla-core under the hood, so we're all good!

seveibar 4y ago

Kysely[1] and zapatos[2] are excellent solutions for type-safe typescript query builders. It’s hard to go back to the days of spending 20-30% of your time in the object mapping layer. [1] https://github.com/koskimas/kysely https://github.com/koskimas/kysely [2] https://github.com/jawj/zapatos https://github.com/jawj/zapatos

friedman23 4y ago

I'm a current ORM user, specifically Prisma. And it's nice that it gives me type checking but the CRUD actions and the programmatic interface are not the real reason I use it. I use it as a tool for declarative schema management, handling schema version control and migrations. If someone has a better solution to this that does not involve xml, I'm all ears. I'd personally much rather use https://pgtyped.vercel.app/ https://pgtyped.vercel.app/ than prisma. edit: Ok, I actually read the article... He briefly mentions the migration tool. Author: please go into more detail about how you do schema migrations.

sunflowerdeath 4y ago

I saw arguments against ORMs a lot of times, but what about the opposite - you might not need raw SQL and ORM might work just fine?

ac130kz 4y ago

Yeah, now imagine a system at the scale of Gitlab, it's essentially unmanageable without an ORM, which helps a big time figuring out issues, types, migrating schemas, rewriting and so on. Messing with SQL is suitable for manual query optimization or the simplest databases.

exagram 4y ago

[dead]

drbojingle 4y ago

ORM until you don't need to, then SQL. IF your green fielding. If you already have the SQL, just work with it. If all you're doing is building Crud, though maybe consider Hasura or something that gives you a performant API instead.

thih9 4y ago

This feels like an ad. The author created a tool that makes it easy to ditch ORMs. The tool is referenced 10 times in the article, including the article's first sentence and the last sentence. The article also includes examples on how to use the tool. I'd be fine if it was a "show hn" post. But I thought this is an objective article about ORMs and their pros and cons; this is not the case.

tomhallett 4y ago
I agree that this blog post is part "orm" and part "lead gen for my saas app which helps you write sql queries". I think a blog title which reflects this, ORM and AiHelperBot, would be more helpful. That being said - the faq's on https://aihelperbot.com/ https://aihelperbot.com/ don't answer the main questions I have: 1) I saw a banner somewhere, can't find it now, which said "your schema is only stored in the browser". While that is nice, is my schema transmitted over the wire to your cloud service's servers? 2) If your servers get it, do you send my schema off to OpenAI's ChatGPT? Or some other AI as a service? 3) Do I own the intellectual property of all sql queries written by your tool? 4) How do you verify the "validness" of a query? For example, do you take my schema, load it into a small postgres/etc database and have your code run the query your AI generates? Do you parse/lint the query? Or is the AI good enough that it always generates valid sql? 5) I'm also interested in the "correctness" of a query, from the perspective of "user with the name of john", will the resulting sql returns results with name = "John", "john", " John ", "Johnny", etc - but I am fine with that being left up to the user. 6) Is snowflake/duckdb/clickhouse on your roadmap? Rather than replacing ORM's, helping business users write analytics/BI queries seems like a home run use-case.

timcavel 4y ago

[dead]

Woodi 4y ago

Is it some AI ad or just parody ? Can't decide...

combatentropy 4y ago

If you're happy with your ORM (and, ahem, your users are happy with your app's speed), then you can disregard this post. An ORM is an abstraction over what already is a huge abstraction, which is SQL. Therefore it would feel to me like driving a bus by remote control, or something like that. SQL isn't a procedural language, like C, Fortran, Cobol, Java, JavaScript, Python, Ruby, etc. It is a "query language", I guess, where you tell it what you want, and it decides how it's actually going to find, filter, sort, etc., that data. It's also the only game in town. Is there any widespread alternative to SQL, at least for querying tabular data? The problem with it being so abstract doesn't rear its head with simple SELECT statements. They all seem to go fast enough. It isn't until you're joining tables together, or no longer getting data row for row but instead aggregating, summing, averaging, etc. Suddenly sometimes the whole thing can slow to a crawl. Thankfully databases like Postgres let you prepend a command called EXPLAIN to the problematic query, so that you can diagnose the slowness (if you can understand the output of EXPLAIN). But it was a long time before I got good at reading the EXPLAIN output and finding a way to get it to run faster. Even though SQL is so abstract, more just a description of what you want, there is more than one way to write it --- and the difference in speed can be hundreds or thousands of times. I have had the luxury of working mainly with Postgres for 17 years, and toward the end of it I finally feel supremely confident working directly with it, just using the psql command-line client, and hand-typed SQL in text files, to get exactly what I want, and as fast as I want it (which usually is less than a second). Mind you, I don't work with Big Data, just a variety of CRUD apps but they often have mindbending reports requirements. It should not have taken me years and years to master SQL (nor should it you). I think the reason is partly distraction, for those of us that are "full-stack developers", and also that there is so little education about how to master SQL. I mean, the books and posts are out there, but the attention to them from the community at large is small. But SQL is really not harder than the other languages and techniques you know so well. If developers spent as much time on SQL as they do on the ins and outs of React, containerization, CI/CD pipelines, or the quirks of their favorite programming language, I think they would find it easier than most of those things. And it pays rich dividends, because SQL is cross-platform, you can carry that knowledge with you from job to job for the rest of your life, and your inefficient database queries are making your app use quadruple the hardware and 10 times the latency than if you truly mastered SQL.