Database Schema Migrations with Alembic: A Practical Guide

[00:00:00]welcome everyone um this is the last talk before the lunch break so food is on the way um introducing Tim Mitchell he works with eron steo uh company based in Christ Church U so it's his home to here and I could see you have quite a turnout and uh an is also looking for python developers so if you haven't heard about it talk to these guys in these t-shirts they're looking really interesting stuff um and yeah Tim going talk about um database migrations if you haven't done it it's a world of pain so you're in for a treat so take it away thank you um yeah as the slid says I'm Tim Mitchell I work for a sto I'm a senior software the engineer I've been here for about 10 years now let not do anything how do I make it go to the next excuse me right um uh we make a geological modeling software we have a whole family of software with three different products uh all under the leap fog brand um and we do most of our work with python with some uh C and scon for the hiby lifting you can't hear me I can't do anything about that apart from look down when I talk hello and hello I've been walk working with databases since last year where the speak up you want me to shout okay so um we started on a new project last year and it's when I started working with real databases um so overview of the talk uh first I'm going to set the scene of uh my working environment which motivate in which I use alook for database migrations and where's my Mase on and going to give you an example schem of migration and some of the things we've learned to do um that go beyond the tutorials that you find on the web uh some tips from our experience using aimic and also really the really good news about how the pain has got less and how our processes have changed since we've been using olymic okay the situation is we're working on a relatively young product and we're adding features to that product at as fast as we can um and so because of that there are frequent schema changes and hence many schema versions um so our test data uh is that different schem of versions because they're written at different times we have uh my databases on all the dev machines you know they're at their own versions for whatever Branch they're working on we have VMS for the testers running and they are at whatever version they're on and we need to manage that whole mess of different versions left right and Center um and so the situation for us was that migrations were giving us migraines um you start up your application and then you find that the database didn't have this table um it was quite a nightmare to manage right a little bit about aook it's a obviously a database migration Tool uh it's written by the same author as ISC Alchemy which is very popular it's very straightforward to setup it's got good documentation um the user forms really good responds to questions really quickly the development status is only four in beta but um we've only had one or two bugs in olymic in the time we've been using it and I think it's more reflection on the capabilities at this point than the reliability and the last thing is if you're going to use a liic that means the r or rather your source code is the source of Truth so if there's a table in your source code and there's no table in your database then it's the database that's wrong okay that's not always the case in everyone's um setup okay uh getting almic s is pretty well documented I just going to mention a few things that we need to know for this talk um elic installs an executable into the scripts folder and this executable does pretty much everything you need to do um it has an a nut command that you can see there and you pass to a folder where you want to keep the files and it will create this folder structure um the limic ini file um simply points you to the folder that you uh specified and has your connection string to your database the inf. pi um sets up the actual migration envir ment for olymic with your om tables and other settings uh there's a readme file for you to delete uh makeo script uh for templating the creation creation of your revision scripts and there's a versions folder and this version folder contains all your revision uh steps upgrade steps as you make them we okay we've missed a slide yay there it goes um so for our example migration today we're going to start with the simple um um o it's got one table it's got two columns an ID and a name and our table has some data in it some test data first customer is Mickey Mouse the second customer is Donald Duck now we want to change our schema um the first thing you do is you edit your code to say what you want it to be we've changed the single name column to be a first name and a second name however our database still looks like this so what we need to do is write a script that migrates our database to match our code this is what a limic does so using the uh executable you use the revision command and you can pass it this wonderful flag called autogenerate what it does is it looks at what's in your code looks at what's in the database figures out what the differences are and writes a script for you and this also lets you um specify a short comment to say what you're doing and it's going to into the version folder put this file with starting with a rision identify and your comment and there it has everything you need to do to migrate your database it's written in uh albook operations which means it can work on multiple backends so if you need to support SQL server and MySQL and post squares then this will generate um the correct code correct SQL for all those database backends uh there's also a downgrade function which doesn't fit on the slide if you need to roll back your database now all we need to do is run the upgrade command um so the upgrade the head uh just specifies upgrade to the latest revision of your database we could put in that long u ID if we wanted and you get kabong and this doesn't happen in the tutorial but it does happen in real life um inter feere you know the data part of the migration doesn't is quite hard to document because it's different in every situation so what do we do this is reality but it's not into documentation we need to split a migration into three steps first we need to add the new columns but they need to be nullable because they're going to start off having nulls in them then we can add the data to the new columns moving it out of the name column and then we can alter the columns be not nullable and drop the column there for those who are asleep I'm going to say it again with pictures first thing we're going to do to our database is make it look like this going to add nullable columns then we're going to copy the data out of the name column into the first name and last name columns and then we're going to delete the name columns and change them to be not nullable now SQL Alchemy however wrote me add column null all what we want Esco it's not Esco Alchemy alook what we want alook to do is to write an a nullable column followed by an alter column statement otherwise we have to do it ourselves thankfully albook will do this for us we just need to rewrite how nullable columns over into our upgrade script and this has been copied directly from the docs so it has the a lbec has the concept of a rewriter you can override how it writes an add column operation and you can see in the code there if the operate if the column is null of all then just do it otherwise change the column to be nullable and add an alter column operation this goes into our m.p file we need to only do it once and thereafter all our nullable columns get rendered with two statements rewriters are also very useful for other things such as setting uh permissions when you add a new table and other tasks like that so now migration script looks like this we have our add columns they're nullable Then followed by our alter columns and all we need to do is add our data migration right and to do that we'll just write some normal code um first thing you need to do is get a connection to your database so the op object has a get bind which gets you the SQL Alchemy connection and then you can use the execute method on that to do whatever you like so this is pretty straightforward we iterate over all the customers we split the name and we update the r now we're going back to migrations we can upgrade our database to the head rision and it prints you out useful little message and that's the job done quite straightforward and our database now looks like this what we've also have is an almit version table which elit puts in by itself you can control where it's put and it stores for you the version of your database um this is really helpful so that your database now knows what version it's at it can know which upgrade steps it needs to run and which ones it doesn't now in some situation we you need to um get your DBA to migrate your databases and so you can't pass some your script and say run this python against the database please he's going to say no way but that's all right elic knows about database administrators and he's there's a simple SQL um option and so instead of actually migrating creting your database it's just going of like your script so easy peasy not non type object is not iterable we don't have a database connection we're just generating a script so we can't iterate over the rows of the table as you see here so uh rule number one when writing uh data migrations is that you can't actually use the results of a select statement this is a good thing in reality because otherwise your upgrade state would upgrade the first customer to be Mickey Mouse new second customer to be Donald Duck and would not migrate the rest of your customers so it's quite simple in this situation to rewrite your upgrade step to something that doesn't use a for loop we're using this instra and the substra SQL commands that are in SQL light and away we go we can run our upgrade commands that generates a script you can give that to your DBA and that's your job done um couple of tips for using a limic um that we have found if you troll through the docs you find you can actually run it programmatically and actually it's really straightforward um you need to create a configuration object from your almic ini file name and then there's a Command Module which has all the commands that you have on the command line and they're very easy to use and so uh we hardly ever use the executable we run our our own scripts and the good thing about being able to customize what your run how you run a limic is that you don't need to use random uids for your identifiers um they use at the start of your file names and you get a folder full of your IDs and you don't know what order of they're in um but they're really good for uniqueness across branches so I can kind of understand why they're done it but you know sequential is good for us and so we changed their code to use sequential numbers 1 2123 and so on and so forth and then John on his Branch would make 10 an iron my Branch would make 10 and then you had two databases with vision 10 and then you had a mess on your hands again so when I get to round to committing my code we're going to start using three-digit numbers followed by a random bit and this morning I thought to myself we could just use the comment string as well um so yes do um write your own new revision command that puts in a sequential um revision identifier cuz then your upgrade steps are sorted in your IDE and then and your file explorer um yeah that was all I wanted to talk about when I planned my talk but when I came to actually writing it uh the really thing things the things I really wanted to say was actually how it have made my life so much better and easier um so what has changed for us since using olymic um I used to do this all the time just uh blow my database away start with a new one while I'm writing my code and then I'd switched to another Branch I had no idea what Vision that database for that because it's been the weekend so now I always stamp the database when you create one that way you always know what version your database is and it helps you keep control and manage all the different copies of your database you have all over the place um we had well we have sorry um integration test let's use SQL dumps for our test data to restore the database to a known State and every time you change the schema you have to recreate that data in the new schema um and that was actually as much work as the feature or bug that you were fixing and it was a real pain now we added the version number to our sche to our SQL dump so it knows what version the schema it's on when we restore the database we then run the upgrade steps now when I add a table I don't need to change the test data at all except for perhaps if I need to new test data for my for what I'm actually doing that means your test data actually changes a lot less often and makes life a lot easier it also means that your migrations tips get tested as well which is excellent um for our testers uh they always used to use the the restore utility that came with the database we used post um and that was fine except that they had their own they' done some experiments they done some stuff they dumped it and they had dumps at all these different versions and they restore it against your branch which didn't match and that was a pain in the butt so now whenever we build our software we build a restore tool that comes with it and so when they running your branch of the application they run your restore tool and it runs your migration steps and migrates their test data up to your version of the schema and it's made life a lot easier for our testers too um as I've already alluded to we had a lot of problems with running up the application talks to the database it runs along fine for a while until it hits the bit that's not there and that happened quite a lot now whenever we start up the data the application we check the schema in the database matches the schema and our o uh we use AIC for all this has this lovely compare metadata command which produces a list of diffs which is exactly how the auto generate command works for generating revision scripts and you just check of that length there zero um just checking the Version num is not enough because otherwise you'll change a table in the r go have coffee come back run your code the versions are the same but actually the schemas are different and you've just forgotten about it um so yes elbec has definitely taken the migraines out of migrations for us um and I guess that property is not unique to limic but um yeah I hope some of these practices will be useful to you guys that's all I have to say thank you for listening and do you have any questions yeah thanks very much Tim than right um questions you can make me run right um yeah Tim you've obviously used this as you've been developing the software and you've been iterating so fast that the database has been changing um but is the intention that it is also part of the release product for the users so that when you go from a version one to a version two that the your users actually migrate their databases as well or is it just purely for internal development um both so at the moment that when we install our uh our server on a machine if there's a database if when you configure it to a database server and and there's a database there uh it will upgrade it okay so it's Enis for some of our customers they will just install the new the new software and it'll upgrade their database when they install it uh we know that some of our other customers will have a DBA and I'll say no way give us an SQL script thank you um but and we'll use a ling for that and toing right the script fly yeah um yeah so uh as you um write the actual data migration as SQL rather than using the irm I guess there's no way to actually make that back in neutral um you can use ESL Alchemy core um functions and methods um but yes you know for anything that's um Beyond a test example for a talk then you do usually end up having to use uh backing specific code um but again you can with the python switch on the dialect and you know uh generator script for for post and Generator script for MySQL that's great thank you does it do um backward migration handling roll backs as well uh yes it does um I didn't show any my slides cuz space um but yes it it writes the reverse of an upgrade step so there's downgrade steps in all migrations file um but when it comes to data involved and we have to manually do all that stuff we tend not to use them uh have you done any comparisons with like Jango Migra or south or anything like that no I haven't um we looked around we said oh it's written by the same person let's give it a shot we haven't had reason to not use it um yes I haven't actually done any real comparisons any more questions well I have a question for the audience who has used almic and up keep your hands up um and yeah we have like two shows two show and you want uh comment any further on the use uh yes thank you for the presentation I've only I guess used sort of the the outer wrapper the vanilla flavor of it so yeah uh you've certainly opened my eyes to the scripting possibilities inside of it I can see it being a lot more useful well guys it's almost lunch thanks very much Tim thank you