The Only Database Abstraction You Need | Prime Reacts
Вставка
- Опубліковано 5 лис 2023
- Recorded live on twitch, GET IN
/ theprimeagen
Reviewed video: • The Only Database Abst...
By: / @mrcompsciguy
MY MAIN YT CHANNEL: Has well edited engineering videos
/ theprimeagen
Discord
/ discord
Have something for me to read or react to?: / theprimeagenreact
Kinesis Advantage 360: bit.ly/Prime-Kinesis
Hey I am sponsored by Turso, an edge database. I think they are pretty neet. Give them a try for free and if you want you can get a decent amount off (the free tier is the best (better than planetscale or any other))
turso.tech/deeznuts - Наука та технологія
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
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.
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
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
We don't use ORM's and Query Builders to avoid writing SQL, we use it to avoid the DRY and string interpolation mess raw SQL creates, there are other benefits as well.
Yeah, even some of the examples of code he flashed in the beginning are clearly a better alternative to writing SQL in the code, specially with messy string interpolation or even more messy and fragmented, concatenated strings. Also, do we want devs to have to sanitize inputs themselves too? And that's just the start(although he does get into the pros of ORM at the end)
Yeah, knowing SQL is important. But writing all the queries in the code, instancing all the objects manually, no thanks.
Also, seems like the guy has only worked on a few ORMs, because his examples are not universally true for all ORMs.
This exactly, Plus also serialization.
How exactly would using a query builder deduplicate any code?
What are you trying to avoid to write? The SQL statement? That SQL statement is hidden behind the small veneer of a heap of string manipulations. You do repeat yourself, you just don't know it, and pretend that it's DRY.
Most of the time you can just abstract away the sql yourself by writing the sql in a function so you have the upsides of an orm and the upsides of manual sql
A student in the Web application security course I used to teach was doing a SELECT * and then writing all kinds of looped conditions to filter out the data he actually wanted, running into all kinds of bugs and I said "if only there were some sort of domain specific language specifically designed to do this." SQL really is not hard and it is amazing what people will do to try to avoid learning literally anything.
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
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.
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.
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
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.
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.
I don't understand the ILADIES part. Could you please explain it?
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.
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.
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#).
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.
isThisAcceptablePartner sounds like a passive aggressive cowboy
I like using dotnet entity framework (with the C# syntax not the SQL syntax) it lets me basically just write SQL queries
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
@@user-kx9yb6tk6g great, so how do I get one of those? 😺 Btw, internships can also be paid. It depends on the company
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
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.
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
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.
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...
Design the database first. Then use a query builder that is very close to SQL syntax and sanitizes user input for you.
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...
The project I'm working on right now uses Sequelize. I jumped into a full stack role after working as a frontend dev for some years so both Sequelize and raw SQL were pretty new to me. At the start I had a lot of difficulty making some more complex queries performant using Sequelize and I kept going through the docs and trying to understand it better, but then even the docs themselves tell you to just use raw SQL if things are difficult to query using their standard syntax. Luckily it's very easy to just run some raw SQL with Sequelize, but the point is it didn't take very long at all for me to see where the pitfalls of using an ORM are. I still prefer it to just having no ORM at all because 90% of queries are perfectly fine with it, but it would be a nightmare if it didn't allow you to easily run a raw query.
I wrote an ORM package this summer. I have a few more small things to add, but it has a lot of tools and features (and 402 unit tests and 4 integration tests). Each model (which can be generated by the CLI tool given a --columns name=type,... parameter) has a `query` method that returns a query builder scoped to the table, and that query builder has a `to_sql` method that returns the generated sql string with parameters interpolated. It also includes a migration system and can generate migrations from models, and you can execute raw SQL if you need to.
Once I finish combining it with my CRDTs package, I'll have a package that allows people to use sqlite as their main db, and it will synchronize in the background with strong eventual consistency. The theory is sound, and the individual components work and are pretty thoroughly tested, so it should work.
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"...
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.
EF Core is awesome to work with and way easier to work with than raw dogging SQL if you work with C#.
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
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.
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.
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 have my own micro-orm and it works great for me. It's API mostly feels like a "document store", where optional joins are automatically made and constructing objects from a 2D table into complex object is done by the library, but also allows for extremely performant raw-dogging (feels like Dapper) and very easy-to-use transactions, it has the full spectrum of possibility that I need
I never really thought of ORMs as a way to avoid knowing SQL. It just reduces duplicate code. I worked on a project where there was no ORM and we had to hand write a custom method implementation and some SQL for every database interaction. Eventually I made my own ORM like thing that made it so that as long as you defined special type up front, you could use it as a class and there was a generic table class that automatically worked with these types. Then I only wrote SQL when doing joins or things that didn't run fast enough. But it was really useful for selecting all the data from a table in order to create a full fledged version of a model class and then to just be able to call .save() or .update() on it later. Actually I had a lot of generic classes that knew how to work with these types and automatically worked with anything you through at it, not just for database interaction. So it would define which tables got backed up, what needed to be synced and how, etc. Working in this project became way more productive after this and there were no downsides to the ORM for me.
Please don't take a offense, but this rang alarm bells in my head from my Tech Lead days. I hope you were either the Tech Lead on that project or got their approval. Working together you might have come up with something better that the whole team could use. I personally believe in writing your code with the belief that the person that will have to maintain it after you is a homicidal maniac who knows where you live. Write code differently to the rest of the team and that's a whole other thing that the maintainer has to figure out.
@@AussieAmigan You seem to be way more concerned about the potential for harmful effects than the potential for beneficial ones, to the point you are going out of your way to reply to a UA-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.
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.
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.
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.
Prime: gets something wrong
Chat: "Netflix btw"
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
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 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.
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
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.
Question - What's the threshold between designing and whiteboard circlejerk (or whatever that term was).
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.
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.
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
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...
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.
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.
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
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.
I worked on a huge java app with hibernate and Oracle as DBMS. We had to write manual migration scripts for db between each minor version and then collected them for bigger migration scripts between major versions. The accumulation process was also manual. And because Oracle does not have transactional DDL - if the migration failed we had manual backout. It sucked so bad...
I think one point not discussed here is developer experinece and TypeScript. People like prisma because you have type safe queries. Performance is mentioned a good bit but with prisma 5, performance is not an issue anymore. I'm using prisma here as an example but other ORMs do similar things. I think you should ask yourself, do I want type safety or not. You could also define the type for every query response with an interface... lol
> with prisma 5, performance is not an issue anymore 😅😅 oh boy
I think you get the bulk of the benefit with MikroORM with the benefit the maintainer understands why people want to use transactions and.. Just none of the Prisma baggage and complexity.
Do you really need type safety? Or it is just js world problem? You're querying SQL database and values must abide by column type. Just know your data source.
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.
One solution for the inheritance problem outlined at the beginning is to create one big table for each class-tree.
The Kotlin ORM for Android was nice. You could write squeel in an @ symbol annotation above a method in your code to control the SQL more directly, but you still got the nice mapping
Yeah that’s Android Room I believe. It’s really nice and checks queries compile time
Yup room database they done very good job. With compile time checks so no room for error.
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...
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.
A good ORM that is configured and used properly by a competent human avoids most of these problems.
"does a full table scan"
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.
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"
}
I use ecto and I was very happy with it.
You can write sql internally but u have errors in ur code that look like elixir errors
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 :)
Oh, never realized how good the laravel eloquent ORM is - it nothing like the examples that were shown.
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.
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.
ahm why not use flyway or liquiedbase for database migration?
My php orm is designed to be sql-driven, with a php-friendly interface for repetitive and simple operations.
I love SQL. But working with raw data in a complex application is a nightmare, so having a php class representation is fantastic.
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.
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?".
I do like using "light" orm's. What I mean is, not Hibernate which layers state and session handling on top. This allows so many errors to creep in (n+1 select problem for example). I prefer control over transaction boundaries and the capability to provide SQL for certain queries that may be a little more complex than just get by id). The one I use is Micronaut Data which deliberately does not provide all the stuff you just do not need. It also generates stuff at compile time (you see errors in the IDE). Arrrrrgh. "Select *" is the spawn of satan!
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!
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.
Most of what this guy is complaining about is people who don't understand databases, and has nothing to do with ORMs.
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
5:34 Yes we can. At least in LARAVEL we definitely can.
And the problems this video has pointed out so far do not happen in the Laravel world.
Either way it's good to know SQL before jumping into any ORM.
This is why everyone jumped from Prisma to Drizzle
I use orm for crud and squirrel if I need something complex
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.
Query by language is basically how hibernate does
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
JOOQ is the way. You essentially just write SQL, except you get java type safety, auto generated POJOs, and parameter sanitization from JDBC
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 usually write my own SQL because I started as a data analyst so it’s faster for me. ORMs are cool too
ORM's blow when you need to set up indexes for speed up a query. It's better to have it call a sproc and pass the parameters in, i.e. UID, PWD.
Years ago I wrote a small app that had the whole backend implemented only in mysqueel and stored procedures. No api!
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 think its always an it depends, ORM's can allow you to not context switch between SQL & Programming Language at the time. Personally I like Dapper in C# :)
What about stored procedures?
And is there such a thing as automated testing for stored procedures?
I mean executed by the database.
Just execute the test in a transaction and rollback when done.