Skip to content

node.js and databases

January 2, 2012

So – after creating a first node.js app with express templates, it is time to add database access to the node.js tagsobe hotel travel solution.

From this stack overflow, we decided to use sequelize:

$ npm install sequelize

We decided to perform all setup-related work directly in app.’s (perhaps not really a good idea…) First, we require the dependency:

var Sequelize = require("sequelize")

After that, we define the database connection as well as the entities:

var sequelize = new Sequelize('tagsobe', 'tagsobe', 'tagsobe', {
	  host: "localhost"
});
var Customer = sequelize.define('Customer', {
	username: Sequelize.STRING,
	password: Sequelize.STRING,
	name : Sequelize.STRING
}, {timestamps: false,freezeTableName: true});
var Hotel = sequelize.define('Hotel', {
	id: Sequelize.INTEGER,
	price: Sequelize.INTEGER,
	name: Sequelize.STRING,
	address: Sequelize.STRING,
	city: Sequelize.STRING,
	state: Sequelize.STRING,
	zip: Sequelize.STRING,
	country: Sequelize.STRING
}, {timestamps: false,freezeTableName: true});
sequelize.sync({force: true});

We prohibit the creation of additional createdAt and updatedAt columns and the pluralization of table names. The last line should actually create the tables. Restarting the server show no errors…. but wait – we did not even start the db server! Obviously, sequelize is not that good at error reporting?
Ok, so we go through the standard procedure of creating a MySQL database:

$ mysqladmin -u root create tagsobe
$ mysql -u root tagsobe -e "grant usage on *.* to tagsobe@localhost identified by 'tagsobe'"
$ mysql -u root tagsobe -e "grant all privileges on tagsobe.* to tagsobe@localhost"

Then we restart the server – et voila! The database tables are there:

mysql> explain Customers;
+-----------+--------------+------+-----+---------+----------------+
| Field     | Type         | Null | Key | Default | Extra          |
+-----------+--------------+------+-----+---------+----------------+
| username  | varchar(255) | YES  |     | NULL    |                |
| password  | varchar(255) | YES  |     | NULL    |                |
| name      | varchar(255) | YES  |     | NULL    |                |
| id        | int(11)      | NO   | PRI | NULL    | auto_increment |
+-----------+--------------+------+-----+---------+----------------+
Though not exactly as we defined – sequelize always adds an id column. Nevertheless we can import the seed data now:
mysql -u tagsobe -ptagsobe tagsobe < ???/import.sql
Now it is surprisingly simple to implement the hotel search:
app.get('/hotels', function(req, res){
	console.log("hotels invoked");
	var query = Hotel.findAll({where:["name like ?", "%"+req.param("searchString")+"%"]});
	query.on('success', function(result) {
		res.render('hotels', {title: 'Hotels', hotels: result});
	});
});
Here, for the first time, the inherently asynchronous nature of node.js and his friends show up explicitly: we have to add an event listener to the query which is called when the result from the database comes in. The result is then passed to the jade template, which looks quite well:
h1 Hotel Results
p
 a(id="changeSearchLink",href="search?searchString=a&pageSize=5") Change Search
p
div(id="hotelResults")
 table(class="summary")
  thead
   tr
    th Name
    th Address
    th City, State
    th Zip
    th Action
  tbody
   each hotel in hotels
    tr
     td= hotel.name
     td= hotel.address
     td= hotel.city
     td= hotel.zip
     td
      a(href="hotels/#{hotel.id}") View Hotel

That’s it! – Next, we have to add authentication.

Advertisements

From → node.js

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: