DONT USE AN ORM | Prime Reacts
Вставка
- Опубліковано 21 сер 2023
- Recorded live on twitch, GET IN
/ theprimeagen
Blog article: wozniak.ca/blog/2014/08/03/1/...
MY MAIN YT CHANNEL: Has well edited engineering videos
/ theprimeagen
Discord
/ discord
Have something for me to read or react to?: / theprimeagenreact
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 - Наука та технологія
As a grandpa dev, instead of learning 5 interchangeable languages of Algol-60 family , if you are a Jr. Dev. spend time learning:
1. Databases: esp db internals and query optimization
2. Unix/Linux esp the networking stack and file system stuff
3. Get into the details of at least one cloud platform
4. Learn bash: the number of problems that can be solved by a simple shell script (sed+awk+grep+pipes)
5. The art of writing clearly and concisely.
This is not only good for junior devs. It’s good for any devs who haven’t already learned those things.
Learning the shell and core utils is amazing. It gives you code that can run basically everywhere
Definitely one of the best comments I have read so far on this channel.
Learn the fundamentals first and not the tools because, in the end, everyone can learn a framework or an ORM by using it but if you have more knowledge you will be the one that finds the quirks and the best solutions for any type of problem.
This is just a personal supplement to basic knowledge, but you still need to learn popular development technologies in order to enter the workforce.
This is SOLID advice. 👍
The engagement tactic of mispronouncing SQL to get people to correct you is genuinely funny.
He is not mispronouncing SQL, that's how you should pronounce it
his random pronounciation always kill me
@@siniarskimar This is the only channel where I hear SQL pronounced as "squeal". The official pronunciation from wiktionary and e.g. mysql-devs would be "es-cue-el" since it is an abbreviation. But I also heard and read the pronunciation "sequel", but never "squeal".
@@StingerAJ 🤓
@StingerAJ. That comment was not serious. As a degen Prime fan myself I can spot when another one of us are meming.
All we really want from an ORM is type safety.
Yep, I end up using Prisma as my schema tool and Kysely as a query builder
In context of an android-app, Room from Google in combination with sqlite is really good and gives type safety and compile-time syntax checking of sql-statements.
@rychardvale you wouldn't happen to have any more info on that would you? Been using prisma for a month now for the type safety but been missing my knex query building, does this kysely hook into the prisma types?
And testing
Concur. "Micro ORM"s, like Dapper, that automate the Object-ification of query results are where the usefulness of ORMs start and finish.
Django's ORM has an amazing migration system and schema definition, managing indexes is easy, and you always have the option to use the DB connector and raw dog the SQL query. Flexibility and using different paradigms is the answer. They aren't mutually exclusive
Definitely the best part of the framework.
Sure the best part it's use RAW QUERY!!! WHY USE ORM at first if you need RAW query!!!!
I like the migration tool I hate the query generator, how Django ORM translate subquery is just a mess, if you're using subquery do it in raw, is easier and has better performance.
@@JaimeChereauBecause Django ORM is so flexible in all use-cases. You don't need to write raw sql 99% of the time, but when you do, the django.db.connection API is available.
What is not nice is debugging that django orm. It does lazy loading by default and when you look at the variables they are a mess of underscore functions.
I have spent 2.5 years on a old django application and let me tell you, it is not nice at all. Raw sql with something simple like express or flask is way easier to understand and maintain.
Migrating feature is nice at first but when you have a test db, local db and a production db which all need that migration applied it falls appart quick and becomes tedious. Rather have a sql script written up to quickly apply all the migrations needed.
1. ORMs, like most good abstractions, make 80% of the tedious work trivial, while still making the hard parts possible
2. You can ALWAYS go back to writing raw SQL, even with an ORM, but it's a lot harder to go the other way.
3. The chance that you actually need to do an optimization the ORM can't easily do (for example, his foreign key problem is easily solved with lazy annotations) is tiny for many categories of project
4. Writing raw SQL turns into string manipulation. SQL is also just objectively a horrible language, because it lacks any of the sane sugars, like functions, variables, etc.
5. At the absolute least, please use a Query Builder. They'll give you some level of type safety, syntax highlighting, and conditional building
6. Know SQL. Hell, don't use any ORM feature you couldn't (roughly) do yourself in SQL
You get it. Using native SQL in code is a highway to writing stored procedures which are inherently violations of the separation of concerns - a valid architectural decision to make in favor of performance. The trouble is that 90% of devs aren't great architects and lack the understanding of their flavor of SQL to create a competent bindings to stored procedures - why reveal that level of SQL and make your application more difficult to maintain in order to provide a boost of performance? In the same vein, why not use an ORM when all you're going to do with your native SQL is bind it to data models in your application code? ORMs were an emergent pattern that we realized we can design to make better. Every "hard problem" that an ORM ends up making is a violation of the SoC anyways. The database is only meant for data, let your API drive your database - do not consider your database anything more than a driver to provision data for your API/DAL. It makes me laugh when developers say "I don't use an ORM," because that means you're making one yourself to provision your data to make your application testable. While you're writing your ORM, I'll be working on actually solving business problems, which is what we're paid to do
Since when does SQL lack variables and functions?
@@psyoptic since we moved from them to use slightly better languages to develop our applications with, like Java, C#, Ruby, Go etc... since then SQL functions stopped existing for most of us :))
You have to learn another library on top of learning SQL to use an ORM. That's why I don't use it.
@@rohitreddy6794 When you use external libraries/packages - you also learn how to use them.
Another option is not to go with either ORM or SQL, but use them together.
A textbook example is using CQRS to separate your reads and writes and then using the ORM on the write side and raw SQL on the read side.
This way you can use ORM features that make your life easier for writes (identity map and in-memory cacheing, update detection, easily map complex properties to json columns etc.), and use raw SQL for reads/projections.
Also, some ORMs like EF Core can handle migrations as well.
Finally someone with common sense!
100% true, I hate devs blaming tools for their own mistakes. An ORM is a tool and it will mess up your application when used improperly. However this is not the fault of the ORM it's the fault of the developer using the wrong tool for the job.
As a dev you should know when an ORM is producting problematic code and write your own SQL in those cases. However this does not mean you have to write every single query in your application by hand.
Great take, I never thought of it that way, and it can make a lot of sense in some contexts!
@@Malekthegreat Agreed, knowing the rough edges/limitations of your tools is important, since it gives you a clear indication when not to use them. And if someone is not up for learning yet another new ORM DSL/API and wants to write SQL by hand, there are still tools like Rezoom.SQL (F#) which effectively give you statically typed SQL based on your DB schema.
this
"Don't focus on the tools, learn the skill of programming"
One of the best advices I got from a senior mentor of mine, I think this sums it up really well.
I once used an ORM to bind an existing TERRIBLE DB to a model that was easier to work with. Makes it easier to get back into it after a break, without having to learn the whole DB layout and relations again.
selecting * from DB?😮
@@alvarorodriguesschmidt8507 explodes.
That's why I am always rooting for Dapper, which just lets me write an SQL query, get exactly what I need and map it however I want - not forcing me to make 100% the same class. Well, you can kinda argue that Dapper is still an ORM (some call it mini ORM) - but I really appreciate that I can just use SQL instead of learning the API of another library someone decided is cool enough for everyone else to dig into. I still have to write SQL queries anyway when investigating/troubleshooting/experimenting with the schema declaration, to later 'translate' into the library's 'language'.
Oh, and what I also hate so much - when ORM decides to cache which tables are often joined and then add it to every other query I need. Troubleshooting these monsters to find out that this bugger decided I need another inner join which actually removes the data I need is torture.
Dapper is great 😊 I like its minimalism and straight forward approach.. EF Core is good, but I don't recomment to use it if you don't have strict understanding why you choosing it
Prime really needs to give Entity Framework Core a fair shake
ok ok ok
preach.
@@ThePrimeTimeagen I love the audience engagement here! In your defense, I think a lot of JS ORMs suck, but it's mostly because the language itself doesn't support the kinds of operations that make working with an ORM easy. EF Core is so convenient to work with thanks to strict typing, great interfaces like IQueryable, and Fluent APIs! Joins amount to a single .Include() call, and projections amount to a single .Select() call, and since the language supports dynamic types, you can cheat by not having to define a concrete class for your projections. Sure, you should still learn SQL for advanced things like window functions, partitioning etc., But if 99% of your logic is simple SELECT FROM WHERE GROUP BY ORDER BY, EF Core is really fast to develop in.
The difference between the poor ORM options you have in the JS ecosystem (Prisma, etc.) and EF Core is very much analogous to the difference between a 15 year-old pentium Dell laptop and an M1 Macbook. You can't have only worked with garbage laptops and then from that experience draw the conclusion that "laptops" are shit.
I feel like he would like Dapper better. It has less abstractions. EF is heavily framework and someone needs to understand the framework a lot in order to use it properly.
For standard MVC applications with lots of relations between tables, using an ORM makes development much faster, makes code easier to read, and makes mistakes less likely. It can also make refactoring a lot easier.
The user doesn't care about how easy it is for you as a dev to do your job. ORM's might help you not shoot yourself in the foot, but you can also just learn SQL and write your own barebone wrappers around complex queries. Honestly feels like a skill issue. That isn't to mention the price of having an entire layer of abstraction adding latency between you and the database, which is already hyper-optimized to query data efficiently.
@@laztheripperIt's 100% a skill issue
@@laztheripper You're not really making any points with what you've said. "The user doesn't care about how easy it is for you" for your argument is at best a moot point, and at worst implies it is best to use an ORM, if the user doesn't care about the tech, why not use the one that makes my life easier. "skill issue", yes, and? again, if what matters is the end product, does it matter if it is made by big brain dev writing everything by hand, or not big brain dev using a tool that lets them also do the job as well. "the price of having an entire layer of abstraction" the whole concept of a database is an abstraction already, the latency for an ORM's internals to generate the query is in practice negligible for most cases, so
@@pinkorcyanbutlong5651 yeah, go ask prisma to do a join
@@laztheripperI disagree with you with your first point, but agree with your second.
The first point if a bug happens, the ability for the current developer to quickly fix anything to improve the user experience is always going to be priority.
Second point I agree with, along with everything primeagen says as a newer viewer to his channel.
The only problem, is for the most part not a skill problem but a discipline issue for some devs.
Look at Regex. Its really, really useful if your job requires working with a lot of string data types, but barely any devs want to learn due to how daunting it seems.
Its the same with SQL. When I was newer with learning and coding projects, I just found certain things to learn more daunting when comparing other tools / language’s that I found learning easier. Thats what I did to SQL the first year learning to be a dev, and other time after the first year I learned vanilla SQL is just the way.
I really like Haskell's quasi quoters for this, where you can throw SQL strings into your code with what looks like string interpolation but is actually automatically sanitized
It always shocks me when I realize some of the shortcuts people take with learning software development. It never occured to me you would use ORMs before you've built applications and grown a healthy distaste for raw doggin SQL.
When I first started I was taught a bit of SQL to know how it works but then I never had to use it for any app, just directly using an ORM.
Shortcut is a shortcut for a reason. It is faster and easier to build applications that way. Later you can always return and study the details of how you did what. You don't always have to start from the ground up. Some people are not theoreticists, but ENGINEERS. Engineers like to build functional stuff from the very first day. Their learnining looks like Practice -> Theory.
More than that, I would argue that Engineers who start from the practical stuff always learn better and become better developers over shorter period of time, than those who started from full 0 and went up step by step. That's why I was able to become job ready in 4 months(and found it in 1 more month) but for other people it takes YEARS to do the same. The difference is in approaches to learning.
This is how it should go indeed IMHO. OTOH I've also been guilty of recommending an ORM to beginners sometimes because they just won't (don't want to) learn SQL properly, they just want to get their hobby projects done quickly, and ORMs rarely get in the way for those simple (typically little more than basic CRUD) cases (EDIT: and will always emit better SQL than the beginner would by hand).
EDIT2: Also ugh, what was mentioned in the video... the noob in question was formatting SQL by hand using user input and obviously no sanitization or escaping.
@@twothreeoneoneseventwoonefour5comment of the year
Distaste of raw SQL?
I would love, if people grow a distaste of ORMs like Hibernate, since many do not seem to understand what's happening behind it. (prime example: Lazy Loading)
As a Rails developer I heavily rely on ORM. I actually had to work on a time series oriented DB and boy, I had some of the problems he mentioned, specifically writing queries which rely on window function. When you know SQL a bit you tend to think how you would write the query and then back port it to ORM jargon, which is usually more complicated unless your query has to join and filter tables dynamically given different parameters. Creating reports is where ORMs really suck hard on. If you have to manually select fields, manually ask for distinct results, manually map native functions (no ORM does that for you), think about inner join or left outer join, understand when lazy load nested models helps you not to load the whole db vs when actually eager load them to avoid hundreds of small queries. Last but not least, query debugging... you need to know SQL.
Right, but i think you'd agree once you master SQL and ActiveRecord, then it's much nicer to use ActiveRecord than not use it in an App. Just knowing sql is good, but i would hate my life if i had to write raw sql all the time. Not to mention, imagine having to manage preloads yourself instead of having AR doing it, i mean you'd just have to use some kind of library there hand rolled or not, and you'd have to learn some kind of convention regardless.
As for functions and windows, you simply just write them as raw sql, so it's not any more difficult to use the orm.
Just reports!!! Only thing works really well in ORM it's CRUD or simple select, basically nothing more...
ActiveRecord is awesome. But a lot of devs fall into trap that I must do everything with ORM. Another bunch is I have to do it with Arel if I can't do it with AR. A lot of things can be done with plain SQL subqueries - `where("#{table.name}.id in (ids_sql)", binds)`, that's usable as normal scope. DB views are, work just like tables. Reports, exports or anything that requires complex SQL, write it in SQL, slap it in `find_by_sql`. And in the end there is always `ActiveRecord::Base.connection`. ORM is just a tool, must learn when to move to different approach.
ORMs save you a ton of boiler plate for simple/common operations. In ever ORM that I've used, you can use raw sql if you need to, while using the ORM most of the time.
oop with raw queries does that too and better utilization and better readability and better efficiency
For every minute of boilerplate an ORM saves you, it costs 10 days of "WHY DOESN'T THAT RELATIONSHIP WORK RIGHT!"
@@phazechange3345 BECAUSE U SUCK AT SQL RELATIONS
This was back around 1993 as I recall, and I was brought in to determine why it was taking around 30 seconds per check to print checks. They needed to print thousands of checks per run. Yeah, they had an obvious problem. The only real bottleneck should have been the printing speed of the laser printers themselves, not the data processing.
The first thing I did was to take a look at the database that the information being printed on the checks was coming from. INCREDIBLY, I saw the problem immediately. While the table contained the unique ID of the checks, that field in the table had not been defined as the unique ID and it had no index. I redefined that field in the table, and BAM!, problem instantly fixed.
This guy claims to have experience with ORMs like SQLAlchemy, but statements like "ORMs don't help manage data migration at all" are just plain wrong. I used alembic at multiple jobs and never had issues. Would much rather be doing this than hand rolling migrations with SQL.
yeah, that makes sense.
Data migrations are a usually simple alter table statements, nothing complex really
awesome podcast app tuo btw. why did u stop?
Entity Framework is the best ORM I've ever used. It's soooooo easy and works like a charm.
complexest orm ever used
good try satya
Yes, it works good when you know what you should NOT do with it, like multiple include, fetching readonly data with tracking etc..
@@mad_tthe same can be said about SQL.
its good enough, but by default I prefer to use Dapper.. because of simplicity and straight forward approach, as well as performance...
And also using EF only in case we clearly see some strong benefits of using it..
Thank you! Seriously, I'm so happy the community is moving back towards understanding the fundamentals instead of adding on unnecessary things
The thing with the 14 sql joins was he was trying to use row oriented transactional db to make reports that should’ve been built with at least columns oriented db or dimensional modeling.
Good ORMs implement "Identity map" and "Unit of work" patterns. They can handle transactions, they can lazy-load joined tables. They always provide query builders. Good ORMs optimize queries (Unit of work). You can do 95% of work using classes that push readability and productivity on another level and 5% using query builders.
If you are fighting your ORM then you've made a bad schema...
Examples of good ORMs?
All the orms you can't find on the list of bad ORMs.
@@ccgarciab MikroOrm for Typescript
@@ccgarciab The confusing example: Doctrine ORM in combination with Symfony framework. PHP.
BTW. High load? Never use ORM. Hire a skilled DBA that will manually tune your queries
@@ccgarciab SQLAlchemy that's quoted in the video is the single best ORM I've seen (and used) so far. I don't think I'm ever going back...
The author seems to be poisoned by hibernate and the shitty documentation around it. Hibernate is a dog shit ORM, because it does a ton of unexpected shit, that noone should really care about. However, it does generate SQL migrations, which means no ORM specific DDL bullshit. To not off yourself you have to have a relatively complex local setup, where you drop and create DB and run migrations and seed the DB on server startup, thus ensuring schema is in sync (pairing your ORM with something like Flyway).
Also, ORM are not replacement for writing or knowing SQL, they are useful for one thing and should be used for this thing only: mapping the query results. In any relatively complex application you will want to drink bleach if you write the queries and mappers by hand. Also also, if you have to wrestle your ORM to give you the query you want, it's probably a shitty ORM that tries to hide SQL. Try TypeORM if you are in JS world.
fair
I like the input sanitation / single place definitions for tables using something like SQLModel in python, I don't really use it for more than input sanitation, validation, and to make those objects self documenting, but you can still query a database however you want, knowing the set you have in there is nice and clean.
I burned so many hours dealing with hibernate. The most brittle thing in the world. Far worse than the borrow checker
I totally agree that Stored Procedures requires one to draw the line carefully. A stored procedure should be delivering data objects that are as agnostic to business logic, as possible. They give you a logic toolset that isn't available through vanilla SQL and run natively as a first class citizen in the DB engine, but need to be designed with the idea that they can have the opportunity to be shared, by other applications or functions, even if they never actually are. This helps to keep business logic out of the equation and leaves the SP to do what it is good at.
also not having to do a recompilation on your whole project (as long as the sproc returns the same fields) is a big win.
I... have actually built parts of an ORM in Java. To handle the simple case of turning a row into an object. It only handles simple cases though.
You can annotate a class with JPA annotations, and tell the api what class you want to turn the rows into, and you get a List back.
It's really easy to use, and works quite well, but it's also very specific to the current project, is very small and limited, so it's not like it's some sort of Hibernate alternative.
It was made so that raw-dogging SQL could be done more easily, by not having you translate row -> constructor yourself.
What I most hate about ORMs is that every ORM has its own API, so you spend a lot of time learning and fighting this API, I prefer to just use SQL, and finding help for SQL is a lot easier than for a specific ORM.
+1
I started my Java career working with the Hibernate ORM. Right in the Hibernate book written by the ORM creators, it starts with "Learn SQL first".
And you *can* tune most ORM queries to eager or lazy load joins/associations as needed. So performance can be fine.
But I just don't see how it's worth the additional learning curve when you must know SQL anyway.
one thing I like about ORM (maymbe more specific to hibernate) is the thing, that it wraps an API Request into a full transaction.
other then that, in a recent project with prisma, we more or less used it for the migration and types. Most of the time we used queryRaw
A lot of things are not true with dynamic language based ORM like prisma or django ORM.
The model(application code) can automatically translate into DDL, also when the model codes change, there's tool to write migration code automatically and keeps the history of migrations.
I think the article's experience of ORM is from hibernate, which is for java and also is very hard to automate, like migration, etc.
Also may I add that, ORM of static language like Java is more difficult to write intuitive query than dynamic languages like python or javascript due to language constraints
Jesus Christ imagine debugging and ensuring that your dynamic language reflects your database schema correctly and then also generates dynamic queries such that your database will not die from a few concurrent queries.
All the listed reasons make using SQLDelight great.
You write compile-time checked SQL where each SQL query is turned into parameterised function on a Kotlin class. It checks your queries by parsing your migrations to understand your Schema. Very handy.
Also supports custom adapters so you can use custom types as args or return values.
Disclaimer: It doesn’t understand all of Postgres syntax yet.
SQL Alchemy has Alembic which diffs your schema as you change it and provides a migrate, and restore function for each step, so you can apply and walk back multiple changes easily. Its a great tool. It also lets you choose the level of ORM you use there is a nice basic version which lets you do vanilla CRUD, and then a girthy layer which adds all the ORM shenanigans.
I'm on a team of using both. ORM / Active Records for simple things (but only if used framework gives some additional bonuses that really make work easier, not if you need to write that on top of some JDBC or other lower-level access). I mean CRUD-like things with a slight touch of relations or forms where you save few rows into one or two tables at most. Raw SQL for anything elseo, especially more complex like data views, tables, searches with relations, reports.
Even more complex reports - I use ETL / Python scripts that generate them from the data received by SQL and save it/cache regularly, don't generate in user session making user to wait.
What's wrong with just using sql? It's only disadvantage is if you move from mysql to postgress for example. There might be difference in syntax. Changing database does not happen all the time though. I prefer writing sql over using orm.
i still believe orms make sense for dbs like mongo because you arent meant to have foreign keys or whatever in them, but you would do a deeply nested document, which would create highly composed structures/fill in fields of highly composed structures in your code. for context, i believe r in orm means relating db and code, not relations between db entities
The "sql builders let you switch out database" is such an unhinged take. There is so much DB specific functionality that you're either not using (i.e. you're doing it wrong), or if you are you _can't_ actually "just" switch out your database.
It's like being cloud provider agnostic. Pure pipe dream, nobody "just" switches cloud providers. That doesn't mean that you'll never want to switch your DB, or never want to switch cloud providers - I'm saying unless what you're doing is so incredibly trivial that it's almost nonsensical to worry about it you'll have to change your code when you do _anyway_.
The only thing we use ORMs for is not having to write the migrations manually. Most ORMs allow you to basically use them as a query builder and that's what we do. For anything performance critical we use stored procedures or other DB specific functionality.
This is where I’ve arrived too. The migration tools are great, other that that it’s too slow for anything useful
Do you actually find that useful? Easy migrations are still easy to write with just SQL (or a sql builder for slightly more convenient syntax). If it's _not_ an easy migration (let's say anything more complicated than adding/removing a table/column), I really wouldn't trust an automatic migration anyway.
@@hannessteffenhagen61 I know that it’s easy to write basic migrations in SQL. Basically every migration tool I’ve used is very reliable and produces acceptable SQL. I never understood why I should bother writing versioned migrations manually. And in case something goes wrong you always have the option to fine tune it manually.
@@roccociccone597 I just don't see the point in automatising easy stuff I need to do _maybe_ once per week and that generally takes minutes to do that'll fail the second I try to do something nontrivial.
Automigrations are OK for local development if you're iterating on something, but why use them for production changes?
@@hannessteffenhagen61 I literally use it to run sql files up and down. I would call all of it nontrivial for what it’s worth.
I use dictionaries of bind variables to return dataframes for joins on other datasets. I also use if statements to dynamically change the where clause based on user selections. Hope I don’t regret this in the future but it’s working fine for my current needs.
I've used Django for many many years - it's the best ORM I've ever used (very easy to make complex queries), and it comes with a superb migration system as well.
Of course, one has to learn the ORM, and it does take a bit of effort to learn the Django ORM and be effective (make performant queries etc).
I think the article author would def. change their stance a bit if they knew for example how good Django can be.
And I think some of the issue they mention (like the identifier issue) is also an issue for raw-SQL. There's no reason you can't generate identifiers in code in an ORM.
Also - the join and attribute mess they mentioned - sounds like the DB was designed poorly. The hardest thing about working with a DB is trying to get the design of the tables right, IMHO.
Also, of course one should learn SQL as well.
And to add.. I've written complicated ORM statements with filtering, subqueries, HAVING clauses etc in a few lines of code that expand to 100s of lines of SQL - even duplicated bits of SQL. Would never want to hand write that.
Also, if one is doing something super unique and complex, they likely already know SQL and aren't trying to force it with an ORM. For 99.99% of "normal" use cases, good third normal form, and a good ORM+migration system, such as Django, is great!
You also get fantastic unit test suite (using SQLite if you like) for free! Can even run all tests in parallel.
It's a dream!
"Dual schema dangers" is for me the second most important reason to use django for my applications.
I write me schema once and I'm done (all the "batteries included" is reason no1).
Migrations are also basically provided for free. With django you don't rly thing about having a database attached, you're just working with your objects.
It's funny how you all didn't understand the 600 columns.
He's fetching ONE table which does 14 auto joins and that totals to 600 columns.
He only wanted 2 joins, but that's just a configuration error of the ORM / Query.
Watching Prime Reacts have made m think that reacting to stuff can actually be very good content.
What do you think about things like Ash Framework? Does that address the dual schema dangers points? I haven't actually tried it yet but I'm planning to soon-ish.
Feels extra good when the DBA supplies you with a more performant query and it takes a week of hibernate spelunking to figure out how to generate it.
Rawdog SQL when making quick prototypes on my own that probably won't see the end of the month.
ORM when making anything that needs to go into production and will get touched by others.
I know how to rawdog some SQL stuff... But can't expect my co-workers to be competent enough for it so I just want to lower the opportunities for them to screw up (which will cost *me* time and effort).
How do you deal with type safety when writing raw sql? Is there a way to type the results or does manually casting into a type turn out to be pretty okay?
This illustrates one of the biggest issues with typescript. Type safety doesn't actually mean type safety, it only refers to how you use the data on your end, not what the type actually is under the hood and it doesn't force type coersion or type checking at run time which in plain old javascript would force you to handle. The ORM abstracts this out and does it for you, but it's still happening.
I've been using ORM's with my Django projects for the past couple of months since it is built into the framework. But I think I need to get out of my comfort zone and learn how to use an actual database versus an ORM. Is it harder to use a database for a framework since SQLite3 is already built into the framework?
Thinking out loud I will go and read the docs on PostgreSQL and figure out how to do this, great video!
Small warning: you will hate any other ORM after Django. Others just don't.. feel right and support everything (migrations) you are used to, even if syntax is sometimes better
It's hard to get visibility into what query is broken when you change your db in raw sql, if you use an entity generator, you just regenerate off the new schema and go fix where the ide puts the red squigly sad computer line.
they don't make writing easier though. Aside from stopping insane sql tricks, like queries that assemble strings into other queries.
It's exhausting having hot takes coming from a random blog, in the chat, and in the picture in picture simultaneously. Then you hear, "new tweets this morning" and I can feel myself becoming a grandpadev by the minute.
My take is that ORM are the natural continuity of defining your entities (think DDD) in your code for business logic and wanting to have a declarative db schema (with automated migration). Since most of the time it is very similar, might as well prevent repetition. Then it's only natural to try to automate the bridge. But it leads to all this mess.
As a Pythonista today, I use Pydantic to define the data model of my domain so I can get JSON schemas, which I can turn into a declarative db schema, but I write my own queries to get the data and let pydantic parse it.
I remember I just had queries as SQL files one project, and then had a bash script the would pop em all into map[string]string for each package, when go generate was run, gave me the beautiful looking query which was also nice to use, and without performance penalty of loading a file
Django ORM saves a lot of time. It not only hide the complexity of SQL joins but also provides signals to run something after the record is updated or created. It's a very productive tool and still opened for raw SQL requests.
Django or Tortoise builder pattern into lazy evaluating querysets are awesome
generally in python all orms, so peewee and pony, even alchemy, are really nice tools
ORMs ensure your code maps directly to the database. When the ORM doesn't work is when you're writing custom reports with complex queries. So yes, you need to learn SQL even if you use an ORM for basic CRUD functionality.
A good ORM will also help you find sloppy database design so you can clean that up.
Yeah, you don't use an ORM to do complex selects. The only thing my ORM supports is AND and that's on purpose. If you need so much as an OR clause, you're writing SQL and then returning the result set as custom strong-typed objects.
People like to find a hammer and then think they can ignore the rest of the toolbox.
I just want to add that ORMs also can make your database more stupid by simply not letting you access very useful database features. I saw people doing crazy DB round-trips instead of just creating and using a view or a stored procedure. If your colleague is also a backend "CRUD purist" then you will need to write your joins on frontend...
Exactly! Even when working with a proprietary program DB you can not change, Views can be put into separate database, and then used via ORM model.
This might be the only time I disagree with you prime. The number one benefit for an ORM is speed of dev. You don't spend precious time thinking about what you want to fetch/write. For complex queries you use the query builder. It's like saying why use tailwind learn css. Of course learn SQL, but why not use an ORM for up 90% of database related work when it can do the heavylift and optimize when necessary ? Seems unwarranted.
Agreed. Even though most of our codebase deals with complex objects that have lots of attributes, our production database spends far more time performing simple `SELECT * FROM ... WHERE ...` queries on tables with just a couple of columns but lots of rows. Those queries are perfectly well-suited for ORMs.
@@yyny0 I agree. I don't see myself passing out on a data mapper, a query builder, a caching layer, a persistence/update event emitter, encapsulation, and this has nothing to do with my ability to correctly use SQL and an SGBD ... On most complex systems I've seen the view does not relate to the schema. Fortunately, we have other patterns to alleviate this. And I'd always argue to start cheap. Why use a hot tablespace or table partitioning if a simple cache layer fixes this with a small to no latency whether it is a full fledged reverse proxy or nextjs function ? Our first job is to keep a good time/cost to RoI balance.
ORM is mostly for easy typing. But yeah, any SQL library (whether it's an ORM or not) will allow you to do prepared statements to prevent sql injection.
JDBI3 ended up being the best tradeoff for Java--raw dog SQL but offloading the tedious work of marshaling and unmarshalling, and also adds some type safety
is there anything like it for JS?
Besides migrations, Prisma offers great Typescript typings. All your enums and querries come out perfectly typed.
I did DBI and result rows in Perl when I started. Everything has trade-offs but I'll take the ORM ones generally. This OP is writing time-series reports, I'm not. I'm doing basic stuff that is not edge-case or pushing the envelope. The ORM should have an escape hatch. Yes, everyone should learn SQL but for the "forever skill" reason (to me).
One thing that I miss in Rails is the dev log shows you the SQL while you work. And there are gems like bullet and loldba which are almost like linters for N+1 and missing indices.
I think Hasura is great if you need some shortcuts, an immediate UI, and are fine with using graphql.
I'm mostly using sqlc (writing pure SQL which then gets built into a type-safe client for me to use in the app code), I tried gorm but it's built using reflection so it's pretty slow and it makes it difficult to find errors. I've also used drizzle which is pretty good, but it doesn't take long until you are writing unusual code just to get typescript to understand what is supposed to happen.
@@GlassEyes have you tried Jet?
The argument about too many joins being generated, in my experience you can manipulate that, at least you can when you use hibernate ORM, by setting fetch type Lazy or Eager, if you want your data to be fetched lazily (in a separate query) or eagerly ( hibernate generates join statement). However, my approach is to use both, ORM for simple CRUD operations on entities and SQL for more complex data fetching/manipulating.
Nearly everyone is missing the fact that between orm and native sql sitting the dbal .. and this is what we love and need ❤
I partially agree with your statement but in many cases companies have to build entire applications from scratch (or even migrate from an existing XLSX) with a small team of developers in less than a year. For those cases usually you have two options, use an already existing ORM which implements the Repository pattern, (or an SQLBuilder), or implement your own. It's all about time and productivity
I have been writing SQL for 30 years! Every few years a new ORM comes out and companies want to adopt it. Luckily we always manage to talk them out of it.
I am writing a customer application using a simple 3 tiered architecture, Angular, C#, and SQL. That is a killer combination. I use stored procedures for complex queries and have built highly optimized databases with just the right indexes.
The performance is insane! Data comes up on my screen like the shutter of a camera! I move around bouncing from screen to screen, retrieving data and there's never even a millisecond of lag!
"If it ain't broke, don't fix it." I will stick with SQL unless or until something REALLY better comes along.
I really like the mapping ability ORMs give you, but I don't like all the clutter it introduces for the multi database support part. I wanted to have something in between like Dapper so I could map my entities and query results in code so they are typed once I receive the query result. I don't like having to manually type check everything for every project.
Agreed, but I use SP's for every interaction with the DB.
I’m so happy I stopped using JS on the backend years ago. In Java Spring Boot you can easily create an interface with some methods, and implement whatever in your repository. Use the ORM, or easily swap it out with SQL methods. Migrations are super easy with flyway or liquibase.
Primary issue with store procedures is multi point deployment. You need to deploy your app, and then all other procedures. And deployment of procedures was a mess in any db system I have used. Essentialy I have not seen a database where you can deploy your procedures as versioned artifact.
I use them sporadically - as form of optimization, and for bookeping jobs like partition creation or reporting etc.
There are many benefits with using ORMs. Especially in an OO/MVC application. For example:
- All the objects get hydrated automatically. You get objects and collections of objects directly that you can work with in in your app.
- It is much easier to read and understand. For example Model.where('col', '=', 1).with(relation).get() on your Model instead of dealing with joins. Long and complex SQL queries are usually a lot harder to understand.
- You can abstract away what kind of database you are using.
- You get a lot of additional tools for migrations, seeding, streams etc.
- If you are not good at SQL you will probably write better queries with an ORM/query builder.
> - All the objects get hydrated automatically. You get objects and collections of objects directly that you can work with in in your app.
Automagic, and then you wonder where the performance has gone.
> - It is much easier to read and understand. For example Model.where('col', '=', 1).with(relation).get() or Model instead of dealing with joins. Long and complex SQL queries are usually a lot harder to understand.
You can use SQL builders.
> - You can abstract away what kind of database you are using.
You can do that by just using ANSI SQL, lol.
> - You get a lot of additional tools for migrations, seeding, streams etc.
I think that's the only reason why I would use migrations.
> - If you are not good at SQL you will probably write better queries with an ORM/query builder.
then get good in it ffs. It's not that hard. It's easier than Rust, tbh. It's like never learning how to use a fork.
a) that may come at a cost, like cardinal queries because of lazy loaded joined classed or even worse, load the entire database cause you are querying the main domain model.
b) debugging a query builder ( usual case scenario, you build a query depending on parameters being passed ) still requires you to know how to read complex SQL queries. This is the only scenario in which patching a sql query string might be more confusing than using an OOP query builder and the reason why Rails developers learn Arel
c) you'll never change the database. Never happened once in my 20 yrs long career. When it happens it's because you've been tasked to rewrite the whole thing. Anyway no ORM abstracts away native functions, only SQL dialects.
d) doesn't mean you have to use the whole package
e) ORMs do a lot of work but still requires you to know what you are doing so there's not much you can do without knowing SQL, easy stuff.
- ORM can also provide caching speading up your application significantly
- They provide attribute converters
- They help you out with different Indexing strategies
- It can help you with cascading operations and so on...
- It can significantly help you to focus on rather object oriented models than tabular format of your data (this is especially good if you are trying to avoid anemic objects as your applications first class citizens)
There are so meany ways that ORM's can save you time if you have mastered them...
@@peppybocan
> Hydration does not give you a significant performance penalty if you are not doing some crazy things. Productivity is typically much more important than small optimisations.
> A good ORM is often very similar to an SQL builder + some extra functionality.
> No, you still need to use things that are specific to your database engine sometimes.
> Why wouldn't you use migrations?
> Sure, it's not that hard and it is of course good to know SQL even if you are using an ORM or an SQL builder. However, the ORM lets you focus on other things, that might be a lot more valuable, if you want
@@ClaudioBrogliato
a) Sure, sometimes you end up doing stupid things if you don't know what is happening under the hood, but in most cases it's pretty easy to fix. You can for example eager load what you want.
b) Yes, knowing underlaying technologies like SQL is usually good. But you should probably focus on the areas where you deliver the most value.
c) Yes, that is not common. But you don't need to worry about what SQL dialect you are using at all. You can work with different databases in different projects for example.
d) No, but that makes it a lot easier. And pulling in a whole ORM just to get for example migrations might not be the best solution.
e) Same as b.
The ORMs were created to distance the programmer from the database, with the initial idea of not having to know SQL, which we know did not work and with the idea that EVERY project at some point will change database engines, at appear hundreds of times, which is not true either. I totally agree and the best solution is the RAW query. If it's true, the migrations are a good idea, but the work to reach a DIFF between one model and another in plain text, I don't know if it's worth it.
Real talk here; ORM's are never meant to skip learning SQL and how RDBMS work.
They are just there to save you from writing and maintaining boilerplate CRUD operation SQL's in a forever changing DB DDL.
We used Hibernate in my previous company, we mainly used it for basic CRUD operations, for anything complex we either wrote 1 - HQL, 2- raw SQL or 3 - A view. I never had to maintain any POJO's CRUD SQL. We never had any performance issue we wouldn't have with raw SQL. We were able to use hibernate to report POJO vs DDL mismatch (We were maintaining multi-tenant DB's each having their own).
We had best of both worlds, still miss using Hibernate.
- A Regular IBatis Hater
I think the ORM bad also has a lot to do with the language. Ecto for Elixir for example follows the Repo pattern, requires explicit eager or lazy loading of relationships, and has a SQL builder built-in, and all changes to data are handled by a dedicated "changeset" struct.
I think writing raw SQL is not needed. Choose the best SQL builder for your language and use that. Compose SQL queries and pass Queries around like types.
I know SQL, I sometimes use a query builder but am moving baco to raw dogging that RDBMS. I like the author’s mental model of “just another API,” that’s a good approach.
I use Mongo without ORMs. The funny thing is that i like it that way. For type safety and validation i have interface classes that will validate the structure of the data and convert to appropiate types. If i will write, i will receive the input, validate it and transform the json t the appropiate type. If i am reading i will fetch the data and do the same but in reverse. the only downside i see is just really that my models only exist at app level and not at DB level
triggers and stored procedures are good but depends on what you are doing. plus they are normally not checked into the codebase so they are hidden.
You don't use schema versioning like flyway?
@@romanfunk3546 no I am a ruby on rails developer right now
I've been using an ORM for my last project. After things were ready for prod, the biggest lesson I've learnt was: I need to learn SQL.
@0:02 Everything's said and done. To every junior reading this: Learn SQL- Period.
This is why drizzle I believe is so popular. It allows the Dev to write type safe SQL while being able to control the underlying query (if you need to).
drizzle on deez nuts
it's popular because it's javascript. And the only main option before then was TypeORM which was a beast but slow and just behind the times. Drizzle really isn't anything new or special tbh. Many languages have thin type safe SQL builders
This is the second time I see this, what do people mean by type safe sql?
You think a int column would turn into a text column out of nowhere?
@@SpikeTauntits auto-complete lol
Feel like you missed an option, auto generating boilerplate code from raw dog SQL queries (something like sqlc in golang). It's the reverse of an ORM but you still get type safety
Drizzle is cool but Kysely is where it's really at
Yeah prisma is the only ORM I have fun using. Even just use the prisma studio instead of pgadmin and stuff even if I don't use JS at all. It feels really SQL with its query system anyhow, not sure though how much it bloats queried attributes, doesn't feel like "SELECT *" though. Has a single schema definition if you use JS/TS.
I was asked once to add a new field to the system that was using a framework with the orm. I was curious about the way it was working, so before I did my thing I've looked into the documentary to see if there's a debug function. I've found it and put into the system. Oh boy ... I've got no idea if that was a framework thing, or maybe the dev that made the script did something wrong, but there were hundreds of queries. Some of them were doing exactly the same thing over and over again. It was even worse than in Wordpress with the fancy template and 5 plugins.
The *only* issue I have with SQL most of the time is language's handling of multi-line strings. The code ends up looking horrible.
But that's a language issue, and it's not with SQL; I find SQL itself to be pretty enjoyable to write and think about.
In my experience some kind of data abstraction becomes necessary at some point. Given that why not find something powerful and well made instead of trying to roll your own.
I agree ORMs are fine for a start up/MVP but nearly always scale to the point where SQL is better. ORMs are also a bunch of anti patterns from a relational database point of view. Personally I go straight to SQL. SQL builder libraries are fine dynamic SQL is often required for example report builder functionality which doesn’t need to be reinvented for each project.
What input sanitization? You mostly want to use parameterized queries which disassociate the query structure from the parameters used.
I definitely agree with this, and I think a good happy medium is libs like sqlc in Go or pgtyped in ts
Late, but stuck in Django land which is super heavy on ORM. Perk is it fully manages the database so migrations are pretty solid, and if you don’t now sql it makes it easy to get the info you need.
You can also slap methods onto the ORMs, for instance just created a Printer ORM today and added a print method, so if you want to send something to a printer it’s just Printer.objects.get(name=“That one”).print(template, context). That keeps all the logic required to interact with the printers in a single location, and someone who is not me can easily manipulate the printers and send data to them without having to know mqtt or any other technical crap.
That said, if it was my choice I’d have chosen a TS framework over a python one and abstracted sql queries away with workers.
Java has a library called JDBI that exposes SQL really nicely. Not an orm, just an ergonomic way to work with squeal
Worked with SQL and Mongoose as ORM? Isn't that for MondoDB? Re attributes, don't know what he means by it, but that's how I would call an improvisational table for storing 'random' key: value pairs (attributes one couldn't anticipate.) for a table X. I think some people might use meta instead of attributes, tho maybe only under specific circumstances.
I'm currently using entgo for a project and the ORM functionality is honestly the least interesting to me.
Basically, I can define my schema and I get
- types for my go codebase
- query builders for db access
- a complete graphql API
- protobuf types for other services that use the same model
- hooks that allow me to implement transparent application side en-/decryption of sensitive fields on the data access level
- a rule engine that allows me to implement efficient, role based authorization on the data access level
For a codebase that's being reused in multiple downstream projects that's just immensely useful
On my previous job all the querries that required more then one table wew stored on db, so in app if you wanted anything you just do a simple select with filter
The wild card here is having kafka store all your data and handle all your business logic, and having databases basically just be an incrementally maintained materialized view
For my simple app, I'm on the Prisma+Kysely train. Prisma for schema definition and buttery-smooth migration, Kysely for the type-safety.
Now for my chonky boy (largest table is 14 billion rows), I made my own SQL builder for my Clickhouse and MySQL dbs that does things in the way I need them to. (I raw dog the schema.) This is primarily because I have microservices that must output the exact same data based on a given set of inputs, and having a centralized sql builder that I made that I know how it works guarantees this. Secondarily, it's because I have to do some really funky stuff with window functions and joins that I wouldn't be able to remember jack about how I to do any of it.
I like ORMS, but i only use the for the mapping of the sql result to my defined objects. I usually just write my sql on my own, because i do not see the point of writing pseudo sql, just so i then have to guess what the ORM actually made out of it.
at 5:46 the same could be said for manual memory management vs GC, like people seems to spend so much time and efforts fighting the GC of their language, pulling up insane Data structures and algorithm, when all of your problem could have been solved, with malloc and free lol. I think people just fking love complexity.
Depends on the type of ORM that you use, language, framework, and who implemented it
Most devs don't even know the different between Active Record vs Data Mapper
Java Hybernate is weird as heck coming from Laravel(PHP) Eloquent where the methods for the query are basically named like the equivalent from SQL (mostly because it's written to work over the query builder).
I've seen some projects on the god language that is JAVA where people broke the ORM because they wanted to add a fancy abstraction that they didn't even know how to explain to the team
We're currently using Larvavel with 100% Eloquent for the back end with a custom API and Vue for the front end. We are using Telescope to test. As long as we are cognizant of the pitfalls I don't see many drawbacks to taking this approach. As a Laravel dev yourself do you see any issues or have any advice?
11:21 domain duplication happens everywhere. Any client calling a server will have a duplicated domain definition. Any actor using a protocol will have a duplicated domain definition. This is only made worse when the clients/actors/servers are written in different languages.
How do you dynamically build queries in an stored procedure, though? And no, building a query string in a stored procedure and then running that is not a sane option.
ORMs are not really meant to replace querying the database but more for mapping tables to class entities.
What bothers me the most about ORMs is that you'll get that one guy who claims to love the ORM, but then never actually uses it outside of the primary application.
...and those people who are using an ORM that doesn't have a REPL, what are you even doing?! The whole point is that you should be able to very quickly do one-off queries.
Switching from Gorm to SQL-C helped me finding sanity again.
Refactoring this took unfortunately long as structs defining the Models tend to get passed out of pure DB-domain, as they give a false sense of sufficient abstraction level.
Using SQL-C I do not get attempted to clutter the rest of my application with simply throwing around direct model-instances of my DB-layer.