Databases in Node.js - Speaker Deck

Slide 1

Slide 1 text

! 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

Slide 2

Slide 2 text

PostgreSQL, MongoDB, Redis

Slide 3

Slide 3 text

PostgreSQL, MongoDB, Redis

Slide 4

Slide 4 text

npm install node-postgres non-blocking, pure JavaScript and native libpq https://github.com/brianc/node-postgres

Slide 5

Slide 5 text

CREATE TABLE episodes ( id integer NOT NULL, title character varying(255), description text );

Slide 6

Slide 6 text

! ! ! , 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;

Slide 7

Slide 7 text

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 =

Slide 8

Slide 8 text

prepared statement better performance and safer queries

Slide 9

Slide 9 text

Slide 10

Slide 10 text

}); 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;

Slide 11

Slide 11 text

extracting helper function reducing duplication and facilitating maintenance

Slide 12

Slide 12 text

! ! ! ! ! ! !   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']);

Slide 13

Slide 13 text

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); });

Slide 14

Slide 14 text

! ! ! 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';

Slide 15

Slide 15 text

Slide 16

Slide 16 text

extracting node module leveraging node’s module system to create components

Slide 17

Slide 17 text

! ! ! ! ! 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;

Slide 18

Slide 18 text

! ! 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');

Slide 19

Slide 19 text

Express app demo

Slide 20

Slide 20 text

http://xkcd.com/327/

Slide 21

Slide 21 text

npm install knex query builder for PostgreSQL, MySQL and SQLite3 https://github.com/tgriesser/knex

Slide 22

Slide 22 text

! ! ! Knex.PG = Knex.initialize({ client: 'pg', connection: { database: 'node_episodes' } }); // When the app starts var Knex = require('knex');

Slide 23

Slide 23 text

! ! ! var episodes = Knex('episodes').select(); episodes.then(function(results) { console.log(results); }); // elsewhere, to use the client .PG var Knex = require('knex') ;

Slide 24

Slide 24 text

npm install bookshelf ORM for PostgreSQL, MySQL and SQLite3 https://github.com/tgriesser/bookshelf

Slide 25

Slide 25 text

https://github.com/TryGhost/Ghost

Slide 26

Slide 26 text

// 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;

Slide 27

Slide 27 text

! ! ! ! Episodes.forge().fetch().then(function(result){ console.log(result.models); }); var Episodes = Bookshelf.Collection.extend({ tableName: 'episodes' });

Slide 28

Slide 28 text

! ! ! ! ! ! episodes.fetch().then(function(result){ console.log(result.models); }); var Episode = Bookshelf.Model.extend({ tableName: 'episodes' }); = Bookshelf.Collection.forge([], { model: Episode }); var episodes

Slide 29

Slide 29 text

! ! ! ! ! ! episodes.fetch().then(function(result){ console.log(result.models); }); = Episode.collection(); var Episode = Bookshelf.Model.extend({ tableName: 'episodes' }); var episodes

Slide 30

Slide 30 text

bookshelf with Express

Slide 31

Slide 31 text

No content

Slide 32

Slide 32 text

CREATE TABLE episodes ( id integer NOT NULL, title character varying(255), description text );

Slide 33

Slide 33 text

CREATE TABLE comments ( id integer NOT NULL, episode_id integer NOT NULL, description text );

Slide 34

Slide 34 text

// app.js var Bookshelf = require('bookshelf'); Bookshelf.PG = Bookshelf.initialize({ client: 'pg', connection: { database: 'node_episodes' } }); ! ...

Slide 35

Slide 35 text

// 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'); } );

Slide 36

Slide 36 text

Slide 37

Slide 37 text

  ! { ? }); var Episode = Bookshelf.Model.extend({ }, var Bookshelf = require('bookshelf').PG; // models/index.js ?

Slide 38

Slide 38 text

! tableName: 'episodes', ! comments: function () { return this.hasMany(Comment); } } ... var Episode = Bookshelf.Model.extend({ }, // models/index.js

Slide 39

Slide 39 text

{ 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

Slide 40

Slide 40 text

// models/index.js var Comment = Bookshelf.Model.extend({ tableName: 'comments' }); ! module.exports = { Episode: Episode, Comment: Comment }

Slide 41

Slide 41 text

Express app demo

Slide 42

Slide 42 text

PostgreSQL, MongoDB, Redis

Slide 43

Slide 43 text

npm install mongoose elegant mongodb object modeling for node.js https://github.com/learnboost/mongoose/

Slide 44

Slide 44 text

mongoose with Express

Slide 45

Slide 45 text

var mongoose = require('mongoose'); var connectionString = 'mongodb://localhost:27017/node_episodes'; mongoose.DB = mongoose.createConnection(connectionString); ! ... // app.js

Slide 46

Slide 46 text

// 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'); } );

Slide 47

Slide 47 text

Episode title description comments description rating Comment

Slide 48

Slide 48 text

No content

Slide 49

Slide 49 text

// 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 }); } ); };

Slide 50

Slide 50 text

// models/index.js var mongoose = require('mongoose'); ! var EpisodeSchema = new mongoose.Schema({ title: String, description: String !       }); ... , comments: [{ description: String, rating: { type: Number, max: 5 } }]

Slide 51

Slide 51 text

... 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

Slide 52

Slide 52 text

var Episode = mongoose.DB.model('Episode', EpisodeSchema); ! module.exports = { Episode: Episode }; ... // models/index.js

Slide 53

Slide 53 text

// 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'); }) }

Slide 54

Slide 54 text

Express app demo

Slide 55

Slide 55 text

PostgreSQL, MongoDB, Redis

Slide 56

Slide 56 text

http://redis.io

Slide 57

Slide 57 text

pub/sub

Slide 58

Slide 58 text

// File: client1.js var redis = require('redis'), client = redis.createClient(), clientListener = redis.createClient(); ...

Slide 59

Slide 59 text

...    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'); ! ...

Slide 60

Slide 60 text

! ... ! ! clientListener.on('message', function (channel, message) { }); clientListener.subscribe('arya');

Slide 61

Slide 61 text

Slide 62

Slide 62 text

// File: client2.js var redis = require('redis'), client = redis.createClient(); // $ node client2 var victim = process.argv[2]; // Kills that character client.publish('arya', victim, function () { client.end(); });

Slide 63

Slide 63 text

redis client demo

Slide 64

Slide 64 text

http://www.meetup.com/orlando-node-js/

Slide 65

Slide 65 text

https://twitter.com/node_orlando

Slide 66

Slide 66 text

! 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