How Does a Website Work (2) - Server, Database

In How does a website work (1) I talked about how a front end and back end of a website work with each other. This blog talks about

• how a server works, and
• how it communicates with its database.

3. How Does a Server Run?

Just like all people in a coffee shop are either customer or coffee shop workers, all the machines connected to Internet are classified to either server or client.
A Server has an IP Address
Each machine has a name on Internet, the IP address. The IP address consists of four numbers ranging from 0 to 255. For example, one of the IP address of google.ca is 172.217.2.131. The IP of a Linux computer can be found by curl ifconfig.co. By visiting the server’s IP address, you can visit the websites that the server is hosting.
Global or Local IP Address
But wait… What if there is not enough IP addresses for all machines? The total available public IP addresses is 256^4 = 4,294,967,296 which is less than the number of people on earth. There is definitely not enough IP addresses for everyone to use. Luckily, there is local area network (LAN). A LAN to the Internet is like a table to the coffee shop. The servers do not have to remember all the ordering numbers of the clients; instead, they remember the table numbers and serve foods to the table. The patriarch at the table then distributes the foods among people at the table. In a LAN, a router redistributes the IP address and assigns each machine in the network a distinct LAN IP address (but share the same Internet IP address). The inet addr shown in the ifconfig command (usually one starting with 192.168) is the one given by your LAN router. If a machine is set to be visible, it can be visited by the IP address.
A Server Has a Domain Name
Nowadays almost nobody visits a website through the four-number IP address; domain name servers(DNS) translate it into human-readable domain names.
When visiting a website, the browser asks for the IP address from DNS, and then visits the server located at the address. You may ask, why not directly save the name-to-IP address mapping to a local file? That speeds up the website visiting process and prevents being the victim of DNS pollution. You get it! That’s the purpose of the hosts file. The hosts file, located at /etc/hosts(Unix-like systems) or C:\System32\Drivers\etc\hosts (Windows), can be modified to store more mappings. Prior to visiting DNS, a browser looks up the site name in hosts. If an IP address is found, the browser just heads to it instead of visiting DNS.
A Server Uses Ports
A server can host multiple websites, delivered through its different ports.
Usually, the following ports have special purposes:

Purpose	Port	Notes
HTTP	80	HyperText Tranfer Protocol
HTTPS	443	HTTP Secure
SSH	22	Secure Shell
FTP	21	File Transfer Protocol
POP3	110	Post Office Protocol 3
SMTP	25	Simple Mail Transfer Protocol
IMAP	143	Internet Message Access Protocol

As an example, for an Apache2 server the /etc/apache2/sites-enabled/sitename.conf should be changed.

4. How Do Server and Database Cooperate?

The database is the server’s memory. A server can interact with its database in various ways, depending on the server and database themselves. I’m going to present two examples of a server performing a query on database, but before that let’s talk about what constitutes a database.

What Consists of a Database?

Two types of databases are popular nowadays: relational (eg: MySQL, SQLite, PostgreSQL, etc) and non-relational (MongoDB, Cassandra, etc).

• A relational database can have many tables, each has many columns(attributes) and rows(entries), as specified by the schema.
• A non-relational database has different architecture. Take MongoDB as example, there is a database, each can have many collections. There are many documents in each collection, whose properties described by fields.
• A comparison between MySQL and MongoDB are listed in the following table.

MySQL	MongoDB
Database	Database
Table	Collection
Row	Document
Column	Field
Index	Index

Examples of How Server Works with Database

Two examples are provided here to illustrate the common pattern of server and database’s cooperation by performing a simple query (prints all the names of the entries with positive id). The LAMP example uses Python Flask server and MySQL 5.7 database; the MEAN example uses Node.js server and MongoDB with Mongoose.
LAMP Example: Python Flask server + MySQL 5.7
The database schema is defined in a whatever_name.sql script. To set it up, run

mysql -uroot -pYOUR_PASSWORD < whatever_name.sql

I’m not putting the schema definition in server script because I don’t want the content to be erased each time the server starts. Instead, the schema is defined in the whatever_name.sql file.
Database Schema

DROP TABLE if exists table_name;
CREATE TABLE table_name (
    id BIGINT NOT NULL AUTO_INCREMENT,
    name BLOB not NULL,
    PRIMARY KEY (id)
);

Server-side Scripts

from flaskext.mysql import MySQL
app = Flask(__name__)
mysql = MySQL()
app.config['MYSQL_DATABASE_USER'] = 'user_name'
# For this example, I'm assuming the MySQL 5.7 has
# a user named user_name.
app.config['MYSQL_DATABASE_AUTHENTICATION_STRING'] = ''
app.config['MYSQL_DATABASE_DB'] = 'db_name'
app.config['MYSQL_DATABASE_HOST'] = 'localhost'
mysql.init_app(app)
@app.route('/query_names', methods=['GET'])
def query_name():
    conn = mysql.connect()
    cursor = conn.cursor()
    query = '''SELECT name FROM table_name WHERE id>0;'''
    cursor.execute(query)
    for name in cursor:
        print name
    cursor.close()
    conn.close()

MEAN example: Node.js server + MongoDB (using Mongoose)
The project structure is a bit different from LAMP. Here the database configuration is incorporated into the same module.

// Define Database
var mongoose = require('mongoose');
var db_url = "whatever_your_url_name_is";
mongoose.connect(db_url);
var db = mongoose.connection;
db.on('error', console.error.bind(console, 'connection error:'));
db.once('open', function() {
	console.log('Now we are connected!');
});
var Schema = mongoose.Schema({
	id_: Number,
	name: String
});
var Entry = mongoose.model('Entry', Schema);
// Performs query. Assume the app is already set up
Entry.find({'id_': {$gt: 0}}, function(err, result) {
    console.log(result.name);
});