Exploring PostgreSQL Connectivity with Node.js: A Powerful Duo - CRUD Operations

In my previous blog, we took a deep look at establishing connection between NodeJS and PostgreSQL, and now we move forward! Welcome to the realm of data manipulation! In this section, we'll dive into the exciting world of performing CRUD (Create, Read, Update, Delete) operations within your Node.js application using PostgreSQL. Buckle up, as we explore how to seamlessly interact with your database, creating, fetching, updating, and deleting records with ease.

Creating Records

To insert new data into your PostgreSQL database, you'll be utilizing the INSERT statement. Here's how to insert a new user into a hypothetical users table:

const newUser = {
  username: 'john_doe',
  email: '[e-mail]',
};

const insertQuery = 'INSERT INTO users (username, email) VALUES ($1, $2)';

client.query(insertQuery, [newUser.username, newUser.email])
  .then(() => {
    console.log('New user inserted successfully');
  })
  .catch((err) => {
    console.error('Error inserting user:', err);
  });

Reading Records

Retrieving data from the database involves the SELECT statement. Let's fetch all users from our users table:

const selectQuery = 'SELECT * FROM users';

client.query(selectQuery)
  .then((result) => {
    const users = result.rows;
    console.log('All users:', users);
  })
  .catch((err) => {
    console.error('Error fetching users:', err);
  });

You can change the query to alter the selection.

Updating Records

Modifying existing records is done with the UPDATE statement. Suppose we want to change the email of a user:

const userIdToUpdate = 1;
const newEmail = '[e-mail]';

const updateQuery = 'UPDATE users SET email = $1 WHERE id = $2';

client.query(updateQuery, [newEmail, userIdToUpdate])
  .then(() => {
    console.log('User updated successfully');
  })
  .catch((err) => {
    console.error('Error updating user:', err);
  });

Deleting Records

To remove records from the database, use the DELETE statement. For instance, let's delete a user with a specific ID:

const userIdToDelete = 2;

const deleteQuery = 'DELETE FROM users WHERE id = $1';

client.query(deleteQuery, [userIdToDelete])
  .then(() => {
    console.log('User deleted successfully');
  })
  .catch((err) => {
    console.error('Error deleting user:', err);
  });

Conclusion

With the power of PostgreSQL and the flexibility of Node.js, you now possess the tools to masterfully manipulate your database. Whether you're adding new data, fetching insights, updating records, or removing entries, your Node.js application and PostgreSQL database are seamlessly connected, allowing you to mold your data with precision and finesse.