{"id":5772,"date":"2017-06-15T00:40:57","date_gmt":"2017-06-15T07:40:57","guid":{"rendered":"http:\/\/www.mysqltutorial.org\/?page_id=5772"},"modified":"2024-01-02T02:33:58","modified_gmt":"2024-01-02T09:33:58","slug":"delete","status":"publish","type":"page","link":"https:\/\/www.mysqltutorial.org\/mysql-nodejs\/delete\/","title":{"rendered":"Deleting Data in MySQL from Node.js"},"content":{"rendered":"\n

Summary<\/strong>: in this tutorial, you will learn how to delete data in a MySQL database from a Node.js application.<\/p>\n\n\n\n

This tutorial picks up where the Updating Data in MySQL Database from Node.js<\/a>\u00a0tutorial left off.<\/p>\n\n\n\n

To delete data in MySQL database from a Node.js application, you follow these steps:<\/p>\n\n\n\n

    \n
  1. Establish a connection to the MySQL database<\/a>.<\/li>\n\n\n\n
  2. Execute a DELETE<\/code><\/a> statement by calling the query()<\/code> method on a Connection<\/code> object.<\/li>\n\n\n\n
  3. Disconnect from the MySQL database server.<\/li>\n<\/ol>\n\n\n\n

    Deleting data example<\/h2>\n\n\n\n

    The following delete.js<\/code> program deletes a row in the todos<\/code> table based on the row 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  let<\/span> sql = `DELETE FROM todos WHERE id = ?`<\/span>;\n\n  let<\/span> data = [1<\/span>];\n\n  connection.query(sql, data, (error, results, fields) => {\n    if<\/span> (error) return<\/span> console<\/span>.error(error.message);\n    console<\/span>.log('Rows affected:'<\/span>, results.affectedRows);\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, import the mysql<\/code> module:<\/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 to the MySQL database using the createConnection<\/code> method:<\/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
    Note that we retrieve the connection parameters (host, port, user, password, and database) from environment variables using .env<\/code>.<\/p>\n\n\n\n
    Third, connect to the MySQL database:<\/p>\n\n\n
    connection.connect((err<\/span>) =><\/span> {\n  if<\/span> (err) return<\/span> console<\/span>.error(err.message);\n});<\/code><\/span>Code language:<\/span> JavaScript<\/span> (<\/span>javascript<\/span>)<\/span><\/small><\/pre>\n\n\n
    This code checks if there is any error and displays it.<\/p>\n\n\n\n
    Fourth, define an SQL query to delete a record in the todos<\/code> table:<\/p>\n\n\n
    let<\/span> sql = `DELETE FROM todos WHERE id = ?`<\/span>;<\/code><\/span>Code language:<\/span> JavaScript<\/span> (<\/span>javascript<\/span>)<\/span><\/small><\/pre>\n\n\n
    Fifth, specify the data used in the query:<\/p>\n\n\n
    let<\/span> data = [1<\/span>];<\/code><\/span>Code language:<\/span> JavaScript<\/span> (<\/span>javascript<\/span>)<\/span><\/small><\/pre>\n\n\n
    In this case, the placeholder ?<\/code> in the SQL query will be replaced by 1<\/code>. To delete a row with a different id, you can change it in the data<\/code> array.<\/p>\n\n\n\n
    Sixth, execute the SQL query using the query<\/code> method:<\/p>\n\n\n
    connection.query(sql, data, (error, results, fields) => {\n  if<\/span> (error) return<\/span> console<\/span>.error(error.message);\n  console<\/span>.log('Rows affected:'<\/span>, results.affectedRows);\n});<\/code><\/span>Code language:<\/span> JavaScript<\/span> (<\/span>javascript<\/span>)<\/span><\/small><\/pre>\n\n\n
    This code executes the query and displays the number of rows affected.<\/p>\n\n\n\n
    Finally, close the database connection:<\/p>\n\n\n
    connection<\/span>.end<\/span>();<\/code><\/span>Code language:<\/span> CSS<\/span> (<\/span>css<\/span>)<\/span><\/small><\/pre>\n\n\n
    Verifying the delete<\/h2>\n\n\n\n
    First, connect to the MySQL server:<\/p>\n\n\n
    mysql -u root -p<\/code><\/span><\/pre>\n\n\n
    Second, change the database to todoapp<\/code>:<\/p>\n\n\n
    use<\/span> todoapp<\/span>;<\/code><\/span>Code language:<\/span> PHP<\/span> (<\/span>php<\/span>)<\/span><\/small><\/pre>\n\n\n
    Third, retrieve the row with id 1:<\/p>\n\n\n
    SELECT * FROM todos WHERE id = 1;<\/code><\/span><\/pre>\n\n\n
    Output:<\/p>\n\n\n
    Empty set<\/span> (0.00 sec)<\/code><\/span>Code language:<\/span> JavaScript<\/span> (<\/span>javascript<\/span>)<\/span><\/small><\/pre>\n\n\n
    It returns no row, meaning that the row with id 1 was deleted.<\/p>\n\n\n\n
    In this tutorial, you have learned how to delete data in MySQL from a Node.js application.<\/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