Django south migration - Adding FULLTEXT indexes

Question

I need to add a FULLTEXT index to one of my Django model's fields and understand that there is no built in functionality to do this and that such an index must be added manually in mysql (our back end DB).

I want this index to be created in every environment. I understand model changes can be dealt with Django south migrations, but is there a way I could add such a FULLTEXT index as part of a migration?

In general, if there is any custom SQL that needs to be run, how can I make it a part of a migration.

Thanks.

Answer 1

You can write anything as a migration. That's the point!

Once you have South up and running, type in python manage.py schemamigration myapp --empty my_custom_migration to create a blank migration that you can customize.

Open up the XXXX_my_custom_migration.py file in myapp/migrations/ and type in your custom SQL migration there in the forwards method. For example you could use db.execute

The migration might look something like this:

class Migration(SchemaMigration):

    def forwards(self, orm):
        db.execute("CREATE FULLTEXT INDEX foo ON bar (foobar)")
        print "Just created a fulltext index..."
        print "And calculated {answer}".format(answer=40+2)


    def backwards(self, orm):
        raise RuntimeError("Cannot reverse this migration.") 
        # or what have you


$ python manage.py migrate myapp XXXX # or just python manage.py migrate.
"Just created fulltext index...."
"And calculated 42"

Answer 2

In newer versions of Django, you can create an empty migration for execute custom sql: python3 manage.py makemigrations --empty app_name

Then, in the generated migration:

from django.db import migrations

class Migration(migrations.Migration):

    operations = [
        migrations.RunSQL(
            sql="CREATE FULLTEXT INDEX `index_name` on table_name(`column_name`);",
            reverse_sql="ALTER TABLE table_name DROP INDEX index_name"
        )
    ]