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 web applications from scratch. Now you can learn not only about Node and JavaScript, but also get all the information, including frontend 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 Electron-based command-line tools and desktop applications, so chapters on both areas have been included in the book. Inside the post will be considered an excerpt "Data storage in applications"

Node.js focuses on an incredibly wide range of developers with equally diverse needs. No database or storage technology can satisfy most of the usage scenarios served by Node. This chapter provides a broad overview of storage capabilities along with some important high-level concepts and terminology.

8.1. Relational databases

For most of the time web technologies have existed, relational databases have been at the forefront of data storage. This topic is already being considered in many other books and educational programs, so we will not spend much time considering 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 data types 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 a one-to-many relationship between User and Post. Then, in SQL (Structured Query Language), queries are formulated for data such as "Get all messages belonging to the user with identifier 123", or in 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 a 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 vary by platform. On macOS, installation boils 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 trying to upgrade. Follow the instructions for your platform to migrate existing databases, or completely erase the database directory:

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

Then initialize and start Postgres:

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

These commands start the Postgres daemon. The daemon should start every time you restart the computer. Perhaps you are building to set up automatic loading of the Postgres daemon at startup; You can find a description of this process for your operating system in many online manuals.

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

8.2.2. Database creation

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

createdb articles

If the operation succeeds, 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 given time are connected to only one database, although you can configure several databases at once, depending on the mode in which the database operates. In many applications, at least two modes are distinguished: development mode and real-life mode.

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

dropdb articles

To reuse the database, you must run 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 will be created and the pg package installed, you can proceed to use the database from Node. Before entering any commands to the server, you must 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 page on GitHub .

8.2.4. Table definition

To store data in PostgreSQL, you must first determine 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 source code archive 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. Data insertion

After the table is defined, you can insert data into it with INSERT queries (Listing 8.3). If id is not specified, then PostgreSQL will select it for you. To find out which identifier was selected for a particular record, attach the RETURNING id condition to the request; the identifier will be displayed in the lines of the result passed to the callback function.

Listing 8.3. Data insertion

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 the 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 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. Data Queries

One of the most remarkable features of relational databases is the ability to perform complex arbitrary data queries. The queries are executed by the SELECT command, and the simplest example of this kind is presented 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 understandable: string concatenation into SQL statements can be a cumbersome process that complicates the understanding and maintenance of queries. This is especially true in relation to the JavaScript language, in which there was no syntax for representing multiline strings until template literals appeared 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 a query builder. The query builder generates SQL strings through a declarative API that 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 ANSI and ISO SQL standards appeared back in the mid-1980s, most databases continue to use native SQL dialects. PostgreSQL is a notable exception: this database boasts compliance with 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.

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

In the table. 8.1 compares the methods for generating the INSERT command depending on the selected database.

Table 8.1. Comparing Knex SQL Commands for Different Databases


Knex supports promises and callbacks in Node style.

8.3.2. Connect and execute queries in Knex

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

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

Knex requests return promises by default, but they also support Node callback conventions 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 directly with the SQLite database 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. Knex API Interoperability

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 to a single file. By executing the previous code, you will see that the articles.sqlite file appears in the current directory. To destroy a SQLite database, just delete one file:

rm articles.sqlite

SQLite also supports in-memory mode, which does not write to disk at all. This mode is commonly used to speed up the execution of automated tests. To configure the operating mode in memory, a special file name is used: memory :. When opening several connections to the file: memory: each connection receives its own isolated database:

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

"For more information on the book is available on the website of the publishing house
" Table of Contents
" Extract

To Habrozhiteley 20% discount coupon - Node.js