I am inserting a note into the quoteNotes table. When I insert it and console log the res. it doesn't give me a record of the inserted note.

router.post('/:id/notes', (req, res) => {
  const {id} = req.params;
  const note = req.body;
  note.quote_id = id

  // if(note.note === "") return res.status(422)
  //   .json({message: 'Note cannot be empty'});

  Quotes.addNote(note).then((quoteNote) => {
    console.log(quoteNote)
    res.status(200).json(quoteNote);
  });
});

console.log =>

Result {
  mand: 'INSERT',
  rowCount: 1,
  oid: 0,
  rows: [],
  fields: [],
  _parsers: undefined,
  _types: TypeOverrides {
    _types: {
      getTypeParser: [Function: getTypeParser],
      setTypeParser: [Function: setTypeParser],
      arrayParser: [Object],
      builtins: [Object]
    },
    text: {},
    binary: {}
  },
  RowCtor: null,
  rowAsArray: false
}

I am inserting a note into the quoteNotes table. When I insert it and console log the res. it doesn't give me a record of the inserted note.

router.post('/:id/notes', (req, res) => {
  const {id} = req.params;
  const note = req.body;
  note.quote_id = id

  // if(note.note === "") return res.status(422)
  //   .json({message: 'Note cannot be empty'});

  Quotes.addNote(note).then((quoteNote) => {
    console.log(quoteNote)
    res.status(200).json(quoteNote);
  });
});

console.log =>

Result {
  mand: 'INSERT',
  rowCount: 1,
  oid: 0,
  rows: [],
  fields: [],
  _parsers: undefined,
  _types: TypeOverrides {
    _types: {
      getTypeParser: [Function: getTypeParser],
      setTypeParser: [Function: setTypeParser],
      arrayParser: [Object],
      builtins: [Object]
    },
    text: {},
    binary: {}
  },
  RowCtor: null,
  rowAsArray: false
}

• javascript
• mysql
• postgresql
• express
• knex.js

3 Answers 3

Figured it out.

needed to add .returning('id') to the query.

function addNote(data) {
  return db('quote_notes')
    .insert(data)
    .returning('id');
}

In my case with mysql, the await insert({...}) returned an array with an id as a single element. And by using returning('id') I got an error saying it's not supported for mysql.

For anyone else that lands here, below is a (clumsy) approach you can use to return a non auto-increment insert ID e.g. a uuid() from a MySQL database using Knex.

In our case, the insert query is being built by Knex, with raw SQL added before the database is queried with MySQL2.

// Assumes your primary key column is 'id'

const query = knex('table_name').insert({
    ...data,
    id: knex.raw('@id'),
}).onConflict('id').merge().toSQL();

const [[setId, checkId, insert, [{ lastId }]]] = await connection.query('SET @id = ?; SET @id = IFNULL(@id, UUID());' + query.sql + '; SELECT cast(@id as CHAR) AS lastId;', [data.id, ...query.bindings]);

console.log(lastId)

multipleStatements or your equivalent (see here) must be enabled for this to work - make sure everything is properly escaped!!