Dapper:
https://github.com/StackExchange/DapperTranscript (Powered by Otter.ai. Please send corrections to george@georgestocker.com)
Hi, my name is George Stocker, and welcome to the build better software podcast. Today we are talking about object relational mappers. Now object relational mappers or ORM s, as they're commonly known, are used by developers and development teams to not write SQL. Now here's what I mean by that. Whenever you're using a relational database, somewhere, somehow you have to write sequel object relational mapper does that for you. Here's how it does it, you create a poco in C sharp, or a plain old C sharp class, and you decorate it with some metadata to tell your ORM that this class represents a table in your database. Now whenever you ask the database for the information from that table using C sharp code, and if you're using C sharp past link 3.5, and you're using some form of language integrated query or link style queries to pull data from the database It translates your link into an actual SQL statement that gets executed by the database server. Now, this is really cool. If you didn't have this, you'd always have to write SQL in your code, map that SQL when it returns a data reader or some other type of database representation of the work you're doing into your classes and be a lot of mapper code back and forth by using an ORM. You don't have to do any of this. Sounds great, right? Shouldn't everybody use orams now? And the answer to that, of course, is no, they shouldn't. Now let's talk about why arms are good when you need to pull out data that roughly matches what your classes look like. And there's nothing too difficult about those, however, or I'm start to fall apart when you need to do more complicated queries, or when a query has lots of relations. Since forums are meant to pull data back and map it to your class automatically, they work really well in simple scenarios. But if you find yourself operating at scale larger than you already can handle, then you have to start and tuning those queries that it generates. Now, the API for doing this varies from Rn to Rm. But all of them have the same thing in common, which is, they don't exactly have the same API that you would use in the database itself. For instance, if I wanted to create an index in a database, tonight would use a create index statement. in SQL Server. If I want to create an index in let's say, Entity Framework, I have two different ways of doing that. I can put the index attribute on a field or fields, or I can use their fluent API to create an index. Now none of these are just as easy and as universal as Creating an index using the databases technology. And when it comes time for me to tune the queries that use that index, it's going to be far easier for me to do that in SQL Server Management Studio than it would be to use that translation layer that the ORM would provide. Because of this, using Rmi. To create queries that need to be finely tuned, is generally a mistake. Now I'm not dumping on RMS here. The fundamental problem is that we use relational databases far too often, even when we don't need them, or EMS make it easy to use a relational database. But it makes it a lot harder to tune our code, the way a relational database would expect, in most cases where we feel like we actually need a relational database, it's more of a vestige of inertia than anything else. Not all use cases. Is are meant for a relational database, if you have relational data, and that is data that actually has relationships to other data, then consider a relational database. But if what you really need are lookups by an ID and your data fits a non relational model, consider not using a relational database using something like a document dB. Now, object relational mappers, they again make it easy to write plain classes that translate to tables and they reduce your need to write sequel. One of the lesser advantages these days of using an ORM is that you may be able to switch out your database still keep the same order. This is sometimes touted as an advantage I have yet to see it happen in person, although I hear it's happened before. It's not one of those advantages I would count on. They do reduce your need to write SQL and they allow you to do Ogg pulling data back from the database into your integrated development environment like Visual Studio. They're really good at things like when you don't need SQL or you don't care if your SQL is highly performant. It's really good to use an ORM when you just need to get a prototype working. It's also really good to use for small projects, personal projects, school projects, things that don't have high load considerations. And it's really good when your team and business context is such that speed to market is more important than maintainability and scale. It's also good when your team doesn't have a dedicated DBA. And you'd rather make changes in code than modifications to the database. Now, I don't say all this to say that, or EMS can't scale they can. Many large teams use alarms for high traffic systems and they work. The problem comes in is that you end up doing a lot of hand tuning to make it happen. If you don't, you end up dealing with two different API's both the database, which has been around since the 70s, and your ORM. And its documentation. This is problematic for most teams. And in general, the more layers you add, the more dependencies you add as a team, the more you have to understand to get new work done. That isn't safe. Don't use an ORM. But it is to say, make sure you're getting the value from the ORM before you commit to using it. Now one issue that tends to compound the problem of using ORM is teams were the idea of consistency of data access is more important than ease of data access. Here's what I mean. If a team says we're going all in on Entity Framework, you must use Entity Framework code first, for all database work. Now immediately, anyone who's used Entity Framework code first will find the problems with that statement. Entity Framework code first does not have first class citizens like views. So if you need a view, you can't even do it through code first. Now, other problems crop up, and they're small problems. Some are some aren't. But they all come down to, there is no ORM out there, that gives you the entire API available to your database. And if you don't know your exact use cases, present and future, then you run the risk of running into problems, when you say consistency is more important than ease of data access. An easy way to get around this is to say you know what, in some cases, we will use RMS and others we will not make that a part of your planning discussions. Because sometimes solve a problem, the easiest way to do it is to read a database index, or a database view, or a stored procedure, or report. And those are things that orms generally have problems with. Now not all teams should consider ORMs. I typically shy away from ORMs entirely. If I need one I'll use a micro ORM like dapper. When I need that simple data access from column to C# property. When I need anything more complicated or anything where I need to tune it, then I'll write my own SQL in code and go from there. This works out well, Since Dapper takes care of that mapping between Column and C# Class Property. RElational Mappers are also not for teams that have dedicated DBAS. And when I talk about dedicated DBAS, I mean DBAs that can help you tune queries and help keep the Database at peak performance. Probably just insert a translation layer that will be harder to debug, and harder to tune. And if your team has a good understanding of SQL, relational database engines, maybe you don't need that ORM maybe the time it takes you to translate code into SQL isn't enough to justify the cost of an ORM. And that's really what all of this comes down to is cost. What are you getting out of an ORM? And what are you paying for using it? And the answer is never nothing. You're always paying in something, even if you don't know it yet. That's it for this week on the build better software podcast. I'm your host, George Stocker, and I hope you'll join me again next time.
Transcribed by https://otter.ai