12 ms·

Ask HN: ORM or Native SQL?

20 points by sh_tomer 4y ago

Do you use ORM or native SQL, or both? which ORM are you using and how did you choose it?

NetOpWibby 4y ago
Off the top, ORM all the way. I don't like SQL syntax, it makes my head hurt. Boooo. I recently switched from RethinkDB to EdgeDB, and it uses SQL under the hood. I love it, it's awesome. I don't need raw SQL but I think EdgeDB allows you to write queries if you want.
- belmont_sup 4y ago
  How do you host your edgedb server? I’ve entertained it but didn’t like the idea of having to host 3 servers just to have my app, edgedb, and a database. Strictly speaking toy projects here. Db can be a managed free tier. I guess I can make edgedb run on the same server as my app but in another process.
  - NetOpWibby 4y ago
    I do! I wrote a nameserver in Deno and have it sitting on the same server as EdgeDB. The baremetal instructions are missing a bit when I was attempting this setup last month but to the devs' credit, they updated the documentation to address my issues. The EdgeDB team is going to release a managed service sometime "soon".
    - belmont_sup 4y ago
      Guess I’ll have to try it now. Just saw they released a .net client too. Neat.
nextaccountic 4y ago
The best solution I've ever seen is this Rust library https://github.com/cornucopia-rs/cornucopia https://github.com/cornucopia-rs/cornucopia You write plain SQL for you schema (just a schema.sql is enough) and plain SQL functions for your queries. Then it generates Rust types and Rust functions from from that. If you don't use Rust, maybe there's a library like that for your favorite language. Optionally, pair it with https://github.com/bikeshedder/tusker https://github.com/bikeshedder/tusker or https://github.com/blainehansen/postgres_migrator https://github.com/blainehansen/postgres_migrator (both are based off https://github.com/djrobstep/migra https://github.com/djrobstep/migra) to generate migrations by diffing your schema.sql files, and https://github.com/rust-db/refinery https://github.com/rust-db/refinery to perform those migrations. Now, if you have simple crud needs, you should probably use https://postgrest.org/en/stable/ https://postgrest.org/en/stable/ and not an ORM. There are packages like https://www.npmjs.com/package/@supabase/postgrest-js https://www.npmjs.com/package/@supabase/postgrest-js (for JS / typescript) and probably for other languages too. If you insist on an ORM, the best of the bunch is prisma https://www.prisma.io/ https://www.prisma.io/ - outside of the typescript/javascript ecosystem it has ports for some other languages (with varying degrees of completion), the one I know about is the Rust one https://prisma.brendonovich.dev/introduction https://prisma.brendonovich.dev/introduction
- sa46 4y ago
  Cornucopia is neat. I wrote a similar library in Go [1] so I'm very interested in comparing design decisions. The pros of the generated code per query approach: - App code is coupled to query outputs and inputs (an API of sorts), not database tables. Therefore, you can refactor your DB without changing app code. - Real SQL with the full breadth of DB features. - Real type-checking with what the DB supports. The cons: - Type mapping is surprisingly hard to get right, especially with composite types and arrays and custom type converters. For example, a query might return multiple jsonb columns but the app code wants to parse them into different structs. - Dynamic queries don't work with prepared statements. Prepared statements only support values, not identifiers or scalar SQL sub-queries, so the codegen layer needs a mechanism to template SQL. I haven't built this out yet but would like to. [1]: https://github.com/jschaf/pggen https://github.com/jschaf/pggen
  - nextaccountic 4y ago
    > Therefore, you can refactor your DB without changing app code. Indeed, that's incredible! I tend to think that the actual layout of db tables a low level concern that is mainly driven by query performance and simplicity. > - Type mapping is surprisingly hard to get right, especially with composite types and arrays and custom type converters. For example, a query might return multiple jsonb columns but the app code wants to parse them into different structs. In Rust there's serde for converting json into structs (or enums if the shape of the json is complicated enough). Doesn't Go have a similar serialization/deserialization library? > - Dynamic queries don't work with prepared statements. Prepared statements only support values, not identifiers or scalar SQL sub-queries, so the codegen layer needs a mechanism to template SQL. I haven't built this out yet but would like to. In this case, what about stored procedures? But, generally, instead of a template language for SQL, I would like to have a real compile-to-SQL higher level language, that could also do dynamic queries. The trouble with templating is that dynamic queries are hard to do in a type-safe manner, and it's hard to prevent generating invalid SQL when you have a bad template substitution (and then you get bad error messages from the db). There's a few languages like this do this https://github.com/ajnsit/languages-that-compile-to-sql https://github.com/ajnsit/languages-that-compile-to-sql but none fits the bill
kevincox 4y ago
I started a project recently using no ORM and having the flexibility to easily merge queries and extract exactly the data I need is very helpful. Converting the rows to objects hasn't been a major concern and I have compile-time type checking against the database schema (for non-dynamically built queries). I'd say that the only real issue I have is building dynamic queries and some helpers for joins to make it easy to confirm that joins are against the right keys and tables. But that hasn't been much of an issue.
Canada 4y ago
I strongly prefer SQL over any ORM.
themadsens 4y ago
My absolute most horrid work experiences have all involved Hibernate / JPA. It slows everything to a grinding crawl, works somewhat 99.9 % of the time and creates inexplicable un-debuggable errors for the remaining 0.1% On top of that you have to ditch referential integrity to make it at least limp along Give me plain SQL or something lowlevel like sql2o any day
- arisAlexis 4y ago
  Hibernate is masochitic
  - emerongi 4y ago
    How to turn a 1x developer into a 10x developer? Stop using Hibernate. I might still have psychological trauma from it.
- karmakaze 4y ago
  I completely agree. I pretty much stopped using Spring/Boot because of it, even though it could be used without Hibernate/JPA. I tried sql2o and later switched to jdbi and Javalin for a lightweight framework. I started making a typesafe library[0] that maps bottom-up like SQL expressions but development as stalled as I haven't been doing much side-project work to use it. [0] https://github.com/karmakaze/safeql https://github.com/karmakaze/safeql
postalrat 4y ago
No ORM. Sucks having to learn a new ORM every project. Even worse when you need to do something in a new ORM one step more advance than the tutorials show you.
- simplotek 4y ago
  > No ORM. Sucks having to learn a new ORM every project. I don't quite follow your logic. ORMs provide a lot of critical features for free right out of the box which take a lot of time and effort to roll out by hand. Database migrations are perhaps the hardest to get right, for example. Why do you believe that the trivial job of onboarding onto a ORM is too much to handle, but manually rolling out and maintaining and debugging hand-written code is somehow an acceptable loss of productivity?
  - javcasas 4y ago
    Learning the ORM is easy. On the other hand, learning the ORM bugs...
  - postalrat 4y ago
    It's never a trivial job. And in my experience it's rarely a single ORM. You will have one for the js code, on for the .net code, one for php, on for ruby, etc, etc. And your developers are going to be sitting read docs for hours to do many things beyond a trivial query.
freedomben 4y ago
I normally dislike ORMs, but Elixir's Ecto has changed my mind on it. It's a DSL that is very close/similar to SQL, enough so that it feels to me like writing SQL. IMHO it's the best of both worlds because it's close to SQL but it returns rich Elixir Structs and provides exceptions/error handling in an idiomatic way for the language.
- h0l0cube 4y ago
  Ecto is more than a typical ORM. You can map queries into arbitrary structures, which permits arbitrary joins and selects. It also provides better mechanisms for validation for insert/update that go beyond enforcing type annotations
milkllz 4y ago
I really like microORM call Dapper when using C#.You still write native sql but it's typed.
Nican 4y ago
Both. StackOverflow official blog mentions they are mixing both Entity Framework and Dapper. [1] [2] Specially with larger projects, I generally find that ORMs can be good enough for 90% of your code. It also enforces strongly typed models, and makes it easier for refacorting, and finding where the models and variables are being used. The 10% critical section of the code, where performance is more sensitive, SQL is acceptable, and needs to be carefully looked at/reviewed. [1] https://stackoverflow.blog/2022/07/04/how-stack-overflow-is-leveling-up-its-unit-testing-game/ https://stackoverflow.blog/2022/07/04/how-stack-overflow-is-... [2] https://www.reddit.com/r/dotnet/comments/9mg7v6/with_stack_overflows_recent_choice_to_mix_dapper/ https://www.reddit.com/r/dotnet/comments/9mg7v6/with_stack_o...
exabrial 4y ago
JPA + EclipseLink. It's pretty simple: em.getTransaction().begin(); customer.setName("smith"); ::bunch of other atomic actions here:: em.getTransaction().commit(): JPA offers caching, cache eviction, and falls back to automatic Version columns (update customer where name = bob and version = 1 set name = smith, version = 2). We use ActiveMQ to put cache eviction notices onto a Topic and distribute them out the JVMs that advertise interest in the objects. Cache hits can be obtained by "business key" like "cust-00001" (or primary key internally)... A common use case is not causing database I/O on RESTful URLs: http://example.com/customer/cust-00001 http://example.com/customer/cust-00001 Granted there is some really complex stuff we dip into native SQL and stored procedures, but JPA does 95% of everything and we handle everything else as it comes. Highly recommend.
Semaphor 4y ago
Simple cases (query a single view/table fully, or get one item, mutate and save): ORM (EF core) More complex queries or needing only a scalar value: Raw SQL via dapper.
lonk11 4y ago
Likely depends on the language. With Go I like using https://sqlc.dev/ https://sqlc.dev/ - it generates Go-code from a SQL script. You get the benefits of static typing for parameters and return types + it removes much of the boilerplate involved in using plain SQL. And you still get the full expressiveness of SQL. I think this is a middle ground between SQL and ORM.
lost_tourist 4y ago
I'm no database admin but I find myself fighting ORMs, I'd rather use SQL for anything but the most basic queries in any given language that I've worked in. It just feels more natural to me. I think it's an opinion thing though. However for me I learned it once and it's easy to adapt.
marstall 4y ago
Any ORM as long as its ActiveRecord.
hot_gril 4y ago
I've used both repeatedly, and I will never use an ORM again. It's never gone well. Conversely, direct SQL read/write works great for a variety of applications if you do it right. No complaints about that. Pretty convinced the only appeal of ORMs is for people who want OOP everywhere or just don't want to learn SQL, but it's worth learning SQL and the basics of a normalized DB schema. Sometimes it's supposed to be about abstracting away the DBMS. But the interface between your code and the DBMS is usually very "wide," plus you might be working around performance concerns. You can try to abstract it away with an ORM, but the abstraction will be just as wide, and now you have another layer introducing limitations and performance issues. So it seems to go against basic software design, but you really have to accept that your backend code is going to be married to your DBMS. It's either that or marry the ORM. Forgive the title, but I also agree with the points here: https://blog.codinghorror.com/object-relational-mapping-is-the-vietnam-of-computer-science/ https://blog.codinghorror.com/object-relational-mapping-is-t... And if your ORM isn't a super well-supported thing, good luck. When I started my new job years ago, they said they were starting to use a custom DB/ORM-as-a-service thing maintained by a partner team. That team had great engineers, but it was simply too big a project for them to essentially build a DBMS on top of a DBMS, so it was a mess. I tried to act like this was ok until one day I showed a skip-manager some unfixable race conditions that broke our whole application. Our team got new devs who also complained about it. It also happened to not comply with some unrelated department mandate, which gave me the justification to take the reigns on our team bailing us out of that. Took a full codebase rewrite where I just gave them a regular SQL database. I'd say it cost us an entire year of development in the end.
Alifatisk 4y ago
ORM all the way, I write native SQL when I really need something specific but most of the time, ActiveRecord does the job well.
spfzero 4y ago
Both, but I find myself having to learn a new ORM practically every time I switch languages and platforms, or just every 5 years or so because new "better" ones come out. Also, I often do not feel I've every really "learned" the ORM. It doesn't act like my understanding of it, all of the time. Either my laziness, poor documentation, etc. So I keep coming back to SQL, even if it means rewriting code I've already written. At least with SQL I know exactly what's going on, probably because I have a lot of experience with it. With SQL I can try code out against the database in parallel if needed. It always works exactly as expected. I get the paradigm so I don't have trouble writing it correctly. I don't have confusing bugs related to saving state vs. leaving it in memory. YMMV.
WorldMaker 4y ago
Choosing this depends a lot on your language, its ecosystem, and your application's own ecosystem (if your app shares a database, ORMs aren't necessarily out of the question, but have a lot more hurdles to overcome). In Python using the Django ORM lights up all sorts of great features in the Django Framework because its automated admin and automated CRUD views feed off the ORM's information and it's often silly not to use Django's ORM for how much free productivity it can buy you. But then if you aren't using Django with Python you've got far more choices of ORMs and maybe fewer reasons to use one or another. In C#, I'm a massive fan of LINQ. I think recent versions of EF (Entity Framework) have been rock solid and do everything that I need to do. (Early versions EF that supported LINQ got some (deserved) bad reputations for its query transformer sometimes doing surprising things. As someone who had to debug many of them, I knew that pain directly. Ever since the "reboot" (EF Core 1.0), EF has had a very good, very "no magic" query transformer that doesn't do anything you don't ask it to do. I do mean it greatly when I say that recent versions have been rock solid.) With LINQ (in either of its forms) I almost never feel like I need to write a query directly in SQL (and most of what is left are fun rare things like MERGE statements) and LINQ offers somethings that writing in SQL can't. Type safety is obviously the big one for why you should want to choose an ORM in the first place. But there are also small things where I think LINQ ergonomics are actually better than their raw SQL equivalents (things like WHERE versus HAVING when writing GROUP BY logic). I've also worked on massive shared database "microservice architectures" where everything needed to be "Native SQL" and not just "Native SQL" but hand tuned micro-apps written in T-SQL (or equivalents) to insure data integrity and overall performance of the entire ecosystem. I don't really recommend architecting applications that way, but I do know they exist for all sorts of reasons and have had cases where any ORM involvement made things more difficult. (That said, in C# modern EF support for type safe calls of stored procedures and views is pretty great and I'd probably still use that today rather than building hand-managed ADO.NET wrappers like I used to have to in those days even if most of the actual ORM parts and query transformation tools were left unused.) Ask two developers and you'll sometimes get five different answers. There's a lot of personal preference here, mixed with all sorts of experiences from all sorts of ORMs. There are so many ORMs to choose from out there and most answers about one don't apply to others, and with EF as an obvious example even the same ORM can have massively different reputations in different eras of its lifespan.
ehutch79 4y ago
I like the Django ORM a lot. That said, I know SQL, and I will absolutely say if you use an ORM you should know SQL.
- zach_garwood 4y ago
  Django ORM is good for ~90% of queries, but there's always that pesky query that would be too convoluted to build up with the ORM, so it's nice to be able to jump down to the DB API and `execute()` a custom query.
- rileymat2 4y ago
  It has been a long time since I have used it, but the ability to create test fixtures and have them run in an in memory sqlite database was killer feature.
reactspa 4y ago
RedbeanPHP has been a lifesaver for me. https://redbeanphp.com/index.php https://redbeanphp.com/index.php So easy to set-up and use. Lots of built in PHP security. Covers 99% of my need. Allows me to use native SQL to cover the remaining 1%.
nine_k 4y ago
Neither. Native SQL syntax is not very uniform, and it prevents parts from composing easily. String manipulation is fiddly, even with nicer (named) parameter substitution. A typical tutorial ORM approach, with mapping relational data to objects, and then allowing to add data manipulation methods to the objects, and especially to override save(), kills most of the advantages of relational databases. It is particularly bad when you need to reach for parts of rows, or do updates on multiple rows, or even selects with more more complex conditions / joins. The sweet spot is a library that allows you to easily describe tables, and write composable, terse statements that translate to SQL. In Python land, for instance, both SQLAlchemy and Django ORM allow that. They allow more but I usually avoid making table models rich. SQLAlchemy is the most composable, Lego-like of the two. You can, for instance, factor out complex conditions and share, mix and match them between various selects, updates, deletes, as needed. Django ORM excels at being terse and describing joins in a select without even mentioning them, just referring to other tables via double__underscore, if the relation is already described in the table models. Both operate with result sets which can be not only fetched but also combined. You can write a select (or .filter in Django) and e.g. give it as a parameter to another select, and the ORM layer will rewrite the result as a single query with appropriate joins or sub-selects. You can easily issue updates to multiple records without fetching and hydrating a single one of them. It's a massive performance improvement compared to the naive ORM approach where you operate on DB data as if it were objects in RAM: a loop to fetch, update, and save each object. You can easily limit the result set to the particular set of columns you want at the time, and avoid hydrating result sets into full-fledged objects which may be too large or unnecessarily nested. You can of course sometimes use "proper" objects formed from relational data, when you need to compute something interesting from their attributes for rendering / reporting. But I try to avoid even that where possible.
iccananea 4y ago
Native sql with sqlc.dev
javcasas 4y ago
Query builder or SQL.
tra3 4y ago
How do you unit test your sql? At least with orms, the data access is tested implicitly via business logic unit tests.
- noisem4ker 4y ago
  At the repository (data access) application layer. For a query (e.g. loading an entity): - Arrange: test database loaded with fixtures - Act: execute repository method - Assert: returned data corresponds to fixture For updating queries: additionally assert the new database state. Repository tests shouldn't care nor know whether the methods under test internally execute raw SQL or use ORM methods. This allows freely switching and mixing the two.
newusertoday 4y ago
native sql with auto generation of code on server side.

20 points by sh_tomer 4y ago

Do you use ORM or native SQL, or both? which ORM are you using and how did you choose it?

Off the top, ORM all the way. I don't like SQL syntax, it makes my head hurt. Boooo. I recently switched from RethinkDB to EdgeDB, and it uses SQL under the hood. I love it, it's awesome. I don't need raw SQL but I think EdgeDB allows you to write queries if you want.

belmont_sup 4y ago
How do you host your edgedb server? I’ve entertained it but didn’t like the idea of having to host 3 servers just to have my app, edgedb, and a database. Strictly speaking toy projects here. Db can be a managed free tier. I guess I can make edgedb run on the same server as my app but in another process.
- NetOpWibby 4y ago
  I do! I wrote a nameserver in Deno and have it sitting on the same server as EdgeDB. The baremetal instructions are missing a bit when I was attempting this setup last month but to the devs' credit, they updated the documentation to address my issues. The EdgeDB team is going to release a managed service sometime "soon".
  - belmont_sup 4y ago
    Guess I’ll have to try it now. Just saw they released a .net client too. Neat.

nextaccountic 4y ago

The best solution I've ever seen is this Rust library https://github.com/cornucopia-rs/cornucopia https://github.com/cornucopia-rs/cornucopia You write plain SQL for you schema (just a schema.sql is enough) and plain SQL functions for your queries. Then it generates Rust types and Rust functions from from that. If you don't use Rust, maybe there's a library like that for your favorite language. Optionally, pair it with https://github.com/bikeshedder/tusker https://github.com/bikeshedder/tusker or https://github.com/blainehansen/postgres_migrator https://github.com/blainehansen/postgres_migrator (both are based off https://github.com/djrobstep/migra https://github.com/djrobstep/migra) to generate migrations by diffing your schema.sql files, and https://github.com/rust-db/refinery https://github.com/rust-db/refinery to perform those migrations. Now, if you have simple crud needs, you should probably use https://postgrest.org/en/stable/ https://postgrest.org/en/stable/ and not an ORM. There are packages like https://www.npmjs.com/package/@supabase/postgrest-js https://www.npmjs.com/package/@supabase/postgrest-js (for JS / typescript) and probably for other languages too. If you insist on an ORM, the best of the bunch is prisma https://www.prisma.io/ https://www.prisma.io/ - outside of the typescript/javascript ecosystem it has ports for some other languages (with varying degrees of completion), the one I know about is the Rust one https://prisma.brendonovich.dev/introduction https://prisma.brendonovich.dev/introduction

sa46 4y ago
Cornucopia is neat. I wrote a similar library in Go [1] so I'm very interested in comparing design decisions. The pros of the generated code per query approach: - App code is coupled to query outputs and inputs (an API of sorts), not database tables. Therefore, you can refactor your DB without changing app code. - Real SQL with the full breadth of DB features. - Real type-checking with what the DB supports. The cons: - Type mapping is surprisingly hard to get right, especially with composite types and arrays and custom type converters. For example, a query might return multiple jsonb columns but the app code wants to parse them into different structs. - Dynamic queries don't work with prepared statements. Prepared statements only support values, not identifiers or scalar SQL sub-queries, so the codegen layer needs a mechanism to template SQL. I haven't built this out yet but would like to. [1]: https://github.com/jschaf/pggen https://github.com/jschaf/pggen
- nextaccountic 4y ago
  > Therefore, you can refactor your DB without changing app code. Indeed, that's incredible! I tend to think that the actual layout of db tables a low level concern that is mainly driven by query performance and simplicity. > - Type mapping is surprisingly hard to get right, especially with composite types and arrays and custom type converters. For example, a query might return multiple jsonb columns but the app code wants to parse them into different structs. In Rust there's serde for converting json into structs (or enums if the shape of the json is complicated enough). Doesn't Go have a similar serialization/deserialization library? > - Dynamic queries don't work with prepared statements. Prepared statements only support values, not identifiers or scalar SQL sub-queries, so the codegen layer needs a mechanism to template SQL. I haven't built this out yet but would like to. In this case, what about stored procedures? But, generally, instead of a template language for SQL, I would like to have a real compile-to-SQL higher level language, that could also do dynamic queries. The trouble with templating is that dynamic queries are hard to do in a type-safe manner, and it's hard to prevent generating invalid SQL when you have a bad template substitution (and then you get bad error messages from the db). There's a few languages like this do this https://github.com/ajnsit/languages-that-compile-to-sql https://github.com/ajnsit/languages-that-compile-to-sql but none fits the bill

kevincox 4y ago

I started a project recently using no ORM and having the flexibility to easily merge queries and extract exactly the data I need is very helpful. Converting the rows to objects hasn't been a major concern and I have compile-time type checking against the database schema (for non-dynamically built queries). I'd say that the only real issue I have is building dynamic queries and some helpers for joins to make it easy to confirm that joins are against the right keys and tables. But that hasn't been much of an issue.

Canada 4y ago

I strongly prefer SQL over any ORM.

themadsens 4y ago

My absolute most horrid work experiences have all involved Hibernate / JPA. It slows everything to a grinding crawl, works somewhat 99.9 % of the time and creates inexplicable un-debuggable errors for the remaining 0.1% On top of that you have to ditch referential integrity to make it at least limp along Give me plain SQL or something lowlevel like sql2o any day

arisAlexis 4y ago
Hibernate is masochitic
- emerongi 4y ago
  How to turn a 1x developer into a 10x developer? Stop using Hibernate. I might still have psychological trauma from it.
karmakaze 4y ago
I completely agree. I pretty much stopped using Spring/Boot because of it, even though it could be used without Hibernate/JPA. I tried sql2o and later switched to jdbi and Javalin for a lightweight framework. I started making a typesafe library[0] that maps bottom-up like SQL expressions but development as stalled as I haven't been doing much side-project work to use it. [0] https://github.com/karmakaze/safeql https://github.com/karmakaze/safeql

postalrat 4y ago

No ORM. Sucks having to learn a new ORM every project. Even worse when you need to do something in a new ORM one step more advance than the tutorials show you.

simplotek 4y ago
> No ORM. Sucks having to learn a new ORM every project. I don't quite follow your logic. ORMs provide a lot of critical features for free right out of the box which take a lot of time and effort to roll out by hand. Database migrations are perhaps the hardest to get right, for example. Why do you believe that the trivial job of onboarding onto a ORM is too much to handle, but manually rolling out and maintaining and debugging hand-written code is somehow an acceptable loss of productivity?
- javcasas 4y ago
  Learning the ORM is easy. On the other hand, learning the ORM bugs...
- postalrat 4y ago
  It's never a trivial job. And in my experience it's rarely a single ORM. You will have one for the js code, on for the .net code, one for php, on for ruby, etc, etc. And your developers are going to be sitting read docs for hours to do many things beyond a trivial query.

freedomben 4y ago

I normally dislike ORMs, but Elixir's Ecto has changed my mind on it. It's a DSL that is very close/similar to SQL, enough so that it feels to me like writing SQL. IMHO it's the best of both worlds because it's close to SQL but it returns rich Elixir Structs and provides exceptions/error handling in an idiomatic way for the language.

h0l0cube 4y ago
Ecto is more than a typical ORM. You can map queries into arbitrary structures, which permits arbitrary joins and selects. It also provides better mechanisms for validation for insert/update that go beyond enforcing type annotations

milkllz 4y ago

I really like microORM call Dapper when using C#.You still write native sql but it's typed.

Nican 4y ago

Both. StackOverflow official blog mentions they are mixing both Entity Framework and Dapper. [1] [2] Specially with larger projects, I generally find that ORMs can be good enough for 90% of your code. It also enforces strongly typed models, and makes it easier for refacorting, and finding where the models and variables are being used. The 10% critical section of the code, where performance is more sensitive, SQL is acceptable, and needs to be carefully looked at/reviewed. [1] https://stackoverflow.blog/2022/07/04/how-stack-overflow-is-leveling-up-its-unit-testing-game/ https://stackoverflow.blog/2022/07/04/how-stack-overflow-is-... [2] https://www.reddit.com/r/dotnet/comments/9mg7v6/with_stack_overflows_recent_choice_to_mix_dapper/ https://www.reddit.com/r/dotnet/comments/9mg7v6/with_stack_o...

exabrial 4y ago

JPA + EclipseLink. It's pretty simple: em.getTransaction().begin(); customer.setName("smith"); ::bunch of other atomic actions here:: em.getTransaction().commit(): JPA offers caching, cache eviction, and falls back to automatic Version columns (update customer where name = bob and version = 1 set name = smith, version = 2). We use ActiveMQ to put cache eviction notices onto a Topic and distribute them out the JVMs that advertise interest in the objects. Cache hits can be obtained by "business key" like "cust-00001" (or primary key internally)... A common use case is not causing database I/O on RESTful URLs: http://example.com/customer/cust-00001 http://example.com/customer/cust-00001 Granted there is some really complex stuff we dip into native SQL and stored procedures, but JPA does 95% of everything and we handle everything else as it comes. Highly recommend.

Semaphor 4y ago

Simple cases (query a single view/table fully, or get one item, mutate and save): ORM (EF core) More complex queries or needing only a scalar value: Raw SQL via dapper.

lonk11 4y ago

Likely depends on the language. With Go I like using https://sqlc.dev/ https://sqlc.dev/ - it generates Go-code from a SQL script. You get the benefits of static typing for parameters and return types + it removes much of the boilerplate involved in using plain SQL. And you still get the full expressiveness of SQL. I think this is a middle ground between SQL and ORM.

lost_tourist 4y ago

I'm no database admin but I find myself fighting ORMs, I'd rather use SQL for anything but the most basic queries in any given language that I've worked in. It just feels more natural to me. I think it's an opinion thing though. However for me I learned it once and it's easy to adapt.

marstall 4y ago

Any ORM as long as its ActiveRecord.

hot_gril 4y ago

I've used both repeatedly, and I will never use an ORM again. It's never gone well. Conversely, direct SQL read/write works great for a variety of applications if you do it right. No complaints about that. Pretty convinced the only appeal of ORMs is for people who want OOP everywhere or just don't want to learn SQL, but it's worth learning SQL and the basics of a normalized DB schema. Sometimes it's supposed to be about abstracting away the DBMS. But the interface between your code and the DBMS is usually very "wide," plus you might be working around performance concerns. You can try to abstract it away with an ORM, but the abstraction will be just as wide, and now you have another layer introducing limitations and performance issues. So it seems to go against basic software design, but you really have to accept that your backend code is going to be married to your DBMS. It's either that or marry the ORM. Forgive the title, but I also agree with the points here: https://blog.codinghorror.com/object-relational-mapping-is-the-vietnam-of-computer-science/ https://blog.codinghorror.com/object-relational-mapping-is-t... And if your ORM isn't a super well-supported thing, good luck. When I started my new job years ago, they said they were starting to use a custom DB/ORM-as-a-service thing maintained by a partner team. That team had great engineers, but it was simply too big a project for them to essentially build a DBMS on top of a DBMS, so it was a mess. I tried to act like this was ok until one day I showed a skip-manager some unfixable race conditions that broke our whole application. Our team got new devs who also complained about it. It also happened to not comply with some unrelated department mandate, which gave me the justification to take the reigns on our team bailing us out of that. Took a full codebase rewrite where I just gave them a regular SQL database. I'd say it cost us an entire year of development in the end.

Alifatisk 4y ago

ORM all the way, I write native SQL when I really need something specific but most of the time, ActiveRecord does the job well.

spfzero 4y ago

Both, but I find myself having to learn a new ORM practically every time I switch languages and platforms, or just every 5 years or so because new "better" ones come out. Also, I often do not feel I've every really "learned" the ORM. It doesn't act like my understanding of it, all of the time. Either my laziness, poor documentation, etc. So I keep coming back to SQL, even if it means rewriting code I've already written. At least with SQL I know exactly what's going on, probably because I have a lot of experience with it. With SQL I can try code out against the database in parallel if needed. It always works exactly as expected. I get the paradigm so I don't have trouble writing it correctly. I don't have confusing bugs related to saving state vs. leaving it in memory. YMMV.

WorldMaker 4y ago

Choosing this depends a lot on your language, its ecosystem, and your application's own ecosystem (if your app shares a database, ORMs aren't necessarily out of the question, but have a lot more hurdles to overcome). In Python using the Django ORM lights up all sorts of great features in the Django Framework because its automated admin and automated CRUD views feed off the ORM's information and it's often silly not to use Django's ORM for how much free productivity it can buy you. But then if you aren't using Django with Python you've got far more choices of ORMs and maybe fewer reasons to use one or another. In C#, I'm a massive fan of LINQ. I think recent versions of EF (Entity Framework) have been rock solid and do everything that I need to do. (Early versions EF that supported LINQ got some (deserved) bad reputations for its query transformer sometimes doing surprising things. As someone who had to debug many of them, I knew that pain directly. Ever since the "reboot" (EF Core 1.0), EF has had a very good, very "no magic" query transformer that doesn't do anything you don't ask it to do. I do mean it greatly when I say that recent versions have been rock solid.) With LINQ (in either of its forms) I almost never feel like I need to write a query directly in SQL (and most of what is left are fun rare things like MERGE statements) and LINQ offers somethings that writing in SQL can't. Type safety is obviously the big one for why you should want to choose an ORM in the first place. But there are also small things where I think LINQ ergonomics are actually better than their raw SQL equivalents (things like WHERE versus HAVING when writing GROUP BY logic). I've also worked on massive shared database "microservice architectures" where everything needed to be "Native SQL" and not just "Native SQL" but hand tuned micro-apps written in T-SQL (or equivalents) to insure data integrity and overall performance of the entire ecosystem. I don't really recommend architecting applications that way, but I do know they exist for all sorts of reasons and have had cases where any ORM involvement made things more difficult. (That said, in C# modern EF support for type safe calls of stored procedures and views is pretty great and I'd probably still use that today rather than building hand-managed ADO.NET wrappers like I used to have to in those days even if most of the actual ORM parts and query transformation tools were left unused.) Ask two developers and you'll sometimes get five different answers. There's a lot of personal preference here, mixed with all sorts of experiences from all sorts of ORMs. There are so many ORMs to choose from out there and most answers about one don't apply to others, and with EF as an obvious example even the same ORM can have massively different reputations in different eras of its lifespan.

ehutch79 4y ago

I like the Django ORM a lot. That said, I know SQL, and I will absolutely say if you use an ORM you should know SQL.

zach_garwood 4y ago
Django ORM is good for ~90% of queries, but there's always that pesky query that would be too convoluted to build up with the ORM, so it's nice to be able to jump down to the DB API and `execute()` a custom query.
rileymat2 4y ago
It has been a long time since I have used it, but the ability to create test fixtures and have them run in an in memory sqlite database was killer feature.

reactspa 4y ago

RedbeanPHP has been a lifesaver for me. https://redbeanphp.com/index.php https://redbeanphp.com/index.php So easy to set-up and use. Lots of built in PHP security. Covers 99% of my need. Allows me to use native SQL to cover the remaining 1%.

nine_k 4y ago

Neither. Native SQL syntax is not very uniform, and it prevents parts from composing easily. String manipulation is fiddly, even with nicer (named) parameter substitution. A typical tutorial ORM approach, with mapping relational data to objects, and then allowing to add data manipulation methods to the objects, and especially to override save(), kills most of the advantages of relational databases. It is particularly bad when you need to reach for parts of rows, or do updates on multiple rows, or even selects with more more complex conditions / joins. The sweet spot is a library that allows you to easily describe tables, and write composable, terse statements that translate to SQL. In Python land, for instance, both SQLAlchemy and Django ORM allow that. They allow more but I usually avoid making table models rich. SQLAlchemy is the most composable, Lego-like of the two. You can, for instance, factor out complex conditions and share, mix and match them between various selects, updates, deletes, as needed. Django ORM excels at being terse and describing joins in a select without even mentioning them, just referring to other tables via double__underscore, if the relation is already described in the table models. Both operate with result sets which can be not only fetched but also combined. You can write a select (or .filter in Django) and e.g. give it as a parameter to another select, and the ORM layer will rewrite the result as a single query with appropriate joins or sub-selects. You can easily issue updates to multiple records without fetching and hydrating a single one of them. It's a massive performance improvement compared to the naive ORM approach where you operate on DB data as if it were objects in RAM: a loop to fetch, update, and save each object. You can easily limit the result set to the particular set of columns you want at the time, and avoid hydrating result sets into full-fledged objects which may be too large or unnecessarily nested. You can of course sometimes use "proper" objects formed from relational data, when you need to compute something interesting from their attributes for rendering / reporting. But I try to avoid even that where possible.

iccananea 4y ago

Native sql with sqlc.dev

javcasas 4y ago

Query builder or SQL.

tra3 4y ago

How do you unit test your sql? At least with orms, the data access is tested implicitly via business logic unit tests.

noisem4ker 4y ago
At the repository (data access) application layer. For a query (e.g. loading an entity): - Arrange: test database loaded with fixtures - Act: execute repository method - Assert: returned data corresponds to fixture For updating queries: additionally assert the new database state. Repository tests shouldn't care nor know whether the methods under test internally execute raw SQL or use ORM methods. This allows freely switching and mixing the two.

newusertoday 4y ago

native sql with auto generation of code on server side.