As the one in charge of the back-end of my team’s latest app, I decided to take the opportunity to use some tech I hadn’t worked with before: PostgreSQL and FeathersJS. I’ve enjoyed using them so far, but working with new things always comes with challenges. What’s been most frustrating so far about both Postgres and Feathers actually has nothing to do with learning curves, but that I wasn’t able to find many good tutorials or resources out there about using Feathers and Postgres together along with Sequelize to create a complete Node.js back end.

It took me about a weekend to figure it all out, so now I am writing this tutorial so you don’t have to. This most likely isn’t the only way to implement Postgres and Feathers in your back end, and it might not even be the best way, but it definitely works.

What You Need

Node.js - The JavaScript runtime environment that allows you to write server-side JavaScript code and install packages with the node package manager; if you’re interested in this tutorial you likely already have at least a basic understanding of how this works.

PostgreSQL - Postgres is a relational database management system. If you already have some experience with other relational databases like mySQL, the basics of Postgres will be very easy to learn. However, Postgres has many more advanced features than other relational database systems, such as more available data types, which definitely makes it worth getting some practice with.

Sequelize — An ORM that adapts many different kinds of relational databases for use in Node.js, including PostgreSQL

FeathersJS - Feathers is a layer for server-side JavaScript code that so far I’ve had nothing but positive things to say about. There is a little bit of a learning curve, but one you get the hang of it, you can build out servers and APIs in no time.

Postman — …or whichever other software you prefer for testing API endpoints

Step One: Getting Started with Feathers

One thing about Feathers that makes it so great is how easy it is to set up. This is because Feathers has generators that do a lot of work for you. In order to make use of these generators, you have to set up the Feathers command line interface first, so open up your favorite terminal and type

npm install -g feathers-cli

Once you have the Feathers CLI installed, you can get to work on building your app. While still in your terminal, make a directory for wherever you’d like your app to live, and use the generator inside that folder with the command feathers generate app .

Once this command is executed, you’ll encounter a number of prompts to help set up your directory. You can change them if you need to, but the default settings usually work fine.

After everything is installed, when you open up that directory, you’ll notice a ton of new files and folders. You’ll mostly be working in the src directory. The entry point for your app will be src/index.js .

As you can see, the code in index.js looks like part of a Node server. The feathers generator writes all this for you automatically. We can run this server right away with npm start .

Apps generated with Feathers run on localhost:3030 by default. These settings can be changed in the config/default.json file. After the server starts, when you run localhost:3030 in a browser…

You get a default page like this that also shows you’ve generated a feathers app successfully. If you’d like to edit this page, it’s located in public/index.html .

Step Two: Set up your Postgres Database

After installing Postgres on your computer, you should also have a way to access it from the command line with psql from either your regular terminal or an SQL Shell that came with your install. After logging in with whatever username, password, and host information you specified in your install, you can create a new database with the command CREATE DATABASE database name; . Then you can work on it by connecting to it with \connect database name . The command \dt will show you all the tables in that database.

However, since this database is brand new, it’s totally empty. Let’s create a simple table called message .

Now that we have a database, it’s time to use Sequelize and Feathers to integrate it with our app.

Step Three: Generate a Sequelize Service in Feathers

Some additional dependencies are needed in order to use Sequelize and Feathers together. In the directory of your app, type these commands:

npm install --save feathers-sequelize pg pg-hstore

This installs both Sequelize for Feathers and the packages needed to work with Postgres.

Just like you used a Feathers generator to create the boilerplate of your app, you can also use it to generate services, which, in this case, would be a service for your database. You do this with feathers generate service .

You’ll get another series of prompts to help generate your service. Under What kind of service is it? , make sure to select Sequelize, give your service the same name as the database, keep what they have as default for the file path, choose PostgreSQL for the database you are connecting to, and keep the connection string it’s default value for now.

After generating your service, you’ll get even more new files in your src directory related to Sequelize and the testdb service. Now, when you run npm start again…

You get an error like this. This is because even though we specified the path to the database, we still never included the password. This can be fixed in the src/sequelize.js file.

Sequelize is trying to connect to the database with the connection string you specified in the prompt earlier. You can find this string stored in src/default.json under the postgres key, and it is assigned to the variable connectionString .

The correct way to write a connection string that includes any auth information is this:

postgres://USERNAME:PASSWORD@HOST:PORT/DBNAME

You could just edit the default.json file to include your password, but I like to keep things safe by setting everything in a .env file and then writing the connection string out in sequelize.js directly. Create a .env file in the root folder, and then set your environment variables like this:

PGNAME=your username

PGPASS=your password

PGPORT=the port for the database

Then make sure to npm install dotenv , include require('dotenv').config(); at the very top of the Sequelize file, and of course, include the path to the .env file in a .gitignore.

After that, assign connectionString to the actual connection string with environment variables instead of app.get('postgres'); .

After this, the app should be working properly again.

Step Four: Create a Model

Now that the database is connected, it’s time to create a model from the message table we made. You can create models in Feathers by generating more services. Use feathers generate service again and give the same answers for the prompts as you did to create the first service, except give your new service and its file path the same name as its corresponding table.

More files will get generated for this new model. The main one you should focus on is src/models/message.model.js .

This is where you define the schema. The property text is included by default, but we don’t have any text columns in our table, just name and message .

Now that we have our schema laid out, let’s test out the /message endpoint in Postman.

See how that endpoint was created right away, with no need for writing app.get over and over again for every type of request we want to define? That’s the beauty of Feathers; it automatically generates endpoints and implements all the CRUD operations for you. CRUD operations can be further customized with hooks, but that’s a topic for another tutorial.

The bad news is that, while a result does come back in the body, if you look in the console, there’s another error.

We can see why this error is happening further down the console:

When a model is generated, it looks for certain columns by default, like createdAt and updatedAt , so we need a way to include these columns in our table. There are probably several ways to do this, but the way that I found easiest was to first make createdat and updatedat columns in the database with their defaults set to NOW() , so it will get the current date every time.

Then, define createdAt and updatedAt in your schema, reassigning them to createdat and updatedat with the field property.

After doing this, you should be able to make a GET request on this endpoint without any issues. But how about if we wanted to start populating our database with POST requests?

We’re also able to successfully post data to this endpoint. If you check the database, you can see that it’s stored there too.

Now, our app’s server and database are all set up and connected and we can make HTTP requests to our endpoints that manipulate data, thus concluding this tutorial.