Seamless Transition: How I Converted an Existing MySQL Database to be Fully with Daniel Ramas

Carbon

Speaker 1: Hello, thank you all for attending my talk. Today I'll be sharing an experience I had with a project I worked on earlier this year where I converted an existing MySQL database to be fully managed by Django's migration framework. Uh a little bit about myself. My name is Daniel Ramos. I am from Los Angeles, California. I've been a Django and view developer for about four years now, and I currently work for a company called Horizon Realty Advisors. Horizon is a Seattle-based company that's a market leader and owner and operator of multifamily housing with a portfolio spanning uh 57 locations across 14 states And this includes both student and conventional housing.

Speaker 1: And this will also be my third DjangoCon attended. I attended 2022 online and 2023 in person. Uh entering my current role, I was more of a front-end and backend developer with limited exposure to the database side of things. And in August 2022, I was tasked with converting a Laravel backend web application to Django. And getting the application up and running was pretty easy. I discovered Inspect DB, which was a super useful utility to create the model files. But one of the biggest roadblocks I had came whenever I needed to start running migrations. I was getting errors about tables already existing.

Speaker 1: fields being named wrong, incorrect column types, orphaned rows, and that's just to name a few. And I'll be first to admit that I didn't spend a lot of time reading document Prior to this project, the only knowledge of migrations I had was, you know, if you run make migrations, it'll create some file, and then if you run migrate, it'll execute. that script. In order to convert the existing MySQL database, I had no choice but to get familiar with what was going on under the hood. So how does Django create and generate migration files? Uh this is Django 's description of the migration system. From the Django Project. com website, and it says

Speaker 1: migrations are Django's way of propagating changes you make to your models, adding fields, deleting a model, etc. into your database schema. So how does Django create and generate migrations files? Since I never really read the documentation, for some reason I assumed that Django could Look inside the database and generate a migration file off that. So to my surprise, Django doesn't actually look at the database when generating migrations. It looks at two things the models. py file and the migration history See the migration history allows for the migration framework to build a current state of what the database should be looking like, compare it against the models. py file and generate a migration file based off that.

Speaker 1: So if you look at this uh diagram that I made, when you run make migrations, um it'll look at the migrations folder, compare it against the state of models. py, create new migrations, and then decide what to execute based on the migrations that have been saved in the Django. migrations table. So let's take a look at an empty migration file to get familiar with what's going on inside The empty migration file contains a class named a migration, and typically you'll see two class attributes already there, dependencies and operations. Operations is a list of uh actions to be ran on the database.

Speaker 1: And aside from the stuff that Django generates, we have the ability to run custom Python scripts. Uh and we can do that using Run Python. So for the forward operation of RunPython It accepts uh quite a few arguments, but the two that I've seen most common are apps and schema editor, and that's the same for the reverse function. And apps is a Django application registry that stores information of the installed applications. And by far the most common use case of apps object is using the get model. method which is a method that returns a specific model at the current state of the migration history.

Speaker 1: This is something that gave me a ton of trouble at first because I assumed that the model would be synced with the models. py and I would even try to directly import the models into a migration file. And the fact that we have to use apps. get model makes a ton of sense because Uh if let's say you alter a field on a model after creating an empty migration, that field doesn't exist yet, so it may not uh exist in this state of the history. And uh schema editor, the other uh argument to the forward and reverse functions It can be used to alter database schema like creating fields, creating tables, etc. but I've personally never had to use this. I think Django's migration framework is robust enough to handle a lot of those changes that you might want to use

Speaker 1: schema editor for. Uh the other common uh migration class that you could use is Run SQL and RunSQL accepts strings of SQL commands to run on the run on the database. Of course be careful when using this. Make sure you sanitize your inputs and Don't uh make yourself uh susceptible to uh sequel injection in this area So uh this brings me to dependencies. Uh and one pattern you may have noticed is that migrations have a dependency to the previous migration that is applied before it.

Speaker 1: This is how uh uh Django links migrations and is able to create a tree to uh describe what the migration history looks like. By far the most common way I've seen other migrations be dependencies other than the previous migration in the same app is when you create foreign queue relationships between models. So if you look at this code snippet, the employee model is in the core app and we're creating a migration and a hypothetical app called sales. And in order to create the foreign key relationship, you need to add core as a dependency in this migration. So now that we're familiar with migration files, let's go through a example of how to sync an existing data.

Speaker 1: base to Django. So uh in this project I'll be using Docker Compose for my development environment but um using virtual and uh virtual environment is fine too. So right now I have an e empty Django project with the basic migrations already ran and my database is existing. I got this off the MySQL website. So uh using my database browser, we can see here that We have an existing database here. It's called Employees, where it has departments, employees, salaries, titles, things like that as full uh populated with a bunch of dummy data. And in settings.

Speaker 1: py I've created uh the connection here as the default database to that um to this to this database So the first thing you want to do is set the default auto field setting. And if we scroll down here, it should be at the bottom. Uh go ahead and take a look at your database and take note of the type of the primary key fields. Uh doing this will create consistency when we decide to create more tables in the future. So for this project, I took a look at the database and the primary keys are primarily integers. So we could change this to be auto field, which is a subclass of integer fields.

Speaker 1: So uh next thing we want to do is use Inspect DB to generate the models. So if we stop the development server and we run inspect db and we output it to a file in the core uh folder and we call it models. py. It will generate a models. py file based on the database schema it was able to detect And so for here, uh because I've already ran Django migrations, we can actually delete uh quite a few of these. I'll delete them right now

Speaker 1: Okay, so uh these are gonna be the uh models from the example database And next thing we want to do is we want to flip manage equals false to manage equals true because we want Django to be managing these uh database tables Okay, so next thing we're gonna do is we're gonna add many to many relationships As we can tell, we have a couple mapping tables here, department employee and department manager. What we could do is for um to make things easier whenever we're creating uh queries, we can create many to menu fields here and we let's just put them on the

Speaker 1: departments model And we're gonna explicitly define this related name And we'll call this one managers. And we'll call this related name department manager Next thing we're gonna do is we're gonna create initial migration files. So if we run python manage. py Make migrations on the core app. We will create an initial migration file

Speaker 1: Uh okay, so what we can do next is we can actually apply these migrations, but if we were to try to apply these migrations We will get an error for pretty much saying like some of these tables already exist, which they do because this is an existing MySQL database. Well we need to do to bypass that because we do need this uh these migrations to be in the migration history, we can just run python manage. py migrate fake and essentially this will what this will do is it will fake all migrations that need to be applied. There's also a command Manage. py migrate

Speaker 1: fake initial and we can use this if we have multiple migrations we need to run, but only the first initial migration need to be uh fake. Uh so the last thing I wanted to discuss was uh foreign keys that are enforced on uh backend application rather than on the database. Um for um for myself whenever I was converting to PHP application into Django, I was able to see on the source code there were certain models that had foreign key fields that were enforced on the back end but not on the database And therefore whenever I ran inspect DB, I wasn't able to find uh certain fields to be a foreign key when it was supposed to be.

Speaker 1: And for this example If we look at uh employee info, uh it's safe to assume that this employee number field should be a foreign key to employees. And so if we just change this, um since this is a primary key, it should be a one-to-one field. And we can point it to the employees model and define the on delete behavior And then define the D B column name, which is employee number. Okay. And if we create the migrations file using make migrations

Speaker 1: We can go ahead and run this migration since we want to have this field be indexed on the database, but we'll notice something interesting might happen. So we get an error here. Cannot add or update a child row. A foreign key constraint fails Uh foreign key employee number references employees and so uh pretty much what this means is that uh There is an employee info object that has an employee number that doesn't exist in the employees database. And Um this can be a result of you know mismanaging the on-delete behaviors.

Speaker 1: Um it could be a number of things, but If we do want to have this foreign key field indexed on the database and we want to have this migration RAND, what we could do is we can actually create a Custom migration. We can have it clean the employee info table and remove any employee numbers that don't point to a existing employee. And so if I delete this uh migration uh I will Uh do make migrations core and create an empty migration

Speaker 1: Um I'm gonna use uh run Python to do the uh cleaning of the columns And then for the reverse function of another one. And uh here we can uh define the functions. And then we can do something like Ploe equals apps dot get model.

Speaker 1: And we can get the employee info. Uh and then we can create an ORM query like So all this query is doing is it's getting any instance That doesn't have an employee number that points to the employee number on the employee table. Um this will be a one-way uh operation so there's gonna be no way to get this uh get those deleter rows back and so uh since this is uh Operation that can't be reversed.

Speaker 1: We actually don't really need to have a reverse uh function. We can just do migrations run Python no op. And so now what this will do is this will clean that column, the employee number column in the employee info table. And so If we run migrate and then if we do make migrations again So we could see here that uh we're gonna be converting this to a one-to-one field. And if we run uh migrate

Speaker 1: It should apply just fine. Uh so this last thing I wanted to go over was a specific scenario where Let's say we have this uh model blog post. I just uh created the migration for it and so now it'll be in the migration history. But let's say uh we get a field called employee underscore ID although the other fields were called employee number but let's just say this one's employee ID and we wanted to convert this to be a foreign key So if we do foreign key and then we point it to employees and we define the on-delete behavior And uh let's

Speaker 1: uh let's provide it a default so it doesn't give us an error. Um If we do this and we just want to call it employee and we create the migration If you look at the migration that created, it actually deletes the field employee ID and creates a new one. And this is something to be uh very careful over. You would uh be essentially deleting that column and losing all that information. Uh my way of fixing this would be to change these to be an alter field rather than remove an add field. So we can just go ahead and remove this

Speaker 1: remove field and change this to be an alter field Uh and then we will just uh change this to the field name that we had, so employee ID. Um

Speaker 2: hey everybody, this is Daniel.

Speaker 1: I'm actually in the middle of editing this video and I wanted to make a correction to this last section To change the employee ID field into a foreign key, you're actually going to want to include uh this db column uh argument just to map it to the employee id field otherwise it would be called something like employee id underscore id And to just change employee ID to be employee, you're gonna want to use rename field and rename employee ID to employee. Now if we run these migrations.

Speaker 1: Uh the migrations will apply and if we take a look at the database we can see that the Employee ID is now a foreign key to employee number and there's no issue with the column name. So to summarize, uh The steps you need to take to convert an existing database to be fully managed by Django's migration framework is number one, you need to set the default auto field to the primary key types of the primary keys of the existing existing database. Then you can use PythonManage. py inspectDB to create the models. py file. And then after that add many to many fields if there are any many to many fields you want to include. Then you need to fake those migrations to have those models and migrations be in the migration history.

Speaker 1: And then optionally you can create foreign key relationships for back and enforce foreign keys. Uh thank you all for attending my talk today and I hope you all have a great rest of your week. Thanks. And I 'll be And then what will you want on them?