[NJS] Database handling #1

With Sinatra, I was using DataMapper as ORM (Object Relational Mapper) for Postgres, and I got a bit spoiled, because it makes things really easy. You first have to define your data mapping:

class Post
  include DataMapper::Resource
  # Set the number of characters for these types of fields, if the DB supports it
  DataMapper::Property::String.length(255)
  DataMapper::Property::Text.length(999999)
  property :id, Serial
  property :title, Text
  property :body, Text
  property :datetime, String
  property :modified, String
  property :link, String
end

And using it is really straightforward:

Post.first
# or with some fields as parameters, to narrow the query down
Post.first(link: 'node-js')
 
Post.create # Taking all fields as parameters
 
post = Post.first
post.destroy
# or
post.update # Taking the fields that need updating as parameters

Now, there are ORMs for Node.js, like Sequelize, but I went with a direct approach instead. First, I created a Post model:

function Post(title = '', body = '', readingTime = '', datetime = '', modified = '', link = '') {
  this.title = title
  this.body = body
  this.readingTime = readingTime
  this.datetime = datetime
  this.date = Post.dateFromDateTime(datetime) || ''
  this.modified = modified
  this.link = link
}
 
module.exports = Post
 
// Somewhere else in the app:
const post = Post(title: 'Title')
post.body = 'Body' ...

And a database manager, that will use the pg package:

const pg = require('pg')
 
function Db() {}
 
// Static functions
Db.createPosts = function(param1, param2) { }
Db.updatePost = function(param1, param2) { }
Db.deletePost = function(param1, param2) { }
Db.fetchPosts = function(param1, param2) { }
 
module.exports = Db

But it's already obvious we could improve upon the params of these functions, since they're usually the same, and, in time, some scenarios will surely end up needing more params that others will not. The answer was to create a config model, which will have default values for its properties:

DbConfig = function() {
  this.fields         = null # The fields to query by
  this.fieldValues    = null # The values to query by
  this.orderBy        = 'datetime' # The field to order by
  this.orderDirection = 'ASC' # The direction to order by
  this.limit          = process.env.PAGE_SIZE || 10 # The number of items to return
  # We'll see more here in future posts
}

Now, the fetchPosts will look like this:

Db.fetchPosts = function(config) {
  // We'll see later exactly what goes in here, but generally
  // config.param1, config.param2 are used instead of param1 and param2
}

And calling it will look like this:

const Db = require('../lib/db')
const DbConfig = require('../models/dbconfig')
const config = new DbConfig()
config.fields = ['link']
config.fieldValues = ['node-js']
config.orderBy = 'title'
 
Db.fetchPosts(config) ...

So, what about the actual implementation of fetchPosts?

Db.fetchPosts = function(config, completion) {
  let query = 'SELECT'...
  pg.connect(dbURL, function(err, client, done) {
    client.query(query, function(err, result) {
      done()
      // Do stuff with result
      completion(result / processedResult, err)
    })
  })
}
 
// Using it
Db.fetchPosts(config, function(result, err) {
  if (err) { 
    // handle failure 
    return
  }
  
  // Do stuff with result
})

While there's not much value in using Promises here, I still decided to do it, at least for learning purposes. I would do them injustice if I tried to go in depth about them, so I suggest reading about them a bit, if required, but the core idea behind promises is that a promise represents the result of an asynchronous operation. So, the above code would turn into:

Db.fetchPosts = function(config) {
  return new Promise(function(resolve, reject) {
    let query = 'SELECT'...
    pg.connect(dbURL, function(err, client, done) {
      client.query(query, function(err, result) {
        done()
        // Do stuff with result
        if (err) { reject (err) }
        else { resolve(result / processedResult) }
      })
    })
  })
}
 
// Using it
Db.fetchPosts(config).then(function(result) {
  // Do stuff with result
}).catch(function(err) {
  // Handle failure
})
// Promises would have really shined if we were to chain several then blocks before the final catch

Next time I will dive into the fetchPosts function, its uses, and how the Config model turned out to be really helpful.