Django.fun

Adding field to Django model when the column already exists in the database

I have a model in Django which represents a MySQL table that has some extra columns. I want to add a field to the model for one of these extra columns but I'm not sure how best to do it.

Let's say the person table has an age column. My model looks like:

class Person(models.Model):
    name = models.CharField(min_length=200)

If I add an age field like:

    age = models.IntegerField(db_column="age")

then when I migrate I get an error about "Duplicate column name 'age'" because it tries to create it. Is there a way around this?

What I've tried:

  1. Add the field with a new column and make a migration for that:

        age = models.IntegerField(db_column="age_2")
    
  2. Create a manual data migration to copy data from original column to new one:
    UPDATE person SET age_2 = age;

  3. Create a manual migration to drop the original column:
    ALTER TABLE person DROP COLUMN age;

  4. Create a manual migration to rename the new column:
    ALTER TABLE person CHANGE COLUMN age_2 age INT(11) NOT NULL;

  5. On the model change it to use the age column (db_column="age") and make an automatic migration.

This works on my existing database, but when I run my tests, and it applies all the migrations to create a test database, it complains about Unknown column 'age' in 'field list' (with no indication which migration is causing this).

I found a solution but it seems so laborious and hacky that I really hope there's a better way. But, this works for both the code with the existing legacy database (that already has an age column) and when running the tests that start by creating a fresh database.

  1. Add the field to the model, with the column name we want to end up with (age):

    class Person(models.Model):
        age = models.IntegerField(db_column="age", null=True)
    

    Do ./manage.py makemigrations and edit the migration that was generated, changing the db_column from age to age_2:

    # ...
    
    operations = [
        migrations.AddField(
            model_name="person",
            name="age",
            field=models.IntegerField(
                db_column="age_2",  # Changed this from age to age_2
                null=True,
            ),
        ),
    ]
    
  2. Create a manual migration (./manage.py makemigrations --empty appname) and edit it to create a migration that will copy data from age to age_2 only if the age column already exists:

    from django.db import migrations
    
    def copy_age(apps, schema_editor):
        with schema_editor.connection.cursor() as cursor:
            cursor.execute("SHOW COLUMNS FROM `person` LIKE 'age';")
    
            if cursor.fetchone() is not None:
                cursor.execute("UPDATE person SET age_2 = age")
    
    
    class Migration(migrations.Migration):
    
        dependencies = [
            ("api", "0017_person_age_with_new_column"),
        ]
    
        operations = [migrations.RunPython(copy_age)]
    
  3. Create another manual migration and edit it to create a migration that will drop the original age column only if it already exists:

    from django.db import migrations
    
    def drop_age(apps, schema_editor):
        with schema_editor.connection.cursor() as cursor:
            cursor.execute("SHOW COLUMNS FROM `person` LIKE 'age';")
    
            if cursor.fetchone() is not None:
                cursor.execute("ALTER TABLE person DROP COLUMN age")
    
    def add_age(apps, schema_editor):
        "For reverse migrations"
        with schema_editor.connection.cursor() as cursor:
            cursor.execute("ALTER TABLE person ADD COLUMN age INT(11) DEFAULT NULL")
    
    
    class Migration(migrations.Migration):
    
        dependencies = [
            ("api", "0018_copy_age_column_to_age_2"),
        ]
    
        operations = [migrations.RunPython(drop_age, add_age)]
    
  4. Create one more manual migration to change age_2 to age, and tell Django that we really are using the age column for this field:

    from django.db import migrations, models
    
    class Migration(migrations.Migration):
    
        dependencies = [
            ("api", "0019_drop_age_column"),
        ]
    
        operations = [
            migrations.RunSQL(
                "ALTER TABLE person CHANGE COLUMN age_2 age INT(11) DEFAULT NULL",
                state_operations=[
                    migrations.AlterField(
                        model_name="person",
                        name="age",
                        field=models.IntegerField(
                            db_column="age",
                            null=True,
                        ),
                    ),
                ],
            )
        ]
    

I think the better way you're after is:

  1. Add the age field to the Django model;
  2. Generate the migration with makemigrations;
  3. Instead of running the migration normally, run migrate with the --fake parameter.

This tells Django that the migration has already been applied to the database (as the column already exists) and so it should just mark the migration as having been applied.