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.

```javascript
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`.

```javascript
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):
  ```js
	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):
	```js
	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:
```js
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
```js
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
```js
const test = async () => {
    try {
      await query("SELECT 1");
      console.log("database connected bro.");
      return true;
    } catch (err) {
  }
};
```
 

transactions
```js


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()
}
```