Neon, server-less Postgres with Nodejs

Are you a PostgreSQL user and have to pay to host a PostgreSQL server besides your backend application? Say hello to Neon, "a fully managed multi-cloud Postgres with a generous free tier with separated storage and compute to offer autoscaling, branching, and bottomless storage".

Neon is built by one of the founders of SingleStore DB. It aims to help developers spin up a Postgres server and connect to it within 3 seconds. It comes with a generous free tier with one project, 10 branches, 3 GB of data per branch and 1 GB of RAM. In this tutorial, I will show you how you can build a basic CRUD application with Nodejs which connects to Neon in the cloud with a fully managed Postgres server so you don't have to care about creating and managing a database locally.

Signup for Neon :

Go to neon.tech and signup using either your email or GitHub. Once signed up you will be prompted to your console. Create a project from New Project button in your console. give your project an appropriate name and once in, copy the connection string from your console. Remember this unique string contains your credentials and password, so don't share it with anyone.

Also, make sure to create a table by running the following query in SQL Editor below your console :

CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    name VARCHAR(255),
    email VARCHAR(255)
);

Create Nodejs project :

Now make sure you have the latest version of Nodejs installed on your machine. Create a directory in which you want to create your project. Open a terminal in your directory, it can either be your command prompt or you can use the integrated terminal in VS Code (or any other IDE). In the terminal, type the following command to initialize the project :

npm init -y

You need the following packages :

• dotenv, to store and read credentials from a .env file

• express, to spin up a backend server

• nodemon, optional, to automatically restart the server once you save changes

• pg, postgres package to connect to a postgres server from your project

Install the above packages by running the following command in your terminal :

npm install dotenv express pg nodemon

Store Credentials :

Now create a .env file in the root of your directory to store your connection string, store credentials like given below :

PORT=4000
DATABASE_URL=

Paste your own connection string after the equal sign, PORT variable stores the port number you want your local host to run the server on, you can change it.

Create server.js :

Create a server.js file in your root directory and paste the following code in there :

const express = require("express");
const bodyParser = require("body-parser");
const app = express();
const db = require("./queries");
require("dotenv").config();

app.use(bodyParser.json());
app.use(
  bodyParser.urlencoded({
    extended: true,
  })
);

app.get("/", (request, response) => {
  response.json({ info: "Node.js, Express, and Postgres API" });
});

app.get("/users", db.getUsers);
app.get("/user", db.getUserById);
app.post("/create-user", db.createUser);
app.put("/update-user", db.updateUser);
app.delete("/delete-user", db.deleteUser);

app.listen(process.env.PORT, () => {
  console.log(`App running on port ${process.env.PORT}.`);
});

Let's break down this code line by line :

1. First, we import the necessary packages like express, dotenv and body parse. then we call the express function on app variable which we will use to spin up our server. We also create a db variable to import queries from 'queries.js' file which we will create shortly.

2. Then we use the 'Body Parser' package on our app variable, this package is used to extract data from the request body, we will need it to extract user data from our requests to create a new user, delete a user or update a user.

3. We use the get method on our app variable and use a single slash to direct to the default route, here we send a JSON object with info variable as response.

4. Below that, we have 5 routes and each route uses functions we created in our 'queries.js' file. We have a route to get all users, get a single user by id, post or create a user, update a user and delete a user. Notice here we use standard HTTP methods like GET, PUT, DELETE and POST which are provided to us by the express package.

5. Finally, we listen to the app on the port we defined in our .env file.

Create queries.js :

Now create a 'queries.js' file in the same directory and paste the following code in there :

const { Pool } = require("pg");
require("dotenv").config();

const pool = new Pool({
  connectionString: process.env.DATABASE_URL,
  ssl: {
    rejectUnauthorized: false,
  },
});

const getUsers = (request, response) => {
  pool.query("SELECT * FROM users ORDER BY id ASC", (error, results) => {
    if (error) {
      throw error;
    }
    response.status(200).json(results.rows);
  });
};

const getUserById = (request, response) => {
  const id = request.query.id;
  pool.query(`SELECT * FROM users WHERE id = ${id}`, (error, results) => {
    if (error) {
      throw error;
    }
    response.status(200).json(results.rows);
  });
};

const createUser = (request, response) => {
  const { name, email } = request.body;
  // Descritpion for this syntax below
  pool.query(
    `INSERT INTO users (name, email) VALUES ($1, $2) RETURNING id`,
    [name, email],
    (error, results) => {
      if (error) {
        throw error;
      }
      response.status(201).send(`User added with ID: ${results.rows[0].id}`);
    }
  );
};

const updateUser = (request, response) => {
  const id = request.query.id;
  const { name, email } = request.body;

  pool.query(
    "UPDATE users SET name = $1, email = $2 WHERE id = $3",
    [name, email, id],
    (error, results) => {
      if (error) {
        throw error;
      }
      response.status(200).send(`User modified with ID: ${id}`);
    }
  );
};

const deleteUser = (request, response) => {
  const id = request.query.id;

  pool.query(`DELETE FROM users WHERE id = ${id}`, (error, results) => {
    if (error) {
      throw error;
    }
    response.status(200).send(`User deleted with ID: ${id}`);
  });
};

module.exports = {
  getUsers,
  getUserById,
  createUser,
  updateUser,
  deleteUser,
};

//  In the context of parameterized queries using the pg library in Node.js, the placeholders are represented by $1, $2, and so on, instead of using ${name} syntax
// The reason for this difference is that the $1, $2 syntax is specific to the pg library and the PostgreSQL query protocol. It is used to bind parameters securely and efficiently in the query.
// When using parameterized queries with the pg library, you pass the actual values as an array in the second parameter of the query() function. The library internally maps these values to the corresponding placeholders in the SQL query string based on their position in the array.
// Therefore, in the given code snippet, you should continue using $1, $2, and $3 placeholders to represent the variables name, email, and id, respectively, instead of using the ${name} syntax.

A simple breakdown of this file is that we import the Pool from our pg package to connect to Neon's postgres server, We create a new Pool by providing our connection string which we saved in our .env file. Then we create and export all 5 methods we use in our server file. These methods run queries on our database and return the appropriate response. Some of them also take optional arguments which are used to change or query specific data.

• getUserById function takes an id parameter in the query to return a single user by that id.

• createUser function takes the name and email of the user you want to create.

• updateUser takes the new name and email but also the id of the existing user you want to edit.

• deleteUser takes the id of the user you want to delete.

That's it, we have created a basic CRUD app using express/nodejs and a hosted postgres. Run the command npm run dev in your terminal to run start the server, you should see the console message saying that app is listening to port.

Test API :

Now it's time to test our API, you can use any API testing software for this but I will use Postman as it is free to use and widely used. Open Postman, to test our first route, set the method to GET and put the path http://localhost:4000/users in the URL field. Send the request and wait for the response, you shouldn't get any user unless you have created one.

Let's create user now, set the method to POST, path to http://localhost:4000/create-user and put JSON data in the body of your request like this :

{
    "name": "abuzar",
    "email": "muhammadabuzar010@gmail.com"
}

Send the request and you will see the response that the user has been created. Similarly, you can delete or update the user, but make sure to use the id parameter in the URL like http://localhost:4000/delete-user?id=6

That's it, you just created your first CRUD application which uses a hosted Postgres server. The entire code can be found at this link. Don't hesitate to email me at muhammadabuzar010@gmail.com if you face any difficulties.

Thank you for reading, you can connect with me on GitHub and Linkedin.

The writer is a software engineer.