Simple API Endpoint Tutorial using Node Js and PostgreSQL

Github Source code : https://github.com/hilalarsa/jcendpoint

As explained in some of my past stories, there are lots of cases in a development or learning on website roadmap, when we encounter the needs to create and practice using our own customizable API endpoint.

Other case might be using it for creating a simple-to-medium scale app which need a certain scale of functionality to show or manipulate a collection of data.

At one point, you might also wanted to expand it to multiple apps, platform like Android, and so on.

If that is the case, so creating your own API endpoint is the answer, and the easiest — and yet — most convenient way is to use Node JS, and PostgreSQL.

1. Setup Node JS

Node JS is an environment that requires installation, you can check the how-to on the Official download page.

2. Setup PostgreSQL

Same as Node JS, PostgreSQL is also require a fresh install if you don’t have it yet on local machine. You can install it by running :

sudo apt-get update
sudo apt-get install postgres-client

3. Setup Express boilerplate using express generator

After installing node, you should have an instance of npm up and running in the global variables, simply check your npm availibility by typing

npm -v

in the command line. If its showing your current npm version, you’re good to go. Now try and run a package from npm called the express-generator by typing

npm i express-generator

Start creating instance of your server by typing

express

The prompt will gave out some options to config your boilerplate, anything chosen is fine.

4. Setup PostgreSQL database

Setup a PostgreSQL database (further will be called Psql) by opening up your terminal, and go to psql cli window using

psql postgres

For fresh install, further configs should be required to add current account with root access. After making sure you got in, you can try to wander around the configs using command :

\l : list all database
\d : list all table
\c <db_name> : connect to database

5. Create Database

Create and connect to your database simply by running query in the root access :

CREATE DATABASE <db_name>;
\c <db_name>

Create your first table using query like this :

CREATE TABLE users (
  id int, 
  name varchar(50),
  email varchar(50),
  profile_image varchar
)

Insert 2 values to your table to make a simple dummy data :

INSERT INTO users (id, name, email, profile_image)
VALUES (
  "1",
  "Jerry",
  "jerry@example.com",
  ""
),
(
  "2",
  "George
  "george@example.com",
  ""
)

Install the postgresql library for node js using

npm install pg

Create pool function in your express file root directory, under db/pool.js, that declare database connection, make sure to insert your correct database credentials.

db/pool.js

6. Create Express Route

app.js

7. Create Request

/routes/users.js

8. Fire it up!

Run up your server using :

npm start

Access your generated server by typing http://localhost:3000 on your browser, try to access your route on /users to show your data from database.

Github Source code : https://github.com/hilalarsa/jcendpoint