How to retrieve the ID of the inserted record in Knex.js

I was trying to add a new note to the quoteNotes table. However, after inserting it and logging the response, I noticed that there was no record of the inserted note showing up.

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 {
  command: '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
}

Answer №1

Solved the problem.

Realized that I had to include .returning('id') in the query.

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

Answer №2

When working with MySQL, I encountered a situation where the await insert({...}) function returned an array containing just the ID as a single element. When trying to use returning('id'), I received an error indicating that it is not supported for MySQL databases.

Answer №3

If you happen to find yourself in a similar situation, here is a workaround that may help you retrieve a non auto-increment insert ID such as a uuid() from a MySQL database using Knex.

In our particular scenario, the insert query is constructed using Knex, with raw SQL added before proceeding to execute the query with MySQL2.

// Assuming 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, [{ '@id': lastId }]]] = await connection.query('SET @id = ?; SET @id = IFNULL(@id, UUID());' + query.sql + '; SELECT @id;', [data.id, ...query.bindings]);

console.log(lastId)

multipleStatements, or its equivalent (check here), needs to be enabled for this method to function correctly - ensure all inputs are properly sanitized!!

Similar questions

If you have not found the answer to your question or you are interested in this topic, then look at other similar questions below or use the search

Leaflet setStyle changes are effective when applied through the console but do not reflect in the actual

I currently have a map with around 1000 polygons that I am loading onto it. My goal is to color these polygons based on a specific property value. The code snippet below showcases my approach, but the results are not as expected. mapLayerFieldGrid = new L ...

Error message encountered when attempting to process json-encoded JavaScript using Ajax

I am struggling with an HTML document that includes: <li id="li_273" data-pricefield="special" data-pricevalue="0" > <label class="description" for="element_273">Helium Foil Balloon #1 </label> <div> ...

When using React with Firebase, remember to specify the "to" property when using setState to avoid errors

Struggling to figure out what's going wrong after hours of trying. I'm new to React and would really appreciate your help. Initially, my state was an array of accommodations which I mapped over successfully. However, once I connected Firebase wit ...

Secure communication with Wildfly and MySQL using SSL technology

My current web app utilizes a MySQL database as its data store and is hosted on Glassfish, securely communicating with the database through SSL. Considering a migration to WildFly, I am encountering difficulties in configuring a datasource that can connec ...

The $watch() function seems to be failing to properly refresh the $scope

Within my controller, I have a function $scope.remove() that triggers a request to the usercart, which then calls an API. The JSON response from the API includes an object with an array of cart items. Despite using an ng-repeat in the HTML to iterate thro ...

What is the best way to attach to the beforeSubmit event of a dynamically loaded AJAX form in Yii?

Recently, I encountered an issue with dynamically loading a form via AJAX and attempting to bind the beforeSubmit event. Strangely, the event didn't seem to work as expected, causing the form to submit and the browser to navigate to a new page. This b ...

Find keys in an array based on a specified value

I need to retrieve an array of keys from an object that match a specified value ...

Prevent Print Screen Functionality in Web Pages using jQuery or JavaScript

Is there a way to insert an image into my webpage while preventing users from saving it on their computers? I want to stop them from using the Print Screen button on their keyboards to capture images of my flash application. Can jQuery or JavaScript help ...

Union malfunction causing no results to show up

I attempted to use both inner join and union but encountered issues. sql="SELECT * FROM " + "(SELECT order_id, order_date, order_status, order_value, order_sapId, order_from, order_clientAcct, order_error, order_visitId, order_cancelled, distribut ...

Top recommendations for implementing private/authentication routes in NextJS 13

When working with routes affected by a user's authentication status in NextJS 13, what is the most effective approach? I have two specific scenarios that I'm unsure about implementing: What is the best method for redirecting an unauthenticated ...

Preserve the wpColorPicker selection using personalized knockout bindings

Utilizing wpColorPicker and knockout, my goal is to update the color picker value in my observable and then store it in the database as JSON. While other elements successfully update and save, there seems to be an issue with my custom binding for the data ...

Issue with react-addons-css-transition-group and multiline TextFields in Material-UI TextField

If a TextField with multiline is nested inside a react-addons-css-transition-group, it seems to disrupt the show transition. Any suggestions on how to handle this situation effectively? Check out my code snippet here: https://codesandbox.io/s/material-dem ...

Values are being set back to '1' within the popup dialog

After recently incorporating Twitter Bootstrap into my web app project, everything seemed to be going smoothly until I encountered an issue with a modal window. Following the Bootstrap documentation, I set up a button that triggers a modal window to appea ...

What could be the reason for the malfunction of Bootstrap Js in my template?

This question focuses on understanding how something works rather than just fixing it. I really enjoy learning about this. Currently, I am involved in a project that combines VueJS and Symfony. One of the things I would like to achieve is using Bootstrap ...

The requested resource does not contain the 'Access-Control-Allow-Origin' header. It has already been included in the headers

Despite adding the 'Access-Control-Allow-Origin': '*' header to my Axios request, I am still encountering the same error. When trying to access 'http://backendUrlEndPoint' from origin 'https://frontendURL', the COR ...

The most effective method for transferring a JavaScript object between a TypeScript frontend and a Node.js backend

I need some advice on how to effectively share a JavaScript object between my Angular2 with Typescript frontend and NodeJS backend in an application I'm working on. Currently, I am using a .d.ts file for the frontend and adding a module.exports in the ...

The challenge of PHP's HTTP_REFERER

I am experiencing an issue with http_referer. In my setup, I have two files: index.php and index.html. The index.php file contains a form that, upon submission, includes information like the http_referer. On the other hand, the index.html file runs an aja ...

Leveraging Parse methods within a Node JS Cron job

My current task involves writing a Cron Job that requires the use of Parse methods. I have the following code snippet at hand: var crontab = require('node-crontab'); var jobId = crontab.scheduleJob("* * * * * *", function(){ ...

Exploring point clouds with three.js and NSF OpenTopography data: What's the best way to configure the camera and implement a controls library for seamless data navigation?

Currently, I am engaged in a small-scale project aimed at showcasing NSF OpenTopography data through a point cloud visualization using three.js. While I have successfully generated the point cloud, I am encountering significant challenges with setting up t ...

Challenges encountered while compiling Node.js code with ts-node (Error: Cannot use import statement outside a module)

Trying to compile TypeScript code with NodeJS using this command: npx ts-node src/server.ts An error is thrown: SyntaxError: Cannot use import statement outside a module Following the error's instructions: Warning: To load an ES module, set " ...