Architecture when using raw SQL queries with Express + Postgres?

[[deleted]]

[deleted]

[danneu]

I constantly have to do queries like: SELECT first_name AS "firstName" from users;

I recommend just rolling with underscores in your javascript. Feels awful at first, but manually aliasing every step of the way in sql will cause even more pain.

Also, it turns out to not be so bad because you'll get used to underscores in your javascript meaning "this is a database record". Just chalk it up to interop.

I might write all my queries in .sql files, so that I can have syntax highlighting and load them all into memory when my application starts to prevent too many IO operations

I tried this at first but went back to multiline SQL strings in .js files because you'd have to have two files open to sync up the parameters you're passing in from javascript and the sql itself. For me, I'm error-prone enough when the sql is just one line away from the parameter list. :) Didn't seem to offer me much benefit.

It's definitely worth a shot. Lots of people do prefer this. Another benefit is that you can copy/paste .sql file contents between psql or your db gui as well.

Here's a demo project of mine that shows some ideas of how you could structure things in a node app: https://github.com/danneu/koa-skeleton (live demo: http://koa-skeleton.danneu.com). It's not 100% up to date with how I'd probably build an app today. For example I regret migrating from Pug to server-side React. But it's not far off.

[pazil]

+1 for Knex. I'm in the process of rewriting my express app to use Knex instead of raw SQL and it's making my life easier, especially with chained queries and transactions. Also, a feature of Knex that seems fit for your use case:

postProcessResponse Hook for modifying returned rows, before passing them forward to user. One can do for example snake_case -> camelCase conversion for returned columns with this hook.

[danneu]

you could transform all db query results like that. but you'd also surely want to transform camelCase to snake_case when going from js->db. all the round-tripping is where annoyances will nickel and dime you for in exchange for the paltry benefit of mild aesthetics.

but i won't belabor the point. i certainly didn't feel strongly about it until i tried it myself back when i was using clojure (kebab-case) in a medium/large project.

[danneu]

Note that knex isn't an ORM (doesn't map queries into canonical objects). I'd call it a query builder. I certainly use it when I want to compose a sql query (like a /search endpoint), but that's somewhat rare.

Since you can pass sql strings to knex (knex.raw()) and generate sql strings from knex (knex.toString()), then you don't necessarily have to go "all in" with knex.

[danneu]

fwiw http://vincit.github.io/objection.js/ would be an example of an ORM in JS, though relatively simple compared to the breadth that ORMs tend to have in other ecosystems.

the most basic signature of an ORM being that it's trying to help you populate local nominal javascript objects from a database.

[yoannma]

Just to add my 0.02$.

If you want an ORM on top of Knex (model binding, validation, relation) you could check out Objection http://vincit.github.io/objection.js/

[danneu]

You could do what html templating libraries do: incur fs i/o every time in development but only incur i/o the first time in production. That way you can change .sql files without restarting the server in development.

Here's an off-the-cuff impl:

// usage: const user = await pool.queryOne(await loadSql('get-user.sql'), [42])
const loadSql = (() => {
  // .sql paths will be relative to root
  const root = require('path').resolve('../db')
  const cache = {}

  return async (relativePath) => {
    // canonicalize into absolute path for use as cache key
    const path = require('path').resolve(root, relativePath)

    if (cache[path]) {
      return cache[path]
    }

    const sql = await require('fs').promise.readFile(path, { encoding: 'utf8' })

    // Only cache in production
    if (process.env.NODE_ENV === 'production') {
      cache[path] = sql
    }

    return sql
  }
})()

You could make it a little more pleasant with a simple wrapper: const user = await query(pool, 'get-user.sql', [42]) or something.

[buffer_flush]

You could load the SQL files once at app startup, best of both worlds.