django.db.utils.DataError: invalid input syntax for integer, migration generated with older Django version not processed correctly during db creation

After upgrading our Django project from Django 1.11.8 to 2.2.6, along with pytest (from 2.9.2 to 5.2.0) and pytest-django (3.1.2 to 3.5.1), we started getting the following error in some of our test cases:

Traceback (most recent call last):
  File "dev/psngr-django/myapp/myapp/apps/premium/serializers.py", line 1638, in create
    OrderItem.objects.bulk_create(order_list)
  File ".pyenv/versions/3.6.8/envs/venv368/lib/python3.6/site-packages/django/db/models/manager.py", line 82, in manager_method
    return getattr(self.get_queryset(), name)(*args, **kwargs)
  File ".pyenv/versions/3.6.8/envs/venv368/lib/python3.6/site-packages/django/db/models/query.py", line 474, in bulk_create
    ids = self._batched_insert(objs_without_pk, fields, batch_size, ignore_conflicts=ignore_conflicts)
  File ".pyenv/versions/3.6.8/envs/venv368/lib/python3.6/site-packages/django/db/models/query.py", line 1204, in _batched_insert
    ignore_conflicts=ignore_conflicts,
  File ".pyenv/versions/3.6.8/envs/venv368/lib/python3.6/site-packages/django/db/models/query.py", line 1186, in _insert
    return query.get_compiler(using=using).execute_sql(return_id)
  File ".pyenv/versions/3.6.8/envs/venv368/lib/python3.6/site-packages/django/db/models/sql/compiler.py", line 1335, in execute_sql
    cursor.execute(sql, params)
  File ".pyenv/versions/3.6.8/envs/venv368/lib/python3.6/site-packages/django/db/backends/utils.py", line 67, in execute
    return self._execute_with_wrappers(sql, params, many=False, executor=self._execute)
  File ".pyenv/versions/3.6.8/envs/venv368/lib/python3.6/site-packages/django/db/backends/utils.py", line 76, in _execute_with_wrappers
    return executor(sql, params, many, context)
  File ".pyenv/versions/3.6.8/envs/venv368/lib/python3.6/site-packages/django/db/backends/utils.py", line 84, in _execute
    return self.cursor.execute(sql, params)
  File ".pyenv/versions/3.6.8/envs/venv368/lib/python3.6/site-packages/django/db/utils.py", line 89, in __exit__
    raise dj_exc_value.with_traceback(traceback) from exc_value
  File ".pyenv/versions/3.6.8/envs/venv368/lib/python3.6/site-packages/django/db/backends/utils.py", line 84, in _execute
    return self.cursor.execute(sql, params)
django.db.utils.DataError: invalid input syntax for integer: "7khgb4fp"
LINE 1: ...m" ("order_id", "product_id", "quantity") VALUES ('7khgb4fp'...

This is the offending code:

with transaction.atomic():
    order = Order.objects.create(
        customer=customer,
        braintree_transaction_id=result.transaction.id,
        order_datetime=result.transaction.updated_at,
        price=total_amount,
        taxamo_transaction_id=transaction_key,
        braintree_payment_token=payment_method_token,
        bt_billing_address=transaction_data['billing_address_id'],
        bt_shipping_address=transaction_data['shipping_address_id'],
        user=user
    )

    order_list = []
    for product in validated_data['order_item']:
        order_list.append(
            OrderItem(order=order, **product)
        )

    # ---> DataError raised here:  <---
    OrderItem.objects.bulk_create(order_list)
    ...

The DataError exception is thrown when attempting to create a new OrderItem which has a ForeignKey to Order. The primary key of Order is braintree_transaction_id which is a CharField. However, Order was initially created with id (integer) as the pkey, which was removed and replaced by varchar braintree_transaction_id as the pkey. This was done in a migration.

Model classes:

class OrderItem(models.Model):
    order = models.ForeignKey(
        'premium.Order',
        on_delete=models.CASCADE,
        related_name="order_item"
    )
    product = models.ForeignKey(
        'premium.Product',
        on_delete=models.PROTECT,
    )
    quantity = models.PositiveSmallIntegerField(
        validators=[validators.MinValueValidator(1)]
    )

    def get_product_price(self):
        return self.product.price * self.quantity

class Order(models.Model):
    braintree_transaction_id = models.CharField(
        max_length=36, primary_key=True, null=False,
        help_text="Braintree transaction id generate by braintree API"
    )
    ...

Initial Order and OrderItem migrations:

# -*- coding: utf-8 -*-
# Generated by Django 1.9.12 on 2018-02-19 13:57
from __future__ import unicode_literals

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


class Migration(migrations.Migration):

    dependencies = [
        ('premium', '0014_subscription_user_instance'),
    ]

    operations = [
        ...
        migrations.CreateModel(
            name='Order',
            fields=[
                ('id', models.AutoField(auto_created=True, primary_key=True, serialize=False, verbose_name='ID')),
                ('braintree_transaction_id', models.CharField(max_length=36)),
                ('order_datetime', models.DateTimeField()),
                ('shipped_datetime', models.DateTimeField()),
                ('status', models.CharField(
                    choices=[('submitted', 'submitted'), ('settled', 'settled'), ('failed', 'failed'),
                             ('shipped', 'shipped'), ('received', 'received'), ('cancelled', 'cancelled')],
                    default='submitted', max_length=20)),
                ('customer', models.ForeignKey(on_delete=django.db.models.deletion.CASCADE, to='premium.Customer')),
            ],
        ),
        migrations.CreateModel(
            name='OrderItem',
            fields=[
                ('id', models.AutoField(auto_created=True, primary_key=True, serialize=False, verbose_name='ID')),
                (
                'quantity', models.PositiveSmallIntegerField(validators=[django.core.validators.MinValueValidator(1)])),
                ('order', models.ForeignKey(on_delete=django.db.models.deletion.CASCADE, to='premium.Order')),
                ('product', models.ForeignKey(on_delete=django.db.models.deletion.CASCADE, to='premium.Product')),
            ],
        ),

        migrations.AlterField(
            model_name='order',
            name='shipped_datetime',
            field=models.DateTimeField(null=True),
        ),

        migrations.AddField(
            model_name='order',
            name='price',
            field=models.FloatField(default=0.0),
        ),
    ]

A following migration then alters the primary key of Order and remaps the ForeignKey from OrderItem:

# -*- coding: utf-8 -*-
# Generated by Django 1.11.8 on 2018-04-06 08:28
from __future__ import unicode_literals

import django.core.validators
from django.db import migrations, models


map_braintree_id_by_order_id = {}


def collect_fk_order(apps, schema_editor):
    Order = apps.get_model("premium", "Order")

    qs = Order.objects.all()

    for q in qs:
        map_braintree_id_by_order_id[str(q.id)] = q.braintree_transaction_id


def rebuild_fk_order_item(apps, schema_editor):
    OrderItem = apps.get_model("premium", "OrderItem")

    for order_item in OrderItem.objects.all():
        br_id = map_braintree_id_by_order_id[order_item.order_id]

        order_item.order_id = br_id
        order_item.save(update_fields=['order_id'])


class Migration(migrations.Migration):

    dependencies = [
        ('premium', '0015_product_subscriptionadminproxy'),
    ]

    operations = [
        migrations.AddField(
            model_name='order',
            name='cancelled_datetime',
            field=models.DateTimeField(blank=True, null=True),
        ),
        migrations.AddField(
            model_name='order',
            name='exception_message',
            field=models.CharField(blank=True, help_text='Shows in the status_msg for failed status', max_length=250, null=True),
        ),
        migrations.AddField(
            model_name='order',
            name='expected_delivery_days',
            field=models.IntegerField(blank=True, help_text='Expected delivery days. Affect the status_msg for shipped status', null=True, validators=[django.core.validators.MinValueValidator(1)]),
        ),
        migrations.AddField(
            model_name='order',
            name='received_datetime',
            field=models.DateTimeField(blank=True, null=True),
        ),
        migrations.AlterField(
            model_name='order',
            name='shipped_datetime',
            field=models.DateTimeField(blank=True, null=True),
        ),
        migrations.RunPython(code=collect_fk_order, reverse_code=migrations.RunPython.noop),
        migrations.RemoveField(
            model_name='order',
            name='id',
        ),
        migrations.AlterField(
            model_name='order',
            name='braintree_transaction_id',
            field=models.CharField(max_length=36, primary_key=True, serialize=False, null=False),
        ),
        migrations.RunPython(code=rebuild_fk_order_item, reverse_code=migrations.RunPython.noop),
    ]

These are the SQL statements generated by pystest when creating the database (I've included only the OrderItem and Order model statements):

DEBUG    django.db.backends.schema:schema.py:128 CREATE TABLE "premium_order" ("id" serial NOT NULL PRIMARY KEY, "braintree_transaction_id" varchar(36) NOT NULL, "order_datetime" timestamp with time zone NOT NULL, "shipped_datetime" timestamp with time zone NOT NULL, "status" varchar(20) NOT NULL, "customer_id" integer NOT NULL); (params None)
DEBUG    django.db.backends.schema:schema.py:128 CREATE TABLE "premium_orderitem" ("id" serial NOT NULL PRIMARY KEY, "quantity" smallint NOT NULL CHECK ("quantity" >= 0), "order_id" integer NOT NULL, "product_id" integer NOT NULL); (params None)
DEBUG    django.db.backends.schema:schema.py:128 ALTER TABLE "premium_order" ALTER COLUMN "shipped_datetime" DROP NOT NULL; (params [])
DEBUG    django.db.backends.schema:schema.py:128 ALTER TABLE "premium_order" ADD COLUMN "price" double precision DEFAULT %s NOT NULL; (params [0.0])
DEBUG    django.db.backends.schema:schema.py:128 ALTER TABLE "premium_order" ALTER COLUMN "price" DROP DEFAULT; (params [])
DEBUG    django.db.backends.schema:schema.py:128 CREATE INDEX "premium_productrule_product_id_8cf5a319" ON "premium_productrule" ("product_id"); (params ())
DEBUG    django.db.backends.schema:schema.py:128 ALTER TABLE "premium_productrule" ADD CONSTRAINT "premium_productrule_product_id_8cf5a319_fk_premium_product_id" FOREIGN KEY ("product_id") REFERENCES "premium_product" ("id") DEFERRABLE INITIALLY DEFERRED; (params ())
DEBUG    django.db.backends.schema:schema.py:128 ALTER TABLE "premium_order" ADD CONSTRAINT "premium_order_customer_id_090ea006_fk_premium_customer_id" FOREIGN KEY ("customer_id") REFERENCES "premium_customer" ("id") DEFERRABLE INITIALLY DEFERRED; (params ())
DEBUG    django.db.backends.schema:schema.py:128 CREATE INDEX "premium_order_customer_id_090ea006" ON "premium_order" ("customer_id"); (params ())
DEBUG    django.db.backends.schema:schema.py:128 ALTER TABLE "premium_orderitem" ADD CONSTRAINT "premium_orderitem_order_id_9760db8b_fk_premium_order_id" FOREIGN KEY ("order_id") REFERENCES "premium_order" ("id") DEFERRABLE INITIALLY DEFERRED; (params ())
DEBUG    django.db.backends.schema:schema.py:128 ALTER TABLE "premium_orderitem" ADD CONSTRAINT "premium_orderitem_product_id_b1556f49_fk_premium_product_id" FOREIGN KEY ("product_id") REFERENCES "premium_product" ("id") DEFERRABLE INITIALLY DEFERRED; (params ())
DEBUG    django.db.backends.schema:schema.py:128 CREATE INDEX "premium_orderitem_order_id_9760db8b" ON "premium_orderitem" ("order_id"); (params ())
DEBUG    django.db.backends.schema:schema.py:128 CREATE INDEX "premium_orderitem_product_id_b1556f49" ON "premium_orderitem" ("product_id"); (params ())
DEBUG    django.db.backends.schema:schema.py:128 ALTER TABLE "premium_order" ADD COLUMN "cancelled_datetime" timestamp with time zone NULL; (params [])
DEBUG    django.db.backends.schema:schema.py:128 ALTER TABLE "premium_order" ADD COLUMN "exception_message" varchar(250) NULL; (params [])
DEBUG    django.db.backends.schema:schema.py:128 ALTER TABLE "premium_order" ADD COLUMN "expected_delivery_days" integer NULL; (params [])
DEBUG    django.db.backends.schema:schema.py:128 ALTER TABLE "premium_order" ADD COLUMN "received_datetime" timestamp with time zone NULL; (params [])
DEBUG    django.db.backends.schema:schema.py:128 ALTER TABLE "premium_order" DROP COLUMN "id" CASCADE; (params ())
DEBUG    django.db.backends.schema:schema.py:128 ALTER TABLE "premium_order" ADD CONSTRAINT "premium_order_braintree_transaction_id_d1e5b253_uniq" UNIQUE ("braintree_transaction_id"); (params ())
DEBUG    django.db.backends.schema:schema.py:128 ALTER TABLE "premium_order" ADD CONSTRAINT "premium_order_braintree_transaction_id_d1e5b253_pk" PRIMARY KEY ("braintree_transaction_id"); (params ())
DEBUG    django.db.backends.schema:schema.py:128 CREATE INDEX "premium_order_braintree_transaction_id_d1e5b253_like" ON "premium_order" ("braintree_transaction_id" varchar_pattern_ops); (params ())

Notice this statement:
CREATE TABLE "premium_orderitem" (... "order_id" integer NOT NULL,... where order_id ForeignKey is created as integer, which is according to the initial migration, but there is no statement that updates order_id to varchar, only one that updates the REFERENCE to premium_order. The ForeignKey type remains integer and is never changed to varchar (though it references a varchar field in premium_order). This results in a "broken" table structure. Here is premium_orderitem table in the test db:

> psql -d test_test

psql (11.5)
Type "help" for help.

test_test=# \d premium_orderitem
                               Table "public.premium_orderitem"
   Column   |   Type   | Collation | Nullable |                    Default
------------+----------+-----------+----------+-----------------------------------------------
 id         | integer  |           | not null | nextval('premium_orderitem_id_seq'::regclass)
 quantity   | smallint |           | not null |
 order_id   | integer  |           | not null |
 product_id | integer  |           | not null |
Indexes:
    "premium_orderitem_pkey" PRIMARY KEY, btree (id)
    "premium_orderitem_order_id_9760db8b" btree (order_id)
    "premium_orderitem_product_id_b1556f49" btree (product_id)
Check constraints:
    "premium_orderitem_quantity_check" CHECK (quantity >= 0)
Foreign-key constraints:
    "premium_orderitem_product_id_b1556f49_fk_premium_product_id" FOREIGN KEY (product_id) REFERENCES premium_product(id) DEFERRABLE INITIALLY DEFERRED

Table structure of Order is:

test_test=# \d premium_order
                             Table "public.premium_order"
          Column          |           Type           | Collation | Nullable | Default
--------------------------+--------------------------+-----------+----------+---------
 braintree_transaction_id | character varying(36)    |           | not null |
 order_datetime           | timestamp with time zone |           | not null |
 shipped_datetime         | timestamp with time zone |           |          |
 status                   | character varying(20)    |           | not null |
 customer_id              | integer                  |           | not null |
 price                    | double precision         |           | not null |
 cancelled_datetime       | timestamp with time zone |           |          |
 exception_message        | character varying(250)   |           |          |
 expected_delivery_days   | integer                  |           |          |
 received_datetime        | timestamp with time zone |           |          |
 taxamo_transaction_id    | character varying(30)    |           |          |
 track_trace              | character varying(255)   |           |          |
 braintree_payment_token  | character varying(50)    |           | not null |
 bt_billing_address       | character varying(36)    |           |          |
 bt_shipping_address      | character varying(36)    |           |          |
 braintree_refund_id      | character varying(36)    |           |          |
 user_id                  | integer                  |           |          |
Indexes:
    "premium_order_braintree_transaction_id_d1e5b253_pk" PRIMARY KEY, btree (braintree_transaction_id)
    "premium_order_braintree_transaction_id_d1e5b253_uniq" UNIQUE CONSTRAINT, btree (braintree_transaction_id)
    "premium_order_taxamo_transaction_id_key" UNIQUE CONSTRAINT, btree (taxamo_transaction_id)
    "premium_order_braintree_transaction_id_d1e5b253_like" btree (braintree_transaction_id varchar_pattern_ops)
    "premium_order_customer_id_090ea006" btree (customer_id)
    "premium_order_taxamo_transaction_id_79d6c572_like" btree (taxamo_transaction_id varchar_pattern_ops)
    "premium_order_user_id_e70bfca6" btree (user_id)
Foreign-key constraints:
    "premium_order_customer_id_090ea006_fk_premium_customer_id" FOREIGN KEY (customer_id) REFERENCES premium_customer(id) DEFERRABLE INITIALLY DEFERRED
    "premium_order_user_id_e70bfca6_fk_users_user_id" FOREIGN KEY (user_id) REFERENCES users_user(id) DEFERRABLE INITIALLY DEFERRED
Referenced by:
    TABLE "premium_ordercommunication" CONSTRAINT "premium_ordercommuni_order_id_f16be3a2_fk_premium_o" FOREIGN KEY (order_id) REFERENCES premium_order(braintree_transaction_id) DEFERRABLE INITIALLY DEFERRED

The primary key appears to be correct: braintree_transaction_id and it's a varchar.

Somehow the second migration, which updates Order's pkey, is not processed correctly and OrderItem ForeignKey to Order remains an integer. This raises an exception when trying to insert new OrderItem with a string order_id to the db (the string refers to braintree_transaction_id which is the pkey of Order).

When running pytest with --nomigrations option, the CREATE TABLE is done correctly (i.e. ForeignKey is a varchar) and no exception is thrown when creating new OrderItem.

Has anyone encountered this issue before?

Start by reviewing the migration sequence and the database-creation SQL shown in the report, especially the OrderItem foreign key created before the later primary-key change. Compare this path with pytest-django's test database setup and Django's migration executor. Done means reproducing the reported schema and confirming whether the issue belongs in pytest-django or in the project's migrations.