Writing good server code with Knex or other SQL DB libs


#1

Are there any open source projects that serve as good demonstrations good server code with Knex, or some other SQL DB libs? I’m looking for something with some degree of complexity, like Telescope/Nova. I am particularly interested in GraphQL, although GraphQL is new, so any open source projects will do.

With limited experience using Javascript on the server, it can be tricky knowing what is the “right way” to write server code.

  1. many frameworks like ROR use “models”. Since javascript is not strictly object oriented, you can use a library like knex which is much simpler than ORMs, but sometimes this can feel quite unstructured.
  2. async code can be really tricky. When you have business logic that needs to fetch things from different tables, and needs one result before it can fetch the next one, this code can be hard to follow (regardless of whether you use promise.then() syntax or async/await). But this is a problem inherent to the language, so probably nothing can fix this.

What data related libs do you use? How do you structure your code?

I’m also happy to share a little more about my experiences using Postres/Knex over the last couple months (spoiler: JSONB fields, while they sound cool for someone coming from MongoDB, are mostly a pain)


#2

Yeah after using SQL with Rails, using JS ORMs feels a bit like going back to the stone age.


#3

I made a very simple “model” abstraction, bascially some knex helper methods that looks like this:

export function createModel (tableName) {
  const table = tableName
  return (fields) => ({
    findOne (where) {
      return knex(table)
        .where(where)
        .limit(1)
        .select(fields)
        .then(first)
    },
    findAll (where = {}) {
      return knex(table)
        .where(where)
        .select(fields)
    },
    getById (id) {
      return this.findOne({id: id})
    },
    updateWhere (where, row) {
      return knex(table)
        .where(...where)
        .update(stringifyArrays(row), fields)
        .then(first)
        .catch(e => console.log('model.update error ', e))
    },
    updateById (id, row) {
      console.log('updateById ', id, row)
      return this.updateWhere(['id', '=', id], row)
    },
    create (row) {
      return knex(table)
        .insert(stringifyArrays(row), fields)
        .then(first)
        .catch(e => console.log('model.create error ', e))
    }
  })
}

Also adding to the above, if you’re using Postgres consider using arrays (aka nested tables) instead of JSONB, if you want to store nested data. I haven’t tried it yet but it looks better.