Build a Web API From Scratch - Principal Software Engineering Manager AMA

I think that's all of them I'm just going to double check LinkedIn as well looks like that's coming through cool I think we're good to go um chat should be live this is one of those fun things that I'm never totally confident in so if you're joining in you have chat access if you want to say hi that's awesome lets me know that I'm getting your messages because I would feel terrible if you were asking me stuff and I couldn't see so go ahead and do that if you want let me know you're here um last time it's been a couple weeks actually I had a little vacation and uh I think last time we had almost almost the entire stream was from Twitter which is really cool um I think usually we get a majority of the people coming from I want to say like YouTube's been pretty good thanks David see Twitter representing already uh yeah so it's been last time was very heavy on Twitter which is cool um I feel like before it was like a lot of LinkedIn a lot of YouTube there we go uh I don't know if I can pronounce your name properly so I apologize uh Nome I believe uh good to see you but we got YouTube that's good um linkedin's always weird it comes in like in comments and stuff um and I think people can comment in and it goes to chat but chat doesn't go back to the LinkedIn comments something weird like that so I got to remember if I'm sharing links to do that properly um this stream's going to be a little bit different and it's going to be different because usually every week I do two streams I've been trying to at least I do a stream at this time which is generally like software engineering in general so mostly career advice things like that um but stuff that's you know very uh language agnostic just uh ideally I like to think tips tricks um and things from my experience as an engineering manager and um because I haven't done uh streams consistently the past couple weeks because of vacation and some other stuff and because this week I'm probably not going to do the same thing like I can't make tomorrow stream um what I wanted to do was I wrote my newsletter about uh building a web API from scratch in C so if you're more experienced as a c developer total transparency this may not be totally exciting cuz you might say Nick I already know how to do that that's totally cool if you want to hang out that's awesome um because I am going to be coding live today that's usually what I do on the Tuesday Morning streams so the more people that hang out while I'm coding the more opportunity there is for you to point out my mistakes because they're going to happen it's part of coding things live can't get around it might as well accept it um but my goal is that we're going to basically go from scratch uh when I say from scratch let me clarify we're going to take the template app in Visual Studio to go build a web API but I wanted to show how you can hook up a database how you can hook up migrations and things like that and because it's live I just wanted to give people that are more Junior the opportunity to chime in and ask questions um I see TT too slow uh from from Instagram good to see you buddy um yeah so I think I I just wanted to like folks to know like please do ask questions um because this is going to be geared for more Junior software developers now you might have experience in another language so maybe you're a JavaScript developer maybe you've been spending more time in front end and stuff like that which is totally cool um but because this is going to be focused entirely on backend development making a web API working with a database like if you're not familiar with anything I'm saying I just want to make sure that I take the time to answer your questions um the only mistake I could on Instagram the only mistake I could think of is if the Greek question mark but that's unlikely for you to accidentally put it in I shall not um and hey on Twitch I think chat's working across the board so that's good to see um to get started what I'm going to do is I'm just going to put the link to the newsletter that I wrote because it will have links to stuff uh let me see one sec and no pressure to subscribe or anything like I post across social media all the time so um I don't like forcing people like it's great if you want to subscribe um to my newsletter but otherwise that's cool because odds are you probably saw something I was posting anyway unless probably people on Twitter maybe it's a random uh encounter I think uh Tik Tok is probably lots of random people just stopping in so that's cool too but that's the link to the newsletter uh and yeah I think we can probably get going I should remind folks too that yes this is going to be live coding and that's fine but uh if you have questions uh that are unrelated so you want to ask software engineering questions career related questions totally cool just put them into the chat I'm happy to answer uh this kind of walk through is going to be similar to another YouTube video I've already made and I like reminding people that when I do these things live the goal is that I'm giving you the opportunity to ask questions otherwise I could just be sitting here recording a YouTube video so uh happy to be interactive and answer any questions you got going on so with that said let me get over to visual studio I'm going to get my screen shared in just a moment I realized that I probably toggled the little picture so give me one sec we'll flip this over here and let me get my camera on I think it's this guy y that's the one and I see a question from you YouTube so do you believe that studying data structures and algorithms is necessary to proceed in Building Systems that may may address real world problems um two ways to answer this I think um one is if you want to be applying for jobs I would say basically guaranteed that you're going to be getting asked data structures and algorithm questions now um the other way to answer this is that I think these are foundational building blocks and you should have an understanding of them but some the level of detail that I see people going into these is pretty extreme there's going to be people that debate this a million different ways so you'll have people that if depending on their work experience they spend a ton of time working low-level stuff they're going to tell you you have to know this stuff like the back of your hand you're going to have to know all of it like super detailed um and I have literally seen people put together systems and know very little about data structures and algorithms now with that said if you know more things that you have more tools to work with you have a better understanding you can make more informed decisions so I'm not recommending that people don't know this stuff but um I would say there's like sort of like a foundational understanding that you should have like characteristics about hash maps and lists um you know you want to just have a general understanding of the stuff but like you'll see like people will make jokes like I saw a couple on I think the same joke on LinkedIn twice today about like ah yes another day like not having to reverse like a a linked list or like to sort a binary tree like like just stuff like that like you're not doing that kind of thing regularly it's just not what's happening now in some cases some jobs yes you are so like that's why I said you'll have people argue this um but generally speaking um you're not doing that kind of thing you're using things that exist yes you're going to be putting algorithms together yes you might pick the slow implementation to start with does it work yeah okay keep going so you want to get things working ship value to customers refine things reiterate and um ultimately yes understanding data structures and algorithms will help but if you were to spend like 95% of your time doing that not building things or not spending time trying to work on interpers skills communication how you develop software in general in teams like those are better opportunities in my opinion so do get a foundational understanding okay hopefully folks can see my screen I realize that when I work at this resolution so what you're seeing is one quarter of my monitor and I can zoom in on the code when it's up but I realize the menus and stuff are going to be kind of small so I apologize but to start things off because this is geared for more uh junior level Developers or people that just don't work in CP and.net this is Visual Studio yes you can use Visual Studio code I personally just don't because I like Visual Studio better and I have an Enterprise Edition so I'mma use it but uh no seriously though I'm just way more comfortable in Visual Studio I know that uh Visual Studio code has come a long way and lots of people use it especially like for me even frontend development I will go to VSS code um learning command lines too hard yep uh and I just realized folks on Instagram you can't see anything on my screen just cuz you're getting a vertical slice of the view so what I'm going to do is just start off by going to make a new project and I'm going to pick ASP core web API press next again this might be hard for you to see so I'm just kind of walking you through what I'm doing um the reason that I'm doing this is that we're going to get like a templated project made literally once I finish clicking through this little wizard we'll have something that uh you can run like we don't have to write any code it will put a template in place for us we can press play and we have a working web server cool but I don't want to stop there obviously that's why we're doing this stream together so and uh on YouTube you are welcome and thank you for the compliment I appreciate that so we can name this anything we want um again for for folks that are new to kind of CP and.net a project is going to be one of the things that is inside of a solution so a solution can have multiple projects you can think about a project being a library or an entry point program for us it's going to be the entrypoint program it's going to be our web server um so the naming you'll notice as I go to change this uh our web API um you'll see the solution name and project name change we're going to basically have a onetoone mapping solution with a project inside of it so pretty simple um It's probably hard to see unless you squint and look really closely but there's a full path to where this is going to go we'll just press next on it um for the most part you don't have to be too concerned with this screen if you just want to get going again sorry Instagram folks this is cut off for you but we're going to pick the latest framework to work with um there is net9 preview I don't have that uh I'm not going to pick any authentication types but you can pick some of this stuff and it will scaffold this into the template you will just get it for free so if you're curious about how o works like go pick that option and try it out one of the ways that uh I don't think a lot of us talk like content creators talk about learning different things that I think works well for me is just taking stuff that works already and modifying it that's sort of how I started programming in the beginning was like tweaking and changing things and in my opinion it just helps me explore and see changes come up faster sometimes when you're starting stuff from scratch you're like coding you're trying to do stuff and it's taking too long before you start to see something really happen so this is just a different way to go about learning we are going to use https just by default here I'm not going to use Docker in my own personal projects I do have Docker enabled here just so that when I'm in Visual Studio it spins up a Docker container because that's how I deploy stuff anyway uh we're not going to turn on open API support um this I don't know right now it's probably still using swagger but Microsoft is moving away from Swagger this is going to be just output uh sort of dock information like uh it's almost like interactive docs that you get for your API which is kind of cool we're just not going to do it um top level statements uh depending if you're very new to C sharp um you might not even know this is a thing but historically we used to have to make a Hello World program in C I'm got to go back to the full camera so I can talk with my hands here um we used to have like a program class right and then you'd have a main method and then you would write hello world and you'd basically have like 15 lines of code just to say hello world because there was a lot of boiler plate and now just like other you know modern programming languages you can basically just write console right line Hello World online so top level statements are just the ability to kind of skip over um sorry so on on Instagram uh I am Live on YouTube they just said uh I should go live on YouTube because it's hard to see on Instagram but yeah the idea with top level statements is now we can just write a top level statement like hello world and not have this boilerplate program CS kind of stuff going on okay let's go back to here um and we're not going to be using controllers there's something called minimal apis which again if you're brand new to this you might say what the heck is that why does that matter it's just less code to get similar functionality in my opinion it's going to be more straightforward to talk about so let's go ahead and press create and we're done that's the end of the stream just kidding but we do have a functioning web application at this point so let me go open this up I'm going to walk through what we got going on on the left as well but this is a you can see like my scroll bar if I just zoom out all the way like that's the entire program and there's stuff that is kind of extra in here anyway like there's some comments and stuff so not many lines of code and what this is going to be is an API that we can work with and uh it's kind of silly but like it will forecast the weather it's all fake information uh you can see there's some let me Zoom back in there's some uh random number generation like this is not real weather forecasting of course but we can uh run an API and hit it with our browser so what else is going on in the left here so I mentioned we have this sorry I know this part's hard to see because the font size but we have our solution inside of it we have our project right so it's a 1: one mapping um in the properties we have something called launch settings so if you're curious what this file does up at the top in Visual Studio I can run it different ways so I could if I had Docker from one of the options that was selected initially I could have Docker from this drop down at the top um like I said we're going to use https you could use IIs as well if you want to use your your windows uh web server but that information and how you can figure how you run things just in your Dev environment is done through the launch settings we're not going to touch that we are going to come back and visit app settings though app settings is where we can configure some things so without having to write code we can change configuration and basically set some variables to use from within our web API so we will come back to this um I have not used these files yet uh but these are HTTP files where you can basically call your uh web app and get responses kind of like Postman uh similar idea here but we're not going to be dealing with those in this just because I personally don't use them so I don't want to try and guide you through that without a lot of experience um on my part okay program.cs what do we got going on here let me go ahead and start removing a bit of code uh yeah just to make it a little bit more uh obvious in my opinion I'm just going to move stuff to make it more vertical so it's not getting cut off but I'll start walking us through what's happening up at the top on the first line we have this web application create Builder call here so this is going to be the thing that we use as you might guess to make a web application and args that you see past here is if we were starting this from the command line args is going to be an array of command line parameters in our case we're not setting anything there so it's just an empty array but just a heads up if you were running this from outside of Visual Studio or even if you configured your run settings you could pass parameters in to here once we do this we have a builder we're going to come back and do some stuff with the builder but not just yet you can see that without doing any other configuration we can just ask that web application Builder to go build the web app done right like literally just to make it look even more succinct um actually like just to prove a point that's a running web server with https like just a few lines of code so very simple but spoiler I just said like we're going to tell the app that we just made to use https for uh the redirection so if you hit an HTTP URL it will redirect to https this next part is going to be where it's a little bit more interesting um and I'm going to kind of jump around here I'm going to come back to the summaries that's just kind of like some fake dummy data but this is a minimal API and the reason that it's called a minimal apis because to go do this normally I should say air quotes normally with controllers you'd go uh create a whole other class you have to overwrite some stuff so you would it's just like very separate and they tried to make it more streamlined with minimal API so you'll see that we don't go make a whole other class we just provide a function so this is called an anonymous delegate which sounds um really fancy it's less fancy than it sounds it's just a method that we declare in line without a name Anonymous without a name that's all delegate method it's just a fancy way to say an inline method with no name just sounds cooler right this is going to be the route that we're hitting and then this is uh we're going to tell our web server that we want to have a get request so you can do other things you can map post patch put um delete right so if you're familiar with web apis these are different types of actions that you can be taking uh but because this is just a getter uh we will be doing a HTTP get to weather forecast now what happens inside here well this is just all dummy stuff you could technically just return uh like that if you wanted to and this will return a 200 back to the caller um in chat from twitch does minimal API include querying the API so um I might need you to elaborate on what you mean by querying so when we say minimal API all that it means is the way that this is defined it's defined in line here and this is just an a an a web API oh um okay so the the added context like customer were age greater than 25 so this part that we're talking about so far and we're going to get to more advanced things in just a moment this part is just the web API what's happening in here is just fake dummy data to go return whatever we we want like literally I could say and we can go run this and in fact like let me comment this code out and we should probably just run this to see because I've been kind of talking about code you guys haven't seen anything running yet so let me go run this to show you like we just have a web server that's going to work and return a silly string so one sec because I don't know where this is going to launch there we go boom right um so you can see that we went to this uh this route to slwe forecast and it ran the code inside there that code doesn't really do anything except return Nick is cool uh because I know with o data there's a whole thing that is added automatically that deals with the query yeah so right now uh we don't have any uh what's called like a dow a d or a data access layer we don't have any of that so there's there is nothing to query um if you wanted to have query parameters let me go close this um and this might be kind of what you're hinting at if you wanted to have query parameters you could do something like this um co-pilot that's not very helpful um I don't know we could do like a uh what's a good way to do this I wanted to do like a date range which is kind of weird um I think I have an example of this yeah I do um we could do a date range where let's do something like this string start time because this is going to be a query string like that goes in the URL and let's see you could you can annotate it this way too you can say from query um I think I don't know the order of uh how C like how the engine will try to resolve these things cuz you don't have to label these things and it will try to go find them for you like it uses some uh ordered way to try and guess at the best way to get this information um but I I always explicitly just say like I want you to go look from the query to find this parameter so now if we were to go run this you'd have to use like question mark start time equals some start time and because it's a string you could put any string you wanted to like Hello World on there so um actually let's go run it and see this is the best part right so you'll see that this doesn't work that's okay it doesn't work because we didn't put a start time on here sorry the taex is probably small so I'm going to say woohoo right and now it matches because the route itself the way we defined it needed a start time coming from the query right so if I take this part off and press enter it blows up but if I put it back on and press enter it matches the route that's there so again just the point I made earlier was that we don't have any data to query but this is how you put a query parameter into your url hopefully that helps um and then you can do like other things too like I'm not going to go into all these details but you can have like placeholders and stuff here um so you can have variables that come off of the the route path that you're looking at so all sorts of things which is pretty cool but we're not doing that for now and I'm got to take this query parameter back off because we just don't need it now what this code did before and by the way I hope that answers your question uh on Twitch let me know if there's a different way that I can try to explain that um what this code did just to kind of walk through it because we're not really going to need it but uh forecast so we're basically creating a variable called forecast and some people don't like using bar because they're like well I don't know what it is so it's this it's just going to end up being an array because I can tell at the end here to array it's going to be an array of weather forecasts so what this is saying is that we want an enumerable that's going to be the numbers 1 through five so this code here will give us an i enumerable 1 2 3 4 5 then we're saying that dot select is almost like doing a four each this is called link by the way if you're new to this uh this syntax is link l i NQ and what we're doing is we're basically saying on the Range 1 to five for each of those indexes we're going to go make a new weather forecast object now the weather forecast object we can see down here so it's defined at the end of the file here um most I shouldn't say most I don't know statistically it's pretty common practice that you would put new records or new classes into other files these are separated uh sometimes people will keep these things in the same file just to keep them organized it's totally fine right here to keep it on the same screen it's nice and easy but uh this weather forecast is using these three parameters so we need a date we need a temperature in Celsius and then we need the summary so that's why here you can see that it's passing in three different parameters so it's going to just pick the current day and add on a day for each number uh that we have and then it's going to make a random temperature so that's why I said this is not real at all that's totally fine and then it's going to add a random summary and that's going to take us back up to line seven because it's just going to pick one of these randomly now this is a pretty silly weather forecast app and I'm not picking on the people of Microsoft that made this I just say it's kind of silly because these two things are randomized and it means that you could have minus 20 show up and it could also pick scorching kind of funny um you could also have 5505 show up and it would say freezing like it's possible that you would have these things so um actually is that Max yeah max value is not inclusive so 54 is the highest number I lied but anyway you can have these really funny situations because the temperature and the summary are not consistent but doesn't really matter just a funny situation so we're going to have this array of weather forecasts and then we're just going to return them so what you saw before was me just returning a string that said hello world I think is what I said um and or maybe I said something else it's been too long and this is going to now return some Json so when we have objects they will automatically get returned as Json for us so let's go run this again right so there we go this is Json are there any funny temperatures in here uh boming at 31 bracing warm uh warm at 53 53° is warm yeah that sounds pretty warm to me sorry this is probably cut off I just realized because of the chat but yeah uh this one's kind of funny right but that's okay and then in the chat in O data let's say you get returns DB contacts customers there is some code that runs after this return and then applies custom query I think yeah it sounds like you're talking about like Entity framework core which we're going to start to move over to but not Entity framework core because I like using something else um which is fine um I want to talk about two things Dapper and DB up now enity framework cord does all of the stuff that I'm about to show you I'm not saying nty framework core is bad in fact it is arguably the most popular thing uh for an omm an object relational mapper and ORS are what are going to glue our code to databases might sound kind of funny but we're going to start working with a database in just a moment and we have stuff in the database that's great and we have code that's running also great but how do we make sure that we can put these things together so you don't have to use an omm but an orm is basically facilitating gluing those things together for you and then yes on Twitch 127 in war is also known as Arizona that's right I was actually just in Texas uh twice uh twice in the past couple weeks um the first time was to go do a presentation which is really cool I got invited down there um to Dallas and then and then last weekend not this past weekend but the weekend before my wife and I went to the CrossFit games so um yeah I had never been to to Texas at all before the past couple weeks but so hot was incredible um yeah it was just wild I've never I've never experienced that it's usually I was telling my wife historically I got to switch back to the full camera because I talk with my hands a lot historically if I go somewhere hot you walk into the Sun and you feel heat coming down on you right you're in the sun you feel the heat coming down in Texas and I'm I know there's other places in the world like this but this is my first time experiencing this where you walk outside and you could be in the shade it doesn't matter but it's like you just walk into a wall of hot doesn't matter where the sun is the sun makes it hotter of course but there was no directional heat it was just like you walk into you could like swim through the heat I've never felt anything like that but um very cool experience uh a lot of good barbecue food so I'm glad we got to go for that but anyway back to the code okay so at this point we do have a working web API and if you wanted to go add more things so let me just are we going to need this code now we're not going to need this anymore so you can go add your own routes Nick is cool like you can just go add more minimal apis like this okay so you can go do whatever you want and you can just add them like this you can make this one a um a delete like anyway you just keep doing this and then you write your code inside here so hopefully that makes sense um as I move on if folks have other questions like please do go ahead and uh yeah I got to tell people on on Instagram uh I'm Live on YouTube will be easier to see there we go um so what we're going to do is start working with the database but what I wanted to say was that if you have questions about how this is set up we're not going to need this code either um or this yeah we won't need that we're going to start with this but if you have questions about what you see here in terms of how to set up a very simple uh web API please just let me know I'm happy to come back and answer questions about this um because yeah we're going to start talking about databases and stuff and we'll see um if you have a ton of endpoints to map should you write them non anonymously in another file so yeah this is a personal preference um you will get different answers on this but um the way that I like to think about this is just about co uh code organization so some people will argue that they want to see more things in one file just to keep it collocated easy to find you're not jumping across files so like would you go put a uh an endpoint using like a partial class or something would you go sprinkle that across like 10 different files but I probably wouldn't um but I like to split them up based on sort of the functional area they're in so you'll hear some people talk about like vertical slice architecture there's a million different kind of ways to do this but I group them in a way that makes sense to me personally and you might have something that works differently for you so to give you an example um so a thing I'm building on the side is called brand ghost this is just very relevant for me so I'll talk about it um with brand ghost what we're doing is I have external apis they're kind of just an extra thing for our system and you're able to uh make post to social media so I have organized some of the end points based on the social media a platform so like if you want to make a post to Facebook or Instagram I have routes for Facebook and Instagram separately if that makes sense I have authentication endpoints separately I have uh basically for local uh debugging they like debug compile time end points in another spot that are like my debug ones so there's there's a bunch of different ways you can organize them but personally like what I I wouldn't do and I'm not saying you're not allowed to but like I wouldn't do this and then go like make your whole program like this you can you're welcome to do it but not for me and yes finally a c de twitch yeah this is all I do um not not the live streaming part but I do make if you're new to my content I put out like three fulllength C YouTube videos a week and I've been doing that for uh 20 months now so yeah infected FPS I like the clean architecture style methods Milan janovic did in a video a few months ago where the extension method to register all classes that implemented an i endpoint yeah so I like that idea but I'm like I have like a personal Vendetta against extension methods uh and no in all seriousness like the way that they're used to set things up I think is a great way to do it um I I kind of joke because well two reasons one uh a lot of my content you'll see that I'm not a big fan of static methods um the reason I don't like static methods is that I seen them get abused a lot where you have a static method that calls like really deep nested code with other static things and it's just like a total nightmare extension methods have to be static though so kind of a kind of no way around it for now but uh the other thing is that I develop a lot with plugins and when it comes to registering things uh I don't like to have my core application say go call this extension method to go set this up I do it the inverse way I scan for plugins and say you guys go register whatever the heck you want that way I can just keep adding new code and I never have to come back in here and go let's see builder. add or sorry Services we're going to do this in just a second by the way so you can do add uh transient let's say like you don't have to come do this for every time you want to extend things but uh what was being mentioned uh by infected FPS is essentially having an extension method that's like add uh cool routes something like this on the services call right uh or it's probably on the app call yeah sorry my my my apologies technically you want to do it like this um right so you build an extension method that does this and then inside this method it would go do this kind of stuff so totally great from like a syntactic sugar perspective making it readable uh truly a benefit to organizing things uh I just it's just a personal preference I write code that's very much inverted from that so uh if I have have to write this it means that my entry point my core program needs to know about my dependencies which is totally fine for like basically 99% of scenarios but I almost always write code the opposite way so just personal preference but thank you for sharing that uh Milan is awesome uh if you're watching my stuff you probably already know about Milan if you don't Milan jonovic I believe that's how you pronounce his last name uh very big uh C YouTube LinkedIn Twitter content creator he's great um yeah let's let's keep moving okay I'm going to copy and paste a couple of things because I'm cheating a little bit I just wanted to make sure I could save some time if I could and what I'm going to do is copy over some dependencies and I'm going to talk about them so we are going to be building with Dapper and DB up so that's these now the other thing you'll notice is you'll see SQL light on my screen I'll zoom in a little bit and that's because the database we're going to use a sqlite why you might ask well it's because I don't want to go set up um a database server I don't want to do that I don't want to mess around um for the purpose of discussion you can absolutely pick any other database you want 100% uh if we're going to if you're following this and you want to use DB up just pick the DB up package that is suitable for your database and in fact let me go ahead and do this let's go to manage Nate packages apologies again if the font is too small I just want to be able to show you this but if you do DB up you can see DB up postgress SQL Server MySQL SQL light uh Firebird Oracle red shift there's tons and the same thing with that ER if we go back to here Dapper for SQL light is going to be able to use this cuz it's a an like an Ado connector but there are other sort of packages that you can add with Dapper to to go to different databases so the reason I'm going to talk about the pieces that we got going on here and Entity framework core just for folks that are watching this if you're new to what I'm talking through here nity framework core is going to be sort of like the de facto thing that is replacing these so a lot of people love enity framework core and I've said this earlier in the Stream if you're just joining I'm not bashing edity framework core uh it's like the most popular om it's very powerful very extensible it's performant I just don't use it because in practice I find I'm always working against it it's truly problem I literally just need to spend more time using it on personal projects the problem is that every time I have a personal project I need to get it done and then I fall back to my other patterns that I use so I figured for this tutorial While most people probably want to see Entity framework core the reason I like talking about these is just so that you can see these pieces because Dapper is going to be the part that lets us do querying or inserting whatever you'd like and it Maps our objects to and from SQL that's going to be its job DB up on the other hand is just for migrating our schemas so what I mean by that again if that sounds like total nonsense you're like I don't know what you're saying um when we have a database like we start it there's nothing there we need something to be there to make sure that our web application works and just like your code that's evolving over time your database schema is probably going to have updates and changes over time so with dbup Entity framework core um I've been posting on Twitter these uh these threads which get a lot of Engagement it's super cool to see these different perspectives but I I think it was yesterday or the day before I can't recall now but I posted and I asked people like what they're using for migrations and you get tons of different responses so there's way more than just uh DB up and Entity framework or tons and everyone has like their opinions of it why they think it's the best uh so if you if you like this kind of thing and you want to see more people like debating like uh feel free to follow me on Twitter I ask open-ended questions and I've had pretty good responses with people just like here's what I use here's why um I've had you know beginners jumping onto threads and going like you know thank you so much because finally getting some different perspectives but um you know in the chat we see liquid base there you go like just something else there's lots of options okay so we're going to use these today not because they're the best in the world or something these are just what I use and then I want to walk through what they do so you have an understanding of how you can leverage them so hope that makes sense Okay now what's a good way to get started with this well we're we we don't have a database yet so it might make sense to get started with DB up to set up our database for us now we are going to be doing some SQL in just a moment so I don't want to scare anyone if you're brand new to SQL uh we are going to be using sqlite like I mentioned I use Dapper specifically because I like writing SQL with Entity framework core it hides a lot of that for you yes you can write raw SQL in framework core but they try to make it so that you're feeling like you're just working with C objects and Link and it's very natural that way like I said this is l a preference for me so let's go back to the program I'm going to add some code just got to copy and paste it over here because I had some of it working before remember this is going to be our minimal API so we are still going to stick with doing some weather forecasting but I'm going to drop in some more code and it's an upgrader and this might seem scary because a bunch of stuff here hey X coding good to see you it's been a little while since I've seen you on stream probably because I haven't been streaming so I apologize but it's good to see you back um this part is from DB up so let's talk about what it does deploy changes. two is going to be a static method and because we're using DB up with the SQL package sorry the sqlite package we're able to set it up with a sqlite database and we need to provide a connection string you'll see that I have connection or my goodness configuration. connection string uh just doing a time check we can probably still fit this in so we're going to want to set up a configuration so we're going to have to go back to app settings uh what we're working on is building a very simple web API in.net and we're going to be working with SQL light uh Dapper and DB up and I was just mentioning to folks that Entity framework core is probably the more popular uh approach here but I'm picking these things because they're what I use and I think they're going to help separate these pieces to talk through so we need to go build this part we need to get a connection string for folks that haven't worked with databases before this is just going to tell our code how you connect to the database like where the heck is it if you were running a database server you might say like the endpoint the port the username and password for sqlite it's going to look something that looks let's see it's going to look kind of like data source equals cool. DB like literally that's our connection string because a sqlite database is just a file that we're going to have so uh I'll leave this here just for a second because we'll come back to the connection string part this part is kind of interesting with scripts embedded in assembly and then get executing assembly I have to go on I'm talking too much and I'm not getting enough oxygen but because this is for migrating databases migrating the schema we need to give it scripts to run to do the migration and the very first migration is just creating the schema that we want to have so that'll be very simple so not not a lot to worry about but it's saying and I'll explain this because we're going to walk through it look inside of our assembly for these scripts now if you're hearing assembly and going I have no idea what that is if you remember at the very beginning of this stream or maybe not because you just joined I mentioned solution and project our project is going to produce an assembly when we build it so typically you will have a onetoone mapping of project to assembly is that a rule I don't think it has to be but uh for the most part when you're building code in C that's what you're going to end up with okay then this next part log to console if you take a wild guess what this does it will log to the console and then we're going to say that we want to go build this upgrader so this is like a fluent uh Builder pattern that we have here so you get this nice chain syntax where we can keep calling dot do something else and then we build it now on line 22 we have an upgrader so this part didn't do the upgrade it just made an upgrader for us it built an upgrader now we have it we're going to go perform the upgrade and you'll notice that I'm doing that before I run the web API okay so this is worth calling out because this came up on YouTube and it came up in some other questions previously and someone was saying hey look like this can't work if you're building microservices totally right um I want to pause to talk about this for a moment let me go back to full camera because the hands are coming out but when we're building this kind of stuff uh pre-recorded sorry it's not pre-recorded uh this person already left the chat uh anyway whatever I feel I just feel bad for the people on Instagram because they have like this much screen literally they can see this and when it's on the code it's like a semicolon but what was I saying now I lost my train of thought schemas databases I don't know it's the it's the Instagram people distracted me um oh microservices thank you it's coming back so people were saying okay well you're going to do this migration what happens when you're running multiple instances of your web service and it's a great point because it's not a good idea to basically on Startup do a database migration if you have microservices spaghetti developer is there really a developer crowd on Instagram uh yes but it's weird and I don't I don't want to come across like negative in any way so it's not my intention but uh when you think about social media and I'm just saying this because I post across every social media platform uh that you can think of and if you notice that I'm not on one let me know I don't think I Poe to Snapchat it's probably the only one I don't post to but you'll notice that across different social media platforms you have different Aesthetics that work well different types of posts right and you get different audiences associated with that so on Instagram what works really well and I don't do it but what works really well are uh very aesthetic like desk setups you'll have people that post a video just like panning across their LED lit up desk um and I'm not knocking these people I got I got LEDs behind my monitor but you'll have that and then they'll say like look at the caption and then you read the caption and it's like make sure you use semicolons and you're like oh thank you for the tip and then you move on um so yeah there are uh Instagram creators I post all my stuff to Instagram uh the other thing that does really well on Instagram are like uh me themes and skits related to like developer life so I find it's a lot more aesthetic and Casual if you're just looking for kind of like relatable things uh from being a developer but they're out there uh I post there every day but yeah not a ton of Engagement compared to other platforms but I feel like there's maybe more web developers on Instagram from my experience okay so microservices when you're doing these migrations if you have multiple instances running like where is the database do you have you know multiple instances of these microservices in one database and now every one of these coming online is trying to do a migration like is this kind of weird so you'll want to think about how your software is set up how is your system configured when should migrations be happening who owns that migration I'm doing this on Startup it's going to be like a monolithic application uh for pro and if you're building just like hobby projects I can almost guarantee for like 99% of what you do what we had on the screen will be totally fine for migration so let me go back to it but I just wanted to call that out because it's an important point and I don't want to um gloss over it or ignore it so hopefully that makes sense okay um let's see this part is going to build the upgrader so let me let me go run this okay this is going to make a database called cool. DB and when we're done we should see that because it's in the running directory you're going to see that it gets added into our solution because it'll make a new file so let's see run this you can already see cool. DB right it's already there that's very that's a very white screen what's going on why did that happen oh I'm not returning anything so it's probably just like that that's okay anyway let's have a quick look what we got going on here I got to move this over so you guys can see it beginning database upgrade so this part is the asp.net core pieces starting up the part above it is going to be from DB up so it's beginning database upgrade checking for a journal table says it doesn't exist and then it says no new scripts need to be executed completing and that's because we made no migration scripts so it doesn't seem that exciting because we haven't configured anything yet but if we look we have cool. DB here now okay so it did make a database file it didn't do anything with it it just connected to it it's an empty database so not that exciting but hey it made it okay so we have a couple options we can go continue and do the migrations first or we can do this uh configuration part uh I'm going to jump over I might not make the full two hours of this stream but I'm going to try so we're going to talk about the configuration part and you might say okay well what the heck is a configuration configuration is able to pull information from some different sources so that could be environment Vari Ables it could be other things uh you can technically map your command line arguments to a config I believe um and our app settings Json is also there so there's different called like providers for configuration but what we're going to do is we're going to ask the um this is so crazy you see what co-pilot did it's like it's listening to what I said um configuration get section but this isn't going to work perfectly yet so you'll see that it's going to get a section called connection strings and I think if I trust co-pilot see how cool that is I don't know if folks are using co-pilot but this saves me like an obscene amount of time um but that's maybe just me but I'm going to make this a record can I just convert to positional record there we go little refactoring tip um I like if you're not familiar records are just what's a good way to put this records are really nice way for what's called a data transfer object so I just had a class here let me Undo It class here with a property that you can get and set this class doesn't do anything it just has a string on it for us because that's going to map back over to here some information we're going to put in here so it doesn't do any logic it just has has some information on it so we are transferring data with a data transfer object and a record I'm not going to go into all the details about what's different about a record they're just very convenient for data transfer objects they have really nice two string ability so we can print this object out and it will print all of the properties it's very nice convenient comparisons work the way that you would expect but that's a language detail so anyway we have connection strings uh and we're going to pull this off but how do we get that in here let's see if co-pilot figures it out very cool um but it says default connection that's not what we have here we have connection string so maybe let's go do this I'm just going to rename it another I press f2 on my keyboard for a refactor shortcut uh for default connection there we go that's weird it didn't rename that one and I should call this something different this should be called connection there we go so just to explain what's going on we are asking our web application Builder actually can I just do this off the app I can good I'm asking the app for its configuration then I'm going to ask for a section so spaghetti developer yes I can just get the whole config but you can break your stuff into sections and I didn't finish this part sorry so we can basically have hierarchical uh data if we choose so that's why like logging is structured this way I could literally just have a connection string and point it right like this without nesting it so we could have it at this top level I've been trying to get in the habit more of uh having hierarchy in mine why organization I don't know but uh I have some app settings that are just very big key value Pairs and I'm starting to get to the point where I'm like ah I should have organized these a little bit better so we're going to get the whole configuration that we're going to ask specifically for the section called connection strings then when we say get this is kind of like an OM an object relational mapper it's going to map that section to this that means we should be able to ask for default connection and it's going to give us this as the default connection so let's go see how badly I messed up this code I'll press play I just put a break point in here by the way and this part didn't crash so that's good news if I hover over it it's I just realized it's impossible for you to see sorry it's probably so small but that we do have a reference to a uh an object here and I can see that it says data source equals cool. DB so that means if we go run this now let me go press F5 there are no exceptions thrown we get the same kind of output here okay and I'm going to run this again just to prove it very quickly I'm going to delete cool. DB okay there is no there is no database there it's gone and when I just run this now it will go make it see I what added cool. DB in the bottom sorry I said bottom cuz this window popped up in the bottom distracted me but yeah cool. DBS in the solution Explorer so that is getting our connection string out of the app settings so again if you have any questions about how that works please feel free to ask happy to answer but there's all sorts of different ways you could if you wanted pass it in on the uh the args right you could pass it in on the startup pardon me losing my voice I was at work all day I was at the office uh I don't go into the office every day try to go on go in on Mondays and Wednesdays but I was blabbing a lot today I was doing a lot of talking and I feel it okay so we have we have a database now it's got nothing in it we're reading our connection string in from the configuration very cool what's next well we need a migration so I'm going to cheat again with a little script I have and the way we're going to do this is I'm going to add a new folder into our project called scripts there we go and then I'm going to add a new file uh can I just do add new item I couldn't read my goodness um I'm just going to add a new text file are these not sorted x file cool and I'm just going to call it script. o1 dsql okay and there's a schema um I'm just going to make a weather forecast table and by the way sorry if you're very familiar with SQL and you like I don't care I know how this works I'm just trying trying to overe explain some things because the whole goal of this is for folks that aren't familiar with these pieces I would much rather take some time and explain some of the very Basics uh Tik Tok thinks that I don't know why between Tik Tok it thinks I'm not active on the stream which is completely ridiculous and then Instagram sometimes goes oh we determine that your account is being automated and I'm like dude I'm literally on a live stream but anyway this is going to be creating a table in our sqlite database called weather forecast it is going to have a primary key that's an ID just going to be an integer I believe in C and equivalence this basically comes out to a 64bit integer it's it's a big one it's a long it's not just a 32bit integer Now sqlite is really weird compared to some other dat bases sqlite does not have a date time format which is probably a shock to many people but you can basically do date times as strings which we're going to do today just to to make it nice and readable but you can also store like tick or an offset from a Unix Epoch as a number you can do all that sort of stuff but they don't have a date time it's very odd and I know this because I spent 8 years building digital Frenzy software and we stored stuff in SQL light and when you're trying to show millions of Records to forensic examiners and you're like we don't have a first class date time and you can imagine how important dates and times are to digital forensic and investigators the answer is very important so we had to make sure we get this stuff right but yeah kind of weird they don't have it as a first class thing and then finally we're going to have a temperature column just as a floating point nothing nothing crazy just simple but the way this works is we've made a SQL script right this is literally SQL it's inside here but I have to do one thing and just to show you one sec let me get rid of that if you recall I said this cut off the screen now but I said that we're going to do this part here um with scripts embedded in assembly this is the key part here embedded in assembly this is in our project but it's not embedded there's a special thing that's going on here basically right now our project and so Ms build in this case doesn't know what to do with this thing it's like great thank you for the SQL file I guess I don't know what you want me to do with it so if we go to properties on this file there's a build action and again oh man this is behind give me one sec this is the thing with streaming one sec there you go U now you can see what's going on we go to properties and you can see this build action here I can say embedded resource now when we build our project it will take this script and make it something that is truly embedded in the dll which means at runtime we can ask the assembly we're running from give me that script you can basically look it up by name and that's what they're doing behind the scenes here so hopefully that part makes sense but at that point we're done well done this part at least so what we can do is when we go to run this now what's going to happen is DB up will find a script to run and that means that it can go do a migration it's going to make that table for us as long as it didn't mess it up don't mess it up Nick there we go cool didn't mess it up okay so if we look at this journal table does not exist and then you can see um that it's doing schema versions table it's going to make that oh sorry I missed this part here executing database server script so it's literally executing this and then it's going to mark that it's done that migration for us uh how does it know the order uh I think by default it's just alphabetical yeah so the numbering uh but I think that you can tell DB up like the the order explicitly if you wanted to I believe you can um but that's going to be the next step we're going to add one more script just to prove it okay so I just wanted to show you that this happened we're going to add one more script and I'm cheating again so let me do this actually let me copy paste this one it's funny to put copy in the middle of the name because that's where the dot is there we go and I'm just going to change this script to put some data in there okay so this is going to add in a bunch of stuff I just realized this is from my YouTube video so it's like a week old because I think I started numbering like around 12 or 13 but anyway um what we're going to do is this is going to run another migration and it's just going to add that data into the database right very simple the first example of the migration was creating a table this one's adding data you can do migrations for all sorts of stuff so you can change the format of data um you might need depending on your system and having intermediate steps you might need to basically break a say you have a current state and an end state that you want your database to be in you might have to deliver changes over time because your service is running live so you might do a migration get all of your data in one format and then you do a code change and then you do another migration so you have to think about these things in terms of what code is running and what your data looks like just as a heads up so we have this but we got to double check properties you can't see it because it's behind here but it is also marked as an embedded resource which is good because it needs to be an embedded resource for this part to happen right here let's go run it of course it's hiding somewhere okay this one looks a little different but similar right executing database server script what number is that O2 right so it ran the other script this time for the migration now to prove a point I'm not changing any scripts there's no Fancy video editing just going to press play again what happens no new scripts to be executed it's done migrating and the reason it's able to do that is because uh I could I don't I'm not going to pull up sqlite expert uh just because it's going to be blinding because it's I don't have a dark mode for it but it has a table for schema versions and it tracks when it does the migrations and then yeah so spaghetti developer and it creates a DB table able to log the migrations that already ran exactly that's exactly what's happening it knows so um you can keep adding new ones and it will go continue on if you ever had um if you had multiple databases and some of the migrations weren't run you could go run DB up against that with all of the migration scripts and it will go figure out how to migrate that data because it will do them sequentially so it's pretty cool but at this point we're getting close we have a web API current does literally nothing so not very exciting but we also stood up a sqlite database so we're not deploying anything anywhere it's not doing anything crazy but we have a sqlite database with some data in it and it's right here it's called cool. DB so it's real it's a thing what are we going to do with it now and this is where Dapper is going to come into play so we're going to basically allow our weather forecast route to return data from our data base and I'm very curious I don't know but I'm very curious if uh co-pilot will literally just write the code for us I'm always like I'm always just like baffled by sometimes it gets things like spoton other times I'm like dude what are you doing but like I'm just curious right I'm gonna press enter and see oh no it's it's cheating it's trying to put back the code that was here already I guess it's been trained on too many uh sample weather app uh programs okay so the way that Dapper works is that it's going to Leverage The SQL connection that we have to go make on our own anyway okay so sometimes when you're dealing with Entity framework core for example you're just dealing with what's called a DB context and that is how you're accessing your information it's always through this DB context uh when we're dealing with Dapper and a lot of other things that aren't just using Entity framework core we're we have to think about setting up a SQL Connection in this case it's a SQL light connection so um the way that this would work is if I say using SQL come on co-pilot no okay I needed VAR that's why let's go boom let's go put that in there so you can see that it's doing the same thing that we saw below and I'm just going to pull this out and put it here split it up make it a little bit more readable right um put it onto a new line just so I don't accidentally trim it off with the chat but it's the same thing that we saw down here actually is it no this this is slightly different I don't know if this one works the same so can let me explain this for just a moment connection string is like a built-in section technically so we might not have had to do this because it might already be a default one I'm super curious anyway this I'm going to put a break point here because this might blow up because of this but if it doesn't work we just need this same type of thing just as a heads up so lines 17 and 18 should be basically like this but the point I was trying to get across is that get connection string I think is like a a built-in way to do uh this mapping I don't know why they made that one very unique there's probably a good reason but just heads up we'll come back to that but now we have a SQL connection no not quite co-pilot I'm I'm just so hopeful that co-pilot is going to nail it but we have to go open this connection first okay so and we can do it async actually so let me convert this might as well go async so if you want to have an async minimal API very easy you put async out the front now it's asynchronous uh you also have access to a cancellation token so for folks that are building these things I would highly recommend that if you are making minimal apis asynchronous with the async keyword pass in this cancellation token if for some reason you're building software and you are able to basically abort the HTTP requests you're making you can cancel everything Downstream on your server so instead of just processing stuff for no reason because the caller already said hey I don't care you can just stop so it's nice to have so I got to change this part to await it now and we'll pass in the can ation token so this basically makes a connection to cool. DB now I'm hoping Dapper can figure it out and it does so let me explain um it's close so query okay so let me pause for a second because I want to show you this part if we go to co-pilot go away query async is an extension method this comes from dapper and what you're able to do if I put it back in is querying takes a uh a type in this case it's taking a weather forecast type and then what it's trying to do is it's saying I'm going to go run this SQL query that you have here and I will do the object relational mapping so whatever columns come back I am going to map that to this object for you now it Tred to guess it what what to do we don't have a weather forecast type so like it didn't do it right but let's see if I do it down here so far so good co-pilot come on close um spaghetti you have a question you're thinking about something what's on your mind I got to go take this code back make sure I get the names right so so you you do need Dapper to do the object mapping automatically just to kind of explain it because the reason I'm saying this is because for years I wasn't using Dapper and then I realized Dapper just does the work that I was doing by hand automatically so with sqlite connection you need to create a command so let me write it out the long way for us okay so we create a command you can set the text on the command to go execute and then you use a reader and then we have to walk through and read each item back so um I'm just letting co-pilot figure it out but this is how you do it co-pilot's really good and you can go look things up by either the uh the index of the column or you can do a mapping of the column to the name of it but this is how you do it with without something like Dapper o or an omm so the SQL connection does not do this by default at least not with sqlite there is no method to do that as I scroll through so that's I'll leave it here just so it's on the screen uh but we're going to go back to the Dapper part uh on SQL connection execute query uh there isn't an execute just query oh did I put Dapper back one sec without Dapper it wants to add it back in so there's only query from dapper and just a I I'm and pass your type in the parameter thing no that's a it's a Dapper thing is what I'm trying to say so let me go back here sorry I'm jumping around I just want to kind of illustrate this the SQL connection does not have it there is no query there is an no execute you need to go make a command right so if we go do this this is the only way to do it and then even if we have a command and we say uh query well this non-query or there's execute I think right execute reader but yeah anyway that's I'm just saying this because I this is how I did it for years and then we'll see with Dapper you can get rid of all this code because it just does it for you which is very nice um yeah so there we go put Dapper back here but yeah we'll see just a moment because it's like you have this code where you go set up a command and I'll leave it right there or you use Dapper and it will just do it for you which is cool so weather forecast I had down here I didn't finish it though we have an ID um the problem with the date time sorry folks um it's going to look really bad string uh I know it's not a string and actually this isn't an integer I said it earlier sqlite is a long and then we have a float here and it might be a double in sqlite I actually don't know if it will force a double or a float I can't recall I'm just going to play it safe temperature and C so this you can tell Dapper to do a different mapping so you can set up more configured mappings but if you make it match the columns then you don't have to do anything fancy which is very nice uh but now let's talk about what else we got going on here so you'll see that this is uh underlined with the cancellation token it's because it's missing this new command definition if I could type it come on keyboard so with a new command definition it's very similar it's just that it takes in the cancellation token without it it doesn't same with transactions but I want to talk about this query we're running because some people might look at this and say oh no no there's something not okay with this query there's a little character in here that we are not happy with um and there's different opinions about this kind of stuff but let's talk about it so right now again I want to talk about this for folks that aren't familiar with SQL is when you do a select star from weather forecast this is saying give me every single column and from this table and technically if there are multiple rows you'll notice that the return type here is a task of I enumerable so it will give us back if there are multiple weather forecast entries so it will give us multiple rows if there are multiple rows in the database and it will give us every column for them just as a heads up so is that what we want maybe but if you're familiar with using Dapper and some of the omm tools like the thing with Dapper is that it's going to do that mapping for you to the column names but I find like I find if I'm not being explicit then I feel like I'm at more risk and I'm just going to walk you through what I mean by that cuz it it's might be personal preference I'm not like totally grounded in this um personally but I feel like when I do this just to give you an example I know that my type has an ID date time UTC and temperature C like if I literally jump to it with F12 ID date time UTC temperature C cool if you were to put a star here and then you changed your schema this part might blow up and that's because if it tried to pull back more columns and it can't use them it's like I don't know how to map these things for you now in your application in your development process you might literally say that's what I want Nick I want to know because I'm going to go run my regression Suite of test I want to know in the code where this stuff breaks great like that that will tell you I'm not saying that's wrong but I think that I found that I would much rather my experience my journey I would rather make changes to the schema and and feel more confident that my existing code if I'm just adding columns for example my existing code should not break should not and if I'm explicit in defining the columns it will not if I'm just adding columns that's my reason um hopefully when I explain things like I I try to not be biased about them there's a little bit of bias for everyone but I try to give you like both sides I guess so hopefully that makes sense but at this point when we run this code it's going to take our SQL connection it's going to use Dapper to go run this query and it's going to give us back an i inumerable of weather forecast so what we can do from there this stuff's always kind of gross when you have multiple lines going on but something like two array come on no why doesn't that work do I need to put one of these guys on there okay so the reason I had to add an extra parenthesis around here is because we are a waiting an I inumerable so we have to get that whole result then we can say to array on the whole thing otherwise it was like a weird order of operations but anyway at this point if I hover my cursor over weather forecast or maybe just to for folks that don't like VAR there you go we have an array of weather forecasts now we're almost done because we have this uh route that we can hit it's going to query the database we're getting some where uh we talked about this earlier we could be passing in like uh date range parameters maybe we'll have a chance to do that we can see if we have some time um but we do the query and then we don't do anything with it so now co-pilot saying do this I want to talk about this for a moment because this will 100% work and just to prove it should I shouldn't say 100% work the the idea behind this should 100% work it never works the first time I probably missed something but uh I want to show you this working and then I want to talk about some changes we could consider making come on work first time oh my breakpoint oh it does work so this was a little Easter egg from earlier remember I said get connection string off the configuration this is is a builtin thing so if you are just using connection strings like this you can use the built-in configuration get connection string just a heads up so glad I reminded myself with that breakpoint come back to that I'm going to press F5 now and we're going to cross our fingers oh no look at that no such table weather forecast you guys you weren't spell checking for me why is there no table weather forecast where did we go wrong what's up with that is it a different database did I get the wrong now I'm I'm actually [Laughter] confused whether it's spelled the same the only thing I can think is that we have the wrong sequel connection and this part is tripping me up so let me do this because I don't trust it it might be totally fine uh oh no I know what's wrong uh okay ready for this one this is a pretty embarrassing mistake see this connection string that we got on line 20 I'm going to wait because I want to see the answer in the chat because I know I know you guys are there think there's a lot of you that are on on uh on X I think yeah we got twitch there's no one on Facebook but I think X is the overwhelming majority there's a bug in this code right here it's only four lines of code I need you to find where the bug is and I want to see the answer in the chat because I know you guys can find it okay so from line 20 to line 23 there is a bug and the hint was that we just ran our web API and it said I can't find that table doesn't exist there we go spaghetti developer got it this right here it's got to go there I actually don't know if open async takes one it doesn't but yeah when you make the connection object you give it the connection string then open is just a there's only a parameter for the the cancellation token but exact same idea so spaghetti developer got it thank you for that answer I was going to probably lose my mind if it wasn't something that simple but thank goodness um cool let's run it now drum roll boom there we go it's beautiful right so these are the records right from the database they're great they're lovely so it works but I wanted to talk about this part for a moment so um this part here yes we can just go return these things that's totally fine we just saw that it worked but I'm going to run it again and get you to look at the data that's here and I want you to just like kind of uh we're going to talk through this but I just want you to think about you're building an API right now what I'm doing is I'm passing back the data that's in our database exactly as it's found exactly the same way now you might be thinking okay I have a weather forecast route that I'm hitting we get per record we get three pieces of data back like we have an ID a date time and a temperature you might in your API you might say well I never actually need to know what the ID is like why would I ever and there's going to be situations where you do and do not want this I'm just giving you an example you might say why do we have this there's no way we need an ID for this I don't want to send it that's totally fine because you don't have to so what we can do instead is we can do a little bit of a conversion before we pass this back so you can just do some link here if you wanted to okay and I'm going to return I'm going to put new up here and then I'm going to say date time UTC and if this was a date time already it's not it's already already a string from sqlite but let's say we were using a real database I'm just kidding sqlite it's a real database but a database that supports dates and times what would happen if you were at this point sorry I'm just realizing again people on Instagram I'm so sorry YouTube looks better um a real realer database that's right it's a little bit more real um if you have date times as a first class citizen in your database um you might say okay like how do want to format that date time going back to the the caller of the API like you might want to do something like uh so you'd be like two thanks co-pilot just that's what I want to say so you could two string it and put the format you want maybe you just want it like this whatever but you could take this opportunity to do that formatting you're going to notice that I'm dropping off the ID okay so that's what I'm doing in this case uh I think I want to try this I haven't done this yet I always put two array after this because I don't know if it will just call two string on a numerable but let's try it it's either going to say something funny or it's going to do what we want it does what we want that's good so it's the same set of data but it's not included the ID so you have these opportunities to sort of translate information at different points in time when you're working in your backend server you might want to make sure that you have that ID as you're passing this data around and by the time you return it to your caller you might want to transform it a different way so just wanted to show you that you have that opportunity why not remove the ID from the select statement so great question I think I was just trying to explain that in the last part so if um it's too much work it's if you in this case in this simple example you 100% could go delete this and then go delete this ID functionally would be identical okay so that would absolutely work in this case totally fine the point that I was briefly mentioning uh couple seconds back maybe a minute back now is that if you need that full data resolution because you're doing other things with these records on your server in this case we're literally not doing anything we're just fetching them and returning them um if you needed to say you have like a a user object and you pull back the whole user then you have to go fetch some other information based on that user or call another API like your business logic might need the full data resolution of whatever you're pulling back then by the time you're ready to provide that to the caller you want to trim it down and just give them back what they need so I'm just trying to illustrate that you may want to massage your data but you 100% right that in this particular case we could just take the ID off and in fact let's just go do that to prove it okay so let's go get rid of this and actually I don't even think we need to say results. okay I think we can just say result and we can go return this and it works right no ID so I just wanted to mention yes that's 100% okay that's acceptable my point was just trying to to illustrate that you can transform Data before you return it if you need to great question though okay I think we have we got a little bit of time here we can try doing some query parameters just to try it out and what's a good way to do this um I shouldn't say what's a good way what's a way that this works so we could do from query and I want to do a let's just do one uh time boundary so we will do minimum date time UTC okay and I need to put a string in front of this because this is going to be on our URL uh local date conversion um I'm sorry I'm not sure what the question is if you just want to re askk it um just so I can I can answer you properly uh but yeah we're going to take one time you could do like time boundaries and you could give a window for the times you want a query I'm just going to do a minimum um oh we don't so interesting this is a very interesting question we don't want to return UTC right maybe um this is it's going to be dependent on your application so most of the time when I'm building software and like what I will try to do is keep everything uh in like a standard unit uh in UTC for times right like basically everything is always standardized to something give you an example when I was building a nutrition platform I tried to make sure everything was in uh metric so if you were dealing in volume it was laders if you were dealing in Mass it was grams um and dates and times were in UTC formatted everything was like standard format right up until you want to show a user so you might argue well Nick my front end that's going to be showing this is just taking it right off the AP I and plopping it onto the user interface like you are totally welcome to go do the translation on the server side but you need to know the caller's uh local which is possible you could have a parameter in the query that says the local the server could be responsible for doing that but the reason that I like doing it like full uh consistency standardized is just that if I push that all the way to right before the users shown it then it makes any logic we have to write on that more consistent if that makes sense uh sometimes when you're transforming data like doing a time zone conversion is not the case but uh sometimes when you're transforming data you can lose resolution to give you an example I just want to uh use the date time one maybe we're like hey we it's it's a forecast we only care about the date we don't care about the time so if I just passed back the date if in the future you changed your mind and said ah we want the time now too like you could have already had that we could have been passing you that and it's just a presentation detail about let's trim off the time part so just things you can consider I don't think there's a a right or a wrong way but that's a pattern that I've tried to adopt is full data resol solution right up until you show the user that's worked well for me um the background there is uh a lot of time in digital forensics so full data resolution of the data we recover literally up until the point where the users like I want to see it in different ways and again from eight years of doing that that's what's served me very well great question thank you for asking that so I'm going to be passing in a minimum date time which means we have to go change our query a little bit so co-pilot let's see can you get it I think it got it and now we have to do something a little bit different because we need to give Dapper a parameter and it's pretty good so I want to write this more explicitly but this looks kind of weird we're making a new object and it's taking in a minimum date time UTC and setting the value to the same thing so you'll notice if I write it out this way how this is grayed out in Visual Studio it's like Hey look it's kind of redundant like if you're naming it the same thing the variable's called we can just do that for you automatically personally this is one of the naming opportunities I like to be very explicit and the reason that's the case I just want to show you can I do this properly yeah okay let me change this variable name to okay so this worked right it changed haha only in the spot that it needed to this is going to be a variable name in SQL that didn't change so that worked okay now let me do this I want to change the parameter name to garbage we've now broken Dapper and that's because this is not obviously this is not the variable name that's used here so this is just again from my personal experience that being more explicit with the naming means that I can preserve these two things and still have the flexibility to go rename this very easily it's like decoupling the names this is what I want callers to know and this is what I want to use internally you know tons of options like you could if you wanted to maybe you're like I want to use this type of casing in all of my SQL I'm just making this up okay um just to kind of prove a point if you want to do this like this is just an opportunity to have a different nameing convention but you can do whatever you want so now we are taking a query parameter from up here and we're going to pass that into our Dapper query and it's weird because it's dealing with strings but the really cool thing about the datetime naming convention that we have is that it's going to make it work okay and I know that because I dealt with strings as dates and times for way too long so if you format them as like year year year year month month DayDay hours minutes seconds or and then milliseconds and stuff after you can get it to work I would probably recommend if you're using equal light like numbers are a pretty good way to do this so you don't have to do some silliness but uh other databases are just kind of better at the dates and times so let's go run this it's going to fail immediately just a heads up as predicted that's okay why does it fail this part right we did not give it the query parameter so I said that it's called minimum um date time UTC equals and now what is one of the dates I have to jump back to here because I can't recall let's start from here okay so that means we should not see anything 11 13 let me pull this back up ready Moment of Truth ooh it works starts at 14 there is no 11 12 13 you can have a look through if you don't believe me but this was a way that we could take a query parameter and yeah it's pretty ugly like don't get me wrong I'm not saying this is an awesome thing uh kind of just like a side effect of SQL light we could have put up here we could have made this like ticks or offset from the Unix e talk as a number we could have converted that to a date time converted that date time to a string given it to sqlite we could we could do that and just keeping it ugly and functional for now but we are an hour and 45 minutes in I want to take this opportunity to say I think we have a working web API with the database with migrations I think we've done it I think that's what I wanted to cover today so um I wanted to say thanks for tuning in thanks for for jumping into the chat uh spaghetti developer huge thanks to you for for sticking through the whole thing asking questions having comments uh really appreciate it uh for folks that are still on as I finish rambling here if you still have outstanding questions about either what we built or you want to ask anything else uh I have another like 15 minutes before I got to go fall asleep so I'm happy to to stay on a few more minutes and ask things there is a delay in what I'm saying versus the chat so uh I'm going to keep blabbing just to give you an opportunity to write down any questions and stuff if you have them and that way it's not like me going oh looks like no one has questions like r as you're pressing enter uh so yeah I just want to give you a little bit more of an opportunity um trying to think if there's anything else I don't have any like fun announcements I don't think um I do I I guess I should should do my pitch I got got courses and stuff so I'm terrible I'm not a good salesperson but let me pull this up and we'll talk about some courses just in case folks are interested uh I have Dome train courses so if you thought that this type of approach for how I teach is helpful it made sense to you if you want I do have courses on C the first one here getting started is uh very much for beginners like you don't even have to have any programming background the Deep dive Builds on that and if you go to the Dome Train website so just doet tr.com uh the bundles do have a discounted version of both of these which is great I have a refactoring course available as well it says it's for c um because that's the language I'm using but uh Concepts apply to any language really and I just wanted to mention that I have two more Dome train chuses in the works uh one of them uh the I don't I still haven't asked Nick chaps if I'm like allowed to talk about what they are or like if he likes keeping them under wraps so I just air on the side of caution uh but for that next course it's something that I've made a bunch of YouTube videos about I enjoy talking about it so again if you watch my stuff on YouTube and you like it there might be a course coming up that's interesting for you uh and then I have another train course like I said which I'm personally uh the most excited for uh I think it's going to be very cool it's very much aligned with things I like to talk about as well so these are available for now and then the two others should be launching over the next couple of months I would say so those are still being put together the one is almost done the other ones still got a little bit more work to do but um yeah that's my that's my pitch and and if you are just jumping on uh this is my newsletter Dev leader weekly so I do put out a newsletter issue once every week generally it's is focused on General software engineering so for context I'm a principal software engineering manager at Microsoft so I do try to share my experiences as an engineering manager before Microsoft I spent eight years building digital forensic software and I was a software engineering manager for those eight years but also an individual contributor at the same time so I like to say that my unique experience is that I got to do both jobs at the same time for eight years and how well I worked a lot I just worked basically twice as long so uh I like to be able to talk about those things because hopefully it comes across when I'm talking about topics I want to give you different sides of the story and especially from an engine ing perspective I like talking about my management perspective as well as from an individual contributor so my newsletter is mostly about things like that and uh unfortunately I have not been blogging as much uh in fact it's been a few months since I've been blogging but um previously I was writing six blog articles a week so that was a lot but uh I am trying to work on my side project called brand ghost which helps me post all of my social media content so um I've had to stop writing blogs so that I have more time to code cuz blog articles take a ton of time and something had to give so blog articles are out um YouTube videos are still there newsletters still there uh and then of course probably wherever you found me you can keep finding me but if uh depending where you are uh I post mostly on LinkedIn I do post on on Twitter and Linkedin every day and YouTube videos three fulllength tutorials a week one software engineering interview and I delete two live streams but this week it's only going to be the one so that is my Spiel um but I do post stuff on every single social media platform so uh if you're following me on one and you like it there great thank you so much I appreciate it if you don't like it there you like what I'm talking about go find me on another platform but folks thank you so much I do appreciate your time I hope this was helpful and uh of course if you want to see different stuff covered in streams just uh shoot me a message on on social media if you're watching this uh recorded on YouTube uh you can just leave a comment on it and say like hey could you do a stream on whatever I'm happy to cover it that's why I'm doing this so again thank you and uh I guess and we'll see you ah you know what I might not be available next uh next Monday because we have uh the in-laws visiting so I'll see my my wife might say no and and realistically I probably shouldn't so it might be another two weeks um that's okay we we'll see how it goes but we'll see