Adding a through table to existing M2M fields

(you cannot alter to or from M2M fields, or add or remove through= on M2M fields)

Perhaps you’ve experienced this… You have a ManyToManyField field in a model and want to update to using a through table to add additional fields. You update your models.py, make migrations, all looks good, then you hit this error when attempting to migrate:

you cannot alter to or from M2M fields, or add or remove through= on M2M fields

All seems lost, but there is a simple fix that can get you the modeling you want and preserve your data.
In this post we’ll explore this scenario and show how this can be resolved with step by step examples.

Let’s begin with our django setup. For the examples below we are using django 3.1.4 with a sqlite3 database. Our app is named ‘example’ and we’re starting with a simple models.py file that contains a model for Player and a model for Team. The Team model contains a ManyToMany field linking to Player. It looks like this:

from django.db import models


class Player(models.Model):
    name = models.CharField(max_length=16)

    def __str__(self):
        return self.name


class Team(models.Model):
    name = models.CharField(max_length=16)
    players = models.ManyToManyField(Player)

    def __str__(self):
        return self.name

We want to include a few more details and create a Roster model that links to Player and Team, but also includes a position and a game date. We want something that looks like this:

from django.db import models


class Player(models.Model):
    name = models.CharField(max_length=16)

    def __str__(self):
        return self.name


class Team(models.Model):
    name = models.CharField(max_length=16)
    players = models.ManyToManyField(Player, through='Roster')

    def __str__(self):
        return self.name


class Roster(models.Model):
    player = models.ForeignKey(Player, on_delete=models.CASCADE)
    team = models.ForeignKey(Team, on_delete=models.CASCADE)
    position = models.CharField(max_length=16, null=True)
    game_day = models.DateTimeField(auto_now_add=True)

This looks like it should be fine and we run the make migrations command. That also seems fine, but when run make migrate command we get this error:

ValueError: Cannot alter field example.Team.players into example.Team.players - they are not compatible types (you cannot alter to or from M2M fields, or add or remove through= on M2M fields)

Why is this happening and what is the resolution?

The clues are in the migration files. The second migration file includes a command to create a table, when in actuality we want to alter an existing table.

When we used the ManyToManyField on the Team model, django actually created a through table for us, so this already exists in the database. The default naming convention in django is <app_name>_<model_name> and the default naming for the through table django automatically created is <app_name>_<model_name>_<m2m_field_name>.

So in our database, while we have only created two models in our models.py (in the initial migration), three tables were created in the database:
example_player, example_team, and example_team_players. Here’s a screenshot of the database:

database tables after initial migration

For reference, let’s look at the data in all three tables. This is data as entered before attempting to add the Roster model.

First the Player table. We have four rows of player names. Second we have the Team table, containing two team names. Lastly we have the auto-generated through table that links the team_id and player_id.

Player model (example_player table)

Team model (example_team table)

auto-created through table (example_team_players table)

Now that we understand the data, let’s get back to the second migration file. It contains the following:

from django.db import migrations, models
import django.db.models.deletion


class Migration(migrations.Migration):

    dependencies = [
        ('example', '0001_initial'),
    ]

    operations = [
        migrations.CreateModel(
            name='Roster',
            fields=[
                ('id', models.AutoField(auto_created=True, primary_key=True, serialize=False, verbose_name='ID')),
                ('position', models.CharField(max_length=16)),
                ('game_day', models.DateTimeField(auto_now_add=True)),
                ('player', models.ForeignKey(on_delete=django.db.models.deletion.CASCADE, to='example.player')),
                ('team', models.ForeignKey(on_delete=django.db.models.deletion.CASCADE, to='example.team')),
            ],
        ),
        migrations.AlterField(
            model_name='team',
            name='players',
            field=models.ManyToManyField(through='example.Roster', to='example.Player'),
        ),
    ]

This migration will try to create a new table, which is actually not what we want as the through table already exists and contains data we want to preserve. Luckily django provides a specialized operation called SeparateDatabaseAndState which allows us alter the state and the database separately. The django docs provide a good example of this: https://docs.djangoproject.com/en/3.1/howto/writing-migrations/#changing-a-manytomanyfield-to-use-a-through-model

Utilizing this, let’s alter the second (not-yet applied) migration file.

We add a state_operations list that defines the through table we want (as defined in the Roster model), updates the table name to example_roster, and adds the through reference to the players field in the Team model.

In the operations list we add the fields desired to the Roster model.

Here’s what the updated migration file looks like:

from django.db import migrations, models
import django.db.models.deletion


class Migration(migrations.Migration):

    dependencies = [
        ('example', '0001_initial'),
    ]

    state_operations = [
        migrations.CreateModel(
            name='Roster',
            fields=[
                ('id', models.AutoField(auto_created=True, primary_key=True, serialize=False, verbose_name='ID')),
                ('position', models.CharField(max_length=16, null=True)),
                ('game_day', models.DateTimeField(auto_now_add=True)),
                ('player', models.ForeignKey(on_delete=django.db.models.deletion.CASCADE, to='example.player')),
                ('team', models.ForeignKey(on_delete=django.db.models.deletion.CASCADE, to='example.team')),
            ],
        ),
        migrations.AlterModelTable(
            name='roster',
            table='example_team_players',
        ),
        migrations.AlterField(
            model_name='team',
            name='players',
            field=models.ManyToManyField(through='example.Roster', to='example.Player'),
        ),
    ]

    operations = [
        migrations.SeparateDatabaseAndState(state_operations=state_operations),
        migrations.AddField(
            model_name='Roster',
            name='position',
            field=models.CharField(max_length=16, null=True),
        ),
        migrations.AddField(
            model_name='Roster',
            name='game_day',
            field=models.DateTimeField(auto_now_add=True),
        ),
        migrations.AlterModelTable(
            name='roster',
            table=None,
        ),
    ]

Now when we run the migrations again, everything works successfully.

./manage.py migrate example
Operations to perform:
  Apply all migrations: example
Running migrations:
  Applying example.0002_auto_20201223_0441... OK

Let’s look at our database once more to see what changed.

database tables after second migration

player table after second migration

team table after second migration

roster table after second migration

We can see that the player and team tables remained the same and the example_team_players table has been renamed to example_roster. Also, the position and game_day columns are added and all previous data is preserved.

And there you have it! Hopefully This provides some insight into django’s use of M2M relations and helps you avoid migration errors. Happy django-ing!

References:

Migration Operations | Django documentation | Django

Migration Operations | Django documentation | Django

Digging Deeper Into Django Migrations - Real Python