joplin-notes/joplin notion extension/Web Development/nodejs/postgresql.md

postgresql

• Install pg module https://node-postgres.com/ For multiple queries there r 2 methods

  In Node.js, when using the pg library (node-postgres) to work with PostgreSQL databases, executing multiple queries consecutively is a common requirement. There are several ways to achieve this depending on the nature of your queries and how they should be executed (e.g., in parallel, in sequence, within a transaction). Below are some approaches:

1. Sequential Execution (with async/await)

If your queries are dependent on each other and need to be executed in a sequential manner, you can use async/await syntax to execute them one after the other.

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

const client = new Client({
  // Your database connection parameters
});

const executeQueriesSequentially = async () => {
  try {
    await client.connect();
    const result1 = await client.query('SELECT * FROM table1;');
    console.log('Result 1:', result1.rows);
    const result2 = await client.query('SELECT * FROM table2;');
    console.log('Result 2:', result2.rows);
    // Continue for more queries
  } catch (err) {
    console.error(err);
  } finally {
    await client.end();
  }
};

executeQueriesSequentially();

2. Parallel Execution

If your queries are independent of each other, you can execute them in parallel using Promise.all.

const executeQueriesInParallel = async () => {
  try {
    await client.connect();
    const promises = [
      client.query('SELECT * FROM table1;'),
      client.query('SELECT * FROM table2;'),
      // Add more queries as needed
    ];
    const results = await Promise.all(promises);
    results.forEach((result, index) => {
      console.log(`Result ${index + 1}:`, result.rows);
    });
  } catch (err) {
    console.error(err);
  } finally {
    await client.end();
  }
};

executeQueriesInParallel();

3. Using Transactions

For cases where your queries must be executed as a single atomic operation, you will want to use a transaction. This is particularly important i    

Defending from SQL injection

Also this way will prevent from situation like when we use insert into tablename values ('${str}') and if str is i won't do then it will like insert into tablename values ('i won't do) so strng will be i won as later is single quote closing in.

This method is called parameterized queries or prepared statements instead of manually constructing query strings which handles sql injection and escaping characters.

• For PostgreSQL (pg package example):

    const { Pool } = require('pg')
    const pool = new Pool()
  
    const text = 'INSERT INTO your_table(name) VALUES($1,$2)'
    const values = [`there isn't anything to do`, `ok go`]
  
    pool.query(text, values, (err, res) => {
      if (err) {
    	console.log(err.stack)
      } else {
    	console.log(res.rows[0])
      }
    })
  

• For MySQL (mysql package example):

  const mysql = require('mysql');
  const connection = mysql.createConnection({
    host: 'example.org',
    user: 'your_user',
    password: 'your_pass',
    database: 'your_db'
  });
  
  connection.connect();
  
  const post  = {name: `there isn't anything to do`};
  const query = connection.query('INSERT INTO your_table SET ?', post, (err, result) => {
    if (err) throw err;
  
    console.log(result);
  });
  
  connection.end();
  

 

Reconnecting database

Sometimes database gets disconnected. There r various ways like reconnecting ir but few things needed to be noted. When reconnecting sometimes we need to create new object too instead of just reconnecting Eg:

const pg = require("pg");
let pgClient

const createPGConnection=()=>{
  pgClient=new pg.Client(conString);
  
  pgClient.connect()
  console.log('database connected')

}

const conString = process.env.CONNECTION_URI;


client.on("ready", async () => {
  console.log(`Logged in as ${client.user.tag}!`);
  

});

createPGConnection()

pgClient.on('error',async(err)=>{
  console.log('err: ',err)
  await pgClient.end()
  console.log('connection ended')
  console.log('trying to reconnect')
  await promiseSetTimeOut(5000) 
  await  createPGConnection()
})

 

Pooling https://node-postgres.com/features/pooling

import pg from "pg";

const { Pool } = pg;

const poolConfig = {
  user: process.env.PGUSER,
  password: process.env.PGPASSWORD,
  database: process.env.PGDATABASE,
  host: process.env.PGHOST,
  port: process.env.PGPORT,
  max: 20,
  idleTimeoutMillis: 30000,
  connectionTimeoutMillis: 5000,
};

export const pool = new Pool(poolConfig);

pool.on("error", (error) => {
  console.error("unexpected error on idle client:", error.message);
  process.exit(1);
});

export const query = (text, params) => pool.query(text, params);

some testing

const test = async () => {
    try {
      await query("SELECT 1");
      console.log("database connected bro.");
      return true;
    } catch (err) {
  }
};

 

transactions

import { Pool } from 'pg'
const pool = new Pool()
 
const client = await pool.connect()
 
try {
  await client.query('BEGIN')
  const queryText = 'INSERT INTO users(name) VALUES($1) RETURNING id'
  const res = await client.query(queryText, ['brianc'])
 
  const insertPhotoText = 'INSERT INTO photos(user_id, photo_url) VALUES ($1, $2)'
  const insertPhotoValues = [res.rows[0].id, 's3.bucket.foo']
  await client.query(insertPhotoText, insertPhotoValues)
  await client.query('COMMIT')
} catch (e) {
  await client.query('ROLLBACK')
  throw e
} finally {
  client.release()
}