You are previewing Node: Up and Running.

Node: Up and Running

Cover of Node: Up and Running by Tom Hughes-Croucher... Published by O'Reilly Media, Inc.
O'Reilly logo

Relational Databases

There are still many good reasons to use a traditional database with SQL, and Node interfaces with popular open source choices.

MySQL

MySQL has become the workhorse of the open source world for good reason: it provides many of the same capabilities as larger commercial databases for free. In its current form, MySQL is performant and feature-rich.

Using NodeDB

The node-db module provides a native code interface to popular database systems, including MySQL, using a common API that the module exposes to Node. Although node-db supports more than just MySQL, this section focuses on using MySQL in your application code. Since Oracle’s purchase of Sun Microsystems, the future of MySQL and its community has come under much speculation. Some groups advocate moving to a drop-in replacement such as MariaDB or switching to a different relational database management system (RDBMS) entirely. Although MySQL isn’t going away anytime soon, you need to decide for yourself whether it will be the right choice of software for your work.

Installation

The MySQL client development libraries are a prerequisite for the Node database module. On Ubuntu, you can install the libraries using apt:

sudo apt-get install libmysqlclient-dev

Using npm, install a package named db-mysql:

npm install -g db-mysql

To run the examples in this section, you will need to have a database called upandrunning with a user dev who has the password dev. The following script will create the database table and basic schema:

DROP DATABASE IF EXISTS upandrunning;

CREATE DATABASE upandrunning;

GRANT ALL PRIVILEGES ON upandrunning.* TO 'dev'@'%' IDENTIFIED BY 'dev';

USE upandrunning;

CREATE TABLE users( 
   id int auto_increment primary key, 
   user_login varchar(25), 
   user_nicename varchar(75) 
);

Selection

Example 6-24 selects all ID and user_name columns from a WordPress user table.

Example 6-24. Selecting from MySQL

var mysql = require( 'db-mysql' );

var connectParams = {
  'hostname': 'localhost',
  'user': 'dev',
  'password': 'dev',
  'database': 'upandrunning'
}

var db = new mysql.Database( connectParams );

db.connect(function(error) {
  if ( error ) return console.log("Failed to connect");

  this.query()
    .select(['id', 'user_login'])
    .from('users')
    .execute(function(error, rows, columns) {
      if ( error ) {
        console.log("Error on query");
      } else {
        console.log(rows);
      }
    });
});

As you can probably guess, this executes the equivalent of the SQL command SELECT id, user_login FROM users. The output is:

{ id: 1, user_login: 'mwilson' }

Insertion

Inserting data is very similar to selection because commands are chained in the same way. Example 6-25 shows how to generate the equivalent to INSERT INTO users ( user_login ) VALUES ( 'newbie');.

Example 6-25. Inserting into MySQL

var mysql = require( 'db-mysql' );

var connectParams = {
  'hostname': 'localhost',
  'user': 'dev',
  'password': 'dev',
  'database': 'upandrunning'
}

var db = new mysql.Database( connectParams );

db.connect(function(error) {
  if ( error ) return console.log("Failed to connect");

  this.query()
    .insert('users', ['user_login'], ['newbie'])
    .execute(function(error, rows, columns) {
      if ( error ) {
        console.log("Error on query");
        console.log(error);
      }
      else console.log(rows);
    });
});

The output is:

{ id: 2, affected: 1, warning: 0 }

The .insert command takes three parameters:

  • The table name

  • The column names being inserted

  • The values to insert in each column

The database drivers take care of escaping and converting the data types in your column values, so you don’t have to worry about SQL injection attacks from code passing through this module.

Updating

Like selection and insertion, updates rely on chained functions to generate equivalent SQL queries. Example 6-26 demonstrates the use of a query parameter to filter the update, rather than performing it across all records in the database table.

Example 6-26. Updating data in MySQL

var mysql = require( 'db-mysql' );

var connectParams = {
  'hostname': 'localhost',
  'user': 'dev',
  'password': 'dev',
  'database': 'unandrunning'
}

var db = new mysql.Database( connectParams );

db.connect(function(error) {
  if ( error ) return console.log("Failed to connect");

  this.query()
         .update('users')
         .set({'user_nicename': 'New User' })
         .where('user_login = ?', [ 'newbie' ])
    .execute(function(error, rows, columns) {
      if ( error ) {
        console.log("Error on query");
        console.log(error);
      }
      else console.log(rows);
  });
});

The output is:

{ id: 0, affected: 1, warning: 0 }

Updating a row consists of three parts:

  • The .update command, which takes the table name (users, in this case) as a parameter

  • The .set command, which uses a key-value object pair to identify the column names to update and their values

  • The .where command, which tells MySQL how to filter the rows that will be updated

Deletion

As shown in Example 6-27, deletion is very similar to updates, except that in the case of a delete, there are no columns to update. If no where conditions are specified, all records in the table will be deleted.

Example 6-27. Deleting data in MySQL

var mysql = require( 'db-mysql' );

var connectParams = {
  'hostname': 'localhost',
  'user': 'dev',  'password': 'dev',
  'database': 'upandrunning'
}

var db = new mysql.Database( connectParams );

db.connect(function(error) {
  if ( error ) return console.log("Failed to connect");

  this.query()
    .delete()
    .from('users')
    .where('user_login = ?', [ 'newbie' ])
    .execute(function(error, rows, columns) {
      if ( error ) {
        console.log("Error on query");
        console.log(error);
      }
      else console.log(rows);
    });
});

The output is:

{ id: 0, affected: 1, warning: 0 }

The .delete command is similar to the .update command, except it does not take any column names or data values. In this example, wildcard parameters are demonstrated in the “where” clause: 'user_login = ?'. The question mark is replaced by the user_login parameter in this code before execution. The second parameter is an array, because if multiple question marks are used, the database driver will take the values in order from this parameter.

Sequelize

Sequelize is an object relational mapper (ORM) that takes much of the repetition out of the tasks performed in the preceding sections. You can use Sequelize to define objects shared between the database and your program, then pass data to and from the database using those objects rather than writing a query for every operation. This becomes a major time-saver when you need to perform maintenance or add a new column, and makes overall data management less error-prone. Sequelize supports installation using npm:

npm install sequelize

As the database and example user were already created for the examples in the previous section, it’s time to create an Author entity inside the database (Example 6-28). Sequelize handles the creation for you, so you don’t have to take care of any manual SQL at this point.

Example 6-28. Creating an entity using Sequelize

var Sequelize = require('sequelize');

var db = new Sequelize('upandrunning', 'dev', 'dev', {
  host: 'localhost'
});

var Author = db.define('Author', {
  name: Sequelize.STRING,
  biography: Sequelize.TEXT
});

Author.sync().on('success', function() {
  console.log('Author table was created.');
}).on('failure', function(error) {
  console.log('Unable to create author table');
});

The output is:

Executing: CREATE TABLE IF NOT EXISTS `Authors` (`name` VARCHAR(255), `biography`
TEXT, `id` INT NOT NULL auto_increment , `createdAt` DATETIME NOT NULL, `updatedAt`
DATETIME NOT NULL, PRIMARY KEY (`id`)) ENGINE=InnoDB;
Author table was created.

In this example, an Author was defined as an entity containing a name field and a biography field. As you can see in the output, Sequelize added an autoincremented primary key column, a createdAt column, and an updatedAt column. This is typical of many ORM solutions, and provides standard hooks by which Sequelize is able to reference and interact with your data.

Sequelize differs from the other libraries shown in this chapter in that it is based on a listener-driven architecture, rather than the callback-driven architecture used elsewhere. This means that you have to listen for both success and failure events after each operation, rather than having errors and success indicators returned with the operation’s results.

Example 6-29 creates two tables with a many-to-many relationship. The order of operation is:

  1. Set up the entity schemas.

  2. Synchronize the schemas with the actual database.

  3. Create and save a Book object.

  4. Create and save an Author object.

  5. Establish a relationship between the author and the book.

Example 6-29. Saving records and associations using Sequelize

var Sequelize = require('sequelize');

var db = new Sequelize('upandrunning', 'dev', 'dev', {
  host: 'localhost'
});

var Author = db.define('Author', {
  name: Sequelize.STRING,
  biography: Sequelize.TEXT
});

var Book = db.define('Book', {
  name: Sequelize.STRING
});

Author.hasMany(Book);
Book.hasMany(Author);

db.sync().on('success', function() {
  Book.build({
    name: 'Through the Storm'
  }).save().on('success', function(book) {
    console.log('Book saved');
    Author.build({
      name: 'Lynne Spears',
      biography: 'Author and mother of Britney'
    }).save().on('success', function(record) {
      console.log('Author saved.');
      record.setBooks([book]);
      record.save().on('success', function() {
        console.log('Author & Book Relation created');
      });
    });
  }).on('failure', function(error) {
    console.log('Could not save book');
  });
}).on('failure', function(error) {
  console.log('Failed to sync database');
});

To ensure that the entities are set up correctly, we do not create the author until after the book is successfully saved into the database. Likewise, the book is not added to the author until after the author has been successfully saved into the database. This ensures that both the author’s ID and the book’s ID are available for Sequelize to establish the association. The output is:

Executing: CREATE TABLE IF NOT EXISTS `AuthorsBooks` 
           (`BookId` INT , `AuthorId` INT , `createdAt` DATETIME NOT NULL, 
           `updatedAt` DATETIME NOT NULL, 
           PRIMARY KEY (`BookId`, `AuthorId`)) ENGINE=InnoDB;
Executing: CREATE TABLE IF NOT EXISTS `Authors` 
           (`name` VARCHAR(255), `biography` TEXT, 
           `id` INT NOT NULL auto_increment , `createdAt` DATETIME NOT NULL, 
           `updatedAt` DATETIME NOT NULL, PRIMARY KEY (`id`)) 
           ENGINE=InnoDB;
Executing: CREATE TABLE IF NOT EXISTS `Books` 
           (`name` VARCHAR(255), `id` INT NOT NULL auto_increment , 
           `createdAt` DATETIME NOT NULL, `updatedAt` DATETIME NOT NULL, 
           PRIMARY KEY (`id`)) ENGINE=InnoDB;
Executing: CREATE TABLE IF NOT EXISTS `AuthorsBooks` 
           (`BookId` INT , `AuthorId` INT , `createdAt` DATETIME NOT NULL,
           `updatedAt` DATETIME NOT NULL, 
           PRIMARY KEY (`BookId`, `AuthorId`)) ENGINE=InnoDB;
Executing: INSERT INTO `Books` (`name`,`id`,`createdAt`,`updatedAt`)
           VALUES ('Through the Storm',NULL,'2011-12-01 20:51:59',
                      '2011-12-01 20:51:59');
Book saved
Executing: INSERT INTO `Authors` (`name`,`biography`,`id`,`createdAt`,`updatedAt`)
           VALUES ('Lynne Spears','Author and mother of Britney',
                      NULL,'2011-12-01 20:51:59','2011-12-01 20:51:59');
Author saved.
Executing: UPDATE `Authors` SET `name`='Lynne Spears',
           `biography`='Author and mother of Britney',`id`=3,
           `createdAt`='2011-12-01 20:51:59',
           `updatedAt`='2011-12-01 20:51:59' WHERE `id`=3
Author & Book Relation created
Executing: SELECT * FROM `AuthorsBooks` WHERE `AuthorId`=3;
Executing: INSERT INTO `AuthorsBooks` (`AuthorId`,`BookId`,`createdAt`,`updatedAt`)
           VALUES (3,3,'2011-12-01 20:51:59','2011-12-01 20:51:59');

PostgreSQL

PostgreSQL is an object-oriented RDBMS originating from the University of California, Berkeley. The project was started by professor and project leader Michael Stonebraker as a successor to his earlier Ingres database system, and from 1985 to 1993 the Postgres team released four versions of the software. By the end of the project, the team was overwhelmed by support and feature requests from its growing number of users. After the Berkeley run, open source developers took over the project, replacing the original QUEL language interpreter with an SQL language interpreter and renaming the project to PostgreSQL. Since the first release of PostgreSQL 6.0 in 1997, the database system has gained a reputation as a feature-rich distribution that is especially friendly to users coming from an Oracle background.

Installation

A production-ready client for PostgreSQL, used by large sites such as Yammer.com, can be downloaded from the npm repository, as shown here:

npm install pg

pg_config is required. It can be found in the libpq-dev package.

Selection

Example 6-30 assumes you have created a database called upandrunning and granted permission to user dev with password dev.

Example 6-30. Selecting data with PostgreSQL

var pg = require('pg');

var connectionString = "pg://dev:dev@localhost:5432/upandrunning";
pg.connect(connectionString, function(err, client) {
  if (err) {
    console.log( err );
  } else {
    var sqlStmt = "SELECT username, firstname, lastname FROM users";
    client.query( sqlStmt, null, function(err, result) {
      if ( err ) {
        console.log(err);
      } else {
        console.log(result);
      }
      pg.end();
    });
  }
});

The output is:

{ rows:
   [ { username: 'bshilbo',
       firstname: 'Bilbo',
       lastname: 'Shilbo' } ] }

This is a big difference from the chainable methods used by the MySQL driver. When you’re working with PostgreSQL, it will be up to you to write your own SQL queries directly.

As in previous examples, calling the end() function closes the connection and allows Node’s event loop to end.

Insertion, updates, and deletion

When typing the SQL queries by hand, as we have seen, you might find it tempting to throw data values directly into the code through string concatenation, but wise programmers seek out methods that protect against SQL injection attacks. The pg library accepts parameterized queries, which should be leveraged everywhere that you use values taken from external sources (such as forms on websites). Example 6-31 demonstrates an insertion, and Examples 6-32 and 6-33 show updates and deletes, respectively.

Example 6-31. Inserting into PostgreSQL

var pg = require('pg');

var connectionString = "pg://dev:dev@localhost:5432/upandrunning";
pg.connect(connectionString, function(err, client) {
  if (err) {
    console.log( err );
  } else {
    var sqlStmt   = "INSERT INTO users( username, firstname, lastname ) ";
        sqlStmt  += "VALUES ( $1, $2, $3)";
    var sqlParams = ['jdoe', 'John', 'Doe'];
    var query = client.query( sqlStmt, sqlParams, function(err, result) {
      if ( err ) {
        console.log(err);
      } else {
        console.log(result);
      }
      pg.end();
    });
  }
});

The output is:

{ rows: [], command: 'INSERT', rowCount: 1, oid: 0 }

The query command accepts the SQL statement in the first parameter, and an array of values in the second parameter. Whereas the MySQL driver used question marks for the parameter values, PostgreSQL uses numbered parameters. Numbering the parameters gives you a lot of control over how variables are constructed.

Example 6-32. Updating data in PostgreSQL

var pg = require('pg');

var connectionString = "pg://dev:dev@localhost:5432/upandrunning";
pg.connect(connectionString, function(err, client) {
  if (err) {
    console.log( err );
  } else {
    var sqlStmt   = "UPDATE users "
                  + "SET firstname = $1 "
                  + "WHERE username = $2";
    var sqlParams = ['jane', 'jdoe'];
    var query = client.query( sqlStmt, sqlParams, function(err, result) {
      if ( err ) {
        console.log(err);
      } else {
        console.log(result);
      }
      pg.end();
    });
  }
});

Example 6-33. Deleting from PostgreSQL

var pg = require('pg');

var connectionString = "pg://dev:dev@localhost:5432/upandrunning";
pg.connect(connectionString, function(err, client) {
  if (err) {
    console.log( err );
  } else {
    var sqlStmt   = "DELETE FROM users WHERE username = $1";
    var sqlParams = ['jdoe'];
    var query = client.query( sqlStmt, sqlParams, function(err, result) {
      if ( err ) {
        console.log(err);
      } else {
        console.log(result);
      }
      pg.end();
    });
  }
});

The best content for your career. Discover unlimited learning on demand for around $1/day.