Episode Transcript
[00:00:00] Speaker A: Hello.
[00:00:00] Speaker B: Welcome to the Robert Duck Dev Show. I'm Chris.
[00:00:03] Speaker A: I'm Krusten.
[00:00:04] Speaker B: And today we're going to talk about ORM versus SQL.
We'll dig into that in just a couple of minutes. But before we do that, we can review. How was your week?
[00:00:16] Speaker A: I am spending as much time as I can working on the course, prepping it for release. January 29 is the projected date. This is my PostgreSQL performance Optimization course.
What's making is a little bit different than other courses that I've done, is I literally.
Well, let me take a step back. I pledged to have a multi terabyte database to be doing the testing for. So I have the schema all set up, but basically I need to program how to create all of this data and the amount of time it's going to take to churn through. Basically, my machine is going to be running 24/7 for quite a while, building up all this fabricated data, essentially, but trying to get it as close to real world as possible.
So this is just a whole nother level that I'm taking on I need to do before I can actually record the lion's share of the content. So I'm spending a whole lot of time doing that, basically. And then as time is available, do everything else that I need to be doing for the company. Right. That's pretty much a lot of what I've been working on. How about you?
[00:01:46] Speaker B: Well, we've got our December 15, we've got our code freeze. So things were going well. But of course, there's always things going well. Yeah, well, there's always the last minute 13th hour. Oh, my God, we missed a thing. Thing.
So there's all those things going on. Like any production release almost ever goes through that little fun fire drill.
So next week is going to be a bit of a scramble for me. Just a lot of I's crossed and T's dotted that need to go on then. But after that, I'll get to kind of take a deep breath and take some time off and rejuvenate a little bit. Right now I've got a bit of a ball buster where we use Travis as our CI CD, and I've been doing this RSwAG stuff to get the API Docs into Swagger. And I've run into the situation where Our normal suite uses transactional. Oh, hi. Focus.
Our normal.
Come on, camera. All right, there we go.
I'm not that ugly, am I? Jeez. Our normal specs, we use database cleaner, and it uses a transaction cleaning strategy.
[00:03:21] Speaker A: Right.
[00:03:22] Speaker B: But for RSWAG, in some of the cases, you can't do that because it ends up doing multilayered nesting of the transactions because of how we're making the calls and stuff. So I need to make it for the RSWAG test. They need to just be the truncation strategy, and that works perfectly fine. I've got it all set up so database cleaner. If it's RSWAG, make it this. If it's make it trunk, and if it's not, make it transactional. But when I put it on Travis, it's given me all kinds of fits. And it's like it's loading the RSWAG helper when it shouldn't be, and it's looking at the commands wrong. And so things are breaking all over Travis.
And then for some reason, the Travis setup is running postgres when this particular platform app runs MySQL. So I'm, like, looking at this know, and this stuff was all set up years ago, long before I got there. I'm looking at this going, why in the hell are we running postgres in this test set up when we don't use postgres in the app?
[00:04:40] Speaker A: Okay. Right.
[00:04:43] Speaker B: So I think maybe what has happened is that there was some kind of, when they were setting up all the different apps, because we've got, and so we've got 8 million different little apps, and they were setting those up in Travis, and they were probably doing a lot of cut and paste because most of them do use postgres.
And that got kind of spaghetti into the setup for this thing. And now I can't unspaguettify it. So that's my next major hurdle is trying to figure out how to get Travis to play nice again.
So it's always just the weird crap that shouldn't be a problem.
[00:05:24] Speaker A: But, yeah, it's the outliers that become the time, right?
[00:05:29] Speaker B: Yeah. This project that's going to take us three months takes five days, and then, oh, this little five minute problem turns into a month long slog.
[00:05:38] Speaker A: Yeah. Because I hate that so much. Because I'm sitting there, I was like, this should be easier. What's going on? It just drives me bananas.
[00:05:47] Speaker B: So speaking of complicated things that should be easier, we're going to talk about ORM versus SQL. So for those of you who don't deal with that stuff, SQL is structured query language. So that's what you talk to a database with. That's, show me this stuff. Put this stuff in there. Take this stuff out.
[00:06:07] Speaker A: At least a relational database.
[00:06:09] Speaker B: Right? Relational, yeah, ORM object relational mapping is kind of a wrapper on that. That lets the language talk in the language that it talks in. So like Ruby, you tell it, hey, give me these things. You make the request to the database, talk to the database through Ruby, and the ORM translates that into SQL behind the scenes to actually send it to the database. So you can kind of think of ORM as like an SQL wrapper.
[00:06:45] Speaker A: Yeah, well, I mean the ORM, it basically means it maps the objects in Ruby to the relations in the database and vice versa. But yeah, it's clearly a wrapper.
[00:06:58] Speaker B: Right. And so there are some.
[00:07:01] Speaker A: But it never has a top 100 head. Yeah, right.
[00:07:06] Speaker B: And there are some people that are like, ORM is evil, horrible, bad and nasty. Just let me write SQL. Shut up and get out of my way. And then there are people that are like, well, why would you write SQL? That's gross and nasty and evil and horrible.
[00:07:22] Speaker A: Exactly.
[00:07:23] Speaker B: OrM is the way to go. So we're going to kind of talk about pros and cons of those things and then we'll hear from you guys in the comments what you think about this. So first of all, what do you prefer, Orm or SQL?
[00:07:43] Speaker A: That's an interesting question. What do I prefer?
So when I am working with data in the database, I prefer it's, to me it's simple, easy to work with. It's actually what I started working with first before Ruby.
And a lot of times when I need to pull back some relatively complex data, the first thing I do is I write it in SQL.
I don't open my rails console immediately to do that. So then what I have to do a lot of times is I do it in SQL and then I translate it to the ORM to active recOrd. In the case of Ruby on Rails case. So I actually translate it to that. Now you're probably thinking, why the heck are you translating it? Well, that'll get kind of thinking that a little bit. Well, that'll get into long explanation, but basically.
So in terms of what I prefer, that's what I prefer. I prefer SQL. If I was never introduced to active record early on, I might have just stuck with SQL because again, that's just how I think I'm more accustomed to it.
And I keep on having to drop down to SQL. Like I have a where statement with double quotes with actually what it should be within there. So the DSL is already polluted to my thinking when I have to work around lack of features in the active record OrM.
So that's another reason.
So that's my preference. But why do I in spite of that, still write most of the stuff in the OrM? And that is because I try to match whatever the style is of the project I'm working on. So if I have tons of consulting clients, they all use the OrM. I'm going to be using the ORM if they happen to choose some other thing and using some SQL weird thing to do or SQL weird thing. Well, I'm going to do the SQL weird thing just to match what their code base is because I'm of the opinion when you step into a code base there should be certain standards that everyone is going to follow. So if I'm going to join a project and start writing and I basically try to write to the style of how the code already exists, okay.
Even though I have that preference, I tend to just write in the style of the existing code. And 90% of the time everybody's using Orms, right?
[00:10:32] Speaker B: That's true. So if you were doing like a Greenfield project in a vacuum all by yourself, you would pick SQL by default.
[00:10:44] Speaker A: But then at this point, not necessarily because I also have to know there is a point in the future where I will probably bring on another Rails developer to assist me. Or maybe I step back and do more strategic stuff and day to day coding is taken over by another developer. Well, I don't want him to have to endure SQl stuff that he's like, this is Greek. I don't know what you're talking about here, man.
[00:11:16] Speaker B: Yeah, that's a problem.
[00:11:20] Speaker A: Again, it's not only matching what the project does, but it's also in the general ecosystem of developers.
What do people tend to do? And if you're talking about Ruby on Rails project, everybody's using active record or the vast majority, there are some that are in the I hate it so much, raw SQL or whatever or some other mapper solution. So because of that I still go ahead and use active record. And even though there's a lot of people that complain about it, I still use it for 95% of my queries. So there are 5% where I'm like, this is just going to be too difficult. I'm just going to do, or I'd have to research a whole lot of how to do it if I can even do it in active record.
So I'm just going to drop down to SQL and do it that way.
[00:12:26] Speaker B: Yeah, see I actually have kind of the reverse preference myself.
And it's not that I can't write SQL. I spent 20 years doing SQL, so I would consider myself kind of master level at SQL.
In fact, I've written such complex SQLs that I had one that I wrote that was, I think, eleven and a half pages printed out one query. It was so insanely complex because the database structure underneath it that I was querying against was stupid.
So I can write queries. I prefer ORM for a couple of reasons. One, I'm not doing complex querying like that anymore. So OrM, I very rarely run into a limitation with the, I'm speaking of rails, ORM with active record. I very rarely run into a limitation where I need to switch over to SQL because the ORM won't handle it easily.
So that's one thing. And it's just easier for me to think in kind of stuttered English than query.
Two, it's an abstraction layer. So to me it's more maintainable not only from the fact that more people read it now than SQL in the rails world, but if I need to switch from postgres to MySQL, I don't have to make as many changes if I'm all ORM rather than if I'm direct query.
[00:14:10] Speaker A: They're pretty much following the same SQL standard. So there's not too much to that. No, it's not to a large point.
[00:14:20] Speaker B: Yeah, but there still are some differences and at least the Orm world that I have to work in, it makes that a simple switch. The only thing I have to do is change the database YAML file over which I have actually done on this project because I wanted to see how much differently things would run in postgres than in MySQL.
Turns out not a whole lot. There's a little bit of improvement, but not worth a big switch to all our customers.
But it was just changing the database YAML and pointing to postgres instead of a MySQL, which was nice because I've been through things where it was more directly into the database and the SQL stuff and we wanted to change databases and it was gross.
It was not nearly as simple.
So I see abstraction benefits there.
And to me it's just easier. My brain has an easier time rubber ducking in that language than in SQL.
Really? Personal preference, I think. I think the biggest difference between the two is how many developers use one over the other for maintainability. Like you brought up.
[00:15:54] Speaker A: Well, the vast majority learned it because they were learning Ruby on Rails and what is taught in all the books and classes.
It's the active record orm is what's taught. So maybe they had a database class in college that had SQL in it. Other than that, how much do they really?
My perception is not a lot of developers have learned a lot about SQL. I mean, maybe they've learned some of the basics and can read queries and what. I mean, I think most of them have kind of the basics. I think they could do a select query to grab a few things. But apart from that, I think it starts to drop down pretty quickly. The number of people that can do a whole lot more with it.
[00:16:52] Speaker B: Yeah, more advanced things like in and outer joins and unions and sub queries and all that kind of stuff when you should use them, when you shouldn't, how you should index tables, how you shouldn't.
Yeah, I would agree. I don't think most developers now, rails developers now and most other frameworky type environments probably don't know a whole lot of SQL.
I would say though, to you developers who do that and maybe you can do a simple select or a simple creator update.
I would highly recommend learning some more advanced SQL and spending some time in that because it helps you figure out what you should be doing with the ORM, if you know how that ORM translates to SQL and what that means in the SQL language.
Because there are a lot of things you could do with ORM that you wouldn't know you could do if you didn't kind of understand the underpinnings of the SQL that it was used.
[00:18:07] Speaker A: Yeah, learning one helps you learn the other. In other words, knowing that, oh, I can do some of these things with SQL, oh, well, how can I do that in the OrM? And then you add a new tool to your toolbox essentially, right?
[00:18:23] Speaker B: Yeah.
Something like understanding the difference between outer joins and inner joins and left and right joins and things like that can help you figure out how you want to put data together and how you need to structure your data in order to report it in certain ways to be able to report it.
[00:18:45] Speaker A: I think I may have mentioned this in a previous episode, but I was working with a client who needed to build a ranking system for something and he said, hey, I kind of want to build this ranking system. Here's the criteria as to prioritize things. And it was a simple order, would not do it.
So looking at all the requirements and I was thinking about all the rails code this could potentially involve. And I'm like, wait a minute, I think I can use window functions in SQL to do this. So I use the window functions of ranking to precisely pull the data exactly as needed with one query and no other Ruby code required. And I was like, awesome, there you go. Because it had like, I don't know, like ten different line item requirements. I said, all right, let's just try doing an SQL to see where it falls apart. And I was able to do Boo Boo Boo Boo. I did all of them. I was like, all right, great, it works.
[00:19:59] Speaker B: Yeah, well, that's a good point that if you know, there are times when the ORM can't do some things that would be very useful to do that, you have to go in and do direct query stuff to accomplish that in.
[00:20:16] Speaker A: A reasonable, much more efficiently.
[00:20:20] Speaker B: Right.
[00:20:21] Speaker A: Not only in probably lines of code, so definitely less lines of code and more performantly.
[00:20:30] Speaker B: Right? Because if you can send one good query off to the database and let it do its thing rather than sending 15 different queries that come back and give me this piece now give me this piece if you know how to do that in just a second.
[00:20:46] Speaker A: Now you got to iterate and filter rails code and that's just blew things down.
[00:20:52] Speaker B: Yeah. So knowing SQL can be hugely helpful because there are times with ORM and while I mentioned earlier, I don't have to deal with that much anymore because our stuff is fairly simplistic from that standpoint.
I have in my career many times said I can't do this efficiently with the ORM. I need to do it directly with the query. And as much as I'd rather do it with the ORM, the better choice was the query.
[00:21:21] Speaker A: And like I said, 95% of the time I'm using the ORM because again, I'm 95% of the time dealing with super simple stuff. And I learned Ruby on rails from the same books and courses and whatever as everyone else. So I've been taught it.
So I never really questioned my preference.
But looking back at my preference, I still think I like the SQL.
But the other 5% of the time I'm like, all right, I'm going to drop down, do it in SQL and deal with the data as it comes.
[00:21:57] Speaker B: Yeah, I don't really see a big one is so much better than the other. They're both useful in certain conditions. I can understand people liking the RM because it's a little simpler to wrap your head around, especially if you're a newer programmer.
SQL isn't exactly a simple language to learn right off.
[00:22:25] Speaker A: Why?
[00:22:26] Speaker B: Well, yeah, if you've been doing it for 30 years, of course it's simple, but yes, you can learn it.
[00:22:33] Speaker A: I want front table.
[00:22:35] Speaker B: Right.
But what I'm saying is it's easier to learn the OrM than it is to learn the query, the SQL directly in my estimation anyway. But it's a little bit more natural, at least the rails. The active record is a little bit more natural language than query stuff because even just understanding the verbs like select and where and stuff is update.
[00:23:11] Speaker A: And.
[00:23:11] Speaker B: The formatting of the language is not always intuitive.
So, not that it's difficult, but not quite as easy as active record, I think.
So anywho, but I don't see the big fuss one way or the other, honestly, because I have run into, not personally, but I've run into things online where people are like just oh God, never use Orms, or oh God, never use SQL directly. And I'm like, why?
[00:23:49] Speaker A: I think the people that don't want to use SQL directly is because they don't want to deal with the SQL. They want you to stick with the Orm so they don't have to deal with it.
And then there's people that hate the Orms. And really that's because of basically impedance mismatch. Basically there's a whole lot of things objects can do. There's all, you know, things like inheritance, and it can be difficult to map that to the relational database structure.
I think therein is why they dislike the ORM or the decisions that some of the Orms make.
[00:24:32] Speaker B: Yeah. And there are cases where the ORM does things in a less efficient way than you probably should.
[00:24:41] Speaker A: Yeah.
A little bit is trust in the Orm, trust in its magic. So it may be doing some things that you're like, what? It's doing this? Why is it doing this? Because I've seen certain cases with stories like when I was doing some scaling postgres episodes where it was setting certain configuration values to do something that was causing problems. And it's like, why is it doing, there's just this questioning thing as to the problem was caused by things the OrM was doing that the developer didn't know about.
So it's kind of like you have a trigger going off you didn't expect or a callback that went off or didn't go off that you didn't expect.
I think that kind of frustrates some programmers.
[00:25:31] Speaker B: Right?
If you're going to use an orm, you really should have something in your development kit like bullet or those kind of reporting things that will tell you, hey, I had to run all these queries. You may have an N Plus one here. This one took this long and it gives you the actual queries that we're running. That's a huge help when you're using Orms because you don't necessarily know exactly what queries are popping off back there.
So it's something you really should pay attention to. And you could read the logs, but it's much easier to have a gem like bullet or something to just tell you right on the page. Here's all the crap I had to run to get this stuff to your eyeballs.
[00:26:15] Speaker A: But, you know, isn't active record, isn't there? What's the command to get the SQL of a.
[00:26:25] Speaker B: Two underscore query, I think, or two underscore SQL? I can't remember which one is which in active record.
[00:26:36] Speaker A: Yes.
[00:26:40] Speaker B: So you can do an Orm call and just put the two query or two SQL can't remember on the end and it'll show you the query.
[00:26:50] Speaker A: Yeah, that's what I was talking about. Yeah.
So I mean, if you do that, then you can basically help teach yourself a little bit of SQL if you want. Yes.
[00:27:03] Speaker B: And that's a great way to start learning because if you understand what or.
[00:27:07] Speaker A: Even just look at the rails log because it shows you the actual queries as it's doing stuff. This account load is doing this SQL query.
[00:27:19] Speaker B: Well, yeah, that's a good way to learn it. Although I will say I don't particularly like looking through logs. I'd rather have other ways like bullet or two query.
[00:27:31] Speaker A: Oh, no, totally. Because it points things out to you. It's more like, hey, there's an exception, dude, pay attention to this, rather than let me just look through the logs. I'm just saying, if you're at the point where you kind of want to learn a little bit more about SQL and you're not that familiar with it, you could kind of just load up a page and say, all right, let me just look at the SQL that's generated, how many different ones were generated? So I'm saying it's more of an educational thing, not from a alerting you, hey, you got an M plus one query going on or whatever else.
[00:28:07] Speaker B: Yes.
So make sure that I'm a big proponent of hi English. I'm a big proponent of Orms.
I like them myself. But I think it's important to understand SQL and to know it pretty well. You're going to be doing that kind of. If you're not doing front end programming, if you're doing backend programming, you should understand SQL.
If you're just doing the JavaScript stuff, if you're doing all front end things.
Not really.
[00:28:38] Speaker A: Pretty much need to know is the API right?
[00:28:42] Speaker B: You don't care about SQL, but if you're doing the backend programming, if you're writing things with an ORM, you should understand the queries that go on behind them so that you know when you should switch to query directly because it's more efficient than the OrM call.
[00:29:01] Speaker A: Yeah, it's kind of like any developer should know the API. The front ends need to know the API they're working with. Well, the backend developers, if you're talking to a database, you should kind of know the APA language that that's using too.
[00:29:14] Speaker B: Yep, that's a know. I don't really see any other big sticking points between SQL and RRMs, do you?
[00:29:29] Speaker A: No.
I find it interesting that because even though, like I said, I have preferences, I harmoniously work between them. It's not rocket science to use one or the other.
Again, 95% of the time I'm using the ORM just because that's what most people use and I want to match kind of what the general developer community is using in case I ever want to bring on help in case I'm working with a client in a consulting engagement.
But I have no qualms about dropping down and using SQL if needed for the other 5% of the time.
[00:30:14] Speaker B: Yeah, and so I think the long and short of it is don't argue about which side of the fence you sit on. Let's just knock the fence down and hang out in both yards.
[00:30:26] Speaker A: Learn a little bit of both and find out how you want to leverage and use it.
[00:30:33] Speaker B: Right. So that's our PSA for today.
So all you new developers starting out, make sure you kind of go down both paths and learn as you go.
Any of you guys that are just feet planted on one side or the otheR, let us know in the comments.
Why are you pro Orm?
[00:30:58] Speaker A: I'm sure we've missed some things that the anti Orm people despise. So if we miss something, put it in the comments.
[00:31:09] Speaker B: Yeah, and it's a broad topic. I mean, there's a lot of stuff to talk about here, but we don't have an eight hour show.
That's our Reader's Digest condensed version of our thoughts, but let us know your thoughts in the comments.
Hope you guys enjoyed that. If you did, please make sure to like and subscribe and make sure you ding that notification bell so you know, when we go, I was going to say go live, but we don't go live very often anymore, but when we upload videos we are going to take a break for the holidays. We're tired. We need a little break. I'm sure you won't begrudge us taking a little nap here for the next so we are going to take the next three weeks off and we will be back in January with some very big changes. So make sure you stay tuned to all the channels. You can reach us on X at Ducky Dev Show. You can come to rubberduckdevshow.com, sign up for the newsletter and see all of our videos there. You can reach out to us on the rubber Duckdev show Discord. That's the thing we do and all of those links will be in the description below.
Please let us know what you think about this topic. Please let us know if you have any other topics that you would like to see on the show and we will take them on board and try to get them scheduled in. Like I said, we will be off for three weeks, but we will be back in January with some very big changes. Very exciting. So you'll want to make sure to stay in touch with us for that.
So we will see you guys then. We hope you have a very Merry Christmas or a Happy Hanukkah or whichever thing you celebrate. Happy one of those until January. Happy programming.
[00:32:56] Speaker A: Happy programming.
