The book “Node.js in action. 2nd edition »

The second edition of “Node.js in action” has been completely redesigned to reflect the realities that every Node developer now faces. You will learn about interface building systems and popular Node web frameworks, as well as learn how to build Express-based web applications from scratch. Now you can learn not only about Node and JavaScript, but also get all the information, including front-end building systems, choosing a web framework, working with databases in Node, testing and deploying web applications.

Node technology is increasingly being used in conjunction with command-line tools and Electron-based desktop applications, so chapters on both areas were included in the book. Inside the post will be considered an excerpt "Storing data in applications"

Node.js focuses on an incredibly wide range of developers with equally diverse needs. No database or storage technology is able to satisfy most of the use cases that Node maintains. This chapter provides a broad overview of data storage capabilities along with some important high-level concepts and terminology.

8.1. Relational databases

Throughout almost the entire lifetime of web technologies, relational databases have been at the forefront of data storage. This topic has already been addressed in many other books and educational programs, so we will not spend much time reviewing it.
')
Relational databases based on mathematical concepts of relational algebra and set theory have been known since the 1970s. A schema defines the format of various types of data and the relationships that exist between these types. For example, when building a social network, you can create User and Post data types and define one-to-many relationships between User and Post. Further, in SQL (Structured Query Language) formulated queries to data such as "Receive all messages belonging to the user with ID 123", or SQL: SELECT * FROM post WHERE user_id = 123.

8.2. PostgreSQL

MySQL and PostgreSQL (Postgres) remain the most popular relational databases for Node applications. The differences between relational databases are mostly aesthetic, so this section applies equally to other relational databases — for example, MySQL in Node. But first, let's figure out how to install Postgres on the development machine.

8.2.1. Installation and Setup

First you need to install Postgres on your system. A simple npm install command is not enough for this. Installation instructions are platform dependent. In macOS, installation comes down to a simple sequence of commands:

brew update brew install postgres 

If Postgres support is already installed on your system, you may encounter problems when attempting to upgrade. Follow the instructions for your platform to migrate existing databases, or completely delete the database directory:

 # WARNING: will delete existing postgres configuration & data rm –rf /usr/local/var/postgres 

Then initialize and run Postgres:

 initdb -D /usr/local/var/postgres pg_ctl -D /usr/local/var/postgres -l logfile start 

These commands launch the Postgres daemon. The daemon should start every time you restart the computer. Perhaps you are building to configure the Postgres daemon auto-load on startup; In many online manuals, you can find a description of this process for your operating system.

Postgres installs some command-line administrative programs. Check them out; necessary information can be found in the electronic documentation.

8.2.2. Database creation

After the Postgres daemon runs, you need to create a database. This procedure is sufficient to perform only once. The easiest way to use the createdb program from the command line. The following command creates a database named articles:

 createdb articles 

If the operation completes successfully, the command displays nothing. If a database with the specified name already exists, the command does nothing and reports an error.

As a rule, applications at any time are connected to only one database, although you can configure several databases at once, depending on the mode in which the database is running. In many applications, at least two modes are distinguished: development mode and real operation mode.

To remove all data from an existing database, run the dropdb command from the terminal, passing in the name of the database in the argument:

 dropdb articles 

To use the database again, you must execute the createdb command.

8.2.3. Connect to Postgres from Node

The most popular package for interacting with Postgres from Node is called pg. It can be installed using npm:

 npm install pg --save 

When the Postgres server is up, the database is created, and the pg package is installed, you can switch to using the database from Node. Before entering any commands to the server, you need to create a connection to it, as shown in listing 8.1

 const pg = require('pg'); const db = new pg.Client({ database: 'articles' });  ←   . db.connect((err, client) => {  if (err) throw err;  console.log('Connected to database', db.database);  db.end();  ←       ,   node . }); 

Detailed documentation on pg.Client and other methods can be found on the pg package wiki on GitHub .

8.2.4. Table definition

To store data in PostgreSQL, you first need to define the tables and the format of the data that will be stored in them. An example of this kind is shown in Listing 8.2 (ch08-databases / listing8_3 in the archive of the source code of the book).

Listing 8.2. Schema definition

 db.query(` CREATE TABLE IF NOT EXISTS snippets (  id SERIAL,  PRIMARY KEY(id),  body text  ); `, (err, result) => { if (err) throw err; console.log('Created table "snippets"'); db.end(); }); 

8.2.5. Insert data

After the table is defined, you can insert data into it with INSERT queries (Listing 8.3). If the id value is not specified, PostgreSQL will select it for you. To find out which identifier has been selected for a particular entry, append the RETURNING id condition to the query; the identifier will be displayed in the rows of the result passed to the callback function.

Listing 8.3. Insert data

 const body = 'hello world'; db.query(`  INSERT INTO snippets (body) VALUES (    '${body}'  )  RETURNING id `, (err, result) => {  if (err) throw err;  const id = result.rows[0].id;  console.log('Inserted row with id %s', id);  db.query(`    INSERT INTO snippets (body) VALUES (      '${body}'    )    RETURNING id  `, () => {    if (err) throw err;    const id = result.rows[0].id;    console.log('Inserted row with id %s', id);  }); }); 

8.2.6. Data update

After the data is inserted, it can be updated with an UPDATE query (Listing 8.4). The number of records involved in the update will be available in the rowCount property of the query result. A complete example for this listing is contained in the ch08-databases / listing8_4 directory.

Listing 8.4. Data update

 const id = 1; const body = 'greetings, world'; db.query(`  UPDATE snippets SET (body) = (    '${body}'  ) WHERE id=${id}; `, (err, result) => {  if (err) throw err;  console.log('Updated %s rows.', result.rowCount); }); 

8.2.7. Requests for data sampling

One of the most remarkable features of relational databases is the ability to perform complex random queries to data. Queries are executed with the SELECT command, and the simplest example of this kind is shown in Listing 8.5.

Listing 8.5. Data request

 db.query(`  SELECT * FROM snippets ORDER BY id `, (err, result) => {  if (err) throw err;  console.log(result.rows); }); 

8.3. Knex

Many developers prefer to work with SQL commands in their applications not directly, but through an abstract add-in. This desire is completely understandable: concatenating strings into SQL commands can be a cumbersome process that complicates understanding and maintaining queries. This is especially true of the JavaScript language, which did not have the syntax for presenting multi-line strings before the appearance of template literals in ES2015 (see https://developer.mozilla.org/en/docs/Web/JavaScript/Reference/Template_literals ). In fig. 8.1 shows Knex statistics with the number of downloads proving popularity.


Knex is a Node package that implements a lightweight abstraction for SQL, known as Query Builder. The query builder generates SQL strings through a declarative API, which has much in common with the generated SQL commands. Knex API is intuitive and predictable:

 knex({ client: 'mysql' })  .select()  .from('users')  .where({ id: '123' })  .toSQL(); 

This call creates a parameterized SQL query in the MySQL dialect:

 select * from `users` where `id` = ? 

8.3.1. jQuery for databases

Although the ANSI and ISO SQL standards appeared in the mid-1980s, most databases continue to use their own SQL dialects. PostgreSQL is a notable exception: this database can boast of adhering to the SQL: 2008 standard. The query builder is able to normalize the differences between SQL dialects, providing a single unified interface for generating SQL in different technologies. This approach has obvious advantages for groups that regularly switch between different database technologies.

Currently Knex.js supports the following databases: PostgreSQL; MSSQL; MySQL; MariaDB; SQLite3; Oracle.

In tab. 8.1 compares how to generate an INSERT command depending on the selected database.

Table 8.1. Comparing SQL commands generated by Knex for different databases


Knex supports promises and Node-style callbacks.

8.3.2. Connecting and querying in Knex

Unlike many other query builders, Knex can also connect and execute queries to the selected database driver for you.

 db('articles')  .select('title')  .where({ title: 'Today's News' })  .then(articles => {    console.log(articles);  }); 

By default, Knex requests return promises, but they also support Node callback agreements using .asCallback:

 db('articles')  .select('title')  .where({ title: 'Today's News' })  .asCallback((err, articles) => {    if (err) throw err;    console.log(articles);  }); 

In Chapter 3, we interacted with the SQLite database directly using the sqlite3 package. This API can be rewritten using Knex. Before running this example, first check from npm that the knex and sqlite3 packages are installed:

 npm install knex@~0.12.0 sqlite3@~3.1.0 --save 

Listing 8.6 uses sqlite to implement a simple Article model. Save the file as db.js; it will be used in Listing 8.7 to interact with the database.

Listing 8.6. Using Knex to connect and issue queries to sqlite3

 const knex = require('knex'); const db = knex({  client: 'sqlite3',  connection: {    filename: 'tldr.sqlite'  },  useNullAsDefault: true  ←             . }); module.exports = () => {  return db.schema.createTableIfNotExists('articles', table => {    table.increments('id').primary();  ←       «id»,      .    table.string('title');    table.text('content');  }); }; module.exports.Article = {  all() {    return db('articles').orderBy('title');  },  find(id) {    return db('articles').where({ id }).first();  },  create(data) {    return db('articles').insert(data);  },  delete(id) {    return db('articles').del().where({ id });  } }; 

Listing 8.7. Interaction with API based on Knex

 db().then(() => {  db.Article.create({    title: 'my article',    content: 'article content'  }).then(() => {    db.Article.all().then(articles => {      console.log(articles);      process.exit();    });  }); }) .catch(err => { throw err }); 

SQLite requires minimal configuration: you do not need to load the server daemon or create databases outside the application. SQLite writes all the data in one file. Having executed the previous code, you will see that in the current directory appeared the file articles.sqlite. To destroy the SQLite database, it is enough to delete only one file:

 rm articles.sqlite 

SQLite also supports an in-memory mode in which writing to the disk is not performed at all. This mode is commonly used to speed up automated tests. To set the memory mode, a special file name is used: memory :. When opening multiple connections to the file: memory: each connection gets its own isolated database:

 const db = knex({  client: 'sqlite3',  connection: {    filename: ':memory:'  },  useNullAsDefault: true }); 

»More information about the book can be found on the publisher's website.
» Table of Contents
» Excerpt

For Habrozhiteley a 20% discount on the coupon - Node.js