Continuing Frameworkless.js

Part 4: Let's save data to a database!

Recap of Last Time

We built a Node.js app utilising the built-in http.createServer
Starts an HTTP server and serves static files out of the ./public directory
We implemented dynamic routing with a handy easy to define syntax
Our routes use Handlebars template files to display content that's not static
We also wrote a super simple form data parser that will allow us to capture user data

A reminder what we're doing

Ever wanted to remind yourself of something, out of the blue? Well, we're building a quick tool to do that!

https://remind.ist

form parser package is released!

On NPM:

https://www.npmjs.com/package/@frameworkless/bodyparser

const { createServer } = require('http')
const { parseBody } = require('@frameworkless/bodyparser')

const requestHandler = async (request, response) => {
  const payload = await parseBody(request)
  return response.end(payload)
}

const server = createServer(requestHandler)

server.listen(1234)

$ npm i --save @frameworkless/bodyparser

Let's implement the package then!

// routes/new-reminder.js

const { parseBody } = require('@frameworkless/bodyparser')

exports.uri = '/new'
exports.template = 'new_reminder'
exports.method = 'POST'

exports.data = async request => {
  const formData = await parseBody(request)
  return formData
}

* also, delete the lib/formdata.js file

Time for the main event!

We're going to be using PostgreSQL in our app.

Look at these awesome logos...

⬅️ ⬅️ ⬅️ Slonik

A few more things...

Adding package-lock.json to .gitignore
We're going to write queries in *.sql files, so let's add that to the npm run start command

Just one more dependency...😉

Brian Carlson's "pg" module is how we're going to access Postgres
The module also comes with connection pools built in!

npm i --save pg

An initialiser + env variable

// initialisers/postgres.js

const { Pool } = require('pg')

const { DATABASE_URL } = process.env

if (!DATABASE_URL) throw new Error('DATABASE_URL not set!')

module.exports = new Pool({ connectionString: DATABASE_URL })

PORT=1234
APP_NAME=remind.ist
DATABASE_URL=postgres://

Get a PSQL database (Heroku, local, etc) + plug it in here:

Let's make sure it gets initialised!

// app.js

const server = require('./initialisers/http')
const db = require('./initialisers/postgres')

const run = () => {
  server({ db })
}

run()

We want to use the db throughout our app's routes, so let's make sure it gets initialised and passed into the server!

Pass the db object around

// initialisers/http.js

// ~ line 10, make sure you add "db" to the arguments
module.exports = ({ db }) => {

// And ~ line 24, let's add "db" to the context passed to routes
responseParams.db = db

We are mostly going to use this "db" object in routes, so it needs to be able to access them!

// lib/responder.js

// ~ line 35, make sure you add "db" to the arguments
const serveRoute = async ({ request, context, db }, response) => {

// And ~ line 45, let's add "db" to the context passed to the data hook
if (route.data) routeContext = await route.data({ request, db })

A db needs a schema!

-- sql/schema.sql

CREATE TABLE IF NOT EXISTS reminders (
  reminder_id SERIAL PRIMARY KEY,

  email TEXT NOT NULL CHECK (email ~* '^.+@.+\..+$'),
  message TEXT,

  send_at DATE NOT NULL,

  created_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW()
);

No ORM models, let's just write some SQL:

We need to init the db...

We should write a runnable script to do this for ourselves:

$ mkdir -p bin/db
$ touch bin/db/init
$ chmod +x bin/db/init

An executable file with no extension is fine, as long as the first line contains a "hashbang" telling your shell how to execute it. We want Node.js to run it, so here's the Node hashbang:

#!/usr/bin/env node

...And the rest of our db init script

#!/usr/bin/env node

// bin/db/init

if (!process.env.DATABASE_URL) require('dotenv').config()

const { readFileSync } = require('fs')

const db = require('../../initialisers/postgres')

const run = () => new Promise(async resolve => {
  const schema = readFileSync('./sql/schema.sql', { encoding: 'utf8' })

try {
    console.info(`=> Running db query:\n`, schema)
    await db.query(schema)
  } catch (error) {
    console.error(`=> Error running query:\n`, schema, error)
    return resolve(1)
  }

  return resolve(0)
})

run().then(process.exit)

let's capture forms!

{{!-- templates/root.hbs --}}

<div>
  <input type="date" id="send_at" name="send_at">
</div>

{{!-- templates/new_reminder.hbs --}}

<p>Send at:</p>

<p>{{send_at}}</p>

Forms are also a bit of a rabbit hole:
https://github.com/pillarjs/understanding-csrf

Db actions as...actions 😉

An "action" (in my own terms) is a database activity (create/read/update/delete).
We should separate our db logic from the rest of our app, or else things are going to get messy fast!

// actions/index.js

const glob = require('glob')

module.exports = glob.sync(
  './actions/**/*.js',
  { ignore: [ './actions/index.js' ] }
).reduce((actions, filename) => {
  const pathTokens = filename.split('/')
  const key = pathTokens[pathTokens.length - 1].slice(0, -3)

  actions[key] = require(`.${filename}`)

  return actions
}, {})

Creating a reminder action

// actions/newReminder.js

module.exports = async (db, { email, message, send_at }) => {
  const { rows: [ reminder ] } = await db.query(
    'INSERT INTO reminders (email, message, send_at) VALUES ($1, $2, $3) RETURNING *',
    [ email, message, send_at ]
  )

  return reminder
}

Note we're using parameterised queries to avoid SQL injection:

https://node-postgres.com/features/queries#Parameterized%20query

Use the action in your route

// routes/new-reminder.js

// Import our actions object
const { newReminder } = require('../actions')

// Run the action and return the result
exports.data = async ({ request, db }) => {
  const formData = await parseBody(request)
  const reminder = await newReminder(db, formData)

  return reminder
}

That's all, let's stop here...

Before our brains melt!

Well done, you made it! 👏👏👏

See you next time where we look at The frontend and serving assets in a pipeline.

Please don't hesitate to contact me or leave feedback on the course:

Telegram channel: https://t.me/frameworkless

Twitter: @mtimofiiv

See today's code: https://github.com/frameworkless-js/remind.ist/tree/stage/4
See today's lesson running live: https://part4.remind.ist