A Guide for RESTful APIs with Node, Sequelize & Postgres

_urlIf you’ve landed here, hopefully you’re looking for help getting data flowing from a Node.js and Express web application, to a PostgreSQL database with Sequelize as the O.R.M. This guide will assist you with setting up a Node.js project, and installing Sequelize to allow for object relational database mapping between a Postgres database.

The simplest way to think of Object Relational Mapping is as a process for accessing a relational database — Postgres in our instance — from an object-oriented language like JavaScript. Sequelize allows us to interact with a Postgres database using JavaScript instead of SQL.

“Sequelize is a promise-based Node.js ORM for Postgres, MySQL, MariaDB, SQLite and Microsoft SQL Server.” — sequelize.org

Before we get moving, let me lay out the tools we’ll be using. This way you have a high-level sense of what tools you should be knowledgeable of, and what you can expect to extract from this guide.

Project Dependencies

  • express 👂🏼
  • dotenv 🔐
  • nodemon 🔁
  • sequelize-cli 🗺

Prerequisites

To have the most success using this guide you should have a general command of JavaScript. You also need to have Node.js and PostgreSQL installed on your machine.

💡 Postgres and PostgreSQL are the same thing. This confused me at first, but it’s just industry semantics — although PostgreSQL is the official name.

While my goal is to help you configure Sequelize with Postgres, in the process we will be building a RESTful API. Once we have data flowing properly, you can expand on this app’s build however you’d like — adding more routes or developing the front-end.

Why Postgres?

Those of us who have only dabbled with the back-end, may have done a few tutorials using a non-relational database like MongoDB. And I can attest I’ve learned a ton by using MongoDB with Node.js — seeing data flowing and testing endpoints with Insomnia.

I quickly realized when landing my first engineering gig that it’s imperative to command knowledge of relational DBs (databases). Postgres itself is an “object-relational database system that uses and extends the SQL language,” according to the official website. Its language is designed to perform CRUD (Create, Read, Update, Delete) operations on a database, it scales vertically and can perform complex queries across multiple tables of data.

Sequelize what?

Without Sequelize, we’d have to execute SQL statements in order for us to interact with Postgres and perform CRUD on our databases. Sequelize handles this translation for us, allowing us to interact with a Postgres database using JavaScript instead of SQL.

Setting up a new Sequelize project

Now that we have some context established, let’s get to the code!

For this guide, I’ll be using yarn 🧶. The official Sequelize documentation uses npx. We'll create yarn scripts along the way for future developers to join the project. For a full working example, click here (Link to spike with yarn scripts).

Create new directory

Let’s start by creating a new directory for our project, and setting it as our working directory.

Open your terminal, and enter to create a new directory (empty folder) on your desktop for this project. Change directories into the folder that you just created.

Configure project

Next, we initialize Node.js and create a new file in the root directory of our app by running the command .

Press to accept the default set up, although we do want to name our main file instead of .

Install the dependencies for this project by running the commands:

From the terminal, now open your project in VS Code.

With VS Code open, select your file. Add in a object, and then add the script below to start our server with nodemon.

💡 We’ve also added to the script so that dotenv is required and pre-loaded. Meaning, we won’t need to require and load dotenv in our application’s code — more on that here.

package.json file with initial dependencies and nodemon script

Now we’re set up to run our application by simply using the command. Nodemon will enable "hot-reloading", allowing our server to continue running while monitoring for changes in our files — auto-restarting the server after saved changes.

Create a file in your root directory — a hidden file to pass environment variables and protect secure information like port numbers, passwords, database URIs.

Add in known environment variables to the file:

  • // default username for Postgres
  • // default password for Postgres

💡 Sequelize can use a full DB_URL string, other config options can be found here.

.env config

🧰 Optional step if pushing to GitHub 🧰

Run // Creates an empty Git repository

Next, create a file and add & as assets that GitHub will ignore when updating your repo. By doing this step, prevents you from accidentally uploading your hidden file to your repo .

.gitignore file for protecting your assets

🧰 — — — — — — — — — — — — — —— — — — — — — — — — — — — — — 🧰

Express Server

Create an file in the root directory, and

  • Require Express
  • Create our server
  • Grab the secure from
  • Set up for our root URL ('/')
  • Set up
  • Start our server that’ll be listening on for connections

💡 This set up below mimics what Express refers to as the “simplest app you can create.” Read more here.

Express server configuration

Save the file. Now let’s fire up our server and test our connection!

Run in the terminal.

💡 If you’re using VS Code, you can type control + ` on your keyboard to open the terminal and run the command from there.

Node server running / listening from within the VS Code terminal

Mission control, we have liftoff 🚀 — everything is working as expected! Our app is listening 👂🏼 for connections at http://localhost:3000. If you follow that link and open a new webpage, you should see the Hello World!’ message.

Install Remaining Dependencies

Let’s install our remaining project dependencies and transition to Sequelize and Postgres.

💡 To kill your server’s connection and input commands again, open the terminal the server is running on and press control + c on your keyboard.

Install Postgres — our database of choice — and Sequelize

// Installs Postgres // Installs Sequelize

Install Sequelize CLI for command-line interface migrations and project bootstrapping.

🥳 Celebrate, you’re halfway there! Here’s how your dependencies should look at this step:

Sequelize Project bootstrapping:

With our app configured, and the necessary dependencies installed, we can create an empty Sequelize project. To do so, execute the command:

This will automatically create the following folders within your root directory:

  • config — contains config file, which tells CLI how to connect with database
  • migrations — contains all migration files
  • models — contains all models for your project
  • seeders — contains all seed files

Now that we have a new Sequelize project created, let’s get it configured correctly.

Sequelize Configuration:

We need to tell Sequelize CLI how to connect to the database. To make that happen, open the default config file . Rename the file to Add the requirement to the top of the file, and destructure your :

Replace the , , and values for each testing environment — development, test, and productionusing the variable names you destructured from . Also, be sure to update the from to .

🤹🏻‍♂ Optional step — It’s good practice to rename the databases, replacing the default names with unique enough titles that will avoid future confusion between your databases. I’ll be using:

  • Development:
  • Test:
  • Production:

Lastly, set module.exports to equal the object in config.js

💡 For a production configuration you may need to update with SSL and other config options found here.

Create a new database in Postgres

We’ve done all the hard work to properly configure our database, so let’s give Sequelize the O.K. to create it.

💡 Sequelize will use the default connection port for Postgres — port 5432. To specify a different port, add the field to . Read more here. First, update package.json and add in a script: Now we can run to create our new database. OR Success! We’ve created a development database with the name .

Creating the first Model & Migration

To actually get data into our database, let’s create a model (table) for our database named , and provide a few attributes (columns) — , , .

In your terminal, run the following command:

Confirmation that a new model and migration were created

The command will automatically create:

1. A model file in the models folder.

💡 Models = Sequelize (App specific). Treat models as the representation of the table you want to create or update.

2. A migration file with name like in the migrations folder.

💡 Migrations = Postgres (Database specific). Treat migrations like a log of changes in the database.

Running (data) Migrations

Up until this point, we actually haven’t inserted any data into the database 😳. We’ve only created the required model and migration files for our model (table). To actually create the table in your database you need to run .

First, let’s update and add in a & db:g:migration--> script:

,

Now we can run and send over our model to Postgres. (Or )

If everything went smooth, we can finally view our empty Sequelize model ( table) within the Postgres database. I’m using Postico below to view the table.

Viewing empty Users table using Postico

Creating Seeds

Let’s insert some mock data into our table by creating a few sample users.

💡 Seed files are used to populate database tables with sample data, so let’s create a seed file that will allow us to add a few sample users to the table.

Again, update and add in the & scripts:

Next, run the command, adding in the desired name of the seed file:

Or

This command will create a seed file in the seeders folder. It's file name will look something like .

Now we have to edit the seed file so that it has a few sample users to insert into the table. Reference the image below to see how to format the up and down functions.

💡 The and functions are included in every migration file, allowing us to define our desired database changes. The function defines the new change, and the function defines how to undo the change — if needed.

Running Seeds

In the previous step you’ve only created a seed file, we still need to transfer the users to the database. To do that run the command (Or )

Boooom 💣💥 ….. We’ve got some legendary users in our database!!

Viewing seed users with Postico

Updating your original model

After all this work, you might realize you need to update your original database structure — let’s say you need to add a new column for on the table.

To accomplish this, you’ll need to create a new migration file using the CLI by running:

Or

Now that you have a new migration file — located inside the migrations folder — you’ll need to update it to define how you want the database to change.

The same and functions we used to seed are included in this migration file. Remember, the function defines the new change, and the function defines how to undo the change.

Once you’re finished updating your migration file, you should migrate the database again.

Run

You’ll also need to update your file in the models folder, adding in the new column name and it’s corresponding data type to the object (see below).

Adding the password column to user.js

Cheers 🍻

If you rode the wave 🌊🏄🏻‍♂️ to this point, then you’ve certainly expanded your developer toolkit! Take a moment and let all this settle in, we’ve covered a lot of information.

You’ve created a functional RESTful API using Node.js and Express, which can transmit data to a Postgres database by utilizing Sequelize to translate JavaScript into equatable SQL commands.

From here, maybe you create more tables to really see how relational DB’s work, or build out pages that have forms gathering or verifying data. Imagine a form for creating a new account and another for logging in, that way you’ll be able to test the two-way process of storing and accessing data.

I wrote this guide to be verbose, because I struggled to locate documentation that put all the pieces together in a way I understood. My hope is that this guide benefits both newcomers and Sr. engineers. We all learn and grow uniquely from the wisdom our teachers & mentors provide. What’s important is that we continue ‘forever learning’.


More about:

Mickey Martinez

Mickey is a Software Engineer at Echobind, with a knack for problem solving with innovative solutions. A former bootcamp grad, he’s developed an avid interest in teaching, mentoring, and advocating for those just starting in software development. As a React.js to mobile convert, he primarily utilizes React Native as his paintbrush to go full Bob Ross on an empty canvas. Beyond coding, Mickey enjoys studying ancient history, world travel, tacos de asada, and bumpin’ some G-funk classics down the PCH.