CRUD with Django, PostgreSQL, Bootstrap and Heroku — Part 3: Creating tables

In the previous part, we did the initial setup for our PostgreSQL and connected it to our Django project.

Now, we’re able to create new tables and columns from our models.py file. This file is dedicated to handling the DBs tables (each class is a new table), whereas inside this tables we create the columns we need. Django generates the primary key for our databases automatically.

On the employee_register/models.py file, we create our Employee and Position tables:

from django.db import models

# Create your models here.
class Position(models.Model):
   title = models.CharField(max_length=50)
class Employee(models.Model):
   fullname = models.CharField(max_length=100)
   emp_code = models.CharField(max_length=100)
   mobile = models.CharField(max_length=15)
   position = models.ForeignKey(Position, on_delete=models.CASCADE)

As we can see, on Employee table we’ll have four columns: fullname, emp_code, mobile and position. As we can see on this table, every field is Char type, except position.

This is because the value of position will be a field we’ll be using as a foreign key. Inside ForeignKey, we specify the Position table created, and the parameter on_delete=models.CASCADE means that, every time we delete an employee on the Position table, we’ll be deleting it on the Employee table as well.

If you don’t understand this, don’t worry. You’ll figure out what I’m talking about if you go along the tutorial.

We make the migrations to our Django app employee_register with the following command:

python manage.py makemigrations employee_register

If our tables are succesfully created, we’ll get the following output:

We just created two tables for the database in our Django app.

Now, we need to translate what we did to SQL. For this, we’ll see inside our project app that we’ll have an updated directory with a folder called migrations.

Each time we do migrations, we’ll need the prefix number of the indicated file.

We translate into SQL our actions with the following command:

python manage.py sqlmigrate employee_register 0001

Output:

BEGIN;
 — 
 — Create model Position
 — 
CREATE TABLE “employee_register_position” (“id” serial NOT NULL PRIMARY KEY, “title” varchar(50) NOT NULL);
 — 
 — Create model Employee
 — 
CREATE TABLE “employee_register_employee” (“id” serial NOT NULL PRIMARY KEY, “fullname” varchar(100) NOT NULL, “emp_code” varchar(100) NOT NULL, “mobile” varchar(15) NOT NULL, “position_id” integer NOT NULL);
ALTER TABLE “employee_register_employee” ADD CONSTRAINT “employee_register_em_position_id_c7c8c467_fk_employee_” FOREIGN KEY (“position_id”) REFERENCES “employee_register_position” (“id”) DEFERRABLE INITIALLY DEFERRED;
CREATE INDEX “employee_register_employee_position_id_c7c8c467” ON “employee_register_employee” (“position_id”);
COMMIT;

The final step to update our tables:

python manage.py migrate

In a nutshell, every time we add new models, we execute the following commands in that order:

python manage.py makemigrations employee_register
python manage.py sqlmigrate employee_register 0001
python manage.py migrate

If we go to Tables section on pgAdmin and refresh, we should see the new tables:

employee_register_employee Columns properties

employee_register_position Columns properties

In the next part, we’ll be handling views, and how to handle request for the front-end of our project.