Getting Started With Node – Course

PostgreSQL with Node

PostgreSQL also referred to as Postgres, is a free and popular relational database system. Postgres competes with relational database likes MySQL, SQL Server or MariaDB.

You need to have Postgres installed in your system to continue with this tutorial. Visit the official download page to install Postgres.

We need to create a user credential in Postgres in order to connect to the database.

Connect to Postgres using the default credentials. Run this command in your terminal.

psql postgres

Create a new user.

CREATE ROLE codeforgeek WITH LOGIN PASSWORD 'somepassword'

Give permission to the new user.

ALTER ROLE codeforgeek CREATEDB;

Now, exit from the current session and login again with your new user credentials.

psql -d postgres -U codeforgeek

Create a new database.

CREATE DATABASE users;

Switch to a new database.

\c users;

Create a new table.

users=>
CREATE TABLE profile (
  ID SERIAL PRIMARY KEY,
  name VARCHAR(30),
  email VARCHAR(30)
);

Insert some records.

INSERT INTO profile (name, email)
  VALUES ('Jerry', '[email protected]'), ('George', '[email protected]');

Let’s connect to it using our Node program.

Install the dependency.

npm install --save pg

Create an app.js file and add the following code in it.

const Pool = require('pg').Pool;
const pool = new Pool({
  user: 'codeforgeek',
  host: 'localhost',
  database: 'users',
  password: 'somepassword',
  port: 5432,
});

// read information from table.
pool.query('SELECT * FROM profile ORDER BY id ASC', (error, results) => {
  if (error) {
    console.log(error);
    return;
  }
  console.log(results);
});

// add a new user profile.
const name = 'Shahid';
const email = '[email protected]';

pool.query('INSERT INTO profile (name, email) VALUES ($1, $2)', [name, email], (error, results) => {
  if (error) {
    console.log(error);
    return;
  }
  console.log(results);
});

We created a new Pool connection to Postgres. We first executed SELECT query using Pool.query method. Pool connection automatically returns the connection to the pool after executing query.

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.