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
 a(id="changeSearchLink",href="search?searchString=a&pageSize=5") Change Search
    th Name
    th Address
    th City, State
    th Zip
    th Action
   each hotel in hotels
     td= hotel.address
      a(href="hotels/#{}") View Hotel

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


From → node.js

Leave a Reply

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

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

Google+ photo

You are commenting using your Google+ 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 )


Connecting to %s

%d bloggers like this: