NodeJs MySql

Last modified on: October 20, 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="7344e8c5-d2c2-4a46-8db9-d0141486c4de" data-message-model-slug="gpt-4o-mini">
<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 with MySQL: A Comprehensive Guide</h3>
<p>Using MySQL with Node.js allows you to create powerful applications that can store and retrieve data efficiently. This guide will walk you through setting up a Node.js application that connects to a MySQL database, executes queries, and manages data.</p>
<hr />
<h3>What is MySQL?</h3>
<p><strong>MySQL</strong> is an open-source relational database management system (RDBMS) that uses Structured Query Language (SQL) for accessing and managing the database. It&rsquo;s widely used for web applications and is known for its reliability and ease of use.</p>
<hr />
<h3>Setting Up Your Environment</h3>
<p>Before you start, ensure you have the following installed:</p>
<ol>
<li><strong>Node.js</strong>: Download and install from <a href="https://nodejs.org/" target="_new" rel="noopener">Node.js official site</a>.</li>
<li><strong>MySQL</strong>: Download and install MySQL from <a href="https://www.mysql.com/" target="_new" rel="noopener">MySQL official site</a>.</li>
</ol>
<hr />
<h3>Create a New MySQL Database</h3>
<ol>
<li>
<p>Open your MySQL command-line client or MySQL Workbench.</p>
</li>
<li>
<p>Create a new database:</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">sql</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>CREATE DATABASE mydatabase;</code></pre>
</div>
</div>
</div>
</div>
</li>
<li>
<p>Use the new database:</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">sql</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>USE mydatabase;</code></pre>
</div>
</div>
</div>
</div>
</li>
<li>
<p>Create a table to store data:</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">sql</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>CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100),
    email VARCHAR(100)
);</code></pre>
</div>
</div>
</div>
</div>
</li>
</ol>
<hr />
<h3>Setting Up a Node.js Project</h3>
<ol>
<li>
<p><strong>Create a new directory for your 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>mkdir mysql-node
cd mysql-node</code></pre>
</div>
</div>
</div>
</div>
</li>
<li>
<p><strong>Initialize a new 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>
</div>
</div>
</div>
</div>
</li>
<li>
<p><strong>Install the required packages</strong>:</p>
<p>You will need to install the <strong>MySQL</strong> package:</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>Connecting to MySQL in Node.js</h3>
<ol>
<li>
<p><strong>Create a new file named <code>server.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 server.js</code></pre>
</div>
</div>
</div>
</div>
</li>
<li>
<p><strong>Open <code>server.js</code> and add the following 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">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', // Your MySQL server host
    user: 'root',      // Your MySQL username
    password: 'password', // Your MySQL password
    database: 'mydatabase' // Your MySQL database name
});

// Connect to the database
connection.connect((err) =&gt; {
    if (err) {
        return console.error('Error connecting: ' + err.stack);
    }
    console.log('Connected as ID ' + connection.threadId);
});

// Close the connection
connection.end();</code></pre>
</div>
</div>
</div>
</div>
</li>
</ol>
<h3>Explanation of the Code</h3>
<ul>
<li><strong>MySQL Connection</strong>: We create a connection to the MySQL database using the <code>mysql.createConnection</code> method. Replace the <code>host</code>, <code>user</code>, <code>password</code>, and <code>database</code> with your actual MySQL credentials.</li>
<li><strong>Connecting to the Database</strong>: The <code>connect</code> method establishes the connection and checks for any errors.</li>
<li><strong>Closing the Connection</strong>: The <code>end</code> method closes the connection after use.</li>
</ul>
<hr />
<h3>Performing CRUD Operations</h3>
<p>Now that you can connect to the MySQL database, let's implement basic CRUD (Create, Read, Update, Delete) operations.</p>
<h4>Create a User</h4>
<p>Add the following code to insert a new user into the <code>users</code> table:</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>// Create a new user
const newUser = { name: 'John Doe', email: 'john@example.com' };

connection.query('INSERT INTO users SET ?', newUser, (error, results) =&gt; {
    if (error) {
        return console.error('Error inserting user: ' + error.stack);
    }
    console.log('User created with ID: ' + results.insertId);
});</code></pre>
</div>
</div>
</div>
</div>
<h4>Read Users</h4>
<p>To fetch all users from the database, add this 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>// Read users
connection.query('SELECT * FROM users', (error, results) =&gt; {
    if (error) {
        return console.error('Error fetching users: ' + error.stack);
    }
    console.log('Users: ', results);
});</code></pre>
</div>
</div>
</div>
</div>
<h4>Update a User</h4>
<p>To update a user's details, add this 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>// Update a user
const userId = 1; // ID of the user to update
const updatedUser = { name: 'Jane Doe', email: 'jane@example.com' };

connection.query('UPDATE users SET ? WHERE id = ?', [updatedUser, userId], (error, results) =&gt; {
    if (error) {
        return console.error('Error updating user: ' + error.stack);
    }
    console.log('User updated: ', results.affectedRows);
});</code></pre>
</div>
</div>
</div>
</div>
<h4>Delete a User</h4>
<p>To delete a user, add this 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>// Delete a user
const deleteUserId = 1; // ID of the user to delete

connection.query('DELETE FROM users WHERE id = ?', deleteUserId, (error, results) =&gt; {
    if (error) {
        return console.error('Error deleting user: ' + error.stack);
    }
    console.log('User deleted: ', results.affectedRows);
});</code></pre>
</div>
</div>
</div>
</div>
<hr />
<h3>Complete <code>server.js</code> Example</h3>
<p>Here&rsquo;s how the complete <code>server.js</code> file would look with all CRUD operations:</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'
});

// Create a new user
    const newUser = { name: 'John Doe', email: 'john@example.com' };
    connection.query('INSERT INTO users SET ?', newUser, (error, results) =&gt; {
        if (error) {
            return console.error('Error inserting user: ' + error.stack);
        }
        console.log('User created with ID: ' + results.insertId);

// Read users
        connection.query('SELECT * FROM users', (error, results) =&gt; {
            if (error) {
                return console.error('Error fetching users: ' + error.stack);
            }
            console.log('Users: ', results);

// Update a user
            const userId = results[0].id; // ID of the first user
            const updatedUser = { name: 'Jane Doe', email: 'jane@example.com' };

// Delete a user
                connection.query('DELETE FROM users WHERE id = ?', userId, (error, results) =&gt; {
                    if (error) {
                        return console.error('Error deleting user: ' + error.stack);
                    }
                    console.log('User deleted: ', results.affectedRows);

// Close the connection
                    connection.end();
                });
            });
        });
    });
});</code></pre>
</div>
</div>
</div>
</div>
<h3>Conclusion</h3>
<p>In this guide, you learned how to integrate MySQL with a Node.js application, covering the following:</p>
<ul>
<li>Setting up a Node.js project and MySQL database.</li>
<li>Connecting to MySQL using Node.js.</li>
<li>Performing CRUD operations (Create, Read, Update, Delete) on a MySQL database.</li>
</ul>
<p>With this knowledge, you can build robust applications that interact with a MySQL database efficiently.</p>
</div>
</div>
</div>
</div>
</div>
</div>

Web Technology View More