An extremely important aspect of being a modern web developer is knowing how to work with APIs to facilitate communication between different software systems.
In this tutorial, you’ll learn how to create your own RESTful API in a Node.js environment running on an Express server and utilizing a PostgreSQL database.
In order to get the most out of this tutorial, there are a few prerequisites:
- You should have basic knowledge of working with the command line
- You should have Node.js and npm installed
By the time you complete this article, you should have a fully functional API server running on an Express framework in Node.js. The API should be able to handle the HTTP request methods that correspond to the PostgreSQL database that the API gets its data from. You will learn how to install PostgreSQL and work with it through the command line interface.
What is a RESTful API?
REST stands for Representational State Transfer and defines a set of standards for web services. An API is an interface that different software programs use to communicate with each other. Therefore, a RESTful API is an API that conforms to the REST architectural style and constraints. REST systems are stateless, scalable, cacheable, and have a uniform interface.
RESTful APIs most commonly utilize HTTP requests. Four of the most common HTTP methods are
DELETE, which are the methods by which a developer can create a CRUD system – create, read, update, delete.
PostgreSQL, commonly referred to as Postgres, is a free and open source relational database management system. You might be familiar with a few other similar database systems, such as MySQL, Microsoft SQL Server, or MariaDB, which compete with PostgreSQL.
PostgreSQL is a robust but stable relational database that has been around since 1997 and is available on all major operating systems — Linux, Windows, and macOS. Since PostgreSQL is known for stability, extensibility, and standards compliance, it’s a popular choice for developers and companies to use for their database needs.
We’ll begin this tutorial by installing PostgreSQL, creating a new user, creating a database, and initializing a table with schema and some data.
If you’re using Windows, download a Windows installer of PostgreSQL.
If you’re using a Mac, this tutorial assumes you have Homebrew installed on your computer as a package manager for installing new programs. If you don’t, simply click on the link and follow the instructions to install Homebrew.
Open up the Terminal and install
You may see instructions on the web that will say
brew install postgresinstead of
postgresql. Both of these options will install PostgreSQL on your computer.
After the installation is complete, we’ll want to get the
postgresql up and running, which we can do with
If at any point you want to stop the
postgresql service, you can run
services stop postgresql.
PostgreSQL is installed now, so the next step is to connect to the
postgrescommand line, where we can run SQL commands.
PostgreSQL command prompt
psql is the PostgreSQL interactive terminal. Running
psql will connect you to a PostgreSQL host. Running
psql --help will give you more information about the available options for connecting with
--host=HOSTNAME| database server host or socket directory (default: “local socket”)
--port=PORT| database server port (default: “5432”)
--username=USERNAME| database username (default: “your_username”)
--no-password| never prompt for password
--password| force password prompt (should happen automatically)
We’ll just connect to the default
postgres database with the default login information – no option flags.
You’ll see that we’ve entered into a new connection. We’re now inside
psql in the
postgres database. The prompt ends with a
# to denote that we’re logged in as the superuser, or root.
psql start with a backslash (
\). To test our first command, we can ensure what database, user, and port we’ve connected to by using the
Here is a reference table of a few common commands which we’ll be using in this tutorial.
\c| Connect to a new database
\dt| List all tables
\du| List all roles
\list| List databases
Let’s create a new database and user so we’re not using the default accounts, which have superuser privileges.
Create a user
First, we’ll create a role called
me and give it a password of
password. A role can function as a user or a group, so in this case, we’ll be using it as a user.
me to be able to create a database.
You can run
\du to list all roles/users.
Now we want to create a database from the
me user. Exit from the default session with
\q for quit.
We’re back in our computer’s default Terminal connection. Now we’ll connect
postgres=#, our prompt shows
postgres=> now, meaning we’re no longer logged in as a superuser.
Create a database
We can create a database with the SQL command.
\list command to see the available databases.
Let’s connect to the new
api database with
me using the
\c (connect) command.
Our prompt now displays that we’re connected to
Create a table
The last thing we’ll do in the
psql command prompt is create a table called
userswith three fields – two
VARCHAR types and an auto-incrementing
PRIMARY KEY id.
Make sure not to use the backtick (`) character when creating and working with tables in PostgreSQL. While
backticks are allowed in MySQL, they’re not valid in PostgreSQL. Also, ensure you do not have a trailing comma in the
CREATE TABLE command.
We’ll add two entries to
users to have some data to work with.
Let’s make sure that got added correctly by getting all entries in
Now we have a user, database, table, and some data. We can begin building our Node.js RESTful API to connect to this data stored in a PostgreSQL database.
Setting up an Express server
At this point, we’re finished with all of our PostgreSQL tasks, and we can begin setting up our Node.js app and Express server.
Create a directory for the project to live.
You can either run
npm init -y to create a
package.json, or copy the code below into a
Now we have our dependencies loaded into
index.js file, which we’ll use as the entry point for our server. At the top, we’ll require the
express module, built in
bodyParser middlware, and set our
We’ll tell a route to look for a
GET request on the root (
/) URL, and return some JSON.
Now set the app to listen on the port you set.
From the command line, we can start the server by hitting
http://localhost:3000 in the URL bar of your browser, and you’ll see the JSON we set earlier.
The Express server is running now, but it’s only sending some static JSON data that we created. The next step is to connect to PostgreSQL from Node.js to be able to make dynamic queries.
Connecting to the database from Node.js
We’ll be using the node-postgres module to create a pool of connections. This way we don’t have to open a client and close it every time we make a query.
A popular option for production pooling would be to use pgBouncer, a lightweight connection pooler for PostgreSQL.
Create a file called
queries.js and set up the configuration of your PostgreSQL connection.
In a production environment, you would want to put your configuration details in a separate file with restrictive
permissions that is not accessible from version control, but for the simplicity of this tutorial , we’re keeping it in the same file as the queries.
The aim of this tutorial is to allow
DELETE operations on the API which will run the corresponding database commands. To do this we’ll set up a route for each endpoint, and a function to correspond to each query.
We’re going to create six functions for six routes, seen in the chart below. First, we’ll go through and create all of the functions for each route, then we’ll export the functions so they’re accessible:
index.js, we made an
app.get() for the root endpoint with a function in it. Now in
queries.js, we’ll create endpoints that will display all users, display a single user, create a new user, update an existing user, and delete a user.
GET all users
Our first endpoint will be a
GET request. Inside the
pool.query() we can put the raw SQL that will touch the
api database. We’ll
SELECT all users and order by id.
GET a single user by id
/users/:id request, we’ll be getting the custom
id parameter by the URL and using a
WHERE clause to display the result.
In the SQL query, we’re looking for
id=$1. In this instance,
$1 is a numbered placeholder, which PostgreSQL uses natively instead of the
? placeholder you may be familiar with from other flavors of SQL.
POST a new user
The API will take a
POST request to the
/users endpoint. In the
POST request, we’ll be adding a new user. In this function, we’re extracting the
INSERTing the values.
PUT updated data in an existing user
/users/:id endpoint will also take two HTTP requests — the
GET we created for
getUserById, and also a
PUT, to modify an existing user. For this query, we’ll combine what we learned in
POST to use the
It is worth noting that
PUT is idempotent, meaning the exact same call can be made over and over and will produce the same result. This is different than
POST , in which the exact same call repeated will continuously make new users with the same data.
DELETE a user
Finally, we’ll use the
DELETE clause on
/users/:id to delete a specific user by id. This call is very similar to our
In order to access these functions from
index.js, we’ll need to export them. We can do this with
module.exports, creating an object of functions. Since we’re using ES6 syntax, we can write
getUsers instead of
getUsers:getUsers, and so on.
Here is our complete
Completing the setup
Now that we have all of our queries, the last thing we need to do is pull them into the
index.js file and make endpoint routes for all the query functions we created.
To get all the exported functions from
require the file and assign it to a variable.
Now for each endpoint, we’ll set the HTTP request method, the endpoint URL path, and the relevant function.
Here is our complete
index.js, the entry point of the API server.
Now with just these two files, we have a server, database, and API all set up. You can start up the server by hitting
Now if you go to
http://localhost:3000/users/1, you’ll see the JSON response of the two
GET requests. But how can we test our
This can be done with curl, a command line tool that’s already available on your terminal. Below are examples you can run on the command line to test all of the protocols.
Make sure that the server is actively running in one Terminal window while you run these commands in a separate window.
Add a new user with the
name Elaine and
Update the user with id
1 to have the
name Kramer and
Delete the user with id
Congratulations, you should now have a functioning API server running on Node.js and hooked up to an active PostgreSQL database. In this tutorial, we learned how to install and set up PostgreSQL in the command line, how to create users, databases, and tables, and how to run SQL commands. We also learned how to create an Express server that can handle multiple HTTP methods, and how to use the
pg module to connect to PostgreSQL from Node.
With this knowledge, you should be able to build on this API and utilize it for your own personal or professional development projects.
Plug: LogRocket, a DVR for web apps
LogRocket is a frontend logging tool that lets you replay problems as if they happened in your own browser. Instead of guessing why errors happen, or asking users for screenshots and log dumps, LogRocket lets you replay the session to quickly understand what went wrong. It works perfectly with any app, regardless of framework, and has plugins to log additional context from Redux, Vuex, and @ngrx/store.