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
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.
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.
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.
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.
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.
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 😢
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'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.
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
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.
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
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
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.
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
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.
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.
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#).
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"...
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.
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.
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.
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.
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.
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
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.
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.
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
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 TH-cam 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.
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...
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).
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
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.
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.
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.
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" }
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
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.
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.
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.
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.
@@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.
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.
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.
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.
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 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.
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.
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.
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.
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!
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
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?".
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.
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 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
I mostly use an ORM because it handles a bunch of patterns OOTB that id be inplementing anyway. I still use SQL as needed when stuff doesnt translate through the ORM well and I dont want to extend it with Expressions.
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
I read somewhere if you're dealing with an application that uses a database, sort out the data and work on the database side first since everything else depends on it.
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...
im working on making async REST apis using SQL. I dont actually understand async yet so its going great. I need the simplicity of SQL but I need async ops and thread/process management so here we are.
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.
SQL statement is easy to test and debug outside the program and you can build SQL statements layer by layer (especially in postgres WITH/CTEs). Simple selects and lazy loading frameworks are ok, more complex queries need to be tested. Rebuilding these queries in selected orm for specific language - not a good idea.
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.
Maybe I got too used to it, but it mostly use ORM at work, it works when it works. There was one time where i had to make a ridiculous query mainly because i had to filter by a subquery with 2 field which the ORM doesnt support. I cheated it by using the CONCAT function. Other time was more recent, where we made a huige statistics and we knew the ORM would be quite slow, so we used Raw SQL for that part where the ORM would just wouldn't work Now i'm mostly using ORM, but i keep thinking about the underlying SQL and if necessary make Raw SQL in it. ORM might work but working code is not necessarily good code
I think I change more often an ORM then the a Database type itself. I agree that we need to keep close to the that source, and writing SQL will have benefits. Also for programming I dislike dialects of programming languages that has to transformed to use in runtime or even just run the test. (TS) We try to use more and more layers over the original, and makes it too complex. If you keep close to the origin, debugging will also be more easy, because you lose a lot of layers that distract you from what happens. This not only happens in programming, also in other industries like cars, that are too complex to repair for the most people. Or electronics that are repairable? Most are trowed away if something isn't working how it should be. How hard is it for your code to understand in all layers? And is it made to repair or throw away, also if some dependency will stop? I think we should go back to simple code, that is made for a lifetime, not for months.
My issue is that in java, JPA is ~100x more complex than the SQL you're abstracting... even if it doesn't leak. Most vexing of all is when the DBA says 'here, this query uses one millionth of the resources' and you have NO IDEA how to get the actual ORM to spit that out, but the DBA query doesn't quite line up w/the object boundaries in your code so you can't just 'raw dog it'. And that's if youre LUCKY... For example `db.selectAll().count()` may do a count query... or it may pull back the entire DB into a list and then take its length. Nobody can answer w/o reading the docs, then you find out 'it depends' on 100 different settings, your driver, the actual RDBMS and the versions of all those parts. I know exactly what `select count(*)` does even in RDBMs' i have never used.
The issue with raw SQL for me is that you still need a library/SDK to run the queries, pack parameters, iterate results, manage db connection, integrate into your app etc. So I'm learning a certain API already anyway. On top of doing my own SQL. At this point why shouldn't it also know my schema and do the repetitive stuff for me? Second thing is migrations and maintainability. ORM with option to do raw SQL is the way to go.
i used an orm to avoid sql back when i made my first app, sqlalchemy for python which was terrible dx terrible docs etc , currently i changed to golang and god is it a fresh breath of air. if i wanted to save an object i currently create a method with save/load and for query just use basic sql
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.
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
See this is why you use a micro-ORM that literally only does the object mapping from objects -> properties and results -> objects with you writing your own rawdog sql commands
I think Prisma gets lumped up with ORMs as bad. But Prisma doesn't have really any of the issues that ORMs are slammed for. It's more just a query builder with type code gen.
Interesting question about DDD in the chat. I think when you're designing your data model in DDD you're thinking mostly about what the entities and value objects and their cardinality with respect to one another. And thus far there's no real impedance mismatch between objects and relations, the biggest problem is inheritance, but who says you have to or even should use it anyway? Or even objects for that matter. The blue book is written with the assumption that you use OOP, but I don't see why that has to be the case. Scott Wlaschin's excellent Domain Modeling Made Functional uses ADTs, and I'm sure you can do it with good old-fashioned structs as well.
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.
Sounds like neither have used SQLAlchemy. - full control of loading strategies - drop down to sqla core or raw sql as needed - bring your own choice of migration library - rich integration to different flavours of sql - ability to define your own native functionality
I have completely stopped using ORM frameworks for my personal projects, and were I to start on a professional project from the ground up then yeah, I would probably avoid ORM and figure out how to do migrations myself.
The only issue with raw dogging SQL is that the common DBs are just not standardised beyond simplistic statements and primitives; and generally I want to stay DB agnostic in my code. Even though Hibernate / JPA is by a wide margin the best ORM, I occasionally find myself finetuning the SQL.
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.
Each time I work with sqlalchemy I find that it is easier and shorter to write bare sql. It is all nice until you try to limit orm what it gets from db, then if you getting related object you want to get it one query without n+1 requests... and in bare sql I see the whole query, with orm I check generated query during testing (so I still deal with sql)... of course I can do all that in sqlalchemy, but if you add enough options to your orm query, it starts easier to read bare sql. I had a project where we dealt to use bare sql and produce desired objects if needed by hands, oh it was wonderful and easy to use project.
I think problem is the SQL. You could imagine that database could expose binary API that is already conveniently made to be used in applications. But no, for computer-to-computer communication, let's use a text based language with sql injection vulnerabilities. Have a parser on sql server side that will parse it every time. And since writing these plain text queries is especially problematic for things like dynamic filtering then let's additionally build a huge tool that will generate these queries and call them ORMs. Basically 2 additional layers - query parser and query factory - just because we chosen plain text language as computer-to-computer interplay. Kinda similar story in browsers with Javascript. You could just decide on a minimal binary bytecode. But no let's ship ill-featured language compiled in-browser so that developers writing code would have fun being locked into decade old language features just so their website can be rendered in someone's outdated browser.
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 :)
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
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
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
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.
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.
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.
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
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
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.
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
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.
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
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?
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.
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
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.
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.
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#).
I like using dotnet entity framework (with the C# syntax not the SQL syntax) it lets me basically just write SQL 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"...
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.
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.
Design the database first. Then use a query builder that is very close to SQL syntax and sanitizes user input for you.
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.
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
I don't understand the ILADIES part. Could you please explain it?
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
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
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.
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.
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.
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
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 TH-cam 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.
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...
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.
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
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.
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.
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.
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"
}
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
6:00 Can't believe you didn't call out the NOT LIKE without any wildcards, such a squeal n00b mistake
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.
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.
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.
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.
A good ORM that is configured and used properly by a competent human avoids most of these problems.
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.
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😂
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.
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.
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.
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 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.
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.
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.
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.
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!
Prime: gets something wrong
Chat: "Netflix btw"
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
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?".
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.
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 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
I mostly use an ORM because it handles a bunch of patterns OOTB that id be inplementing anyway. I still use SQL as needed when stuff doesnt translate through the ORM well and I dont want to extend it with Expressions.
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.
I use JSON for each page of my website. I then store the JSON in sql assigned to the username and page ID.
It's basically a small ORM stashed in SQL
I read somewhere if you're dealing with an application that uses a database, sort out the data and work on the database side first since everything else depends on it.
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...
im working on making async REST apis using SQL. I dont actually understand async yet so its going great. I need the simplicity of SQL but I need async ops and thread/process management so here we are.
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.
SQL statement is easy to test and debug outside the program and you can build SQL statements layer by layer (especially in postgres WITH/CTEs). Simple selects and lazy loading frameworks are ok, more complex queries need to be tested. Rebuilding these queries in selected orm for specific language - not a good idea.
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.
Maybe I got too used to it, but it mostly use ORM at work, it works when it works.
There was one time where i had to make a ridiculous query mainly because i had to filter by a subquery with 2 field which the ORM doesnt support. I cheated it by using the CONCAT function.
Other time was more recent, where we made a huige statistics and we knew the ORM would be quite slow, so we used Raw SQL for that part where the ORM would just wouldn't work
Now i'm mostly using ORM, but i keep thinking about the underlying SQL and if necessary make Raw SQL in it.
ORM might work but working code is not necessarily good code
ORM also can be useful to me with migrations, where it handles my updated columns without having me checking and doing it on startup
I think I change more often an ORM then the a Database type itself.
I agree that we need to keep close to the that source, and writing SQL will have benefits.
Also for programming I dislike dialects of programming languages that has to transformed to use in runtime or even just run the test. (TS)
We try to use more and more layers over the original, and makes it too complex.
If you keep close to the origin, debugging will also be more easy, because you lose a lot of layers that distract you from what happens.
This not only happens in programming, also in other industries like cars, that are too complex to repair for the most people.
Or electronics that are repairable? Most are trowed away if something isn't working how it should be.
How hard is it for your code to understand in all layers? And is it made to repair or throw away, also if some dependency will stop?
I think we should go back to simple code, that is made for a lifetime, not for months.
My issue is that in java, JPA is ~100x more complex than the SQL you're abstracting... even if it doesn't leak. Most vexing of all is when the DBA says 'here, this query uses one millionth of the resources' and you have NO IDEA how to get the actual ORM to spit that out, but the DBA query doesn't quite line up w/the object boundaries in your code so you can't just 'raw dog it'. And that's if youre LUCKY...
For example `db.selectAll().count()` may do a count query... or it may pull back the entire DB into a list and then take its length. Nobody can answer w/o reading the docs, then you find out 'it depends' on 100 different settings, your driver, the actual RDBMS and the versions of all those parts. I know exactly what `select count(*)` does even in RDBMs' i have never used.
The issue with raw SQL for me is that you still need a library/SDK to run the queries, pack parameters, iterate results, manage db connection, integrate into your app etc. So I'm learning a certain API already anyway. On top of doing my own SQL. At this point why shouldn't it also know my schema and do the repetitive stuff for me? Second thing is migrations and maintainability. ORM with option to do raw SQL is the way to go.
i used an orm to avoid sql back when i made my first app,
sqlalchemy for python which was terrible dx terrible docs etc ,
currently i changed to golang and god is it a fresh breath of air.
if i wanted to save an object i currently create a method with save/load and for query just use basic sql
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.
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
Activerecord in Rails is nice - works fine moat the time. You can use raw queries if needed
See this is why you use a micro-ORM that literally only does the object mapping from objects -> properties and results -> objects with you writing your own rawdog sql commands
I think Prisma gets lumped up with ORMs as bad.
But Prisma doesn't have really any of the issues that ORMs are slammed for.
It's more just a query builder with type code gen.
Interesting question about DDD in the chat. I think when you're designing your data model in DDD you're thinking mostly about what the entities and value objects and their cardinality with respect to one another. And thus far there's no real impedance mismatch between objects and relations, the biggest problem is inheritance, but who says you have to or even should use it anyway?
Or even objects for that matter. The blue book is written with the assumption that you use OOP, but I don't see why that has to be the case. Scott Wlaschin's excellent Domain Modeling Made Functional uses ADTs, and I'm sure you can do it with good old-fashioned structs as well.
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.
JOOQ is the way. You essentially just write SQL, except you get java type safety, auto generated POJOs, and parameter sanitization from JDBC
Sounds like neither have used SQLAlchemy.
- full control of loading strategies
- drop down to sqla core or raw sql as needed
- bring your own choice of migration library
- rich integration to different flavours of sql
- ability to define your own native functionality
I have completely stopped using ORM frameworks for my personal projects, and were I to start on a professional project from the ground up then yeah, I would probably avoid ORM and figure out how to do migrations myself.
The only issue with raw dogging SQL is that the common DBs are just not standardised beyond simplistic statements and primitives; and generally I want to stay DB agnostic in my code. Even though Hibernate / JPA is by a wide margin the best ORM, I occasionally find myself finetuning the SQL.
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.
I use orm for crud and squirrel if I need something complex
Each time I work with sqlalchemy I find that it is easier and shorter to write bare sql. It is all nice until you try to limit orm what it gets from db, then if you getting related object you want to get it one query without n+1 requests... and in bare sql I see the whole query, with orm I check generated query during testing (so I still deal with sql)... of course I can do all that in sqlalchemy, but if you add enough options to your orm query, it starts easier to read bare sql.
I had a project where we dealt to use bare sql and produce desired objects if needed by hands, oh it was wonderful and easy to use project.
I think problem is the SQL. You could imagine that database could expose binary API that is already conveniently made to be used in applications. But no, for computer-to-computer communication, let's use a text based language with sql injection vulnerabilities. Have a parser on sql server side that will parse it every time. And since writing these plain text queries is especially problematic for things like dynamic filtering then let's additionally build a huge tool that will generate these queries and call them ORMs. Basically 2 additional layers - query parser and query factory - just because we chosen plain text language as computer-to-computer interplay. Kinda similar story in browsers with Javascript. You could just decide on a minimal binary bytecode. But no let's ship ill-featured language compiled in-browser so that developers writing code would have fun being locked into decade old language features just so their website can be rendered in someone's outdated browser.
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 :)