-
Notifications
You must be signed in to change notification settings - Fork 1
Database
For this project we have chosen PostgreSQL as our database of choice. In combination with Django ORM, it allows for faster development because the ORM eliminates the need for us to write any SQL code and also gives advantages such as protection against SQL injection out of the box without needing any extra setup or work. The database also becomes easier to modify when needed.
For the overall structure of our database, more specifically our EER diagram and logical design, please click here.
Django ORM works with models specified in models.py to generate our database. Every model corresponds to a table in our database, giving us an easy and fast way to add, edit and remove tables where necessary. For more detailed information about how you can make these models, click here
Django migrations are used to propagate changes made in our models (models.py) to our database schema. Below we will give a general overview of how they work, but a more detailed guide can be found here.
In order to simplify this process a bit, we have provided a script that makes the migrations and migrates them at the same time. Just use
$ ./migrations.shin the root of the project directory. Make sure that your docker container is running with docker-compose up!
The first line in the script
docker-compose exec backend python manage.py makemigrations
Creates a new migration based on the changes you have made to the model in models.py.
New migrations that are created have a unique identifier, which is just an auto incremented id.
There are already some migration files in our project, these can be found in the migrations folder.
Warning: The filenames and content of these files must never be changed, as this will cause problems for those who already executed these migrations!
This is because django keeps track of a migration history inside the database, in this table the filename is tracked.
Instead just create a new migrations on top of the already executed migrations.
The migrations that are created with the commands above, need to be migrated to the database. This is what the second command in the script does:
docker-compose exec backend python manage.py migrate
We can also use migrations to insert (or remove) data in our database. These are called data migrations, more information about them can be found here. We haven't used this in our project however, instead we have opted for fixtures which will be covered in the next section. This section about data migrations has only been left in for the sake of completion.
To create a new autogenerated data migration we can use the command:
docker-compose exec backend python manage.py makemigrations --empty drtrottoir
In this migration we can then define a function that inserts/removes data. This needs to be added to the operations list inside the migration class and then the migration can be executed by using the same command as with model migrations:
docker-compose exec backend python manage.py migrate
Fixtures in Django are another useful way to dump data and add the data to our database, and they are our preferred method. An example of a fixtures file is datadump.json.
In order to create a data dump of our database file using docker, we can use the command:
docker-compose exec backend python manage.py dumpdata --natural-foreign --natural-primary -e contenttypes -e auth.Permission --indent 4 -o <output_name>.jsonThere are other file extension possible but this will do for our test data.
Before loading the data in the database, we want to make sure it's empty to avoid any conflicts. Follow the steps below to achieve this.
Execute the following command:
docker psThis will give you the CONTAINER_ID for the postgres:15-alpine image.
Execute following commands:
docker exec -it <CONTAINER_ID> psql -U django -d postgres -c "DROP DATABASE drtrottoir WITH(FORCE);"
and
docker exec -it <CONTAINER_ID> psql -U django -d postgres -c "CREATE DATABASE drtrottoir;"
../migrations.shTo load the data in the database, use the command:
docker-compose exec backend python manage.py loaddata <fixturename>This means that if you want to load our example fixture, you have to run:
docker-compose exec backend python manage.py loaddata datadump.jsonThe example fixture we have provided will serve as a good initial batch of data for testing purposes. Please note that not all tables have been filled; below is a list of those that have been filled:
- Building on tours
- Buildings
- Garbage Collections
- Regions
- Student at buildings on tours
- Tours
- Users
- Manual
- Email templates
There are currently 22 users, all of whom share the same password drtrottoir123.
The names of these users have been chosen to make it easier to recognize what role they have:
- Users whose name start with
Adare admins - Users whose name start with
Stare students - Users whose name start with
Suare superstudents - Users whose name start with
Syare syndics
Additionally, male names indicate that the user's region is set to Gent, whereas female names indicate that a user's
region is set to Antwerpen.
To make logging into the admin page easier with these users, there is also an admin/superuser whose email address is set
to [email protected].
More information about fixtures can be found here.
You can also connect to the actual database and request the data using SQL queries. However, you normally don't need this since we usually interact with it through the back-end.
To check whether the data has been correctly inserted into the database, we can log onto the postgres docker container: We can do this by first checking our running containers, using the command:
$ docker ps
CONTAINER ID IMAGE COMMAND CREATED STATUS PORTS NAMES
7742c9f9dfcb project-frontend "docker-entrypoint.s…" About a minute ago Up About a minute project-frontend-1
075ff1639b24 project-backend "python manage.py ru…" About a minute ago Up About a minute project-backend-1
32771801b817 reverseproxy "/docker-entrypoint.…" About a minute ago Up About a minute 0.0.0.0:80->80/tcp, 0.0.0.0:443->443/tcp, 0.0.0.0:2002->2002/tcp project-reverseproxy-1
68e6b6e00ab1 postgres:15-alpine "docker-entrypoint.s…" 26 hours ago Up About a minute 0.0.0.0:5432->5432/tcp
In the output we can view the CONTAINER ID the postgres container has, in this case it is 68e6b6e00ab1.
Finally, we can log onto the container with the command:
$ docker exec -it <container_id> psql -U django drtrottoir
here you replace <container_id> with the corresponding container id.
Now if we want to do any operation in SQL, we can do so. The tables that are created in the database get named by django. Django names the tables like appname_modelname.
For example if we want the rows of our users, we can type:
drtrottoir=# SELECT * from base_user;
To get a list of all tables created by django, you can use the command:
drtrottoir=# \dt
Alternatively, you can head over to the admin page to get a GUI to view the contents of the database. You can also easily add, remove and edit entries on the admin page without having to write any SQL code. Just make sure you have a superuser to login to the admin page. You can do this by either running the following command:
docker-compose exec backend python manage.py createsuperuser which makes a new superuser, or by using our provided datadump which already has multiple superusers.