[NJS] Database handling #2

:  ~ 4 min read

Last time I was talking about the DbConfig model and the fetchPosts function, so let's dive in. First, a few examples of using the config:

const Db = require('../lib/db')
const DbConfig = require('../models/dbconfig')

// Fetching one post
const config = new DbConfig()
config.fields      = ['link']
config.fieldValues = [req.baseUrl.substring(1)] // The path without the initial `/`
config.limit       = 1

// Search, meaning to fetch based on a query
const config     = new DBConfig()
config.searching = true // Special logic for creating the query command
config.limit     = 0 // All
config.fields    = ['body', 'title']
// Create an array of strings with matches between quotes as single items, 
// and matches outside quotes as multiple items
// "one item" several items -> ['one item', 'several', 'items']
config.fieldValues = req.query.query
  .match(/\"(.*?)\"|(\w+)/g)
  .map(function(match) {
    return match.replace(/"/g, '')
  })

// Archive, meaning to fetch all posts, but only the title, link and datetime to group them
const config   = new DbConfig()
config.limit   = 0
config.columns = 'title, link, datetime' 
// Not an array, like fields and fieldValues,
// because they go together, anyway: 'SELECT title, link, datetime FROM posts'

As for the fetchPosts, it's a bit tricky, but not that bad. First, let's introduce a new object, Result, to use as the result for fetching posts:

DbResult = function() {
  this.posts = []
  this.totalPosts = 0
}

And then, the main course:

Db.fetchPosts = function(config) {
  // As I said, columns is used in one go
  let query   = 'SELECT ' + config.columns + ' FROM posts'
  // These two default to null, so if they contain anything, we are going to do add WHERE clauses to the query.
  // For the archive we fetch them all, for example
  const queried = config.fields && config.fieldValues
  if (queried) {
    // If we are searching, we are going to have more than one WHERE clause
    if (config.searching) {
      query += ' WHERE '
	
      // Create a string that looks like 
      // WHERE field1 LIKE '%value1%' OR field1LIKE '%value2%' OR field2 LIKE '%value1%' OR field2 LIKE '%value2%'
      config.fields.forEach(function(field) {
        config.fieldValues.forEach(function(value) {
          query += field + ' LIKE '
          query += '\'%' + value + '%\''
          query += ' OR '
        })
      })
      
      // Remove the last ' OR '
      query = query.slice(0, -4)
    }
    // This is basically for fetching one post by its link
    else {
      query += ' WHERE ' + config.fields[0] + ' = \'' + config.fieldValues[0] + '\''
    }
  }
  // Then we order the query, default by the datetime field, and ascending
  query += ' ORDER BY ' + config.orderBy + ' ' + config.orderDirection
  
  return new Promise(function(resolve, reject) {
    pg.connect(dbURL, function(err, client, done) {
      client.query(query, function(err, result) {
        done()

        if (err) {
          reject(new Error(err.message + ' ' + err.id))
          return
        }

        const DbResult = require('../models/dbresult')
        const res      = new DbResult()
        res.totalPosts = result.rows.length
        let posts      = result.rows.reverse()

        // When updating all posts must be fetched, otherwise just the ones that are not in the future. 
        // Sometimes I write posts now, but date them in the future,
        // but I also want to be able to access them by URL, to be able to see how they look before getting live.
        if (!config.updating && config.limit != 1) {
          posts = posts.filter(function(rawPost) {
          // Just a helper function that transforms YYYY-MM-dd into a Date object
          const date = Post.dateFromDateTime(rawPost.datetime)
            return date && date < new Date()
          })
        }
          
        // If the limit is 0, then we asked for all posts
        if (config.limit) {
          posts = posts.slice(config.offset, config.offset + config.limit)
        }

        res.posts = posts.map(function(rawPost) {
          let body = rawPost.body

          // If the user is searching, I'd like to mark his searched terms throughout the results
          if (queried && config.searching) {
            // This will create a regex like /value1|value2/, matching any string that equals any value
            let pattern = ''
            config.fieldValues.forEach(function(value) {
              pattern += value + '|'
            })
            // Remove the last '|'
            pattern   = pattern.slice(0, -1)
            // And make the regex global and insensitive
            const regex = new RegExp(pattern, 'gi')

            // Wrap the value between a mark of class 'search', to style it
            body = body.replace(
              regex,
              '<mark class=\'search\'>\$&</mark>'
            )

            let lines = []
            // Split the body into lines
            body.split('\n').forEach(function(line) {
              let _line = line
              // Search for hrefs, asset links and images
              if (line.indexOf('<a href=<') ||
                  line.indexOf('/assets/') ||
                  line.indexOf('<img src=')) {
                  // And remove the occurrences of the search marks on that line,
                  // otherwise links and images will break
                _line = _line.replace(/<\/mark>/g, '')
                _line = _line.replace(/<mark class='search'>/g, '')
              }
              lines.push(_line)
            })
            // Then join the lines again
            body = lines.join('\n')
          }

          return new Post(
            rawPost.title,
            body,
            rawPost.readingtime,
            rawPost.datetime,
            rawPost.modified || '',
            rawPost.link
          )
        })

        resolve(res)
      })
    })
  })
}

As for using it, here's my post route:

const router   = require('express').Router()
const NotFound = require('./not-found')
const Db = require('../lib/db')

// This resolves to http://rolandleth.com/we-are-here
router.get('/', function(req, res) {
  const config       = new DB.Config()
  // Query by the link field
  config.fields      = ['link']
  // Use the path without the starting '/' as the value
  config.fieldValues = [req.baseUrl.substring(1)]
  // Fetch only 1
  config.limit       = 1

  Db.fetchPosts(config).then(function(data) {
    if (data.posts.length == 0) {
      // Since I need this in several places, I just created a helper to render the 404 page
      NotFound.show(res)
      return
    }

    // Get the first post and render it
    const post = data.posts[0]
    res.render('index', {
      posts: data.posts, // Since it's the same partial for one post, or several, it expects an array
      title: post.title,
      page: 1,
      totalPosts: 1,
      metadata: post.title
    })
  }).catch(function() {
    NotFound.show(res)
  })
})

I'm sure I could improve a lot of code, so if you have any suggestions (or questions), I'd be more than happy to hear from you @rolandleth.