Problem successfully persisting or querying data, despite db.listTables() functioning correctly

[johncmunson]

Summary

Massive appears to successfully be connecting to the database, as witnessed by req.app.get('db').listTables() printing out the correct list of database tables. However, any attempts to persist or query data always return null.

Example

const express = require('express')
const massive = require('massive')
const { Client } = require('pg')

const connection = {
  host: process.env.DATA_POSTGRES_HOST,
  port: 5432,
  database: 'appdb',
  user: process.env.DATA_POSTGRES_USER,
  password: process.env.DATA_POSTGRES_PASS,
  max: 10,
  min: 0
}

const client = new Client(connection)

client.connect()

client.query('CREATE TABLE IF NOT EXISTS account(email varchar(355) unique not null primary key, password varchar(50) not null')
  .catch(e => console.error(e.stack))
  .then(() => client.end())

const db = massive(connection)
  .then(db => {
    app.set('db', db)

    console.log(app.get('db').listTables()) // PRINTS [ 'account' ] AS EXPECTED

    app.post('/signup', (req, res) => {
      req.app.get('db').account.save({ email: req.body.email, password: req.body.password }).then(data => {
        console.log(data) // PRINTS NULL
      })
    })

    app.post('/getaccountinfo', (req, res) => {
      req.app.get('db').account.findOne({ email: req.body.email }).then(data => {
        console.log(data) // PRINTS NULL
      })
    })

    app.listen(
      port,
      '0.0.0.0',
      () => console.log(`Example app listening on port ${port}!`)
    )
  })

[dmfay]

This is one of the ramifications of save not being a real "upsert": it doesn't play well with primary keys that aren't generated from sequences or functions. Specifically, it will generate an UPDATE statement for tables like account no matter what, since creating a new record means you include an email. If you have to set a value for the primary key in new records, you need to use insert, although I note the docs don't currently mention this.

For what it's worth, I have only rarely had reason to prefer a natural over a synthetic key, and emails and other plausibly-mutable facts in particular make bad primary key candidates due to the possibility of breaking external links and references (bookmarks, reports, etc) when they change.

[johncmunson]

Thanks!!