npm mysql Command

npm MySQL integrates MySQL, a popular open-source relational database, with Node.js applications using Node Package Manager (npm). MySQL is widely used to manage structured data. When combined with Node.js, it allows developers to build dynamic, scalable, and powerful web applications that interact with databases.

npm plays an important role in Node.js applications by managing MySQL and other dependencies. through various packages By installing the mysql or mysql2 package via npm, developers can easily connect Node.js applications to MySQL databases, querying, retrieving data, and managing database operations, making npm indispensable for modern web development because it makes managing dependencies easier.

Installation

Before working with npm and MySQL, make sure you have the following set up:

Node.js Installed on Your System

Node.js allows you to run JavaScript on the server. Download and install it from the official Node.js website.

For Windows:

• Download and run the installer from the Node.js website.
• After installation, verify it by running the following commands in Command Prompt (or PowerShell):

node -v
npm -v

• If both versions are returned, Node.js and npm are successfully installed.

For macOS:

brew install node

For Ubuntu/Debian:

sudo apt update
sudo apt install nodejs
sudo apt install npm

npm Installed (comes by default with Node.js)

npm (Node Package Manager) is included with Node.js. Verify its installation by running:

npm -v

MySQL Installed and Running on Your Local Machine or Server

MySQL is the relational database system you’ll be using. Install it as follows:

For Windows:

• Download and install MySQL from the official MySQL website.
• Use the "MySQL Installer for Windows" and complete the setup, including setting the root password.
• After installation, start MySQL from Command Prompt:

net start mysql

• To log in:

mysql -u root -p

• Enter the root password to access the MySQL server.

For macOS (using Homebrew):

brew install mysql
brew services start mysql

For Ubuntu/Debian:

sudo apt update
sudo apt install mysql-server
sudo service mysql start

Use and importance of npm and MySQL together

The combination of npm and MySQL in a Node.js application plays an important role in building dynamic and scalable web applications. Node.js is a powerful JavaScript runtime environment that is widely used. When you integrate a relational database like MySQL, it gives your application the ability to store, manipulate, and retrieve structured data efficiently.

The mysql package available through npm (Node Package Manager) is required for enabling database interaction in Node.js applications. This package enables the process of connecting to a MySQL database, executing SQL queries, and Manage results more easily All of this is contained within your JavaScript code.

Installing the MySQL package via npm

To use MySQL in a Node.js application, you need to install the mysql or mysql2 package before npm. You can do this by running the following command in your project directory.

npm install mysql

or

npm install mysql2

This command will download and install the MySQL package and add it as a dependency in your project’s package.json file, making it easy to manage and update later.

Role of the MySQL Package in Node.js Applications

The mysql package facilitates all major database operations within a Node.js application:

Connecting to the Database

The mysql package allows you to create a connection to a MySQL database using simple configuration parameters such as the host, user, password, and database name. Here’s an example:

const mysql = require('mysql');
const connection = mysql.createConnection({
    host: 'localhost',
    user: 'root',
    password: 'password',
    database: 'test_db'
});

connection.connect((err) => {
    if (err) {
        console.error('Error connecting to the database:', err);
        return;
    }
    console.log('Connected to the MySQL database');
});

Output:

Connected to the MySQL database

Querying the Database

Once connected, you can easily run SQL queries to interact with the database. For example, retrieving data from a table:

connection.query('SELECT * FROM users', (err, results) => {
    if (err) throw err;
    console.log('User data:', results);
});

Users table has content as:

Output:

User data: [
  { id: 1, name: 'Shalini', age: 21 },
  { id: 2, name: 'Arun', age: 22 },
  { id: 3, name: 'Jimmy', age: 7 }
]

Inserting Data

You can insert data into your database using SQL INSERT statements:

const user = { name: 'Anvay', age: 22 };
connection.query('INSERT INTO users SET ?', user, (err, result) => {
    if (err) throw err;
    console.log('Data inserted with ID:', result.insertId);
});

Output:

Data inserted with ID: 4

Retrieving Data

The package allows you to retrieve data efficiently with various queries, like filtering data or using aggregate functions.

connection.query('SELECT name, age FROM users WHERE age > 21', (err, results) => {
    if (err) throw err;
    console.log('Filtered user data:', results);
});

Output:

Filtered user data: [
  { name: 'Arun', age: 22 },
  { name: 'Anvay', age: 22 }
]

Why npm and MySQL are Important Together?

• Seamless Integration: The mysql package from npm integrates smoothly with Node.js, making it easier to write and maintain database-related code in JavaScript.
• Dependency Management: npm makes it simple to install, manage, and update the MySQL package, ensuring that your project uses the latest features and security updates.
• Scalability: With Node.js handling asynchronous operations, combined with MySQL’s powerful data storage capabilities, you can build scalable applications capable of handling large volumes of users and data efficiently.

Features of the mysql npm package

The mysql npm package has several important features that are very useful for integrating MySQL databases with Node.js applications. These features help improve database operations. ensuring safety scalability and efficiency in web development

Easy database connection

One of the main advantages of the mysql package is its ability to easily create connections between Node.js and MySQL. with simple settings You can quickly configure your database connection using some parameters such as host, username, password, and database name:

const mysql = require('mysql');
const connection = mysql.createConnection({
    host: 'localhost',
    user: 'root',
    password: 'password',
    database: 'test_db'
});

connection.connect((err) => {
    if (err) {
        console.error('Error connecting:', err);
        return;
    }
    console.log('Connected to MySQL database');
});

This simple setup makes the mysql package easy for beginners as well. It allows you to seamlessly interact with the MySQL database within your Node.js application.

Output:

Connected to MySQL database

Asynchronous execution

The mysql package supports asynchronous database operations. This is required in a non-blocking environment such as Node.js, which ensures that database queries do not interfere with other operations. Asynchronous handling can be done using callbacks, promises (in the mysql2 package), or the asynchronous/wait model

Example:This callback example retrieves all rows from the "users" table, logs the results, and throws an error if any occurs during the query execution.

connection.query('SELECT * FROM users', (err, results) => {
    if (err) throw err;
    console.log(results);
});

Example: This async/await example retrieves all rows from the "users" table using the mysql2/promise library and logs the results.

const mysql = require('mysql2/promise');
const connection = await mysql.createConnection({/* configuration */});

const [rows, fields] = await connection.execute('SELECT * FROM users');
console.log(rows);

This asynchronous behavior helps the application scale efficiently. Handle multiple database queries without performance bottlenecks.

Prepared statements

The mysql package supports prepared statements, which is an important feature for improving database security. Prepared statements protect against SQL injection attacks by allowing parameterized queries. where user input is considered data. Instead of executable SQL code This ensures that no malicious input can modify database commands.

const user = { name: 'John', age: 30 };
connection.query('INSERT INTO users SET ?', user, (err, result) => {
    if (err) throw err;
    console.log('Data inserted:', result.insertId);
});

Multiple Connection Support

For larger applications that need to handle multiple simultaneous database connections efficiently, the mysql package provides support for connection pooling. A connection pool allows you to reuse database connections rather than creating a new one for every request, improving performance and resource management.

const pool = mysql.createPool({
    connectionLimit: 10,
    host: 'localhost',
    user: 'root',
    password: 'password',
    database: 'test_db'
});

pool.query('SELECT * FROM users', (err, results) => {
    if (err) throw err;
    console.log(results);
});

Creating a Table in mysql

Example: This example demonstrates the creation of a table `geeks`.

const createTable = `
  CREATE TABLE IF NOT EXISTS geeks (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    email VARCHAR(255) NOT NULL UNIQUE
  )
`;

connection.query(createTable, (err, result) => {
    if (err) throw err;
    console.log("Table created");
});

Output:

Table created

Inserting Data into the Table

Example: This example demonstrates the insertion of data in the table 'geeks'.

const insertUser = `INSERT INTO geeks (name, email) VALUES ('Geek1', 'abc@example.com')`;

connection.query(insertUser, (err, result) => {
    if (err) throw err;
    console.log(`User inserted, ID: ${result.insertId}`);
});

Output:

User inserted, ID: 1

Selecting Data from the Table

Example: This example demonstrates the selection of data from the table 'geeks'.

const selectUsers = `SELECT * FROM geeks`;

connection.query(selectUsers, (err, results) => {
    if (err) throw err;
    console.log('Users:', results);
});

Output:

Users: [ { id: 1, name: 'Geek1', email: 'abc@example.com' } ]

Updating Data in the Table

Example: This example demonstrates the updation of data in the table 'geeks'.

const updateUser = `UPDATE geeks SET email = 'abcde@example.com' WHERE id = 1`;

connection.query(updateUser, (err, result) => {
    if (err) throw err;
    console.log(`Changed ${result.changedRows} row(s)`);
});

Output:

Changed 1 row(s)

Deleting Data from the Table

Example: This example demonstrates the Deletion of data from the table 'geeks'.

const deleteUser = `DELETE FROM geeks WHERE id = 1`;

connection.query(deleteUser, (err, result) => {
    if (err) throw err;
    console.log(`Deleted ${result.affectedRows} row(s)`);
});

Output:

Deleted 1 row(s)

Closing the Connection

Example: This example demonstrates the closing of the connection.

connection.end((err) => {
    if (err) throw err;
    console.log('Connection closed');
});

Output:

Connection closed