Building a simple PostgreSQL API in Node.js[Part 1: Reading]

Hello lads and lasses!

Yesterday at night, I was having a conversation with my cat and the bastard walked all over my keyboard! I was stressing overexposing some data from my Postgres database. Mixed in there with the mash that fluffy bastard typed, I could clearly see my answer: fndbnmdamapinasvbhsdvbamdb. Yep! I needed a fucking API.

So what the fuck is an API you ask?

API is an abbreviation for Application Programming Interface. ‘Gee weez Patrick! Thanks for making it even more useless!’. APIs allow two programs to talk to each other nicely like buddies. This can happen while using local resources e.g kernel resources or over a protocol like HTTP. When an API works over HTTP, it is called a web service.

In databases, APIs can be used to access data from the database and perform crud operations. APIs can also be used to perform ETL tasks among many other things.

If you still haven’t gotten it, go grab some mountain dew and come back real quick, I’ll wait for you.

Today, we are going to look at how to create a very basic Postgres API. It’s nothing like what I am in the process of creating as my solution but hopefully, it gets your head in the game. For this first part, we’ll only be able to query data from the database using the API. In the next parts, we’ll work on other CRUD operations.

We’ll be doing this in node.js. Node js can be downloaded here. We’ll also need npm which is the package manager for node. I think the new versions of node come with npm.

To check your installed node and npm versions run:

$node -v
$npm -v

Create a folder and call it a sexy name. If you’re out of sexy names, you can use mine.

In your terminal/Git Bash if you’re using windows, cd into that folder you’ve just created.

Run:

$npm init

This will initialize your project.

You will notice a new file package.json has been created.

On your terminal, you’ll need to install the following packages:

1. express:
2. pg:
3. body parser

To install the above packages locally in your project, run:

$npm install <package name>

After installing all the packages, you’ll notice a new file has been created: package.lock.json

Now it’s time to begin our work.

Of course, you’ll need to have a database which has a table already set up.

In my local Postgres, I have a database called api. Inside, I have created a schema called fluffy in honor of that fluffy bastard who walked all over my keyboard. Inside the fluffy schema, I have a table called sandwiches:

The DDL for the table is below:

CREATE TABLE fluffy.sandwiches
(
  sandwich_id serial NOT NULL,
  sandwich_name character varying(50),
  sandwich_price integer,
  CONSTRAINT sandwiches_pkey PRIMARY KEY (sandwich_id)
)

The data in my sandwich table looks like:

sandwich_id | sandwich_name | sandwich_price
    1         PBJ                 4
    2         Fried egg           2
    3         Ham and Cheese      5
    4         Patrick Classic     4
    5         Omelette Sandwich   3

After your database has been set, Create a new file: queries.js where you’ll define your basic CRUD operations.

First, create a connection pool to your Postgres database:

const Pool = require('pg').Pool
const pool = new Pool({
  user: 'postgres',
  host: 'localhost',
  database: 'api',
  password: 'xxxxxx',
  port: 5432,
})

My operations will include:

1. getSandwich: get all the sandwiches in my table
2. getSandwichById: get any sandwich by sandwich_id

Now, let’s define them:

Now that our queries have been defined, we need to create a new file: app.js

This will be like our API headquarters. We’ll have to route all those operations and define all the dependencies we are using.

To change the port on which the app will run on, edit the line:

const port = 3000

To run, go to the project folder in Terminal and type:

$ node app.js

You will get a message on the terminal:

App is running on port 3000.

If you get an error, go back to your work and check if you skipped any part of this tutorial.

If you succeeded, open your browser and enter:

http://localhost:3000/

You should get a message like the one below:

If you run:

http://localhost:3000/sandwich

You should get a list of all your table data in a JSON:

You can also get a specific sandwich by defining the id on the URL.

http://localhost:3000/sandwich/1

This should give information on the sandwich with id 1

That’s it for now, if you encounter any issues, feel free to comment below and I’ll assist as soon as possible.

All the code in this article is available on this Github repo.

Enjoyed this article? For this and much more exciting stuff, check out MindNinja.