Node.js Table Creation and Data Insertion

Last Updated August 28, 2023

Node.js Table Creation and Data Insertion

Section (1.7) - Node.js Table Creation and Data Insertion

In this tutorial, you'll learn how to create tables and insert data into them using Node.js. This guide is designed as a follow-along tutorial and educational reference, providing you with code examples and helpful information to make your software development tasks easier.

Setting Up the Database

Before creating tables and inserting data, you need to set up a database. In this tutorial, we'll use MySQL as an example, but the concepts are applicable to other database systems as well.

First, install the mysql package using npm:

npm install mysql

Then, establish a connection to your MySQL server:

const mysql = require('mysql');

const connection = mysql.createConnection({
  host: 'localhost',
  user: 'your_username',
  password: 'your_password',
  database: 'your_database',
});

connection.connect((err) => {
  if (err) throw err;
  console.log('Connected to the MySQL server');
});

Replace 'your_username', 'your_password', and 'your_database' with your actual MySQL credentials and the name of the database you want to work with.

Creating a Table

To create a table, you'll need to execute a SQL CREATE TABLE query. Use the connection.query() method to send the query to the MySQL server:

const createTableQuery = `
  CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    email VARCHAR(255) UNIQUE NOT NULL,
    age INT NOT NULL
  );
`;

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

In this example, we create a users table with columns for id, name, email, and age. Customize the query to create a table that suits your needs.

Inserting Data

To insert data into the table, you'll need to execute a SQL INSERT INTO query. You can use the connection.query() method again to send the query to the MySQL server:

const insertDataQuery = `
  INSERT INTO users (name, email, age)
  VALUES ('John Doe', 'john@example.com', 30);
`;

connection.query(insertDataQuery, (err, result) => {
  if (err) throw err;
  console.log('Data inserted');
});

In this example, we insert a new user with the name 'John Doe', email 'john@example.com', and age 30. You can customize the query to insert data that suits your needs.

To insert multiple rows at once, you can use the INSERT INTO query with multiple VALUES:

const insertMultipleDataQuery = `
  INSERT INTO users (name, email, age)
  VALUES ('Alice', 'alice@example.com', 28),
         ('Bob', 'bob@example.com', 35),
         ('Charlie', 'charlie@example.com', 42);
`;

connection.query(insertMultipleDataQuery, (err, result) => {
  if (err) throw err;
  console.log('Multiple rows inserted');
});

In this example, we insert three new users into the users table.

Closing the Connection

After you've finished working with the database, remember to close the connection to avoid potential issues:

connection.end();

Frequently Asked Questions

Q: How can I retrieve data from a table using Node.js?

A: You can use the SELECT query to retrieve data from a table. Here's an example:

const selectDataQuery = 'SELECT * FROM users';

connection.query(selectDataQuery, (err, result) => {
  if (err) throw err;
  console.log(result);
});

This example retrieves all rows and columns from the users table. You can customize the SELECT query to retrieve specific columns, rows, or apply sorting and filtering.

Q: How can I update data in a table using Node.js?

A: You can use the UPDATE query to modify data in a table. Here's an example:

const updateDataQuery = `
  UPDATE users
  SET age = 31
  WHERE id = 1;
`;

connection.query(updateDataQuery, (err, result) => {
  if (err) throw err;
  console.log('Data updated');
});

In this example, we update the age column of the user with an id of 1 to 31. You can customize the UPDATE query to modify different columns or rows.

Q: How can I delete data from a table using Node.js?

A: You can use the DELETE query to remove data from a table. Here's an example:

const deleteDataQuery = `
  DELETE FROM users
  WHERE id = 1;
`;

connection.query(deleteDataQuery, (err, result) => {
  if (err) throw err;
  console.log('Data deleted');
});

In this example, we delete the user with an id of 1. You can customize the DELETE query to remove different rows based on specific conditions.

Q: How can I handle transactions in Node.js?

A: You can handle transactions using the beginTransaction(), commit(), and rollback() methods. Here's an example:

connection.beginTransaction((err) => {
  if (err) throw err;
  
  const firstQuery = `
    UPDATE users
    SET balance = balance - 100
    WHERE id = 1;
  `;

  connection.query(firstQuery, (err, result) => {
    if (err) {
      return connection.rollback(() => {
        throw err;
      });
    }

    const secondQuery = `
      UPDATE users
      SET balance = balance + 100
      WHERE id = 2;
    `;

    connection.query(secondQuery, (err, result) => {
      if (err) {
        return connection.rollback(() => {
          throw err;
        });
      }

      connection.commit((err) => {
        if (err) {
          return connection.rollback(() => {
            throw err;
          });
        }
        
        console.log('Transaction completed');
      });
    });
  });
});

In this example, we transfer 100 units from the user with id of 1 to the user with id of 2. If an error occurs during any step of the transaction, the transaction is rolled back.

Q: How can I use a different database system with Node.js?

A: You can use different database systems with Node.js by installing the appropriate package for the system and using its API to connect and interact with the database. Some popular database systems with Node.js packages include PostgreSQL, MongoDB, and SQLite. Check the documentation for the specific package to learn how to use it with Node.js.