How do software projects achieve zero downtime database migrations?

I wanted to talk a little bit about doing migrations in production and for those of you who are kind of unaware what a migration is is basically you have a certain form of data in your database and you need to modify in such a way to support new features for example like let's say you have a column called name and you want to split it up into a first name and a last name you might need to write a migration script to handle that transformation but the issue with a migration script is that depending on how many records you have for example if that's like a million records or 10 million it can take a lot of time to Loop over every single row and transform that data into the new columns and because of how long it takes you can actually have some downtime for users or their app could break if you don't properly do this so what I want to talk about in this video is something called expand and contract which if you check out this Prisma link they have a good document explaining like what expand and contract is but it kind of walks you through like what happens if you need to rename a column in your database which sometimes you have to do if you can avoid doing operations on your database that like require this type of work I would try to avoid it if possible because it is a little bit more involved okay so read through this but I'm going to go ahead and just try to talk you through the way I understand end it so let's say you have a database here right and you also have an API okay so let's say you currently have a column called name which has like a bunch of first name and last names kind of combined so like like say Cody Seibert my full name but business requirements change and you need to basically figure out a way to instead of having the whole name stored in a single column you want to create another column called first name and a secondary column called a last name okay and you want to basically take this name split it by a space this again this is just a rudimentary example to kind of talk about this but we'll go ahead and put Cody here and we will put my last name over there all right so that's kind of like the setup let's talk about how expand and contract kind of works right so how it works is you have an API or a client or a server that's currently set up in a way that is going to read and write using this name column okay so for example if someone wants to update their information it's going to update this name column but again the goal is to get this information over here and have the API reading and writing these things so the way expanding contract works is the first thing you do is you need to write a SQL statement to basically create the new columns so you'll create a first name and you'll create a last name and you can do that without any type of downtime right you just basically add those columns so now the second step of expanding contract is you need to deploy a change to your API to have it so that it writes into these fields when someone tries to write into this column right so we're going to go ahead and write into first name and also write into last name okay so whenever someone like goes into the UI and does something whenever you try to modify this column you're also going to update these columns I'll go ahead and put another row down here just to kind of exemplify this but over here we still have data for example let's say a user hasn't logged in yet and they never modified any of their user data so they would still have empty things here right so you're going to have your new code right to the new schema slash columns and again you're still reading over here you're still going to read from old schema names so anytime that you need to show their name you're still going to read from this column here but again you're just making sure that your live data as it's changing your API is writing also to first name and last name so the second step is you now need to write some type of script that's going to run through all of your records for example you have like a million or 10 million records or something run through all those records and start finding the data that hasn't been modified yet and start doing this process automatically so for example let's say Rick James never logs in well you'd have a script that runs in the background that's gonna it's gonna get the Rick James eventually and it's going to make Rick James over here all right and again there's like tons and tons and tons of more records but there's a background process that's basically updating all those and at some point all those are going to finish and at the same time as users are using your currently deployed application it's still reading from the old name and it's writing to the new name so at this point after your little migration script finishes with those million records all of your data should be populated with the first name and last name column okay so now the third part of expand and contract is you have to deploy new code to basically say okay no longer read from the old name we're going to go ahead and read and write from both of these so step three is read and write who knew columns okay so now you have a bunch of old data that's never going to be used and what you can actually do is do a fourth step to basically write a SQL statement to drop this column just delete this column and now you basically occur no downtime your system was able to move all that old data over to a new column over here this could also be migrating over to a new database right the same process can work with the new database let's say you had two different databases and on database a and database b or something like that so basically by following this process you're able to make changes to your schema without having any downtime for users right definitely read through this Prisma document it's super helpful they have like nice little diagrams that kind of show you how it all works um they have some additional steps like test out the new interface cut reads over to the new interface I will say in their diagram they say that you continue writing to the old schema and you don't just like do a complete swap over because what if you do this and something goes wrong you want to keep that column around and just keep on writing data to it in case you have to revert so I showed you like a you know a shortcut way which could potentially lead to some issues but at some point you're going to have I kind of skipped that step right but at step six you're going to have everything reading and writing from the new schema and then you can go and just delete that old old column like I kind of showed you so I just thought this is a pretty cool concept when it comes to managing production systems and how to apply migration scripts to a running system without occurring some downtime I would say though if you have the flexibility to just turn off your system for like 30 minutes run the migration and turn it back on it'll be a lot easier than having to orchestrate this multi-step deployment but sometimes we don't have the ability to do that sometimes we want to achieve this without having downtime which as you can tell to achieve the no downtime you've got to do a lot of extra steps you got to synchronize all this stuff you got to make sure it all works but if you guys enjoyed watching this video give me a thumbs up comment subscribe press the Bell icon like always I have a Discord channel that you're welcome to join if you want to just find a place to hang out or talk to some other developers the link will be in the description below other than that have a good day and happy coding

Channel: Web Dev Cody

Views: 17,330

Keywords: web development, programming, coding, code, learn to code, tutorial, software engineering

Id: cw5K2O4AHJc

Length: 7min 5sec (425 seconds)

Published: Thu Aug 03 2023