There are still many good reasons to use a traditional database with SQL, and Node interfaces with popular open source choices.
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.
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.
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) );
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' }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.
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:
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 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:
Set up the entity schemas.
Synchronize the schemas with the actual database.
Create and save a
Bookobject.Create and save an
Authorobject.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 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.
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.
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.
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();
});
}
});Get Node: Up and Running now with the O’Reilly learning platform.
O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.
