Making your API with Node, Express and Postgres

In this blog tutorial we will cover on some of the fundamentals of REST API creation creating a food application endpoints using Node with Express framework and we will use Postgres as our database.

Image for post
Image for post

One of the key things to become a better developer is to manage and efficiently make APIs so that everyone in the team can make easy use of the API calls. In this tutorial we will learn about some fundamentals of making a workable endpoints.

Main Goal

The goal of this article is to make a fully functional endpoint for our Food App. By the time you finish the article you will be comfortable enough to make API server running on Express framework in Node.js.

The API we build will be able to handel the HTTP request method that corresponds to PostgresSQL database that the API gets its data from. We will implement most commonly used methods like GET, POST, PUT and DELETE. Now without any delay let’s get started into making our endpoints.

Setting Up

We will firstly create a new project where we will add all the dependencies we will use beforehand as well. So, open up terminal and add following commands:

mkdir Node_API 
cd Node_API
yarn init
yarn add express node-postgres pg nodemon dot-env cors Helmet

Well in the above commands we created a directory named NODE_API and we got inside the path and initialized out package.json file where we added all the required dependencies we shall use in making the endpoints. Lets discuss on what is the purpose of each packages

express It is our web server framework of choice. This helps us in making easy to create endpoints and enhancing the development experience.

node-postgres It is by design pretty light on abstractions. It’s non-blocking PostgresSQL client for Node for interfacing with our PostgreSQL database. It has support for callbacks, promises, async/await, connection pooling, prepared statements, cursors, streaming results, C/C++ bindings, rich type parsing, and more.

pg-pool It is the API provided by node-postgres, which assists us make queries to the database.

nodemon Helps us in hot reloading our server in respond to some changes

dot-env Allows us to load environment variables from .env file

cors Additional HTTP headers to tell browsers to give a web application running at one origin, access to selected resources from a different origin.

Helmet Its not a must package but I rather love to wear a helmet ⛑ which helps us in securing our Express apps by setting various HTTP headers.

Spinning server

After installing these packages let move on spinning a Node server locally. For that we will create app.js file inside our root folder which looks like below:

const express = require('express');
const morgan = require('morgan');
const helmet = require('helmet');
const cors = require('cors')
const app = express();
app.use(helmet());
app.use(express.json());
app.use(cors())
if (process.env.NODE_ENV === 'development') {
app.use(morgan('tiny'));
}
app.get('/', function (req, res) {
res.send('Express application working ...');
});
// Start server
app.listen(8080, () => console.log('server running ...'))
module.exports = app;

In the code above we have imported our packages installed through yarn and used it. We created a .get() HTTP method from express to send a response of application working and listened to port 8080. Lets move ahead and add nodemon into our package.json file to listen to file changes.

"scripts": {
"start": "nodemon app.js",
},

Creating and Inserting data

We will get into our command line and create a database with a new role by creating a new table and inserting some data into it. For that we are using CLI. So first lets start Postgresql with command

brew services start postgresql

As I am on mac this works for me but you can also write commands like psql or psql postgres whatever works for you. Lets login into our postgres and create a role where we will have a username and password and give them access to create database access.

psql
CREATE ROLE api_user WITH LOGIN PASSWORD ‘*/password*/';
ALTER ROLE api_user CREATEDB;

We have created a role with username api_user and a password for them and given them create database access. Lets now log out of the root user and login to newly created user.

\q 
psql -d postgres -U api_user;

We will now move ahead to create a database and table and we will insert some data for our table as well with command

CREATE DATABASE foodie; 
\c foodie
CREATE TABLE food (
ID BIGINT PRIMARY KEY,
Dish VARCHAR(30) NOT NULL,
Country VARCHAR(30) NOT NULL
);
INSERT INTO food (dish, country)
VALUES (‘Migas', ‘Mexican’),(’Fries', ’French’);

Creating Environment Variables

We will add our environment variables inside this file. We will set up our database name, user, password, host and all port we need into this file.

DB_USER=user
DATABASE_PASSWORD=root
DB_HOST=localhost
NODE_ENV=development
PORT=8080
DB_PORT=5432
DB_DATABASE=foodie

* .env files does not get version controlled

Connecting to Database

For connecting to database with our Express application you can also refer to the official docs here we will follow the same. For that we will create a new file server.js for dedicated file for running server and move some of our code here as well. Lets take the code inside.

require('dotenv').config();const app = require('../Node_API/app');
const port = process.env.PORT;
app.listen(port, () => {
console.log(`App running on port ${port}.`);
});

Now, let’s move on connecting to our local database named foodie. Taking reference from official doc of Express taking the pattern

postgresql://user@host/database

We will import dotenv and Pool into our file and initialized it. And create our connection-string

require('dotenv').config()const { Pool } = require('pg')
const isProduction = process.env.NODE_ENV === 'production'
const connectionString = `postgresql://${process.env.DB_USER}:${process.env.DATABASE_PASSWORD}@${process.env.DB_HOST}:${process.env.DB_PORT}/${process.env.DB_DATABASE}`const pool = new Pool({
connectionString: isProduction ? process.env.DATABASE_URL : connectionString,
ssl: isProduction
})
module.exports = pool ;

Lets move to our main file app.js where we will write some basic GET method just to make sure everything is working fine in the code.

const express = require('express');
const morgan = require('morgan');
const helmet = require('helmet');
const cors = require('cors')
const app = express();
app.use(helmet());
app.use(cors());
app.use(express.json());
if (process.env.NODE_ENV === 'development') {
app.use(morgan('dev'));
}
const getFood = (req, res) => {
res.send('You sent a GET request!')
};
app.route('/food').get(getFood);module.exports = app;

With the code above we look fine to create our endpoints now we will create some HTTP request like GET, POST, PUT, DELETE in this same file rather dedicating separate files for controllers and models for current convinence.

GET request

We will now create a route for getting all food from our database we inserted from the shell. Below is the code for it

// Get all foodconst getFood = (req, res) => {  pool.query('SELECT * FROM food', (error, results) => {
if (error) {
throw error;
}
res.status(200).json({
status: 'sucess',
requestTime: req.requestTime,
data: results.rows,
});
});
};

Here for our GET we kept our raw SQL that helps to return all data inside table food. And we sent it as a response to the client. Our route in the bottom still ok with the changes.

app.route('/food').get(getFood);

GET a single Food by id

For our /food/:id request we will get the food id first as a parameter by the URL using WHERE clause and inside the query we’re looking for $id=1 its numbered placeholder that PostgreSQL uses natively instead of ? placeholder.

// Get food by idconst getFoodById = (req, res) => {
const reqId = parseInt(req.params.id);
pool.query('SELECT * FROM food WHERE id = $1', [reqId], (error, results) => {
if (error) {
throw error;
}
res.status(200).json({
status: 'sucess',
requestTime: req.requestTime,
data: results.rows,
});
});
};
// we also add this in the bottom //
app.route('/food/:id').get(getFoodById)

POST new food

Here we will be adding new food along with the dish and country, where we will extract it from the request body. And we will finally insert the values.

// Create foodconst newFood = (req, res) => {
const { dish, country } = req.body;
pool.query(
'INSERT INTO food (dish, country) VALUES ($1, $2)',
[dish, country],
(error, results) => {
if (error) {
throw error;
}
res.status(201).send(`New food added with ${results.insertId} ID`); });
};
// we will also add .post() method below //
app.route('/food').get(getFood).post(newFood);

PUT update food

The endpoint will take two request we will first getFoodById and we will also PUT to modify to use the UPDATE clause in the query.

// Update foodconst updateFood = (req, res) => {
const reqId = parseInt(req.params.id);
const { dish, country } = req.body;
pool.query(
'UPDATE food SET dish = $1, country = $2 WHERE id = $3',
[dish, country, reqId],
(error, results) => {
if (error) {
throw error;
}
res.status(200).send(`Food modified with ID: ${reqId}`);
});
};
// we will also add .put() method below //
app.route('/food/:id').put(updateFood)

DELETE food

Now we will get into deleting our data from the database. We use DELETE clause on /food/:id to delete a specific food with the same id.

// Delete food
const deleteFood = (req, res) => {
const reqId = parseInt(req.params.id);
pool.query('DELETE FROM food WHERE id = $1', [reqId], (error, results) => {
if (error) {
throw error;
}
res.status(200).send(`Food deleted with ID: ${reqId}`);
});
};
app.route.delete(deleteFood);

Conclusion

We have just scratched the surface of creating API with ExpressJS. I think you had some ideas on how we can create some functional endpoints with these stack. Please find my 🎥 youtube video 🎥below and link to my github repository here.

Happy coding!

Recommended resources

Written by

Product Designer and Frontend Developer | https://twitter.com/ishan02016

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store