Postgresql Installation Guide.md

🏁 Getting Started with SQL

✔️ Installing PostgreSQL

First, update the system software packages using the following command:

sudo apt update

and install the latest version of PostgreSQL from the default repositories.

sudo apt install postgresql

After intallation, you can confirm that PostgreSQL service is ative, running, enabled or ready to accept connections from clients using the following commands

sudo systemctl is-active postgresql  

sudo systemctl is-enabled postgresql 

sudo systemctl status postgresql 

sudo pg_isready

✔️ Installing pgAdmin

pgAdmin is a management tool for PostgreSQL, but it's not available in the Ubuntu repositories, so we need to install it from the pgAdmin4 APT repository, add the public key and create the repository configuration file.

curl https://www.pgadmin.org/static/packages_pgadmin_org.pub

sudo apt-key add

sudo sh -c 'echo "deb https://ftp.postgresql.org/pub/pgadmin/pgadmin4/apt/$(lsb_release -cs) pgadmin4 main" > /etc/apt/sources.list.d/pgadmin4.list && apt update'

Then install,

sudo apt install pgadmin4

✔️ Installing NodeJs and node-postgres

node-postgres is a collection of node.js modules for interfacing with your PostgreSQL database

sudo apt install nodejs
 
sudo apt install npm  

npm install pg

🔌 Connecting NodeJs with PostgreSQL

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

const pool = new Pool({
    user: 'lessa',
    password: '88eb9394',
    host: '/var/run/postgresql',
    port: 5432,
    database: 'service_management',
});

pool.on('error', (err, client) => {
    console.error('Unexpected error on idle client', err)
    process.exit(-1)
})

const startDatabase = (req, res, query, values) => {
    pool.connect()
        .then(client => {
            return client.query(query, values)
                .then(result => {
                    client.release();
                    res.status(200).send(result.rows)
                })
                .catch(err => {
                    client.release();
                    res.sendStatus(500);
    
                })
        })
}

🔻 Exemple of data manipulation using pg

const getCustomer = (req, res) => {
    const customer = [req.params.name];
    let query = 'SELECT * FROM customer WHERE deleted_date IS NULL'

    if (customer) {
        query += " AND name LIKE '%'||$1||'%'"
    }

    startDatabase(req, res, query, customer)
}

---