Been using edgedb with typescript and its such a joy. I wrote a simple script that creates orm like classes by inspecting the db. Any change in the db schema is now more or less directly reflected in my code. Since the generated classes are just a wrapper around the typesafe query builder, you are not required to use them at all. I am more than happy that i moved away from the orm pain.
I think ORM's are worth the hassle in a number of situations. I am a big fan of 'convention over configuration' and have found ORM's to help guide developers to think more about their models and how they persist data, rather than DB-first architecture. For some devs, its hard to show them just how bad their DB schema design is until they have to interact with it using an ORM. The positives far outweigh the negatives, in my experience.
yeah the old plain method is good when you have a simple DB design that is like sculpted on a stone, most of the projects unfortunately are a complete mess to put it kindly, and an orm helps alot
This is completely backwards and no, the reason you listed is precisely why ORMs are bad. I have been working on a db that was "designed by ORM" (hibernate). And it was a mess. 300+ tables, 20+ empty many to many join tables, duplicated data, deprecated columns, etc. I'm not even going to talk about fetching data in a for loop all over the code. So how do you go and optimize this? You cache everything on server start up of course (load important data into datastructures on the server), making the server consume 7GB... So no, if you don't know how you interact with database, the ORM can only make it worse, not better. However, the reason ORMs can be good is the M part. I have been impressed by TypeORM which is for me the best ORM by far. You have the mapping of the schema to object, ORM generates the migration, which you can of course modify, you have a lot of control over what queries get executed (it would be possible to do what Chris did in the video with it) and how the result gets mapped, saving you a lot of time you would need to write your own mappers. The video strawmans what ORMs actually do. If you have to only map one table, then ORM is overkill, but if you have to map relationships, then ORM will help you achieve that way faster.
@@sarabwt typeorm also support activemodel pattern, which i kinda prefer to the repository dao pattern, but yeah, projects can be a mess expecially when a lot of juniors and just bad programmers work on it during the years...
@@sharkpyro93 Interesting, I prefer repository, but I don't have a good reason for it :D This is the thing, the guys working on this were not some random plebs, but uni professors and lab assistants... CS doctors... If they make this kind of a mess with these tooling, what chance do the juniors have? xD
I think you pointed the most important part very well. As long as everything is super simple, ORM can be beneficial for those who want to stay away from SQL. But I have seen, in each and every project I worked on, that ORM has its limitations. Depending on the ORM super simple things like creating a GIN index on a property stored in a jsonb field can be impossible. I also have the issue when colleagues are working with ORM that they try hilarious things because things just don't work as in plain SQL and then everything is much harder to debug and review. Often they just give up and ask if I can help them with a query. More often than not, we have to rewrite queries to raw SQL for performance reasons because of ORM limitations. So also my conclusion is that raw SQL is way more powerful when used by someone who knows what he/she is doing.
I don't mind ORM, but I like things seperated. There is a database, use psql straight from the terminal There is backend, mostly an api/smartcontract/automation/... , use rust or js There is a frontend, use html css and js There is a UI, use Figma The complexity is already wild for applications, to I keep things seperated helps to not go nuts^ ^ ORM are nice, but they also add another point of failure. 🐒
Happy to see a video about an alternative to ORMs. I swore them off long ago, and thought enough other people had done so that the wider programming community would at least be aware that not everyone was fawning over ORMs and that there are other options. Then I saw a video criticizing the repository pattern because it doesn't make sense on top of an ORM. the original repository pattern is to me clearly an alternative to an ORM. I was stunned by the degree to which ORMs were taken for granted. compile-checked SQL queries have a lot of value. However, it's not uncommon for an application to need to build queries dynamically from query fragments. Even if the fragments are made from compiled SQL, combining the fragments dynamically requires functions, and no matter how much those function names resemble SQL and no matter how fluent the syntax is, it will probably be considered a DSL. It's also not immediately clear if SQLx can check query fragments. Directly working with SQL queries also means working in terms of the current database schema. The developer is frequently needing to do domain-schema mapping in their head, and in the case of a schema change, they can need to do a lot more work compared to if there was a tool mapping domain-oriented queries to schema-oriented queries based on a separately-defined schema. Checking queries against a real database can be useful, but checking them against a separately defined is often more convenient and practical. And of course, SQLx only works with databases that accept SQL. If a project moves to a NoSQL database, that can be a lot of redoing. SQLx ultimately strikes me as a very incomplete solution for major projects. Since the description says ORMs have been built on top of it, I'm hoping it can serve as a component of a more powerful non-ORM tool. When it comes to tools that can supplant ORMs, the Slick library for Scala comes to mind (note: Google won't find it unless you specify Scala). It lets you perform fairly arbitrary mappings from SQL schema to domain objects and perform SQL-like queries on them, and the library will perform appropriate SQL queries and convert the results. It differs from what I see in ORMs in that there is a very clear and hard boundary between querying the database through the lens of domain objects and using such objects in normal code. When I last looked at it, it had a lot of room for improvement, but it's been at least a year, so those improvements may have already been done. I'm also hopeful that Rust's macros will allow even better implementations of the idea.
Thank you for taking time to write such a long and thoughtful comment. Yes sadly we will have generate some sql dynamically, i dont see a way around it. SQLx has a query builder, which is pretty much a string builder that takes unchecked sql fragments. How inconvenient that might seem, i still prefer it over a library with a fluent query builder in the application language. Its not that hard to set up an integration test to verify that it works and does not regress. If you are moving your database from sql to nosql, it has been my experience that changes had to be done even with the use of an orm. It might be the case that its easier to migrate to a nosql database if you are using plain sql as your app data query language. The reason being that a sql string sticks out, and you dont want them all over your application, therefore you wrap them in functions that handle the mapping from db to the optimial format for you application. So all you need to do is to replace the sql with the native query language of your new data store. This is in contrast to how i think orms are used. They look like a very clean abstraction over you db, which makes it easier to pass into parts of your application and the mapping of db data to app structures are done ad-hoc in the place where its needed but shouldn't be done. This might not be what you meant, but SQLx have a feature to extract the info needed from db into a json format, which can then be used during compilation. Its meant for situations where you dont have a db running. I use that in my build pipeline. And, yes, i have forgotten to run the tool to regenerate the json file and have it fail in build due to this a couple of times already. 🙈 I found the slick scala. Will have a look for good ideas 👍.
Your demo of SqlX was really eye-opening. I didn't realize that typing info would be propagated up all the way to the language server. I wonder if there is a library that could do similarly for TypeScript, while not having to switch to a whole new DB like edgedb. Thinking of the comments on refactoring, conceptually the model that SqlX produces could be generated into files and those could be imported into the project allowing the models to be refactored such that field and table names could be changed in one location. This would remove some of the flexibility and agility of the SqlX approach though. Thanks for sharing.
Eh. Sure, for some complex queries you might be better off with raw SQL than using ORM. But ORMs tend to do much more: persistence, associations management, transactions management, type coercion, dirty tracking, etc.
Not to say that it's not nice to already have it when you really need it. Simplifies a lot. I wonder though, couldn't every one of these automatic features be implemented as decorators around your query object or something. And then you could explicitly opt in when it's needed.
In ORMs like Spring Data JDBC or JPA, you can absolutely do this sort of typed SQL query thing in the Repository interface. You just have to annotate the method with a @Query() where you write your SQL and make sure the return type is correct, like User or List
Also, many older programs that do use raw SQL for querying dbs seem to concatenate many strings together, eg statements in the where clause are dynamically built, or the selected columns are dynamically built. The code to do that is often strewn across the application, so it becomes almost impossible to tell what the sql statement will be without actually running the code and logging the query. Those are very frustrating apps to work with.
clearly you havent had to use dynamic queries with an orm, its even harder to understand whats going on, and half the time you run into issues you wouldn't have had with plain sql
But that's not an ORM issue... I'm currently working on an application which is just like that. There is nothing preventing a "plain SQL" application from using the repository or (if you really really have to) the Active Record design patterns and separating their concerns but we seem to live in a world of extremes where an application is over-engineered with classes on top of classes on top of classes (this is where we most often see ORMs) and under-engineered with SQL, HTML, PHP and Javascript all mashed up in one file...... actually, I'm working on an application at the moment which is BOTH. :(
@@ed_iz_ed Yeah... I'm used to the Doctrine ORM where almost everything is possible (eventually!!!) But recently I've been working with Eloquent and it takes such a very long long long time trying to make it's over-complex "architecture" fit in with our legacy database.
ORM's are just a try to simplify a complex matter. If you want to have the best performance in both development and processing you need to know a lot about persistance data storage and how to read from it. In my opinion the only real use case for ORM's is to use them while learning programming. If you'r goal is to produce a stable scalable application you have to dig into SQL and use it raw. Don't avoid usinig dynamic sql as well - it's like a magic wand when applied right. Thx for the vid!
But the point of an ORM is not to abstract away SQL... they are not an alternative to SQL for data access (although most do this a a side effect). The point of ORMs is to do Object-Relational Mapping. It's not the data querying that they're trying to simplify it's the mapping of the returned data to an object format. As an exercise, find a complex database and do a simple SQL to query that data to about 3 levels of LEFT JOIN... and then render that dataset in JSON.... That's what ORMs are for.
@@edgeeffect You are perfect right. I personally love Dapper - that does exactly what you say. Experience has told me that an idea of an object is neither the object in code like a class nor the persistant representation. When it comes to store objects you have to keep in mind in which way you want to query them. When it comes to business logic it's the class'es abstraction that helps you implement your idea. Many ORMs try to sell you those two different conecpts as one. That's where I'm having the problem with it. But you are right. In order to work with records of a database you have to have some easy way to map between a query result and a class.
@@edgeeffect As the video uploader said in the first minute of the video (and the reply above me) - returning results in an object format is useful, nobody thinks it's not - but there are libraries available which will do that for you without the abstraction of the way you write queries. ORMs also tend to write very poor performing queries, and you are going to run into performance issues sooner rather than later. Worse is, once you do run into performance issues, you have no way to find the query in reverse (go from SQL back to code) in order to find the problematic code. To clarify, from the database side if you see that you have certain queries which are hogging resources, you can't simply ctrl+f in your code to find where in your code that query is being executed, because the query isn't in your code at all. You have to either understand your app very well so that you can make an educated guess to where/what in your code is generating that query, or if it's from code written by someone else, you end up going hunting and taking guesses. Even if you know how to use the ORM well, it's so easy to write bad code. I'll give you an example. In C# personally I use Dapper like the guy above (write raw SQL and get back a C# object), but Entity Framework is an ORM that abstracts the SQL away into code and was one of those mentioned in the video. Just last week one of our teams that use Entity Framework had a query that took 58 seconds to run, which should have run in milliseconds, and it was causing performance issues for all users. The reason was because the generated query had no "where" clause. So the application was querying the entire table into memory then filtering the rows needed in the application in C# code. If they had written the SQL by hand, they'd have to be doing it on purpose to write the app in such an ignorant way. When I notified the team of the problematic query, they spent 2 hours trying to find in their code where that query was coming from before they could start to fix it. Honestly I have problems with this team every few weeks because of bad queries, selecting excessive number of rows or columns or unnecessary joins etc.
I both agree and disagree lol. For example, if I change a column name, I need find all the sql statements that contain that column name and manually change it. It’s not as easy as right-click rename, or a find and replace, as the sql is declarative and may or may not be appended by a table name or alias and the column name may be used in multiple tables like “name” or “description”. So to make any schema changes, you need to explicitly find understand and update the sql as necessary.
In the video I use sqlx which does schema validation as part of the compilation. If your language doesn't support that, there might be a linter you can run which would give you the places where you are doing broken queries, due to mismatch in names. But I will agree that a simple rename refractor is easier where you have a orm in the mix. Although that's not something you should do very often as it will lock the whole table including reads if I remeber correctly. Atleast in postgres. 😅
Hmmm This usually means your code repeats itself and might have a coupling issue... Technically, you should not create 30 times the same-ish sql query (like 30 kind of select on an entity). - Why? It makes your code more fragile to those kind of changes. The ORM will protect for simple changes like those but won't for huge one (example: change database technology) The best approach is to isolate the database implementation behind a boundary of some sort like a group of function or implementations that modularise its uses. Exemple: create a class, the one he queries with works, and create member functions (or static for selects) that does your query for you. With that approach, you only need to check the models (which you would anyway) with a standard refactoring. What happens in major refactor? You adapt only the models classes/implementation which is far less work than reworking every call one by one. Why? Because your application does not know about what tech you are using. Your code is safe from tech changes because it does not know about what tech is used under the hood. Why even care about those changes? Trust me, those kind of changes are very rare but they comes out of nowhere. It happened to me in a project... We wasted SO MUCH TIME remaking query by hand and dumping our ORM (prisma). Could we have found another ORM instead? Not really, we didn't have time to do so. We all knew sql so it had to do.
@@elvarght the whole point of sql is to be able to be hyper flexible with queries and potentially be same same but maybe joined and or aggregated in different ways. It’s inherently going to cause repetitions in code, which isn’t necessarily a bad thing. Using ORMs actually make these kinds of changes easier because they do act as a boundary layer to the database. And yes a boundary layer between your public api and the internals is beneficial so you can limit the effect of internal changes on your public apis. But the author of this post is suggesting to do away with ORMs, so we need to think about the trade offs that means. Our domain model is always going to change and grow, and our persisted data model is always going to change and grow too, and any and every query needs to adapt to that. The fact that sql can really only be represented as strings is the main problem. We will never get native sql support because each database has different syntax and even different paradigms like relations vs graph. And although plugins like what the author suggested may help to add syntax highlighting and refactor support, unless those tools have access to the actual underlying database engine and can understand the queries in those strings, those tools are always going to be less than 100% effective. And then there’s the problem that strings can be concatenated and manipulated. Sql parts existing in different strings and concatenated with different sql parts. At some point it surely becomes impossible for those tools to be useful.
@@br3nto Hmm I see what you mean... I still feel like with a good code structure, you don't "need" ORMs. It does simplify some things tho. Tho you should never concat string with sql queries... never do a raw query even with a tool as simple as this one.
The problem with Active Record (the design pattern not any framework or library that might implement it) goes far beyond ORM, I'm working on a system where we have no ORM but still configure our data access layer as a collection of Active Records. Active Records seem to break Single Responsibility where data objects end up having to "schlep" around everything involved in query and update as well as data. This isn't so much of a problem if you're just updating one record and maybe a couple of dependent records. But when you're doing a big query for (e.g.) a financial report the overhead becomes unbearable. This is less of a problem in Rust than (e.g.) Ruby or PHP as there are compiler optimisations. I'm no advocate of ORMs - they always seem to be needlessly "dense" and over-complex - but it's quite ironic if we have a large query-result perhaps with many joins and we want to render this into JSON... The very definition of what we need to do there is Object Relational Mapping, and without an ORM to "do it's magic" this is a non-trivial problem. Again, if you just have one record with maybe a couple of dependent records, it's no problem - but once the joined tables start growing it gets surprising complex very quickly. ORMs are a tricky question because no matter what answer you have to "the ORM question" - it's wrong! Use them - wrong! Don't use them - wrong! Hide under the table and wish it would stop - wrong! ;) Best answer I've seen so far is the Doctrine ORM (in PHP land) that doesn't use the Active Record Pattern, and does it's best to try to overcome the inherent limitations of using an ORM... It's not a good answer, but it's the best I've found so far.... I often wonder if there's an ORM that uses The Repositry and Unit of Work patterns alongside Plain Data Objects in any other languages out there (Rust, Python, JS)?????
I think Ecto in the Elixir ecosystem is probably the best data access library I've used that is more slightly more than a library like sqlx but also still not an ORM.
Yeah this is just like a DB Repository in C# that uses _only_ Dapper to map SQL + DB into plain ol objects. Love it. Glad rust has this fantastic, type safe package! There's something similar in F# land which has type checking on the schema as well just like here. Lovely.
But Dapper doesn't have a "type-safe" SQL queries like the ORM that you presented I'm this video. For me, it's a deal breaker. If i make a typo in my query, I won't receive any error until I execute it (runtime exception).
Totally agree with you. I also think that it is a great benefit to take advantage of the power that sql engines provide... orms performance overhead is product damaging, even with simple insert operations concurrently
Yes, you are right. Sql is powerful, locking it down and only using a subset of its powers is a mistake. It is also not easy to learn by just reading a book about it either, we need to practice. And I think the best and most fun place to practice is in our day-to-day development.
I'm also a huge fan of sqlx, but I think Ecto from the Elixir ecosystem is the best data access library I've used so far. It's slightly more than sqlx but still not an ORM and it provides a great macro based DSL for constructing queries.
Your approach works for simple cases but good ORMs(e.g. SQLAlchemy) implement Identity Maps, transaction support etc.. .which you will need as your use case becomes more complex.
transactions shouldn't be that hard to implement, sqlx have support for transactions, just need to start using them and pass them to where they should go when needed. I didn't know about Identity maps, but a quick look into them, its a cache layer and wonder how precise these are about columns and such. Will they fetch all fields from a table or only the ones i need? And will it populate only the missing field if its lacking in later queries? And, what about data consistency when data is appended? So much magic so many questions! 😱😆
@@lindblomdev Identity maps ensure that all objects referencing the same row are the same(reused) and prevents duplicate fetches too. Please watch ruclips.net/video/woKYyhLCcnU/видео.html. It's long but I'm sure it's worth your time!
I’m not sure if you know about the repository pattern as it sounds like what you were describing with the data access classes. My personal go-to is an ORM and following the repository design pattern. As for me it’s the best of both worlds, flexibility and just the ease of use for complex join statements in SQL. Doesn’t leak SQL as all entities have their own Repository. Great video btw.
When fans of a language does not have a good implementation of ORM. Rhis types of videos appearing a lot. ORMs are good good implementation of them is great.
Personally I think that using ORM so you don't have to learn SQL is the wrong way to go anyway, I don't even think that's why they were created to begin with, but I don't really know anything about that.
Agree. Authors of orms are probably SQL ninjas. I also belive that many developers introduce orms and their query builders for increased developer productivity. With thay said. Don't you think your avarage developer would have a better understanding of the database and the query language if it weren't for these tools? Also, our applications is more likely to do data processing in the application, instead of asking the db to do the task, due to the same reason? Causing overfetching and multiple round trips.
@@lindblomdev I agree that is easier to make that mistake when using orms. Still, I've only used a few, eloquent and seaorm to be more precise, and the documentation and teaching material usually highlights these issues, I can't speak for every dev but i was aways aware of these things despite beeing fairly inexperienced.
It feels like the pendulum always swings. At some point I will swing the other way again, as I get tired of maintaining the data access. Or at least slim it down a bit. Seaorm looks interesting and I might give it a try. Thanks for the recommendation 👍
ORM usually has some tools' integration, like Alembic for sqlalchemy, etc, which helps to groom the database, do migrations, and other routine staff you otherwise should write yourself. It saves some time. And bad developer may screw more with raw SQL, imho Anyway, nice video - will consider the stuff down. Thank you a lot!
I think I forgot to mention this is in the video. But if you are working with a database, you will eventually end up doing ad hoc queried from your db manager system for different things. Investing/looking up stuff or fixing stuff. By using sql while coding, you will get the training you need during development and not have to train in production.
@@lindblomdev I see what you mean. My idea was mostly of the "compiling languages - script languages" tradeoffs type. ORM lets you ignore, for the time being, the details of your SQL mechanics, and to focus on the other side of the interface. It more or less gracefully does the query optimizations, tooling, one-to-many links handling, sqli protection, etc for you, while you develop the MVP or something. And it takes some work off you in a way compiler saves you from knowing assembly. But you still have to know the architecture and fundamentals below your ORM, even you don't write SQL by hand, like the Cpp/Rust developer knowing cache coherency/latencies/buses/ And when you need speed, you have to dive in, and develop the expertise, etc. - in our case, you resort to SQL to debug stuff, in godbolt-like type of ways. Basically that was the idea of the point. And I was quite happy to see the rust-lsp supports integration with macroses, and can do autocompletes, type deductions, etc to help you do precisely that low-level high performance stuff you have shown. Real pleasure!
Could it be interesting to have an application with an ORM but when things are too complicated, write some part in SQL? Use case: I'm launching a simple project (as a CEO) and I need to move fast. The ORM will allow me to do that because I'm new in backend development. If I'm growing, the strategy is to hire someone to rewrite the code from ORM to SQL version.
Then I would go with what works for you. Moving forward is the most important, don't worry too much what is "the best", good enough is good enough, go go go. 👊
curious your thoughts on prisma that complies in rust under the hood if you have used it? One thing thats nice is being able to do type-safe SQL raw queries and updates that prisma doesn't have yet eg GIS
Doesn't it suck even more than other ORMs since it doesn't support Joins and only fakes it by running two full queries and then joining inside of Rust?
Sql injection is generally not a problem unless you use string concatenation to fuse your query with your parameters. Use the method for adding parameters that your connector library supplies and you'll be fine 👍
I use the plugin: qufiwefefwoyn.inline-sql-syntax. With that installed you also need to stop rust-analyzer from overriding it with this "rust-analyzer.highlighting.strings": false. I have not noticed any drawbacks from changing the rust-analyzer setting.
There are a lot of things that are more efficient with just sql queries and orm can’t do it with the some efficiency. With a few records is ok but when you have millions uff , you need the more efficient way, in speed an resources
ye but how would you go about managing relationships / associations and tracking of changing objects without an orm ? i feel like that's the primary reason for it in the first place
I dont see any problems with relationships, as it would be not much more than syntax differences. When it comes to change tracking, I don't know how big of a problem that is in reality, as you usually know where things are mutated and its not that hard to add change tracking at that point.
I have to say I am glad that I am not the only one who thinks this (I am certainly the only one in the company 😂). Also coming from Rust to a .NET C# Entity Framework is not a good transition. While everyone is praising entity framework I have to say that coming on an existing (poorly written) project where everything is code first and when the code introduces runtime errors (coming from Rust this is something I know can be avoided) it seems to me that you don't speed up the development if you don't know how entity framework works. And almost nobody in the company really understands what entity framework is doing. And also entity framework works different between versions. And writing extra code so it can translate to SQL,... In the end you have more work and are a hostage of the framework... Different database support? Sure but not all code will work with different databases... So in the end you increase overhead for the development and onboarding and introduce an extra layer of complexity and the whole idea we introduce an ORM like EF is to reduce complexity. But you just hide it and it looks like that complexity is not there and it's hidden and that is way worse. And the application is super slow on a modern computer... And no one really knows why... That's the perks when writing software for 1-4 customers... Try scaling that if your user count grows... You will have more work to improve speed. I find myself that once you go beyond a hello world example the extra overhead EF presents is not worth it. And for sure you will go beyond simple schemas and your code will be much more complex, you have to groom the extra layer just to get some data from the DB you would otherwise just write SQL...
I hear you, especially the part where no one knows how entity framework works. When magic is involved you really go out of your way not to fuck it up, as you don't know how to sort it out afterwards. I'm talking specifically about migrations and contexts in entity frameworks. It's something that someone sets up early in the project, then other "monkey see monkey do", which works until something major has to be down. By that time there is no one around who knows how it works and you are multiple versions behind the rest of the world. Good luck 😅 with sql and explicit code in project, it's as easy as reading the code to continue to work on it.
Here it's so far so good. Even if one might pick up a query builder or a full orm in the future. It's good for a developers progress to live without one, to really get to know your database.
@@lindblomdev I worked with ORMs extensively as a professional Ruby on Rails coder for a number of years, so I fully see your point. It's alluring to think that you can be insulated in your favorite pet language, but then you realize that you're having to learn a language within a language that is a translation of another language (SQL). So, yeah.... I'm all about simplification. Software should be as simple, dumb and predictable as possible.
All libraries included in your project will add overhead; I know, I'm being captain obvious. What I expect any reasonable engineer to do is the following: 1. What benefit does the library add especially when compared to the learning overhead? 2. Make sure the library isn't poorly designed in terms of performance; they all will add overhead, but is it measurable when compared to the total cost of the request? 3. Design your system to be simple, clean, and easy to understand. This is where the bulk of your cost (time and money) comes from. Please use VARCHAR vs. TEXT in your examples. All that being said, I use sqlx with query! and query_as! and I'm not using a full blown ORM. Why? sqlx with the macros is very nice and gives me a lot of what I need. A good ORM like say Hibernate in the Java world gives you very good performance if used correctly. In fact, Hibernate not only gives you good performance, but helps you have cleaner code with it's built in caching, dirty checks, etc Any "overhead" that Hibernate gives you is offset with its power. We will always have two worlds fighting. Pro ORM and Anti-ORM. I myself sit in the middle... it all depends on the complexity of the application, database model and what Im trying to built. I try to remove my basis when it comes to engineering. My main basis tends to be towards keeping things simple; at least I try to have the basis.
I think it depends if a library adds overhead or not, it could be that the library only runs at compile time and you end up with the same number of instructions as if you made it yourself, but true, very close to 100% of the time, a library will add some overhead. Of course, I missed the main reason, which triggered the video. It's that developers today are awful when it comes to writing SQL, compared to what they would have been if the projects they worked on didn't use an ORM with a query builder, myself included. I'd rather have developers learn sql during development than during an incident when we need to fix things in the production db. Since Postgres are treating text and varchar the same, I opt for using text everywhere (it's shorter and looks nicer). If I used varchar somewhere, I apologize for the inconsistency. I agree, simple is good. I'm not trying to introduce artificial complexity, just show people that you might be able to start your project without abstracting SQL out of the code base. If it hurts and you cannot clean it up, sure, by all means, use an ORM, your project might warrant it.
I'm not sure where you think "Figuring out how to do it with an ORM" is difficult.. you literally figure it out once and use it for the life of the project.. not only that in a scenario where you need a complicated sql query that the ORM can't just "Magically" do, the ORM usually (always from my experience) has a way to pass it a sql query and it will still handle turning it into a relational representation using your defined models
Now imagine you need to build dynamic query. Probably even without all that hard stuff, but 10+ dynamic where clauses for e.g. It will be a nightmare (or shitcode). Also, what about migrations? Why I see types in your code if sqlX doing everything for you? I'm not advocating ORMs, I hate them, especially in TS, there are still no adequate ORM for TypeScript. But pure sql is even worse in a mid-large project tbh. By the way, if you want to use sql for complex query you can still do it within ORM, all of them support raw queries I believe.
yeah, i havent done dynamic queries in sqlx yet. We will see how it turns out, when i have the actual need for it. Its so easy to bring something in for the "what if", when the "what if" you are imagining might not happen and when it happens it turns out to be not a big issue anyway.
I don't see why it wouldn't. I'm guessing by nester structures you mean either jsonb columns or that you write a CTE that constructs these jsonb objects that you later join into your original query to eliminate a db round trip.
@@lindblomdev I mean like this: struct Customer { id: u16, name: Name, age: u8, is_active: bool, } struct Name { first: String, last: String, } would that be possible?
@@johnnyblack4261 yes no problem. You will probably have to do the mapping yourself, but you can use the Record struct generated from your query in your mapping to have it be typed all the way. It doesn't matter how the name is actually saved in db, with with anything (single column(split by query), different columns, different table that you join in).
maybe, i havent heard about it or used it 🙃. I almost exclusively make web apps and for that i prefer dedicated database systems over embedded databases, as it makes it easier to make more instances of the apps, and the already set up backup process will include future apps.
I'm all for migrations. Sqlx comes with migration support. Another thing that I like to have in my apps is application level migrations. To have stuff that would be manual configurations be applied programmatically instead. Like db schema migrations but using your app language and app services.
There are ORMs for rust, you can do many things you can do in OOP in Rust too, cant say i miss something from C# (and I write much more C# than Rust) rather the otherway around.
agreed Rust is an atrocity that should have never been made. It's a massive step backwards from a development point of view.. All of the documentation examples are procedural. The syntax is a mess. It's apparent that every dev pushing the greatness of procedural languages has never had a job where they work on a large code base.
I agree avoid use ORM in simple scenarios and avoid ORMs that polute domain entity, but ORM shine when we have complexes data structures with relationships, try to make a raw select and mount manually a complex structure, for example, order with OrderItens and each OrderItem with Product data, its very complicated
We should try that. And compare the amount of data that is fetched. It feels that the orm will do some overfetching in this scenario, especially if you don't need all columns on each product.
it looks cool. Does it also ensure that your sql actually works agains the db? There are some comments around dynamic queries, do you know how sqlc handles that? Example: you have a graphql where you want to hit the db directly, but only fetch the fields that the query defined as sub fields and the where clause should be constructed from the params given to the graphql query. Not trying to poke holes in sqlc, just curious if you know this? as i believe i would loose my compile time checks if i where to do in with my current knowledge of sqlx. It would work, but it might blow up during runtime if i mess it up.
No, I rarely write backends in node (anymore), only if they are part of the frontend rendering/proxy, but not the main application. I think i forgot one of my main points for using SQL in your day to day development, and im pretty sure one does not write very much sql when using prisma. Its that you as a developer get better at SQL, which is i believe is very important as you probably need to run some ad-hoc queries at some point in the future against the production db. Then its much nicer if you are a native speaker of the sql language. Either way, thank you for mentioning prisma, I will check it out 😊👍
@@lindblomdev which is an utopic scenario, most project are a complete train wreck from a DB design pov, this is why orms were invented in the first place, to abstract that mess, i like this workflow but is really not sustainable for a big project where dozens of devs are working on at the same time
@@lindblomdev making multiple relationships in SQL and it gives a flat resultset and it's not convenient to use. ORM made it simple. Yes ORM operations are expensive sometimes. I suggest to use SQL on such situations only. Here we have to use alias, joins etc to get results.
Or, i'm just embarrassed of how bad the ORMs has kept me on writing sql, as i rarely had to do it, and trying to shift blame 😆. Whoa, Hibernate, that is some real OG stuff you got on our hands 😎
That last point about data-access layer where you hide the SQL inside of some rust structures is great! super happy i came back to this video
Been using edgedb with typescript and its such a joy. I wrote a simple script that creates orm like classes by inspecting the db. Any change in the db schema is now more or less directly reflected in my code. Since the generated classes are just a wrapper around the typesafe query builder, you are not required to use them at all. I am more than happy that i moved away from the orm pain.
I've been watching them and I definitely want to try them. I hope DB explorers(DBeaver, DataGrip) etc... support them.
I think ORM's are worth the hassle in a number of situations. I am a big fan of 'convention over configuration' and have found ORM's to help guide developers to think more about their models and how they persist data, rather than DB-first architecture. For some devs, its hard to show them just how bad their DB schema design is until they have to interact with it using an ORM. The positives far outweigh the negatives, in my experience.
yeah the old plain method is good when you have a simple DB design that is like sculpted on a stone, most of the projects unfortunately are a complete mess to put it kindly, and an orm helps alot
If they have a hard time interacting with the schema using ORMs, i dont think they would have jolly time in plain sql either 😅
This is completely backwards and no, the reason you listed is precisely why ORMs are bad. I have been working on a db that was "designed by ORM" (hibernate). And it was a mess. 300+ tables, 20+ empty many to many join tables, duplicated data, deprecated columns, etc. I'm not even going to talk about fetching data in a for loop all over the code. So how do you go and optimize this? You cache everything on server start up of course (load important data into datastructures on the server), making the server consume 7GB... So no, if you don't know how you interact with database, the ORM can only make it worse, not better.
However, the reason ORMs can be good is the M part. I have been impressed by TypeORM which is for me the best ORM by far. You have the mapping of the schema to object, ORM generates the migration, which you can of course modify, you have a lot of control over what queries get executed (it would be possible to do what Chris did in the video with it) and how the result gets mapped, saving you a lot of time you would need to write your own mappers. The video strawmans what ORMs actually do. If you have to only map one table, then ORM is overkill, but if you have to map relationships, then ORM will help you achieve that way faster.
@@sarabwt typeorm also support activemodel pattern, which i kinda prefer to the repository dao pattern, but yeah, projects can be a mess expecially when a lot of juniors and just bad programmers work on it during the years...
@@sharkpyro93 Interesting, I prefer repository, but I don't have a good reason for it :D
This is the thing, the guys working on this were not some random plebs, but uni professors and lab assistants... CS doctors... If they make this kind of a mess with these tooling, what chance do the juniors have? xD
I think you pointed the most important part very well.
As long as everything is super simple, ORM can be beneficial for those who want to stay away from SQL.
But I have seen, in each and every project I worked on, that ORM has its limitations.
Depending on the ORM super simple things like creating a GIN index on a property stored in a jsonb field can be impossible.
I also have the issue when colleagues are working with ORM that they try hilarious things because things just don't work as in plain SQL and then everything is much harder to debug and review.
Often they just give up and ask if I can help them with a query.
More often than not, we have to rewrite queries to raw SQL for performance reasons because of ORM limitations.
So also my conclusion is that raw SQL is way more powerful when used by someone who knows what he/she is doing.
I don't mind ORM, but I like things seperated.
There is a database, use psql straight from the terminal
There is backend, mostly an api/smartcontract/automation/... , use rust or js
There is a frontend, use html css and js
There is a UI, use Figma
The complexity is already wild for applications, to I keep things seperated helps to not go nuts^ ^
ORM are nice, but they also add another point of failure. 🐒
Happy to see a video about an alternative to ORMs. I swore them off long ago, and thought enough other people had done so that the wider programming community would at least be aware that not everyone was fawning over ORMs and that there are other options. Then I saw a video criticizing the repository pattern because it doesn't make sense on top of an ORM. the original repository pattern is to me clearly an alternative to an ORM. I was stunned by the degree to which ORMs were taken for granted.
compile-checked SQL queries have a lot of value. However, it's not uncommon for an application to need to build queries dynamically from query fragments. Even if the fragments are made from compiled SQL, combining the fragments dynamically requires functions, and no matter how much those function names resemble SQL and no matter how fluent the syntax is, it will probably be considered a DSL. It's also not immediately clear if SQLx can check query fragments.
Directly working with SQL queries also means working in terms of the current database schema. The developer is frequently needing to do domain-schema mapping in their head, and in the case of a schema change, they can need to do a lot more work compared to if there was a tool mapping domain-oriented queries to schema-oriented queries based on a separately-defined schema.
Checking queries against a real database can be useful, but checking them against a separately defined is often more convenient and practical.
And of course, SQLx only works with databases that accept SQL. If a project moves to a NoSQL database, that can be a lot of redoing.
SQLx ultimately strikes me as a very incomplete solution for major projects. Since the description says ORMs have been built on top of it, I'm hoping it can serve as a component of a more powerful non-ORM tool.
When it comes to tools that can supplant ORMs, the Slick library for Scala comes to mind (note: Google won't find it unless you specify Scala). It lets you perform fairly arbitrary mappings from SQL schema to domain objects and perform SQL-like queries on them, and the library will perform appropriate SQL queries and convert the results. It differs from what I see in ORMs in that there is a very clear and hard boundary between querying the database through the lens of domain objects and using such objects in normal code. When I last looked at it, it had a lot of room for improvement, but it's been at least a year, so those improvements may have already been done. I'm also hopeful that Rust's macros will allow even better implementations of the idea.
Thank you for taking time to write such a long and thoughtful comment.
Yes sadly we will have generate some sql dynamically, i dont see a way around it. SQLx has a query builder, which is pretty much a string builder that takes unchecked sql fragments. How inconvenient that might seem, i still prefer it over a library with a fluent query builder in the application language. Its not that hard to set up an integration test to verify that it works and does not regress.
If you are moving your database from sql to nosql, it has been my experience that changes had to be done even with the use of an orm. It might be the case that its easier to migrate to a nosql database if you are using plain sql as your app data query language. The reason being that a sql string sticks out, and you dont want them all over your application, therefore you wrap them in functions that handle the mapping from db to the optimial format for you application. So all you need to do is to replace the sql with the native query language of your new data store. This is in contrast to how i think orms are used. They look like a very clean abstraction over you db, which makes it easier to pass into parts of your application and the mapping of db data to app structures are done ad-hoc in the place where its needed but shouldn't be done.
This might not be what you meant, but SQLx have a feature to extract the info needed from db into a json format, which can then be used during compilation. Its meant for situations where you dont have a db running. I use that in my build pipeline. And, yes, i have forgotten to run the tool to regenerate the json file and have it fail in build due to this a couple of times already. 🙈
I found the slick scala. Will have a look for good ideas 👍.
I appreciate the effort you put in your comment, thank you
Your demo of SqlX was really eye-opening. I didn't realize that typing info would be propagated up all the way to the language server. I wonder if there is a library that could do similarly for TypeScript, while not having to switch to a whole new DB like edgedb.
Thinking of the comments on refactoring, conceptually the model that SqlX produces could be generated into files and those could be imported into the project allowing the models to be refactored such that field and table names could be changed in one location. This would remove some of the flexibility and agility of the SqlX approach though.
Thanks for sharing.
Eh.
Sure, for some complex queries you might be better off with raw SQL than using ORM. But ORMs tend to do much more: persistence, associations management, transactions management, type coercion, dirty tracking, etc.
How much of that are you actually using, and how much is just overhead?
Not to say that it's not nice to already have it when you really need it. Simplifies a lot. I wonder though, couldn't every one of these automatic features be implemented as decorators around your query object or something. And then you could explicitly opt in when it's needed.
In ORMs like Spring Data JDBC or JPA, you can absolutely do this sort of typed SQL query thing in the Repository interface. You just have to annotate the method with a @Query() where you write your SQL and make sure the return type is correct, like User or List
Sounds neat, I have never worked with these. I think it's important that developers understand SQL, if they are working with SQL databases. 👍
Also, many older programs that do use raw SQL for querying dbs seem to concatenate many strings together, eg statements in the where clause are dynamically built, or the selected columns are dynamically built. The code to do that is often strewn across the application, so it becomes almost impossible to tell what the sql statement will be without actually running the code and logging the query. Those are very frustrating apps to work with.
yes i agree. Then its scary 🙈
clearly you havent had to use dynamic queries with an orm, its even harder to understand whats going on, and half the time you run into issues you wouldn't have had with plain sql
I think it comes from not very clear separation of concerns rather then using raw SQL vs ORM.
But that's not an ORM issue... I'm currently working on an application which is just like that. There is nothing preventing a "plain SQL" application from using the repository or (if you really really have to) the Active Record design patterns and separating their concerns but we seem to live in a world of extremes where an application is over-engineered with classes on top of classes on top of classes (this is where we most often see ORMs) and under-engineered with SQL, HTML, PHP and Javascript all mashed up in one file...... actually, I'm working on an application at the moment which is BOTH. :(
@@ed_iz_ed Yeah... I'm used to the Doctrine ORM where almost everything is possible (eventually!!!) But recently I've been working with Eloquent and it takes such a very long long long time trying to make it's over-complex "architecture" fit in with our legacy database.
ORM's are just a try to simplify a complex matter. If you want to have the best performance in both development and processing you need to know a lot about persistance data storage and how to read from it. In my opinion the only real use case for ORM's is to use them while learning programming. If you'r goal is to produce a stable scalable application you have to dig into SQL and use it raw. Don't avoid usinig dynamic sql as well - it's like a magic wand when applied right. Thx for the vid!
But the point of an ORM is not to abstract away SQL... they are not an alternative to SQL for data access (although most do this a a side effect). The point of ORMs is to do Object-Relational Mapping. It's not the data querying that they're trying to simplify it's the mapping of the returned data to an object format. As an exercise, find a complex database and do a simple SQL to query that data to about 3 levels of LEFT JOIN... and then render that dataset in JSON.... That's what ORMs are for.
@@edgeeffect You are perfect right. I personally love Dapper - that does exactly what you say.
Experience has told me that an idea of an object is neither the object in code like a class nor the persistant representation. When it comes to store objects you have to keep in mind in which way you want to query them. When it comes to business logic it's the class'es abstraction that helps you implement your idea.
Many ORMs try to sell you those two different conecpts as one. That's where I'm having the problem with it. But you are right. In order to work with records of a database you have to have some easy way to map between a query result and a class.
@@edgeeffect As the video uploader said in the first minute of the video (and the reply above me) - returning results in an object format is useful, nobody thinks it's not - but there are libraries available which will do that for you without the abstraction of the way you write queries. ORMs also tend to write very poor performing queries, and you are going to run into performance issues sooner rather than later. Worse is, once you do run into performance issues, you have no way to find the query in reverse (go from SQL back to code) in order to find the problematic code. To clarify, from the database side if you see that you have certain queries which are hogging resources, you can't simply ctrl+f in your code to find where in your code that query is being executed, because the query isn't in your code at all. You have to either understand your app very well so that you can make an educated guess to where/what in your code is generating that query, or if it's from code written by someone else, you end up going hunting and taking guesses. Even if you know how to use the ORM well, it's so easy to write bad code.
I'll give you an example. In C# personally I use Dapper like the guy above (write raw SQL and get back a C# object), but Entity Framework is an ORM that abstracts the SQL away into code and was one of those mentioned in the video. Just last week one of our teams that use Entity Framework had a query that took 58 seconds to run, which should have run in milliseconds, and it was causing performance issues for all users. The reason was because the generated query had no "where" clause. So the application was querying the entire table into memory then filtering the rows needed in the application in C# code. If they had written the SQL by hand, they'd have to be doing it on purpose to write the app in such an ignorant way. When I notified the team of the problematic query, they spent 2 hours trying to find in their code where that query was coming from before they could start to fix it. Honestly I have problems with this team every few weeks because of bad queries, selecting excessive number of rows or columns or unnecessary joins etc.
I both agree and disagree lol. For example, if I change a column name, I need find all the sql statements that contain that column name and manually change it. It’s not as easy as right-click rename, or a find and replace, as the sql is declarative and may or may not be appended by a table name or alias and the column name may be used in multiple tables like “name” or “description”. So to make any schema changes, you need to explicitly find understand and update the sql as necessary.
In the video I use sqlx which does schema validation as part of the compilation. If your language doesn't support that, there might be a linter you can run which would give you the places where you are doing broken queries, due to mismatch in names.
But I will agree that a simple rename refractor is easier where you have a orm in the mix. Although that's not something you should do very often as it will lock the whole table including reads if I remeber correctly. Atleast in postgres. 😅
Hmmm This usually means your code repeats itself and might have a coupling issue...
Technically, you should not create 30 times the same-ish sql query (like 30 kind of select on an entity).
- Why? It makes your code more fragile to those kind of changes. The ORM will protect for simple changes like those but won't for huge one (example: change database technology)
The best approach is to isolate the database implementation behind a boundary of some sort like a group of function or implementations that modularise its uses.
Exemple: create a class, the one he queries with works, and create member functions (or static for selects) that does your query for you. With that approach, you only need to check the models (which you would anyway) with a standard refactoring.
What happens in major refactor? You adapt only the models classes/implementation which is far less work than reworking every call one by one.
Why? Because your application does not know about what tech you are using. Your code is safe from tech changes because it does not know about what tech is used under the hood.
Why even care about those changes? Trust me, those kind of changes are very rare but they comes out of nowhere. It happened to me in a project... We wasted SO MUCH TIME remaking query by hand and dumping our ORM (prisma).
Could we have found another ORM instead? Not really, we didn't have time to do so. We all knew sql so it had to do.
@@elvarght the whole point of sql is to be able to be hyper flexible with queries and potentially be same same but maybe joined and or aggregated in different ways. It’s inherently going to cause repetitions in code, which isn’t necessarily a bad thing. Using ORMs actually make these kinds of changes easier because they do act as a boundary layer to the database. And yes a boundary layer between your public api and the internals is beneficial so you can limit the effect of internal changes on your public apis. But the author of this post is suggesting to do away with ORMs, so we need to think about the trade offs that means. Our domain model is always going to change and grow, and our persisted data model is always going to change and grow too, and any and every query needs to adapt to that. The fact that sql can really only be represented as strings is the main problem. We will never get native sql support because each database has different syntax and even different paradigms like relations vs graph. And although plugins like what the author suggested may help to add syntax highlighting and refactor support, unless those tools have access to the actual underlying database engine and can understand the queries in those strings, those tools are always going to be less than 100% effective. And then there’s the problem that strings can be concatenated and manipulated. Sql parts existing in different strings and concatenated with different sql parts. At some point it surely becomes impossible for those tools to be useful.
@@br3nto Hmm I see what you mean... I still feel like with a good code structure, you don't "need" ORMs. It does simplify some things tho.
Tho you should never concat string with sql queries... never do a raw query even with a tool as simple as this one.
Great video, I started using sqlx after watching this video but you can still use raw queries in most ORMs for special cases.
The problem with Active Record (the design pattern not any framework or library that might implement it) goes far beyond ORM, I'm working on a system where we have no ORM but still configure our data access layer as a collection of Active Records. Active Records seem to break Single Responsibility where data objects end up having to "schlep" around everything involved in query and update as well as data. This isn't so much of a problem if you're just updating one record and maybe a couple of dependent records. But when you're doing a big query for (e.g.) a financial report the overhead becomes unbearable. This is less of a problem in Rust than (e.g.) Ruby or PHP as there are compiler optimisations.
I'm no advocate of ORMs - they always seem to be needlessly "dense" and over-complex - but it's quite ironic if we have a large query-result perhaps with many joins and we want to render this into JSON... The very definition of what we need to do there is Object Relational Mapping, and without an ORM to "do it's magic" this is a non-trivial problem. Again, if you just have one record with maybe a couple of dependent records, it's no problem - but once the joined tables start growing it gets surprising complex very quickly.
ORMs are a tricky question because no matter what answer you have to "the ORM question" - it's wrong! Use them - wrong! Don't use them - wrong! Hide under the table and wish it would stop - wrong! ;)
Best answer I've seen so far is the Doctrine ORM (in PHP land) that doesn't use the Active Record Pattern, and does it's best to try to overcome the inherent limitations of using an ORM... It's not a good answer, but it's the best I've found so far.... I often wonder if there's an ORM that uses The Repositry and Unit of Work patterns alongside Plain Data Objects in any other languages out there (Rust, Python, JS)?????
I think Ecto in the Elixir ecosystem is probably the best data access library I've used that is more slightly more than a library like sqlx but also still not an ORM.
Yeah this is just like a DB Repository in C# that uses _only_ Dapper to map SQL + DB into plain ol objects. Love it. Glad rust has this fantastic, type safe package! There's something similar in F# land which has type checking on the schema as well just like here. Lovely.
Yes Dapper is a fresh breeze of air in the land of entity framework.
But Dapper doesn't have a "type-safe" SQL queries like the ORM that you presented I'm this video. For me, it's a deal breaker. If i make a typo in my query, I won't receive any error until I execute it (runtime exception).
Totally agree with you. I also think that it is a great benefit to take advantage of the power that sql engines provide... orms performance overhead is product damaging, even with simple insert operations concurrently
Yes, you are right. Sql is powerful, locking it down and only using a subset of its powers is a mistake. It is also not easy to learn by just reading a book about it either, we need to practice. And I think the best and most fun place to practice is in our day-to-day development.
I'm also a huge fan of sqlx, but I think Ecto from the Elixir ecosystem is the best data access library I've used so far. It's slightly more than sqlx but still not an ORM and it provides a great macro based DSL for constructing queries.
I will have to take your word for it. Since I have no hands on experience with elixir, the examples look very messy to me 😅
Your approach works for simple cases but good ORMs(e.g. SQLAlchemy) implement Identity Maps, transaction support etc.. .which you will need as your use case becomes more complex.
transactions shouldn't be that hard to implement, sqlx have support for transactions, just need to start using them and pass them to where they should go when needed. I didn't know about Identity maps, but a quick look into them, its a cache layer and wonder how precise these are about columns and such. Will they fetch all fields from a table or only the ones i need? And will it populate only the missing field if its lacking in later queries? And, what about data consistency when data is appended? So much magic so many questions! 😱😆
@@lindblomdev Identity maps ensure that all objects referencing the same row are the same(reused) and prevents duplicate fetches too. Please watch ruclips.net/video/woKYyhLCcnU/видео.html. It's long but I'm sure it's worth your time!
Added to watch later 👍 thanks
I’m not sure if you know about the repository pattern as it sounds like what you were describing with the data access classes. My personal go-to is an ORM and following the repository design pattern.
As for me it’s the best of both worlds, flexibility and just the ease of use for complex join statements in SQL. Doesn’t leak SQL as all entities have their own Repository.
Great video btw.
Yes... repositories are wonderful and active records stink. :)
When fans of a language does not have a good implementation of ORM. Rhis types of videos appearing a lot. ORMs are good good implementation of them is great.
Personally I think that using ORM so you don't have to learn SQL is the wrong way to go anyway, I don't even think that's why they were created to begin with, but I don't really know anything about that.
Agree. Authors of orms are probably SQL ninjas. I also belive that many developers introduce orms and their query builders for increased developer productivity.
With thay said. Don't you think your avarage developer would have a better understanding of the database and the query language if it weren't for these tools?
Also, our applications is more likely to do data processing in the application, instead of asking the db to do the task, due to the same reason? Causing overfetching and multiple round trips.
@@lindblomdev I agree that is easier to make that mistake when using orms. Still, I've only used a few, eloquent and seaorm to be more precise, and the documentation and teaching material usually highlights these issues, I can't speak for every dev but i was aways aware of these things despite beeing fairly inexperienced.
It feels like the pendulum always swings. At some point I will swing the other way again, as I get tired of maintaining the data access. Or at least slim it down a bit. Seaorm looks interesting and I might give it a try. Thanks for the recommendation 👍
ORM usually has some tools' integration, like Alembic for sqlalchemy, etc, which helps to groom the database, do migrations, and other routine staff you otherwise should write yourself. It saves some time. And bad developer may screw more with raw SQL, imho
Anyway, nice video - will consider the stuff down. Thank you a lot!
I think I forgot to mention this is in the video. But if you are working with a database, you will eventually end up doing ad hoc queried from your db manager system for different things. Investing/looking up stuff or fixing stuff. By using sql while coding, you will get the training you need during development and not have to train in production.
@@lindblomdev I see what you mean.
My idea was mostly of the "compiling languages - script languages" tradeoffs type.
ORM lets you ignore, for the time being, the details of your SQL mechanics, and to focus on the other side of the interface. It more or less gracefully does the query optimizations, tooling, one-to-many links handling, sqli protection, etc for you, while you develop the MVP or something. And it takes some work off you in a way compiler saves you from knowing assembly.
But you still have to know the architecture and fundamentals below your ORM, even you don't write SQL by hand, like the Cpp/Rust developer knowing cache coherency/latencies/buses/
And when you need speed, you have to dive in, and develop the expertise, etc. - in our case, you resort to SQL to debug stuff, in godbolt-like type of ways.
Basically that was the idea of the point.
And I was quite happy to see the rust-lsp supports integration with macroses, and can do autocompletes, type deductions, etc to help you do precisely that low-level high performance stuff you have shown. Real pleasure!
I agree! I think this is why I love MongoDB though. Btw the text is easier to read but it could still be bigger!
Haha, but I fit so few characters on the screen if I go bigger 😆 I will experiment to get even bigger text next time 👊😉
@@lindblomdev instead of increasing the font size, consider cropping the screen capture even more
if you are using OBS, this should not be hard
My plan is to crop/zoom in davinci when I do the edit. I think it will have the same effect as cropping in obs.
@@lindblomdev still a great video!
I will try this .. thanks
👊
Font size is still small for mobile devices, try increasing more and also toggle word wrap if necessary, btw gre8 video!
will do, thanks for the feedback 👍
Thank you
Glad you liked it 🙃
Could it be interesting to have an application with an ORM but when things are too complicated, write some part in SQL?
Use case: I'm launching a simple project (as a CEO) and I need to move fast. The ORM will allow me to do that because I'm new in backend development. If I'm growing, the strategy is to hire someone to rewrite the code from ORM to SQL version.
Then I would go with what works for you. Moving forward is the most important, don't worry too much what is "the best", good enough is good enough, go go go. 👊
But how would I pass in parameters to these functions or files?
curious your thoughts on prisma that complies in rust under the hood if you have used it? One thing thats nice is being able to do type-safe SQL raw queries and updates that prisma doesn't have yet eg GIS
im also courious about my thoughts about it 😆 it looks interesting, will have to check it out at some point. Thanks for the recommendation 👍
Doesn't it suck even more than other ORMs since it doesn't support Joins and only fakes it by running two full queries and then joining inside of Rust?
can't agree more, orm just add a layer of complexity for me
there are some orms that ease work with dbs, like prisma, love this :)
I will give prima a good look 👀, thanks for mentioning it 👍
What about sql injection?
Sql injection is generally not a problem unless you use string concatenation to fuse your query with your parameters. Use the method for adding parameters that your connector library supplies and you'll be fine 👍
I think you should try SQLAlchemy in python
Thanks the recommendation, i will at least read their docs, as reading docs (even for things i never end up using) can give new perspectives.
@@lindblomdev SQLAlchemy as one of the least intuitive docs i've read. Watch this instead. ruclips.net/video/sO7FFPNvX2s/видео.html
Can you give an example of setting up a service locator that you mentioned at 9:03?
Yea , in Laravel , I didn’t touch orm at all it query builder is much more flexible and faster at scale
How good are you with sql if I take your query builder away? 🧐
How did you get your sqlx syntax to be highlighted? I only get orange text in VS Code...
I use the plugin: qufiwefefwoyn.inline-sql-syntax. With that installed you also need to stop rust-analyzer from overriding it with this "rust-analyzer.highlighting.strings": false. I have not noticed any drawbacks from changing the rust-analyzer setting.
@@lindblomdev Thanks, worked perfectly.
Honestly even the description of this video screams "I've never shipped code in real life"
Wow! I really need to up my description authoring skills 🤣
There are a lot of things that are more efficient with just sql queries and orm can’t do it with the some efficiency. With a few records is ok but when you have millions uff , you need the more efficient way, in speed an resources
ye but how would you go about managing relationships / associations and tracking of changing objects without an orm ? i feel like that's the primary reason for it in the first place
I dont see any problems with relationships, as it would be not much more than syntax differences. When it comes to change tracking, I don't know how big of a problem that is in reality, as you usually know where things are mutated and its not that hard to add change tracking at that point.
But it would be awesome to have better support for common table expressions, merge statements, and stored procedures.
I have to say I am glad that I am not the only one who thinks this (I am certainly the only one in the company 😂).
Also coming from Rust to a .NET C# Entity Framework is not a good transition.
While everyone is praising entity framework I have to say that coming on an existing (poorly written) project where everything is code first and when the code introduces runtime errors (coming from Rust this is something I know can be avoided) it seems to me that you don't speed up the development if you don't know how entity framework works. And almost nobody in the company really understands what entity framework is doing. And also entity framework works different between versions. And writing extra code so it can translate to SQL,... In the end you have more work and are a hostage of the framework... Different database support? Sure but not all code will work with different databases... So in the end you increase overhead for the development and onboarding and introduce an extra layer of complexity and the whole idea we introduce an ORM like EF is to reduce complexity. But you just hide it and it looks like that complexity is not there and it's hidden and that is way worse.
And the application is super slow on a modern computer... And no one really knows why... That's the perks when writing software for 1-4 customers... Try scaling that if your user count grows... You will have more work to improve speed.
I find myself that once you go beyond a hello world example the extra overhead EF presents is not worth it. And for sure you will go beyond simple schemas and your code will be much more complex, you have to groom the extra layer just to get some data from the DB you would otherwise just write SQL...
I hear you, especially the part where no one knows how entity framework works. When magic is involved you really go out of your way not to fuck it up, as you don't know how to sort it out afterwards. I'm talking specifically about migrations and contexts in entity frameworks. It's something that someone sets up early in the project, then other "monkey see monkey do", which works until something major has to be down. By that time there is no one around who knows how it works and you are multiple versions behind the rest of the world. Good luck 😅 with sql and explicit code in project, it's as easy as reading the code to continue to work on it.
Convinced. Thank you.
Here it's so far so good. Even if one might pick up a query builder or a full orm in the future. It's good for a developers progress to live without one, to really get to know your database.
@@lindblomdev I worked with ORMs extensively as a professional Ruby on Rails coder for a number of years, so I fully see your point. It's alluring to think that you can be insulated in your favorite pet language, but then you realize that you're having to learn a language within a language that is a translation of another language (SQL). So, yeah.... I'm all about simplification. Software should be as simple, dumb and predictable as possible.
Nice video! Thanks
All libraries included in your project will add overhead; I know, I'm being captain obvious.
What I expect any reasonable engineer to do is the following:
1. What benefit does the library add especially when compared to the learning overhead?
2. Make sure the library isn't poorly designed in terms of performance; they all will add overhead, but is it measurable when compared to the total cost of the request?
3. Design your system to be simple, clean, and easy to understand. This is where the bulk of your cost (time and money) comes from.
Please use VARCHAR vs. TEXT in your examples.
All that being said, I use sqlx with query! and query_as! and I'm not using a full blown ORM. Why? sqlx with the macros is very nice and gives me a lot of what I need.
A good ORM like say Hibernate in the Java world gives you very good performance if used correctly. In fact, Hibernate not only gives you good performance, but helps you have cleaner code with it's built in caching, dirty checks, etc Any "overhead" that Hibernate gives you is offset with its power.
We will always have two worlds fighting. Pro ORM and Anti-ORM. I myself sit in the middle... it all depends on the complexity of the application, database model and what Im trying to built. I try to remove my basis when it comes to engineering. My main basis tends to be towards keeping things simple; at least I try to have the basis.
I think it depends if a library adds overhead or not, it could be that the library only runs at compile time and you end up with the same number of instructions as if you made it yourself, but true, very close to 100% of the time, a library will add some overhead.
Of course, I missed the main reason, which triggered the video. It's that developers today are awful when it comes to writing SQL, compared to what they would have been if the projects they worked on didn't use an ORM with a query builder, myself included. I'd rather have developers learn sql during development than during an incident when we need to fix things in the production db.
Since Postgres are treating text and varchar the same, I opt for using text everywhere (it's shorter and looks nicer). If I used varchar somewhere, I apologize for the inconsistency.
I agree, simple is good. I'm not trying to introduce artificial complexity, just show people that you might be able to start your project without abstracting SQL out of the code base. If it hurts and you cannot clean it up, sure, by all means, use an ORM, your project might warrant it.
One big thing for me is I often need to support multiple SQL dialects, and in those cases sadly SQLx doesn't work. Great video though.
Yeah, same with multiple databases in the same project, sqlx cant do that either atm.
I’m not Agent 99. I never was. Most you couldn’t even take me even then.
👀
I'm not sure where you think "Figuring out how to do it with an ORM" is difficult.. you literally figure it out once and use it for the life of the project.. not only that in a scenario where you need a complicated sql query that the ORM can't just "Magically" do, the ORM usually (always from my experience) has a way to pass it a sql query and it will still handle turning it into a relational representation using your defined models
I think it was a geo within distance query I wanted to do against postgres that is still haunting me years later 😅
Now imagine you need to build dynamic query. Probably even without all that hard stuff, but 10+ dynamic where clauses for e.g. It will be a nightmare (or shitcode). Also, what about migrations? Why I see types in your code if sqlX doing everything for you?
I'm not advocating ORMs, I hate them, especially in TS, there are still no adequate ORM for TypeScript. But pure sql is even worse in a mid-large project tbh. By the way, if you want to use sql for complex query you can still do it within ORM, all of them support raw queries I believe.
yeah, i havent done dynamic queries in sqlx yet. We will see how it turns out, when i have the actual need for it. Its so easy to bring something in for the "what if", when the "what if" you are imagining might not happen and when it happens it turns out to be not a big issue anyway.
Interesting, but does it support nested structures by any chance?
I don't see why it wouldn't. I'm guessing by nester structures you mean either jsonb columns or that you write a CTE that constructs these jsonb objects that you later join into your original query to eliminate a db round trip.
@@lindblomdev I mean like this:
struct Customer
{
id: u16,
name: Name,
age: u8,
is_active: bool,
}
struct Name
{
first: String,
last: String,
}
would that be possible?
@@johnnyblack4261 yes no problem. You will probably have to do the mapping yourself, but you can use the Record struct generated from your query in your mapping to have it be typed all the way. It doesn't matter how the name is actually saved in db, with with anything (single column(split by query), different columns, different table that you join in).
@@lindblomdev I might just then use native_db then that seems to support all this.
maybe, i havent heard about it or used it 🙃. I almost exclusively make web apps and for that i prefer dedicated database systems over embedded databases, as it makes it easier to make more instances of the apps, and the already set up backup process will include future apps.
nobody talk about migrations, what about it?
I'm all for migrations. Sqlx comes with migration support.
Another thing that I like to have in my apps is application level migrations. To have stuff that would be manual configurations be applied programmatically instead. Like db schema migrations but using your app language and app services.
ORM in rust??? You need OOP first, I don't believe rust has OOP.
So what you are saying is go back to 80/90's?
There are ORMs for rust, you can do many things you can do in OOP in Rust too, cant say i miss something from C# (and I write much more C# than Rust) rather the otherway around.
@@lindblomdev rust orm are terrible, expecially diesel, i don't know why people reccomend that disaster lib
agreed Rust is an atrocity that should have never been made. It's a massive step backwards from a development point of view.. All of the documentation examples are procedural. The syntax is a mess. It's apparent that every dev pushing the greatness of procedural languages has never had a job where they work on a large code base.
Cool.
i always wonder why use orm
That is a video someone else will have to make 😅
I agree avoid use ORM in simple scenarios and avoid ORMs that polute domain entity, but ORM shine when we have complexes data structures with relationships, try to make a raw select and mount manually a complex structure, for example, order with OrderItens and each OrderItem with Product data, its very complicated
We should try that. And compare the amount of data that is fetched. It feels that the orm will do some overfetching in this scenario, especially if you don't need all columns on each product.
sqlc in Go is better
it looks cool. Does it also ensure that your sql actually works agains the db? There are some comments around dynamic queries, do you know how sqlc handles that? Example: you have a graphql where you want to hit the db directly, but only fetch the fields that the query defined as sub fields and the where clause should be constructed from the params given to the graphql query.
Not trying to poke holes in sqlc, just curious if you know this? as i believe i would loose my compile time checks if i where to do in with my current knowledge of sqlx. It would work, but it might blow up during runtime if i mess it up.
Did you know Prisma?
No, I rarely write backends in node (anymore), only if they are part of the frontend rendering/proxy, but not the main application. I think i forgot one of my main points for using SQL in your day to day development, and im pretty sure one does not write very much sql when using prisma. Its that you as a developer get better at SQL, which is i believe is very important as you probably need to run some ad-hoc queries at some point in the future against the production db. Then its much nicer if you are a native speaker of the sql language.
Either way, thank you for mentioning prisma, I will check it out 😊👍
Using terrible SQL language in large applications without ORM is a recipe for bug ridden software.
yeah, terrible sql sounds like a bad idea, good sql on the other hand 👍😉
@@lindblomdev which is an utopic scenario, most project are a complete train wreck from a DB design pov, this is why orms were invented in the first place, to abstract that mess, i like this workflow but is really not sustainable for a big project where dozens of devs are working on at the same time
100%
This is not developer friendly at all.
in which way do you mean this is not developer friendly?
@@lindblomdev making multiple relationships in SQL and it gives a flat resultset and it's not convenient to use. ORM made it simple. Yes ORM operations are expensive sometimes. I suggest to use SQL on such situations only. Here we have to use alias, joins etc to get results.
Stop using sql...
but its so fun :)
I guess You probably had experience with some lousy orm libs ;). In hibernate you achieve what you’ve shown with no effort.
Or, i'm just embarrassed of how bad the ORMs has kept me on writing sql, as i rarely had to do it, and trying to shift blame 😆. Whoa, Hibernate, that is some real OG stuff you got on our hands 😎