Let's Build A Web API - ASP NET Core, Dapper, DbUp, and SQLite

when it comes to building web applications in net there are tons of different packages that we can use and one of the most popular ones is Entity framework core but I often stick to a couple of other packages that replace this hi my name is Nick centino and I'm a principal software engineering manager at Microsoft in previous videos I've talked about using Dapper I've talked about using DB up and in this video I want to walk you through building a very simple web application in net using both of those things instead of Entity framework core now the goal of this video is not to say that Entity framework core sucks or anything like that and in fact I think it's actually a very awesome package to be able to use I think it solves a ton of needs for most people if I'm being honest it's probably just because I don't spend enough time with it and that's because I haven't carved out the time to go sit down and force myself to use it but in this video we're going to look at using Dapper and DB up together in order to get some migrations in place for web application so if that sounds interesting remember to subscribe to the channel and check out that pined comment for my courses on dome Trin and with that said let's jump over to visual studio and jump into everyone's favorite sample application the weather app all right so on my screen right now I have a bit more code than what comes with the traditional weather application but you can see that I do still have this weather forecast rad and I'm going to walk you through the different pieces that we're going to need to build this application so to start off I want to jump into the Nate packages so I'm going to go over to the project itself you'll see that I have Dapper referenced here I am going to be using SQL light with both Dapper and DB up so you'll notice that I have these two DB up packages the core one and the sqlite one as well and then I also have Microsoft data sqlite you can follow along with this entire video and you can pick something that's different than sqlite if you don't want to use sqlite in fact in most production environments you're probably not using sqlite unless you're building a mobile app or something like that I'm just doing this for Speed of development to put this video together but you can absolutely swap out the different pieces here for postgress my SQL SQL Server anything you want so just a heads up that's why I'm using these particular packages you're going to need a set like this and again you can pick a different database implementation and just remember that you pick the alternative dbup package for that as well once we have these in place I'm going to walk through the structure of this application and talk about the different pieces that we're going to need to get this going so of course we're going to need our minimal API and I'm going to come back to the details about what's going on here because it's a little bit different than the one that comes with sample app but the concept the core idea of what it's going to do are very much the same it's just that we're going to be loading that data from a database instead what I want to start off with is a DB up migrator background service and you can do this in many different ways and I want to touch on this for a moment because I think this came up in comments whether it was on YouTube or LinkedIn where I was posting information about DB up some people were saying well hey if you're going to be doing database migrations on app startup like isn't that going going to be a problem if you're doing uh microservices and things like that and the answer is absolutely yes you can't just blindly say okay I'm going to do on every startup of every web service I'm going to try to go migrate the database you can totally get away with this kind of thing if you have a monolith if you have things controlled but the idea is that you need to think about where these things and when these things are going to run so when I put together videos like this my goal is not to say hey just copy and paste this code and you have a working application I'm trying to give you different pieces to play with and it's still going to require a bit of homework on your end to go do the right thing in your circumstances in this video we're going to look at having a background service that does migrations on Startup that means that you're probably not going to want to use the exact same thing in your application if you have different instances of this thing if your migrations are going to be such that uh if you have an intermediate step or you don't have an intermediate step in the migration what's going to happen to the other services that don't know how to read from the database in that current state so you have to think about all of these things and this is another reason why people generally say hey look if you don't need microservices don't do it um start with a monolith start with something that's a lot more easy to control the deployments for there's lots of complexity that comes with multiple instances so in this case this is going to be a monolithic application it's very tiny it's a single endpoint and we are going to have one instance of the database one instance of the the service so when I go to make this background service it is going to on every app start try to migrate the database if it needs it and like I said if that's not going to work in your case you're going to want to think about when and how to do those migrations so I just want to call that out so you'll notice I have this public sealed class DB up migrator from line 61 down to 77 and I'm going to walk through what that does so to start off it's going to take in a DB configuration we're going to jump over to this in just a moment and what you'll notice is if you've watched the previous videos on dbup that I put together this is very much the same idea we're going to get an upgrader and we need to build that upgrader so we're going to be using sqlite like I said so we'll read in the connection string off our DB configuration we'll jump over to that shortly we are going to run the scripts that we have embedded in our assembly there's different ways to do this but we are going to have migration scripts that are embedded in the assembly so this line here on line 71 is going to load that for us uh log to console is as you might guess just going to log some information on the console as it's doing the upgrade and then we go build that upgrader line 75 is just going to perform that upgrade for us and once this whole thing finishes running we should have a database that's in a working state for our application so all of the migrations should have finished and we should be good to go use it that's going to be the background service and just to show you if I scroll all the way back up to the top you can see that I'm adding a hosted service called DB up migrator right on to the service collection doing it this way means that asp.net core is going to start up this service for us we don't have to do anything else it will just go run it on Startup that means that we get migration on Startup I wanted to jump over to this DB config so let's go jump into that so we can see it's going to be a couple layers of things going on here the idea with this DB configuration is that I just wanted a uh sort of a simplified encapsulation of the settings that we have in the app settings so you'll notice I'm taking into this primary Constructor and ie configuration which is going to be coming from app settings from there I'm looking for a section called connection strings and then I'm getting connection strings which if you look a little bit above on line 79 to 80 this is just going to be a uh a record so a data transfer object that has the different properties that we're interested in that map to the app settings what I do is I get that so if you look at what I've highlighted from 85 to 87 once we've executed this part we now have an instance of this connection strings record and then we can pull off the default connection property and of course what I'm doing is just to be a little bit more safe is that if that doesn't exist I'm explicitly throwing an exception instead of allowing like a null or something else to kind of propagate through the system then in fact what could be even better is having a little bit more specific checks around this so maybe if this is only checking if that's null so maybe if we wanted to have null or Whit space we could do a better check here to throw an exception on app startup in my opinion and you can do this in many different ways in my opinion crashing at the start of the app if you're not configured properly is uh is good practice you might have different considerations where you don't want that but if this is misconfigured the rest of our current application this weather app it just won't work so there's no point in going further if we're misconfigured before we continue on this is just a quick reminder that I do have coures available on D train if you're just getting started in your programming journey and you want to learn C you can head over to dome train I have a getting started in C course it's approximately 5 hours of content taking you from absolutely no experience to being able to program in C and after that I have my deep dive course which will take you to the next level with another 6 hours of content so that you can start building basic applications head over to D pre and check it out let's head back to the video just to show you what this looks like right so we see connection strings we see default connection if I jump over to app settings you can see that I have connection strings here and then within that I have default connection here on the right hand side we have the connection string that we're going to be using for sqlite if you're not familiar with sqlite connection strings this is a super simple one it just says where the file is right so it's going to be called weather. DB no special relative path no other settings going on here just super simple if you're using a server for being able to do this you might have login information and the host and the port and that kind of stuff but sqlite in this case is going to be very simple from line 9 to 11 this part of the config is all that we were reading in back over in this part of the code so this part right here is just going to be some code that maps to our app settings so we can go leverage it in the rest of our application at this point we've seen loading in some of the configuration and there's many ways to do this this is just one way we've seen that we have this DB up migrator background service again many ways that you can do this and I do strongly suggest that you consider how this would work in your application especially if you have multiple instances of the database multiple instances of your service running you need to think this through this one is very simple of course let's go to more interesting Parts I actually don't need this part here so let me get rid of that let's just left over from the sample app I want to talk about this route and then we're going to start running some code to go see how this works so this route is just the weather forecast route that's built in in the sample app just modified it a little bit so I'm going to be passing in the DB config I should have mentioned that this is registered onto our dependency injection container so we can pull that in on the minimal API I'm also taking in a cancellation token and I forgot that probably needs to be passed in on here so we'll go update that in just a moment in order to go perform this forecast what I wanted to do is say let's start from the time that someone executes this and go 7 days out maybe you want to have query parameters that give you a date range I'm just trying to keep it simple for the demo what we're doing here is right on the minimal API we're going to open up a connection to the database so that's line 27 and 28 here and then we're going to use Dapper in order to be able to pull this information back I like Dapper personally because I do like using SQL I like writing the SQL out myself I like seeing it I like being able to work with it if you don't you don't want to look at it you don't want to deal with it Entity framework core can ab absolutely be an option for you like I said I'm not trying to say it's bad and of course I think newer versions of nty framework core do allow you to go write your own sequel inside them if you need to so you might say well Nick why aren't you using Entity framework core and it's just because I'm comfortable with this for now I will eventually move over to EF core but that's not what this video is about this syntax is going to be for SQL light so most SQL is compatible across different databases but you do need to consider like if you're like hey I want to follow this tutorial but I'm picking a different database implementation just make sure you're paying attention to nuances with uh different variations and how SQL is interpreted for those databases this is pretty basic so nothing too fancy here this part is how we use uh parameters in Dapper so you can see I have start time and end time and then you can see right here on line 37 they're also being used now I did mention this cancellation token is up here so I want to change this a little bit and I'm pretty sure I need to do a new command definition and inside of that very much the same I just need to say cancellation token cancellation token if you are familiar with using cancellation tokens in your uh your routes and you should be you are able to cancel an HTTP request and that means that we can cancel all the downstream work and free up server resources if we need to this one's going to go pretty fast it's not going to be a big deal but in my opinion this is a best practice to be able to pass your cancellation token down right from your minimal API through the rest of the app if we look at what this is doing it's going to go run this sequel I probably would do a little bit better of a job not use star here I'm not totally against using a star for selects but I have found that Dapper can be kind of picky so if you modify the schema and you have a star there you might not realize that you have something going on and it might break and maybe that's what you want maybe you want to say hey look I've modified this schema I want to force anyone selecting from this table to go update all of their their calls maybe you want that my opinion uh usually I don't not when using Dapper at least there are cases where I will use select star so I'm not saying it's necessarily bad just something to think through but if I select these explicitly I just want to show you that ID datetime UTC and temperature C if we go down all the way to this weather dto you can see that I have the same things mapped here if we pause and look at this for just a moment you might might say something looks weird about this maybe a couple things look weird about this and you're right the first one's going to be datetime UTC as a string this is just a SQL light thing in sqlite they don't have a date time as a type so that's kind of weird you can use a number for that and do uh sort of like milliseconds or seconds from the Unix Epoch that's totally fine uh you can use strings uh I'm just using strings to make it readable for this demo but SQL it's kind of weird for this Dapper does have a problem with using a date time here it's just that sqlite doesn't support it so when I'm doing this and building a record to map with Dapper it needs to have a compatible type I'm pretty sure with Dapper you can use custom mappers and do things like that I'm just doing this to be very simple I just wanted to call out why you see it this way cuz we'll see it come up in just a moment again I have these three things that map to the different columns that I'm going to have in our database and we haven't looked at the migration for that yet but we will once we start to go run it so going all the way back up here this part is going to select in that date range I'm going to pick those three columns that we were just talking about and then you can see query a sync with this weather dto Dapper is going to do that mapping for us and we will have weather dtos to work with now when I go to do the return this is something that I like to do personally I don't like to have my data transfer objects sort of bleed between different parts of my application generally I would go build an entire repository class for this type of thing it feels like Overkill especially for this demo but just to call it out this code that I've highlighted from 27 to 46 I personally would generally pull into repository that way I can have my sort of SQL related things in one spot I might have an abstraction with an interface on that repository as well the way I like to design things but in this case I'm not doing that it's all directly in the minimal API what I mean by this part down here where you see I'm doing a select and then neing up an object is that I don't want to have things that are implementation details for my backend like specifically how I'm querying records for my database I don't want to have that bleed all the way through to the route so the caller doesn't have to know details that they shouldn't have to see so one example if you pay close attention on here where I'm neing up this object you'll notice I'm not providing the ID back I don't think that the caller of my API that's just trying to get a weather forecast needs any idea about the ID like the row ID in particular of my my SQL records they just don't need that now you might be building other systems where you want that kind of thing you need an ID for a user and you're like yep we need to pass that back to the caller absolutely but in my opinion I like making an explicit decision about that and this is where I want it to bring up the date time thing once again so I wanted to show you on line 50 I had this thing commented out and that's because when I was putting this example together I totally forgot that sqlite does not have date times so my object my record had date time UTC as a date time and what I wanted to do is on my Json that's being returned to the caller is format that in a way that I was happy with so line 50 was the example that I was going to show you where we took a datetime object and then I could explicitly format that the way that I wanted it to be shown in the Json we don't have that so I had to comment it out and I'm just passing a string back here with no different changing in the formatting or anything like that but I am removing the ID I'm not passing the row ID or in this case my explicit ID back to the caller so hopefully that's pretty straightforward I just wanted to show you that I am using Dapper to get records out of the database and then before I return them back to the caller I am doing my own transform on them to make them in the format that I want my caller to be able to see so far so good now it's probably time to go run this thing and just to recap we are going to run this DB migration part at the beginning and then my initial application URL that we're going to hit is weather forecast so when I start this thing up it's going to go hit this route let's try it out and the first thing that happens when we go to run this thing is that it fails it says there's no such table weather forecast and you can see in the URL bar here it says weather forecast like we are hitting that route and it's failing so what's going on why is this happening didn't we go set everything up properly and the answer is sort of but I left out something on purpose to be able to start walking through these different steps so we have all the right code at least in my opinion this is functioning working code but we did not include the scripts for migration so this code down here that's running and doing the migration with DB up it's trying to go look for scripts in the assembly and you can see I have this folder on the left here where there are scripts to go run but I didn't go embed them so if I click script 00001 this is the one that goes and creates my weather forecast table if we look in the top right I need to set the build action to be embedded resource so I'll go do that now and if I save that now at this point in time if I go run this we should have a weather forecast table created so let's go run now okay and now that we've run that you can see that well it's not crashing but it's also not that exciting and that's because there's nothing in here so at least at this point DB up did the migration that's cool that all worked to prove to you that that's the case you can see that it's doing the database upgrade we go upgrade the whole thing right so it actually says which script it ran then we start running our server this all worked it made the database and then dapp also worked accordingly as well so it went and queried that table but it's a brand new table there's nothing in there so this is just telling us that we're moving in the right direction there were no results so far so good it's just not exciting yet so how can we make it a little bit more exciting let's go add in another migration this is kind of silly right because I'm just using a migration to go populate data but this could be a real scenario you might have data that you need to ingest or import into your your product maybe you want to go run a migration like this that goes and seeds some data I'm not telling you this is right or wrong and this data is totally made up so you know it doesn't really matter in this case I just wanted to give you an example of how to go run an additional migration you can see that I've called it script and then this one is O2 so if I go embed this one now make it an embedded resource and save it now at this point DB up has already done the migration on the database for the first script but this second script is new one it has not yet migrated that so if I go and press play it should go now migrate this part and if I bring this part up let's see beginning database upgrade right now it's saying executing database server script right here for O2 so that's cool the upgrade was successful and if we check it out we do get results coming back so this is going to be the records that are from basically now the next 7 days out according to that last migration with that fake data and just to prove what's going on here if we take this first record right you'll see that this doesn't have the ID it's not taking that direct record from the database it's actually doing that transformation so this is part of what I was showing you especially if we look at this part right here right it's not including the ID just to do one more example here just to kind of put it all together I'm going to go ahead and delete the database right so we'll get rid of it now we're going to run both migrations together because I did them incrementally but this is going to be totally from scratch there is no database we're going to create the table with one migration seed it with the next one and then run the Snapper query let's try it and if we have a look here at the migration part we can see that it's doing script o1 and then O2 so great that part worked and if I bring over the weather application we can see that we get all the results in the Json so this is an example of it working end to endend migrating two tables and that's just a quick example of how you can use DB up and Dapper together to do database migrations and query records from your database in this example I did use SQL light it's got some weird characteristics like not having a built-in datetime type that's okay though you can take these same Concepts and apply them to different database types so I hope that you found that useful and I wish you luck trying out Dapper and DB up in your applications thanks and I'll see you next time