{"id":5763,"date":"2017-06-15T00:14:11","date_gmt":"2017-06-15T07:14:11","guid":{"rendered":"http:\/\/www.mysqltutorial.org\/?page_id=5763"},"modified":"2024-01-03T17:50:44","modified_gmt":"2024-01-04T00:50:44","slug":"insert","status":"publish","type":"page","link":"https:\/\/www.mysqltutorial.org\/mysql-nodejs\/insert\/","title":{"rendered":"Inserting Rows into a Table from Node.js"},"content":{"rendered":"\n

Summary<\/strong>: in this tutorial, you will learn how to insert one or more rows into a table from the Node.js application.<\/p>\n\n\n\n

This tutorial picks up where the Connecting to MySQL server from a Node.js application<\/a> tutorial left off.<\/p>\n\n\n\n

To insert a new row into a table, you follow these steps:<\/p>\n\n\n\n

    \n
  1. First, connect to the MySQL database.<\/a><\/li>\n\n\n\n
  2. Second, execute an INSERT<\/code><\/a> statement by calling the query()<\/code> method on a connection<\/code> object.<\/li>\n\n\n\n
  3. Third, close the database connection.<\/li>\n<\/ol>\n\n\n\n

    Insert one row into a table<\/h2>\n\n\n\n

    Create a new insert.js<\/code> file in the project directory and add the following code to the file to insert a new row into the todos<\/code> table:<\/p>\n\n\n

    let<\/span> mysql = require<\/span>('mysql'<\/span>);\n\nlet<\/span> connection = mysql.createConnection({\n  host<\/span>: process.env.DB_HOST,\n  port<\/span>: process.env.DB_PORT,\n  user<\/span>: process.env.DB_USER,\n  password<\/span>: process.env.DB_PASSWORD,\n  database<\/span>: process.env.DB_NAME,\n});\n\nconnection.connect((err<\/span>) =><\/span> {\n  if<\/span> (err) return<\/span> console<\/span>.error(err.message);\n\n  \/\/ insert statment<\/span>\n  let<\/span> sql = `INSERT INTO todos(title,completed)\n           VALUES('Learn how to insert a new row',true)`<\/span>;\n\n  \/\/ execute the insert statment<\/span>\n  connection.query(sql);\n\n  \/\/ close the database connection<\/span>\n  connection.end();\n});<\/code><\/span>Code language:<\/span> JavaScript<\/span> (<\/span>javascript<\/span>)<\/span><\/small><\/pre>\n\n\n
    How it works.<\/p>\n\n\n\n
    First, use the mysql<\/code> module to connect to the MySQL server:<\/p>\n\n\n
    let<\/span> mysql = require<\/span>('mysql'<\/span>);<\/code><\/span>Code language:<\/span> JavaScript<\/span> (<\/span>javascript<\/span>)<\/span><\/small><\/pre>\n\n\n
    Second, create a connection object with the information provided in the .env<\/code> file:<\/p>\n\n\n
    let<\/span> connection = mysql.createConnection({\n  host<\/span>: process.env.DB_HOST,\n  port<\/span>: process.env.DB_PORT,\n  user<\/span>: process.env.DB_USER,\n  password<\/span>: process.env.DB_PASSWORD,\n  database<\/span>: process.env.DB_NAME,\n});<\/code><\/span>Code language:<\/span> JavaScript<\/span> (<\/span>javascript<\/span>)<\/span><\/small><\/pre>\n\n\n
    The .env<\/code> file looks like the following:<\/p>\n\n\n
    DB_HOST=localhost\nDB_PORT=3306\nDB_USER=user\nDB_PASSWORD=password\nDB_NAME=todoapp<\/code><\/span>Code language:<\/span> plaintext<\/span> (<\/span>plaintext<\/span>)<\/span><\/small><\/pre>\n\n\n
    Note that you need to replace the user and password with the ones you use to connect to the MySQL server.<\/p>\n\n\n\n
    Third, connect to the MySQL server using the connect()<\/code> method:<\/p>\n\n\n
    connection.connect((err<\/span>) =><\/span> {\n  if<\/span> (err) return<\/span> console<\/span>.error(err.message);\n\n  \/\/ ...<\/span>\n\n});<\/code><\/span>Code language:<\/span> JavaScript<\/span> (<\/span>javascript<\/span>)<\/span><\/small><\/pre>\n\n\n
    Fourth, construct the insert<\/code> statement:<\/p>\n\n\n
    let<\/span> sql = `INSERT INTO todos(title,completed)\n           VALUES('Learn how to insert a new row',true)`<\/span>;<\/code><\/span>Code language:<\/span> JavaScript<\/span> (<\/span>javascript<\/span>)<\/span><\/small><\/pre>\n\n\n
    Fifth, execute the INSERT<\/code> statement using the query()<\/code> method:<\/p>\n\n\n
    connection.query(sql);<\/code><\/span>Code language:<\/span> JavaScript<\/span> (<\/span>javascript<\/span>)<\/span><\/small><\/pre>\n\n\n
    Finally, close the database connection:<\/p>\n\n\n
    connection.end();<\/code><\/span>Code language:<\/span> JavaScript<\/span> (<\/span>javascript<\/span>)<\/span><\/small><\/pre>\n\n\n
    To run the insert.js<\/code> program, you use the following command:<\/p>\n\n\n
    node --env-file .env insert.js<\/code><\/span>Code language:<\/span> plaintext<\/span> (<\/span>plaintext<\/span>)<\/span><\/small><\/pre>\n\n\n
    Verify the insert<\/h2>\n\n\n\n
    First, connect to the MySQL server:<\/p>\n\n\n
    mysql -u root -p<\/code><\/span>Code language:<\/span> plaintext<\/span> (<\/span>plaintext<\/span>)<\/span><\/small><\/pre>\n\n\n
    Second, retrieve data from the todos<\/code> table:<\/p>\n\n\n
    select<\/span> * from<\/span> todos;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
    Output:<\/p>\n\n\n
    +----+-------------------------------+-----------+\n| id | title                         | completed |\n+----+-------------------------------+-----------+\n|  1<\/span> | Learn how to insert a new<\/span> row |         1<\/span> |\n+----+-------------------------------+-----------+\n1<\/span> row in<\/span> set<\/span> (0.00 sec)<\/code><\/span>Code language:<\/span> JavaScript<\/span> (<\/span>javascript<\/span>)<\/span><\/small><\/pre>\n\n\n
    The output indicates that the program has inserted a new row into the todos<\/code> table successfully.<\/p>\n\n\n\n
    Inserting a row and returning the inserted id<\/h2>\n\n\n\n
    Create a new file insert_return.js<\/code> and add the following code to the file to insert a new row into the todos<\/code> table and returns the inserted ID:<\/p>\n\n\n
    let<\/span> mysql = require<\/span>('mysql'<\/span>);\n\nlet<\/span> connection = mysql.createConnection({\n  host<\/span>: process.env.DB_HOST,\n  port<\/span>: process.env.DB_PORT,\n  user<\/span>: process.env.DB_USER,\n  password<\/span>: process.env.DB_PASSWORD,\n  database<\/span>: process.env.DB_NAME,\n});\n\nconnection.connect((err<\/span>) =><\/span> {\n  if<\/span> (err) return<\/span> console<\/span>.error(err.message);\n\n  \/\/ insert statment<\/span>\n  let<\/span> sql = `INSERT INTO todos(title,completed)\n             VALUES(?,?)`<\/span>;\n\n  let<\/span> todo = ['Insert a new row with placeholders'<\/span>, false<\/span>];\n\n  \/\/ execute the insert statment<\/span>\n  connection.query(sql, todo, (err, results, fields) => {\n    if<\/span> (err) return<\/span> console<\/span>.error(err.message);\n\n    console<\/span>.log('Todo Id:'<\/span> + results.insertId);\n  });\n\n  \/\/ close the database connection<\/span>\n  connection.end();\n});\n<\/code><\/span>Code language:<\/span> JavaScript<\/span> (<\/span>javascript<\/span>)<\/span><\/small><\/pre>\n\n\n
    How it works.<\/p>\n\n\n\n
    First, construct the query that uses question marks (?<\/code>) as the placeholders:<\/p>\n\n\n
    let<\/span> sql = `INSERT INTO todos(title,completed)\n           VALUES(?,?)`<\/span>;<\/code><\/span>Code language:<\/span> JavaScript<\/span> (<\/span>javascript<\/span>)<\/span><\/small><\/pre>\n\n\n
    Second, execute the query and pass data to it in the query()<\/code> method and display the inserted ID if the query succeeded:<\/p>\n\n\n
    connection.query(sql, todo, (err, results, fields) => {\n  if<\/span> (err) return<\/span> console<\/span>.error(err.message);\n\n  console<\/span>.log('Todo Id:'<\/span> + results.insertId);\n});\n<\/code><\/span>Code language:<\/span> JavaScript<\/span> (<\/span>javascript<\/span>)<\/span><\/small><\/pre>\n\n\n
    Execute the insert_return.js<\/code> file:<\/p>\n\n\n
    node --env-file .env insert_return.js<\/code><\/span>Code language:<\/span> plaintext<\/span> (<\/span>plaintext<\/span>)<\/span><\/small><\/pre>\n\n\n
    Output:<\/p>\n\n\n
    Todo Id:2<\/span><\/code><\/span>Code language:<\/span> JavaScript<\/span> (<\/span>javascript<\/span>)<\/span><\/small><\/pre>\n\n\n
    Insert multiple rows<\/h2>\n\n\n\n
    Create a new file called insert_multiple.js<\/code> and use the following code to insert multiple rows into the todos<\/code> table:<\/p>\n\n\n
    let<\/span> mysql = require<\/span>('mysql'<\/span>);\n\nlet<\/span> connection = mysql.createConnection({\n  host<\/span>: process.env.DB_HOST,\n  port<\/span>: process.env.DB_PORT,\n  user<\/span>: process.env.DB_USER,\n  password<\/span>: process.env.DB_PASSWORD,\n  database<\/span>: process.env.DB_NAME,\n});\n\nconnection.connect((err<\/span>) =><\/span> {\n  if<\/span> (err) return<\/span> console<\/span>.error(err.message);\n\n  \/\/ insert statment<\/span>\n  let<\/span> sql = 'INSERT INTO todos(title, completed) VALUES ?'<\/span>;\n\n  let<\/span> todos = [\n    ['Master Node.js MySQL'<\/span>, false<\/span>],\n    ['Build Node.js \/ MySQL App'<\/span>, true<\/span>],\n  ];\n\n  \/\/ execute the insert statment<\/span>\n  connection.query(sql, [todos], (err, results, fields) => {\n    if<\/span> (err) return<\/span> console<\/span>.error(err.message);\n\n    console<\/span>.log(`Inserted Rows: ${results.affectedRows}<\/span>`<\/span>);\n  });\n\n  \/\/ close the database connection<\/span>\n  connection.end();\n});\n<\/code><\/span>Code language:<\/span> JavaScript<\/span> (<\/span>javascript<\/span>)<\/span><\/small><\/pre>\n\n\n
    How it works.<\/p>\n\n\n\n
    First, construct the INSERT<\/code> statement with one question mark(?<\/code>):<\/p>\n\n\n
    let<\/span> sql = `INSERT INTO todos(title, completed) VALUES ?`<\/span>;<\/code><\/span>Code language:<\/span> JavaScript<\/span> (<\/span>javascript<\/span>)<\/span><\/small><\/pre>\n\n\n
    The question mark (?) in the INSERT<\/code> statement represents multiple-row data that will come from an array.<\/p>\n\n\n\n
    Second, prepare data for insertion:<\/p>\n\n\n
    let<\/span> todos = [\n    ['Master Node.js MySQL'<\/span>, false<\/span>],\n    ['Build Node.js \/ MySQL App'<\/span>, true<\/span>],\n];<\/code><\/span>Code language:<\/span> JavaScript<\/span> (<\/span>javascript<\/span>)<\/span><\/small><\/pre>\n\n\n
    Third, execute the INSERT<\/code> statement with the data that comes from the todos<\/code> array:<\/p>\n\n\n
    connection.query(sql, [todos], (err, results, fields) => {\n  if<\/span> (err) return<\/span> console<\/span>.error(err.message);\n\n  console<\/span>.log('Row inserted:'<\/span> + results.affectedRows);\n});<\/code><\/span>Code language:<\/span> JavaScript<\/span> (<\/span>javascript<\/span>)<\/span><\/small><\/pre>\n\n\n
    You can access the number of rows inserted via the affectedRows<\/code> property of the results object.<\/p>\n\n\n
    node --env-file .env insert_multiple.js<\/code><\/span>Code language:<\/span> JavaScript<\/span> (<\/span>javascript<\/span>)<\/span><\/small><\/pre>\n\n\n
    Output:<\/p>\n\n\n
    Inserted Rows: 2<\/code><\/span><\/pre>\n\n\n
    The output indicates that two rows were inserted.<\/p>\n\n\n\n
    In this tutorial, you have learned how to insert one or more rows into a MySQL table from a Node.js program.<\/p>\n
    \n\t
    \n\t\t
    Was this tutorial helpful? <\/div>\n\t\t
    \n\t\t\t\n\t\t\t\t\n\t\t\t\t\t<\/path>\n\t\t\t\t<\/svg>\n\t\t\t\t Yes <\/span>\n\t\t\t<\/button>\n\n\t\t\t\n\t\t\t\t\n\t\t\t\t\t<\/path>\n\t\t\t\t<\/svg>\n\t\t\t\t No <\/span>\n\t\t\t<\/button>\n\t\t<\/div>\n\t<\/header>\n\n\t
    \n\t\t
    \n\t\t\t
    <\/div>\n\t\t\t\n\t\t\t