PostgreSQL(neondb) with Node.js : Beginner’s Guide

Cover Image

In the world of web development, databases play a pivotal role in storing and managing data efficiently. Among the myriad of choices available, PostgreSQL stands out as a powerful, open-source relational database system. When combined with the flexibility and versatility of Node.js, a robust and scalable backend solution can be crafted. In this guide, we’ll explore the process of connecting Neondb with a Node.js application, offering a clear path for developers seeking an efficient data storage solution.

Why PostgreSQL?

1. Open Source and Community-Driven:

• PostgreSQL is an open-source database with a rich history and a vibrant community. Its community-driven development ensures continuous improvement, security updates, and feature enhancements.

2. ACID Compliance:

• PostgreSQL adheres to the ACID (Atomicity, Consistency, Isolation, Durability) properties, ensuring reliability and data integrity in even the most complex transactions.

3. Extensible and Customizable:

• The extensibility of PostgreSQL allows users to define their data types, operators, functions, and aggregates, offering a level of customization that’s crucial for diverse application requirements.

4. Scalability and Performance:

• Whether you’re dealing with small-scale projects or enterprise-level applications, PostgreSQL’s architecture is designed for scalability. Its performance capabilities shine, particularly in scenarios with a high volume of concurrent transactions.

What is NeonDB ?

NeonDB, also known as Neon, is a serverless, open-source alternative to traditional database-as-a-service (DaaS) offerings like AWS Postgres. It utilizes a unique architecture that separates compute and storage, granting it several advantages over its competitors.

NeonDB

Why neon ?

• Serverless: NeonDB eliminates the need for you to manage and provision database servers. This allows you to focus on your application development and saves you time and resources.
• Scalable: NeonDB can automatically scale up or down based on your needs, ensuring that you always have the right amount of resources available.
• Fault-tolerant: NeonDB is designed to be highly fault-tolerant, with automatic failover and data replication.
• Branchable: NeonDB supports database branching, which allows you to create multiple versions of your database for development, testing, and production purposes.
• Cost-effective: NeonDB is priced based on usage, so you only pay for the resources you use.

Lets start with the step by step guide :

1. Make account on https://neon.tech/

2. Create a free project, create a database !

3. Create a new Database, name it whatever you want! (i am naming it as ‘test’)

4. copy the connection string for the same

5. Using a library that let’s you connect and put data in it

pg is a Node.js library that you can use in your backend app to store data in the Postgres DB (similar to mongoose). We will be installing this eventually in our app.

6. Connecting db with NodeJS

6.1 : Initialize empty NodeJS app

npm init -y

6.2 : Install pg

pg is a Node.js library that you can use in your backend app to store data in the Postgres DB (similar to mongoose). We will be installing this eventually in our app.

npm install pg

6.3 : Connect Database client : ( paste your connectionString )

import { Client } from "pg";

const client = new Client({
  connectionString:"postgresql://sanketsj00900:************@ep-holy-forest-a51n3thp.us-east-2.aws.neon.tech/test?sslmode=require"
})

client.connect();

7. Creating Table by defining schema

Tables in SQL :

Tables are database objects that contain all the data in a database. In tables, data is logically organized in a row-and-column format similar to a spreadsheet. Each row represents a unique record, and each column represents a field in the record.

Please refer below article to know about different attributes and keys in detail :

SQL CREATE TABLE Statement

Using Nodejs to manipulate PostgreSQL Data:

CRUD Operations :

C — Create Table, Database

R — Read data from database

U — Update data from database

D — Delete data from database

1. Create Table

const { Client } = require('pg')
 
const client = new Client({
  connectionString:"postgresql://sanketsj00900:************@ep-holy-forest-a51n3thp.us-east-2.aws.neon.tech/test?sslmode=require";
})

// Function to create a users table in database.
async function createUsersTable() {
    await client.connect()
    const result = await client.query(`
        CREATE TABLE users (
            id SERIAL PRIMARY KEY,
            username VARCHAR(50) UNIQUE NOT NULL,
            email VARCHAR(255) UNIQUE NOT NULL,
            password VARCHAR(255) NOT NULL,
            created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
        );
    `)
    console.log(result)
}

createUsersTable();

Output :

You can easily see tables from test -> tables, and yes users table is created succesfully!

2. Inserting data into the table

async function insertData() {
  try {
    await client.connect(); // Ensure client connection is established
    const insertQuery =
      "INSERT INTO users (username, email, password) VALUES ('username1', 'user1@example.com', 'user1_password');";
    const res = await client.query(insertQuery);
    console.log("Insertion success:", res); // Output insertion result
  } catch (err) {
    console.error("Error during the insertion:", err);
  }
}

Output :

Entry is inserted succesfully !

But, there is one issue in this approach

This is an insecure way to store data in your tables. When you expose this functionality eventually via HTTP, someone can do an SQL INJECTION to get access to your data/delete your data.

More secured way : don’t put user provided fields (ie. username, password, eamil) in the SQL string

Solution :

const insertQuery = 'INSERT INTO example_table(column1, column2) VALUES($1, $2)';
const res = await client.query(insertQuery, [column1Value, column2Value]);

More secured way of interting data :

async function insertData(username, email, password) {
  try {
    await client.connect();
    // Use parameterized query to prevent SQL injection
    const insertQuery =
      "INSERT INTO users (username, email, password) VALUES ($1, $2, $3)";
    const values = [username, email, password];
    const res = await client.query(insertQuery, values);
    console.log("Insertion success:", res); // Output insertion result
  } catch (err) {
    console.error("Error during the insertion:", err);
  }
}

// createUsersTable();
insertData("user2", "user2@gmail.com", "user2_pass");

3. reading data from database

async function getData() {
  await client.connect();
  const res = await client.query(`SELECT * FROM users`);
  console.log(res);
  return res;
}

Output :

4. Updating data from database

async function updatePass(username, newPass) {
  await client.connect();
  const updateQuery = `UPDATE users SET password = $2 WHERE username = $1`;
  const values = [username, newPass];
  const res = await client.query(updateQuery, values);
  console.log(res);
  return res;
}

updatePass("user2", "updates2_pass");

4. Deleting data from database

async function deleteUser(username) {
  await client.connect();
  const deleteQuery = `DELETE FROM users WHERE username = $1`;
  const res = await client.query(deleteQuery, [username]);
  console.log(res);
  return res;
}

deleteUser("user2");

Output :

Updated Database :

So, this was the beginner’s guide about connecting the database, and manipulating data by doing CRUD operations !

If you like this particular blog, please follow Sanket Jagdale! and stay tuned for more such amazing blogs !