Using sequelize with expressJS

and connecting to PostgreSQL.

Photo by Twitter: @jankolario on Unsplash

This is part 2 of multi-part series on express.

You can find Part 1 here. It talks about basic express app scaffolding and setting up mocha for testing.

In the last part, we scaffolded a basic express API, added an endpoint and also set up automated tests for it.

This part will talk about how to set up a database and connect it with express using an ORM called Sequelize.

We will be using PostgreSQL (The world’s most advanced open source database!) as our database of choice.

Before getting on with our setup, however, we need to update our test and decide what kind of response we desire from GET /posts.

Update test and add factory

We will make use of something called as a factory. A factory is a mock data generator for tests. We will use factory-girl package for our mock data needs.

npm i --save-dev factory-girl

Time to update our tests and set up factories!

mkdir test/factories
touch test/factories/index.js

Add the following code to the factories/index.js —

We require the factory-girl package, set up a Post factory and export it for the test runner to use.

We also create a helper file, which will run the factory and create mock data before our tests run. After all the tests are run, it will clean up the database.

mkdir test/helpers
touch test/helpers/index.js

Add the following code to helpers/index.js -

We run factories.create('Post') four times. this means we expect to receive a response containing an array of 4 objects, each with keys id title and content for our GET /posts request.

We update our test like so —

We check that response.body is an array with 4 objects in it and object has keys id title and content .

Now that our tests are ready, time to start with PostgresQL.

PostgreSQL install and add a user

Please download and set up PostgreSQL as per your OS. Confirm the installed client version with psql --version .

To create a PostgreSQL user, follow these steps:

1. To create a user, type the following command createuser --interactive --pwprompt in the terminal.
2. At Enter name of role to add: prompt, type the user’s name. I will call mine as demo_user
3. At Enter password for new role: prompt, type a password for the user. I will use password as demo_user
4. At Enter it again: prompt, retype the password.
5. At the Shall the new role be a superuser? prompt, type y if you want to grant superuser access. Otherwise, type n. I selected n.
6. At Shall the new role be allowed to create databases? prompt, type y if you want to allow the user to create new databases. Otherwise, type n. Obviously, I selected y.
7. At Shall the new role be allowed to create more new roles? prompt, type y if you want to allow the user to create new users. Otherwise, type n. Let’s select n here.
8. PostgreSQL creates the user with the settings you specified.

Now we have successfully set up demo_user for our database.

Set up sequelize and sequelize-cli

We will install the following packages

npm i sequelize pg pg-hstore

Sequelize is an ORM that helps our express application to connect with databases and makes our life easier in general related to handling of data.

pg and pg-hstore are connectors that allow PostgreSQL and Sequelize to talk to each other.

We will also install sequelize-cli in order to gain some command line sequelize functionality.

npm i sequelize-cli

Initialize sequelize

We now have all the needed packages, so we can now scaffold sequelize in our project.

First, add a configuration file .sequelizerc that tells sequelize how to build the infrastructure around our project.

touch .sequelizerc

Add the following code

As you can see, we add four paths for sequelize to create folders —

• config holds database connection information,
• models hold our models,
• seeders hold data that we can add to our databases,
• and migrations hold files that tell us what changes have we made to our databases and tables over the course.

Use our sequelize-cli to automatically create these folders and insert other boilerplate files.

sequelize init

Connect with database using config file

Let’s visit the config/config.json where we store our database connection information and update it like this

We declare two objects — development and test for our two development environments. Production will be declared similarly. For each, we tell sequelize about username/password, database name, dialect and turn off the ORM logs.

We should ideally hold all the information in our .env file and fetch it here, like we do with our PORT in our app.js .

Create both databases

To create development database, run sequelize db:create

To create test database, run NODE_ENV=test sequelize db:create

Generate a model

Now we have created databases and connected them with express API using sequelize, time to create a model.

Sequelize gives us a few generators to help scaffold oft needed files. We will use model:generate generator to create our first model.

sequelize model:generate --name Post --attributes title:string,content:text

To map these changes to database, we will run the migration generated.

sequelize db:migrate

Running a migration means to make sure the changes we make in our code regarding data structure and tables structure are reflected in the underlying database we have connected with our app.

Connect a model to a controller and send response

Let’s create a posts controller. Controller ‘controls’ interaction with database on behalf of application. It’s an architectural choice, and you are free to restructure your app to you liking.

mkdir src/controllers

touch src/controllers/posts.js

In posts.js we will add sequelize query to fetch data from our database, and send it as a response.

We will update our route to pass on the network request to appropriate controller so it can be responded to, properly.

This sets up the flow nicely and the app should work when you run it npm run start and visit localhost:3000/posts .

We get an empty array as response.

This is entirely expected, because we have not added any trial data in our database.

This trial data is also called as seed data.

Generate a seed

Just like model:generate , sequelize helps us with generating seed data with seed:generate command which generate boilerplate code for us.

sequelize seed:generate --name demo-posts

This creates an empty seed file for us. Let’s populate it!

And run the seed to add these posts in our database

sequelize db:seed:all

In our app.js we make sure that database is properly synced to the models. We do it by using db.sequelize.sync() .

If we run our app npm run start and visit localhost:3000/posts we will see the seed data!

Seed the database in another way

We can take another approach to seed data, and here we will use some fake data generation packages like chancejs and fakerjs .

In this article I will showcase both the libraries,though one is more than sufficient for seeding purposes.

Let’s add index.js in our seeders folder.

touch src/models/seeders/index.js

Install chancejs and fakerjs npm i — save-dev chance faker

Update our seeders/index.js to create multiple posts using both libraries

Here we use both packages to create 3 posts each. I have shown different ways of using these libraries.

We now set it up in app.js —

As you can see, I am checking for const seed, which is passed in when I run some command in terminal. If seed is true, I clean up the database and run the createSeed function.

Time to add the command to package.json scripts -

“seed”: “node ./src/app.js true”,

Now, run npm run seed in your terminal and database will remove earlier seeds and add new ones.

See the response in the browser —

Not only do we see the desired response in the browser, but our test also goes green ( Well, it goes green when you hook up the controllers, tests use factories and do not need seed data to run)!

In the next part, we will look at associations between models and we will add Author to Posts.

Here is Part 3.

Craft Academy is a Tech Education Provider that aims to bring new talent to the market and help to solve the shortage of tech workers. We are founded on the belief that modern development standards, agile methodologies, and business skills are fundamental for IT professionals.

Our primary service is a 12-week coding bootcamp designed to provide individuals with a foundation of skills that allows them to enter the industry as junior developers.

With that foundation, our learners find employment in various industries or start their own businesses that bring new innovations to the market.

Would you like to know more about what we do? Follow us here on Medium, Facebook, Twitter or visit our website.