DEV Community 👩‍💻👨‍💻

Luana Lessa
Luana Lessa

Posted on

🏁 Getting Started with SQL

✔️ Installing PostgreSQL

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

sudo apt update
Enter fullscreen mode Exit fullscreen mode

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

sudo apt install postgresql
Enter fullscreen mode Exit fullscreen mode

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
Enter fullscreen mode Exit fullscreen mode

✔️ 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'
Enter fullscreen mode Exit fullscreen mode

Then install,

sudo apt install pgadmin4
Enter fullscreen mode Exit fullscreen mode

✔️ 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
Enter fullscreen mode Exit fullscreen mode

🔌 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);

                })
        })
}

Enter fullscreen mode Exit fullscreen mode

🔻 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)
}
Enter fullscreen mode Exit fullscreen mode

Top comments (0)

🌚 Friends don't let friends browse without dark mode.

Sorry, it's true.