How To Show SQL Query Command In Django Migration

Django model class is defined in models.py file in Django application folder, each model class will be mapped to a database table in the project backend database server. After you define the model class, you can create the related table use Django manage.py tasks command, all the low level sql command ( such as table create, update etc ) will be generated automatically by Django, you do not need to care about that. But some times you really want to see the sql command generated by Django, this example will show you how to get it.

1. How To Display Django Application Migration SQL Command.

After you define the model classes, you can use below Django manage.py tasks to create the mapped table in db.

1. Open a terminal and go to Django project root folder.
2. Then run python manage.py makemigrations django_app_name command to check current Django application’s model changes.

   $ python manage.py makemigrations dept_emp

3. Then you can find a python file like 0001_initial.py has been added in the django_application (dept_emp) / migrations folder if there has any model changes. All the model changes migration files will be saved in this folder.
4. Below is the source code of file 0001_initial.py.
   

   from django.conf import settings
   from django.db import migrations, models
   import django.db.models.deletion
   
   
   class Migration(migrations.Migration):
   
       initial = True
   
       dependencies = [
           migrations.swappable_dependency(settings.AUTH_USER_MODEL),
       ]
   
       operations = [
           migrations.CreateModel(
               name='Department',
               fields=[
                   ('id', models.AutoField(auto_created=True, primary_key=True, serialize=False, verbose_name='ID')),
                   ('dept_name', models.CharField(max_length=1000)),
                   ('dept_desc', models.CharField(max_length=1000)),
               ],
           ),
           migrations.CreateModel(
               name='Employee',
               fields=[
                   ('id', models.AutoField(auto_created=True, primary_key=True, serialize=False, verbose_name='ID')),
                   ('emp_mobile', models.CharField(max_length=100)),
                   ('emp_salary', models.IntegerField()),
                   ('emp_onboard_date', models.DateTimeField(auto_now=True)),
                   ('dept', models.ForeignKey(on_delete=django.db.models.deletion.CASCADE, to='dept_emp.Department')),
                   ('user', models.ForeignKey(on_delete=django.db.models.deletion.CASCADE, to=settings.AUTH_USER_MODEL)),
               ],
           ),
           migrations.AlterUniqueTogether(
               name='department',
               unique_together={('dept_name',)},
           ),
           migrations.AlterUniqueTogether(
               name='employee',
               unique_together={('emp_mobile',)},
           ),
       ]
   

5. Above .py file do not contain the low level sql command that will be executed to the table.
6. Run command python manage.py sqlmigrate app_name migration_file_number in terminal to show the sql command generated with this migration .py file.

   $ python manage.py sqlmigrate dept_emp 0001
   System check identified some issues:
   
   WARNINGS:
   ?: (urls.W001) Your URL pattern '^$' uses include with a route ending with a '$'. Remove the dollar from the route to avoid problems including URLs.
   BEGIN;
   --
   -- Create model Department
   --
   CREATE TABLE "dept_emp_department" ("id" integer NOT NULL PRIMARY KEY AUTOINCREMENT, "dept_name" varchar(1000) NOT NULL, "dept_desc" varchar(1000) NOT NULL);
   --
   -- Create model Employee
   --
   CREATE TABLE "dept_emp_employee" ("id" integer NOT NULL PRIMARY KEY AUTOINCREMENT, "emp_mobile" varchar(100) NOT NULL, "emp_salary" integer NOT NULL, "emp_onboard_date" datetime NOT NULL, "dept_id" integer NOT NULL REFERENCES "dept_emp_department" ("id") DEFERRABLE INITIALLY DEFERRED, "user_id" integer NOT NULL REFERENCES "auth_user" ("id") DEFERRABLE INITIALLY DEFERRED);
   --
   -- Alter unique_together for department (1 constraint(s))
   --
   CREATE UNIQUE INDEX dept_emp_department_dept_name_f1b58519_uniq ON "dept_emp_department" ("dept_name");
   --
   -- Alter unique_together for employee (1 constraint(s))
   --
   CREATE UNIQUE INDEX dept_emp_employee_emp_mobile_e3bcd06e_uniq ON "dept_emp_employee" ("emp_mobile");
   CREATE INDEX "dept_emp_employee_dept_id_9eccbf29" ON "dept_emp_employee" ("dept_id");
   CREATE INDEX "dept_emp_employee_user_id_2895129d" ON "dept_emp_employee" ("user_id");
   COMMIT;
   

7. When you are sure the sql command is correct, you can run python manage.py migrate app_name command to run above sql command to create table in backend database.

   python manage.py migrate dept_emp

8. As you can see in the sql command, all the table name is combined by app-name_model-class-name. And each table has automatically create an auto-increment id primary key field by default. If you do not want to use the default primary key and want to specify one of the model class filed as primary key, you can set primary_key=True in the model class field definition like below.

   class Employee(models.Model):
       emp_name = models.CharField(max_length=100, primary_key=True)

Reference

1. How To Manage Models In Django Admin Site