NodeJS And SQL Database

How do you connect a Node.js application to a SQL database?

To connect a Node.js application to a SQL database, you typically use a Node.js driver or an ORM (Object-Relational Mapping) library. For example, for MySQL, you can use the mysql or mysql2 package, and for PostgreSQL, you can use pg. These libraries allow you to establish a connection to the database, run queries, and handle results.

Example of connecting to a MySQL database using the mysql package:

const mysql = require('mysql');

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

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

How do you execute SQL queries in Node.js?

Once you have connected to a SQL database, you can execute SQL queries using the query() method provided by the database driver. You can run SELECT, INSERT, UPDATE, and DELETE queries to interact with the database.

Example of executing a SELECT query using MySQL:

connection.query('SELECT * FROM users', (error, results, fields) => {
    if (error) {
        console.error('Error executing query:', error);
    } else {
        console.log('Results:', results);
    }
});

In this example, the results of the SELECT query are logged to the console, and any errors are handled in the callback function.

How do you insert data into a SQL database in Node.js?

You can insert data into a SQL database using an INSERT query. You can pass the data directly into the query string or use prepared statements to avoid SQL injection vulnerabilities.

Example of inserting data using the mysql package:

const user = { name: 'John Doe', email: '[email protected]' };

connection.query('INSERT INTO users SET ?', user, (error, results, fields) => {
    if (error) {
        console.error('Error inserting data:', error);
    } else {
        console.log('Data inserted:', results.insertId);
    }
});

In this example, a user is inserted into the users table, and the newly inserted record’s ID is logged to the console.

How do you update data in a SQL database in Node.js?

To update data in a SQL database, you can use an UPDATE query. Like with insertion, you can use placeholders to avoid SQL injection.

Example of updating data:

const updatedUser = { name: 'Jane Doe' };
const userId = 1;

connection.query('UPDATE users SET ? WHERE id = ?', [updatedUser, userId], (error, results, fields) => {
    if (error) {
        console.error('Error updating data:', error);
    } else {
        console.log('Data updated:', results.affectedRows);
    }
});

In this example, the name of the user with id = 1 is updated to "Jane Doe," and the number of affected rows is logged to the console.

How do you delete data from a SQL database in Node.js?

To delete data from a SQL database, you can use the DELETE query.

Example of deleting a record:

const userId = 1;

connection.query('DELETE FROM users WHERE id = ?', [userId], (error, results, fields) => {
    if (error) {
        console.error('Error deleting data:', error);
    } else {
        console.log('Data deleted:', results.affectedRows);
    }
});

This example deletes the user with id = 1 from the users table, and the number of affected rows is logged.

How do you use prepared statements in Node.js with SQL databases?

Prepared statements allow you to safely execute SQL queries with dynamic data by separating the SQL query from the data, helping to prevent SQL injection attacks. You use placeholders (?) in the SQL query and pass the dynamic values as an array in the second argument of the query() method.

Example of using prepared statements:

const userId = 1;

connection.query('SELECT * FROM users WHERE id = ?', [userId], (error, results, fields) => {
    if (error) {
        console.error('Error executing query:', error);
    } else {
        console.log('User:', results[0]);
    }
});

In this example, the userId is safely passed into the query using a placeholder (?), preventing SQL injection.

What is Sequelize, and how does it work with SQL databases in Node.js?

Sequelize is a promise-based ORM (Object-Relational Mapping) library for Node.js that supports multiple SQL databases, including MySQL, PostgreSQL, SQLite, and MSSQL. Sequelize simplifies database interactions by providing models, associations, and query methods, allowing you to work with SQL databases using JavaScript objects and methods.

Example of defining a model and querying data using Sequelize:

const { Sequelize, DataTypes } = require('sequelize');
const sequelize = new Sequelize('mydatabase', 'root', 'password', {
    host: 'localhost',
    dialect: 'mysql'
});

const User = sequelize.define('User', {
    name: {
        type: DataTypes.STRING,
        allowNull: false
    },
    email: {
        type: DataTypes.STRING,
        allowNull: false
    }
});

async function getUsers() {
    try {
        const users = await User.findAll();
        console.log('Users:', users);
    } catch (error) {
        console.error('Error fetching users:', error);
    }
}

getUsers();

In this example, Sequelize is used to define a User model and retrieve all users from the database.

How do you handle database connection pooling in Node.js?

Connection pooling is the process of reusing database connections instead of creating a new connection for every request. This helps improve performance by reducing the overhead of establishing a new connection each time. Many Node.js SQL drivers, including mysql and pg, support connection pooling.

Example of setting up a MySQL connection pool:

const mysql = require('mysql');

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

pool.query('SELECT * FROM users', (error, results, fields) => {
    if (error) {
        console.error('Error executing query:', error);
    } else {
        console.log('Results:', results);
    }
});

In this example, a pool of 10 connections is created, allowing queries to reuse the connections efficiently.

How do you handle SQL query errors in Node.js?

SQL query errors in Node.js can be handled by passing a callback function to the query() method. The callback function takes an error parameter, which contains the error object if the query fails.

Example of handling query errors:

connection.query('SELECT * FROM non_existent_table', (error, results, fields) => {
    if (error) {
        console.error('Error executing query:', error);
    } else {
        console.log('Results:', results);
    }
});

In this example, if the table doesn’t exist, the error is logged to the console, and the query execution is stopped.

How do you close a database connection in Node.js?

To close a database connection in Node.js, you can use the connection.end() method for individual connections or the pool.end() method for connection pools. It’s important to close connections when they are no longer needed to avoid memory leaks or performance issues.

Example of closing a connection:

connection.end((error) => {
    if (error) {
        console.error('Error closing the connection:', error);
    } else {
        console.log('Connection closed');
    }
});

In this example, the database connection is closed after executing all necessary queries.