Database migrations matter! Get up and running with Alembic + sqlmodel

welcome back to Pyrex YouTube and today a quick video on how to get up and running with alembic in SQL model and Olympic if you don't know is a database migration system so databases orems it always starts out nice with single table and create the table from scratch but once you start to have a bigger application and real data you really want to be careful about how you manage your database and much like git for a version control of your code you can use a version control system for database as well so you can keep track of all the changes you're making and in columns removing columns renaming columns all these kind of SQL operations you do in your database you can keep that keep track of that inversion files and manage your database schema Evolution router in a more safe way so without risking corrupting your data so let's look at an example of using alendrick with SQL model in this video I got a virtual environment set up in this Olympic directory I'm going to pip install SQL model in alembic and I took a little bit of starter code already from the um SQL model documentation page which um if you're new to SQL model actually is an orm so for a long time we had SQL Alchemy then pedentic came along for the type hints and validation and Sebastian Ramirez immersed them together and now we have SQL model that you can use um yeah that kind of Leverage The Power of both SQL Alchemy and pedentic and the documentation has a first example of a hero class that you can find with table equals true and that then we'll create hero table in the database and here test name secret name and age and what we maybe can do is just start with name and use alembic to add those columns one by one let's actually put this in a models.pi import SQL model take that from here and the optional actually optional you would need that for python 3.8 or the 3.9 upward or 310 probably or maybe 310. I think it's already in three nine you can do you can use the pipe symbol for this and let's keep this app dot pi to quickly test out if it works so I'm actually not going to run this we're going to manage the migration process with alembic but I do want to have a little bit of code here to see if it can write a hero to the database so it's actually do that I will do that in a bit let's focus first on the Lemak part so alembic we can kick off alembic with alembic inits in the name of a boulder I usually call it migrations and what I now got apart from my modules is new migrations directory with a readme m.pi a macro file and versions all the migration files are going to be written to the version directory and an nth in alembic.ini and M um we need to do some configuration to to tell alembic how to connect to the database now you can do that in alembic.ini but I do find that a bit disturbing to hard code a database postgres user and password Here so I'm not going to do it here I'm going to do it in m.pi and actually we want to load in the database from an environment variable and let's use a postgres database so I can an environment variable so I'm going to make a dot m file going to say alembic demo as the name of the database locally I run postgres with just usernet user postgres and password password runs a local host and on Port 5432 and in this end file which I should get ignore as well I can set one or more environment variables and I pip install python decouple to load in that environment file and it also will need cycle GE binary site oops binary can use for local developments and production it's a much preferred or recommended to use cycle G so without the dash binary okay um so back to my m.pi I can now do from the couple import Copic and then set database URL equals config database URL and that will load it in from the dot amp file and now instead of setting that hard coding that database URL in alembic.ini I can do a config set main option refer back to the alembic key here so SQL community.url and set that to the database URL we set here and which again was loaded from the environment so that's the database connection part now with alemic you can run it as two ways you can write your migration files manually and then sync into the database or you can have alembic Watcher models so what we have in models.pi and auto generate them and I much recommend that way of doing things it's also how Django does it and works really well and there's already there already some useful commands here to enable that so you can add your model's metadata object here for auto generate support and then you also need to import your models right so my models currently live in models.pi so I can do from Models import hero and I will also need to get the metadata objects which should be in SQL model metadata and that comes from SQL model import SQL so that's the metadata and it goes into Target metadata which then is used here in one migrations offline non-migrations online which you don't have to change anything um okay so that's the import that's the metadata we have set the database and we need one more change and that's in the macro file so Macau um this is the template Olympic is going to use every time it makes a new migration file and there's only SQL Alchemy imported so we also need to import SQL model because it's going to reference SQL model stuff here and if you don't have this import then that will fill all right let's um see if that works and we can use the alembic revision command for that and that's created a retrition file well I'm actually not happy because I didn't give it a name and nothing happens so normally every migration file comes with an upgrade and downgrade function and that should have the um the steps that the Olympic will follow to migrate the model changes into the database and that's because I didn't run it the right way so let me remove this migration run this again but now with auto generate and also give it a meaningful message well this is really just the um yeah I guess the hero table right here on table okay that's an error on my part because in NF I should have done postgres ql that's what happens when you copy things from a Django project where this works but for SQL Alchemy and SQL model I need to call this postgres SQL and no module cycle G2 search out I uh installed the wrong cycle to driver okay and I forgot to make the database as well so create DB it's a postgres command that allows you to create databases and now it should work so we got a new migration file notice that the file is now better named because I use the minus m meaningful message and let's see what's in there here's the SQL model import I put in a macro file and the upgrade and downgrade now have meaningful things so it will create a hero table with two columns that's the primary key constraint and in a downgrade that's kind of the reverse right if we would roll back this migration that's those are the steps to undo it so in that case it will just drop the table so that's cool so how do I now get it into the database I can do alembic upgrade head which basically says go to the latest revision and now the database should have those changes right so it has a hero table also has a proficient table select all from which keeps track of the hash of the latest revision we we migrated very similar to git all right let's add a column secret name and make a new migration and it worked and now we see in the new migration file that we added a column and the reverse is to drop the column again run alembic upgrade ahead to apply the change to the database and then if I do uh backslash D on hero we have the new column foreign yeah so that's basically all you need to get started I think one two more things uh we could verify if we can work with this model so we can do hero name equals Julian secret name dead session at hero session commit and I need to import that notice here I'm still using the sqlite database that's fine for now um if you want to work with the same database you would have to do that decouple step I did before so let's run that oh yeah of course because I well let's actually use the same database I think that's easier so um okay going back to my database and effectively we have the Julian row being written to the database perfect and one final thing here in the conflict um we do the models import as well so now we have model Imports here and we have model Imports here which is a bit unfortunate ideally we want to do that in one place so what I read in an issue on SQL model I believe is that you can also do from app import SQL model and as you know it Imports um it will actually parse the whole file so it then also comes across this model's import and then we um so we skip this one the SQL model SQL model object we rather do that here with the advantage of we still get this but as we're parsing app and that could be anything right whatever you call that module it can also be DB but in this case I call it app.pi not only do we import the SQL model but you're also doing the model Imports so then I can just do the model Imports in one place which is a bit more dry or don't repeat yourself so maybe to confirm that that works let's do one final migration also to um drive this home this I changed to a more modern typing syntax so we have an optional H we can add so we changed our models again so we can make another Auto generate migration oh and I'm actually uh because I'm importing app in the envelope pi business running so let's protect this by calling it um only if we call the script directly so upon import this name equals Main is not hitting and we have another video on that um so with when we import this doesn't hit so it doesn't run this that's what I want okay so yet another migration file again they all go into this um versions subdirectory and again we have an add column of H and deeper verse is to remove that column so then we do to Commit This to the database and then big upgrade head and that worked and now if you check the heroes table we can we have the new h field as well and as it's optional that's just set to null for Julian one final thing is um how friendly or not lambi can be so let's make one more column um say we call it um Thursday and it's going to be a date so we need to import that and as it's not defaulted or typed to or non-optional um it's a required field so what happens with the existing data right well we can make another migration that's fine it's going to generated this file again add column but now it says nullable equals false right it reverses to drop the column what happens if I apply this migration it actually errors out because the column birthday of relation hero contains null values right so um there's already a role in this database and yeah now it doesn't know what to put there right and that's where I definitely like Django's migration system better because at this point it would now ask you the question like what do you do you want to go back to your migration file and edit it or do we want to provide a default value and I often say well I will give a default value and then it will move on here here you could actually a crash right so you have to kind of figure it out that probably means that you will have to write some SQL in your migration file so again to show you how that is only because there's already data in the table we can do trunk a table Arrow so now that table is empty and then it should work because there are no rows in that database where it needs to make a decision right so yeah again I would much prefer getting a prompt here to provide the default if if that's possible let me know um let me know in the comments uh I would love to hear that maybe not a very short video but definitely um something I needed the other day to start using alembic in combination with SQL model so if you're in that position I hope this video helps you get set up if you have any feedback or questions comment below and I will see you in the next video

Channel: Pybites

Views: 5,384

Keywords: Python, databases, ORMs, sqlmodel, SQLAlchemy, postgres, database migrations

Id: gekC1ESLxPs

Length: 19min 53sec (1193 seconds)

Published: Tue Apr 11 2023