Photo by Lucian Alexe on Unsplash

Connect a Relational DB and Backend with Node.js, Sequelize, PostgreSQL

Robert K.
8 min readNov 2, 2020

--

A Flatiron School instructor once told me that when learning a new language, library, or framework it helps to build a project idea that you’ve built before. This takes out the “I have to come up with a great idea before I can start coding” so you can focus on learning the syntax and architecture of the new technology.

This technique is how I’ve been learning node.js over the past couple of weeks.

Project Basics:

A DIY sharing site where you can upload instructions with pictures on how to make something and share it with other users. There will be a liking and tagging system with push notifications(eventually).

What we will focus on today:

=> Getting a PostgreSQL server connected to our node.js backend.
=> Adding Sequelize as an ORM to establish relationships between two models
=> Creating a few instances of our models, and storing them in the DB
=> Recalling our DB instances through calls to the backend using Insomnia so we can see our success

Creating the Sequelize application:

We are going to take full advantage of Sequelize CLI to help us get through the Sequelize set up as quickly as possible. I will leave some links to get more information including a full list of commands.

First let’s start a node application and install some packages. In your project directory run the following:

npm init -y
npm install sequelize pg express

This will generate a package.json file then add sequelize, express and postgreSQL packages to our application.

Now we initialize a Sequelize project, and open the directory in our favorite editor (VS Code here)

npx sequelize-cli init

To learn about all the Sequelize CLI commands take a look here

Next we need to configure our Sequelize project to work with a Postgres db. Find your config.json file in the /config directory and change the code to look like this.

{
"development": {
"database": "<YOUR_PROJECT_NAME>_development",
"dialect": "postgres"
},
"test": {
"database": "<YOU_PROJECT_NAME>_test",
"dialect": "postgres"
},
"production": {
"use_env_variable": "DATABASE_URL",
"dialect": "postgres",
"dialectOptions": {
"ssl": {
"rejectUnauthorized": false
}
}
}
}

The env variable will allow us to easily deploy to Heroku later. (more on that in another blog)

Now we can use the Sequelize CLI to create our Postgres DB.

npx sequelize-cli db:create

Defining our models and addind seed data for testing the api

My application has two models to start, Users and Projects. Each Project will have one User and a User can have many Projects. Let’s create the User first since they don’t need another model to create an instance.

npx sequelize-cli mode:generate --name User --attributes firstName:string,lastName:string

**if you get an error make sure there are no spaces in your attributes flag. This will cause a failure**

Running model:generate automatically creates our model file and our migration file with the attributes we specified. You can manually edit these fields if you forget something in the /models/user.js file and the /migrations/<long number string>-create-user.js files. You can also remove the files mentioned above and run the model:generate command again with the fixes.

Migrate your changes and make the User table in the database by running

npx sequelize-cli db:migrate

Now let’s make a seed file:

npx sequelize-cli seed:generate --name users

You will see a new directory called /seeders . In that file, add some dummy users in the following format:

module.exports = {
up: (queryInterface, Sequelize) => {
return queryInterface.bulkInsert('Users', [{
firstName: 'John',
lastName: 'Doe',
createdAt: new Date(),
updatedAt: new Date()
},
{ another user like above if you want}
], {});
},
down: (queryInterface, Sequelize) => {
return queryInterface.bulkDelete('Users', null, {});
};
}

We can make our other model Projects in the same way as before.

npx sequelize-cli model:generate --name Project --attributes title:string,description:text,userId:integer

Now we can hook up the association between these two models

Here, is more information about Creating sequelize associations with the Sequelize CLI

Firse let’s add the project association to our user.js model. The association is added to the static associate(models) method.

In the project.js Model we will add the other end of the same association so a Project can belong to a User

The last step is to add the foreign key to the projects migration file. You should have to files in migrations. We need to work on the one with create-projects in the file name.

Migrate your newly changed projects file to get the projects table up and running on the db.

Let’s seed our db with some projects for our user.

npx sequelize-cli seed:generate --name projects

You’ll find the new seed file in the /seeders directory. Add some projects!

'use strict';module.exports = {up: async (queryInterface, Sequelize) => {await queryInterface.bulkInsert("Projects", [{title: "test project 1",description: "This project was built using Vanilla JavaScript, HTML, and CSS",userId: 1,createdAt: new Date(),updatedAt: new Date()},{title: "test project 2",description: "This project was built using React & a 3rd-party API.",userId: 3,createdAt: new Date(),updatedAt: new Date()},])/*** Add seed commands here.** Example:* await queryInterface.bulkInsert('People', [{*   name: 'John Doe',*   isBetaMember: false* }], {});*/},down: async (queryInterface, Sequelize) => {return queryInterface.bulkDelete('Projects', null, {})/*** Add commands to revert seed here.** Example:* await queryInterface.bulkDelete('People', null, {});*/}};

Now that our relationships are set and our models have seeds we can run our seed files to populate our db.

npx sequelize-cli db:seed:all

We can check that our data hit the database quickly with a psql query.

Access thje psql command line with psql <YOUR_PROJECT_NAME>_development

then run the following SQL query

SELECT * FROM "Users" JOIN "Projects" ON "Users".id = "Projects"."userId";

You should get some data coming back from the db. SWEEET.

Set up Express

Now that our Sequelize portion is up and running lets create and Express API to serve up our data when we make calls from the front end.

We are going to add two more packages (since we added express earlier)

npm i nodemon -D
npm i body-parser

Nodemon is going to listen for changes in our express application and restart the server when changes are made so we never have to worry if we did it manually.

Body-parser is going to handle the information from user requests.

Let’s set ourselves up for success by adding a routesdirectory and controllers directorty where our logic is going to live.

In our root folder add a server.js file as well this is where our express application is going to live.

For the sake of brevity, I’ll run us through the Users routes and controller, but it would be very similar to do the same for projects.

First make a small change to the package.json file. We will configure nodemon to be the default script to run when we call npm start

....
"scripts": {
"test": "echo \"Error: no test specified\" && exit 1",
"start": "nodemon server.js"
},
....

Let’s make the skeleton for our Express application. In our server.js file we need the following:

const express = require('express');
const routes = require('./routes');
const bodyParser = require('body-parser');
const usersRouter = require('./routes/users.routes')
const PORT = process.env.PORT || 5000;const app = express();
app.use(bodyParser.json())
app.use('/api/users', usersRouter);
app.listen(PORT, () => console.log(`Listening on port: ${PORT}`))

This is our basic Express server which will listen on port 5000. Instead of defining all of the routes in this file, we are going to define them in the /routes directory in the users.routes.js file. Let’s make that file now and add our first route.

Create a file in the /routes directory called users.routes.js and add the following then test from http://localhost:5000 to make sure our node app works.

const { Router } = require('express');
const controllers = require('../controllers');
const router = Router();
router.get('/', (req, res) => res.send('This is root!'))module.exports = router

Test the route by starting up your express server with npm start

Great. We’re making strides. Take a minute and get a water, coffee etc. you’ve earned it.

Back? Let’s deliver some data from our database. To do that we are going to need a users controller. Create a users.controller.js file in your /controllers directory.

We will make a getAllUsers function that will get all users from the db and their associated Projects.

In this file we’ve incorporated the User and Project model from our /model directory and made an async function to get the data and return it from the db. If successful we return the response with a 200 status code and the data as JSON. We export the function so we can have access to it in our users.routes.js file. Let’s add it there and test it out.

In our users.routes.js file lets set up a route to handle this function and import our users.controller.js file to have access to it. Add the following to our routes.

const usersController = require('../controllers/users.controller')
//replace our cuurent route with the following
router.get('/', usersController.getAllUsers)

Remember we set our server.js file to use this router when we make calls to /api/users so when testing we need to run a get request to http://localhost:5000/api/users/

If well went well we should be able to test this in our browser when the server is running. What we get back should look something like this (i have a few more users and projects than I made in this tutorial).

our data from the db

You can see that each user has a Projects key with an array of all of the projects with their userId.

What Next?

From here you cand add all of the routes and controller actions necessary to perform all the CRUD actions on your API for your Projects and Users

Final Thoughts:

I’ve been focusing on JavaScript mostly since finishing bootcamp and I can now say that I can get a backend API up and running in a project much faster with Express and Sequelize than I can with Ruby on Rails. I’ll be continuing on this project and adding images to the db as well and will blog about getting that set up. (hopefully easier that Rails Active Storage)

Happy Hacking!

Resources:

Connect with me

--

--

Robert K.

Full- Stack Software Engineer with a former life in high-end restaurant management. Currently working in React JS and Ruby on Rails.