You will learn how to develop apps using node.js and MySQL.
nodejs 101 (File Persistence), basic familiarity with databases and MySQL.
For this project, we will be storing contacts in a table in MySQL. We will keep the schema simple as follows:
|
CREATE SCHEMA nodejs; CREATE TABLE `nodejs`.`contacts` ( `id` INT NOT NULL AUTO_INCREMENT, `firstname` VARCHAR(45) NOT NULL , `lastname` VARCHAR(45) NOT NULL , `phone` VARCHAR(45) NULL , PRIMARY KEY (`id`) ); |
We have a contacts table (in nodejs schema) with id as the primary key and columns for firstname, lastname and phone.
We will be using the nodejs mysql module to connect to mysql and execute queries. Detailed documentation of this module can be found here: https://github.com/felixge/node-mysql/.
The simplest form of .query() is .query(sqlString, callback), where a SQL string is the first argument and the second is a callback:
|
connection.query('SELECT phone FROM `contacts` WHERE `firstname` = "Bill"', function (error, results, fields) { // error will be an Error if one occurred during the query // results will contain the results of the query // fields will contain information about the returned results fields (if any) }); |
The second form .query(sqlString, values, callback) comes when using placeholder values (see escaping query values):
|
connection.query('SELECT phone FROM `contacts` WHERE `firstname` = ?', ['Bill'], function (error, results, fields) { // error will be an Error if one occurred during the query // results will contain the results of the query // fields will contain information about the returned results fields (if any) });
|
Node.js provides an async, non-blocking I/O model. This means that any I/O operation (such as reading from a file, writing to a database, or making a network call) will not block the calling thread. Instead, the calling thread has to provide a function callback that will be invoked by node.js once the I/O operation is complete.
The following diagram from http://www.webdevelopmenthelp.net/wp-content/uploads/2014/04/SyncVsAsync.png indicates the differences between synchronous and asynchronous processing.
In the case of synchronous processing, the thread is blocked until the operation is complete and cannot be used to do any other work. In the case of asynchronous model, the thread is free to process other requests. Node.js will issue a callback once the processing is complete.
This means that any code in your application that needs to process the database results needs to be implemented inside the query() function call as a callback as shown below:
|
connection.query("select id, firstname as firstName, lastname as lastName, phone from contacts", function(err, result){ //access the database results in the callback. console.log(result); }); |
In this tutorial, we will build a simple node.js app that inserts and reads records from a mysql database.
First, import the required mysql module and create a connection to the db server. In the code below, we assume mysql is running on default port (3306) and connects to the nodejs schema.
|
var mysql = require('mysql'); var theConnection = mysql.createConnection({ host: "localhost", user: "root", password: "root", database: "nodejs" }); |
Next, we will explore a couple of ways of inserting records and examining the output. In the first example, we specify the column name/value pairs that need to be inserted. In the callback, we can access the id of the contact created by accessing result.insertId property.
|
var contactId = null; theConnection.query("insert into contacts SET ?", {firstname: "Bill", lastname: "Gates", phone: "23002300"},
function(err, result){ if(err){ console.log(err); } console.log("Contact insert result: ", result); contactId = result.insertId; }); |
You can run the above code by doing node tutorial.js – The http server will listen on port 3000 and the handleRequest function will be called when any incoming request is returned. For now, the handleRequest method simply returns “Hello World”.
Another way of creating a record is shown below. You can pass a javascript object whose property names match the table column names to the query() call as shown below.
|
var c = new Object(); c.firstname = "Steve"; c.lastname = "Jobs"; c.phone = "23002300"; theConnection.query("insert into contacts SET ?", [c], function(err, result){ console.log("Contact insert result: ", result); contactId = result.insertId; }); |
Lastly, we show how to select records from the contacts table. As you can see below, specify the sql query string with columns and the contactId as a dynamic parameter. The result object is an array of matching records.
|
theConnection.query("select id, firstname as firstName, lastname as lastName, phone from contacts WHERE id = ?", [contactId], function(err, result){ if(err){ console.log(err); } console.log(result) }); |
|
theConnection.query("insert into contacts SET ?", [c],
function(err, result){ if(err){ console.log(err); process.exit(1); } console.log("Contact insert result: ", result); contactId = result.insertId;
console.log("selecting contact with id: " + contactId); //execute the select query inside the callback of the insert callback. theConnection.query("select id, firstname as firstName, lastname as lastName, phone from contacts WHERE id = ?", [contactId], function(err, result){ if(err){ console.log(err); } console.log(result); process.exit(0); }); });
|
You can look at the final code in Tutorial.js.
You will implement a way to add a new contact to the database in this activity
You will implement a way to read a contact row with specified id in this activity
You will implement a way to read contacts matching a specified set of criteria in this activity.
You will implement a way to update a specified contact with new values in this activity.
You will implement a way to delete a specified record
You will write your code into DbPersistence.js