Databases in Node.js

This presentation covers interacting with the PostgreSQL, MongoDB and Redis data stores from Node.js.

1. ! with Node.js working with drivers and ORMs Carlos Souza (@caike)
 Orlando Node.js User Group March 2014 DataBases https://github.com/node-orlando/march2014 source code for examples
2. PostgreSQL, MongoDB, Redis
3. PostgreSQL, MongoDB, Redis
4. npm install node-postgres non-blocking, pure JavaScript and native libpq https://github.com/brianc/node-postgres
5. CREATE TABLE episodes ( id integer NOT NULL, title character varying(255), description text );
6. ! ! ! , function (err, result) { if(err) throw err;
 done(); ! 
 }); console.log('Results: ' + result.rowCount); console.log(result.rows); var pg = require('pg'); var settings = 'pg://localhost:5432/node_episodes'; pg.connect(settings, function (err, client, done) { }); client.query('SELECT * FROM episodes' if(err) throw err;
7. pg.connect(settings, function (err, client, done) { }); var pg = require('pg'); var settings = 'pg://localhost:5432/node_episodes'; client.query('SELECT * FROM episodes'); query.on('row', function (row, result) { result.addRow(row); }); query.on('end', function (result) { done();
 console.log('Results: ' + result.rowCount); console.log(result.rows); }); if(err) throw err; var query =
8. prepared statement better performance and safer queries
9. pg.connect(settings, function (err, client, done) { }); var pg = require('pg'); var settings = 'pg://localhost:5432/node_episodes'; client.query('SELECT * FROM episodes'); query.on('row', function (row, result) { result.addRow(row); }); query.on('end', function (result) { done();
 console.log('Results: ' + result.rowCount); console.log(result.rows); }); if(err) throw err; var query =
10. }); var query = client.query('SELECT * FROM episodes WHERE title = $1', ['Winter is Coming']); var pg = require('pg'); var settings = 'pg://localhost:5432/node_episodes'; pg.connect(settings, function (err, client, done) { query.on('row', function (row, result) { result.addRow(row); }); query.on('end', function (result) { done();
 console.log('Results: ' + result.rowCount); console.log(result.rows); }); if(err) throw err;
11. extracting helper function reducing duplication and facilitating maintenance
12. ! ! ! ! ! ! ! 
 query.on('row', function(row, result) { result.addRow(row); }); query.on('end', function(result) { done(); console.log('Results: ' + result.rowCount); console.log(result.rows); }); pg.connect(settings, function (err, client, done) { if(err) throw err; ! 
 var pg = require('pg'); var settings = 'pg://localhost:5432/node_episodes'; }); var query = client.query('SELECT * FROM episodes WHERE title = $1', ['Winter is Coming']);
13. runQuery('SELECT * FROM episodes', [], function (rows) { console.log('Results: ' + rows.length); console.log(rows); }); ! runQuery("SELECT * FROM episodes where title ILIKE $1", 
 ['%snow'], function (rows) { 
 console.log('Results: ' + rows.length); console.log(rows); });
14. ! ! ! function runQuery(queryString, args, cb){ ! ! ! ! ! ! ! ! ! } pg.connect(settings, function (err, client, done) { if(err) throw err; client.query(queryString, args, function (err, result) { if(err) throw err; }); }); done(); cb(result.rows); var pg = require('pg'); var settings = 'pg://localhost:5432/node_episodes';
15. runQuery('SELECT * FROM episodes', [], function (rows) { console.log('Results: ' + rows.length); console.log(rows); }); ! runQuery("SELECT * FROM episodes where title ILIKE $1", 
 ['%snow'], function (rows) { 
 console.log('Results: ' + rows.length); console.log(rows); });
16. extracting node module leveraging node’s module system to create components
17. ! ! ! ! ! db.query = function (queryString, args, cb) { ! ! ! ! ! ! ! ! ! ! } ! // File: db.js var pg = require('pg'); var settings = 'pg://localhost:5432/node_episodes'; var db = {}; pg.connect(settings, function (err, client, done) { if(err) throw err; ! client.query(queryString, args, function (err, result) { if(err) throw err; ! done(); cb(result.rows); }) }); module.exports = db;
18. ! ! db.query('SELECT * FROM episodes', [], function (rows) { console.log('Results: ' + rows.length); console.log(rows); }); ! db.query('SELECT * FROM episodes WHERE title ILIKE $1',
 [‘%snow’], function (rows) { 
 console.log('Results: ' + rows.length); console.log(rows); }); var db = require('./db');
19. Express app demo
20. http://xkcd.com/327/
21. npm install knex query builder for PostgreSQL, MySQL and SQLite3 https://github.com/tgriesser/knex
22. ! ! ! Knex.PG = Knex.initialize({ client: 'pg', connection: { database: 'node_episodes' } }); // When the app starts var Knex = require('knex');
23. ! ! ! var episodes = Knex('episodes').select(); episodes.then(function(results) { console.log(results); }); // elsewhere, to use the client .PG var Knex = require('knex') ;
24. npm install bookshelf ORM for PostgreSQL, MySQL and SQLite3 https://github.com/tgriesser/bookshelf
25. https://github.com/TryGhost/Ghost
26. // When the app starts var Bookshelf = require('bookshelf'); ! Bookshelf.PG = Bookshelf.initialize({ client: 'pg', connection: { database: 'node_episodes' } }); ! // elsewhere, to use the client
 var Bookshelf = require('bookshelf').PG;
27. ! ! ! ! Episodes.forge().fetch().then(function(result){ console.log(result.models); }); var Episodes = Bookshelf.Collection.extend({ tableName: 'episodes' });
28. ! ! ! ! ! ! episodes.fetch().then(function(result){ console.log(result.models); }); var Episode = Bookshelf.Model.extend({ tableName: 'episodes' }); = Bookshelf.Collection.forge([], { model: Episode }); var episodes
29. ! ! ! ! ! ! episodes.fetch().then(function(result){ console.log(result.models); }); = Episode.collection(); var Episode = Bookshelf.Model.extend({ tableName: 'episodes' }); var episodes
30. bookshelf with Express
32. CREATE TABLE episodes ( id integer NOT NULL, title character varying(255), description text );
33. CREATE TABLE comments ( id integer NOT NULL, episode_id integer NOT NULL, description text );
34. // app.js var Bookshelf = require('bookshelf'); Bookshelf.PG = Bookshelf.initialize({ client: 'pg', connection: { database: 'node_episodes' } }); ! ...
35. // app.js ...
 var episode = require('./routes/episode'); 
 app.get('/episodes', episode.list); app.get('/episodes/new', episode.new); app.post('/episodes', episode.create); app.get('/episodes/:id/edit', episode.edit); app.patch('/episodes/:id', episode.update); app.del('/episodes/:id', episode.destroy); ! 
 ! ! http.createServer(app).listen(app.get('port'),
 function(){ console.log('Express server running'); } );
36. var Episode = require('./../models').Episode; ! exports.list = function(req, res){ Episode.findAll(req.param('keyword'), function (episodes) { return res.render('episodes', { episodes: episodes }); }) }; // routes/episode.js
37. 
 ! { ? }); var Episode = Bookshelf.Model.extend({ }, var Bookshelf = require('bookshelf').PG; // models/index.js ?
38. ! tableName: 'episodes', ! comments: function () { return this.hasMany(Comment); } } ... var Episode = Bookshelf.Model.extend({ }, // models/index.js
39. { findAll: function (keyword, cb) { ! ! ! ! ! ! ! ! ! ! ! ! ! } }); var Episode = Bookshelf.Model.extend({ }, ... ! ! if(keyword){ var match = '%' + keyword + '%'; episodes.query(function (qb) { qb.where('title', 'ilike', match). orWhere('description', 'ilike', match); }); } ! ! ! var episodes = this.collection().query('orderBy', 'id', 'ASC'); episodes.fetch({ withRelated: 'comments' }).then(function(result){ cb(result.toJSON()); }); // models/index.js
40. // models/index.js var Comment = Bookshelf.Model.extend({ tableName: 'comments' }); ! module.exports = { Episode: Episode, Comment: Comment }
41. Express app demo
42. PostgreSQL, MongoDB, Redis
43. npm install mongoose elegant mongodb object modeling for node.js https://github.com/learnboost/mongoose/
44. mongoose with Express
45. var mongoose = require('mongoose'); var connectionString = 'mongodb://localhost:27017/node_episodes'; mongoose.DB = mongoose.createConnection(connectionString); ! ... // app.js
46. // app.js ...
 var episode = require('./routes/episode');
 app.get('/episodes', episode.list); app.get('/episodes/new', episode.new); app.post('/episodes', episode.create); app.get('/episodes/:id/edit', episode.edit); app.patch('/episodes/:id', episode.update); app.del('/episodes/:id', episode.destroy); http.createServer(app).listen(app.get('port'),
 function(){ console.log('Express server running'); } );
47. Episode title description comments description rating Comment
49. // routes/episode.js var Episode = require('./../models').Episode ! exports.list = function(req, res){ Episode.findAll(req.param('keyword'), function (err, episodes) { if(err) throw err; return res.render('episodes', { episodes: episodes }); } ); };
50. // models/index.js var mongoose = require('mongoose'); ! var EpisodeSchema = new mongoose.Schema({ title: String, description: String ! 
 
 
 }); ... , comments: [{ description: String, rating: { type: Number, max: 5 } }]
51. ... EpisodeSchema.static('findAll', function(keyword, cb) { ! if(keyword){ var match = new RegExp(keyword, 'i'); return this.find({ $or: [{ title: match}, { description: match }] }).exec(cb); }else{ return this.find().exec(cb); } }); ... // models/index.js
52. var Episode = mongoose.DB.model('Episode', EpisodeSchema); ! module.exports = { Episode: Episode }; ... // models/index.js
53. // routes/episode.js exports.edit = function (req, res) { Episode.findById(req.params.id, function(err, episode) { return res.render('episode', { episode: episode }); }); } ! exports.update = function (req, res) { Episode.findByIdAndUpdate(req.params.id, req.body.episode, function (err, episode) { return res.redirect('/episodes'); } ); } ! exports.destroy = function (req, res) { Episode.findByIdAndRemove(req.params.id, function () { return res.redirect('/episodes'); }) }
54. Express app demo
55. PostgreSQL, MongoDB, Redis
56. http://redis.io
57. pub/sub
58. // File: client1.js var redis = require('redis'), client = redis.createClient(), clientListener = redis.createClient(); ...
59. ...
 
 client.sadd('characters', 'Eddard Stark'); client.sadd('characters', 'Cersei Lannister'); client.sadd('characters', 'Robert Baratheon'); client.sadd('characters', 'Khal Drogo'); client.sadd('characters', 'Jon Snow'); client.sadd('characters', 'Littlefinger'); ! ...
60. ! ... ! ! clientListener.on('message', function (channel, message) { }); clientListener.subscribe('arya');
61. var character = message; ! client.srem('characters', character, function(err) { if(err) throw err; ! client.smembers('characters', function (err, members) { console.log('Remainig characters: ' + members); }) }); clientListener.on('message', function (channel, message) { });
62. // File: client2.js var redis = require('redis'), client = redis.createClient(); // $ node client2 <character> var victim = process.argv[2]; // Kills that character client.publish('arya', victim, function () { client.end(); });
63. redis client demo
64. http://www.meetup.com/orlando-node-js/
65. https://twitter.com/node_orlando
66. ! with Node.js working with drivers and ORMs Carlos Souza (@caike)
 Orlando Node.js User Group March 2014 DataBases https://github.com/node-orlando/march2014 source code for examples