WITHOUT Entity Framework Core?! - Using DbUp For Database Migrations

there are a million different types of databases that we can go build our applications with and in C and.net one of the most popular tools we can use as Entity framework core but what if I told you there were other things we might consider hi my name is Nick centino and I'm a principal software engineering manager at Microsoft in this video I'm going to look at one tool that you might not have heard of and no it's not Entity framework core we're going to be looking at something called DB up and this is going to be a very introductory video for looking at DB up what it's capable of doing and how you can start using it in your own applications if that sounds interesting remember to subscribe to the channel and check out that pin comment for my courses on dome train with that said let's go to visual studio check out this basic console application and see what we can do okay to start things off I have a very simple program here which I'm going to walk through but I want to start by going over to our project file in this project file I want to share which Nate packages we're going to be using so you might see on the screen right now I do have SQL light included here so yes this example is going to be using SQL light so we have DB up core and DB up specifically for SQL light this is going to be the core package and then of course DB up does have other connectors that you can use so if you want postgress MySQL SQL Server whatever it happens to be you can get it to work with DB up but I am going to be using sqlite just to keep things super simple here that means this next line here you see Microsoft data sqlite I'm including this just so that I have the unmanaged sqlite library is also included so these three things together give us DB up with the ability to have a local sqlite database cool with that stuff out of the way let's look at some code because that's probably a little bit more interesting the part that we're going to look at in particular is just this much code so it's not a ton of code there's actually more other code on the screen just to have this example set up so we can go and investigate what's looking on not many lines but here's what we're able to do with it you can see on line eight I have this deploy changes. 2 and then beyond that we can see that we're going to be using a SQL light database so it's using this connection string right here on line 7 just going to be a file that's in my running directory so we can use that connection string right here and you might have guessed it but using that one nougat package where we have SQL light allows us to get this part to work so if you were using some of the other Nate packages to do MySQL or postgress then you would have a different method here to go set things up this next part we're going to come back to so still on this video we'll see this and log to console as you might guess by the name it's going to log the information to the conso when we go to run this so we're going to build an upgrader by running these things together so this is a nice like fluent Builder syntax to work with I really like this kind of thing makes it nice and readable and there's lots of different other options if you were to kind of Step through all of the stuff inside before we move on this is just a quick reminder that I do have a course on c refactoring a available on dome train refactoring is one of the most critical skills that you can learn as a software engineer and this helps you continue to build upon applications that already exist making sure that they can scale and have extensibility I walk you through a bunch of various techniques and give you some examples that we walk through together to see how we can apply these techniques to refactor the code check out the pin comment and the links in the description to get this course now back to the video now we see on line 14 a single line to go perform the upgrade so we're going to come back to what upgrading actually means but I want to basically jump through the rest of this code super quick at the bottom just to talk about what's going on so we are going to see if the upgrade was successful right here from line 15 and 16 just to print some info to the console and the rest of this is just some sqlite specific stuff so we can go see the schema of the database that we have to work with so this part that I have highlighted is not specific at all to DB up it's just so that you can go test things out navigate and I am going to show another tool called SQL light expert just something I like to use to have a visualization of the database so you're probably eager to see this thing run and I'm going to go run it for you right now so we can see what it does okay so we have a little bit of stuff printed the console here we can see beginning database upgrade it's checking the journal table Journal table doesn't exist that's because there is no database and then you can see no new scripts need to be executed completing success this basically suggests to us that it tried to go do a database upgrade but as you might have guessed there is nothing to upgrade we don't have any migrations or anything like that we don't even have a database there's nothing and then you can see that it succeeded so it finished because there was nothing really to do and when we go to ask for the database schema as you can see there's nothing here so this code I'll move this over a little bit this code that was doing the reading from SQL light said hey there's nothing so the interesting part about this output that we can see is yeah there is no schema but what might not be obvious from here is that we actually did create a database it might not be obvious because I went to go run this part down here where it was actually connecting out to the sealite database and technically if it wasn't there this code would have went and created it but the part above where it's trying to do the upgrade will create an empty database for us so now we have an empty database that's not that exciting you might be saying Nick why the heck do we want this and the answer is that you can leverage this to have database migrations so this is one of the things that people love an Entity framework core I'm not about to sit here and suggest that just by using DB up you get all of the power in the world from Entity framework core but if you're someone like me I just don't really like using Entity framework core it's a me problem I'm not saying it's bad I think nty framework core is awesome it's just not the way that I like to code I like having something like this to go do my migrations then I can use something else I like using Dapper on top of this which will be a future video and and from there I can go write my own sequel this type of thing works for me but it's not that exciting right this moment because we don't have any migrations which is the next part we're going to talk through so you can see on line 10 it says with scripts embedded in assembly so if you were paying close attention to what's on my screen I do have a scripts folder inside of my solution Explorer so I'm going to go over and press script. 1sq and you can see that I made this little table here called blog post so this is a SQL file this is SQL syntax sqlite syntax in particular I'm going to create a table called blog post it's going to have these three columns and we can basically go have DB up create a schema for us so it will go run this code during the migration thanks to jumping back to the code this line 10 right here so it's going to look in the executing assembly for this script and then go run the migration well you might say well Nick it's already in included here Why didn't it run and that's because I have this properties panel pulled up in the top right here cuz I wanted to be able to talk about this you can see the build action with this file selected does say none what you're going to want to do is change it to an embedded resource now when we go to run this we're going to have some slightly different behavior and that's because this script is now truly embedded as a resource in the assembly before that it was in our project but we weren't telling visual Studio or Ms build what to go do with this thing it's like thank you very much for the file I don't have a purpose for it see you later now we're saying it's an embedded resource it will get included in the assembly so when we go to run line 10 in particular like it will configure things to go leverage that script let's go press play and see what happens and this is a little bit more exciting right it's still just some console output but we can see executing database server script and then it pulls out the 0000 1 SQL file we can see checking whether the journal table exists creating the schema versions table so we're going to see that in just a moment and then it says it was created the upgrade was successful and we see a success this line here that I have highlighted is the line that I printed the console specifically and that's because we can check the result of the upgrader if we look at the database schema we'll see that blog post is a table there schema versions and sqlite sequence these are three tables that got created as part of the upgrade at this point we were able to go run a migration and that means that this instance of the sqlite database has been configured and I want to show you cuz I'm not using any fancy tricks here I can't get my Editor to promise you that he didn't manipulate this video in some way but I'm just going to go run it again and hopefully you believe me but hey look there it is we just ran it and the same thing happened except you can see that it didn't go do the upgrade and that's because there were no migrations that were needed we all already upgraded it I didn't go change any other I didn't remove the script from being an embedded resource and we still have that schema here what's really cool about this is that it keeps track of the migrations for you so it knows how your database has been upgraded based on those migrations and then it decides what it needs to run in this case nothing we've already done it just to show you the final output what I want to do now is open up sqlite expert and just have a visual of what's in the database what I've done now as I've opened up sqlite expert like I said this is one of the tools that I really like to use I'm going to drag and drop Dev leader. DB this is the sqlite database that I made I'm just going to open it here and you can see that we have the two tables here I think the other one that we were seeing is a builtin table it's not being shown so blog post is the one that I created as part of the migration there's no data in it though right but you can already see the columns up at the top the row ID is the built-in one and sqlite if you're not familiar I made my own ID column just because I was creating the schema that I wanted to play around with if we go over to the ddl you can see this is the schema that I had in my code as well so this is the exact same thing with the three columns that I created and I'm going to jump over to scheme of versions cuz I didn't make this one myself and if we look closely here we can see that it's going to that's the ddl so not what I want to show you but let's go to the data that's more interesting we can see the schema version ID so this is going to be the first version of that schema thanks to our migration we can see the script name that's included here and if you recall I had mine called Script 00001 dosq and you can see the rest of the stuff here this is going to be the assembly and then the location with in the assembly here it's in that scripts folder and then it has the date that it was applied as well so we get this really simple sort of migration checkpoint list and that way dbup like we saw knows how much it's done for upgrades at this point what we could do is we could add other scripts for going to do migrations keep in mind the one I showed you was very very simple this is where Entity framework core and some of the scaffolding stuff could come into play but in this script that I showed you it just created a table if you wanted to in a new migration you could go make a new table but when you start having to move data manipulating the schemas that already exist these scripts might get a little bit more complicated so I don't mean to trivialize it it's just not the focus of the video but with that said we got to see some DB up in this video what I want to do in the next video is show you DB up with something I like to use called Dapper instead of nty framework core and we'll see that inside of an asp onic core application so if you're interested in seeing that when that video is uploaded you can check it out here thanks and I'll see you next time