As a grandpa dev, instead of learning 5 interchangeable languages of Algol-60 family , if you are a Jr. Dev. spend time learning: 1. Databases: esp db internals and query optimization 2. Unix/Linux esp the networking stack and file system stuff 3. Get into the details of at least one cloud platform 4. Learn bash: the number of problems that can be solved by a simple shell script (sed+awk+grep+pipes) 5. The art of writing clearly and concisely.
Definitely one of the best comments I have read so far on this channel. Learn the fundamentals first and not the tools because, in the end, everyone can learn a framework or an ORM by using it but if you have more knowledge you will be the one that finds the quirks and the best solutions for any type of problem.
@@siniarskimar This is the only channel where I hear SQL pronounced as "squeal". The official pronunciation from wiktionary and e.g. mysql-devs would be "es-cue-el" since it is an abbreviation. But I also heard and read the pronunciation "sequel", but never "squeal".
Django's ORM has an amazing migration system and schema definition, managing indexes is easy, and you always have the option to use the DB connector and raw dog the SQL query. Flexibility and using different paradigms is the answer. They aren't mutually exclusive
I like the migration tool I hate the query generator, how Django ORM translate subquery is just a mess, if you're using subquery do it in raw, is easier and has better performance.
@@JaimeChereauBecause Django ORM is so flexible in all use-cases. You don't need to write raw sql 99% of the time, but when you do, the django.db.connection API is available.
What is not nice is debugging that django orm. It does lazy loading by default and when you look at the variables they are a mess of underscore functions. I have spent 2.5 years on a old django application and let me tell you, it is not nice at all. Raw sql with something simple like express or flask is way easier to understand and maintain. Migrating feature is nice at first but when you have a test db, local db and a production db which all need that migration applied it falls appart quick and becomes tedious. Rather have a sql script written up to quickly apply all the migrations needed.
In context of an android-app, Room from Google in combination with sqlite is really good and gives type safety and compile-time syntax checking of sql-statements.
@rychardvale you wouldn't happen to have any more info on that would you? Been using prisma for a month now for the type safety but been missing my knex query building, does this kysely hook into the prisma types?
This was back around 1993 as I recall, and I was brought in to determine why it was taking around 30 seconds per check to print checks. They needed to print thousands of checks per run. Yeah, they had an obvious problem. The only real bottleneck should have been the printing speed of the laser printers themselves, not the data processing. The first thing I did was to take a look at the database that the information being printed on the checks was coming from. INCREDIBLY, I saw the problem immediately. While the table contained the unique ID of the checks, that field in the table had not been defined as the unique ID and it had no index. I redefined that field in the table, and BAM!, problem instantly fixed.
My experience of not wanting to use an ORM several times: You still have to map the returned object to a class of some sorts because it is self-documenting in code and you get intellisense. Then you want to automate some boilerplate code away and abstract it out a little bit. Then you want to use the same model for updating data as well because it saves you time. Next thing you know you have a half-baked ORM of your own making that none else knows how to use. In my home PHP project i ended up with SOMETHING that looks like a mix of Doctrine and Eloquent. So now I just slap ORM in from the start.
I have been using Django for years and where I have had to resort to raw SQL to fix performance issues, I’ve eventually fixed properly by using the ORM correctly. And you raised a great point… even if you write it in SQL, you still have to put it into some kind of object model.
Same. A lot of issues mentioned in the post feel like they're language/ORM specific, too. In C# for example, you can use EF Core with the "Select()" method to select specific rows. "Select(row => new Model{ Id = row.Id, Name = row.Name })" for example, will issue a "SELECT Id, Name FROM table" statement, which is pretty nice, as far as I'm concerned. The only thing it doesn't play well with, in my experience, are computed columns and stored procedures, but I'd argue you should just fire whoever wants to place all your basic CRUD logic in those anyways and save yourself the trouble.
I once used an ORM to bind an existing TERRIBLE DB to a model that was easier to work with. Makes it easier to get back into it after a break, without having to learn the whole DB layout and relations again.
What I most hate about ORMs is that every ORM has its own API, so you spend a lot of time learning and fighting this API, I prefer to just use SQL, and finding help for SQL is a lot easier than for a specific ORM.
+1 I started my Java career working with the Hibernate ORM. Right in the Hibernate book written by the ORM creators, it starts with "Learn SQL first". And you *can* tune most ORM queries to eager or lazy load joins/associations as needed. So performance can be fine. But I just don't see how it's worth the additional learning curve when you must know SQL anyway.
I really like Haskell's quasi quoters for this, where you can throw SQL strings into your code with what looks like string interpolation but is actually automatically sanitized
Another option is not to go with either ORM or SQL, but use them together. A textbook example is using CQRS to separate your reads and writes and then using the ORM on the write side and raw SQL on the read side. This way you can use ORM features that make your life easier for writes (identity map and in-memory cacheing, update detection, easily map complex properties to json columns etc.), and use raw SQL for reads/projections. Also, some ORMs like EF Core can handle migrations as well.
100% true, I hate devs blaming tools for their own mistakes. An ORM is a tool and it will mess up your application when used improperly. However this is not the fault of the ORM it's the fault of the developer using the wrong tool for the job. As a dev you should know when an ORM is producting problematic code and write your own SQL in those cases. However this does not mean you have to write every single query in your application by hand.
@@Malekthegreat Agreed, knowing the rough edges/limitations of your tools is important, since it gives you a clear indication when not to use them. And if someone is not up for learning yet another new ORM DSL/API and wants to write SQL by hand, there are still tools like Rezoom.SQL (F#) which effectively give you statically typed SQL based on your DB schema.
"Dual schema dangers" is for me the second most important reason to use django for my applications. I write me schema once and I'm done (all the "batteries included" is reason no1). Migrations are also basically provided for free. With django you don't rly thing about having a database attached, you're just working with your objects.
"Don't focus on the tools, learn the skill of programming" One of the best advices I got from a senior mentor of mine, I think this sums it up really well.
like everywhere: tools can be good and helpful. but to know if its good and how to benefit the most you have to understand what the tool is doing, therefore you need to understand what you would have done by hand. so your mentor is absolutely right: understand the fundamental concept so you can understand and judge tools and code.
That's why I am always rooting for Dapper, which just lets me write an SQL query, get exactly what I need and map it however I want - not forcing me to make 100% the same class. Well, you can kinda argue that Dapper is still an ORM (some call it mini ORM) - but I really appreciate that I can just use SQL instead of learning the API of another library someone decided is cool enough for everyone else to dig into. I still have to write SQL queries anyway when investigating/troubleshooting/experimenting with the schema declaration, to later 'translate' into the library's 'language'. Oh, and what I also hate so much - when ORM decides to cache which tables are often joined and then add it to every other query I need. Troubleshooting these monsters to find out that this bugger decided I need another inner join which actually removes the data I need is torture.
Dapper is great 😊 I like its minimalism and straight forward approach.. EF Core is good, but I don't recomment to use it if you don't have strict understanding why you choosing it
As a Rails developer I heavily rely on ORM. I actually had to work on a time series oriented DB and boy, I had some of the problems he mentioned, specifically writing queries which rely on window function. When you know SQL a bit you tend to think how you would write the query and then back port it to ORM jargon, which is usually more complicated unless your query has to join and filter tables dynamically given different parameters. Creating reports is where ORMs really suck hard on. If you have to manually select fields, manually ask for distinct results, manually map native functions (no ORM does that for you), think about inner join or left outer join, understand when lazy load nested models helps you not to load the whole db vs when actually eager load them to avoid hundreds of small queries. Last but not least, query debugging... you need to know SQL.
Right, but i think you'd agree once you master SQL and ActiveRecord, then it's much nicer to use ActiveRecord than not use it in an App. Just knowing sql is good, but i would hate my life if i had to write raw sql all the time. Not to mention, imagine having to manage preloads yourself instead of having AR doing it, i mean you'd just have to use some kind of library there hand rolled or not, and you'd have to learn some kind of convention regardless. As for functions and windows, you simply just write them as raw sql, so it's not any more difficult to use the orm.
ActiveRecord is awesome. But a lot of devs fall into trap that I must do everything with ORM. Another bunch is I have to do it with Arel if I can't do it with AR. A lot of things can be done with plain SQL subqueries - `where("#{table.name}.id in (ids_sql)", binds)`, that's usable as normal scope. DB views are, work just like tables. Reports, exports or anything that requires complex SQL, write it in SQL, slap it in `find_by_sql`. And in the end there is always `ActiveRecord::Base.connection`. ORM is just a tool, must learn when to move to different approach.
It always shocks me when I realize some of the shortcuts people take with learning software development. It never occured to me you would use ORMs before you've built applications and grown a healthy distaste for raw doggin SQL.
Shortcut is a shortcut for a reason. It is faster and easier to build applications that way. Later you can always return and study the details of how you did what. You don't always have to start from the ground up. Some people are not theoreticists, but ENGINEERS. Engineers like to build functional stuff from the very first day. Their learnining looks like Practice -> Theory. More than that, I would argue that Engineers who start from the practical stuff always learn better and become better developers over shorter period of time, than those who started from full 0 and went up step by step. That's why I was able to become job ready in 4 months(and found it in 1 more month) but for other people it takes YEARS to do the same. The difference is in approaches to learning.
This is how it should go indeed IMHO. OTOH I've also been guilty of recommending an ORM to beginners sometimes because they just won't (don't want to) learn SQL properly, they just want to get their hobby projects done quickly, and ORMs rarely get in the way for those simple (typically little more than basic CRUD) cases (EDIT: and will always emit better SQL than the beginner would by hand). EDIT2: Also ugh, what was mentioned in the video... the noob in question was formatting SQL by hand using user input and obviously no sanitization or escaping.
Distaste of raw SQL? I would love, if people grow a distaste of ORMs like Hibernate, since many do not seem to understand what's happening behind it. (prime example: Lazy Loading)
Django ORM saves a lot of time. It not only hide the complexity of SQL joins but also provides signals to run something after the record is updated or created. It's a very productive tool and still opened for raw SQL requests.
I... have actually built parts of an ORM in Java. To handle the simple case of turning a row into an object. It only handles simple cases though. You can annotate a class with JPA annotations, and tell the api what class you want to turn the rows into, and you get a List back. It's really easy to use, and works quite well, but it's also very specific to the current project, is very small and limited, so it's not like it's some sort of Hibernate alternative. It was made so that raw-dogging SQL could be done more easily, by not having you translate row -> constructor yourself.
What's wrong with just using sql? It's only disadvantage is if you move from mysql to postgress for example. There might be difference in syntax. Changing database does not happen all the time though. I prefer writing sql over using orm.
ORMs ensure your code maps directly to the database. When the ORM doesn't work is when you're writing custom reports with complex queries. So yes, you need to learn SQL even if you use an ORM for basic CRUD functionality. A good ORM will also help you find sloppy database design so you can clean that up. Yeah, you don't use an ORM to do complex selects. The only thing my ORM supports is AND and that's on purpose. If you need so much as an OR clause, you're writing SQL and then returning the result set as custom strong-typed objects. People like to find a hammer and then think they can ignore the rest of the toolbox.
All the listed reasons make using SQLDelight great. You write compile-time checked SQL where each SQL query is turned into parameterised function on a Kotlin class. It checks your queries by parsing your migrations to understand your Schema. Very handy. Also supports custom adapters so you can use custom types as args or return values. Disclaimer: It doesn’t understand all of Postgres syntax yet.
This guy claims to have experience with ORMs like SQLAlchemy, but statements like "ORMs don't help manage data migration at all" are just plain wrong. I used alembic at multiple jobs and never had issues. Would much rather be doing this than hand rolling migrations with SQL.
I bet most people that don't like ORMs don't even hand roll their own migration system and don't do proper version controlled migrations in the first place
I totally agree that Stored Procedures requires one to draw the line carefully. A stored procedure should be delivering data objects that are as agnostic to business logic, as possible. They give you a logic toolset that isn't available through vanilla SQL and run natively as a first class citizen in the DB engine, but need to be designed with the idea that they can have the opportunity to be shared, by other applications or functions, even if they never actually are. This helps to keep business logic out of the equation and leaves the SP to do what it is good at.
Data and business logic are very tightly intertwined, they are two parts of the same Business thing. The fact that the data is stored on a separate machine and accessed over the network, and that queries are written in the special SQL language, don't change that. SQL code, both DML and DDL, is application code / business logic. This is nonsensical and completely arbitrary distinction.
I think we'd disagree. Stored procedures only exist for business logic. If you are not using stored procedures for the business logic, there really is no reason to use stored procedures.
@@sevilnatas more that if the business logic is not in your SPs, you probably should not be using SPs in the first place. Views and other dbms tools make for better data management. I also assume that there are at least a few bad actors smarter than me and want inappropriate access to my data at least as bad as I want them not to. On the reverse side, I also assume that I am not smart enough to be able to stop stupid people from doing stupid things, so I do what I can to minimize damage that can be blamed for.
The thing with the 14 sql joins was he was trying to use row oriented transactional db to make reports that should’ve been built with at least columns oriented db or dimensional modeling.
i still believe orms make sense for dbs like mongo because you arent meant to have foreign keys or whatever in them, but you would do a deeply nested document, which would create highly composed structures/fill in fields of highly composed structures in your code. for context, i believe r in orm means relating db and code, not relations between db entities
I worked a job that had business logic in the sprocs. They had it scale well because each customer got its own database and they had legions of servers. It was pretty slick being able to solve production problems by just calling sprocs and eventually having some standardized scripts/wiki blurbs. We didn't need to write custom apps to apply the fixes. We could just use the already system tested sprocs.
@@ThePrimeTimeagen I love the audience engagement here! In your defense, I think a lot of JS ORMs suck, but it's mostly because the language itself doesn't support the kinds of operations that make working with an ORM easy. EF Core is so convenient to work with thanks to strict typing, great interfaces like IQueryable, and Fluent APIs! Joins amount to a single .Include() call, and projections amount to a single .Select() call, and since the language supports dynamic types, you can cheat by not having to define a concrete class for your projections. Sure, you should still learn SQL for advanced things like window functions, partitioning etc., But if 99% of your logic is simple SELECT FROM WHERE GROUP BY ORDER BY, EF Core is really fast to develop in.
The difference between the poor ORM options you have in the JS ecosystem (Prisma, etc.) and EF Core is very much analogous to the difference between a 15 year-old pentium Dell laptop and an M1 Macbook. You can't have only worked with garbage laptops and then from that experience draw the conclusion that "laptops" are shit.
I feel like he would like Dapper better. It has less abstractions. EF is heavily framework and someone needs to understand the framework a lot in order to use it properly.
SQL Alchemy has Alembic which diffs your schema as you change it and provides a migrate, and restore function for each step, so you can apply and walk back multiple changes easily. Its a great tool. It also lets you choose the level of ORM you use there is a nice basic version which lets you do vanilla CRUD, and then a girthy layer which adds all the ORM shenanigans.
This is why drizzle I believe is so popular. It allows the Dev to write type safe SQL while being able to control the underlying query (if you need to).
it's popular because it's javascript. And the only main option before then was TypeORM which was a beast but slow and just behind the times. Drizzle really isn't anything new or special tbh. Many languages have thin type safe SQL builders
Primary issue with store procedures is multi point deployment. You need to deploy your app, and then all other procedures. And deployment of procedures was a mess in any db system I have used. Essentialy I have not seen a database where you can deploy your procedures as versioned artifact. I use them sporadically - as form of optimization, and for bookeping jobs like partition creation or reporting etc.
I have been writing SQL for 30 years! Every few years a new ORM comes out and companies want to adopt it. Luckily we always manage to talk them out of it. I am writing a customer application using a simple 3 tiered architecture, Angular, C#, and SQL. That is a killer combination. I use stored procedures for complex queries and have built highly optimized databases with just the right indexes. The performance is insane! Data comes up on my screen like the shutter of a camera! I move around bouncing from screen to screen, retrieving data and there's never even a millisecond of lag! "If it ain't broke, don't fix it." I will stick with SQL unless or until something REALLY better comes along.
I really like the mapping ability ORMs give you, but I don't like all the clutter it introduces for the multi database support part. I wanted to have something in between like Dapper so I could map my entities and query results in code so they are typed once I receive the query result. I don't like having to manually type check everything for every project.
The "sql builders let you switch out database" is such an unhinged take. There is so much DB specific functionality that you're either not using (i.e. you're doing it wrong), or if you are you _can't_ actually "just" switch out your database. It's like being cloud provider agnostic. Pure pipe dream, nobody "just" switches cloud providers. That doesn't mean that you'll never want to switch your DB, or never want to switch cloud providers - I'm saying unless what you're doing is so incredibly trivial that it's almost nonsensical to worry about it you'll have to change your code when you do _anyway_.
For standard MVC applications with lots of relations between tables, using an ORM makes development much faster, makes code easier to read, and makes mistakes less likely. It can also make refactoring a lot easier.
The user doesn't care about how easy it is for you as a dev to do your job. ORM's might help you not shoot yourself in the foot, but you can also just learn SQL and write your own barebone wrappers around complex queries. Honestly feels like a skill issue. That isn't to mention the price of having an entire layer of abstraction adding latency between you and the database, which is already hyper-optimized to query data efficiently.
@@laztheripper You're not really making any points with what you've said. "The user doesn't care about how easy it is for you" for your argument is at best a moot point, and at worst implies it is best to use an ORM, if the user doesn't care about the tech, why not use the one that makes my life easier. "skill issue", yes, and? again, if what matters is the end product, does it matter if it is made by big brain dev writing everything by hand, or not big brain dev using a tool that lets them also do the job as well. "the price of having an entire layer of abstraction" the whole concept of a database is an abstraction already, the latency for an ORM's internals to generate the query is in practice negligible for most cases, so
@@laztheripperI disagree with you with your first point, but agree with your second. The first point if a bug happens, the ability for the current developer to quickly fix anything to improve the user experience is always going to be priority. Second point I agree with, along with everything primeagen says as a newer viewer to his channel. The only problem, is for the most part not a skill problem but a discipline issue for some devs. Look at Regex. Its really, really useful if your job requires working with a lot of string data types, but barely any devs want to learn due to how daunting it seems. Its the same with SQL. When I was newer with learning and coding projects, I just found certain things to learn more daunting when comparing other tools / language’s that I found learning easier. Thats what I did to SQL the first year learning to be a dev, and other time after the first year I learned vanilla SQL is just the way.
I think the issue with ORMs is that they are a vertical abstraction rather than a horizontal abstraction. What I mean by that is vertical abstractions try to hide things away from you and have implicit magical behaviors that are hard to debug unless you know exactly everything about their way of doing things. A horizontal abstraction is hiding things but in an explicit way. You are effectively compressing your code but you can open the hood at any time to inspect it. Examples: - Vertical Abstraction : Django Django has this whole ecosystem behind it where it abstract everything away and you have opaque control flow and implicit behaviors. - Horizontal Abstraction : SQLc SQLc generates SQL type safe interfaces for you that you can inspect and if it isn't to your liking you can change the configuration so that it generates it properly. If the SQLc doesn't solve what you want or it is too many docs for you to read, you can still generate the stuff you need then create the rest on your own manually. The problem isn't Vertical Abstractions, but too many of them. Like without Vertical Abstractions, we would be using Assembly Code still. They are useful but they always reduce the power you have for convienience. Prefer Horizontal abstractions until you really need to Vertically Abstract.
The only thing we use ORMs for is not having to write the migrations manually. Most ORMs allow you to basically use them as a query builder and that's what we do. For anything performance critical we use stored procedures or other DB specific functionality.
Do you actually find that useful? Easy migrations are still easy to write with just SQL (or a sql builder for slightly more convenient syntax). If it's _not_ an easy migration (let's say anything more complicated than adding/removing a table/column), I really wouldn't trust an automatic migration anyway.
@@hannessteffenhagen61 I know that it’s easy to write basic migrations in SQL. Basically every migration tool I’ve used is very reliable and produces acceptable SQL. I never understood why I should bother writing versioned migrations manually. And in case something goes wrong you always have the option to fine tune it manually.
@@roccociccone597 I just don't see the point in automatising easy stuff I need to do _maybe_ once per week and that generally takes minutes to do that'll fail the second I try to do something nontrivial. Automigrations are OK for local development if you're iterating on something, but why use them for production changes?
I just want to add that ORMs also can make your database more stupid by simply not letting you access very useful database features. I saw people doing crazy DB round-trips instead of just creating and using a view or a stored procedure. If your colleague is also a backend "CRUD purist" then you will need to write your joins on frontend...
1. ORMs, like most good abstractions, make 80% of the tedious work trivial, while still making the hard parts possible 2. You can ALWAYS go back to writing raw SQL, even with an ORM, but it's a lot harder to go the other way. 3. The chance that you actually need to do an optimization the ORM can't easily do (for example, his foreign key problem is easily solved with lazy annotations) is tiny for many categories of project 4. Writing raw SQL turns into string manipulation. SQL is also just objectively a horrible language, because it lacks any of the sane sugars, like functions, variables, etc. 5. At the absolute least, please use a Query Builder. They'll give you some level of type safety, syntax highlighting, and conditional building 6. Know SQL. Hell, don't use any ORM feature you couldn't (roughly) do yourself in SQL
You get it. Using native SQL in code is a highway to writing stored procedures which are inherently violations of the separation of concerns - a valid architectural decision to make in favor of performance. The trouble is that 90% of devs aren't great architects and lack the understanding of their flavor of SQL to create a competent bindings to stored procedures - why reveal that level of SQL and make your application more difficult to maintain in order to provide a boost of performance? In the same vein, why not use an ORM when all you're going to do with your native SQL is bind it to data models in your application code? ORMs were an emergent pattern that we realized we can design to make better. Every "hard problem" that an ORM ends up making is a violation of the SoC anyways. The database is only meant for data, let your API drive your database - do not consider your database anything more than a driver to provision data for your API/DAL. It makes me laugh when developers say "I don't use an ORM," because that means you're making one yourself to provision your data to make your application testable. While you're writing your ORM, I'll be working on actually solving business problems, which is what we're paid to do
@@psyoptic since we moved from them to use slightly better languages to develop our applications with, like Java, C#, Ruby, Go etc... since then SQL functions stopped existing for most of us :))
There are many benefits with using ORMs. Especially in an OO/MVC application. For example: - All the objects get hydrated automatically. You get objects and collections of objects directly that you can work with in in your app. - It is much easier to read and understand. For example Model.where('col', '=', 1).with(relation).get() on your Model instead of dealing with joins. Long and complex SQL queries are usually a lot harder to understand. - You can abstract away what kind of database you are using. - You get a lot of additional tools for migrations, seeding, streams etc. - If you are not good at SQL you will probably write better queries with an ORM/query builder.
> - All the objects get hydrated automatically. You get objects and collections of objects directly that you can work with in in your app. Automagic, and then you wonder where the performance has gone. > - It is much easier to read and understand. For example Model.where('col', '=', 1).with(relation).get() or Model instead of dealing with joins. Long and complex SQL queries are usually a lot harder to understand. You can use SQL builders. > - You can abstract away what kind of database you are using. You can do that by just using ANSI SQL, lol. > - You get a lot of additional tools for migrations, seeding, streams etc. I think that's the only reason why I would use migrations. > - If you are not good at SQL you will probably write better queries with an ORM/query builder. then get good in it ffs. It's not that hard. It's easier than Rust, tbh. It's like never learning how to use a fork.
a) that may come at a cost, like cardinal queries because of lazy loaded joined classed or even worse, load the entire database cause you are querying the main domain model. b) debugging a query builder ( usual case scenario, you build a query depending on parameters being passed ) still requires you to know how to read complex SQL queries. This is the only scenario in which patching a sql query string might be more confusing than using an OOP query builder and the reason why Rails developers learn Arel c) you'll never change the database. Never happened once in my 20 yrs long career. When it happens it's because you've been tasked to rewrite the whole thing. Anyway no ORM abstracts away native functions, only SQL dialects. d) doesn't mean you have to use the whole package e) ORMs do a lot of work but still requires you to know what you are doing so there's not much you can do without knowing SQL, easy stuff.
- ORM can also provide caching speading up your application significantly - They provide attribute converters - They help you out with different Indexing strategies - It can help you with cascading operations and so on... - It can significantly help you to focus on rather object oriented models than tabular format of your data (this is especially good if you are trying to avoid anemic objects as your applications first class citizens) There are so meany ways that ORM's can save you time if you have mastered them...
@@peppybocan > Hydration does not give you a significant performance penalty if you are not doing some crazy things. Productivity is typically much more important than small optimisations. > A good ORM is often very similar to an SQL builder + some extra functionality. > No, you still need to use things that are specific to your database engine sometimes. > Why wouldn't you use migrations? > Sure, it's not that hard and it is of course good to know SQL even if you are using an ORM or an SQL builder. However, the ORM lets you focus on other things, that might be a lot more valuable, if you want
@@ClaudioBrogliato a) Sure, sometimes you end up doing stupid things if you don't know what is happening under the hood, but in most cases it's pretty easy to fix. You can for example eager load what you want. b) Yes, knowing underlaying technologies like SQL is usually good. But you should probably focus on the areas where you deliver the most value. c) Yes, that is not common. But you don't need to worry about what SQL dialect you are using at all. You can work with different databases in different projects for example. d) No, but that makes it a lot easier. And pulling in a whole ORM just to get for example migrations might not be the best solution. e) Same as b.
its good enough, but by default I prefer to use Dapper.. because of simplicity and straight forward approach, as well as performance... And also using EF only in case we clearly see some strong benefits of using it..
Even if you use an ORM for 80% of your queries, nothing is preventing you, the developer, from raw dogging SQL for the more complex queries when you need to
EF with Linq is fantastic. Especially now they are doing more performance optimisations. Also re the point made at 9:00 Linqpad ... so just write it in linq from the start. Yes its declarative, yes its a query language ... but you get a lot of benefits simply not available to SQL. Also if you realllllly need to you can always execute sql statements and bind the results to models too. Plus migrations, automatically escaped inputs and a whole heap of other shit like query optimisations, deferred execution etc. SQL is great but so are good ORMs.
Indeed, EF is great, the problem are the programmers. I always recommend C# juniors to check what query EF is generating before pushing code, so they can learn what Linq and EF do internally.
11:34 good ORMs manage that for you. When using a code-first ORM you only ever worry about your database entities. You dont maintain anything else and when you change the models you generate migrations which you can run against databases to update them. Ergo ... its not really redundancy you are going to want models for reading your data out of the database anyway ... so realistically you have to write if anything less code.
@@victorhf9804 Dapper's great, it's what I use at my current company. For medium to large scale projects To be honest I would rather we had been using EF here, simply because of some of the legacy problems we have which would not exist if we had used EF. Nothing to do with Dapper per-say just historic practices.
at 5:46 the same could be said for manual memory management vs GC, like people seems to spend so much time and efforts fighting the GC of their language, pulling up insane Data structures and algorithm, when all of your problem could have been solved, with malloc and free lol. I think people just fking love complexity.
Good ORMs implement "Identity map" and "Unit of work" patterns. They can handle transactions, they can lazy-load joined tables. They always provide query builders. Good ORMs optimize queries (Unit of work). You can do 95% of work using classes that push readability and productivity on another level and 5% using query builders. If you are fighting your ORM then you've made a bad schema...
@@ccgarciab The confusing example: Doctrine ORM in combination with Symfony framework. PHP. BTW. High load? Never use ORM. Hire a skilled DBA that will manually tune your queries
in spring boot ( i think the module is spring data jpa) you get these built-in repositories where you just name interface methods these long ass sentences. Like in your UserRepository you might make a method like "findByOrganizationAndSignupDateGreaterThan(Long organizationId, Date joinDate)". And you don't have to implement the method, it just works. It's pretty cool as java things go.
I often have to use queries with multiple joins, unions, group by, etc, doing it with an orm is a nightmare, the sql produced is always crazily inefficient to the point that not even a junior would come with that. It's like any orm was made with just todos tutorials in mind. Considering that on aws you pay for the io produced in the db, it makes anything prohibitive, not just slow and inefficient. Prisma is criminal in that aspect.
The author seems to be poisoned by hibernate and the shitty documentation around it. Hibernate is a dog shit ORM, because it does a ton of unexpected shit, that noone should really care about. However, it does generate SQL migrations, which means no ORM specific DDL bullshit. To not off yourself you have to have a relatively complex local setup, where you drop and create DB and run migrations and seed the DB on server startup, thus ensuring schema is in sync (pairing your ORM with something like Flyway). Also, ORM are not replacement for writing or knowing SQL, they are useful for one thing and should be used for this thing only: mapping the query results. In any relatively complex application you will want to drink bleach if you write the queries and mappers by hand. Also also, if you have to wrestle your ORM to give you the query you want, it's probably a shitty ORM that tries to hide SQL. Try TypeORM if you are in JS world.
Couldn't agree more. It's annoying to see how many people think ORMs are for people who don't know SQL. That was never the point of ORMs. The point is literally Object Relational Mapping. The migration system is a bonus
Hibernate is the best documented ORM in the world with a detailed documentation and countless books written about it. If it is doing something unexpected, you didn't read the manual before using it. 9 out of 10 problems when using Hinernate come from developers copy pasting some examples mindlessly while not even trying to understand what are they really using and why. If you don't need multiple caches and essentially a DB on top of DB that Hibernate is, use one of countless simple object mappers
@@NJ-wb1cz "If you don't need multiple caches and essentially a DB on top of DB that Hibernate is" - nobody needs that. The only reason anyone even comes to use Hibernate is because they mindlessly copy paste from any java blog post ever.
@@sarabwt nah, most moderately complicated server side processing needs that. You update an entity and want to see the changes in your other parts/stages/threads reflected synchronously without updating from db. Hibernate makes that easy without convoluted messaging and manual updates constantly going on between all parts and without slowdowns. But you still have understand what are you actually doing, and have some semblance of idea of what an enormous task hibernate is actually doing behind the simple calls
If you raw dog sql all the time, then you'll find yourself repeating a lot of queries, repeating mapping logic to map the sql results to domain models, then you'll ask yourself wtf am i not just using an ORM? Are there tradeoffs? Yes. Any non-trivial queries (which from my experience isn't common) you need to raw dog it, but for most queries they're trivial crud operations. You get type safety, migrations, schema version control, etc. I used to raw dog sql, back in the early 2000s when using PHP. ORMs save a lot of time. Like everything in engineering, there are trade offs. I'll take benefits of ORM 99% of the time.
Depends on the type of ORM that you use, language, framework, and who implemented it Most devs don't even know the different between Active Record vs Data Mapper Java Hybernate is weird as heck coming from Laravel(PHP) Eloquent where the methods for the query are basically named like the equivalent from SQL (mostly because it's written to work over the query builder). I've seen some projects on the god language that is JAVA where people broke the ORM because they wanted to add a fancy abstraction that they didn't even know how to explain to the team
We're currently using Larvavel with 100% Eloquent for the back end with a custom API and Vue for the front end. We are using Telescope to test. As long as we are cognizant of the pitfalls I don't see many drawbacks to taking this approach. As a Laravel dev yourself do you see any issues or have any advice?
Rawdog SQL when making quick prototypes on my own that probably won't see the end of the month. ORM when making anything that needs to go into production and will get touched by others. I know how to rawdog some SQL stuff... But can't expect my co-workers to be competent enough for it so I just want to lower the opportunities for them to screw up (which will cost *me* time and effort).
This might be the only time I disagree with you prime. The number one benefit for an ORM is speed of dev. You don't spend precious time thinking about what you want to fetch/write. For complex queries you use the query builder. It's like saying why use tailwind learn css. Of course learn SQL, but why not use an ORM for up 90% of database related work when it can do the heavylift and optimize when necessary ? Seems unwarranted.
Agreed. Even though most of our codebase deals with complex objects that have lots of attributes, our production database spends far more time performing simple `SELECT * FROM ... WHERE ...` queries on tables with just a couple of columns but lots of rows. Those queries are perfectly well-suited for ORMs.
@@yyny0 I agree. I don't see myself passing out on a data mapper, a query builder, a caching layer, a persistence/update event emitter, encapsulation, and this has nothing to do with my ability to correctly use SQL and an SGBD ... On most complex systems I've seen the view does not relate to the schema. Fortunately, we have other patterns to alleviate this. And I'd always argue to start cheap. Why use a hot tablespace or table partitioning if a simple cache layer fixes this with a small to no latency whether it is a full fledged reverse proxy or nextjs function ? Our first job is to keep a good time/cost to RoI balance.
What an ORM does for you is help attach functionality to objects (it's called Object Oriented Programming). SQLAlchemy lets you build SQL expressions (SQL Core) and you can mix and match this with the ORM style if needed.
So I define a model once. This is a perfect representation of the db table. I can then have automatically generated CRUD views, automatic create/edit forms, automatic filters, automatic migrations, all with a few lines of code. All the logic is in one language, I never touch the DB. And somehow this is bad?!
Nobody noticed that the article is from 2014? ORMs have come a long way since then... Used to hate working with EF but now I find solace in the fact that I can project queries to objects and limit the number of joins an optimize queries that result from LINQ but also do SQL when I really need to... This combined with precompiled queries makes it close to native performance with the added confort of using type safety and migrations and LINQ
ORM is definitely a good example of the type of engineering where you think the solution to a problem is adding another layer to the stack. Not fun to debug, too much magic going on
ORMs save you a ton of boiler plate for simple/common operations. In ever ORM that I've used, you can use raw sql if you need to, while using the ORM most of the time.
@@phazechange3345 that's just a skill issue though. This entire thread reads : I don't know the orm properly, therefore it's bad. I've never had these issues with the django orm, and I'm cranking out one CMS website every sprint/other sprint.
Most projects have tremendous benefit from ORM. It’s only when you scale to a significant size that you get into trouble, which is a minority of the projects out there.
A lot of things are not true with dynamic language based ORM like prisma or django ORM. The model(application code) can automatically translate into DDL, also when the model codes change, there's tool to write migration code automatically and keeps the history of migrations.
Also may I add that, ORM of static language like Java is more difficult to write intuitive query than dynamic languages like python or javascript due to language constraints
Jesus Christ imagine debugging and ensuring that your dynamic language reflects your database schema correctly and then also generates dynamic queries such that your database will not die from a few concurrent queries.
Real talk here; ORM's are never meant to skip learning SQL and how RDBMS work. They are just there to save you from writing and maintaining boilerplate CRUD operation SQL's in a forever changing DB DDL. We used Hibernate in my previous company, we mainly used it for basic CRUD operations, for anything complex we either wrote 1 - HQL, 2- raw SQL or 3 - A view. I never had to maintain any POJO's CRUD SQL. We never had any performance issue we wouldn't have with raw SQL. We were able to use hibernate to report POJO vs DDL mismatch (We were maintaining multi-tenant DB's each having their own). We had best of both worlds, still miss using Hibernate. - A Regular IBatis Hater
Cause, as we all know, your app is changing database backends all the time, and your basic crud app that runs basic ISO only queries definitely needs vendor specific optimizations/syntax that don't exist. This is a superb tradeoff to make for a framework that's going to random ass generate a query that'll take your whole production down a couple times a year.
@@phazechange3345 I’ve never had an ORM generate a query that took the “…whole production down a couple times a year”. You could make a better argument saying that it generates inefficient queries. ORMs have their place especially with applications that need to work -at the same time- with two, or more, different database backends. Or applications that allow for the same functionality with option of different database backends. Migrating a large amount data from say, SQLServer to Oracle is no easy/quick task; just to accommodate an application.
I use dictionaries of bind variables to return dataframes for joins on other datasets. I also use if statements to dynamically change the where clause based on user selections. Hope I don’t regret this in the future but it’s working fine for my current needs.
The only ORM I actually liked is the the one from spring data. It provides de basics only and it allows you to write custom sql in the most ergonomic way
I'm currently using entgo for a project and the ORM functionality is honestly the least interesting to me. Basically, I can define my schema and I get - types for my go codebase - query builders for db access - a complete graphql API - protobuf types for other services that use the same model - hooks that allow me to implement transparent application side en-/decryption of sensitive fields on the data access level - a rule engine that allows me to implement efficient, role based authorization on the data access level For a codebase that's being reused in multiple downstream projects that's just immensely useful
Gotta say, after dealing with JS ORMs and their bs, (looking at you specifically Prisma) I fell in love with SQLC in Go. Write all my SQL queries and SQLC creates the Go models needed to run those queries but otherwise isn't really taking over my queries and man are things buttery smooth compared to what i'm used to on the js side.
I like the input sanitation / single place definitions for tables using something like SQLModel in python, I don't really use it for more than input sanitation, validation, and to make those objects self documenting, but you can still query a database however you want, knowing the set you have in there is nice and clean.
I really like all that. I always have this weird feeling when using ORM or other cool badass astraction that something is wrong. Their website is great, the marketing is awesome, the examples are beautiful, everyone is drinking the cool-aid. But still, something feels wrong. I really your videos, they really resonate with me and my feeling with programming. Cheers!
Django or Tortoise builder pattern into lazy evaluating querysets are awesome generally in python all orms, so peewee and pony, even alchemy, are really nice tools
In my experience ORMs can be fine and actually pretty helpful if the Dev knows SQL and they can significantly reduce the time to build the SQL you need with proper sanitisation, etc. Agree with the point that Devs should learn SQL first. I'd never get rid of using ORMs to build SQL, though, that would be a step back for me.
I'm on a team of using both. ORM / Active Records for simple things (but only if used framework gives some additional bonuses that really make work easier, not if you need to write that on top of some JDBC or other lower-level access). I mean CRUD-like things with a slight touch of relations or forms where you save few rows into one or two tables at most. Raw SQL for anything elseo, especially more complex like data views, tables, searches with relations, reports. Even more complex reports - I use ETL / Python scripts that generate them from the data received by SQL and save it/cache regularly, don't generate in user session making user to wait.
I partially agree with your statement but in many cases companies have to build entire applications from scratch (or even migrate from an existing XLSX) with a small team of developers in less than a year. For those cases usually you have two options, use an already existing ORM which implements the Repository pattern, (or an SQLBuilder), or implement your own. It's all about time and productivity
When you are still developing your product and things are changing fast, an ORM is great - mainly for the auto-migrations and type safety that propagates throughout your IDE as things change. It's great for teams moving quickly. As your product matures and scales and you need to improve performance, start writing SQL.
11:21 domain duplication happens everywhere. Any client calling a server will have a duplicated domain definition. Any actor using a protocol will have a duplicated domain definition. This is only made worse when the clients/actors/servers are written in different languages.
ORMs are one of the worst ideas in the history of software engineering. Just learn SQL and write mappers from the database to your types. Use the repository pattern. Let the database be structured in a way that makes sense (normalized or not, joins or not - these considerations are orthogonal to your object model) and let the repositories turn the data into your objects. This applies to any language and any database. Put a repository interface in front of Mongo, Redis, Cassandra, whatever. ORMs are a massive waste of developer time and energy, and usually compute as well.
I just want to be there in the room when an engineer is presenting at a design review and someone asks what ORM is going to be used and they respond: "WE ARE GOING TO RAW DOG SQL" And the delicate ears melt 😅 And the engineer is confused cause they have been listening to the Primeagen for so long they think that's the way people talk.
one thing I like about ORM (maymbe more specific to hibernate) is the thing, that it wraps an API Request into a full transaction. other then that, in a recent project with prisma, we more or less used it for the migration and types. Most of the time we used queryRaw
The argument about too many joins being generated, in my experience you can manipulate that, at least you can when you use hibernate ORM, by setting fetch type Lazy or Eager, if you want your data to be fetched lazily (in a separate query) or eagerly ( hibernate generates join statement). However, my approach is to use both, ORM for simple CRUD operations on entities and SQL for more complex data fetching/manipulating.
It's funny how you all didn't understand the 600 columns. He's fetching ONE table which does 14 auto joins and that totals to 600 columns. He only wanted 2 joins, but that's just a configuration error of the ORM / Query.
My take is that ORM are the natural continuity of defining your entities (think DDD) in your code for business logic and wanting to have a declarative db schema (with automated migration). Since most of the time it is very similar, might as well prevent repetition. Then it's only natural to try to automate the bridge. But it leads to all this mess. As a Pythonista today, I use Pydantic to define the data model of my domain so I can get JSON schemas, which I can turn into a declarative db schema, but I write my own queries to get the data and let pydantic parse it.
For my simple app, I'm on the Prisma+Kysely train. Prisma for schema definition and buttery-smooth migration, Kysely for the type-safety. Now for my chonky boy (largest table is 14 billion rows), I made my own SQL builder for my Clickhouse and MySQL dbs that does things in the way I need them to. (I raw dog the schema.) This is primarily because I have microservices that must output the exact same data based on a given set of inputs, and having a centralized sql builder that I made that I know how it works guarantees this. Secondarily, it's because I have to do some really funky stuff with window functions and joins that I wouldn't be able to remember jack about how I to do any of it.
I remember I just had queries as SQL files one project, and then had a bash script the would pop em all into map[string]string for each package, when go generate was run, gave me the beautiful looking query which was also nice to use, and without performance penalty of loading a file
The big deal of SQL builders is that you get almost 100% SQL syntax check at compile time which avoids the need to run tests to confirm the SQL is correct and works right on the IDE or editor and not having to recompile the code and re-run every time the SQL is changed. That's the biggest deal.
I can reuse query parts and can refactor a column in hundreds of queries at once without any issues. No runtime errors. ORMs have issues, but usually the pros outweigh the cons
I've been using an ORM for my last project. After things were ready for prod, the biggest lesson I've learnt was: I need to learn SQL. @0:02 Everything's said and done. To every junior reading this: Learn SQL- Period.
While I agree with what was said in the video, my experience with (and without) ORMs don't fall in line with your conclusion. I was part of a team working on multiple projects at once. One of them was using raw SQL and another was using an ORM. The biggest problem with the non-ORM project was the half-baked classes and their joins. When calling a function you never knew which fields were populated and which weren't. This became especially complex when we had entities spanning 20-some table. (Yes, probably a document-based database would've been better but we all know how difficult it could be to change that in an existing project). An unpopulated array for an 1-to-many join would sometimes be null, sometimes undefined and sometimes even an empty array. Properties could suddenly become strings instead of numbers due to the language being dynamically typed. Worst of all, parsing and converting the result of a multi-table query into an instance of a class was always problematic. It was incredibly easy to overlook something. Overall, small queries were alright, but large queries with more than 5 joins were adding tons of (programmer) overhead. Much of the code was just filled with property assignations and conversions (from the query result to some nebulous class) The advantage was performance. Optimizations were quite easy to do. Migrations were not really a problem (you could even use an external plugin to manage them), but even without, easy to implement. The biggest problem with the ORM project was... well, basically what you said in the video. But the advantage was standardization. It became incredibly easy to make complex queries with multiple joins. When something was an instance of a specific class, you would already know how to navigate its properties. While the performance was not great (probably because of the creation of each entity), it was always quite simple to make an optimization to the query that the ORM was using (you always knew what query the ORM was executing) The problem might be the language here. It might be the database. But, there are times when the best decision wasn't made at the beginning of the project and you are stuck using them. Combine that with having a team of people that are prone to simple mistakes and ... ORMs suddenly don't look like a bad idea
The problem with the stored procedures is that hard to maintain. For example you have 1000 lines of stored procedure that is calling several other ones. Would you prefer to navigate manually reading each line because you can not search (squirrel), and coping each procedure in normal text editor so you can apply some search rather than using some normal IDE to navigate in the code. True, there might be some limitations but try to migrate the DB and start fixing DB specific issues, adding the above and it becomes nightmare.
It's exhausting having hot takes coming from a random blog, in the chat, and in the picture in picture simultaneously. Then you hear, "new tweets this morning" and I can feel myself becoming a grandpadev by the minute.
I have not worked on any enterprise projects directly tied into an ORM but man, you can really feel it when engineers *don't* understand SQL. I've seen some projects brought to their knees by early misunderstandings about how the data in a database is constructed. 6 months of work tossed down the drain feels extra bad when you see that the entire program was built with false assumptions around what data does or does not exist. I've had to set expectations with new engineers to learn SQL basics so that they can program without blindly assuming that the data will work in X way. Just learn SQL. Everyone, especially your reporting team (if you have one) will thank you
I was using Borland Delphi. I was coming from a procedural mainframe background using SQL and my co-workers were OO developers coming to using SQL. I identified the SQL/OO interface was going to be a major pain point. 25 years later nothing has changed. SQL is still the best DB for enterprise transactional data, OO hasn't adapted. The ODB has never matured to be a viable enterprise product.
I did DBI and result rows in Perl when I started. Everything has trade-offs but I'll take the ORM ones generally. This OP is writing time-series reports, I'm not. I'm doing basic stuff that is not edge-case or pushing the envelope. The ORM should have an escape hatch. Yes, everyone should learn SQL but for the "forever skill" reason (to me). One thing that I miss in Rails is the dev log shows you the SQL while you work. And there are gems like bullet and loldba which are almost like linters for N+1 and missing indices.
When a table gets wider than 10 columns, it stops being nice and starts being painful. Every extra column above 10 is probably just going to waste anyway.
As a grandpa dev, instead of learning 5 interchangeable languages of Algol-60 family , if you are a Jr. Dev. spend time learning:
1. Databases: esp db internals and query optimization
2. Unix/Linux esp the networking stack and file system stuff
3. Get into the details of at least one cloud platform
4. Learn bash: the number of problems that can be solved by a simple shell script (sed+awk+grep+pipes)
5. The art of writing clearly and concisely.
This is not only good for junior devs. It’s good for any devs who haven’t already learned those things.
Learning the shell and core utils is amazing. It gives you code that can run basically everywhere
Definitely one of the best comments I have read so far on this channel.
Learn the fundamentals first and not the tools because, in the end, everyone can learn a framework or an ORM by using it but if you have more knowledge you will be the one that finds the quirks and the best solutions for any type of problem.
This is just a personal supplement to basic knowledge, but you still need to learn popular development technologies in order to enter the workforce.
This is SOLID advice. 👍
The engagement tactic of mispronouncing SQL to get people to correct you is genuinely funny.
He is not mispronouncing SQL, that's how you should pronounce it
his random pronounciation always kill me
@@siniarskimar This is the only channel where I hear SQL pronounced as "squeal". The official pronunciation from wiktionary and e.g. mysql-devs would be "es-cue-el" since it is an abbreviation. But I also heard and read the pronunciation "sequel", but never "squeal".
@@StingerAJ 🤓
@StingerAJ. That comment was not serious. As a degen Prime fan myself I can spot when another one of us are meming.
Django's ORM has an amazing migration system and schema definition, managing indexes is easy, and you always have the option to use the DB connector and raw dog the SQL query. Flexibility and using different paradigms is the answer. They aren't mutually exclusive
Definitely the best part of the framework.
Sure the best part it's use RAW QUERY!!! WHY USE ORM at first if you need RAW query!!!!
I like the migration tool I hate the query generator, how Django ORM translate subquery is just a mess, if you're using subquery do it in raw, is easier and has better performance.
@@JaimeChereauBecause Django ORM is so flexible in all use-cases. You don't need to write raw sql 99% of the time, but when you do, the django.db.connection API is available.
What is not nice is debugging that django orm. It does lazy loading by default and when you look at the variables they are a mess of underscore functions.
I have spent 2.5 years on a old django application and let me tell you, it is not nice at all. Raw sql with something simple like express or flask is way easier to understand and maintain.
Migrating feature is nice at first but when you have a test db, local db and a production db which all need that migration applied it falls appart quick and becomes tedious. Rather have a sql script written up to quickly apply all the migrations needed.
All we really want from an ORM is type safety.
Yep, I end up using Prisma as my schema tool and Kysely as a query builder
In context of an android-app, Room from Google in combination with sqlite is really good and gives type safety and compile-time syntax checking of sql-statements.
@rychardvale you wouldn't happen to have any more info on that would you? Been using prisma for a month now for the type safety but been missing my knex query building, does this kysely hook into the prisma types?
And testing
Concur. "Micro ORM"s, like Dapper, that automate the Object-ification of query results are where the usefulness of ORMs start and finish.
"I didn't like the ORM library I was using, so I wrote my own ORM"
This was back around 1993 as I recall, and I was brought in to determine why it was taking around 30 seconds per check to print checks. They needed to print thousands of checks per run. Yeah, they had an obvious problem. The only real bottleneck should have been the printing speed of the laser printers themselves, not the data processing.
The first thing I did was to take a look at the database that the information being printed on the checks was coming from. INCREDIBLY, I saw the problem immediately. While the table contained the unique ID of the checks, that field in the table had not been defined as the unique ID and it had no index. I redefined that field in the table, and BAM!, problem instantly fixed.
My experience of not wanting to use an ORM several times:
You still have to map the returned object to a class of some sorts because it is self-documenting in code and you get intellisense.
Then you want to automate some boilerplate code away and abstract it out a little bit.
Then you want to use the same model for updating data as well because it saves you time.
Next thing you know you have a half-baked ORM of your own making that none else knows how to use.
In my home PHP project i ended up with SOMETHING that looks like a mix of Doctrine and Eloquent.
So now I just slap ORM in from the start.
I have been using Django for years and where I have had to resort to raw SQL to fix performance issues, I’ve eventually fixed properly by using the ORM correctly. And you raised a great point… even if you write it in SQL, you still have to put it into some kind of object model.
ah.. the classic case of refusing to add a dependency and having to write a dependency of your own.
Same. A lot of issues mentioned in the post feel like they're language/ORM specific, too. In C# for example, you can use EF Core with the "Select()" method to select specific rows. "Select(row => new Model{ Id = row.Id, Name = row.Name })" for example, will issue a "SELECT Id, Name FROM table" statement, which is pretty nice, as far as I'm concerned. The only thing it doesn't play well with, in my experience, are computed columns and stored procedures, but I'd argue you should just fire whoever wants to place all your basic CRUD logic in those anyways and save yourself the trouble.
I once used an ORM to bind an existing TERRIBLE DB to a model that was easier to work with. Makes it easier to get back into it after a break, without having to learn the whole DB layout and relations again.
selecting * from DB?😮
@@alvarorodriguesschmidt8507 explodes.
What I most hate about ORMs is that every ORM has its own API, so you spend a lot of time learning and fighting this API, I prefer to just use SQL, and finding help for SQL is a lot easier than for a specific ORM.
+1
I started my Java career working with the Hibernate ORM. Right in the Hibernate book written by the ORM creators, it starts with "Learn SQL first".
And you *can* tune most ORM queries to eager or lazy load joins/associations as needed. So performance can be fine.
But I just don't see how it's worth the additional learning curve when you must know SQL anyway.
I really like Haskell's quasi quoters for this, where you can throw SQL strings into your code with what looks like string interpolation but is actually automatically sanitized
Another option is not to go with either ORM or SQL, but use them together.
A textbook example is using CQRS to separate your reads and writes and then using the ORM on the write side and raw SQL on the read side.
This way you can use ORM features that make your life easier for writes (identity map and in-memory cacheing, update detection, easily map complex properties to json columns etc.), and use raw SQL for reads/projections.
Also, some ORMs like EF Core can handle migrations as well.
Finally someone with common sense!
100% true, I hate devs blaming tools for their own mistakes. An ORM is a tool and it will mess up your application when used improperly. However this is not the fault of the ORM it's the fault of the developer using the wrong tool for the job.
As a dev you should know when an ORM is producting problematic code and write your own SQL in those cases. However this does not mean you have to write every single query in your application by hand.
Great take, I never thought of it that way, and it can make a lot of sense in some contexts!
@@Malekthegreat Agreed, knowing the rough edges/limitations of your tools is important, since it gives you a clear indication when not to use them. And if someone is not up for learning yet another new ORM DSL/API and wants to write SQL by hand, there are still tools like Rezoom.SQL (F#) which effectively give you statically typed SQL based on your DB schema.
this
"Dual schema dangers" is for me the second most important reason to use django for my applications.
I write me schema once and I'm done (all the "batteries included" is reason no1).
Migrations are also basically provided for free. With django you don't rly thing about having a database attached, you're just working with your objects.
"Don't focus on the tools, learn the skill of programming"
One of the best advices I got from a senior mentor of mine, I think this sums it up really well.
like everywhere: tools can be good and helpful. but to know if its good and how to benefit the most you have to understand what the tool is doing, therefore you need to understand what you would have done by hand. so your mentor is absolutely right: understand the fundamental concept so you can understand and judge tools and code.
True the basics is what actually matters to learn and spend time refining the knowleddge
That's why I am always rooting for Dapper, which just lets me write an SQL query, get exactly what I need and map it however I want - not forcing me to make 100% the same class. Well, you can kinda argue that Dapper is still an ORM (some call it mini ORM) - but I really appreciate that I can just use SQL instead of learning the API of another library someone decided is cool enough for everyone else to dig into. I still have to write SQL queries anyway when investigating/troubleshooting/experimenting with the schema declaration, to later 'translate' into the library's 'language'.
Oh, and what I also hate so much - when ORM decides to cache which tables are often joined and then add it to every other query I need. Troubleshooting these monsters to find out that this bugger decided I need another inner join which actually removes the data I need is torture.
Dapper is great 😊 I like its minimalism and straight forward approach.. EF Core is good, but I don't recomment to use it if you don't have strict understanding why you choosing it
As a Rails developer I heavily rely on ORM. I actually had to work on a time series oriented DB and boy, I had some of the problems he mentioned, specifically writing queries which rely on window function. When you know SQL a bit you tend to think how you would write the query and then back port it to ORM jargon, which is usually more complicated unless your query has to join and filter tables dynamically given different parameters. Creating reports is where ORMs really suck hard on. If you have to manually select fields, manually ask for distinct results, manually map native functions (no ORM does that for you), think about inner join or left outer join, understand when lazy load nested models helps you not to load the whole db vs when actually eager load them to avoid hundreds of small queries. Last but not least, query debugging... you need to know SQL.
Right, but i think you'd agree once you master SQL and ActiveRecord, then it's much nicer to use ActiveRecord than not use it in an App. Just knowing sql is good, but i would hate my life if i had to write raw sql all the time. Not to mention, imagine having to manage preloads yourself instead of having AR doing it, i mean you'd just have to use some kind of library there hand rolled or not, and you'd have to learn some kind of convention regardless.
As for functions and windows, you simply just write them as raw sql, so it's not any more difficult to use the orm.
Just reports!!! Only thing works really well in ORM it's CRUD or simple select, basically nothing more...
ActiveRecord is awesome. But a lot of devs fall into trap that I must do everything with ORM. Another bunch is I have to do it with Arel if I can't do it with AR. A lot of things can be done with plain SQL subqueries - `where("#{table.name}.id in (ids_sql)", binds)`, that's usable as normal scope. DB views are, work just like tables. Reports, exports or anything that requires complex SQL, write it in SQL, slap it in `find_by_sql`. And in the end there is always `ActiveRecord::Base.connection`. ORM is just a tool, must learn when to move to different approach.
It always shocks me when I realize some of the shortcuts people take with learning software development. It never occured to me you would use ORMs before you've built applications and grown a healthy distaste for raw doggin SQL.
When I first started I was taught a bit of SQL to know how it works but then I never had to use it for any app, just directly using an ORM.
Shortcut is a shortcut for a reason. It is faster and easier to build applications that way. Later you can always return and study the details of how you did what. You don't always have to start from the ground up. Some people are not theoreticists, but ENGINEERS. Engineers like to build functional stuff from the very first day. Their learnining looks like Practice -> Theory.
More than that, I would argue that Engineers who start from the practical stuff always learn better and become better developers over shorter period of time, than those who started from full 0 and went up step by step. That's why I was able to become job ready in 4 months(and found it in 1 more month) but for other people it takes YEARS to do the same. The difference is in approaches to learning.
This is how it should go indeed IMHO. OTOH I've also been guilty of recommending an ORM to beginners sometimes because they just won't (don't want to) learn SQL properly, they just want to get their hobby projects done quickly, and ORMs rarely get in the way for those simple (typically little more than basic CRUD) cases (EDIT: and will always emit better SQL than the beginner would by hand).
EDIT2: Also ugh, what was mentioned in the video... the noob in question was formatting SQL by hand using user input and obviously no sanitization or escaping.
@@twothreeoneoneseventwoonefour5comment of the year
Distaste of raw SQL?
I would love, if people grow a distaste of ORMs like Hibernate, since many do not seem to understand what's happening behind it. (prime example: Lazy Loading)
Django ORM saves a lot of time. It not only hide the complexity of SQL joins but also provides signals to run something after the record is updated or created. It's a very productive tool and still opened for raw SQL requests.
I... have actually built parts of an ORM in Java. To handle the simple case of turning a row into an object. It only handles simple cases though.
You can annotate a class with JPA annotations, and tell the api what class you want to turn the rows into, and you get a List back.
It's really easy to use, and works quite well, but it's also very specific to the current project, is very small and limited, so it's not like it's some sort of Hibernate alternative.
It was made so that raw-dogging SQL could be done more easily, by not having you translate row -> constructor yourself.
What's wrong with just using sql? It's only disadvantage is if you move from mysql to postgress for example. There might be difference in syntax. Changing database does not happen all the time though. I prefer writing sql over using orm.
ORMs ensure your code maps directly to the database. When the ORM doesn't work is when you're writing custom reports with complex queries. So yes, you need to learn SQL even if you use an ORM for basic CRUD functionality.
A good ORM will also help you find sloppy database design so you can clean that up.
Yeah, you don't use an ORM to do complex selects. The only thing my ORM supports is AND and that's on purpose. If you need so much as an OR clause, you're writing SQL and then returning the result set as custom strong-typed objects.
People like to find a hammer and then think they can ignore the rest of the toolbox.
Thank you! Seriously, I'm so happy the community is moving back towards understanding the fundamentals instead of adding on unnecessary things
ORMs are not unnecessary.
All the listed reasons make using SQLDelight great.
You write compile-time checked SQL where each SQL query is turned into parameterised function on a Kotlin class. It checks your queries by parsing your migrations to understand your Schema. Very handy.
Also supports custom adapters so you can use custom types as args or return values.
Disclaimer: It doesn’t understand all of Postgres syntax yet.
This guy claims to have experience with ORMs like SQLAlchemy, but statements like "ORMs don't help manage data migration at all" are just plain wrong. I used alembic at multiple jobs and never had issues. Would much rather be doing this than hand rolling migrations with SQL.
yeah, that makes sense.
Data migrations are a usually simple alter table statements, nothing complex really
awesome podcast app tuo btw. why did u stop?
I bet most people that don't like ORMs don't even hand roll their own migration system and don't do proper version controlled migrations in the first place
Feels extra good when the DBA supplies you with a more performant query and it takes a week of hibernate spelunking to figure out how to generate it.
I totally agree that Stored Procedures requires one to draw the line carefully. A stored procedure should be delivering data objects that are as agnostic to business logic, as possible. They give you a logic toolset that isn't available through vanilla SQL and run natively as a first class citizen in the DB engine, but need to be designed with the idea that they can have the opportunity to be shared, by other applications or functions, even if they never actually are. This helps to keep business logic out of the equation and leaves the SP to do what it is good at.
also not having to do a recompilation on your whole project (as long as the sproc returns the same fields) is a big win.
Data and business logic are very tightly intertwined, they are two parts of the same Business thing. The fact that the data is stored on a separate machine and accessed over the network, and that queries are written in the special SQL language, don't change that. SQL code, both DML and DDL, is application code / business logic. This is nonsensical and completely arbitrary distinction.
I think we'd disagree. Stored procedures only exist for business logic. If you are not using stored procedures for the business logic, there really is no reason to use stored procedures.
@@glensmith491 To say this, I assume then you would, then, say that you could accomplish anything a stored procedure could do, with tSQL query?
@@sevilnatas more that if the business logic is not in your SPs, you probably should not be using SPs in the first place. Views and other dbms tools make for better data management. I also assume that there are at least a few bad actors smarter than me and want inappropriate access to my data at least as bad as I want them not to. On the reverse side, I also assume that I am not smart enough to be able to stop stupid people from doing stupid things, so I do what I can to minimize damage that can be blamed for.
The thing with the 14 sql joins was he was trying to use row oriented transactional db to make reports that should’ve been built with at least columns oriented db or dimensional modeling.
i still believe orms make sense for dbs like mongo because you arent meant to have foreign keys or whatever in them, but you would do a deeply nested document, which would create highly composed structures/fill in fields of highly composed structures in your code. for context, i believe r in orm means relating db and code, not relations between db entities
I worked a job that had business logic in the sprocs. They had it scale well because each customer got its own database and they had legions of servers. It was pretty slick being able to solve production problems by just calling sprocs and eventually having some standardized scripts/wiki blurbs. We didn't need to write custom apps to apply the fixes. We could just use the already system tested sprocs.
Prime really needs to give Entity Framework Core a fair shake
ok ok ok
preach.
@@ThePrimeTimeagen I love the audience engagement here! In your defense, I think a lot of JS ORMs suck, but it's mostly because the language itself doesn't support the kinds of operations that make working with an ORM easy. EF Core is so convenient to work with thanks to strict typing, great interfaces like IQueryable, and Fluent APIs! Joins amount to a single .Include() call, and projections amount to a single .Select() call, and since the language supports dynamic types, you can cheat by not having to define a concrete class for your projections. Sure, you should still learn SQL for advanced things like window functions, partitioning etc., But if 99% of your logic is simple SELECT FROM WHERE GROUP BY ORDER BY, EF Core is really fast to develop in.
The difference between the poor ORM options you have in the JS ecosystem (Prisma, etc.) and EF Core is very much analogous to the difference between a 15 year-old pentium Dell laptop and an M1 Macbook. You can't have only worked with garbage laptops and then from that experience draw the conclusion that "laptops" are shit.
I feel like he would like Dapper better. It has less abstractions. EF is heavily framework and someone needs to understand the framework a lot in order to use it properly.
SQL Alchemy has Alembic which diffs your schema as you change it and provides a migrate, and restore function for each step, so you can apply and walk back multiple changes easily. Its a great tool. It also lets you choose the level of ORM you use there is a nice basic version which lets you do vanilla CRUD, and then a girthy layer which adds all the ORM shenanigans.
This is why drizzle I believe is so popular. It allows the Dev to write type safe SQL while being able to control the underlying query (if you need to).
drizzle on deez nuts
it's popular because it's javascript. And the only main option before then was TypeORM which was a beast but slow and just behind the times. Drizzle really isn't anything new or special tbh. Many languages have thin type safe SQL builders
This is the second time I see this, what do people mean by type safe sql?
You think a int column would turn into a text column out of nowhere?
@@SpikeTauntits auto-complete lol
Primary issue with store procedures is multi point deployment. You need to deploy your app, and then all other procedures. And deployment of procedures was a mess in any db system I have used. Essentialy I have not seen a database where you can deploy your procedures as versioned artifact.
I use them sporadically - as form of optimization, and for bookeping jobs like partition creation or reporting etc.
I have been writing SQL for 30 years! Every few years a new ORM comes out and companies want to adopt it. Luckily we always manage to talk them out of it.
I am writing a customer application using a simple 3 tiered architecture, Angular, C#, and SQL. That is a killer combination. I use stored procedures for complex queries and have built highly optimized databases with just the right indexes.
The performance is insane! Data comes up on my screen like the shutter of a camera! I move around bouncing from screen to screen, retrieving data and there's never even a millisecond of lag!
"If it ain't broke, don't fix it." I will stick with SQL unless or until something REALLY better comes along.
I really like the mapping ability ORMs give you, but I don't like all the clutter it introduces for the multi database support part. I wanted to have something in between like Dapper so I could map my entities and query results in code so they are typed once I receive the query result. I don't like having to manually type check everything for every project.
Agreed, but I use SP's for every interaction with the DB.
The "sql builders let you switch out database" is such an unhinged take. There is so much DB specific functionality that you're either not using (i.e. you're doing it wrong), or if you are you _can't_ actually "just" switch out your database.
It's like being cloud provider agnostic. Pure pipe dream, nobody "just" switches cloud providers. That doesn't mean that you'll never want to switch your DB, or never want to switch cloud providers - I'm saying unless what you're doing is so incredibly trivial that it's almost nonsensical to worry about it you'll have to change your code when you do _anyway_.
For standard MVC applications with lots of relations between tables, using an ORM makes development much faster, makes code easier to read, and makes mistakes less likely. It can also make refactoring a lot easier.
The user doesn't care about how easy it is for you as a dev to do your job. ORM's might help you not shoot yourself in the foot, but you can also just learn SQL and write your own barebone wrappers around complex queries. Honestly feels like a skill issue. That isn't to mention the price of having an entire layer of abstraction adding latency between you and the database, which is already hyper-optimized to query data efficiently.
@@laztheripperIt's 100% a skill issue
@@laztheripper You're not really making any points with what you've said. "The user doesn't care about how easy it is for you" for your argument is at best a moot point, and at worst implies it is best to use an ORM, if the user doesn't care about the tech, why not use the one that makes my life easier. "skill issue", yes, and? again, if what matters is the end product, does it matter if it is made by big brain dev writing everything by hand, or not big brain dev using a tool that lets them also do the job as well. "the price of having an entire layer of abstraction" the whole concept of a database is an abstraction already, the latency for an ORM's internals to generate the query is in practice negligible for most cases, so
@@pinkorcyanbutlong5651 yeah, go ask prisma to do a join
@@laztheripperI disagree with you with your first point, but agree with your second.
The first point if a bug happens, the ability for the current developer to quickly fix anything to improve the user experience is always going to be priority.
Second point I agree with, along with everything primeagen says as a newer viewer to his channel.
The only problem, is for the most part not a skill problem but a discipline issue for some devs.
Look at Regex. Its really, really useful if your job requires working with a lot of string data types, but barely any devs want to learn due to how daunting it seems.
Its the same with SQL. When I was newer with learning and coding projects, I just found certain things to learn more daunting when comparing other tools / language’s that I found learning easier. Thats what I did to SQL the first year learning to be a dev, and other time after the first year I learned vanilla SQL is just the way.
I think the issue with ORMs is that they are a vertical abstraction rather than a horizontal abstraction.
What I mean by that is vertical abstractions try to hide things away from you and have implicit magical behaviors that are hard to debug unless you know exactly everything about their way of doing things.
A horizontal abstraction is hiding things but in an explicit way. You are effectively compressing your code but you can open the hood at any time to inspect it.
Examples:
- Vertical Abstraction : Django
Django has this whole ecosystem behind it where it abstract everything away and you have opaque control flow and implicit behaviors.
- Horizontal Abstraction : SQLc
SQLc generates SQL type safe interfaces for you that you can inspect and if it isn't to your liking you can change the configuration so that it generates it properly.
If the SQLc doesn't solve what you want or it is too many docs for you to read, you can still generate the stuff you need then create the rest on your own manually.
The problem isn't Vertical Abstractions, but too many of them. Like without Vertical Abstractions, we would be using Assembly Code still. They are useful but they always reduce the power you have for convienience.
Prefer Horizontal abstractions until you really need to Vertically Abstract.
The only thing we use ORMs for is not having to write the migrations manually. Most ORMs allow you to basically use them as a query builder and that's what we do. For anything performance critical we use stored procedures or other DB specific functionality.
This is where I’ve arrived too. The migration tools are great, other that that it’s too slow for anything useful
Do you actually find that useful? Easy migrations are still easy to write with just SQL (or a sql builder for slightly more convenient syntax). If it's _not_ an easy migration (let's say anything more complicated than adding/removing a table/column), I really wouldn't trust an automatic migration anyway.
@@hannessteffenhagen61 I know that it’s easy to write basic migrations in SQL. Basically every migration tool I’ve used is very reliable and produces acceptable SQL. I never understood why I should bother writing versioned migrations manually. And in case something goes wrong you always have the option to fine tune it manually.
@@roccociccone597 I just don't see the point in automatising easy stuff I need to do _maybe_ once per week and that generally takes minutes to do that'll fail the second I try to do something nontrivial.
Automigrations are OK for local development if you're iterating on something, but why use them for production changes?
@@hannessteffenhagen61 I literally use it to run sql files up and down. I would call all of it nontrivial for what it’s worth.
I just want to add that ORMs also can make your database more stupid by simply not letting you access very useful database features. I saw people doing crazy DB round-trips instead of just creating and using a view or a stored procedure. If your colleague is also a backend "CRUD purist" then you will need to write your joins on frontend...
Exactly! Even when working with a proprietary program DB you can not change, Views can be put into separate database, and then used via ORM model.
1. ORMs, like most good abstractions, make 80% of the tedious work trivial, while still making the hard parts possible
2. You can ALWAYS go back to writing raw SQL, even with an ORM, but it's a lot harder to go the other way.
3. The chance that you actually need to do an optimization the ORM can't easily do (for example, his foreign key problem is easily solved with lazy annotations) is tiny for many categories of project
4. Writing raw SQL turns into string manipulation. SQL is also just objectively a horrible language, because it lacks any of the sane sugars, like functions, variables, etc.
5. At the absolute least, please use a Query Builder. They'll give you some level of type safety, syntax highlighting, and conditional building
6. Know SQL. Hell, don't use any ORM feature you couldn't (roughly) do yourself in SQL
You get it. Using native SQL in code is a highway to writing stored procedures which are inherently violations of the separation of concerns - a valid architectural decision to make in favor of performance. The trouble is that 90% of devs aren't great architects and lack the understanding of their flavor of SQL to create a competent bindings to stored procedures - why reveal that level of SQL and make your application more difficult to maintain in order to provide a boost of performance? In the same vein, why not use an ORM when all you're going to do with your native SQL is bind it to data models in your application code? ORMs were an emergent pattern that we realized we can design to make better. Every "hard problem" that an ORM ends up making is a violation of the SoC anyways. The database is only meant for data, let your API drive your database - do not consider your database anything more than a driver to provision data for your API/DAL. It makes me laugh when developers say "I don't use an ORM," because that means you're making one yourself to provision your data to make your application testable. While you're writing your ORM, I'll be working on actually solving business problems, which is what we're paid to do
Since when does SQL lack variables and functions?
@@psyoptic since we moved from them to use slightly better languages to develop our applications with, like Java, C#, Ruby, Go etc... since then SQL functions stopped existing for most of us :))
You have to learn another library on top of learning SQL to use an ORM. That's why I don't use it.
@@rohitreddy6794 When you use external libraries/packages - you also learn how to use them.
Nearly everyone is missing the fact that between orm and native sql sitting the dbal .. and this is what we love and need ❤
There are many benefits with using ORMs. Especially in an OO/MVC application. For example:
- All the objects get hydrated automatically. You get objects and collections of objects directly that you can work with in in your app.
- It is much easier to read and understand. For example Model.where('col', '=', 1).with(relation).get() on your Model instead of dealing with joins. Long and complex SQL queries are usually a lot harder to understand.
- You can abstract away what kind of database you are using.
- You get a lot of additional tools for migrations, seeding, streams etc.
- If you are not good at SQL you will probably write better queries with an ORM/query builder.
> - All the objects get hydrated automatically. You get objects and collections of objects directly that you can work with in in your app.
Automagic, and then you wonder where the performance has gone.
> - It is much easier to read and understand. For example Model.where('col', '=', 1).with(relation).get() or Model instead of dealing with joins. Long and complex SQL queries are usually a lot harder to understand.
You can use SQL builders.
> - You can abstract away what kind of database you are using.
You can do that by just using ANSI SQL, lol.
> - You get a lot of additional tools for migrations, seeding, streams etc.
I think that's the only reason why I would use migrations.
> - If you are not good at SQL you will probably write better queries with an ORM/query builder.
then get good in it ffs. It's not that hard. It's easier than Rust, tbh. It's like never learning how to use a fork.
a) that may come at a cost, like cardinal queries because of lazy loaded joined classed or even worse, load the entire database cause you are querying the main domain model.
b) debugging a query builder ( usual case scenario, you build a query depending on parameters being passed ) still requires you to know how to read complex SQL queries. This is the only scenario in which patching a sql query string might be more confusing than using an OOP query builder and the reason why Rails developers learn Arel
c) you'll never change the database. Never happened once in my 20 yrs long career. When it happens it's because you've been tasked to rewrite the whole thing. Anyway no ORM abstracts away native functions, only SQL dialects.
d) doesn't mean you have to use the whole package
e) ORMs do a lot of work but still requires you to know what you are doing so there's not much you can do without knowing SQL, easy stuff.
- ORM can also provide caching speading up your application significantly
- They provide attribute converters
- They help you out with different Indexing strategies
- It can help you with cascading operations and so on...
- It can significantly help you to focus on rather object oriented models than tabular format of your data (this is especially good if you are trying to avoid anemic objects as your applications first class citizens)
There are so meany ways that ORM's can save you time if you have mastered them...
@@peppybocan
> Hydration does not give you a significant performance penalty if you are not doing some crazy things. Productivity is typically much more important than small optimisations.
> A good ORM is often very similar to an SQL builder + some extra functionality.
> No, you still need to use things that are specific to your database engine sometimes.
> Why wouldn't you use migrations?
> Sure, it's not that hard and it is of course good to know SQL even if you are using an ORM or an SQL builder. However, the ORM lets you focus on other things, that might be a lot more valuable, if you want
@@ClaudioBrogliato
a) Sure, sometimes you end up doing stupid things if you don't know what is happening under the hood, but in most cases it's pretty easy to fix. You can for example eager load what you want.
b) Yes, knowing underlaying technologies like SQL is usually good. But you should probably focus on the areas where you deliver the most value.
c) Yes, that is not common. But you don't need to worry about what SQL dialect you are using at all. You can work with different databases in different projects for example.
d) No, but that makes it a lot easier. And pulling in a whole ORM just to get for example migrations might not be the best solution.
e) Same as b.
Great take on why not to use ORMs! Since so many people seem to use and like them, perhaps it makes sense to explore what the benefits are?
Entity Framework is the best ORM I've ever used. It's soooooo easy and works like a charm.
complexest orm ever used
good try satya
Yes, it works good when you know what you should NOT do with it, like multiple include, fetching readonly data with tracking etc..
@@mad_tthe same can be said about SQL.
its good enough, but by default I prefer to use Dapper.. because of simplicity and straight forward approach, as well as performance...
And also using EF only in case we clearly see some strong benefits of using it..
An ORM is like riding a bike. It makes what is easy, easier (going straight or downhill). But it makes doing what is hard, harder (going uphill).
Even if you use an ORM for 80% of your queries, nothing is preventing you, the developer, from raw dogging SQL for the more complex queries when you need to
@@TheRealCornPop then be prepared for 100 code review comments saying 'you can actually do this with x.y.z.q.r.s.t.u.v.a.b.c.query()'
EF with Linq is fantastic. Especially now they are doing more performance optimisations.
Also re the point made at 9:00 Linqpad ... so just write it in linq from the start.
Yes its declarative, yes its a query language ... but you get a lot of benefits simply not available to SQL.
Also if you realllllly need to you can always execute sql statements and bind the results to models too. Plus migrations, automatically escaped inputs and a whole heap of other shit like query optimisations, deferred execution etc.
SQL is great but so are good ORMs.
I love EF and c#, but most people here will probabely hate on c#
Indeed, EF is great, the problem are the programmers. I always recommend C# juniors to check what query EF is generating before pushing code, so they can learn what Linq and EF do internally.
Also Dapper is really good for learning/small projects. C# really is well fed in terms of ORMs.
11:34 good ORMs manage that for you. When using a code-first ORM you only ever worry about your database entities. You dont maintain anything else and when you change the models you generate migrations which you can run against databases to update them.
Ergo ... its not really redundancy you are going to want models for reading your data out of the database anyway ... so realistically you have to write if anything less code.
@@victorhf9804 Dapper's great, it's what I use at my current company.
For medium to large scale projects
To be honest I would rather we had been using EF here, simply because of some of the legacy problems we have which would not exist if we had used EF.
Nothing to do with Dapper per-say just historic practices.
at 5:46 the same could be said for manual memory management vs GC, like people seems to spend so much time and efforts fighting the GC of their language, pulling up insane Data structures and algorithm, when all of your problem could have been solved, with malloc and free lol. I think people just fking love complexity.
Good ORMs implement "Identity map" and "Unit of work" patterns. They can handle transactions, they can lazy-load joined tables. They always provide query builders. Good ORMs optimize queries (Unit of work). You can do 95% of work using classes that push readability and productivity on another level and 5% using query builders.
If you are fighting your ORM then you've made a bad schema...
Examples of good ORMs?
All the orms you can't find on the list of bad ORMs.
@@ccgarciab MikroOrm for Typescript
@@ccgarciab The confusing example: Doctrine ORM in combination with Symfony framework. PHP.
BTW. High load? Never use ORM. Hire a skilled DBA that will manually tune your queries
@@ccgarciab SQLAlchemy that's quoted in the video is the single best ORM I've seen (and used) so far. I don't think I'm ever going back...
in spring boot ( i think the module is spring data jpa) you get these built-in repositories where you just name interface methods these long ass sentences. Like in your UserRepository you might make a method like "findByOrganizationAndSignupDateGreaterThan(Long organizationId, Date joinDate)". And you don't have to implement the method, it just works. It's pretty cool as java things go.
I often have to use queries with multiple joins, unions, group by, etc, doing it with an orm is a nightmare, the sql produced is always crazily inefficient to the point that not even a junior would come with that. It's like any orm was made with just todos tutorials in mind. Considering that on aws you pay for the io produced in the db, it makes anything prohibitive, not just slow and inefficient. Prisma is criminal in that aspect.
Most ORMs allow you to write raw SQL, so for complex queries you have that option, but for typical crud operations ORMs reduce a lot of boilerplate
the dude just needed to learn from his own mistakes and laziness don't blame the ORM for that
The author seems to be poisoned by hibernate and the shitty documentation around it. Hibernate is a dog shit ORM, because it does a ton of unexpected shit, that noone should really care about. However, it does generate SQL migrations, which means no ORM specific DDL bullshit. To not off yourself you have to have a relatively complex local setup, where you drop and create DB and run migrations and seed the DB on server startup, thus ensuring schema is in sync (pairing your ORM with something like Flyway).
Also, ORM are not replacement for writing or knowing SQL, they are useful for one thing and should be used for this thing only: mapping the query results. In any relatively complex application you will want to drink bleach if you write the queries and mappers by hand. Also also, if you have to wrestle your ORM to give you the query you want, it's probably a shitty ORM that tries to hide SQL. Try TypeORM if you are in JS world.
fair
Couldn't agree more. It's annoying to see how many people think ORMs are for people who don't know SQL. That was never the point of ORMs. The point is literally Object Relational Mapping. The migration system is a bonus
Hibernate is the best documented ORM in the world with a detailed documentation and countless books written about it. If it is doing something unexpected, you didn't read the manual before using it.
9 out of 10 problems when using Hinernate come from developers copy pasting some examples mindlessly while not even trying to understand what are they really using and why. If you don't need multiple caches and essentially a DB on top of DB that Hibernate is, use one of countless simple object mappers
@@NJ-wb1cz "If you don't need multiple caches and essentially a DB on top of DB that Hibernate is" - nobody needs that. The only reason anyone even comes to use Hibernate is because they mindlessly copy paste from any java blog post ever.
@@sarabwt nah, most moderately complicated server side processing needs that. You update an entity and want to see the changes in your other parts/stages/threads reflected synchronously without updating from db. Hibernate makes that easy without convoluted messaging and manual updates constantly going on between all parts and without slowdowns. But you still have understand what are you actually doing, and have some semblance of idea of what an enormous task hibernate is actually doing behind the simple calls
If you raw dog sql all the time, then you'll find yourself repeating a lot of queries, repeating mapping logic to map the sql results to domain models, then you'll ask yourself wtf am i not just using an ORM?
Are there tradeoffs? Yes. Any non-trivial queries (which from my experience isn't common) you need to raw dog it, but for most queries they're trivial crud operations. You get type safety, migrations, schema version control, etc.
I used to raw dog sql, back in the early 2000s when using PHP. ORMs save a lot of time. Like everything in engineering, there are trade offs. I'll take benefits of ORM 99% of the time.
Depends on the type of ORM that you use, language, framework, and who implemented it
Most devs don't even know the different between Active Record vs Data Mapper
Java Hybernate is weird as heck coming from Laravel(PHP) Eloquent where the methods for the query are basically named like the equivalent from SQL (mostly because it's written to work over the query builder).
I've seen some projects on the god language that is JAVA where people broke the ORM because they wanted to add a fancy abstraction that they didn't even know how to explain to the team
We're currently using Larvavel with 100% Eloquent for the back end with a custom API and Vue for the front end. We are using Telescope to test. As long as we are cognizant of the pitfalls I don't see many drawbacks to taking this approach. As a Laravel dev yourself do you see any issues or have any advice?
Rawdog SQL when making quick prototypes on my own that probably won't see the end of the month.
ORM when making anything that needs to go into production and will get touched by others.
I know how to rawdog some SQL stuff... But can't expect my co-workers to be competent enough for it so I just want to lower the opportunities for them to screw up (which will cost *me* time and effort).
This might be the only time I disagree with you prime. The number one benefit for an ORM is speed of dev. You don't spend precious time thinking about what you want to fetch/write. For complex queries you use the query builder. It's like saying why use tailwind learn css. Of course learn SQL, but why not use an ORM for up 90% of database related work when it can do the heavylift and optimize when necessary ? Seems unwarranted.
Agreed. Even though most of our codebase deals with complex objects that have lots of attributes, our production database spends far more time performing simple `SELECT * FROM ... WHERE ...` queries on tables with just a couple of columns but lots of rows. Those queries are perfectly well-suited for ORMs.
@@yyny0 I agree. I don't see myself passing out on a data mapper, a query builder, a caching layer, a persistence/update event emitter, encapsulation, and this has nothing to do with my ability to correctly use SQL and an SGBD ... On most complex systems I've seen the view does not relate to the schema. Fortunately, we have other patterns to alleviate this. And I'd always argue to start cheap. Why use a hot tablespace or table partitioning if a simple cache layer fixes this with a small to no latency whether it is a full fledged reverse proxy or nextjs function ? Our first job is to keep a good time/cost to RoI balance.
Man, Elixir and Ecto are secret superpowers and an amazing competitive advantage.
What an ORM does for you is help attach functionality to objects (it's called Object Oriented Programming). SQLAlchemy lets you build SQL expressions (SQL Core) and you can mix and match this with the ORM style if needed.
-10 respect points for your opinion on this one Mr Agen.
So I define a model once. This is a perfect representation of the db table. I can then have automatically generated CRUD views, automatic create/edit forms, automatic filters, automatic migrations, all with a few lines of code. All the logic is in one language, I never touch the DB. And somehow this is bad?!
Nobody noticed that the article is from 2014? ORMs have come a long way since then... Used to hate working with EF but now I find solace in the fact that I can project queries to objects and limit the number of joins an optimize queries that result from LINQ but also do SQL when I really need to... This combined with precompiled queries makes it close to native performance with the added confort of using type safety and migrations and LINQ
ORM is definitely a good example of the type of engineering where you think the solution to a problem is adding another layer to the stack. Not fun to debug, too much magic going on
ORMs save you a ton of boiler plate for simple/common operations. In ever ORM that I've used, you can use raw sql if you need to, while using the ORM most of the time.
oop with raw queries does that too and better utilization and better readability and better efficiency
For every minute of boilerplate an ORM saves you, it costs 10 days of "WHY DOESN'T THAT RELATIONSHIP WORK RIGHT!"
@@phazechange3345 BECAUSE U SUCK AT SQL RELATIONS
@@phazechange3345 that's just a skill issue though. This entire thread reads : I don't know the orm properly, therefore it's bad.
I've never had these issues with the django orm, and I'm cranking out one CMS website every sprint/other sprint.
Most projects have tremendous benefit from ORM. It’s only when you scale to a significant size that you get into trouble, which is a minority of the projects out there.
A lot of things are not true with dynamic language based ORM like prisma or django ORM.
The model(application code) can automatically translate into DDL, also when the model codes change, there's tool to write migration code automatically and keeps the history of migrations.
I think the article's experience of ORM is from hibernate, which is for java and also is very hard to automate, like migration, etc.
Also may I add that, ORM of static language like Java is more difficult to write intuitive query than dynamic languages like python or javascript due to language constraints
Jesus Christ imagine debugging and ensuring that your dynamic language reflects your database schema correctly and then also generates dynamic queries such that your database will not die from a few concurrent queries.
@@youtubeenjoyer1743 what are you talking about? Why is this not an issue for literally anyone using Django professionally at scale?
Real talk here; ORM's are never meant to skip learning SQL and how RDBMS work.
They are just there to save you from writing and maintaining boilerplate CRUD operation SQL's in a forever changing DB DDL.
We used Hibernate in my previous company, we mainly used it for basic CRUD operations, for anything complex we either wrote 1 - HQL, 2- raw SQL or 3 - A view. I never had to maintain any POJO's CRUD SQL. We never had any performance issue we wouldn't have with raw SQL. We were able to use hibernate to report POJO vs DDL mismatch (We were maintaining multi-tenant DB's each having their own).
We had best of both worlds, still miss using Hibernate.
- A Regular IBatis Hater
ORMs remove the differences between different database engines. For example the Django ORM works the same with Postgres, MySQL, SQLite etc.
Cause, as we all know, your app is changing database backends all the time, and your basic crud app that runs basic ISO only queries definitely needs vendor specific optimizations/syntax that don't exist. This is a superb tradeoff to make for a framework that's going to random ass generate a query that'll take your whole production down a couple times a year.
@@phazechange3345 I’ve never had an ORM generate a query that took the “…whole production down a couple times a year”. You could make a better argument saying that it generates inefficient queries. ORMs have their place especially with applications that need to work -at the same time- with two, or more, different database backends. Or applications that allow for the same functionality with option of different database backends. Migrating a large amount data from say, SQLServer to Oracle is no easy/quick task; just to accommodate an application.
I use dictionaries of bind variables to return dataframes for joins on other datasets. I also use if statements to dynamically change the where clause based on user selections. Hope I don’t regret this in the future but it’s working fine for my current needs.
The only ORM I actually liked is the the one from spring data. It provides de basics only and it allows you to write custom sql in the most ergonomic way
I'm currently using entgo for a project and the ORM functionality is honestly the least interesting to me.
Basically, I can define my schema and I get
- types for my go codebase
- query builders for db access
- a complete graphql API
- protobuf types for other services that use the same model
- hooks that allow me to implement transparent application side en-/decryption of sensitive fields on the data access level
- a rule engine that allows me to implement efficient, role based authorization on the data access level
For a codebase that's being reused in multiple downstream projects that's just immensely useful
Gotta say, after dealing with JS ORMs and their bs, (looking at you specifically Prisma) I fell in love with SQLC in Go. Write all my SQL queries and SQLC creates the Go models needed to run those queries but otherwise isn't really taking over my queries and man are things buttery smooth compared to what i'm used to on the js side.
I like the input sanitation / single place definitions for tables using something like SQLModel in python, I don't really use it for more than input sanitation, validation, and to make those objects self documenting, but you can still query a database however you want, knowing the set you have in there is nice and clean.
I really like all that. I always have this weird feeling when using ORM or other cool badass astraction that something is wrong. Their website is great, the marketing is awesome, the examples are beautiful, everyone is drinking the cool-aid. But still, something feels wrong. I really your videos, they really resonate with me and my feeling with programming. Cheers!
Django or Tortoise builder pattern into lazy evaluating querysets are awesome
generally in python all orms, so peewee and pony, even alchemy, are really nice tools
In my experience ORMs can be fine and actually pretty helpful if the Dev knows SQL and they can significantly reduce the time to build the SQL you need with proper sanitisation, etc. Agree with the point that Devs should learn SQL first. I'd never get rid of using ORMs to build SQL, though, that would be a step back for me.
I'm on a team of using both. ORM / Active Records for simple things (but only if used framework gives some additional bonuses that really make work easier, not if you need to write that on top of some JDBC or other lower-level access). I mean CRUD-like things with a slight touch of relations or forms where you save few rows into one or two tables at most. Raw SQL for anything elseo, especially more complex like data views, tables, searches with relations, reports.
Even more complex reports - I use ETL / Python scripts that generate them from the data received by SQL and save it/cache regularly, don't generate in user session making user to wait.
Django ORM looks good tho. I know its easy to be slow using the query, but with time, they also give tricks and tools to help speed up performance
Besides migrations, Prisma offers great Typescript typings. All your enums and querries come out perfectly typed.
This is what most ORMs provide in any statically typed language
I burned so many hours dealing with hibernate. The most brittle thing in the world. Far worse than the borrow checker
Huh? I have built amazing software systems that are completely DB agnostic with Hibernate. It was always a pleasure and a breeze to work with it.
I partially agree with your statement but in many cases companies have to build entire applications from scratch (or even migrate from an existing XLSX) with a small team of developers in less than a year. For those cases usually you have two options, use an already existing ORM which implements the Repository pattern, (or an SQLBuilder), or implement your own. It's all about time and productivity
When you are still developing your product and things are changing fast, an ORM is great - mainly for the auto-migrations and type safety that propagates throughout your IDE as things change. It's great for teams moving quickly. As your product matures and scales and you need to improve performance, start writing SQL.
11:21 domain duplication happens everywhere. Any client calling a server will have a duplicated domain definition. Any actor using a protocol will have a duplicated domain definition. This is only made worse when the clients/actors/servers are written in different languages.
ORMs are one of the worst ideas in the history of software engineering. Just learn SQL and write mappers from the database to your types. Use the repository pattern. Let the database be structured in a way that makes sense (normalized or not, joins or not - these considerations are orthogonal to your object model) and let the repositories turn the data into your objects. This applies to any language and any database. Put a repository interface in front of Mongo, Redis, Cassandra, whatever. ORMs are a massive waste of developer time and energy, and usually compute as well.
I just want to be there in the room when an engineer is presenting at a design review and someone asks what ORM is going to be used and they respond:
"WE ARE GOING TO RAW DOG SQL"
And the delicate ears melt 😅 And the engineer is confused cause they have been listening to the Primeagen for so long they think that's the way people talk.
😂
Should be rawdog squeal.
Based. ORMs are trash
one thing I like about ORM (maymbe more specific to hibernate) is the thing, that it wraps an API Request into a full transaction.
other then that, in a recent project with prisma, we more or less used it for the migration and types. Most of the time we used queryRaw
The argument about too many joins being generated, in my experience you can manipulate that, at least you can when you use hibernate ORM, by setting fetch type Lazy or Eager, if you want your data to be fetched lazily (in a separate query) or eagerly ( hibernate generates join statement). However, my approach is to use both, ORM for simple CRUD operations on entities and SQL for more complex data fetching/manipulating.
It's funny how you all didn't understand the 600 columns.
He's fetching ONE table which does 14 auto joins and that totals to 600 columns.
He only wanted 2 joins, but that's just a configuration error of the ORM / Query.
Skills issue
ORMs are not really meant to replace querying the database but more for mapping tables to class entities.
My take is that ORM are the natural continuity of defining your entities (think DDD) in your code for business logic and wanting to have a declarative db schema (with automated migration). Since most of the time it is very similar, might as well prevent repetition. Then it's only natural to try to automate the bridge. But it leads to all this mess.
As a Pythonista today, I use Pydantic to define the data model of my domain so I can get JSON schemas, which I can turn into a declarative db schema, but I write my own queries to get the data and let pydantic parse it.
For my simple app, I'm on the Prisma+Kysely train. Prisma for schema definition and buttery-smooth migration, Kysely for the type-safety.
Now for my chonky boy (largest table is 14 billion rows), I made my own SQL builder for my Clickhouse and MySQL dbs that does things in the way I need them to. (I raw dog the schema.) This is primarily because I have microservices that must output the exact same data based on a given set of inputs, and having a centralized sql builder that I made that I know how it works guarantees this. Secondarily, it's because I have to do some really funky stuff with window functions and joins that I wouldn't be able to remember jack about how I to do any of it.
I remember I just had queries as SQL files one project, and then had a bash script the would pop em all into map[string]string for each package, when go generate was run, gave me the beautiful looking query which was also nice to use, and without performance penalty of loading a file
The big deal of SQL builders is that you get almost 100% SQL syntax check at compile time which avoids the need to run tests to confirm the SQL is correct and works right on the IDE or editor and not having to recompile the code and re-run every time the SQL is changed.
That's the biggest deal.
I can reuse query parts and can refactor a column in hundreds of queries at once without any issues.
No runtime errors.
ORMs have issues, but usually the pros outweigh the cons
I've been using an ORM for my last project. After things were ready for prod, the biggest lesson I've learnt was: I need to learn SQL.
@0:02 Everything's said and done. To every junior reading this: Learn SQL- Period.
While I agree with what was said in the video, my experience with (and without) ORMs don't fall in line with your conclusion.
I was part of a team working on multiple projects at once. One of them was using raw SQL and another was using an ORM.
The biggest problem with the non-ORM project was the half-baked classes and their joins. When calling a function you never knew which fields were populated and which weren't. This became especially complex when we had entities spanning 20-some table. (Yes, probably a document-based database would've been better but we all know how difficult it could be to change that in an existing project). An unpopulated array for an 1-to-many join would sometimes be null, sometimes undefined and sometimes even an empty array. Properties could suddenly become strings instead of numbers due to the language being dynamically typed. Worst of all, parsing and converting the result of a multi-table query into an instance of a class was always problematic. It was incredibly easy to overlook something. Overall, small queries were alright, but large queries with more than 5 joins were adding tons of (programmer) overhead. Much of the code was just filled with property assignations and conversions (from the query result to some nebulous class)
The advantage was performance. Optimizations were quite easy to do. Migrations were not really a problem (you could even use an external plugin to manage them), but even without, easy to implement.
The biggest problem with the ORM project was... well, basically what you said in the video. But the advantage was standardization. It became incredibly easy to make complex queries with multiple joins. When something was an instance of a specific class, you would already know how to navigate its properties. While the performance was not great (probably because of the creation of each entity), it was always quite simple to make an optimization to the query that the ORM was using (you always knew what query the ORM was executing)
The problem might be the language here. It might be the database. But, there are times when the best decision wasn't made at the beginning of the project and you are stuck using them. Combine that with having a team of people that are prone to simple mistakes and ... ORMs suddenly don't look like a bad idea
The problem with the stored procedures is that hard to maintain. For example you have 1000 lines of stored procedure that is calling several other ones. Would you prefer to navigate manually reading each line because you can not search (squirrel), and coping each procedure in normal text editor so you can apply some search rather than using some normal IDE to navigate in the code. True, there might be some limitations but try to migrate the DB and start fixing DB specific issues, adding the above and it becomes nightmare.
It's exhausting having hot takes coming from a random blog, in the chat, and in the picture in picture simultaneously. Then you hear, "new tweets this morning" and I can feel myself becoming a grandpadev by the minute.
I have not worked on any enterprise projects directly tied into an ORM but man, you can really feel it when engineers *don't* understand SQL. I've seen some projects brought to their knees by early misunderstandings about how the data in a database is constructed. 6 months of work tossed down the drain feels extra bad when you see that the entire program was built with false assumptions around what data does or does not exist.
I've had to set expectations with new engineers to learn SQL basics so that they can program without blindly assuming that the data will work in X way.
Just learn SQL. Everyone, especially your reporting team (if you have one) will thank you
I was using Borland Delphi. I was coming from a procedural mainframe background using SQL and my co-workers were OO developers coming to using SQL. I identified the SQL/OO interface was going to be a major pain point. 25 years later nothing has changed. SQL is still the best DB for enterprise transactional data, OO hasn't adapted. The ODB has never matured to be a viable enterprise product.
I did DBI and result rows in Perl when I started. Everything has trade-offs but I'll take the ORM ones generally. This OP is writing time-series reports, I'm not. I'm doing basic stuff that is not edge-case or pushing the envelope. The ORM should have an escape hatch. Yes, everyone should learn SQL but for the "forever skill" reason (to me).
One thing that I miss in Rails is the dev log shows you the SQL while you work. And there are gems like bullet and loldba which are almost like linters for N+1 and missing indices.
When a table gets wider than 10 columns, it stops being nice and starts being painful.
Every extra column above 10 is probably just going to waste anyway.
Also, are we talking about the table at rest, or when data is flowing through it in production?