Queries with pg

In the realm of Node.js and database integration, executing SQL queries is an essential task. pg, a specific Node.js library for PostgreSQL, enables you to carry out these operations smoothly. This topic delves into executing SELECT, UPDATE, DELETE, and INSERT queries. You'll gain the understanding and practical insights necessary to work efficiently with pg. Let's embark on this journey to master the art of database queries using pg.

Setting up a connection pool

Before diving into executing SQL queries, it's vital to establish the underlying infrastructure. This infrastructure encompasses the necessary components enabling your Node.js application to communicate and interact with the database. A key element for efficient database interactions is the connection pool.

A connection pool is a cache of database connections maintained so that the connections can be reused when needed. It boosts the performance of executing commands on a database because it eliminates the overhead of establishing a new connection every time a user sends a request.

The code snippet below shows how to create a connection pool using the pg library. This connection pool forms the basis for executing your queries.

const { Pool } = require('pg');

const pool = new Pool({
  user: 'your_user',
  host: 'your_host',
  database: 'your_database',
  password: 'your_password',
  port: 5432,
});

The code snippet provided demonstrates the creation of a connection pool using the pg library in Node.js. This connection pool is a crucial part of the infrastructure that facilitates efficient interactions with your PostgreSQL database.

It's important to note that sensitive information, such as passwords, should never be hard-coded directly into your source code. Instead, consider storing this sensitive information in environment variables. This practice improves your application's security by safeguarding it against unauthorized access and potential data breaches.

With this connection pool set up, you're now ready to execute SQL queries on your PostgreSQL database from your Node.js application.

Executing select queries

To harness the pg capabilities, it's essential to understand how to retrieve data from a PostgreSQL database. This section focuses on executing SELECT queries using pg.

SELECT query basics

A SELECT query is a fundamental SQL command used to fetch data from a database. It lets you specify the precise data you need, from specific columns to filtered rows based on specific conditions. Understanding how to construct and execute a SELECT query is an essential skill in database management.

Now, let's explore the fundamental structure of a SELECT query with the pg library and how to retrieve specific data from your database.

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

The code snippet demonstrates how to use pg to execute a SELECT query. It selects all records from the products table and logs the results. This is a foundational step to retrieve data from a PostgreSQL database. 'SELECT * FROM products' is an SQL query itself, and it's written as plain text. It doesn't use any placeholders for parameters. Therefore, it's a text query.

Modifying data with update and delete queries

In database management, you frequently need to modify data, not just retrieve it. This section presents how to execute UPDATE and DELETE queries using pg.

Updating data

To update records in your database, you use UPDATE queries. Here is an example of how it works:

// Update a record
const updateQuery = {
  text: 'UPDATE products SET price = $1 WHERE id = $2',
  values: [19.99, 42],
};
pool.query(updateQuery, (updateError, results) => {
  if (updateError) {
    throw updateError;
  }
  console.log('Record updated successfully.');
});

In the above code snippet, you execute an UPDATE query to change a product's price with a specific ID. The SET clause defines the changes you want to make, and the WHERE clause specifies which record to update. The outcome is precise and efficient data modification.

updateQuery is an object that contains two properties:

• The 'text' property contains the SQL query with placeholders. In this instance, it's an UPDATE query that modifies the "price" column in the "products" table, using two placeholders, $1 and$2.
• The 'values' property is an array containing the values that will substitute the placeholders. For instance, in the array [19.99, 42], '1' will be replaced with 19.99 whereas '2' will be replaced with 42.

Deleting data

Sometimes, you need to remove records from your database. DELETE queries come to the rescue. Here is how it is done:

// Delete a record
const deleteQuery = {
  text: 'DELETE FROM products WHERE id = $1',
  values: [42],
};
pool.query(deleteQuery, (deleteError, deleteResults) => {
  if (deleteError) {
    throw deleteError;
  }
  console.log('Record deleted successfully.');
});

In the DELETE query snippet, you remove a specific product from the products table based on its ID. DELETE queries are essential for data maintenance and keeping your database up to date.

Inserting data with insert queries

Adding new data to your database is a fundamental aspect of database management. INSERT queries play a pivotal role in this process. They allow you to add new records efficiently, making them an indispensable tool in your database toolkit.

// Insert a new record
const query = {
  text: 'INSERT INTO products(name, price) VALUES($1, $2)',
  values: ['New Product', 24.99],
};
pool.query(query, (error, results) => {
  if (error) {
    throw error;
  }
  console.log('New record inserted successfully.');
});

You execute an INSERT query to add a new product to the products table. This illustrates how pg streamlines the process of data insertion, enabling you to manage your database efficiently. Whether you're adding new products, user information, or any other data, INSERT queries are your go-to tool for database updates.

Conclusion

You have now mastered the art of executing SQL queries with pg library in Node.js. You can retrieve data, modify records, insert new entries, and execute queries asynchronously. Whether you're building a web application, creating a backend service, or managing a database, these skills will prove invaluable.

With the skills you've acquired in executing SQL queries using the pg library in Node.js, you're now well-equipped to handle various database operations. This knowledge will significantly enhance your ability to develop robust Node.js applications using PostgreSQL. IIt's essential to explore more advanced topics to further enhance your database development skills.