Dont bring back the nightmares. Currently working in a team where this contractor created his own golang ORM and we're left maintaining it 😭 like just use one of the many db libraries out there
Well hello there. Appreciate that you took a very charitable take on my video, definitely agree that arguments in the beginning were absolute garbage. I have no idea what I was thinking. Will blame it on being sick for like 2 weeks and just getting blinded from having no interaction with another human in-person, but oh well. In general I try to make people who might not have built that much stuff/have as much computer science background avoid some very easily made mistakes. Thanks for the point about the intro, always struggle with low retention if I try to go slower, but maybe I should try that again. Very fun to hear your commentary from this POV, never thought I'd have someone react to a video I've made. Also, danggggiiit, 8:17, I didn't catch it... Facepalmed so hard when you paused there. that's what I get for not running unit tests :p
16:40 In Brasil if your product targets multiple government agencies then abstracting the database might be the cheapest and fastest idea, I've done some projects in that area and agencies always have very specific requirements for ALL tools so you're either lucky or every agency uses a different Database tool.
Well, the whole discussion is completely different if you do 'enterprise' on-prem installable products and clients come in like 'we need it to run with oracle', 'it should run on mysql', 'we use postgres exclusively'. Then you'd be really glad you have the abstraction layer in place.
All of that is true. I’ve also seen the other side of things, where I’m warned not to use Postgres’s built in features because “what if we switch databases?”. I’ve NEVER seen a company ditch Postgres. It’s hard for me to imagine a scenario in which I’d recommend anybody swap one mature RDBMS for a different one. I have seen companies swap web frameworks/ORMs and keep the original database.
@@stevezelaznik5872 Yeah, same here. Companies which build their own platforms (as in not products for others to run) usually do not change their databases. And when they do they need to rewrite stuff anyways.
@@stevezelaznik5872 We had to switch a monolith from Postgres to Microsoft SQL Server and we'd written all the queries without an ORM. Thankfully, we had a bunch of tests for all the SQL queries (we are super serious about TDD) and we weren't doing much that wasn't ANSI SQL so it wasn't too bad in the end. I still miss Postgres 😢
Most of the time I see a video called "Don't use X, use Y" it actually should be named "Learn when to use X, and when to use Y" Both approaches are good depending on what you're doing
Basically use ORM if all the things you do are simple, but if you go so complicated as "select all posts tagged X, Y, and Z" (multiple tag filtering), then raw SQL is unavoidable.
The sweet spot for me are simple query-builders that reflect exactly the syntax of the query under the hood, but save you from the pain of fighting with strings. Primary example is squirrel in GO. Once you have to build a ginormous query using a loop, using raw strings quickly ceases to be fun.
I understand you point of view and there is only one group to blame for it. The language designers that never seem to understand that dynamic & modular SQL queries must be easily expressible in application code. It almost always needs to be shoehorned in with a less than optimal syntax using some form of string concatenation (essentially). Same is true for any other textual language, obviously. And as expressing other language texts is not part of the syntax, people need to use strings. From there it means, the editors do not gracefully handle and assist the developer. And from there people consider ORMs. And from there, Hell reigns on Earth.
If you have to build a query using a loop, you're doing it wrong. Every query/command should be a compile time constant value. For example I often see this for bulk inserts with a million parameters, or worse a lot of values added directly into the query, but any RDBMS worth using has a way to batch/bulk insert data without needing this.
@@georgehelyar Dynamically extended/modular queries will never be known at compile time and that is fine. But you are right that the dynamic part should not be from a loop that just adds the same kind of check over and over. That generates a lot of overhead. If many selected values need to be communicated, it is better to serialize/de-serialize into/from JSON. In simpler cases, a basic comma separated list of stringified integers also works well. A good database can easily parse/split those back into individual values for use in the query. Your point is also spot on in that there is a limit to how many individual parameters can be in a query. The way these systems work is that they cache earlier generated query plans for reuse. The same query with one ID to be filtered is distinct from one that filters fifty IDs stored in 50 parameters. This can be avoided by basic serialization/deserialization as then there is always just one parameter.
@@georgehelyar What about for example selecting posts with multiple tags. Then obviously you need to do an INNER JOIN for each tag (actually two because presumably you're using a pivot table between posts and tags) and you build the query using a loop for each tag.
Our "database team" changes the database every 2 years - we are now at the 5th attempt and since not all databases supported SQL, we are left with a middleman API that supports only "SELECT * FROM WHERE x". Please don't do this to your teammates!
It's not necessary a bad idea that database can only do 'select * from ... where x'. In fact I think it's good as this way you write more of the application logic within your application's layer which comes with good testing tools - meaning you can easily unit test your application without even having database. On other hand when you start writing complex queries you endup with half of your business logic living in sequel query language which then requires complicated setup to be tested.
We don't use ORM's and Query Builders to avoid writing SQL, we use it to avoid the DRY and string interpolation mess raw SQL creates, there are other benefits as well.
Yeah, even some of the examples of code he flashed in the beginning are clearly a better alternative to writing SQL in the code, specially with messy string interpolation or even more messy and fragmented, concatenated strings. Also, do we want devs to have to sanitize inputs themselves too? And that's just the start(although he does get into the pros of ORM at the end) Yeah, knowing SQL is important. But writing all the queries in the code, instancing all the objects manually, no thanks. Also, seems like the guy has only worked on a few ORMs, because his examples are not universally true for all ORMs.
What are you trying to avoid to write? The SQL statement? That SQL statement is hidden behind the small veneer of a heap of string manipulations. You do repeat yourself, you just don't know it, and pretend that it's DRY.
Most of the time you can just abstract away the sql yourself by writing the sql in a function so you have the upsides of an orm and the upsides of manual sql
A student in the Web application security course I used to teach was doing a SELECT * and then writing all kinds of looped conditions to filter out the data he actually wanted, running into all kinds of bugs and I said "if only there were some sort of domain specific language specifically designed to do this." SQL really is not hard and it is amazing what people will do to try to avoid learning literally anything.
@@peteschaefer no one forces anyone to use an orm and for some problems sometimes it might make sense. We weren't using an ORM in that class. I don' t know if I'd accuse them of being lazy a lot of it is risk aversion. They aren't confident in doing anything other than copy pasta no matter how many times it fails and how many times you specifically tell them to stop.
I'm glad we use EF Core. It's very well thought-out and mostly prevents you from shooting yourself in the foot. But I'd always want developers to first understand SQL before they write LINQ.
That will do no good, you clearly have no idea how much overhead you have. With an ORM there is no way to tune a query proper and tuning most of the time and with little effort will bring a 2-5x in speed benefits. And hundreds of times faster is not rare either. And complicated data processing in SQL can be broken up in steps and use temp tables and clever transformation tricks and the like. With an ORM you are very limited to single query scaling and doing very basic stuff in a bad way. Any company serious about their data and processes does not use an ORM (if they want to survive that is).
@@TheEVEInspiration I can tell you don't use an ORM. I do, and have 100,000 users worldwide running on currently 3 sql db's with Entity Framework and I seem to still be alive with fab performance.
@@judgewest2000 I have used ORMs since the 90s and I also worked with EF (= Hell on Earth). Besides that I work daily with a basic abstraction for CRUD operations for simple data-entry stuff. But when it comes to processing data in volumes, like any reasonably relevant company does, SQL is king and ORMs in all shape and forms suck. And I can tell you do not know SQL very well. It is far more than just simple selects/joins/updates and deletes that ORMs can handle in limited fashion.
@@TheEVEInspirationI' litterally used to tune EF queries at my old job, you can absolutely do it.Plus of it really matters you can write specific queries in sql in the framework and have it still handle things like turn it into the object for you. ORMs are just a tool, you don't use the, for everything, batch complex data processing you are better off with stored procedures. If you are tuning all your queries you are just wasting time, like any other optimisation profile then determine where it makes sense to spend the effort, its often not where you think.
Django's inbuilt orm does the queries lazy and definitely has literally every feature he said orms don't. Migrations are actually so smooth. Allows transaction, and locking too. Allows prefetching if you dug yourself into a relational pit.
My problem with ORM is that essentially they limit your structures to be database-like. So you never get to work with the most convenient structures to solve business problem instead you always get to work with these models that have sqlish limitations. I like the basic idea describes in ddd literature of defining business logic through plain objects that are structured to be optimal to solve business problem as opposed to be optimal for sql model. Then you implement bespoke save() and get() that deals with it and translate it to efficient sql representation. But for simple application this approach does indeed take a bit too much boilerplate compared to what you can do with something like django models.
Django ORM is trash. Specially if you need to deal with a database that was created before your django project. Also, it does not support SQL Server (WTF???), and even using with supported databases, that ORM does not have the flexibility to work well with relational databases. Unfortunately, django does not work so well without this trash, because almost everything need to be based on models.
@@david23627 I disagree. I can do evething I mention even without Django. Try to work with Django + SQL Server or to do Django stuff without django-orm and you'll know what I'm talking about.
The problem is they have been claiming for decade that their database is ACID but it's not. Check the Jepsen test if you don't know about it. They test database. With that in mind Mongo is good to do proof of concepts, MVP, or to store things you are fine to lose or where relations break. But if you count on to have a rock solid ACID relational database it's not.
A pattern I have heard about in C# is Dapper for queries and Entity Framework for inserts and updates. Although, personally, when I talk to a database, I like to use SQL, and then map it to some struct or class using Dapper.
Just added a comment. I think that's the way. EF/ORM for inserts/updates or just getting an object by ID. But use Dapper with SQL to do any custom selects.
I've used Djangos ORM extensively at this point. For simple queries the code is simple. Where complexity is needed, it is possible. Can't complain, and the examples in the video seem like petty strawman arguments.
Django is an exception rather than the rule. The Django ORM is really nice, and it covers common use cases. If you want a complex query that Django cannot produce, you use raw SQL. SQLAlchmy, on the other hand, lets you build your own monstrosity of an ORM with all the features you want. I'm not saying SQLAlchemy is terrible, but the fact it's so flexible leads devs into a rabbit hole when using raw SQL is a better solution.
Maybe these arguments are more convincing in the wild west of JS ORMs. But in C#, Go, and Java land the ORMs are much more mature and have great adjustability on both in memory options and external query building options when querying. Migrations, sanitization, scaling with dev count, all are good reasons in these languages to be using an ORM too.
I went from EF in C# to SqlAlchemy in Python and was shocked at how much more work SqlAlchemy makes you do to make the same results and how much more painful it is tomworkmeith.. EF can infer the database from the objects. SqlAlchmey makes you tell it both. As someone who did raw sql in their early career then switched to orms I didn't understand the dislike but quality of the tool makes such a difference.
Relational database engines are far more optimized than your application depending on an ORM could ever be. I think most devs choose an ORM either out of convenience or they don't choose to segregate their data manipulation statements and query statements. Queries are easily exposed to applications by constructing views in the db. Commands are implemented as any other language does-- a procedure.
True. You can't do anything even remotely complicated with orms. People who use them most likely don't need anything more than one join. There is the reason why stuff like functions, procedures and triggers exists. Because life is not as simple as select * from table smh
ORM can get your object from one of its caches much faster than you can query a DB. If all your writes and reads go through ORM, it knows how do you use each table and adjusts accordingly. It can do instant repeatable reads because it knows the data wasn't modified, and can query db for long term and complex things. With DB you have to always keep in mind that every query is costly and you usually cache some data yourself in some way, which can get very complex if your app is complex. Good ORMs make that easier
Migrations for me are the #1 reason to use ORMs. Raw dog migration scripts is a serious pain. And being able to build your DB objects as code is super nice when things are changing a lot. And can't all ORMs execute raw sql? Anything complicated, I get it. ORMs can be a pain. But for most simple things in projects that change a lot, ORMs are amazing.
Most databases live a lot longer than the applications. Some databases are even used by multiple applications, you see where I am going with this? Defining and maintaining a model using a type system dictated in code is just a recipe for disaster, an accident waiting to happen. And it is more work to maintain too (not to mention the many limitations that approach has).
The same issue applies to ORM migrations though - it can do the basics easily enough, but anything even remotely complicated will have the ORM either produce horrible, inefficient, error prone SQL or just be outright impossible and you'll need to fall back to raw SQL to get the job done.
@@TheEVEInspiration you are basically talking about long term data sets. They might outlive also particular database system as they may be exported from one to another essentially moving through different applications. More typically as software engineers when we talk about database we're talking about the one that stores application's runtime information and it is typically architected not with long-term-storage idea in mind but instead with application's runtime performance in mind thus structure is not necessary best for it to be kept for long term. And situations where applications share same database instead I'd invite to considered it from a little bit different angle: in such case your database is THE application that exposes SQL interface. In context of building applications - database's primary goal is abstraction for storing data from hardware level details.
@@TheEVEInspiration Can't speak to "Most databases live a lot longer than the applications." or "databases are even used by multiple applications". In microservices and SOA, everything you describe seems like an anti-pattern. If your application doesn't outlast the DB, then you would migrate. If you have many applications accessing the same DB, I think ORMs are going to get in the way and for sure are not the solution. For simpler things though, ORMs are amazing IMO. Guess the real answer is "it depends"...
right off the bat he says ORMs are a attempt to make SQL obsolete and/or to avoid learning SQL, i mean if youre gonna shit on something you dont like at least be true to it.
What i do is. I know sql I create the base myself I use orm for simple querys, always adding fields from select. And if we need complex query. Go full raw query
Orms can often more conveniently generate a db for you and handle migrations. Doesn't apply to complex cases, but when you're repeatedly doing crud it can help a lot to keep the layers consistent automatically with just one source code to worry about
In Java there is Spring Data JPA (Hibernate with extra steps), that allows you to write raw ("native") queries if you want; and Spring Data JDBC, that just does the basic CRUD operations like an ORM, but requires you to write the SQL, if you want anything more complicated. I use the first one at work, but the second one is obviously the right choice 80% of the time.
Other than just pure SQL, the only thing i used was laravel's eloquent, a query builder. All the flexibility of SQL with an abstraction that saves you from having to deal with the raw string output. Also the migrations are alright.
I like ORM in Laravel. Migrations let you play with design much easier than raw SQL, you get some nice bells and whistles without extra effort, querying is easy and testable and when you need it you can whip out SQL... And it handles sanitization for you... And learning curve is not so steep 10/10 would try again
fun fact: If you do in Germany an apprenticeship for an IT specialist (no matter what subfield), you don't even have the option to not learn SQL since it's part of the final exam. One part is literally: Here is a database definition, here is a sheet of the SQL syntax (think of it like a formulary), now write a "few" SQL statements by hand (on a sheet of paper ofc). Depending on which subfield you are, the SQL part can be up to a third of your total points.
@@lolikpof apprenticeship is not the same as internship. Internship is the American word for free work and apprenticeship is the word you use when you get money to :) xD
Sqlalchemy (in ORM mode) does all the things the video says ORMs don't, with relatively readable code. It also allows you to raw dog SQL if you need to. It has a lot of issues still, but the video seems to show ignorance on the subject.
Same for GORM (for Go), Hibernate (Java) and also Diesel (Rust). In fact, Java Hibernate's Criteria Queries are very powerful (thought not as good as LINQ) you can do almost everything you can do with SQL without needing to work with Raw SQL Strings.
As an accountant (with some dev work too) who came to this by doing above and beyond coding within accounting tools, I can say for most a thorough study of SQL is far easier than any other language, including stuff like Python, VBScript, or PowerShell. The problem I see (and other dev I worked with seem to agree) is that CompSci education tends to produce people who are not used to thinking in Set Based thought. They tend to understand algorithms, and language basics, but will think of loop oriented ways of handling things and some cannot easily switch paradigms in their mind. In SQL the DBMS does all the underlying looping, and you have only to create efficient Schema design, and execute proper SQL and the sets come out and can be handed off. DB objects can be designed to work around sets as the lingua franca and how they intercommunicate, so at a reasonable level of SQL design much can be done there but the designer has to think in "set" oriented thought. The Impedance thing is real, and probably comes from how SQL originated long before OOP was even well established. This is 1960's DB language design smacking against 1980's stuff.
I think you are right. I actually saw non-IT, not-programer person to learn SQL. When I was explaining it to her, I didn't belive she would understand what am I telling her. But to my suprise, she persisted and now she is rawdogging SELECTs with multiple JOINs lika a madman.
@@gdwe1831 SQL is also a much simpler and higher-level paradigm. It wasn't too long ago that SQL was considered for the laypeople. Accountants, secretaries, the like - and programming was for the technical people. SQL is unbelievably concise and high-level. A few lines of SQL can easily be 100 likes of Java or C#, in a traditional iterative algorithm.
I tested a few of his examples in EF Core and none of them did what he said they would. In the inheritance example EF created one shared table with a column called "discriminator" that stored the class name, and created nullable columns for child properties. It also easily generated NOT LIKE and COUNT statements from LINQ, and if you use a LINQ select statement in the query the generated SQL only fetches the columns containing those properties. ORMs definitely have their downsides, but many of the arguments in this video don't apply to modern ORMs.
The company I work for is obsessed with pre-planning for massive success. It causes so many problems that I'm convinced it's single-handedly preventing us from being successful.
In my current job we mostly use stored procedures and just use the ORM to do the actual relation between the result of the stored procedure and the entity that represents it. I have to say that almost any query we do is kind of complex, even the simplest ones requires some inner joins, and we found this is the best solution for us
The issue is ORM creates Anti Patterns from a relations database point of a view. I have been saying this for years. Databases migration and roll back can be separate tools from the ORM.
I've never had great experiences with ORMs. My favorite method was in a prior company where we created stored procedures for every non-CRUD (or performance-critical CRUD function), and then had a simple tool to auto-generate all the SQL functions in the primary language (C#).
19:20 he didn’t destroy the second half of the vid… all his points were surface level deep, but when you dive in with any rational thinking you realise his fears are based on misunderstandings of what ORMs really are and how they should be used.
@@benjamismo I know right! Honestly, whenever I'm working with python and databases (which isn't all too often unfortunately), I love using it for my projects.
Ages ago I wrote what could be considered a micro ORM, but it mostly just kept metadata about some datatype mappings that I would like to happen automatically. It still expected you to write the SQL yourself, it just cleaned up the row interface to remove some boilerplate.
So it's like JDBI or JOOQ. Working with those is wonderful: type-safety and automatic type conversions, while being able to use all of the expressivity of Postgres SQL.
@@carlerikkopseng7172 Well it was PHP, so type safety is a strong word. It had a schema annotated with user provided type information, and it would make sure these were applied to your queries. It could also do semi-automatic database migration (the actual reason it was written). After writing the database migration code I noticed some of it could be reused and turned into a basic ORM and some CRUD helpers. It was flyway before flyway existed with some additional query convenience stuff.
I have my own micro-orm and it works great for me. It's API mostly feels like a "document store", where optional joins are automatically made and constructing objects from a 2D table into complex object is done by the library, but also allows for extremely performant raw-dogging (feels like Dapper) and very easy-to-use transactions, it has the full spectrum of possibility that I need
I never really thought of ORMs as a way to avoid knowing SQL. It just reduces duplicate code. I worked on a project where there was no ORM and we had to hand write a custom method implementation and some SQL for every database interaction. Eventually I made my own ORM like thing that made it so that as long as you defined special type up front, you could use it as a class and there was a generic table class that automatically worked with these types. Then I only wrote SQL when doing joins or things that didn't run fast enough. But it was really useful for selecting all the data from a table in order to create a full fledged version of a model class and then to just be able to call .save() or .update() on it later. Actually I had a lot of generic classes that knew how to work with these types and automatically worked with anything you through at it, not just for database interaction. So it would define which tables got backed up, what needed to be synced and how, etc. Working in this project became way more productive after this and there were no downsides to the ORM for me.
Please don't take a offense, but this rang alarm bells in my head from my Tech Lead days. I hope you were either the Tech Lead on that project or got their approval. Working together you might have come up with something better that the whole team could use. I personally believe in writing your code with the belief that the person that will have to maintain it after you is a homicidal maniac who knows where you live. Write code differently to the rest of the team and that's a whole other thing that the maintainer has to figure out.
@@AussieAmigan You seem to be way more concerned about the potential for harmful effects than the potential for beneficial ones, to the point you are going out of your way to reply to a RUclips comment about a project you know almost nothing about, because I didn't provide much detail, hoping I didn't go about making the positive change I mentioned in a bad way such that it actually did more harm than good. Of course it is always possible for someone to do that, but this is not a healthy response to hearing about someone make positive changes to a project they worked on. I made these changes many years ago, and still to this day, I can assure you the code/changes I'm referring to is the best code I have ever written in my about 20 years of software development. I had been the tech lead on that project for a while, and by the time I made those changes, I was the only programmer still working on it. I'm still chasing the dream of having an entire system implemented with this approach. There would be declarative types that provide lots of metadata and specification and rules, and an engine that honors these for the entire system.
A good middle ground solution is CQRS and DDD. Model your domain aggregates correctly. Have a simple repository abstraction for commands (add, get by id, delete) that only return aggregate roots (the orm usually handles updates pretty well). And then for the complicated queries, which are usually views or reports, have a different view repository that runs raw SQL under the hood and returns the data you need.
We are using sqlalchemy with postgresql on a relatively big flask project, and as far as I know, never had any problem writing complex queries using the ORM functions, sometimes we use raw SQL but thats only when we know the query is gonna be REALLY big so its just easier for us to start writing the SQL as we have more experience on it and its not worth bothering about translating it to ORM functions unless there is nothing more important to do (there is always something more important than refactoring queries that already work and hardly ever need to get modified).
You can have table-per-hierarchy in almost all ORMs I've ever come across. In fact that's usually the default. Table-per-concrete class is pretty rare.
On the select part yeah, ActiveRecord can select individual fields but it will instanciate the entire model anyway with the non selected fields set to nil. This can lead to misunderstandings (frontend guys, was the field nil cause it wasn't selected or what?), different serializers for the same resource, all fields might be optional in the frontend model but required when updating the backend...
Using Drizzle I have run into 0 of the problems. The Drizzle select syntax is already very close to regular SQL, just with great auto complete, end-2-end type safety and sanitization. And whenever a particular query gets too complex or needs specific features that are not implemented, I can use raw SQL inside my drizzle query, wherever I like (anywhere between 0-100% raw sql). And even with raw SQL I can still get easy type safety and sanitization. (I did look at a lot of ORMs and chose Drizzle specifically for how close it stays to raw SQL in the first place. My second option would have been something like query builder. But I really wanted migrations) Besides building a mid sized DB, I also have to migrate a 8 year old mySql db into postgres, and again the entire setup is super convenient with drizzle (the migration part is mostly raw sql for date conversions, but the setup and db connection is still drizzle). I did make a point out of re-learning raw SQL before starting my current project, though. That has helped immensely in really understanding what is going on.
Doing joins at the application isn't necessarily bad. For many to many relations, doing the join in the DB would still take time AND then send magnitudes more data OTA. Meanwhile the in memory join makes the transfer far smaller.
I think an important point in the video is that the ORM created extra tables, but without it, the data could have existed on one table and a join would not even be necessary
good luck debugging, extending, migrating, writing your own custom serializers, wrappers around SQL, upgrading, any other sort of maintaining pure SQL. ORMs are for simple queries, sqlbuilders and verifiers/generators are made to simplify writing SQL, and raw SQL is for small, mostly fetching, maintainable pieces, where it is 100% unavoidable
You are so wrong, it is not even funny. Data processing should be done in a data processing language and as close to the data as possible (aka Database). And business programs are mostly about data and lengthy processes, not your basic CRUD, that is just a small part. Meaning most processing requires non-trivial SQL that can never be abstracted as they cannot be expressed in other languages.
@@TheEVEInspiration sql is not the fastest way to process data in really complex queries with custom nested transactions, data regrouping, transformations, schema validation, and so on, just look at pl/pgsql benchmarks, it's slower than python, and complex processing will be the limiting factor since it's not just fetching, updating or inserting data after all. And I'm not even trying to mention how horrible that mess is to support through various changes, and to integrate with types and everything.
@@ac130kz SQL is the fastest way to process data in a relational DB, period. Sure application logic should not be in SQL. But fetching, aggregating, filtering, regrouping, and validation will ALWAYS be faster in SQL, even if simply by rules of data locality. Those benchmarks are lies, as they don't take into account network time, query planning time, etc. If I want to aggregate data from a DB for a report, it will be fastest in SQL. And it won't even be close. Avoiding SQL is fine, but as soon as you're getting data from multiple tables, or doing grouping, or anything of that nature, it should be in SQL.
19:00 Yeah, people that use ORM to not depend on the database are now depending on the ORM. If you want to create a product that really doesnt depends on some infraestruture compoment, the real way to go is with a domain centric archtecture like hexagonal archtecture or clean archtecture.
My new favorite channel. Spicy takes with rich justifications. I'm with you on translating queries you've already composed to ORMs can be difficult. My first Python project years ago was also my first ORM project, because every resource I found on connecting it to a database demanded an ORM. I understood the concept of the ORM, but I hated every second of troubleshooting it.
Where I messed up with ORM was automagically making certain things happen. Those magic things were great until someone wanted something JUST A LITTLE Different and now I have "ignore_dependency" options which I have to carefully make sure don't open holes...
Laravels Eloquent Builder and DB class can do basically anything the guy said ORM can't do. Model::query()->select('field1', 'field2')->whereNot('field3', 'like', $variable)->get(); try { DB::transaction(function() { // your code }); } catch (Throwable $e) { return "Database transaction failed" }
Everything mentioned in this video stating not possible or hard to achieve using ORM is not true and can be done. I would say it's a skill issue rather than ORM issue.
Since the guy is clearly a scandi, a scandi term for a favour you do that makes things worse because you didn't think things through is a bear's favour or a bear favour. (Based on the g->y translation in yenneral, I think he's Swedish, so he'd say björntjänst.) Using an ORM is doing yourself a bear favour.
LINQ converts everything into SQL using its Lambda/Expression tree reflection class. Function are attempted to be turned into LINQ unless it is turned into a list first.
The project I'm working on right now uses Sequelize. I jumped into a full stack role after working as a frontend dev for some years so both Sequelize and raw SQL were pretty new to me. At the start I had a lot of difficulty making some more complex queries performant using Sequelize and I kept going through the docs and trying to understand it better, but then even the docs themselves tell you to just use raw SQL if things are difficult to query using their standard syntax. Luckily it's very easy to just run some raw SQL with Sequelize, but the point is it didn't take very long at all for me to see where the pitfalls of using an ORM are. I still prefer it to just having no ORM at all because 90% of queries are perfectly fine with it, but it would be a nightmare if it didn't allow you to easily run a raw query.
I wrote an ORM package this summer. I have a few more small things to add, but it has a lot of tools and features (and 402 unit tests and 4 integration tests). Each model (which can be generated by the CLI tool given a --columns name=type,... parameter) has a `query` method that returns a query builder scoped to the table, and that query builder has a `to_sql` method that returns the generated sql string with parameters interpolated. It also includes a migration system and can generate migrations from models, and you can execute raw SQL if you need to. Once I finish combining it with my CRDTs package, I'll have a package that allows people to use sqlite as their main db, and it will synchronize in the background with strong eventual consistency. The theory is sound, and the individual components work and are pretty thoroughly tested, so it should work.
Using ORM to abstract your database is stupid. ORM for me is always about its tools. Migrations, query builder, declarative approach using annotations, decorators, attributes, built in code generators, debug tools, etc. And when you need a little bit of raw SQL no one can stop you from using it!
In some circumstances, migrations are not just not great, but can be downright terrible. I worked at a place where we built air-gapped pieces of hardware that had databases on them, and updates were infrequent. Given enough time, you'll eventually make multiple complex changes to the database (things that require rebuilding clustered primary keys, etc). If you use a migration pattern, then when you get around to updating one of these systems your series of migrations will take an insane amount of time because it's building and rebuilding indexes, adding columns only to then drop them, etc. A schema comparison/upgrade tool that utilizes backups to rollback is just plainly more performant in that case. But of course, at that job we also rolled our own separate migration tool in order to combine the two approaches, because sometimes you do need to execute queries after making certain schema changes! We also rolled our own dynamic query system that would allow you to use a stored proc to run different versions of the same query... thinking back on it, we were kind of database monsters over there.
If you use the right abstraction, design your data well and avoid using raw SQL in your application code then you can actually test your code without needing to spin up a real database every time. You can also add and tune optimisations at your abstraction layer without rewriting application code and then in 10 years time when your database outgrows your needs (or starts costing $$$) you have a chance of replacing it with something different, even a NoSQL data store, without having to rewrite 10 years worth of legacy data access code.
I mean shitting on ORMs without even showing laravel's eloquent pffft. It's super convenient to have abstracted tables into models and have the relations, scopes etc. there for autocompletion. Having to always go through database to see the structures or having it all in memory to write some code has to suck.
I'm gonna be honest, I don't think the majority of people use ORMs to "avoid learning SQL". I think most of us know sql and either it's just what is being used at work or it's just a little bit more convenient. I'm sure you can find a few people who actually use ORMs to avoid learning SQL but I really don't believe it's the majority.
At 0:45 the laravel example used the query builder so idk what is wrong with that example. Try to add conditionals to a normal query string and you will get an even uglier output.
At 2:45 that example is folly, in most ORM's i used you can have different models that reference the same table with specific conditions for querying. Coming back to Laravel, you just create a queryScope to say that GraduateStudent ( as an example ) is from the table "persons" and always applies the filter "is_graduate = true" ( again, as an example ). Which imo is better then always remembering to add the condition or having a repository that you need to use to fetch the correct response.
12:30 Or you use a good ORM, a good one should allow you to dump the SQL generated and the params it will bind. Easy to see the query, easy to try it yourself, easy to optimize.
14:40 - again, ORM's that respect themselves have this, drizzle for Node as an example, Eloquent for laravel, both can run with transactions, the same as in raw SQL, you need to optimize the query. You can have the same bug in both as easy.
16:35 well, not if you have tests. With a ORM or Query Builder you can simply a SQLite database for testing, which is fast and requires no additional setup and you can have the real big deal database for testing. Migrating to a new database based on requirements or working with multiple also works. At work currently we have 2 databases, one legacy one that uses mysql and a new one that uses postgresql, we use Eloquent for both and to justify which is which we just have a `protected string $connection` property on the models. This makes it more readable for us as we know which db we use and makes it easier to work with as we write the same syntax.
In the end, i think this guy might need to venture down in some good ORMs, from my experience, most Active Record ORMs are very good, when you use them you feel like writing SQL by hand is a huge downgrade as they do just that, give you 100% control where you want to have 100% control and they can be integrated with multiple databases at once ( for testing as an example, or for a small staging website where a SQLite database on the local filesystem is just fine ). If even Active Record is too much for you, go with a query builder, if a query builder does something bad you already know that's you not the query builder and you would have done the same mistake raw dogging squel
Honestly, spring data did it right. It provides just enough abstraction and CRUD utilities to get you going quick but at the same time you can create methods to run arbitrary SQL. Just perfect ❤
I'm on dotnet team, but I agree - saying that "ORMs are bad" and showing some JS stuff is stupid - EF Core is one the most epic pieces of tech I know of. You use the db table abstractions just like normal lists - you add, remove, use LINQ (like Where(x => x.Name.Lenght > 5) ), and they get translated to SQL. If they can't be translated you can easily either just pull the objects and do the thing in C#, or you can just throw in some raw sql. I almost said "it's perfect" - of course it's not. But having to also write some complex SQL dependend on some "SearchCriteria" object - I take EF Core all the time and fall back to raw dogging when needed
17:30 nooo… an ORM shouldn’t be chosen so you can switch database tech, instead chosen to be a client written in your language of choice to manage your persisted data. Just don’t use raw SQL in code. If the db schema changes, you’d have no IDE support to help you change all the places that may need changing. You and your team will likely get to a point where you’re fearful of changing the db schema, which is bad. The exception to this should be when you abstract the SQL behind a stored proc, the SQL of the stored proc should of course be managed by the migrations, and therefore should be in the code… but the calls to the stored proc should be done via an ORM.
The "don't abstract things because by the time you really need to you'll have enough money to hire 1000 people to do it" is great for the 3% of devs who are startup founding members. The rest of us are one of the 1000 hired to fix things.
0:01 oh dude… ORMs aren’t trying to make SQL obsolete… this is gunna be painful. ORMs are no different from using a client written in your favourite language to speak to an API written in another language. So if you have a problem with ORMs, you have a problem with any type of system to system integration.
I do agree with base premise that orms can abstract too much and nake things more complex. Especially if done incorrectly like his examples. All these seem like skill-issues. Things like activerecord has count, ability to select out individual fields (not doing select *), eager load data to avoid multiple queries and joins, has single table inheritance if wanted, can even raw dog the sql and let it load into your models, transactions, even nested transactions if wanted, etc. This is why we should look around at other ecosystems, js, php, python, cpp, java, c#, lisp, etc. Gets you broader view of whats possible and already commonly solved elsewhere.
Agree with you, they claim ORM is bad, but they don't seem to conduct enough research to say for sure, and already making video like they know whole universe... that's the reason why learning something on youtube is dangerous, people here usually is actors - not specialists
Honestly, this is just a bad take. Most people use ORM-s because they just don't want to maintain insert/update statements for 100+ tables that change relatively frequently, handle optimistic locking by hand for every single update or map results of selects manually to application structs or even worse - use the result types provided by the driver. And saying things like people use ORM-s because they don't know SQL is absolutely not true, at least in my experience, we just prefer to use our SQL knowledge when the tool is not smart enough to generate a specific query or hand-crafting it would be significantly more efficient. Most ORMs will allow you to do that and have no problem with mixing raw SQL with framework-provided methods and generally also automatically map the results of your query to application structs, at least with rails, c#, and hibernate/spring data that's the case, not sure how things are in js-land
ORMs work great for saving data, that's where the cheese is. For querying, unless you are looking to select an ORM object by primary key, you are best off using SQL. The example about inheritance is table-per-type. Sometimes that's appropriate, but usually table-per-hierarchy works better. Entity Framework now defaults to TPH.
This is completely orthogonal to the point here, but I'm 9 months into my first job as a data engineer and have completely fallen for Data Build Tool (DBT) which uses Jinja templating. It's very similar to the 3rd approach mentioned in the video but a lot less concerning. The idea is simply to allow code in the middle of your SQL query that compiles in-place into the corresponding SQL. For example: select * from Person where {{ is_admin() }} might compile to: select * from Person where account_type = 1 Nothing crazy. No "manipulate this so that it hides a potential cross join". Just "put code right here". And surprisingly it solves a lot of the pain points I have with raw SQL by keeping it SQL but still enabling a lot of flexibility. I will say however that DBT is mainly intended for data engineering/analytics, not for 1-off queries like "who is this specific user?".
The main function of an ORM is to maintain an identity map of model objects vs rows in the database. About 99% of hard to fix problems with DB usage comes from stale data. You can roll your own identity map and that's fine too, it's way less work than writing your own ORM. The only other use is just having a nice type-checked wrapper around raw strings. But tbh that part is really not as useful and it also can be incredibly slow. A good ORM will have those elements modular so it's not all or nothing. A good example of modular ORM design is SQLAlchemy
If you have a good understanding of SQL and understand the trade offs of whichever ORM you are using, use it if it's a net positive to your project. All the 'haha gotcha ORM' arguments presented are all very easy to understand to someone familiar with SQL and are simple to deal with. Index creation, migrations, cartesian explosion, etc... Also using an ORM does not mean you cannot use RAW SQL anywhere in your project. A deep understanding of the tools you use is very unlikely to lead you to shooting yourself in the foot.
Stop talking about the only reason people use orm is that they trying to avoid learning sql. Thsts a bullshit. Personally i use ORM beacause of default security against most stupid sql ijections, because of great maintainability of your queries, if someone changes a model its obvious where are those queries that you should change accordingly. I simply dont wat to maintain dozens of dead texts as queries. I like migrations and all that stuff. I also like dynamic composability of a query. You just can build it up as an object, thats super nice. I wont trade all that stuff for a pseudo simplicity. No way.
"most stupid sql ijections" It is super easy to parameterize SQL when using a micro ORM. And you can always use a RegEx pattern to detect and block suspect SQL before it is executed. That is absolutely not a valid reason to unleash hell on your developers and company/customers.
@@TheEVEInspiration LOL. What composability? SQL is still a joke, if it would be good we wouldn't have these many ORMs. People unfortunately switched to Node.js and they haven't seen a good ORM and now they are trying to gaslight everyone. I have seen too many page-long SQL queries with the same parts duplicated over and over. I want real variables, functions, separation of concerns. SQL forces you to abandon 50 years of progress in compsci.
I think a lot of the time it's not orm bad, it's orm being poorly documented, people not willing to dive deep into an orm that they might replace tomorrow, people believing and spreading misinfo that orm can't do something etc.
16:07 migration scripts should be maintained and refractored, same as with regular code. I'm in favour of regularly compressing the scripts down to represent the current desired database schema over endlessly adding delta scripts - you just need to be sure the scripts you're refactoring have been fully released through to all environments. Generally that means having a base layer of "create if not exists table index / etc" and "create+alter view / procedure" idempotent scripts that you backport the changes into. These can even be automatically generated using the production schema
`if not exist`... how it is possible that anything got changed before migration script had changed it, "just make sure" that is another huge problem that is not simple at any scale, except one person project maybe. There is no need to maintain migration scripts, when migration is in production - delete it - why would you ever run it again except for immediate rollback? The current schema is in version control regenerated when running migrations (basically `pgdump ---schema-only`). ORM handles all of this. When this kind of approach stops working, most likely there is no readymade solution anyway.
idk, most examples showed if not all popular ORM libraries handle easily. this looks like a video from someone who either has not used an ORM in 15 years or is just trying to push bs
I always think in raw sql first then translate them to orm to write them in code. That translate to orm is just an extra step of thinking, not really needed, instead of writing the sql directly as string. The only advantage of ORM that I see is if you need to change database server at some point in time. E.g. mysql to prostgress or any other.
The big advantage I feel from an ORM over raw SQL is automatic translation into a model object. Actually, not the biggest advantage... the only advantage. My ideal ORM would be one that lets me write raw SQL and translate the results into objects, and does literally nothing else.
No interest in a .save() method or getters for derived properties, such as getUrl() where maybe only a slug is stored in db & the url depends upon some environment config? Personally, i love SQL, but i need some convenience features.
It's just trading writing a SQL string for a library that abstracts it into somewhat unreliable function chaining that builds a string which you then have to debug.
Jetbrains Exposed lets you do two things: - Choose between SQL DSL and DAO - Requires explicit transactions for writing data. You surround your code with transaction { } and it does the rest.
What i see, that most programms implement ORMs in a way that queries the Database all the Time Row per Row where you get like 10k queries that actually could be 1-5 queries :)
16:24 Sounds like this is a good example of where the YAGNI (you ain't gonna need it) principle comes into play? Making consequential and often costly choices on the basis of hypothetical future scenarios that may never come to pass (and on the off chance that they do, you could deal with it then, and you may be overestimating how much this would actually help you)... Is often a waste of time.
I always found the biggest gains from an ORM has been the database agnostic error handling and input sanitisation. No one wants to be the person who is responsible for the avoidable injection attack or dealing with each database driver's different set of errors...
I used a handful of ORMs for years, but they only gave me -two- three things: 1. The ability to call the database in code 2. Built-in sanity checks / input validation. 3. Compile-time errors via serializing the database as structs in code The rest were eventually disadvantages. And I think it's way more powerful to generate functions based on stored procedures (and basic CRUD for each table).
That is a big assumption, got something to hide as in, never tried to learn SQL? I know both and beyond the most simple CRUD, ORMs are worthless and do nothing but hold you back.
ngl, I kinda feel like it would be cool to have something like lua running on detabase-side so we don't have to deal with the crap that is PL/SQL for stored procedures / functions...
As you say ORMs are fine if you have the flexibility of jumping out of ORMland into writing direct SQL. You should have a decent understanding of how to write performant SQL as well though and also be monitoring the performance in your DB so you can optimise any ORM queries and break them out. And yes absolutely design your DB schema first, build your ORM domain models around it.
In 15 years of the frontend community being enchanted by functional programming, FP has yet to deliver on any promises of building user interfaces in a cleaner or more performant way. OOP is perfect for creating user interfaces. Be careful with the gurus you choose and the dogmas you accept.
@@nandoflorestan I'm not in favor of functional programming. It's useful in niche cases. So is OOP. There's many different kinds of UI, but when it comes to things like web, most of the time it's better to let the interface just be data. Of course most gui frameworks and systems are different from html, but it was also a mistake to force it into OOP. I agree with you totally that you should be careful about what dogma you get caught up in. But OOP+"clean code" according to bobs Bible are some of the worst.
The obvious solution is to write your own ORM using JDSL. You are welcome.
Ask Tom
TOM IS A GENIUS!
Someone should totally write an npm package that wraps JDSL in APEX
JDSL is slowly getting its lore!
Dont bring back the nightmares. Currently working in a team where this contractor created his own golang ORM and we're left maintaining it 😭 like just use one of the many db libraries out there
Well hello there. Appreciate that you took a very charitable take on my video, definitely agree that arguments in the beginning were absolute garbage. I have no idea what I was thinking. Will blame it on being sick for like 2 weeks and just getting blinded from having no interaction with another human in-person, but oh well. In general I try to make people who might not have built that much stuff/have as much computer science background avoid some very easily made mistakes.
Thanks for the point about the intro, always struggle with low retention if I try to go slower, but maybe I should try that again.
Very fun to hear your commentary from this POV, never thought I'd have someone react to a video I've made.
Also, danggggiiit, 8:17, I didn't catch it... Facepalmed so hard when you paused there. that's what I get for not running unit tests :p
Also, your thumbnail is so much better than mine 😢
Still, nice video. It takes boldness put yourself out there.
ActiveRecord doesn't have any of the issues you listed, and supports raw SQL
16:40 In Brasil if your product targets multiple government agencies then abstracting the database might be the cheapest and fastest idea, I've done some projects in that area and agencies always have very specific requirements for ALL tools so you're either lucky or every agency uses a different Database tool.
Este ser não passa frio, pois está coberto de razão
Well, the whole discussion is completely different if you do 'enterprise' on-prem installable products and clients come in like 'we need it to run with oracle', 'it should run on mysql', 'we use postgres exclusively'. Then you'd be really glad you have the abstraction layer in place.
All of that is true.
I’ve also seen the other side of things, where I’m warned not to use Postgres’s built in features because “what if we switch databases?”. I’ve NEVER seen a company ditch Postgres. It’s hard for me to imagine a scenario in which I’d recommend anybody swap one mature RDBMS for a different one. I have seen companies swap web frameworks/ORMs and keep the original database.
@@stevezelaznik5872 Yeah, same here. Companies which build their own platforms (as in not products for others to run) usually do not change their databases. And when they do they need to rewrite stuff anyways.
At the end, it always depend on the needs
@@stevezelaznik5872 We had to switch a monolith from Postgres to Microsoft SQL Server and we'd written all the queries without an ORM. Thankfully, we had a bunch of tests for all the SQL queries (we are super serious about TDD) and we weren't doing much that wasn't ANSI SQL so it wasn't too bad in the end. I still miss Postgres 😢
@@stevezelaznik5872iirc Discord stopped using postgres back in the day. There should be a great article about it
Most of the time I see a video called "Don't use X, use Y" it actually should be named "Learn when to use X, and when to use Y"
Both approaches are good depending on what you're doing
Phenomenal point!
Also that not all ORMs have the issues mentioned.
@@thekwoka4707like?
Basically use ORM if all the things you do are simple, but if you go so complicated as "select all posts tagged X, Y, and Z" (multiple tag filtering), then raw SQL is unavoidable.
Indeed :D
The sweet spot for me are simple query-builders that reflect exactly the syntax of the query under the hood, but save you from the pain of fighting with strings. Primary example is squirrel in GO. Once you have to build a ginormous query using a loop, using raw strings quickly ceases to be fun.
I understand you point of view and there is only one group to blame for it.
The language designers that never seem to understand that dynamic & modular SQL queries must be easily expressible in application code.
It almost always needs to be shoehorned in with a less than optimal syntax using some form of string concatenation (essentially).
Same is true for any other textual language, obviously.
And as expressing other language texts is not part of the syntax, people need to use strings.
From there it means, the editors do not gracefully handle and assist the developer.
And from there people consider ORMs.
And from there, Hell reigns on Earth.
this all day long 🙂👍
If you have to build a query using a loop, you're doing it wrong.
Every query/command should be a compile time constant value.
For example I often see this for bulk inserts with a million parameters, or worse a lot of values added directly into the query, but any RDBMS worth using has a way to batch/bulk insert data without needing this.
@@georgehelyar Dynamically extended/modular queries will never be known at compile time and that is fine.
But you are right that the dynamic part should not be from a loop that just adds the same kind of check over and over. That generates a lot of overhead.
If many selected values need to be communicated, it is better to serialize/de-serialize into/from JSON.
In simpler cases, a basic comma separated list of stringified integers also works well.
A good database can easily parse/split those back into individual values for use in the query.
Your point is also spot on in that there is a limit to how many individual parameters can be in a query. The way these systems work is that they cache earlier generated query plans for reuse.
The same query with one ID to be filtered is distinct from one that filters fifty IDs stored in 50 parameters. This can be avoided by basic serialization/deserialization as then there is always just one parameter.
@@georgehelyar What about for example selecting posts with multiple tags. Then obviously you need to do an INNER JOIN for each tag (actually two because presumably you're using a pivot table between posts and tags) and you build the query using a loop for each tag.
Our "database team" changes the database every 2 years - we are now at the 5th attempt and since not all databases supported SQL, we are left with a middleman API that supports only "SELECT * FROM WHERE x". Please don't do this to your teammates!
Database team creating work for themselves 🧐
@@Thezftwlol
That's insane lol. They should all be fired
Sounds like whoever leads the engineering team/CTO needs to tell the engineers to stop being morons and use ANSI SQL.
It's not necessary a bad idea that database can only do 'select * from ... where x'. In fact I think it's good as this way you write more of the application logic within your application's layer which comes with good testing tools - meaning you can easily unit test your application without even having database. On other hand when you start writing complex queries you endup with half of your business logic living in sequel query language which then requires complicated setup to be tested.
We don't use ORM's and Query Builders to avoid writing SQL, we use it to avoid the DRY and string interpolation mess raw SQL creates, there are other benefits as well.
Yeah, even some of the examples of code he flashed in the beginning are clearly a better alternative to writing SQL in the code, specially with messy string interpolation or even more messy and fragmented, concatenated strings. Also, do we want devs to have to sanitize inputs themselves too? And that's just the start(although he does get into the pros of ORM at the end)
Yeah, knowing SQL is important. But writing all the queries in the code, instancing all the objects manually, no thanks.
Also, seems like the guy has only worked on a few ORMs, because his examples are not universally true for all ORMs.
This exactly, Plus also serialization.
How exactly would using a query builder deduplicate any code?
What are you trying to avoid to write? The SQL statement? That SQL statement is hidden behind the small veneer of a heap of string manipulations. You do repeat yourself, you just don't know it, and pretend that it's DRY.
Most of the time you can just abstract away the sql yourself by writing the sql in a function so you have the upsides of an orm and the upsides of manual sql
A student in the Web application security course I used to teach was doing a SELECT * and then writing all kinds of looped conditions to filter out the data he actually wanted, running into all kinds of bugs and I said "if only there were some sort of domain specific language specifically designed to do this." SQL really is not hard and it is amazing what people will do to try to avoid learning literally anything.
@@peteschaefer no one forces anyone to use an orm and for some problems sometimes it might make sense. We weren't using an ORM in that class. I don' t know if I'd accuse them of being lazy a lot of it is risk aversion. They aren't confident in doing anything other than copy pasta no matter how many times it fails and how many times you specifically tell them to stop.
isThisAcceptablePartner sounds like a passive aggressive cowboy
Or exploring new options for bedroom fun. ;)
I'm glad we use EF Core. It's very well thought-out and mostly prevents you from shooting yourself in the foot. But I'd always want developers to first understand SQL before they write LINQ.
Every time I hear people bitch about ORMs:
* Laughs in EF Core *
Nearly all of these complaints are a consequence of using trash ORMs.
That will do no good, you clearly have no idea how much overhead you have.
With an ORM there is no way to tune a query proper and tuning most of the time and with little effort will bring a 2-5x in speed benefits.
And hundreds of times faster is not rare either.
And complicated data processing in SQL can be broken up in steps and use temp tables and clever transformation tricks and the like.
With an ORM you are very limited to single query scaling and doing very basic stuff in a bad way.
Any company serious about their data and processes does not use an ORM (if they want to survive that is).
@@TheEVEInspiration I can tell you don't use an ORM.
I do, and have 100,000 users worldwide running on currently 3 sql db's with Entity Framework and I seem to still be alive with fab performance.
@@judgewest2000 I have used ORMs since the 90s and I also worked with EF (= Hell on Earth).
Besides that I work daily with a basic abstraction for CRUD operations for simple data-entry stuff.
But when it comes to processing data in volumes, like any reasonably relevant company does, SQL is king and ORMs in all shape and forms suck.
And I can tell you do not know SQL very well.
It is far more than just simple selects/joins/updates and deletes that ORMs can handle in limited fashion.
@@TheEVEInspirationI' litterally used to tune EF queries at my old job, you can absolutely do it.Plus of it really matters you can write specific queries in sql in the framework and have it still handle things like turn it into the object for you.
ORMs are just a tool, you don't use the, for everything, batch complex data processing you are better off with stored procedures.
If you are tuning all your queries you are just wasting time, like any other optimisation profile then determine where it makes sense to spend the effort, its often not where you think.
Django's inbuilt orm does the queries lazy and definitely has literally every feature he said orms don't. Migrations are actually so smooth. Allows transaction, and locking too. Allows prefetching if you dug yourself into a relational pit.
My problem with ORM is that essentially they limit your structures to be database-like. So you never get to work with the most convenient structures to solve business problem instead you always get to work with these models that have sqlish limitations. I like the basic idea describes in ddd literature of defining business logic through plain objects that are structured to be optimal to solve business problem as opposed to be optimal for sql model. Then you implement bespoke save() and get() that deals with it and translate it to efficient sql representation. But for simple application this approach does indeed take a bit too much boilerplate compared to what you can do with something like django models.
Django ORM is trash. Specially if you need to deal with a database that was created before your django project. Also, it does not support SQL Server (WTF???), and even using with supported databases, that ORM does not have the flexibility to work well with relational databases. Unfortunately, django does not work so well without this trash, because almost everything need to be based on models.
@@henriquegomesnunes8184 idk man sounds like some sort of skill issue on your part
@@david23627 I disagree. I can do evething I mention even without Django. Try to work with Django + SQL Server or to do Django stuff without django-orm and you'll know what I'm talking about.
The problem is they have been claiming for decade that their database is ACID but it's not. Check the Jepsen test if you don't know about it. They test database.
With that in mind Mongo is good to do proof of concepts, MVP, or to store things you are fine to lose or where relations break. But if you count on to have a rock solid ACID relational database it's not.
A pattern I have heard about in C# is Dapper for queries and Entity Framework for inserts and updates. Although, personally, when I talk to a database, I like to use SQL, and then map it to some struct or class using Dapper.
I do the same thing. Dapper mapping is very handy.
Now it's EF core for everything.
Just added a comment. I think that's the way. EF/ORM for inserts/updates or just getting an object by ID. But use Dapper with SQL to do any custom selects.
@@minnesotasteveyou can use ef core for custom selects now too
I've used Djangos ORM extensively at this point. For simple queries the code is simple. Where complexity is needed, it is possible. Can't complain, and the examples in the video seem like petty strawman arguments.
Django is an exception rather than the rule. The Django ORM is really nice, and it covers common use cases. If you want a complex query that Django cannot produce, you use raw SQL. SQLAlchmy, on the other hand, lets you build your own monstrosity of an ORM with all the features you want. I'm not saying SQLAlchemy is terrible, but the fact it's so flexible leads devs into a rabbit hole when using raw SQL is a better solution.
Django ORM is by far the best ORM out there
@@punkweb Laugs in EF Core
@@petrmalecik5661use both, and believe me Django ORM is by far superior, easy, concise
Maybe these arguments are more convincing in the wild west of JS ORMs. But in C#, Go, and Java land the ORMs are much more mature and have great adjustability on both in memory options and external query building options when querying. Migrations, sanitization, scaling with dev count, all are good reasons in these languages to be using an ORM too.
I went from EF in C# to SqlAlchemy in Python and was shocked at how much more work SqlAlchemy makes you do to make the same results and how much more painful it is tomworkmeith.. EF can infer the database from the objects. SqlAlchmey makes you tell it both. As someone who did raw sql in their early career then switched to orms I didn't understand the dislike but quality of the tool makes such a difference.
@@101MantI'm new to C#, by EF do you mean "Entity Framework"?
@@101Mantat this point sqlalchemy is just a wrapper to run SQL queries for me.
@@anima94 Yes, "EF" in C# speak is "Entity Framework". So, this is MSFT"s ORM styled tooling.
This video was so full of bunk I don't know how the front or back half could be considered good.
I don't understand the ILADIES part. Could you please explain it?
Relational database engines are far more optimized than your application depending on an ORM could ever be. I think most devs choose an ORM either out of convenience or they don't choose to segregate their data manipulation statements and query statements. Queries are easily exposed to applications by constructing views in the db. Commands are implemented as any other language does-- a procedure.
True. You can't do anything even remotely complicated with orms. People who use them most likely don't need anything more than one join. There is the reason why stuff like functions, procedures and triggers exists. Because life is not as simple as select * from table smh
ORM can get your object from one of its caches much faster than you can query a DB. If all your writes and reads go through ORM, it knows how do you use each table and adjusts accordingly. It can do instant repeatable reads because it knows the data wasn't modified, and can query db for long term and complex things. With DB you have to always keep in mind that every query is costly and you usually cache some data yourself in some way, which can get very complex if your app is complex. Good ORMs make that easier
Sounds like you guys have never met a good ORM. But in 20 years using SQLAlchemy, I have never needed to raw dog a query.
@@nandoflorestan what were your typical and most complicated queries?
Migrations for me are the #1 reason to use ORMs. Raw dog migration scripts is a serious pain. And being able to build your DB objects as code is super nice when things are changing a lot. And can't all ORMs execute raw sql? Anything complicated, I get it. ORMs can be a pain. But for most simple things in projects that change a lot, ORMs are amazing.
Most databases live a lot longer than the applications.
Some databases are even used by multiple applications, you see where I am going with this?
Defining and maintaining a model using a type system dictated in code is just a recipe for disaster, an accident waiting to happen.
And it is more work to maintain too (not to mention the many limitations that approach has).
The same issue applies to ORM migrations though - it can do the basics easily enough, but anything even remotely complicated will have the ORM either produce horrible, inefficient, error prone SQL or just be outright impossible and you'll need to fall back to raw SQL to get the job done.
There are standalone rmigration tools
@@TheEVEInspiration you are basically talking about long term data sets. They might outlive also particular database system as they may be exported from one to another essentially moving through different applications. More typically as software engineers when we talk about database we're talking about the one that stores application's runtime information and it is typically architected not with long-term-storage idea in mind but instead with application's runtime performance in mind thus structure is not necessary best for it to be kept for long term. And situations where applications share same database instead I'd invite to considered it from a little bit different angle: in such case your database is THE application that exposes SQL interface. In context of building applications - database's primary goal is abstraction for storing data from hardware level details.
@@TheEVEInspiration Can't speak to "Most databases live a lot longer than the applications." or "databases are even used by multiple applications". In microservices and SOA, everything you describe seems like an anti-pattern. If your application doesn't outlast the DB, then you would migrate. If you have many applications accessing the same DB, I think ORMs are going to get in the way and for sure are not the solution. For simpler things though, ORMs are amazing IMO. Guess the real answer is "it depends"...
right off the bat he says ORMs are a attempt to make SQL obsolete and/or to avoid learning SQL, i mean if youre gonna shit on something you dont like at least be true to it.
Yeah, that was absolute nonsense.
What i do is.
I know sql
I create the base myself
I use orm for simple querys, always adding fields from select.
And if we need complex query. Go full raw query
Orms can often more conveniently generate a db for you and handle migrations. Doesn't apply to complex cases, but when you're repeatedly doing crud it can help a lot to keep the layers consistent automatically with just one source code to worry about
In Java there is Spring Data JPA (Hibernate with extra steps), that allows you to write raw ("native") queries if you want; and Spring Data JDBC, that just does the basic CRUD operations like an ORM, but requires you to write the SQL, if you want anything more complicated. I use the first one at work, but the second one is obviously the right choice 80% of the time.
Java 💀
I find the translation from "native" to native frustrating. Duplicate alias my arse.
Use kotlin with the same library if you don't like Java @@stephen7715
@@stephen7715funny thing the world is built on cpp, java, js and php, funny for you to cringe at it😅
Just use JOOQ. Thank me later.
Other than just pure SQL, the only thing i used was laravel's eloquent, a query builder. All the flexibility of SQL with an abstraction that saves you from having to deal with the raw string output. Also the migrations are alright.
I like ORM in Laravel. Migrations let you play with design much easier than raw SQL, you get some nice bells and whistles without extra effort, querying is easy and testable and when you need it you can whip out SQL... And it handles sanitization for you... And learning curve is not so steep
10/10 would try again
fun fact: If you do in Germany an apprenticeship for an IT specialist (no matter what subfield), you don't even have the option to not learn SQL since it's part of the final exam.
One part is literally: Here is a database definition, here is a sheet of the SQL syntax (think of it like a formulary), now write a "few" SQL statements by hand (on a sheet of paper ofc).
Depending on which subfield you are, the SQL part can be up to a third of your total points.
Why is word "few" in quotation marks? What sort of frightening implication are you suggesting?
How do I get an internship in Germany if I'm not German and I'm not in Germany?
@@BlazingMagpieWriting code by hand is an... **experience**. I would rather type 300 lines of code than write 50.
@@lolikpof apprenticeship is not the same as internship. Internship is the American word for free work and apprenticeship is the word you use when you get money to :) xD
@@IulianAiloae great, so how do I get one of those? 😺 Btw, internships can also be paid. It depends on the company
Sqlalchemy (in ORM mode) does all the things the video says ORMs don't, with relatively readable code. It also allows you to raw dog SQL if you need to.
It has a lot of issues still, but the video seems to show ignorance on the subject.
Entity Framework (with LINQ) does all of this as well. I think this video is a bit misleading...
Same for GORM (for Go), Hibernate (Java) and also Diesel (Rust). In fact, Java Hibernate's Criteria Queries are very powerful (thought not as good as LINQ) you can do almost everything you can do with SQL without needing to work with Raw SQL Strings.
I was looking for this comment, most of the time when i sqlachemy i feel like i'm writing SQL one for one
As an accountant (with some dev work too) who came to this by doing above and beyond coding within accounting tools, I can say for most a thorough study of SQL is far easier than any other language, including stuff like Python, VBScript, or PowerShell. The problem I see (and other dev I worked with seem to agree) is that CompSci education tends to produce people who are not used to thinking in Set Based thought. They tend to understand algorithms, and language basics, but will think of loop oriented ways of handling things and some cannot easily switch paradigms in their mind. In SQL the DBMS does all the underlying looping, and you have only to create efficient Schema design, and execute proper SQL and the sets come out and can be handed off. DB objects can be designed to work around sets as the lingua franca and how they intercommunicate, so at a reasonable level of SQL design much can be done there but the designer has to think in "set" oriented thought. The Impedance thing is real, and probably comes from how SQL originated long before OOP was even well established. This is 1960's DB language design smacking against 1980's stuff.
I think you are right. I actually saw non-IT, not-programer person to learn SQL. When I was explaining it to her, I didn't belive she would understand what am I telling her. But to my suprise, she persisted and now she is rawdogging SELECTs with multiple JOINs lika a madman.
Do CompSci majors actually struggle with SQL?
Right, but we all know how to code. SQL is a very different paradigm.
@@gdwe1831 SQL is also a much simpler and higher-level paradigm. It wasn't too long ago that SQL was considered for the laypeople. Accountants, secretaries, the like - and programming was for the technical people. SQL is unbelievably concise and high-level. A few lines of SQL can easily be 100 likes of Java or C#, in a traditional iterative algorithm.
I tested a few of his examples in EF Core and none of them did what he said they would. In the inheritance example EF created one shared table with a column called "discriminator" that stored the class name, and created nullable columns for child properties. It also easily generated NOT LIKE and COUNT statements from LINQ, and if you use a LINQ select statement in the query the generated SQL only fetches the columns containing those properties.
ORMs definitely have their downsides, but many of the arguments in this video don't apply to modern ORMs.
Design the database first. Then use a query builder that is very close to SQL syntax and sanitizes user input for you.
I like using dotnet entity framework (with the C# syntax not the SQL syntax) it lets me basically just write SQL queries
The company I work for is obsessed with pre-planning for massive success. It causes so many problems that I'm convinced it's single-handedly preventing us from being successful.
In my current job we mostly use stored procedures and just use the ORM to do the actual relation between the result of the stored procedure and the entity that represents it. I have to say that almost any query we do is kind of complex, even the simplest ones requires some inner joins, and we found this is the best solution for us
The issue is ORM creates Anti Patterns from a relations database point of a view. I have been saying this for years. Databases migration and roll back can be separate tools from the ORM.
I've never had great experiences with ORMs. My favorite method was in a prior company where we created stored procedures for every non-CRUD (or performance-critical CRUD function), and then had a simple tool to auto-generate all the SQL functions in the primary language (C#).
19:20 he didn’t destroy the second half of the vid… all his points were surface level deep, but when you dive in with any rational thinking you realise his fears are based on misunderstandings of what ORMs really are and how they should be used.
Django ORM doesn't seem to have most of the issues mentioned inside this video
Most of people here are python hating rust-fanatics, so they wouldn't know. But Django ORM indeed doesn't have any of these problems
@@benjamismo I know right! Honestly, whenever I'm working with python and databases (which isn't all too often unfortunately), I love using it for my projects.
Yeah, all of the problems described in this video, django orm does not have; all the things this video says orms can't do, django orm can do😂
Ages ago I wrote what could be considered a micro ORM, but it mostly just kept metadata about some datatype mappings that I would like to happen automatically. It still expected you to write the SQL yourself, it just cleaned up the row interface to remove some boilerplate.
So it's like JDBI or JOOQ. Working with those is wonderful: type-safety and automatic type conversions, while being able to use all of the expressivity of Postgres SQL.
@@carlerikkopseng7172 Well it was PHP, so type safety is a strong word. It had a schema annotated with user provided type information, and it would make sure these were applied to your queries. It could also do semi-automatic database migration (the actual reason it was written). After writing the database migration code I noticed some of it could be reused and turned into a basic ORM and some CRUD helpers.
It was flyway before flyway existed with some additional query convenience stuff.
I have my own micro-orm and it works great for me. It's API mostly feels like a "document store", where optional joins are automatically made and constructing objects from a 2D table into complex object is done by the library, but also allows for extremely performant raw-dogging (feels like Dapper) and very easy-to-use transactions, it has the full spectrum of possibility that I need
I never really thought of ORMs as a way to avoid knowing SQL. It just reduces duplicate code. I worked on a project where there was no ORM and we had to hand write a custom method implementation and some SQL for every database interaction. Eventually I made my own ORM like thing that made it so that as long as you defined special type up front, you could use it as a class and there was a generic table class that automatically worked with these types. Then I only wrote SQL when doing joins or things that didn't run fast enough. But it was really useful for selecting all the data from a table in order to create a full fledged version of a model class and then to just be able to call .save() or .update() on it later. Actually I had a lot of generic classes that knew how to work with these types and automatically worked with anything you through at it, not just for database interaction. So it would define which tables got backed up, what needed to be synced and how, etc. Working in this project became way more productive after this and there were no downsides to the ORM for me.
Please don't take a offense, but this rang alarm bells in my head from my Tech Lead days. I hope you were either the Tech Lead on that project or got their approval. Working together you might have come up with something better that the whole team could use. I personally believe in writing your code with the belief that the person that will have to maintain it after you is a homicidal maniac who knows where you live. Write code differently to the rest of the team and that's a whole other thing that the maintainer has to figure out.
@@AussieAmigan You seem to be way more concerned about the potential for harmful effects than the potential for beneficial ones, to the point you are going out of your way to reply to a RUclips comment about a project you know almost nothing about, because I didn't provide much detail, hoping I didn't go about making the positive change I mentioned in a bad way such that it actually did more harm than good. Of course it is always possible for someone to do that, but this is not a healthy response to hearing about someone make positive changes to a project they worked on.
I made these changes many years ago, and still to this day, I can assure you the code/changes I'm referring to is the best code I have ever written in my about 20 years of software development. I had been the tech lead on that project for a while, and by the time I made those changes, I was the only programmer still working on it. I'm still chasing the dream of having an entire system implemented with this approach. There would be declarative types that provide lots of metadata and specification and rules, and an engine that honors these for the entire system.
Most of what this guy is complaining about is people who don't understand databases, and has nothing to do with ORMs.
Which one teaches you how to understand databases?
A good middle ground solution is CQRS and DDD. Model your domain aggregates correctly. Have a simple repository abstraction for commands (add, get by id, delete) that only return aggregate roots (the orm usually handles updates pretty well). And then for the complicated queries, which are usually views or reports, have a different view repository that runs raw SQL under the hood and returns the data you need.
We are using sqlalchemy with postgresql on a relatively big flask project, and as far as I know, never had any problem writing complex queries using the ORM functions, sometimes we use raw SQL but thats only when we know the query is gonna be REALLY big so its just easier for us to start writing the SQL as we have more experience on it and its not worth bothering about translating it to ORM functions unless there is nothing more important to do (there is always something more important than refactoring queries that already work and hardly ever need to get modified).
Exactly and you have sqlalchemy core as a middle ground as well.
SQLAlchemy might be the best ORM in existence, and all these people complaining, must never have used it.
You can have table-per-hierarchy in almost all ORMs I've ever come across. In fact that's usually the default. Table-per-concrete class is pretty rare.
Just use a data serializer. Like Dapper in C#. Combines type safety with the ability to do anything you want since you supply the SQL.
On the select part yeah, ActiveRecord can select individual fields but it will instanciate the entire model anyway with the non selected fields set to nil. This can lead to misunderstandings (frontend guys, was the field nil cause it wasn't selected or what?), different serializers for the same resource, all fields might be optional in the frontend model but required when updating the backend...
Using Drizzle I have run into 0 of the problems. The Drizzle select syntax is already very close to regular SQL, just with great auto complete, end-2-end type safety and sanitization. And whenever a particular query gets too complex or needs specific features that are not implemented, I can use raw SQL inside my drizzle query, wherever I like (anywhere between 0-100% raw sql). And even with raw SQL I can still get easy type safety and sanitization. (I did look at a lot of ORMs and chose Drizzle specifically for how close it stays to raw SQL in the first place. My second option would have been something like query builder. But I really wanted migrations)
Besides building a mid sized DB, I also have to migrate a 8 year old mySql db into postgres, and again the entire setup is super convenient with drizzle (the migration part is mostly raw sql for date conversions, but the setup and db connection is still drizzle).
I did make a point out of re-learning raw SQL before starting my current project, though. That has helped immensely in really understanding what is going on.
Doing joins at the application isn't necessarily bad.
For many to many relations, doing the join in the DB would still take time AND then send magnitudes more data OTA.
Meanwhile the in memory join makes the transfer far smaller.
I think an important point in the video is that the ORM created extra tables, but without it, the data could have existed on one table and a join would not even be necessary
good luck debugging, extending, migrating, writing your own custom serializers, wrappers around SQL, upgrading, any other sort of maintaining pure SQL. ORMs are for simple queries, sqlbuilders and verifiers/generators are made to simplify writing SQL, and raw SQL is for small, mostly fetching, maintainable pieces, where it is 100% unavoidable
You are so wrong, it is not even funny.
Data processing should be done in a data processing language and as close to the data as possible (aka Database).
And business programs are mostly about data and lengthy processes, not your basic CRUD, that is just a small part.
Meaning most processing requires non-trivial SQL that can never be abstracted as they cannot be expressed in other languages.
@@TheEVEInspiration sql is not the fastest way to process data in really complex queries with custom nested transactions, data regrouping, transformations, schema validation, and so on, just look at pl/pgsql benchmarks, it's slower than python, and complex processing will be the limiting factor since it's not just fetching, updating or inserting data after all. And I'm not even trying to mention how horrible that mess is to support through various changes, and to integrate with types and everything.
@@ac130kz SQL is the fastest way to process data in a relational DB, period. Sure application logic should not be in SQL. But fetching, aggregating, filtering, regrouping, and validation will ALWAYS be faster in SQL, even if simply by rules of data locality. Those benchmarks are lies, as they don't take into account network time, query planning time, etc. If I want to aggregate data from a DB for a report, it will be fastest in SQL. And it won't even be close. Avoiding SQL is fine, but as soon as you're getting data from multiple tables, or doing grouping, or anything of that nature, it should be in SQL.
6:00 Can't believe you didn't call out the NOT LIKE without any wildcards, such a squeal n00b mistake
19:00
Yeah, people that use ORM to not depend on the database are now depending on the ORM. If you want to create a product that really doesnt depends on some infraestruture compoment, the real way to go is with a domain centric archtecture like hexagonal archtecture or clean archtecture.
My new favorite channel. Spicy takes with rich justifications. I'm with you on translating queries you've already composed to ORMs can be difficult. My first Python project years ago was also my first ORM project, because every resource I found on connecting it to a database demanded an ORM. I understood the concept of the ORM, but I hated every second of troubleshooting it.
A good ORM that is configured and used properly by a competent human avoids most of these problems.
Where I messed up with ORM was automagically making certain things happen. Those magic things were great until someone wanted something JUST A LITTLE Different and now I have "ignore_dependency" options which I have to carefully make sure don't open holes...
Laravels Eloquent Builder and DB class can do basically anything the guy said ORM can't do.
Model::query()->select('field1', 'field2')->whereNot('field3', 'like', $variable)->get();
try {
DB::transaction(function() {
// your code
});
} catch (Throwable $e) {
return "Database transaction failed"
}
Everything mentioned in this video stating not possible or hard to achieve using ORM is not true and can be done. I would say it's a skill issue rather than ORM issue.
Since the guy is clearly a scandi, a scandi term for a favour you do that makes things worse because you didn't think things through is a bear's favour or a bear favour. (Based on the g->y translation in yenneral, I think he's Swedish, so he'd say björntjänst.)
Using an ORM is doing yourself a bear favour.
EF Core is awesome to work with and way easier to work with than raw dogging SQL if you work with C#.
EF is a nightmare of abstraction.
LINQ converts everything into SQL using its Lambda/Expression tree reflection class. Function are attempted to be turned into LINQ unless it is turned into a list first.
The project I'm working on right now uses Sequelize. I jumped into a full stack role after working as a frontend dev for some years so both Sequelize and raw SQL were pretty new to me. At the start I had a lot of difficulty making some more complex queries performant using Sequelize and I kept going through the docs and trying to understand it better, but then even the docs themselves tell you to just use raw SQL if things are difficult to query using their standard syntax. Luckily it's very easy to just run some raw SQL with Sequelize, but the point is it didn't take very long at all for me to see where the pitfalls of using an ORM are. I still prefer it to just having no ORM at all because 90% of queries are perfectly fine with it, but it would be a nightmare if it didn't allow you to easily run a raw query.
Who the squeal doesn't design before using an ORM?
I wrote an ORM package this summer. I have a few more small things to add, but it has a lot of tools and features (and 402 unit tests and 4 integration tests). Each model (which can be generated by the CLI tool given a --columns name=type,... parameter) has a `query` method that returns a query builder scoped to the table, and that query builder has a `to_sql` method that returns the generated sql string with parameters interpolated. It also includes a migration system and can generate migrations from models, and you can execute raw SQL if you need to.
Once I finish combining it with my CRDTs package, I'll have a package that allows people to use sqlite as their main db, and it will synchronize in the background with strong eventual consistency. The theory is sound, and the individual components work and are pretty thoroughly tested, so it should work.
Using ORM to abstract your database is stupid. ORM for me is always about its tools. Migrations, query builder, declarative approach using annotations, decorators, attributes, built in code generators, debug tools, etc. And when you need a little bit of raw SQL no one can stop you from using it!
In some circumstances, migrations are not just not great, but can be downright terrible. I worked at a place where we built air-gapped pieces of hardware that had databases on them, and updates were infrequent. Given enough time, you'll eventually make multiple complex changes to the database (things that require rebuilding clustered primary keys, etc). If you use a migration pattern, then when you get around to updating one of these systems your series of migrations will take an insane amount of time because it's building and rebuilding indexes, adding columns only to then drop them, etc. A schema comparison/upgrade tool that utilizes backups to rollback is just plainly more performant in that case. But of course, at that job we also rolled our own separate migration tool in order to combine the two approaches, because sometimes you do need to execute queries after making certain schema changes! We also rolled our own dynamic query system that would allow you to use a stored proc to run different versions of the same query... thinking back on it, we were kind of database monsters over there.
If you use the right abstraction, design your data well and avoid using raw SQL in your application code then you can actually test your code without needing to spin up a real database every time. You can also add and tune optimisations at your abstraction layer without rewriting application code and then in 10 years time when your database outgrows your needs (or starts costing $$$) you have a chance of replacing it with something different, even a NoSQL data store, without having to rewrite 10 years worth of legacy data access code.
Question - What's the threshold between designing and whiteboard circlejerk (or whatever that term was).
I mean shitting on ORMs without even showing laravel's eloquent pffft. It's super convenient to have abstracted tables into models and have the relations, scopes etc. there for autocompletion. Having to always go through database to see the structures or having it all in memory to write some code has to suck.
I'm gonna be honest, I don't think the majority of people use ORMs to "avoid learning SQL". I think most of us know sql and either it's just what is being used at work or it's just a little bit more convenient. I'm sure you can find a few people who actually use ORMs to avoid learning SQL but I really don't believe it's the majority.
At 0:45 the laravel example used the query builder so idk what is wrong with that example. Try to add conditionals to a normal query string and you will get an even uglier output.
At 2:45 that example is folly, in most ORM's i used you can have different models that reference the same table with specific conditions for querying. Coming back to Laravel, you just create a queryScope to say that GraduateStudent ( as an example ) is from the table "persons" and always applies the filter "is_graduate = true" ( again, as an example ). Which imo is better then always remembering to add the condition or having a repository that you need to use to fetch the correct response.
12:30 Or you use a good ORM, a good one should allow you to dump the SQL generated and the params it will bind. Easy to see the query, easy to try it yourself, easy to optimize.
14:40 - again, ORM's that respect themselves have this, drizzle for Node as an example, Eloquent for laravel, both can run with transactions, the same as in raw SQL, you need to optimize the query. You can have the same bug in both as easy.
16:35 well, not if you have tests. With a ORM or Query Builder you can simply a SQLite database for testing, which is fast and requires no additional setup and you can have the real big deal database for testing. Migrating to a new database based on requirements or working with multiple also works. At work currently we have 2 databases, one legacy one that uses mysql and a new one that uses postgresql, we use Eloquent for both and to justify which is which we just have a `protected string $connection` property on the models. This makes it more readable for us as we know which db we use and makes it easier to work with as we write the same syntax.
In the end, i think this guy might need to venture down in some good ORMs, from my experience, most Active Record ORMs are very good, when you use them you feel like writing SQL by hand is a huge downgrade as they do just that, give you 100% control where you want to have 100% control and they can be integrated with multiple databases at once ( for testing as an example, or for a small staging website where a SQLite database on the local filesystem is just fine ). If even Active Record is too much for you, go with a query builder, if a query builder does something bad you already know that's you not the query builder and you would have done the same mistake raw dogging squel
Honestly, spring data did it right. It provides just enough abstraction and CRUD utilities to get you going quick but at the same time you can create methods to run arbitrary SQL. Just perfect ❤
It still depends on Hibernate imho
@@Quiarkso?
I'm on dotnet team, but I agree - saying that "ORMs are bad" and showing some JS stuff is stupid - EF Core is one the most epic pieces of tech I know of. You use the db table abstractions just like normal lists - you add, remove, use LINQ (like Where(x => x.Name.Lenght > 5) ), and they get translated to SQL. If they can't be translated you can easily either just pull the objects and do the thing in C#, or you can just throw in some raw sql.
I almost said "it's perfect" - of course it's not. But having to also write some complex SQL dependend on some "SearchCriteria" object - I take EF Core all the time and fall back to raw dogging when needed
Yeah, I keep my utility usage to basics save, delete, find, etc. Everything else I raw dog SQL since spring data makes it darn easy to do so.
17:30 nooo… an ORM shouldn’t be chosen so you can switch database tech, instead chosen to be a client written in your language of choice to manage your persisted data. Just don’t use raw SQL in code. If the db schema changes, you’d have no IDE support to help you change all the places that may need changing. You and your team will likely get to a point where you’re fearful of changing the db schema, which is bad. The exception to this should be when you abstract the SQL behind a stored proc, the SQL of the stored proc should of course be managed by the migrations, and therefore should be in the code… but the calls to the stored proc should be done via an ORM.
The "don't abstract things because by the time you really need to you'll have enough money to hire 1000 people to do it" is great for the 3% of devs who are startup founding members. The rest of us are one of the 1000 hired to fix things.
0:01 oh dude… ORMs aren’t trying to make SQL obsolete… this is gunna be painful. ORMs are no different from using a client written in your favourite language to speak to an API written in another language. So if you have a problem with ORMs, you have a problem with any type of system to system integration.
I do agree with base premise that orms can abstract too much and nake things more complex. Especially if done incorrectly like his examples. All these seem like skill-issues. Things like activerecord has count, ability to select out individual fields (not doing select *), eager load data to avoid multiple queries and joins, has single table inheritance if wanted, can even raw dog the sql and let it load into your models, transactions, even nested transactions if wanted, etc. This is why we should look around at other ecosystems, js, php, python, cpp, java, c#, lisp, etc. Gets you broader view of whats possible and already commonly solved elsewhere.
Agree with you, they claim ORM is bad, but they don't seem to conduct enough research to say for sure, and already making video like they know whole universe...
that's the reason why learning something on youtube is dangerous, people here usually is actors - not specialists
Honestly, this is just a bad take. Most people use ORM-s because they just don't want to maintain insert/update statements for 100+ tables that change relatively frequently, handle optimistic locking by hand for every single update or map results of selects manually to application structs or even worse - use the result types provided by the driver. And saying things like people use ORM-s because they don't know SQL is absolutely not true, at least in my experience, we just prefer to use our SQL knowledge when the tool is not smart enough to generate a specific query or hand-crafting it would be significantly more efficient. Most ORMs will allow you to do that and have no problem with mixing raw SQL with framework-provided methods and generally also automatically map the results of your query to application structs, at least with rails, c#, and hibernate/spring data that's the case, not sure how things are in js-land
Activerecord in Rails is nice - works fine moat the time. You can use raw queries if needed
ORMs work great for saving data, that's where the cheese is. For querying, unless you are looking to select an ORM object by primary key, you are best off using SQL.
The example about inheritance is table-per-type. Sometimes that's appropriate, but usually table-per-hierarchy works better. Entity Framework now defaults to TPH.
This is completely orthogonal to the point here, but I'm 9 months into my first job as a data engineer and have completely fallen for Data Build Tool (DBT) which uses Jinja templating. It's very similar to the 3rd approach mentioned in the video but a lot less concerning. The idea is simply to allow code in the middle of your SQL query that compiles in-place into the corresponding SQL.
For example:
select * from Person where {{ is_admin() }}
might compile to:
select * from Person where account_type = 1
Nothing crazy. No "manipulate this so that it hides a potential cross join". Just "put code right here". And surprisingly it solves a lot of the pain points I have with raw SQL by keeping it SQL but still enabling a lot of flexibility. I will say however that DBT is mainly intended for data engineering/analytics, not for 1-off queries like "who is this specific user?".
The main function of an ORM is to maintain an identity map of model objects vs rows in the database. About 99% of hard to fix problems with DB usage comes from stale data. You can roll your own identity map and that's fine too, it's way less work than writing your own ORM. The only other use is just having a nice type-checked wrapper around raw strings. But tbh that part is really not as useful and it also can be incredibly slow. A good ORM will have those elements modular so it's not all or nothing. A good example of modular ORM design is SQLAlchemy
If you have a good understanding of SQL and understand the trade offs of whichever ORM you are using, use it if it's a net positive to your project.
All the 'haha gotcha ORM' arguments presented are all very easy to understand to someone familiar with SQL and are simple to deal with. Index creation, migrations, cartesian explosion, etc...
Also using an ORM does not mean you cannot use RAW SQL anywhere in your project. A deep understanding of the tools you use is very unlikely to lead you to shooting yourself in the foot.
Stop talking about the only reason people use orm is that they trying to avoid learning sql. Thsts a bullshit. Personally i use ORM beacause of default security against most stupid sql ijections, because of great maintainability of your queries, if someone changes a model its obvious where are those queries that you should change accordingly. I simply dont wat to maintain dozens of dead texts as queries. I like migrations and all that stuff. I also like dynamic composability of a query. You just can build it up as an object, thats super nice.
I wont trade all that stuff for a pseudo simplicity. No way.
"most stupid sql ijections"
It is super easy to parameterize SQL when using a micro ORM.
And you can always use a RegEx pattern to detect and block suspect SQL before it is executed.
That is absolutely not a valid reason to unleash hell on your developers and company/customers.
"I also like dynamic composability of a query"
This is most certainly is where plain SQL shines, it is very modular/conditionally expandable.
@@TheEVEInspiration LOL. What composability? SQL is still a joke, if it would be good we wouldn't have these many ORMs. People unfortunately switched to Node.js and they haven't seen a good ORM and now they are trying to gaslight everyone. I have seen too many page-long SQL queries with the same parts duplicated over and over. I want real variables, functions, separation of concerns. SQL forces you to abandon 50 years of progress in compsci.
I think a lot of the time it's not orm bad, it's orm being poorly documented, people not willing to dive deep into an orm that they might replace tomorrow, people believing and spreading misinfo that orm can't do something etc.
16:07 migration scripts should be maintained and refractored, same as with regular code.
I'm in favour of regularly compressing the scripts down to represent the current desired database schema over endlessly adding delta scripts - you just need to be sure the scripts you're refactoring have been fully released through to all environments.
Generally that means having a base layer of "create if not exists table index / etc" and "create+alter view / procedure" idempotent scripts that you backport the changes into. These can even be automatically generated using the production schema
`if not exist`... how it is possible that anything got changed before migration script had changed it, "just make sure" that is another huge problem that is not simple at any scale, except one person project maybe. There is no need to maintain migration scripts, when migration is in production - delete it - why would you ever run it again except for immediate rollback? The current schema is in version control regenerated when running migrations (basically `pgdump ---schema-only`). ORM handles all of this. When this kind of approach stops working, most likely there is no readymade solution anyway.
idk, most examples showed if not all popular ORM libraries handle easily. this looks like a video from someone who either has not used an ORM in 15 years or is just trying to push bs
I always think in raw sql first then translate them to orm to write them in code. That translate to orm is just an extra step of thinking, not really needed, instead of writing the sql directly as string.
The only advantage of ORM that I see is if you need to change database server at some point in time. E.g. mysql to prostgress or any other.
The big advantage I feel from an ORM over raw SQL is automatic translation into a model object. Actually, not the biggest advantage... the only advantage. My ideal ORM would be one that lets me write raw SQL and translate the results into objects, and does literally nothing else.
No interest in a .save() method or getters for derived properties, such as getUrl() where maybe only a slug is stored in db & the url depends upon some environment config?
Personally, i love SQL, but i need some convenience features.
i would be happy if all the orm did was translate snake case column names into camel case property names on some ad hoc object, or vice versa.
one such exists... called JOOQ. I love it
Ok but is it postgres or postgre?
It's just trading writing a SQL string for a library that abstracts it into somewhat unreliable function chaining that builds a string which you then have to debug.
Jetbrains Exposed lets you do two things:
- Choose between SQL DSL and DAO
- Requires explicit transactions for writing data. You surround your code with transaction { } and it does the rest.
What i see, that most programms implement ORMs in a way that queries the Database all the Time Row per Row where you get like 10k queries that actually could be 1-5 queries :)
Prime: gets something wrong
Chat: "Netflix btw"
16:24 Sounds like this is a good example of where the YAGNI (you ain't gonna need it) principle comes into play? Making consequential and often costly choices on the basis of hypothetical future scenarios that may never come to pass (and on the off chance that they do, you could deal with it then, and you may be overestimating how much this would actually help you)... Is often a waste of time.
I always found the biggest gains from an ORM has been the database agnostic error handling and input sanitisation.
No one wants to be the person who is responsible for the avoidable injection attack or dealing with each database driver's different set of errors...
When people who don't like to use an ORM try to convince you not to use an ORM with their lack of ORM experience.
I used a handful of ORMs for years, but they only gave me -two- three things:
1. The ability to call the database in code
2. Built-in sanity checks / input validation.
3. Compile-time errors via serializing the database as structs in code
The rest were eventually disadvantages. And I think it's way more powerful to generate functions based on stored procedures (and basic CRUD for each table).
That is a big assumption, got something to hide as in, never tried to learn SQL?
I know both and beyond the most simple CRUD, ORMs are worthless and do nothing but hold you back.
ngl, I kinda feel like it would be cool to have something like lua running on detabase-side so we don't have to deal with the crap that is PL/SQL for stored procedures / functions...
As you say ORMs are fine if you have the flexibility of jumping out of ORMland into writing direct SQL.
You should have a decent understanding of how to write performant SQL as well though and also be monitoring the performance in your DB so you can optimise any ORM queries and break them out.
And yes absolutely design your DB schema first, build your ORM domain models around it.
One solution for the inheritance problem outlined at the beginning is to create one big table for each class-tree.
This guy must not have been doing dev in 2005, writing SQL as text was a nightmare to maintain, ORM's were created for a reason
A summation of the OP video: I don't know how to use a tool, so I'm gonna bitch about it on the Internet.
This isn't a ORM problem, just an OOP problem. Get out of OPP dogma and the mismatch is gone.
In 15 years of the frontend community being enchanted by functional programming, FP has yet to deliver on any promises of building user interfaces in a cleaner or more performant way. OOP is perfect for creating user interfaces. Be careful with the gurus you choose and the dogmas you accept.
@@nandoflorestan I'm not in favor of functional programming. It's useful in niche cases. So is OOP.
There's many different kinds of UI, but when it comes to things like web, most of the time it's better to let the interface just be data. Of course most gui frameworks and systems are different from html, but it was also a mistake to force it into OOP.
I agree with you totally that you should be careful about what dogma you get caught up in. But OOP+"clean code" according to bobs Bible are some of the worst.
So, when use ORM and when use SQL?
to be fair, isn't LIKE without wildcards in the string the same as equivalence?
JOOQ is the way. You essentially just write SQL, except you get java type safety, auto generated POJOs, and parameter sanitization from JDBC