FastAPI # 0027 # Migrations using SQLAlchemy Alembic

[Music] okay so this is the next video into the continuation of our first gpa series and in this video like we will look about the uh sql alchemy alembic so how to use what is like alembic how to use that and why we have to use that but before definitely jump into let me activate my environment [Music] and let me show you the code [Music] so this is my code right and if you see this line number uh base dot matter data dot create all bind is equal to engine so within the code we have like mentioned that like okay read the metadata and create all the tables uh on the particular database all right so we are creating the tables using this command this command is working perfectly fine and like the tables are getting created uh as well but what the disadvantage of of this command is or you can say this approach is so let's suppose in futures so in the modal.pivo file we have like all the tables which we have defined so let's suppose in the future like you came here right and you want to define like some new field all right so let's suppose i want to change the field from advantage to supervisor all right so let's suppose there is a change in this field and let me save the that field and let me go to that in the testing so right now we are using the testing database leave this alembic version so just ignore that in the user if you go to the columns so in the column we have id field email field password field is active field all right but we have like updated our code and we have added one more column so and the column is like is active is admin so whether the user is admin or not so we have added this column earlier like only we have like this uh four columns so you could see in the database we have only this four column id email password and is active but in the future like we go to the requirement from the customer and the customer told that now we have to incorporate what add one more column into the user table so what would we did is we will come into the modus dot pv file we add here like is admin that is one more column and we save it all right and we will like start our like code using the hyphen f1 reload because we are like running our main file and within the main file it is written like base dot metadata dot create all so it is reading the metadata and it is creating the table so if i like run it so let's wait for a few seconds so application has been started but if i go to the database and if i refresh my schema and i go to the user table and i go to the columns i could not see like uh the column is admin which we have like uh added into the into our models so why this has not been like a reflect here clear nano main dot pivot file because here we are mentioning here read the metadata and create all the tables so for the first time it is fine so whatever the column name like it is mentioned it will read all the metadata and it will like define that but in the second case it will read the metadata even though like it find the change the metadata but the table is already created so it will not recreate those table so that if it if the table is not getting recreated then the another table is not getting added so the one approach is either you will like drop all the tables and then rerun this command so if you rerun this command then you have added one more column and then that column will be get reflected here but the disadvantage of this approach is if you drop the table so you will lost all the data so we can't we can drop the table in the testing environment because in the testing environment it contains the test data so it doesn't matter we can create the test data like again that is not a like a very concern for us but in the production environment where the data is of actual customers of actual users we can't drop the data right because if we drop the table like the data resides in that table will be get dropped and if we can't draw the data but we want to add the new new column in the user table but how to do that because create all command will not able to like reflect the table reflect that that column basically it is just creating that table and the table already exists it is not modifying the existing table so what is the another approach so the another approach is so we have the another approach so we have the alembic so alembic is a like one you can say sql alchemy uh package which work with the python so it has various uh like functions so it will create the table as well right but the main function like of this olympic module is the migrations so when i say migration so migration means let me open the terminal so when i say migration models so migrations means so my grade from earlier table to the new table so earlier table has only four columns id email password and is active now we want the new table which has the column is admin but when we add the new table we want the same data as is so we are migrating our databases you can say we are not migrating our database from one database to another that is we are not migrating from post as grace sql to the microsoft sql or sql server no the database underlying database remain will be the same but we are migrating our like underline structures so earlier the structure has only four columns now we have the like five columns so for that we have to use the alembic so if you google it and if you write sql alchemy alembic and if you open the first uh url that is alembic.sql comment that is official documentation if you read the first line so it is mentioned here alembic is the lightweight database migration tool or uses with the sql coming database toolkit for the python so it is like a python python package and which is used with the sql alchemy databases only all right so it is a it can like perform so many operations so it will create that table so it doesn't mean that it will only do the migration it will create the tables as well but the main purpose is the migrations because for the creation so to create the table so why we want the external package we we can like create the tables like with the normal normal commands write the base dot metadata cradle that is only one line command we can create the table with this but with that command we can't do the my migration migration is from one schema to the another schema if there is any change if we add one column if we will draw one columns even if we add like uh okay if we add one more table then the create old table will even detect the table and we will do that but if we change the schema that that will not be done all right so that's why we have to use the alembic uh package so to use the alembic language package first we have to update our requirement.txt and we have to update here with the alembic and let's save here and let's install that because we have to use the alembic module so first we have to update our requirement to txt and we have to install that alembic module so all the requirement got satisfied but it will install the alembic so it is it has installed the alembic so successfully installed alembic all right so alembic has been installed now what next so if we know how the alembic will work what are the various commands then it is very easy for us to like to do the work with alembic so either you can read this documentation so this is like basically tutorial so if you go to the front matter and you code the tutorial so like everything is mentioned here or rather than reading the tutorial you can follow this a simple flowchart so approximately 70 to 80 percent of the things like from the tutorial i have covered within this is a very simple flowchart so it is a like as sql algorithm so first step is we have to install the alembic so we already did that we like did the entry into requirement.txt file and we installed that so this step is done then we have to use this alembic module so that like it will perform the migration so first step is we have to create the migration environment all right when i say migration environment so like what generally alembic will do is now so like a lamb generally like at the back end it like work in a form of versions so when i say it will work in a form of versions means so like let's earlier we have only like four columns now we are adding the five column so what it will do it will save the earlier content as well so it will like uh create a one file in which like it will like store the earlier structure so it means the earlier structure has only four table and it will create a new version with the five columns and why like we have to like saving the old version why can't we are only saving the new version because let's suppose if in future we want to revert to our old version so we have the old version file as well so old version file means we have the like uh only the file which contains only like four columns this is so we have that file as well so we can revert to that file as well so if we don't want is admin column in near future we can remove that as well so just we will like use the old version so it works in a form of version but where it will store versions or like so many configuration files right so there is a place where it will store that everything all right so my when i say create the migration environment which we have to create that place so to so we will not create that place manually so place means you can say a kind of a folder so where is the folder where it will save all those versions so we have to create that folder but we will not create that folder manually so there is a command so the compile is alembic indeed so alembic init means initialize and then the folder name so whatever whatever the folder name you want to like create you can give that let's suppose i gave the migrations or you can give lm bigger you can give abc ef gh any any any folder name you can give that so it will create an environment and when i say environment it will create a folder and subfolder as well so install alembic is done now we have to create an environment so environment we have to create a folder where it will store everything so to work here like for the further commands it needs environment so let's create the environment okay so the command is alembic so right now we don't have like we have only these folders right so let's create alembic init all right and let's suppose like i gave the folder name as migrations right as soon as i hit enter it will just create the folders that's it it will not touch the table it will not touch the database it is just creating the environment and when i say create the environment it is just creating the folders that's it so if you read the command it is creating a directory it is creating a directory it is generating some files it is creating the directory that's it it is not touching given database it is not reading the metadata nothing like that it is just creating a directory structure so what all folder it it created so it will create the migrations folder it will create the versions it will create the alembic note infi it will get the en without pva file but why it is creating these so many files what is what are the like use of all these files so let's look that so if i do ls so earlier we don't have the migrations folder now we have the migrations folder because in the command we told the alembic initialize it when when it initialized it create a migration environment but which name by by which folder name it will create the environment so we give migration so this is not like you can give any name alembic abc efgh any name you can give all right so it created this migration folder along with that it created this alembic dot ini file so it created in the outer directory it create these two things alembic.ini file and migrations folder if i go to the migrations folder and do ls under the migrations folder it creates so many other things that is the versions folder script.po file dot mako file so mako is like a kind of one more extension like dot piva file is for the python mako is also the python module so that mako read me a normal readme envy.pv file in normal pi files all right so it created that all right so let's understand what is alambic.ini what is migrations and what are all these so only these folders it created so what all portrait credit migrations so migrations created versions so within the version we have the within the migration we have the version olympic dot eni so at the outer folder we have the alembic dot ini e nv dot pfi we have the envi skip dot py dot mako script.py.com all right so read me is just a normal read me so nothing is like that so just a read me file versions so nothing is in the version so it is empty so versions is the basically directly because it will like store the version so when we like earlier we have the four columns right now we are trying to create a five column so it will store two version one person which has a four column four columns and one version which has the five columns so it and let's go in the near future we will add few more columns then it will create one more version so anytime we can like move to any version so those files will be stored into the versions folder okay version folder is done now let's look at the olympic dot ini file so nano alembic dot inf so a lambda lambic.ini file is you can say is a is a kind of a configuration file not a configuration but uh when we run any command so we are running alembi command so to use the alembic modules we are running the lmb commands to run the lmb commands so where it will store the versions where it will store like everything so how it knows that so ini is you can say a kind of a configuration like database configuration like file so it told that like script location is the migrations folder so they are saying that okay we have the migrations folder you need to check like check the migrations folder and why it has migrations folder because we we have the migration voltage if we create with the like alembic init alembic then you will say like here the folder will be change alarm big so so many things like here like what is the database url like when it create the table like which database like it will create the table like where it will create the tables because uh like which database we are whether we are in sqlite whether you use that using uh ms sql whether we are using posts as girl so which database so by default it writes some like some any like a random db name but we have to override it so basically like it is like containing some kind of like information which alembic needs to execute its command all right so let's update the information so first we need the like we are using the postgresql so we have to update this sql alchemy dot url so rather than updating this so rather than updating this let me comment this and let me add alchemy dot url so we are using the postgres sql uh let me show you so where like in the configuration.pv file so this is our database url right so the same thing like we have to like uh define here so what you will do so from the sqlcom.url we will like do postgresql colon colon then username postgres then password postgres then add the rate localhost then port 5432 then database so our database name is testing so we have like uh define the connection as well right because when we run the command it will create the tables because we will not uh like it it will create the table it will modify the table it will alter the table it will delete the table anything whatever it will do so it will do everything so it should know like in which database i have to do all these things so in the ini file we have to like uh mention that so we have mentioned that like our like a database url okay so let's save that so alembic dot ini is done now let's look at the other files so now we have the envelope pivot file so let's look at the enviro file so envy.po file is you can you can say it is another kind of a like a piva file or you can say a kind of a configuration file only like uh which will run like when we run some commands which command it will run so they have like predefined some functions here so they have like offline function and the online function so offline functions means so let's suppose like when we run the command and our database our database is not up then run migrations offline automatically will run but let's if our database is like up and running then run migrations online will be run automatically internally i am telling you but you will not like uh like run manually this so here like automatically like they are mentioned if the contact is offline then run the offline then if the context is online then run the online functions so so this is like a like a very like a basic file but this is the main field that is the target metadata field so target metadata is basically if we want to like we are saying that uh like but how the alembic will know like which column because we will add a new column or we will we will modify any new column into the models dot pv file but how how the alembic know like which column i have added which column i have removed like what changes i have done all right so let me again show you so no main road so here we have the base we have the metadata so we are reading the metadata and we are creating all the tables so metadata is reading all the tables from the mod po file because in the model dot profile we have defined the base so automatically it will read the base file so similarly to the alembic we have to inform from where it will read that metadata so we have to inform that as well all right how it knows that like from where i need to know the like where where you have defined the table structure so we have to like define the metadata here okay so that is also done so now the only one file is left that is the script node mako file so mako if you google it what is mako mako file type so developers file use developer file used by mako a python template engine may contain both text and the statement written in the mako language compiled into the python module which can be invoked in the python program to output a field template most used for the auto generating web pages so what is a maker file so it is a kind of a template so what it will do is right here so like they have like created some template here it will be like here the message will become here the revision id will become here the device is created some some some imports are coming let's suppose we want to we don't want to write our code but we want to dynamically generate our code python code so to dynamically to dynamically generate our python code we should have some template so a standard template so the standard template to write the standard template we use the mako file so when we run some commands automatically it will read this maco template and it will generate our python code all right so what it will do when i i will show you like in the like just after a few seconds so it will read this mako file and automatically it will like put like wherever you see the dollar symbol it everything like it will like put here all right so we have read like uh look at the mako file so everything is done and now what next so our environment is ready so let me close this so our environment is ready so environment is just basically means where it will store the store the versions along with that like we will update the alambic.ini file so like which database like it will connect so we have defined the i will like update the like alambic.enf now we have the two options one option is whether you want to manually create the scripts or whether you want to fully automated scripts manually and fully automated is like whether you will manually type everything like okay i have added this column i have delete this column i modify this column whether you manually do this or whether you want alembic should do that alembic automatically read your models dot piva file and alambic automatically do that everything for you so we have two options either you can go with this path or either you can go with the below path so i will show you the both the path so let's go with the manual steps so for the manual steps what we have to do we have to run this column command alembic revision and then we have to give some message message with the command minus f and then some message name so let's run that command alembic rev revision minus m and let me give some command initial alright so i have like enter that command so they told that okay you have entered that command now i have like created some like initial like uh so if i go to the migration if i go to the version if i do ls now you could see one version is created if i do 4c so initial version is created and it is same as our mako file so this py file is creating from the maker file because in the maker file let me again show you so this is the same file like first we have the codes and the message uh where is that file yeah first we have the codes and the message then we have the revision id devices creation rate revision addresses creation date then we have some imports then we have some imports then we have like this revision number then we have upgraded downgrade then we have the upgraded and downgrade so if we want to generate some python code automatically from some template then we have to use the dot maker file so that's why they have like they have provided the maker file because they are like creating these versions these versions like using this these files okay so let me close this okay so now we have like def upgrade and def def pass so dev upgrade means like here we i have to mention everything like so using the olympic module so alembic is like imported as op so we will use op dot create a table because upgrade means let's create the table downgrade means upgrade means let's suppose we have the four columns up and we are upgrading it and we are like adding one more column so then we will run the upgrade function but let's put now we have the five column we want the previous version then we will use the downgrade function so manually means we have to define everything so up op dot create create table then create table name users name then the column name so everything we have to define manually here so one of one approach is this so we are so this is very like not a like a useful approach because like otherwise why why we are using the like alembic if we have to do everything like manually then what is the use of alembic so let me close that and let me remove that version remove four so now we don't have anything so i have removed that uh like a version as well so one of one one approach is this all right okay but what is the other approach so other approaches automatically generate everything for us so when because when we see see our two function upgrade and the download but upgrade the download function both are empty so it is not reading the data so we have to auto so we want automatically it will read our metadata and automatically it will read automatically it will create statements all right so if we want to read the metadata so first we have to update the enviro file so here is the target metadata so they have mentioned if you want to auto generate like if you want to auto generate the scripts then we have to update this line otherwise if you are doing manually then leave this field as is so we want to update this file so how to read the metadata base dot meta data so we we read the metadata like in this form because if you look at this file um nano dot main road view so how we adding the meta data base dot meta data and then we are using the create all command but rather than the create all command we are using the alembic so we have we just need the metadata rest everything will be done by the alembic so how to read the metadata using the base dot metadata but we have to import the base as well so we are reporting the base as well from models import base so here we have to type from models import base yeah that's it so this is run so if you this so we have to update our target metadata into the enviro file so this step is done now we can issue the same command with some extra parameter that is minus minus auto generate so let's run the same command alembic revision now we have to some extra parameter because we are auto generating the script so if we dislike auto generate it it automatically read the env dot piva file and within the enviro file it will read the metadata and after reading the metadata only it will know okay what all the tables name what all the columns name it has so metadata then give some message let's give some messages initial so i have like run this command let me run this command okay can't locate a revision identified by okay why this error is like a came because like uh let me show you so we have to update a few things more a nano alembic.ina file so in the alambic.ini file nothing in the lamborghini in the migrations unless nano dot enviro file nothing in the environment will file nano script dot video file and nothing in the script.pr file cd versions ls so [Music] so like uh what basically like uh happen is like uh because we earlier issue this command like alembic revision and uh minus m initial without the auto generate so it created like uh some some version like we have manually delete that version right so but it created some version all right so what will happen is like it created some version and that's why like this issue scheme can't locate revision uh identified by this so let me copy this and let me google it so if there is anything because i am unable to find this could not locate your revision identified so it is a stack overflow delete your alembic version so we have to delete the alembic version as well you delete the migration directory but the version has been saved in the database so you have to delete the version info into the database run okay because we have set up the we have to delete this one minute let me i have deleted that yeah so now it automatically like recognize like a few things so if i go to the migrations ls cd versions ls now we have the version nano 3 so you could see like upgrade and the downgrade like uh functions so why like only this will recognize because our table structure already exists into the database items and the users the only structure we are adding like added is is column so that's why like it is like recognized only is admin column right and if we want to like uh go back to the previous call like previous version like then we have the downgrade then we can like drop the is as well so because we we have the tables already with us so that's why like it created like the script but now the scripts are getting automatically generated all right so but uh before that uh let me remove that ls rm36 and ls remove pi cache rm minus r so we don't have anything and from here if we refresh it so automatically here now alembic dot one version a table get created so if you click on this table so so let me delete this and let me delete the items as well let me delete the user table as well all right so we have deleted everything and let me refresh everything so if you go to the tables we don't have anything here okay and nothing is there yeah so hope everything is okay one minute let me cross check yeah everything is looks okay let me remove that as well yeah so cd versions let's minus a we have some hidden files here so we have to remove that so how to remove the hidden files so give me a few minutes ls minus l is the command and the file path but we don't able to see the file name so rm minus rfv then dot slash i am refusing to remove dot or [Music] not sure i think this will create the issue anyhow let me because we have everything now ready so let me rerun the command alembic revision minus minus auto generate minus m initial yeah so cd migrations ls cd versions ls nano 9c so now we could see automatic like our script has been generated like otherwise we have to write op dot create table user then column everything we have to write now everything has been generated for us but only the script has been generated but here we still like we don't have anything so we don't have steel anything so in the tables we only have the alembic version we don't have the actual table at the user table so what what is the next step so next step is to apply this script so if you look uh my documentation uh so what so if you like there are two processes whether you manually write the script or whether you automatically generate the script so what option we will prefer we will automatically generate the script so our script has been now automatically generated so we have to create the table so create table means we have to upgrade it if we have to like delete the tables then we have to use the downgrade functions now we have to perform the upgrade functions so what we will do alembic upgrade and then head adam is the particular version number that is the current version number whatever is the latest version number you can give the head otherwise you can mention the version number as well that is like this this version number all right so you can give this version number as well but i am giving head so adam is automatically it will like pick the latest version so if i click enter so it will like created the table so let me refresh it and now i am able to see the items table and the user table so let's suppose now my requirement is so in my user table we have the is admin but my now requirement came is i i don't want this this is admin table is admin column so what i will do is i will remove this admin table all right i have removed this admin table now i will like region rerun this command revision minus minus auto generate auto generatement like a generator new script minus m remove is admin column so give some useful message auto generate yeah oh i am running it wrong because i have to run here because i am in the virtual environment alembic revision minus minus auto generate minus m remove is admin column so automatically it detected that this column has been removed right and hopefully it is created the script as well so now we have two scripts one is the old script one is the like a new script one is the initial script one is this new script remove this column so if i look at this column for a so now in the upgrade so upgrade means we are dropping the column and if we want to go back to that then it means we have to add adding that column back again so now we are like dropping the column so if we want to drop the column we have to apply this script [Music] so how to apply the script alembic we have to run the upgrade function because the upgrade has the drop column then head head means the latest so now it has been done so let's check whether our column has been deleted or not so in the users column now we able to see only four columns each admin column has been deleted so this is the main advantage for the alembic so now we don't need this so we can comment this because we are using alembic migrations so now we don't need this line anymore so we have commented this so this is like the alembic uh how to use that so what are the various commands what are the like various types like so where we have to update when we have to update env file if we have to run the manually then we don't have to update the env file if we use the auto generated then we have to update the env file when we have to update the alembic road ini file so everything like i have like explained uh in this so along with that like alembic upgrade head so rather than the head you can use like alembic upgrade plus one plus two plus three like similarly lambda upgrade minus one minus two minus three minus one minus previous one per version minus like previous two previous minus three means previous to previous to previous version all right so you can like uh use uh this command as well but one thing i note to that so there is a note so auto generate is not intended to be perfect so though you are generating the script so let me show you cd migrations cd versions ls nano for a though you are like generating the script automatically that's using the auto generate features but it is always recommended that you should before actually applying the scripts because it is just like it is generated the skill but to actually apply the script we are using the alembic upgrade ahead upgrade means like we are calling the upgrade function or either if we want to like uh downgrade it then we can like downgrade the function like we have to downgrade olympic downgrade but it is always recommended to cross verify this like uh this script because alembic is not 100 perfect even though from like from time to time from various version to version a few improvement is like a doing but it still has like a few drawbacks even like in the comments they have mentioned commands auto generated by alembic please adjust so you have to cross verify those command rather than blindly issue those command all right so this is the main thing which we have to like cover because there are few things which alembic did not like uh uh read from the metadata write the table name if i change the table name then it will not read that so you have to manually like uh write that command so you have to manually adjust the visual if we not use the auto generate option then we have to write everything but if you generate if you use the auto generate option most of the thing will be ready for us so 99 of the thing ready for us so 99 percent of things you don't need any changes but still you have to verify that if there is any change so you have to do that manually okay so yeah so that's the end of the video uh thank you

Channel: Sumanshu Nankana

Views: 6,132

Keywords: FastAPI, Python

Id: gISf9AWAS7k

Length: 45min 35sec (2735 seconds)

Published: Fri Sep 03 2021