Alembic Introduction - Migrations and Auto-Generating Revisions from SQLAlchemy Models

in this video we're going to demystify the almic package in Python almic is a database migration package that's commonly used in flask and fast API applications as well as with any other framework that requires a migration model that's not built in such as Jangles and when you're working with databases migrations are a very important tool to maintain consistency and allow you to roll back changes in this video we're going to create a postgres database and build up a small schema using a limic and SQL Alchemy and we're going to show the basic operations that you can do in almic and how to apply changes to your database with this migration package so let's get started so let's start with a quick look at the almic documentation which I'll leave a link to below the video almic is a lightweight database migration tool for use with SQL Alchemy and we're going to look into some of the topics here later in the video such as creating a migration environment and we'll also look at autogenerating migrations from SQL Alchemy models that are defined in our applications now what we need to do to get started is actually have a database where we can then apply migrations to so we're going to use postgresql and to keep things simple I'm going to use a Docker image that I'm going to get from Docker Hub here so I'll leave a link to this below the video and you can copy the command on the right here Docker pool postgres and we can go to vs code now I have an empty directory open here we can paste into the terminal the docker pool command and then that's going to bring the postresql database image down onto your computer and this will also work if you're not using Docker if you want to download postgres directly I'll leave a link in the video for that as well now once we've downloaded post GES you should see the image when you run the command Docker images so I'm going to run that just now and you can see we have a couple of postz images at the top here now what you can do in Docker is start a container from the image and in order to do that let's go back to the documentation for this Docker image for post SQL and if we go down here we have a Docker run command now I'm going to going to use a modified version of this command in order to start a container that's running post gql and then we can apply the migrations to that database so let's go back to VSS code I'm going to clear the terminal and what we're going to do is we're going to issue a Docker run command and we're going to give the container that we're about to start a name of a lmic - demo now we need to be able to connect to the database running in the container so we're going to map Port 5432 in the container to 5432 in the host with the- P flag to this Docker run command and once we've done that we're going to set an environment variable called postgres password and we're going to say that equal to secret and in production that can come from somewhere more secure such as a n file and what we can also do is we can pass the- D flag to the docker run command and that's going to basically run the container in the background and that will free up the terminal that we have in vs code so that we can run the almic commands later in the video and the final thing to pass is the actual image that we want to start the container from and that's the post gz image that we have just pulled so let's now run this command and you can see we get the container ID that's been started here and we get the control of the terminal coming back here because we've started that in the background with the- d flag now we can verify that postgres is now running There's an actual container running with the docker PS command we get back the container ID for that postgres container and you can see the name of the the container that's been started and that's a lmic demo so the post SQL database server is now running in Docker we can now create an actual database within that server so what we're going to do in order to do that is we're going to run a Docker exit command and the name or rather the name of the container that we're going to connect to is a lmic Das demo and the command we're going to run in that container is the create DB command so we're going to create a database the user is post GES and let's call the database alicore DB so we now have that database and that's the database we're going to connect to from almic and we're going to make changes to that database add tables and add different columns to tables using almic migrations so let's get started with that now now what we're going to do to start with is create the migration environment but in order to do that we need to actually install a lmic and we're also going to install the cyop PG package which is the post SQL driver for python so you might notice on the left hand side here we have a virtual environment I'm going to activate that environment now and we're going to to run the PIP install commands so once the environment is activated you can run pip install almic and cycop pg2 once those are installed we can minimize the terminal a little bit and what we're going to do is go back to the documentation and we're going to look at the migration environment that comes with a lmic so when you use a lmic you start by creating the migration environment and this is a directory of scripts that is specific to a particular application and the migration environment is created just once and is then maintained along with the application source code itself so the migration environment is going to be committed to your repositories on GitHub or gitlab or other systems like that and in order to actually create the migration environment you use the almic and net command now there's some structure here for the resulting environment but what we are going to do is we're going to go back to vs code and we're going to run the almic inet command and we need to give the environment a name so I'm just going to call it my app now when we run that you can see what's being created on the left hand side we have an almic do ini file and this is the file that almic scripts look for when you invoke the almic commands and we'll see a little bit more about what this directory does later in the video if we look at the my app directory what you can see in here is that we have a directory called versions which is currently empty and we have an env. Pi file here as well and n.p is a python script that is run anytime the almic my migrations are invoked and if you look at this file you can see a couple of things being imported from SQL Alchemy and this file will always be run when you perform your migrations and what it's going to do is it's going to use SQL Alchemy and use the engine that's provided by SQL Alchemy in order to connect to the database and it uses this engine from config function from SQL Alchemy if we go down to line 60 here in the Run migrations online function we create a connectable variable here by running the engine from config function and then we have a couple of context managers here to connect to the database and then to start a transaction that's going to run the migrations now we don't need to know too much about this in. pile what we're going to do now is we're going to tell almic how to connect to our database that's currently running in the docker container now in order to do that we're actually going to go to the almic do ini file and in this file we're going to edit one setting that's a SQL Alchemy do URL setting so you can see at the moment that we have this generic iic code here we need the driver which will be a postgres driver or a MySQL driver or one of the other databases we also need a user and a password and a host and then the name of the database that we're actually connecting to so I'm going to paste in some code here so let's remove this and what we're going to paste in here is this line of code we have the driver which is postgresql our user in that database was the postgres user and the password Here was secret not my secret password and that was specified in the docker run command when we we pass that post password environment variable and then we have the host and we're going to use Local Host here and the name of the database that we created was almic DB so this line of configuration here will tell almic how to connect to the database that is the connection URL that we're going to use so now let's get started by running some migrations now in order to create a migration we have a command called almic revision that we can invoke and we can pass a message to that that's similar to the message you use on GitHub for example when you make a commit the message just describes the migration that we're about to create so let's just say we're about to create a user table in the database we can create a revision with that message and then execute this command and you can see what has happened on the left hand side under the versions directory we now have this revision that's been created here with the name it's got a hash here and then create user table which matches the message that we used so if we open that up let's see what is in that file so this is an autogenerated file it's got a rision ID and if we scroll down you can see we have the revision in a variable here we have another variable under that called down revision which is currently set to none and this will be none only on the first revision that you actually create the first migration and the purpose of this variable is to point to the ID of the previous Revision in our almic configuration we'll see a bit more about that in a second and we have two empty functions here one called upgrade and the other called downgrade now in almic what we can do is we can add code to to Define what happens in the migration to the upgrade function and the downgrade function we can add code in here too and the code we add here tells alambic how to reverse the migration and later on we'll see how to autogenerate the code that is added to these functions from SQL Alchemy models but for now let's define the code here and we're going to use some almic directives in these functions in order to Define what happens for the revision now if we go to the top here you can see from almic we've imported op and this can be used to define the operations in the database for this particular revision so we're going to use the op. create table function and the first parameter to create table is the name of the table and even though we called the migration create user table I'm actually going to create a table called employee here and we're going to Define some columns now for that you can see on the vs code highlighting here we can Define any number of columns after the table name here now we have access to SQL Alchemy columns through this SA object so we've imported SQL Alchemy as sa so let's go back down to the upgrade function and we can Define columns using SQL Alchemy do column so let's define the First Column and that's the ID column which is going to be the primary key for the table and that's going to have a data type of SQL Alchemy do integer and we can pass primary key equals true to that table once we have that we can Define the second column so it's going to be s. column and the name of the column is going to be name this is going to be the name of the employee and that's going to be a data type of SQL Alchemy do string with a length of 50 and we want to make sure that that is not nullable so we're going to pass nullable equals false every employee must have a name and let's now Define the final column using the SQL Alchemy do column and the name of this column let's just call it current and this is going to check whether or not the employee is currently employed and that could potentially not be the case if we have a table of employees but some of those employees are no longer employed they've been sacked or they've moved on so this is going to be a bullion column that tracks that so it's going to have the data type of SQL Alchemy do bullion and let's default that value to true so that is the upgrade function that defines an operation that creates a new table for this particular revision and we need to also Define or we can optionally Define the downgrade function as well so all we need to do in downgrade is basically reverse the migration so let's use the op. drop table function and the name of the table we're going to drop is the employee table and that will essentially reverse the operation that we have in the upgrade function and it's going to remove that employee table from the database when we downgrade or when we reverse the migration so let's save this revision file and what we're going to do now is we're actually going to run the migration against the database now in order to do that we have a command in almic and that's the almic upgrades command and to that we can pass the revision ID of the particular revision that we want to apply here but most conveniently if we just want to apply the most recent migration we can use the almic upgrade head command so let's run this command now and you can see it's applied some kind of upgrade here and it's got the revision ID for this file that we have and again the message I put create user table it should have been create employee table but the important thing to check is has this table been created in our post SQL database now in order to check that what we're going to do is connect to that database using the docker exit command and we're going to pass the name of the container we want to connect to and that's the almic demo container and we're going to connect using the postgres command line too and that's psql and we need to pass a user to that which is the postgres user and the database with the- d flag that we're going to connect to is the almic DB so let's run this command and now you can see we're connected to the postes database we can run the/ DT command in the psql tool and that will display the databases or the relations that we have in the postc database and you can see we do have a table now called employee now we can issue a select command select all from employee and that currently returns no rows but you can see the structure of the columns we have an ID column a name column and a current column and that matches what we passed into the almic op. create table statement we had three columns here with the exact same names and you'll find that the data types for these columns and everything else is as we Define them in that operation now let's say we wanted to perform a further change to this table we have ID name and current but we might want to also add a job title for each employee so the process of changing the database again or changing this particular table is that we create a new revision with the almic revision command and we can pass a message to that as before and the message we're going to pass is ADD job title column now actually I need to get out of the psql tool so what we can do is stop that and the Slash Q command will get us out of psql and then we can paste that back into the terminal so we need to do this on our local computer not in psql on the container when we do that you can see that we generate a new revision file on the left hand side so the versions directory here every time we create a revision it's put into that directory and if we look at this file you can see some details that we have here we have the revision ID as before but this time we have a down revision with an ID that points to the pre previous revision file so every time we create a new revision it's going to point to the previous one and this allows a limic to build up the correct order of the revisions when it performs its actions against the database so these IDs are very important they allow almic to understand what was the first revision that was created that one has a down revision of none and from there it can look at all of the files and look at the down revision and build the correct historical order for these revisions and that's kind of similar in programming to a linked list where you have a pointer that points from one item to the next item and then from the next item to the one after that so what we want to do in this revision is add a new column to an existing table so what we're going to do in the upgrade function here is we're going to use the op. add column directive now remember when we created the table we used the create table directive this time it's going to be add column and again the table name needs to be the first parameter here and this is going to be a new column in the employe table and what we're going to do to define the column again is we're going to use the SQL Alchemy do column and we're going to pass the name of our new column which is going to be job title and a job title is going to have a data type of string so let's pass SQL Alchemy do string and we'll give it a length of 64 and finally we'll allow this one to be nullable so we can set nullable equals true and let's also Define the code for the downgrade function so in order to reverse this we can use the op. drop column function so dropcore Colum column and the name of the table that we want to apply this to is the employee table and the column name that we actually want to drop is the job title colum so let's now save this this is the code that defines our revision what we can do once we've saved that is go back to the terminal and we can run a lmic upgrade head and this time it's running the upgrade from our previous ID to the new one and it's adding that job title of column so let's go back to psql using the docker EXA command that we ran before and if we run select all from employee we can see this time we have a new column in that table called job title so that's working fine we're applying these revisions and we're able to change the structure and the schema of our underlying database using a lmic let's get out of the psql console and we can also show a couple of extra commands here we can run the almic history command in order to generate the history from the first revision up to the most recent revision and this is from the most recent one which is at the top to the very first revision which is at the bottom if we run that command we can see the historical revisions that have been applied with almic let's now talk about downgrading the database what you can do with almic is you can download to the last version or you can optionally entirely roll back all of the changes that have been applied to the database so for example if you wanted to downgrade to the last revision and that's the one before we added the job title column we can apply what's called a relative migration and relative migrations they operate in place from the current revision so I can run a command here called a lmic current and that gives us the ID of the current revision that we have and it's the one beginning with C here after we added the job title column with this function here so a relative identifier will work relatively from the current migration so in order to downgrade what we can do is we can run a command called a lmic downgrade and we can add a relative identifier here and that's just a number so let's add minus one and what that's going to do is it's going to downgrade to the previous revision that we have from the current revision that we're on so let's run this command and you can see we get this output here if we go back to the psql terminal here and we rerun the selection you can see we no longer have the job title column so what's happened when we run the olymic downgrade command is that we're telling olymic to revert the last migration now let's run the almic current command and you can see that the current migration that we're on it's no longer the one beginning with C it's the original migration where we actually just created the table so Olympic now knows that this particular migration beginning with C is no longer actually applied in the database we can again apply a relative migration here with the almic upgrade command and instead of passing head to that or passing a revision ID we can actually pass a relative identifier here so if we pass Pass Plus One what it's going to do is it's going to apply the next migration from wherever we are currently so let's run that and it's going to read the job title column again if we go back to psql here and we show the selection you can see we now have the job title column so this is important we can apply relative upgrades here relative downgrades from wherever we currently are in the tree that represents our migrations and if we want to completely revert the entire history of migrations we can run a lmic downgrade and we can pass base as the identifier here that's going to completely reverse everything and go back to the original state of the database let's go back to psql on Docker and again if we select all from employee this time we get back the statement that the relation employee does not exist that's because the table is no longer in the database because we've reversed the entire history of the migrations so let's regenerate everything with the almic upgrade head command and that's going to apply all of the revisions and regenerate that employee table with all of the columns now let's finish this video on almic by looking at how we can autogenerate migrations from SQL Alchemy models now if you use something like d jangle you know that when you change a jangle model you run the make migrations command and jangle automatically detects what's changed and then when you run the migrate command it will apply those changes and change the underlying database so so how do we do something similar with SQL Alchemy this is useful because we don't want to always specify the code ourselves when we have an upgrade function if we change a SQL Alchemy model class it would be very useful if the changes that we've applied to that model are automatically picked up by olymic and those changes are applied to the underlying table when we actually run these commands without is actually specifying the code ourselves now let's start by removing all of the changes that we made to the this database we can do that with the almic downgrade base command that we saw a second ago and then what I'm going to do is actually remove these two revision files entirely so let's delete these from the file system and we can remove them from the py cach as well so we're back to our original state with an empty versions directory what we're going to do now is at the top level of this directory structure we're going to create a models.py file alongside the Olympic ini file and let me make the terminal smaller here at the top we're going to bring some imports in from SQL Alchemy and that's the column date time string integer and Funk objects from SQL Alchemy and we're also going to bring in the declarative base so let's create an object called base here by instantiating declarative base and we can create some metadata from that so metadata is going to be equal to base do metadata now we can create subclasses of this declarative base so let's do that just now we're going to create a class and let's just call this one company and that's going to inherit from That Base Class and the Base Class you can think of it is kind of similar to the jangle model class if you're familiar with that and we can also have a double uncore table name property and that tells SQL Alchemy what we want this table to be called so let's call it company now a SQL Alchemy model just like a jangle model it consists of columns so we're going to create a column called ID and that is going to be a data type of integer and remember we imported these at the top and we can pass primary key equals true into the ID column let's now create another column called name so again it's a column this time it's going to be a data type of string and we'll give it a length of 60 and for the name of the company let's add another constraint here we're going to say unique equals true and one more column let's add one called create that and that's going to be a column of type date time and we're going to give this column a default value of the funk. Now function in the underlying database and finally in this model let's define a Dunder repper method and that's going to print the ID of the company as well as the name of the company so this is a model we now need to tell almic about this model so where can we actually do this if we open up the my app almic migration environment we can actually edit this n.p file so I'm going to go to the top of this file and we're going to bring some imports in here for our model but what we're going to do first is go to the documentation in Olympic for autogenerating migrations I'll leave a link to this below the video what it says here is that Olympic can view the status of the database and compare against the table metadata in the application and generate the obvious migrations based on the comparison and this is achieved using the-- autogenerate option in the almic revision command what this is going to do is it's going to place the so-called candidate migrations into the new migrations file that's created in that versions directory and as a developer you can then review and modify those by hand and then proceed normally with the olymic upgrade now to use autogenerate you need to modify that n.p file as I said so that it gets access to a table metadata object that contains the target what we need to do basically is defin in this code here we need to import the declarative base class and then reference the target metadata and set that equal to that base do metadata property so let's do that in n Pi now at the top underneath the almic import from the models module let's import the base class and then we can find the Declaration of Target metadata and you can see that's down here on line 22 and what we can do is change that from none and we're going to reference base. metadata so now that we've linked the metadata here containing the table objects to almic we can now pass that-- autogenerate flag to the almic revision command so let's try that out now now on the terminal I'm going to clear the terminal and we're going to run the almic revision command and this time we're passing D- Auto generate now as well as that we need to again pass a message in for this and let's pass the message added company model let's run that now and see what kind of output we get if we look at the revision file that's created in the versions directory we can see what SQL Alchemy or rather what a lmic has added to the upgrade function based on this SQL Al Comm model we have another op. create table directive we create a table called company and you can see there are columns for the ID the name and the date time for created that so it's got those completely correct and we also have a primary key constraint on the ID field and a unique constraint on the name field so that looks completely fine if we look at the downgrade function you can see it simply drops the table company and that is totally fine as well that is the reverse of that migration we just get rid of the table that we're creating for that model so you can always modify these if you need to but if you're happy with that what you can do on the terminal is run the upgrade so it's the olymic upgrade command and we can pass head in and we get back the statement that says we've added that company model I'm going to go back to the docker psql tool and what we're going to do in here is display the tables and you can see we now have a company table in the database and again this is going to work perfectly fine if we go back to models.py and let's create a new column in this table so we're going to create a column called address so that's going to be a SQL Alchemy column let's just make it a string for Simplicity and we'll set nullable equal to True here if we go back to the terminal we can clear that get out of the psql tool and again we're going to run a lmic revision -- autogenerate and this time we have added address to the company model so let's run this command to create the revision file and again we can go back to that revision file and we can INSP ECT what's been added to that file so if we go to the upgrade command here the upgrade function you can see we have an add column statement for a column called address and the downgrade function will simply remove that column from the company table so that looks good as well what we can do is go back to the terminal and I'm going to run the almic upgrade head command again and this time let's go back to psql and we're going to select all from that company table so select Dash select all sorry from company when we run that you can see we now have the new address column in that table and that's going to be about all for this video we've showed how to create revisions in almic we've shown how to set up the almic inii file in order to connect to a database and we've seen how to define operations in the upgrade and downgrade functions that come in your revision files and we also saw how to integrate a lmic with a SQL Alchemy model class in order to autodetect changes that have been made to SQL Alchemy models and apply those in your revisions now there's so much more that we can do with almic if you're interested in more content let me know in the comments and we will also use almic in upcoming videos on Fast API when it comes to integrating fast API with a relational database but for now thank you for watching this video I hope you've enjoyed the video and learned something if you have please give it a thumbs up and subscribe to the channel and we'll see you in the next video

Channel: BugBytes

Views: 9,391

Keywords:

Id: i9RX03zFDHU

Length: 28min 28sec (1708 seconds)

Published: Tue Dec 12 2023