How to Automate PostgreSQL Query by Node.js

PostgreSQL, a powerful open-source database system, is widely used for its robustness and extensibility. While it offers a rich set of features, manually executing SQL queries can be time-consuming, especially when dealing with repetitive tasks or large datasets. Automating PostgreSQL queries empowers developers to manage database tasks efficiently, improving productivity and reducing manual effort.

It can help you with efficient data migration, scheduled data backups, report generation, etc which can embrace any organization with improved efficiency and productivity. In this detailed guide, we will look at different methods of automating PostgreSQL queries:

1. Method #1: Automate PostgreSQL Query by Node.js
2. Method #2: Automating PostgreSQL Query through Locale

‍

Method #1: Automate PostgreSQL Query by Node.js

1. Install the pg package:

Use npm (Node Package Manager) to install the pg package by running the following command in your terminal:‍

npm install pg

‍

2. Set up a PostgreSQL connection:

Create a file in your Node.js project where you will set up the connection and write your query. Let’s name it database.js.

‍

3. Require the pg package and set up a Pool or Client:

Here’s an example using Pool, which manages multiple client connections for you and is suitable for most use cases:

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

const pool = new Pool({
  user: 'your_username',
  host: 'localhost',
  database: 'your_database',
  password: 'your_password',
  port: 5432, // default port for PostgreSQL
});

Replace your_username, your_database, and your_password with your PostgreSQL credentials.

‍

4. Write a function to execute the SQL query:

const queryText = 'SELECT * FROM your_table_name WHERE condition = $1;';

// async function to run the query
async function runQuery() {
  try {
    const res = await pool.query(queryText, [valueForCondition]);
    console.log(res.rows); // outputs the query results
  } catch (err) {
    console.error(err);
  } finally {
    // close the pool if no more queries are to be made
    await pool.end();
  }
}

runQuery();

Replace your_table_name with the name of your table and adjust the queryText to match your desired SQL query. The $1 is a placeholder for parameterized queries which helps prevent SQL injection. The valueForCondition is the value that will replace $1 when the query is run. You can run this by using

node database.js

‍

5. Schedule the script with Cron

Once you have the above script ready, let’s add a cron to run this script on a given cron schedule. Launch your crontab file with crontab -e and add a new line to schedule your node script. For example, to run it every 5 minutes, add:

*/5 * * * * node database.js

‍

Save and exit the editor to activate the new cron job. By following these steps, your node script will now run every 5 minutes, just like a cron job.

‍

Going through the above method require lot of manual work and prior knowledge of node and how to set cron jobs. it also requires a rigorous testing, and ongoing maintenance. Especially when there is requirement for a small change there might need a lot of change in the script, the overhead can add up quickly. Also, it’s complex to deduce whether a query has run successfully and you might need constant monitoring of the logs to know whether it has run successfully or failed.

In the following section, Let’s see how Locale significantly trims down this process, and automating PostgreSQL queries is an easy deal.

‍

Method #2: Automating PostgreSQL Query through Locale

Locale is a no-code platform which significantly simplifies the process of PostgreSQL query runs. With just a few clicks, you can automate the PostgreSQL queries and with Locale you can also get notified on your preferred channel about the query ran status.

‍

Step 1: Login to Locale, Go to Org Setting and Connect your Postgres database from the data source.

‍

Step 2: Now go to Alerts → Create New Alerts. Choose your database → Click on Setup data to be monitored.

‍

Step 3:  Write your own SQL Query and click Run to verify the data. Once you are satisfied, click Done.

‍

Step 4: Now Setup your schedule based on your requirement, this would automatically run the query based on the frequency you set. Click on Run the query at frequency and choose your schedule

‍

Step 5: With Locale, You can also get notified on you preferred communication channel about the success or failure of the query run. To configure the Slack message, Click on Configure incidents and enter your message in the Incident Title.

You can also customise the message based on the query result by clicking on { } icon.

‍

Step 6: Final Step, Set up whom or on which channel the Slack alert should be sent. Go to the Setup Notification section and click on Add Notification then select Send Slack Message. Select whether you want the Slack message to be sent to a channel or a user. You can also preview and send test notifications.

‍

Save the alert. It will automatically run itself on the pre-set schedule and trigger a Slack notification.

‍

Conclusion

Automating PostgreSQL queries is essential for optimizing efficiency and productivity in managing database tasks. While the traditional method of using Node.js and cron jobs provides a solution, it involves manual work, requires prior knowledge of Node.js, and demands continuous monitoring and maintenance.

Locale, offers a streamlined and hassle-free approach to automate PostgreSQL queries with minimal one-time setup. It also provides you with the option to choose a schedule for sending messages based on your requirements. It can dynamically choose people to send Slack message based on the query result allowing alerts only go to the person it needs, and even allowing to group the notifications ensuring less noise to the users.

Locale not only simplifies the process but also enhances reliability by providing instant notifications about query run statuses. This eliminates the need for constant manual checks, ensuring that you stay informed about the success or failure of your queries.

‍

Excited to get started? It takes less than 10 minutes to get all things set up: Try for free