Set Up an Express API Using Sequelize and PostgreSQL

In this tutorial I will be explaining how to set up a node-express API using sequelize and postgres. The API will have a many to many relationships which will be as follows:

An artist has many songs and many genres through songs

A genre has many songs and many artists through songs

A song belongs to an artist and a genre

Let us begin by creating our directory

mkdir node-express-api

and then

npm init -y
npm install sequelize nodemon express body-parser cors && npm install --save-dev sequelize-cli

Sequelize is a promise based ORM for Postgres, MySQL, MariaDB, SQLite, and Microsoft SQL server.

Sequelize-cli allows us to use the command line to access various commands that will be used throughout this tutorial. (It functions similarly to the cli commands that are included in Ruby on Rails)

Nodemon will monitor node for any changes being made and automatically restart the server if there are changes.

Body-parser is body parsing middleware for node.js. It parses incoming request bodies before your handlers.

Cors can be read more about here: https://developer.mozilla.org/en-US/docs/Web/HTTP/CORS as it is beyond the scope of this blog.

Next, enter this into your terminal.

sequelize init
// if this and the below sequelize commands do not work properly, try typing sequelize-cli instead

This will create a number of folders into which we will be adding the files needed for the creation of the API.

At this point we will modify the config.json file to look like this:

config.json

The “dialect” corresponds to the database you want to use for the API. I have chosen to use PostgreSQL. You will notice that in addition to removing parts of the config.json file, I have also added “dialectOptions” along with “ssl” and “rejectUnauthorized”. Check https://nodejs.org/api/tls.html if you want to learn more about ssl.

Next, run these commands to create a song, genre, and artist using sequelize-cli

sequelize model:generate --name Genre --attributes name:string
sequelize model:generate --name Artist --attributes name:string,bio:string
sequelize model:generate --name Song --attributes title:string,lyrics:string

in order to automatically create their corresponding model and migration files with the attributes listed above.

As I am demonstrating how to create a node-express API featuring a many to many relationship, you will now need to open the song migration file and add the following code.

// migrations/create-song
artistId: {
   type: Sequelize.INTEGER,
   onDelete: ‘CASCADE’,
   references: {
   model: ‘Artists’,
   key: ‘id’,
   as: ‘artistId’, }
},
genreId: {
   type: Sequelize.INTEGER,
   onDelete: ‘CASCADE’,
   references: {
   model: ‘Genres’,
   key: ‘id’,
   as: ‘genreId’,
}

such that it looks like this inside your create-song migration file.

create-song.js

If you create your migrations without these lines of code, the song table in your database will lack columns corresponding to artist and genre id.

Now, run

sequelize db:migrate

in order to set up the tables in our database. At this point, I would recommend checking your database to see if the tables and columns have been set up properly. In order to do this, you can use the below psql commands.

psql db_development
\dt

The above command displays all tables

\d “Artists”
\d “Genres”
\d “Songs”

and the above 3 commands describe the tables.

If everything is in order, you can proceed to the next step.

Now we will create our seed files for the database

sequelize seed:generate –-name artists
sequelize seed:generate –-name genres
sequelize seed:generate –-name songs

You should now have 3 files within a folder named seeders. In each of these files, we will be creating seed data.

/seeders/artists.js

/seeders/genres.js

/seeders/songs.js

Once your seed data has been created, run this command:

sequelize db:seed:all

Your database should now be seeded!

Make sure everything is in order with the below commands.

psql db_development
select * from “Artists”;
select * from “Songs”;
select * from “Genres”;

If the values displayed are to your liking, then move on to the next step.

Now create the following folders and files:

mkdir controllers
mkdir routes
touch server.js

It would also behoove us to modify the package.json file so as to more easily allow us to use nodemon.

Add line 8 into the “scripts” portion of your package.json file.

Now inside of the routes folder, create a new file called index.js

touch routes/index.js

This file will hold our API endpoints

And then add the following files to the controllers.js folder

touch controllers/artists.js
touch controllers/genres.js
touch controllers/songs.js

These files will give us access to CRUD actions once completed. Additionally, you have now created all the files and folders that will be used in this tutorial.

Ensure that your file and folder structure looks like this:

Inside of the server.js file, enter the following code:

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

Next, inside of the routes/index.js file, enter the following code:

const { Router } = require(‘express’);
const controllers = require(‘../controllers’);
const router = Router();
router.get(‘/’, (req, res) => res.send(‘Server running!’))
module.exports = router

to get the server up and running.

Now to check if the server is running type either

npm start

or

npx nodemon server.js

into your terminal and then open http://localhost:3000/ in your browser.

If you see the message “Server running!” in the top left, then you have successfully created an express server.

Begin filling out the files in your controllers folder so that the logic for handling your CRUD actions will be implemented.

controllers/artist.js

controllers/artist.js

controllers/artist.js

Make sure that you do the same for the genre and song controllers as well!

Now that you have finished with that, it is time to move on to the routes!

Copy the below lines of code into your routes/index.js file so that you can access the various crud actions you wrote in your controllers folder.

// routes/index.js
const { Router } = require(‘express’);
const artists = require(‘../controllers/artists’);
const genres = require(‘../controllers/genres’);
const songs = require(‘../controllers/songs’);
const router = Router();

router.get(‘/’, (req, res) => res.send(‘Server running!’))
router.get(‘/artists’, artists.getAllArtists)
router.get(‘/artists/:id’, artists.getArtistById)
router.patch(‘/artists/:id’, artists.updateArtist)
router.delete(‘/artists/:id’, artists.deleteArtist)
router.post(‘/artists’, artists.createArtist)

router.get(‘/genres’, genres.getAllGenres)
router.get(‘/genres/:id’, genres.getGenreById)
router.patch(‘/genres/:id’, genres.updateGenre)
router.delete(‘/genres/:id’, genres.deleteGenre)
router.post(‘/genres’, genres.createGenre)

router.get(‘/songs’, songs.getAllSongs)
router.get(‘/songs/:id’, songs.getSongById)
router.patch(‘/songs/:id’, songs.updateSong)
router.delete(‘/songs/:id’, songs.deleteSong)
router.post(‘/songs’, songs.createSong)

module.exports = router

At this point you should be able to access these routes using a REST client like Postman. Alternatively, you should also be able to access your API via localhost if you do not wish to utilize a REST client.

As always, I hope this will be useful to you.

Github repository for the completed code: https://github.com/sdornel/node-many-to-many-relationship