035.3 Lesson 1

The Structured Query Language is specific to databases. Write and read operations are expressed in sentences called statements and queries. Both statements and queries are made up of clauses, which define the conditions for executing the operation.

Names and email addresses, for example, can be stored in a database table that contains name and email fields. A database can contain several tables, so each table must have a unique name. If we use the name contacts for the names and emails table, a new record can be inserted with the following statement:

This insertion statement is composed of the INSERT INTO clause, which defines the table and fields where the data will be inserted. The second clause, VALUES, sets the values that will be inserted. It is not necessary to capitalize clauses, but it is common practice, so as to better recognize SQL keywords within a statement or query.

A query on the contacts table is done in a similar way, but using the SELECT clause:

In this case, the SELECT email clause selects one field from entries in the contacts table. The WHERE clause restricts the query to specific rows:

SQL has many other clauses, and we’ll look at some of them in later sections. But first it is necessary to see how to integrate the SQL database with Node.js.

SQLite is probably the simplest solution for incorporating SQL database features into an application. Unlike other popular database management systems, SQLite is not a database server to which a client connects. Instead, SQLite provides a set of functions that allow the developer to create a database like a conventional file. In the case of an HTTP server implemented with Node.js Express, this file is usually located in the same directory as the server script.

Before using SQLite in Node.js, you need to install the sqlite3 module. Run the following command in the server’s installation directory; i.e., the directory containing the Node.js script you will run.

Be aware that there are several modules that support SQLite, such as better-sqlite3, whose usage is subtly different from sqlite3. The examples in this lesson are for the sqlite3 module, so they might not work as expected if you choose another module.

To demonstrate how a Node.js Express server can work with an SQL database, let’s write a script that stores and displays messages sent by a client identified by a cookie. Messages are sent by the client via the HTTP POST method and the server response can be formatted as JSON or HTML (from a template), depending on the format requested by the client. This lesson won’t go into detail about using HTTP methods, cookies, and templates. The code snippets shown here assume that you already have a Node.js Express server where these features are configured and available.

The simplest way to store the messages sent by the client is to store them in a global array, where each message previously sent is associated with a unique identification key for each client. This key can be sent to the client as a cookie, which is presented to the server on future requests to retrieve its previous messages.

However, this approach has a weakness: because messages are stored only in a global array, all messages will be lost when the current server session is terminated. This is one of the advantages of working with databases, because the data is persistently stored and is not lost if the server is restarted.

Using the index.js file as the main server script, we can incorporate the sqlite3 module and indicate the file that serves as the database, as follows:

If it doesn’t already exist, the messages.sqlite3 file will be created in the same directory as the index.js file. Inside this single file, all structures and respective data will be stored. All database operations performed in the script will be intermediated by the db constant, which is the name given to the new sqlite3 object that opens the messages.sqlite3 file.

No data can be inserted into the database until at least one table is created. Tables are created with the statement CREATE TABLE:

The db.run() method is used to execute SQL statements in the database. The statement itself is written as a parameter for the method. Although SQL statements must end with a semicolon when entered in a command-line processor, the semicolon is optional in statements passed as parameters in a program.

Because the run method will be performed every time the script is executed with node index.js, the SQL statement includes the conditional clause IF NOT EXISTS to avoid errors in future executions when the messages table already exists.

The fields that make up the messages table are id, uuid, and message. The id field is a unique integer used to identify each entry in the table, so it is created as PRIMARY KEY. Primary keys cannot be null and there cannot be two identical primary keys in the same table. Therefore, almost every SQL table has a primary key in order to track the table’s contents. Although it is possible to explicitly choose the value for the primary key of a new record (provided it does not yet exist in the table), it is convenient for the key to be generated automatically. The AUTOINCREMENT flag in the id field is used for this purpose.

The uuid and message fields store the client identification and message content, respectively. A field of type CHAR(36) stores a fixed amount of 36 characters, and a field of type TEXT stores texts of arbitrary length.

The main function of our example server is to store messages that are linked to the client that sent them. The client sends the message in the message field in the body of the request sent with the HTTP POST method. The client’s identification is in a cookie called uuid. With this information, we can write the Express route to insert new messages in the database:

This time, the db.run() method executes an insert statement, but note that the uuid and req.body.message are not written directly into the statement line. Instead, question marks were substituted for the values. Each question mark corresponds to a parameter that follows the SQL statement in the db.run() method.

Using question marks as placeholders in the statement that is executed in the database makes it easier for SQLite to distinguish between the static elements of the statement and its variable data. This strategy allows SQLite to escape or sanitize the variable contents that are part of the statement, preventing a common security breach called SQL injection. In that attack, malicious users insert SQL statements into the variable data in the hope that the statements will be executed inadvertently; sanitizing foils the attack by disabling dangerous characters in the data.

As shown in the sample code, our intent is to use the same route to insert new messages into the database and to generate the list of previously sent messages. The db.all() method returns the collection of all entries in the table that match the criteria defined in the query.

Unlike the statements performed by db.run(), db.all() generates a list of records that are handled by the arrow function designated in the last parameter:

This function, in turn, takes two parameters: err and rows. The err parameter will be used if an error occurs that prevents the execution of the query. Upon success, all records are available in the rows array, where each element is an object corresponding to a single record from the table. The properties of this object correspond to the field names indicated in the query: uuid and message.

The rows array is a JavaScript data structure. As such, it can be used to generate responses with methods provided by Express, such as res.json() and res.render(). When rendered inside an EJS template, a conventional loop can list all records:

Instead of filling the rows array with all the records returned by the query, in some cases it might be more convenient to treat each record individually with the db.each() method. The db.each() method syntax is similar to the db.all() method, but the row parameter in (err, row) ⇒ {} matches a single record at a time.

So far, our client can only add and query messages on the server. Since the client now knows the id of the previously sent messages, we can implement a function to modify a specific record. The modified message can also be sent to an HTTP POST method route, but this time with a route parameter to catch the id given by the client in the request path:

This route demonstrates how to use the UPDATE and WHERE clauses to modify an existing record. An important difference from the previous examples is the use of named parameters, where values are bundled into a single object (param) and passed to the db.run() method instead of specifying each value by itself. In this case, the field names (preceded by $) are the object’s properties. Named parameters allow the use of field names (preceded by $) as placeholders instead of question marks.

A statement like the one in the example will not cause any modification to the database if the condition imposed by the WHERE clause fails to match some record in the table. To evaluate whether any records were modified by the statement, a callback function can be used as the last parameter of the db.run() method. Inside the function, the number of changed records can be queried from this.changes. Note that arrow functions cannot be used in this case, because only regular functions of the form function(){} define the this object.

Removing a record is very similar to modifying it. We can, for example, continue using the :id route parameter to identify the message to be deleted, but this time in a route invoked by the client’s HTTP DELETE method:

Records are deleted from a table with the DELETE FROM clause. We again used the callback function to evaluate how many entries have been removed from the table.

Once defined, the db object can be referenced at any time during script execution, because the database file remains open throughout the current session. It is not common to close the database while the script is running.

A function to close the database is useful, however, to avoid abruptly closing the database when the server process finishes. Although unlikely, abruptly shutting down the database can result in inconsistencies if in-memory data is not yet committed to the file. For instance, an abrupt database shutdown with data loss can occur if the script is terminated by the user by pressing the Ctrl+C keyboard shortcut.

In the Ctrl+C scenario just described, the process.on() method can intercept signals sent by the operating system and execute an orderly shutdown of both the database and the server:

The Ctrl+C shortcut invokes the SIGINT operating system signal, which terminates a foreground program in the terminal. Before ending the process when receiving the SIGINT signal, the system invokes the callback function (the last parameter in the process.on() method). Inside the callback function, you can put any cleanup code, in particular the db.close() method to close the database and server.close(), which gracefully closes the Express instance itself.