Node Js MySQL Delete

Last modified on: October 19, 2024

<div class="flex-shrink-0 flex flex-col relative items-end">
<div>
<div class="pt-0">
<div class="gizmo-bot-avatar flex h-8 w-8 items-center justify-center overflow-hidden rounded-full">
<div class="relative p-1 rounded-sm flex items-center justify-center bg-token-main-surface-primary text-token-text-primary h-8 w-8">&nbsp;</div>
</div>
</div>
</div>
</div>
<div class="group/conversation-turn relative flex w-full min-w-0 flex-col agent-turn">
<div class="flex-col gap-1 md:gap-3">
<div class="flex max-w-full flex-col flex-grow">
<div class="min-h-8 text-message flex w-full flex-col items-end gap-2 whitespace-normal break-words [.text-message+&amp;]:mt-5" dir="auto" data-message-author-role="assistant" data-message-id="9cba4e80-40dd-4633-b9c1-a10eb1d70aa1" data-message-model-slug="gpt-4o">
<div class="flex w-full flex-col gap-1 empty:hidden first:pt-[3px]">
<div class="markdown prose w-full break-words dark:prose-invert light">
<h3>Node.js MySQL: Deleting Records with the <code>DELETE</code> Statement</h3>
<p>The <code>DELETE</code> statement in SQL is used to remove records from a table. When working with Node.js and MySQL, you can use the <code>DELETE</code> statement to delete specific rows or all rows from a table. Unlike the <code>DROP TABLE</code> statement, which deletes an entire table, the <code>DELETE</code> statement only removes the data while keeping the table structure intact.</p>
<hr />
<h3>Prerequisites</h3>
<p>Ensure you have the following:</p>
<ol>
<li>
<p><strong>Node.js</strong>: Installed from <a href="https://nodejs.org/" target="_new" rel="noopener">nodejs.org</a>.</p>
</li>
<li>
<p><strong>MySQL</strong>: Installed and running.</p>
</li>
<li>
<p><strong>MySQL Table</strong>: A table with data that you want to delete.</p>
</li>
<li>
<p><strong>MySQL Package</strong>: Installed in your Node.js project:</p>
<div class="dark bg-gray-950 contain-inline-size rounded-md border-[0.5px] border-token-border-medium relative">
<div class="flex items-center text-token-text-secondary bg-token-main-surface-secondary px-4 py-2 text-xs font-sans justify-between rounded-t-md h-9">bash</div>
<div class="sticky top-9 md:top-[5.75rem]">
<div class="absolute bottom-0 right-2 flex h-9 items-center">
<div class="flex items-center rounded bg-token-main-surface-secondary px-2 font-sans text-xs text-token-text-secondary">
<pre class="language-javascript"><code>npm install mysql
</code></pre>
</div>
</div>
</div>
</div>
</li>
</ol>
<hr />
<h3>Step 1: Set Up the Project</h3>
<ol>
<li>
<p><strong>Create a project directory</strong> (if not already done):</p>
<div class="dark bg-gray-950 contain-inline-size rounded-md border-[0.5px] border-token-border-medium relative">
<div class="flex items-center text-token-text-secondary bg-token-main-surface-secondary px-4 py-2 text-xs font-sans justify-between rounded-t-md h-9">bash</div>
<div class="sticky top-9 md:top-[5.75rem]">
<div class="absolute bottom-0 right-2 flex h-9 items-center">
<div class="flex items-center rounded bg-token-main-surface-secondary px-2 font-sans text-xs text-token-text-secondary">
<pre class="language-javascript"><code>mkdir node-mysql-delete
cd node-mysql-delete
</code></pre>
</div>
</div>
</div>
</div>
</li>
<li>
<p><strong>Initialize a Node.js project</strong>:</p>
<div class="dark bg-gray-950 contain-inline-size rounded-md border-[0.5px] border-token-border-medium relative">
<div class="flex items-center text-token-text-secondary bg-token-main-surface-secondary px-4 py-2 text-xs font-sans justify-between rounded-t-md h-9">bash</div>
<div class="sticky top-9 md:top-[5.75rem]">
<div class="absolute bottom-0 right-2 flex h-9 items-center">
<div class="flex items-center rounded bg-token-main-surface-secondary px-2 font-sans text-xs text-token-text-secondary">
<pre class="language-javascript"><code>npm init -y
</code></pre>
<strong>Install the MySQL package</strong>:</div>
</div>
</div>
</div>
</li>
<li>
<div class="dark bg-gray-950 contain-inline-size rounded-md border-[0.5px] border-token-border-medium relative">
<div class="flex items-center text-token-text-secondary bg-token-main-surface-secondary px-4 py-2 text-xs font-sans justify-between rounded-t-md h-9">bash</div>
<div class="sticky top-9 md:top-[5.75rem]">
<div class="absolute bottom-0 right-2 flex h-9 items-center">
<div class="flex items-center rounded bg-token-main-surface-secondary px-2 font-sans text-xs text-token-text-secondary">
<pre class="language-javascript"><code>npm install mysql
</code></pre>
</div>
</div>
</div>
</div>
</li>
</ol>
<hr />
<h3>Step 2: Connect to MySQL</h3>
<p>To delete records from a table, you must first establish a connection to the MySQL database.</p>
<ol>
<li>
<p><strong>Create a file named <code>app.js</code></strong>:</p>
<div class="dark bg-gray-950 contain-inline-size rounded-md border-[0.5px] border-token-border-medium relative">
<div class="flex items-center text-token-text-secondary bg-token-main-surface-secondary px-4 py-2 text-xs font-sans justify-between rounded-t-md h-9">bash</div>
<div class="sticky top-9 md:top-[5.75rem]">
<div class="absolute bottom-0 right-2 flex h-9 items-center">
<div class="flex items-center rounded bg-token-main-surface-secondary px-2 font-sans text-xs text-token-text-secondary">
<pre class="language-javascript"><code>touch app.js
</code></pre>
</div>
</div>
</div>
</div>
</li>
<li>
<p><strong>Open <code>app.js</code></strong> and add the following code to connect to the MySQL server:</p>
<div class="dark bg-gray-950 contain-inline-size rounded-md border-[0.5px] border-token-border-medium relative">
<div class="flex items-center text-token-text-secondary bg-token-main-surface-secondary px-4 py-2 text-xs font-sans justify-between rounded-t-md h-9">javascript</div>
<div class="sticky top-9 md:top-[5.75rem]">
<div class="absolute bottom-0 right-2 flex h-9 items-center">
<div class="flex items-center rounded bg-token-main-surface-secondary px-2 font-sans text-xs text-token-text-secondary">
<pre class="language-javascript"><code>const mysql = require('mysql');

// Create a connection to the database
const connection = mysql.createConnection({
    host: 'localhost',
    user: 'root',
    password: 'password',
    database: 'mydatabase'
});

// Connect to the MySQL server
connection.connect((err) =&gt; {
    if (err) {
        console.error('Error connecting to MySQL: ' + err.stack);
        return;
    }
    console.log('Connected to MySQL as ID ' + connection.threadId);
});
</code></pre>
</div>
</div>
</div>
</div>
</li>
</ol>
<hr />
<h3>Step 3: Delete Records Using the <code>DELETE</code> Statement</h3>
<p>Now, let's write a query to delete specific records from a table (e.g., the <code>users</code> table).</p>
<ol>
<li>
<p><strong>Delete a Single Record</strong>:</p>
<p>To delete a specific record based on a condition, such as deleting a user with a specific <code>id</code>, use the <code>DELETE</code> query:</p>
<div class="dark bg-gray-950 contain-inline-size rounded-md border-[0.5px] border-token-border-medium relative">
<div class="flex items-center text-token-text-secondary bg-token-main-surface-secondary px-4 py-2 text-xs font-sans justify-between rounded-t-md h-9">javascript</div>
<div class="sticky top-9 md:top-[5.75rem]">
<div class="absolute bottom-0 right-2 flex h-9 items-center">
<div class="flex items-center rounded bg-token-main-surface-secondary px-2 font-sans text-xs text-token-text-secondary">
<pre class="language-javascript"><code>// Query to delete a user with a specific id
const deleteQuery = 'DELETE FROM users WHERE id = 5';

connection.query(deleteQuery, (err, results) =&gt; {
    if (err) {
        console.error('Error deleting record: ' + err.stack);
        return;
    }
    console.log('Record deleted successfully.');
});

// Close the connection
connection.end();
</code></pre>
</div>
</div>
</div>
</div>
</li>
</ol>
<p>In this example, the query deletes the row where <code>id = 5</code> from the <code>users</code> table.</p>
<hr />
<h3>Step 4: Run the Application</h3>
<p>To run the Node.js script and delete the record, execute the following command in your terminal:</p>
<div class="dark bg-gray-950 contain-inline-size rounded-md border-[0.5px] border-token-border-medium relative">
<div class="flex items-center text-token-text-secondary bg-token-main-surface-secondary px-4 py-2 text-xs font-sans justify-between rounded-t-md h-9">bash</div>
<div class="sticky top-9 md:top-[5.75rem]">
<div class="absolute bottom-0 right-2 flex h-9 items-center">
<div class="flex items-center rounded bg-token-main-surface-secondary px-2 font-sans text-xs text-token-text-secondary">
<pre class="language-javascript"><code>node app.js
</code></pre>
</div>
</div>
</div>
</div>
<p>After running the query, the record with <code>id = 5</code> will be deleted from the <code>users</code> table if it exists.</p>
<hr />
<h3>Step 5: Delete Multiple Records</h3>
<p>You can delete multiple records by using conditions in the <code>WHERE</code> clause. For example, to delete all users who are older than 30:</p>
<div class="dark bg-gray-950 contain-inline-size rounded-md border-[0.5px] border-token-border-medium relative">
<div class="flex items-center text-token-text-secondary bg-token-main-surface-secondary px-4 py-2 text-xs font-sans justify-between rounded-t-md h-9">javascript</div>
<div class="sticky top-9 md:top-[5.75rem]">
<div class="absolute bottom-0 right-2 flex h-9 items-center">
<div class="flex items-center rounded bg-token-main-surface-secondary px-2 font-sans text-xs text-token-text-secondary">
<pre class="language-javascript"><code>const deleteQuery = 'DELETE FROM users WHERE age &gt; 30';

connection.query(deleteQuery, (err, results) =&gt; {
    if (err) {
        console.error('Error deleting records: ' + err.stack);
        return;
    }
    console.log('Records deleted successfully.');
});

connection.end();
</code></pre>
</div>
</div>
</div>
</div>
<p>This query will delete all users from the <code>users</code> table whose age is greater than 30.</p>
<hr />
<h3>Step 6: Delete All Records in a Table</h3>
<p>If you want to delete all records from a table without dropping the table itself, use the <code>DELETE</code> statement without a <code>WHERE</code> clause:</p>
<div class="dark bg-gray-950 contain-inline-size rounded-md border-[0.5px] border-token-border-medium relative">
<div class="flex items-center text-token-text-secondary bg-token-main-surface-secondary px-4 py-2 text-xs font-sans justify-between rounded-t-md h-9">javascript</div>
<div class="sticky top-9 md:top-[5.75rem]">
<div class="absolute bottom-0 right-2 flex h-9 items-center">
<div class="flex items-center rounded bg-token-main-surface-secondary px-2 font-sans text-xs text-token-text-secondary">
<pre class="language-javascript"><code>const deleteQuery = 'DELETE FROM users';

connection.query(deleteQuery, (err, results) =&gt; {
    if (err) {
        console.error('Error deleting all records: ' + err.stack);
        return;
    }
    console.log('All records deleted successfully.');
});

connection.end();
</code></pre>
</div>
</div>
</div>
</div>
<p>This query will remove <strong>all rows</strong> from the <code>users</code> table, but the table structure will remain intact.</p>
<hr />
<h3>Step 7: Use Prepared Statements to Delete Records</h3>
<p>For security reasons, especially when dealing with user input, always use prepared statements to avoid SQL injection attacks.</p>
<ol>
<li>
<p><strong>Delete a Record Using a Prepared Statement</strong>:</p>
<p>Here&rsquo;s how to delete a specific user by using a prepared statement with placeholders:</p>
<div class="dark bg-gray-950 contain-inline-size rounded-md border-[0.5px] border-token-border-medium relative">
<div class="flex items-center text-token-text-secondary bg-token-main-surface-secondary px-4 py-2 text-xs font-sans justify-between rounded-t-md h-9">javascript</div>
<div class="sticky top-9 md:top-[5.75rem]">
<div class="absolute bottom-0 right-2 flex h-9 items-center">
<div class="flex items-center rounded bg-token-main-surface-secondary px-2 font-sans text-xs text-token-text-secondary">
<pre class="language-javascript"><code>const userId = 5;

// Query using a placeholder to delete a record safely
const deleteQuery = 'DELETE FROM users WHERE id = ?';

connection.query(deleteQuery, [userId], (err, results) =&gt; {
    if (err) {
        console.error('Error deleting record: ' + err.stack);
        return;
    }
    console.log(`Record with id ${userId} deleted successfully.`);
});

connection.end();
</code></pre>
</div>
</div>
</div>
</div>
</li>
</ol>
<p>In this example, the <code>?</code> is a placeholder for the <code>userId</code>, and the value is passed securely in the array <code>[userId]</code>.</p>
<hr />
<h3>Step 8: Using Conditions with the <code>WHERE</code> Clause</h3>
<p>The <code>WHERE</code> clause allows you to define specific conditions for deleting data. You can use various operators in the <code>WHERE</code> clause:</p>
<ul>
<li>
<p><strong>Equal to</strong> (<code>=</code>):</p>
<div class="dark bg-gray-950 contain-inline-size rounded-md border-[0.5px] border-token-border-medium relative">
<div class="flex items-center text-token-text-secondary bg-token-main-surface-secondary px-4 py-2 text-xs font-sans justify-between rounded-t-md h-9">javascript</div>
<div class="sticky top-9 md:top-[5.75rem]">
<div class="absolute bottom-0 right-2 flex h-9 items-center">
<div class="flex items-center rounded bg-token-main-surface-secondary px-2 font-sans text-xs text-token-text-secondary">
<pre class="language-javascript"><code>const deleteQuery = 'DELETE FROM users WHERE id = 5';
</code></pre>
</div>
</div>
</div>
</div>
</li>
<li>
<p><strong>Not equal to</strong> (<code>!=</code> or <code>&lt;&gt;</code>):</p>
<div class="dark bg-gray-950 contain-inline-size rounded-md border-[0.5px] border-token-border-medium relative">
<div class="flex items-center text-token-text-secondary bg-token-main-surface-secondary px-4 py-2 text-xs font-sans justify-between rounded-t-md h-9">javascript</div>
<div class="sticky top-9 md:top-[5.75rem]">
<div class="absolute bottom-0 right-2 flex h-9 items-center">
<div class="flex items-center rounded bg-token-main-surface-secondary px-2 font-sans text-xs text-token-text-secondary">
<pre class="language-javascript"><code>const deleteQuery = 'DELETE FROM users WHERE age != 25';
</code></pre>
</div>
</div>
</div>
</div>
</li>
<li>
<p><strong>Less than or greater than</strong> (<code>&lt;</code>, <code>&gt;</code>, <code>&lt;=</code>, <code>&gt;=</code>):</p>
<div class="dark bg-gray-950 contain-inline-size rounded-md border-[0.5px] border-token-border-medium relative">
<div class="flex items-center text-token-text-secondary bg-token-main-surface-secondary px-4 py-2 text-xs font-sans justify-between rounded-t-md h-9">javascript</div>
<div class="sticky top-9 md:top-[5.75rem]">
<div class="absolute bottom-0 right-2 flex h-9 items-center">
<div class="flex items-center rounded bg-token-main-surface-secondary px-2 font-sans text-xs text-token-text-secondary">
<pre class="language-javascript"><code>const deleteQuery = 'DELETE FROM users WHERE age &lt;= 40';
</code></pre>
</div>
</div>
</div>
</div>
</li>
<li>
<p><strong>Like</strong> (for pattern matching):</p>
<div class="dark bg-gray-950 contain-inline-size rounded-md border-[0.5px] border-token-border-medium relative">
<div class="flex items-center text-token-text-secondary bg-token-main-surface-secondary px-4 py-2 text-xs font-sans justify-between rounded-t-md h-9">javascript</div>
<div class="sticky top-9 md:top-[5.75rem]">
<div class="absolute bottom-0 right-2 flex h-9 items-center">
<div class="flex items-center rounded bg-token-main-surface-secondary px-2 font-sans text-xs text-token-text-secondary">
<pre class="language-javascript"><code>const deleteQuery = 'DELETE FROM users WHERE name LIKE "J%"';
</code></pre>
</div>
</div>
</div>
</div>
</li>
</ul>
<hr />
<h3>Conclusion</h3>
<p>In this guide, you&rsquo;ve learned how to:</p>
<ul>
<li>Use the <code>DELETE</code> statement to remove records from a MySQL table using Node.js.</li>
<li>Delete a single record, multiple records, or all records from a table.</li>
<li>Use prepared statements to prevent SQL injection.</li>
</ul>
<p>Deleting records is a fundamental operation, and using prepared statements helps make it more secure and reliable.</p>
</div>
</div>
</div>
</div>
</div>
</div>

Web Technology View More