Throughout this book, you will be writing code. If you need to refer to the complete code for this chapter, you can find it in the GitHub repository here: https://github.com/PacktPublishing/Web-Development-with-Django-Second-Edition/tree/main/Chapter02.

In most web applications, data forms the core part of the application. Unless we’re talking about a very simple application such as a calculator, in most cases, we need to store data, process it, and display it to the user on a page. Since most operations in user-facing web applications involve data, there is a need to store data somewhere secure, easily accessible, and readily available. This is where databases come in handy. Imagine a library operation before the advent of computers. The librarian would have to maintain records of book inventories, book lending, returns from students, and so on. All of these would have been maintained in physical records. While carrying out their day-to-day activities, the librarian would modify these records for each operation, for example, when lending a book to someone or when the book was returned.

Today, we have databases to help us with such administrative tasks. A database looks like a spreadsheet or an Excel sheet containing records, with each table consisting of multiple rows and columns. An application can have many such tables. Here is an example table of a book inventory in a library:

Table 2.1: Table of a book inventory for a library

In the preceding table, we can see that there are columns with details about various attributes of the books in the library, while the rows contain entries for each book. To manage a library, there can be many such tables working together as a system. For example, along with an inventory, we may have other tables such as student information, book lending records, and so on. Databases are built with the same logic, where software applications can easily manage data.

A database is a structured collection of data that helps manage information easily. A software layer called the database management system (DBMS) is used to store, maintain, and perform operations on the data. There are two types of databases, relational databases, and non-relational databases. In the following sections, we will learn in brief about relational databases and how data is stored in such databases.

Relational databases

Relational databases or structured query language (SQL) databases store data in a pre-determined structure of rows and columns called tables. A database can be made up of more than one such table, and these tables have a fixed structure of attributes, data types, and relations with other tables. For example, as we just saw in Figure 2.1, the book inventory table has a fixed structure of columns comprising Book Number, Author, Title, and Number of Copies, and the entries form the rows in the table. There could be other tables as well, such as Student Information and Lending Records, which could be related to the inventory table. Also, whenever a book is lent to a student, the records will be stored per the relationships between multiple tables (say, the Student Information and the Book Inventory tables).

This pre-determined structure of rules defining the data types, tabular structures, and relationships across different tables acts like scaffolding or a blueprint for a database. This blueprint is collectively called a database schema. It will prepare the database to store application data when applied to a database. To manage and maintain these databases, there is a common language for relational databases called SQL. Some examples of relational databases are SQLite, PostgreSQL, MySQL, and OracleDB.

In the next section, you will be introduced to non-relational databases, some examples of non-relational databases, and the reasons for using them in web applications.

Non-relational databases

Non-relational databases or not only SQL (NoSQL) databases are designed to store unstructured data. They are well suited to large amounts of generated data that does not follow rigid rules, as is the case with relational databases. Some examples of non-relational databases are Cassandra, MongoDB, CouchDB, and Redis.

For example, imagine that you need to store the stock value of companies in a database using Redis. Here, the company name will be stored as the key and the stock value as the value. Using the key-value type NoSQL database in this use case is appropriate because it stores the desired value for a unique key and is faster to access.

For the scope of this book, we will be dealing only with relational databases, as Django does not officially support non-relational databases. However, if you wish to explore, there are many forked projects, such as Django non-rel, that support NoSQL databases.

In the next section, you will be introduced to database CRUD operations using SQL commands.

Database operations using SQL

SQL uses a set of commands to perform a variety of database operations, such as creating an entry, reading values, updating an entry, and deleting an entry. These operations are collectively called CRUD operations. To understand database operations in detail, let’s first get some hands-on experience with SQL commands. Most relational databases share a similar SQL syntax; however, some operations will differ.

For the scope of this chapter, we will use SQLite as the database. SQLite is a lightweight relational database and is a part of Python’s standard libraries. That’s why Django uses SQLite as its default database configuration. However, we will also learn more about how to perform configuration changes to use other databases in Chapter 17, Deploying a Django Application (Part 1 – Server Setup). This chapter can be downloaded from the GitHub repository of this book, here: https://github.com/PacktPublishing/Web-Development-with-Django-Second-Edition/tree/main/Chapter17.

In the next section, we will learn about the importance of data types in a database.

Data types in relational databases

Databases provide us with a way to restrict the type of data that can be stored in a given column. These are called data types. Some examples of data types for a relational database, such as SQLite3, are given here:

• INTEGER: This is used for storing integers
• TEXT: This can store text
• REAL: This is used for floating-point values

For example, you would want the title of a book to have TEXT as the data type. So, the database will enforce a rule that no type of data, other than text data, can be stored in that column. Similarly, the book’s price can have a REAL data type, and so on.

Using some of the concepts learned before about databases, in the next section, we will get hands-on by creating a database and a database table.

Exercise 2.01 – creating a book database

In this exercise, you will create a book database for a book review application. For better visualization of the data in the SQLite database, you will install an open source tool called DB Browser for SQLite. This tool helps visualize the data and provides a shell to execute the SQL commands.

If you haven’t done so already, visit https://sqlitebrowser.org, and from the Download section, install the application as per your operating system and launch it. Detailed instructions for DB Browser installation can be found in the Preface. To create a database, follow these steps:

Note

Database operations can be performed using a command-line shell as well.

1. After launching the application, create a new database by clicking New Database in the top-left corner of the application. Create a database named bookr, as you are working on a book review application:

Figure 2.1 – Creating a database named bookr

2. Next, click on the Create Table button in the top-left corner and enter book as the table name.

Note

After clicking the Save button, you may find that the window for creating a table opens up automatically. In that case, you won’t have to click the Create Table button; simply proceed with the creation of the book table as specified in the preceding step.

3. Now, click the Add field button, enter the field name as title and select the TEXT type from the drop-down menu. Here, TEXT is the data type for the title field in the database:

Figure 2.2: Adding a TEXT field named title

4. Similarly, add two more fields for the table named publisher and author and select the TEXT type for both fields. Then, click on the OK button:

Figure 2.3: Creating TEXT fields named publisher and author

This creates a database table called book in the bookr database with the title, publisher, and author fields. This can be seen as follows:

Figure 2.4: Database with the title, publisher, and author fields

Overall, in this section about databases, we have learned about why we need databases and briefly about how they store data, the two most common types of databases. Additionally, we created a simple database and a table using an open source tool called DB Browser (SQLite). In the next section, we will learn more about how to do CRUD operations on the database using SQl.

Let’s assume that the editors or the users of our book review application want to make some modifications to the book inventory, such as adding a few books to the database, updating an entry in the database, and so on. SQL provides various ways to perform such CRUD operations. Before we dive into the world of Django models and migrations, let’s explore these basic SQL operations first.

You will be running a few SQL commands for the CRUD operations that follow.

To run them, follow these steps:

1. Navigate to the Execute SQL tab in DB Browser. You can type in or paste the SQL commands we’ve listed in the sections that follow in the SQL 1 window. You can spend some time modifying your queries, and understanding them before you execute them.
2. When you’re ready, click the icon that looks like a Play button or press the F5 key to execute the command. The results will show up in the window below the SQL 1 window:

Figure 2.5: Executing SQL commands in DB Browser

Now we will try out some SQL create operations to create a few records in the database.

SQL create operations

A create operation in SQL is performed using the insert command, which, as the name implies, lets us insert data into the database. Let’s go back to our bookr example. Since we have already created the database and the book table, we can now create or insert an entry in the database by executing the following command:

insert into book values ('The Sparrow Warrior', 'Super Hero
Publications', 'Patric Javagal');

This inserts the values defined in the command into the book table. Here, The Sparrow Warrior is the title, Super Hero Publications is the publisher, and Patric Javagal is the author of the book. Note that the order of insertion corresponds with the way we have created our table; that is, the values are inserted into the columns representing title, publisher, and author, respectively. Similarly, let’s execute two more inserts to populate the book table:

insert into book values ('Ninja Warrior', 'East Hill Publications', 'Edward Smith');
insert into book values ('The European History', 'Northside
Publications', 'Eric Robbins');

The three inserts executed so far will insert three rows into the book table. But how do we verify that? How do we know whether those three entries we inserted are entered into the database correctly? Let’s learn how to do that in the next section.

SQL read operations

We can read from the database using the select SQL operation. For example, the following SQL select command retrieves the selected entries created in the book table:

select title, publisher, author from book;

You should see the following output:

Figure 2.6: Output after using the select command

Here, select is the command that reads from the database, and the title, publisher, and author fields are the columns that we intend to select from the book table. Since these are all the columns the database has, the select statement has returned all the values present in the database. The select statement is also called an SQL query. An alternate way to get all the fields in the database is by using the * wildcard in the select query instead of specifying all the column names explicitly:

select * from book;

This will return the same output as shown in the preceding figure. Now, suppose we want to get the author’s name for the book titled The Sparrow Warrior; in this case, the select query would be as follows:

select author from book where title="The Sparrow Warrior";

Here, we have added a special SQL keyword called where so that the select query returns only the entries that match the condition. The result of the query, of course, will be Patric Javagal. Now, what if we wanted to change the name of the book’s publisher?

SQL update operations

In SQL, the way to update a record in the database is by using the update command:

update book set publisher = 'Northside Publications' where
title='The Sparrow Warrior';

Here, we set the publisher value to Northside Publications if the value of the title is The Sparrow Warrior. We can then run the select query we ran in the SQL read operations section to see how the updated table looks after running the update command:

Figure 2.7: Updating the publisher value for The Sparrow Warrior

Next, what if we wanted to delete the title of the record we just updated? We will see just that in the next section.

SQL delete Operations

Here is an example of how to delete a record from the database using the delete command:

delete from book where title='The Sparrow Warrior';

The delete command is the SQL keyword for delete operations. Here, this operation will be performed only if the title is The Sparrow Warrior. Here is how the book table will look after the delete operation:

Figure 2.8 – Output after performing the delete operation

These are the basic operations of SQL. We will not go further into all the SQL commands and syntax, but feel free to explore more about database base operations using SQL.

Note

For further reading, you can start by exploring some advanced SQL select operations with join statements, which are used to query data across multiple tables. For a detailed course on SQL, you can refer to The SQL Workshop (https://www.packtpub.com/product/the-sql-workshop/9781838642358).

In this section, we learned how to interact with the database by performing CRUD operations on the database using SQL. In the next section, we will learn about how Django’s ORM, which is an abstract layer, interacts with a database.

Web applications constantly interact with databases, and one of the ways to do so is by using SQL. If you decide to write a web application without a web framework such as Django and instead use Python alone, Python libraries such as psycopg2 (for PostgreSQL ) could be used to interact directly with the databases using SQL commands. But while developing a web application with multiple tables and fields, SQL commands can easily become overly complex and thus difficult to maintain. For this reason, popular web frameworks such as Django provide a level of abstraction that allows us to easily work with databases. The part of Django that helps us do this is called ORM.

Django ORM converts object-oriented Python code into actual database constructs, such as database tables with data type definitions, and facilitates all the database operations via simple Python code. Because of this, we do not have to deal with SQL commands while performing database operations. This helps in faster application development and ease in maintaining the application source code.

Django supports relational databases such as SQLite, PostgreSQL, Oracle Database, and MySQL. Django’s database abstraction layer ensures that the same Python/Django source code can be used across any of these relational databases with very little modification to the project settings. Since SQLite is part of the Python libraries and Django is configured by default to SQLite, for the scope of this chapter, we will use SQLite while we learn about Django models and migrations. Next, we will understand how database configuration is done with Django.

Database configuration and creating Django applications

As we have already seen in Chapter 1, An Introduction to Django, when we create a Django project and run the Django server, the default database configuration is SQLite3. The database configuration will be present in the project directory, in the settings.py file.

Note

Make sure you go through the settings.py file for the bookr app. Going through the entire file once will help you understand the concepts that follow. You can find the file here: https://github.com/PacktPublishing/Web-Development-with-Django-Second-Edition/blob/main/Chapter02/final/bookr/bookr/settings.py.

So, for our example project, the database configuration will be present at the following location: bookr/settings.py. The default database configuration present in this file when a Django project is created is as follows:

DATABASES = {
    'default': {
        'ENGINE': 'django.db.backends.sqlite3',
        'NAME': BASE_DIR / 'db.sqlite3',
    }
}

The DATABASES variable is assigned a dictionary containing the database details for the project. Inside the dictionary, there is a nested dictionary with a default key. This holds the configuration of a default database for the Django project. The reason we have a nested dictionary with default as a key is that a Django project could potentially interact with multiple databases, and the default database is the one used by Django for all operations unless explicitly specified. The ENGINE key represents which database engine is being used; in this case, sqlite3.

The NAME key defines the name of the database, which can have any value. But for SQLite3, since the database is created as a file, NAME can have the full path of the directory where the file needs to be created. The full path of the db file is processed by joining (or concatenating) the previously defined path in BASE_DIR with db.sqlite3. Note that BASE_DIR is the project directory as already defined in the settings.py file.

If you are using other databases, such as PostgreSQL, MySQL, and so on, changes will have to be made in the preceding database settings as shown here:

DATABASES = {
    'default': {
        'ENGINE': 'django.db.backends.postgresql',
        'NAME': 'bookr',
        'USER': <username>,
        'PASSWORD': <password>,
        'HOST': <host-IP-address>,
        'PORT': '5432',}}

Here, changes have been made to ENGINE to use PostgreSQL. The host IP address and port number of the server need to be provided for HOST and PORT, respectively. As the names suggest, USER is the database username, and PASSWORD is the database password. In addition to changes in the configuration, we will have to install the database drivers or bindings along with the database host and credentials. This will be covered in detail in later chapters, but for now, since we are using SQLite3, the default configuration will be sufficient. Note that the preceding code is just an example to show the changes you’ll need to make to use a different database such as PostgreSQL, but since we are using SQLite, we will use the database configuration that exists already, and there is no need to make any modifications to the database settings. Next, we will briefly read about Django apps and some of the default apps present in Django.

Django apps

A Django project can have multiple apps that often act as discrete entities. That’s why, whenever required, an app can also be plugged into a different Django project. For example, if we develop an e-commerce web application, the web application can have multiple apps, such as a chatbot for customer support or a payment gateway to accept payments as users purchase goods from the application. These apps, if needed, can also be plugged into or reused in a different project.

Django comes with the following apps enabled by default. The following is a snippet from a project’s settings.py file:

  INSTALLED_APPS = [
      'django.contrib.admin',
      'django.contrib.auth', 
      'django.contrib.contenttypes', 
      'django.contrib.sessions', 
      'django.contrib.messages', 
      'django.contrib.staticfiles',
      ]

These are a set of installed or default apps used for the admin site, authentication, content types, sessions, messaging, and an application to collect and manage static files. In the upcoming chapters, we shall study this in depth. For the scope of this chapter, though, we shall understand why Django migration is needed for these installed apps in the following section.

Django migration

As we learned before, Django’s ORM helps make database operations simpler. A major part of the operation is transforming the Python code into database structures, such as database fields with stated data types and tables. In other words, the transformation of Python code into database structures is known as migration. For example, instead of creating database tables by running SQL commands, you would write models for them in Python; something you’ll learn to do in the upcoming Creating models and migrations section. These models will have fields that form the blueprints of database tables. The fields, in turn, will have different field types giving us more information about the type of data stored there (recall how we specified the data type of our field as TEXT in step 4 of Exercise 2.01 – creating a book database).

Since we have a Django project set up, let’s perform our first migration. Although we have not added any code yet to our project, we can migrate the applications listed in INSTALLED_APPS. This is necessary because Django’s installed apps need to store the relevant data in the database for their operations, and migration will create the required database tables to store the data in the database. The following command should be entered in the terminal or shell to do this:

python manage.py migrate

Note

For macOS, you can use python3 instead of python in the preceding command.

Here, manage.py is a script that was automatically created when the project was created. It is used for carrying out managerial or administrative tasks. By executing this command, we create all the database structures required by the installed apps.

As we are using DB Browser for SQLite to browse the database, let’s take a look at the database for which changes have been made after executing the migrate command.

The database file will be created in the project directory under the db.sqlite3 name. Follow these steps to verify that migration has been successful and database tables have been created:

1. Open DB Browser, click Open Database, navigate until you find the db.sqlite3 file, and open it. You should see a set of newly created tables created by the Django migration. It will look as follows in DB Browser:

Figure 2.9: Contents of the db.sqlite3 file

2. Now, browse through the newly created database structure by clicking the database tables, and we should see the following:

Figure 2.10: Browsing through the newly created database structure

3. Notice that the database tables created have different fields, each with their respective data types. Click on the Browse data tab in DB Browser and select a table from the drop-down menu. For instance, after clicking the auth_group_permissions table, you should see something like this:

Figure 2.11: Viewing the auth_group_permissions table

You will see that there is no data available for these tables yet because Django migration only creates the database structure or the blueprint, and the actual data in the database is stored during the operation of the application. Now that we have migrated the built-in or default Django apps, let’s try to create an app and perform a Django migration.

In this section, we learned about Django’s ORM and how it can simplify database operations for a web application. Later, we learned about how Django’s database configuration settings. We also briefly covered Django migrations, which we will go deeper into in the next section by creating models and migrations for our Bookr application.