Almost every dynamic website makes use of some type of server-based data source. By far the most common data source for these sites is a database. Back in Chapter 1, you learned that many real-world sites make use of a database server, which is a computer (real or virtual) that is devoted to running a relational DBMS. In smaller sites (such as those you create in your lab exercises), the database server is usually the same machine as the web server.

In this book, the relational DBMS used will be either SQLite or MySQL. SQLite is a file-based approach to databases; since it doesn't require any additional software, it is ideal for learning scenarios but isn't used that commonly in real-world sites. MySQL has traditionally been the database system used for PHP websites. It is a full-fledged DBMS that needs to be installed and configured. While the MySQL source code is openly available, it is now owned by Oracle Corporation. MariaDB is a more recent open-source, drop-in (i.e., fully compatible) replacement for MySQL that was created due to copyright concerns over Oracle’s purchase of Sun and MySQL. There are many other open-source and proprietary relational DBMS alternates to MySQL, such as PostgreSQL, Oracle Database, IBM DB2, and Microsoft SQL Server. All of these relational database management systems are capable of managing large amounts of data, maintaining data integrity, responding to many queries, creating indexes and triggers, and more.

In addition to the powerful relational database systems we will use throughout the book, there are non-relational models for database systems that will also be explored in this chapter. These systems are usually categorized with the term NoSQL and includes systems such as Cassandra and MongoDB that can be installed on your development machine, as well as cloud-based systems such as AWS DynamoDB or Google FireBase.

For the rest of this book, we will use the term database to refer to both the software (i.e., the DBMS) and to the data that is managed by the DBMS.

14.1.1 The Role of Databases in Web Development

The reason that databases are such an essential feature of real-world websites is that they provide a way to implement one of the most important software design principles: namely, that one should separate that which varies from that which stays the same. In the context of the web, sites typically display different content on different pages, but those different pages share similar user interface elements, or even have an identical visual design, as shown in Figure 14.1.

Figure 14.1 Separating content from data

Figure 14.1 Full Alternative Text

In such cases, the visual appearance (i.e., the HTML and CSS) is that which stays the same, while the data content is that which varies. In Chapter 10, you have had some experience already with this principle, in that you used JavaScript to fetch data from an API and then “inserted” the received data into the DOM. Server-side environments such as PHP or Node can use databases in a similar way, except rather than modifying the DOM, they can generate HTML that contains the retrieved data. Databases usually provide the data for the web APIs used by JavaScript. Databases are also used for nondisplay purposes, such as user authentication, saving form data, or preserving analytic information. Figure 14.2 illustrates three of these uses and also illustrates how a DBMS might be running on the same machine as the web application itself, on a separate data server, or even on a cloud service.

Figure 14.2 How websites use databases

Figure 14.2 Full Alternative Text

Note

Since this chapter uses both PHP and Node, the labs for this chapter have been split into two files: Lab14a (PHP) and Lab14b (Node).