This article describes how you can use Node.js and PostgreSQL to create an API and provides an example for how to create a table.
1. Why Use Node.js and PostgreSQL?
2. Getting started
3. Creating an API
It is critical for software systems to have a seamless communication system to mutually communicate all the important details. Unlike in the past, streamlined communication is now easy to have thanks to Node.js. The framework allows the creation of simple APIs that can be up and running in a matter of minutes.
Today’s tutorial digs deeper into the use of PostgreSQL to create a communicative API that works along with Node.js. We start from the basics and then delve into every step that you need to take to set up your API.
We will explore all the required steps to set up the environment and go deeper into the actual creation of the API. The step-by-step instructions will help you gain a clear understanding of the process.
Why Use Node.js and PostgreSQL?
Even though there a range of different options available when it comes to creating an API, we are using Node.js and PostgreSQL for the sheer convenience they provide in the creation of APIs.
Contrary to other frameworks, we can get our API up and running in a matter of minutes thanks to the convenience afforded to us by our choice.
Getting Started
Getting started with development is simple. First, we need to check that we have installed certain required software to ensure that the right environment is present for the development to take place.
Prerequisites
The following software components are required before we can begin developing the API:
Generally, it is really simple to get started with the installation of the required elements.
Creating an API
To get started, first we need to initialize our node project. We can do that by simply running the following command in the terminal:
npm init -y
This creates a package.json in the directory in which this command is run. A package.json is simply a file containing all the dependencies for your project:
{
"name": "my-project",
"version": "1.0.0",
"description": "",
"main": "index.js",
"scripts": {
"test": "echo \"Error: no test specified\" && exit 1"
},
"keywords": [],
"author": "",
"license": "ISC"
}
Next we install Express.js as our dependency by running the following command in the same directory where we initialized the package.json:
npm i express
Here the “i” flag means “install,” and it’s telling npm to install the Express.js as a project dependency.
Now you will see a folder in your project node_modules. This folder will have all your project dependencies and the binaries, depending on the packages you install.
Your folder structure will look something like this:
Project Folder
|__ node_modules
|__ package.json
Now we will create a new file index.js in the root of the project and write the following code (this creates a simple Express.js app that has only one endpoint, which is a get request):
const express = require('express')
const app = express()
const port = 3000
app.use(express.json());
app.get('/', (req, res) => res.json({ message: 'Hello World' }))
app.listen(port, () => console.log(`Example app listening on port ${port}!`))
We first require express as the app dependency.
Then we invoke the application by saying const app = express().
app.use(express.json()) is telling our application to accept the incoming JSON body in requests and that we are dealing with JSON, as REST APIs communicate in JSON form.
Then we define a simple GET route by writing app.get('url_endpoint', (req, res) ⇒ res.json({ message: 'Hello World' })). This takes in a string of the URL endpoint and then a callback, which takes an anonymous function that has request and response objects as parameters. In the function body, we can process the incoming request and send a response. In our case, it's simply sending a “Hello World” string.
In the last line, we tell the application to listen on a given port for requests, basically creating a simple server.
To run our server we have to add a start script in our package.json file. Add the following line in the scripts:
"start": "node index.js"
Your package.json should now look like this:
{
"name": "my-project",
"version": "1.0.0",
"description": "",
"main": "index.js",
"scripts": {
"test": "echo \"Error: no test specified\" && exit 1",
"start": " node index.js"
},
"keywords": [],
"author": "",
"license": "ISC"
}
This will ensure that you have successfully installed Express.js and have it working.
This is really a basic example that does not do much, so let's take it a step further and use a database for storing a user. We will be using PostgreSQL for the database and Sequelize as the ORM for our simple application. We can install Sequelize by running the following command:
npm i sequelize pg pg-hstore
This will install Sequelize for us. The other two dependencies are based on the database you are using; for us, it is PostgreSQL, so we will download and install the PostgreSQL dialect for connecting to the database.
Once we have downloaded the dependencies, we can require Sequelize in our application by adding the following line and importing it:
const Sequelize = require('sequelize')
Then we can invoke the Sequelize object and pass in the database connection string to its constructor by doing the following:
const sequelize = new Sequelize('postgres://user:pass@example.com:5432/dbname')
To test if the connection with the database is successful we can write the following code:
sequelize
.authenticate()
.then(() => {
console.log('Connection has been established successfully.');
})
.catch(err => {
console.error('Unable to connect to the database:', err);
});
Now we will model our User table:
const User = sequelize.define('user', {
// attributes
firstName: {
type: Sequelize.STRING,
allowNull: false
},
lastName: {
type: Sequelize.STRING
// allowNull defaults to true
}
}, {
// options
});
Here we defined a simple User model that has two attributes: firstname and lastname. This can be done by declaring User = sequelize.define. It takes in the first argument (‘user’) as the table name that is to be created in the database, and then it takes the model itself as an Object and another optional Object with other options (for more information refer to Sequelize Docs).
Now that our model is defined in the application we have to sync it in the database itself, which roughly means when making the table for our user-defined attributes in the model, it will create an exact reflection in the table like our model. To sync your model we write:
// Note: using `force: true` will drop the table if it already exists
User.sync({ force: true }) // Now the `users` table in the database corresponds to the model definition
This will sync our User model in the database.
We can have many models in our application, but for this example, we will use one. Now let's store a user in our database that we send with a request. We will use Postman for making HTTP requests to our server, but you can use other HTTP clients as well (another common client is Insomnia).
But first let's add a POST endpoint that contains the user in the request body:
app.post('/user', async (req, res) => {
try {
const newUser = new User(req.body)
await newUser.save()
res.json({ user: newUser }) // Returns the new user that is created in the database
} catch(error) {
console.error(error)
}
})
We declared our post route, so let's restart the server to check the changes and make a POST request to this endpoint. We are using an async function so we can use async/await keywords.
This stores the user in the database, so let’s define an endpoint to retrieve the user from the database.
app.get('/user/:userId', async (req, res) => {
const userId = req.params.userId
try {
const user = await User.findAll({
where: {
id: userId
}
}
)
res.json({ user })
} catch(error) {
console.error(error)
}
})
This route takes in the user id (userId) as the parameter. Parameters are defined in express using “:” and the name of the parameter.
So now we can make a request like this: http://localhost:3000/user/1 and you will get the user in return.
This is the complete code that we’ve created so far:
const express = require('express')
const Sequelize = require('sequelize')
const app = express()
const port = 3000
app.use(express.json());
const sequelize = new Sequelize('postgres://user:pass@example.com:5432/dbname')
sequelize
.authenticate()
.then(() => {
console.log('Connection has been established successfully.');
})
.catch(err => {
console.error('Unable to connect to the database:', err);
});
const User = sequelize.define('user', {
// attributes
firstName: {
type: Sequelize.STRING,
allowNull: false
},
lastName: {
type: Sequelize.STRING
// allowNull defaults to true
}
}, {
// options
});
// Note: using `force: true` will drop the table if it already exists
User.sync({ force: true }) // Now the `users` table in the database corresponds to the model definition
app.get('/', (req, res) => res.json({ message: 'Hello World' }))
app.post('/user', async (req, res) => {
try {
const newUser = new User(req.body)
await newUser.save()
res.json({ user: newUser }) // Returns the new user that is created in the database
} catch(error) {
console.error(error)
}
})
app.get('/user/:userId', async (req, res) => {
const userId = req.params.userId
try {
const user = await User.findAll({
where: {
id: userId
}
}
)
res.json({ user })
} catch(error) {
console.error(error)
}
})
app.listen(port, () => console.log(`Example app listening on port ${port}!`))
Well done! You should now be able to have a functional API that runs effectively on Node.js and uses a connected PostgreSQL database. This tutorial covered all the steps required to install the required components of PostgreSQL using npm and to further utilize communication using commands. The server allows the utilization of numerous methods to effectively establish the internal connection well.
Using the steps laid out in the tutorial, you can replicate and build your own API and use it as well.
Popular Links
Featured Links
Relevant Blogs
EDB Tutorial: How to run a TPC-E-like benchmark easily
The TPC Benchmark(TM) E (TPC-E) was released back in 2007 as the successor to the TPC Benchmark(TM) C (TPC-C) for an OLTP type workload. To briefly recap, the TPC-E models...EDB Tutorial: How to Use Privilege Analysis to Identify [Un]Used Privileges
Have you ever searched for unnecessary granted privileges in your database? To find these, you need to know 1. The granted privileges 2. How many of the granted privileges have...EDB Tutorial: How To Get the Most Out of EDB Query Advisor
Enterprise-class databases require DBAs who are responsible for maximizing query performance. Therefore, when it comes to large scale deployment of databases, minimizing the work of DBA is important. One of...More Blogs
EDB Tutorial: How to Configure Databases for EDB JDBC SSL Factory Classes
The fact is, SSL configuration required by EDB JDBC SSL factory classes is considered confusing and challenging by many EDB customers. This stems from the concepts behind the SSL handshake...