May 3: Building a PostgreSQL API in JavaScript with Express and Sequelize (Part 1)

© 2003 Universal Pictures/Buena Vista Entertainment

During my time at Flatiron School, I studied the basics of web APIs, databases, and object relationship mappers (ORMs) — which all work in harmony to give web applications the incredible functionality that helps us shape our world. We learned all this in Ruby on Rails, a wonderful framework that’s easy to use, relatively secure, and supported by a vibrant community and a wealth of free gems.

Sadly, although many products still rely on Rails, its use seems to be declining. In many circumstances Rails can be rigid, inflexible and slow, though recent versions have added features to address some of these concerns. Nevertheless, the job market seems to be moving toward JavaScript, Python and PHP.

With this in mind, and in a spirit of constant improvement and education, I decided after graduating from Flatiron to branch out and learn other web application frameworks… but of course there aren’t a ton of beginner-friendly resources out there for doing so. So what follows is my attempt at a JavaScript web API cookbook, aimed at those who are transitioning from Rails.

This is a huge topic, and I’ve no doubt this tutorial won’t even come close to doing this subject justice. In fact, there’s so much to cover that I’ll have to split this tutorial into several parts; this first part will just guide you through setting up your project, config and environment.

Background

Please note this tutorial assumes the following:

• You’re familiar with JavaScript syntax and bash commands; and
• You have a working PostgreSQL installation. Download an installer and read installation instructions here.

Sequelize can work with many dialects of SQL, including MySQL, MariaDB, SQLite3 and Microsoft SQL Server. For the sake of brevity I’ll use PostgreSQL in this tutorial because it plays well with the cloud services I use and it’s what I’m most familiar with. Please comment if you’re interested in a tutorial on using Sequelize with another flavor of SQL and I might be inclined to write it.

1: Initialize NPM in a new directory and install packages

Get started in your terminal by making a directory for your API and cding into it. Initialize a new Node project with yarn init --yes, and a wild package.json will appear in your directory. The --yes flag just generates package.json automatically; feel free to omit it, but I find it easier since you can always edit it manually later.

If you’re using Github (strongly recommended!), your remote will include dependencies in every push if they’re not .gitignored in your initial commit. That’s redundant and slows things down, so this is a good time to create a .gitignore file in the root of your project directory with just the following two lines:

node_modules/
.env

After creating a .gitignore file, initialize and push your initial commit in the terminal with git init && git add . && git commit -m "initial commit" && git push.

Finally, install the dependencies you’ll need in the terminal with yarn add bcryptjs cors dotenv express jsonwebtoken pg pg-hstore sequelize --save && yarn add nodemon sequelize-cli --dev. This upsettingly long terminal command will install the dependencies listed below, ordered alphabetically (not by importance!):

Production dependencies

• bcryptjs: for hashing sensitive data, especially passwords
• cors: for allowing cross-origin resource sharing (CORS), to ensure your front-end and back-end are sharing data safely and properly
• dotenv: for coordinating and loading environment variables — not strictly necessary but incredibly helpful
• express: our application framework — read more about Express here
• jsonwebtoken: for implementing JSON web tokens, which standardizes the data we’re sending around
• pg and pg-hstore: for connecting to a PostgreSQL server and serializing the data we store/retrieve from it
• sequelize: an object-relationship mapper, just like ActiveRecord in Ruby on Rails — read more about Sequelize here

Development dependencies

• nodemon: enables “hot-loading,” so we can see changes immediately instead of having to shut down and restart our server
• sequelize-cli: for creating Sequelize projects and defining Sequelize objects/relationships

After running the command above, you should see a node_modules folder appear, and your package.json file should look something like this:

{
   "name": "My Express API",
   "version": "1.0.0",
   "description": "A simple Express API",
   "main": "index.js",
   "scripts": {
      "test": "echo \"Error: no test specified\" && exit 1"
   },
   "keywords": [],
   "author": "Josh Frank",
   "license": "ISC",
   "dependencies": {
      "bcryptjs": "^2.4.3",
      "cors": "^2.8.5",
      "dotenv": "^8.2.0",
      "express": "^4.17.1",
      "jsonwebtoken": "^8.5.1",
      "pg": "^8.6.0",
      "pg-hstore": "^2.3.3",
      "sequelize": "^6.6.2"
   },
      "devDependencies": {
      "nodemon": "^2.0.7",
      "sequelize-cli": "^6.2.0"
   }
}

add, commit and push to commit your progress.

2: Create the Express application

Create a file, index.js, in your root directory, and fill it with some basic code to get your Express app started:

const express = require( "express" );
const cors = require( "cors" );
const app = express();
const port = 3000;
app.use( express.json() );
app.use( express.urlencoded(.{ extended: true } ) );
app.use( cors( { origin: `http://localhost:${ port }` } ) );
app.get( "/", ( request, response ) => response.send( "Test" ) );
app.listen( port, () => console.log( `Listening: port ${ port }` ) );

A quick line-by-line breakdown:

1. We require the "express" and "cors” packages, which listen for requests, verify them, and return responses;
2. We create an Express app object and a port number to listen on;
3. We tell our app to organize and parse request data as .json() data, to ignore request data that isn’t properly .urlencoded(), and allow requests coming from localhost so we can test as we develop;
4. We define a default GET route with app.get(), which takes a path ("/") and a callback as arguments;
5. In our app.get() function, we write a callback with two arguments, request and response, to tell Express what to do about a GET request to "/" — in this case, kick back a string, "Test";
6. Finally, we listen for requests on the port we defined, console.logging a message when we start listening.

Now, when you run node index.js in your terminal, you’ll see a message, Listening on port 3000, and you’ll see your terminal freeze as the app listens. Open up a second terminal with your app running and run curl localhost:3000 to GET your test route, and you’ll see Test come back as a response. Success! You now have a working Express app!

Eventually, of course, our API will grow more complex and do more interesting things than just spitting out Test. So I highly recommend trying out routes with a REST client instead of using a browser or curl. The most popular seems to be Postman; I prefer ARC because it’s open-source.

It’s interesting to note at this point that if you change index.js, your saved changes won’t change your app’s behavior until you shut the app down and restart it. See for yourself by changing your app’s response on line 11:

...
app.get( "/", ( request, response ) => response.send( "A different test" ) );

If you run curl localhost:3000 in a terminal without restarting app.js, you’ll still see a response of Test. It’s tedious to repeatedly shut down and restart our server, so let’s use nodemon to force our app to “hot-load” and reflect saved changes immediately. In your package.json file, delete the auto-generated test script and replace it with this:

...
"scripts": {
   "dev": "nodemon index.js"
},
...

You’ve just defined a Node script — a shortcut for terminal commands you use frequently when building your app. Running nodemon app.js in your terminal will start your Express app with nodemon instead of directly with node, so that it restarts with every save. But thanks to your script, you can do the same thing by running yarn dev, resulting in:

yarn run v1.22.10
$ nodemon app.js
[nodemon] 2.0.7
[nodemon] to restart at any time, enter `rs`
[nodemon] watching path(s): *.*
[nodemon] watching extensions: js,mjs,json
[nodemon] starting `node index.js`
Listening: port 3000

Now, your app will automatically refresh each time changes are saved. We’ll be adding many more Node scripts as we build our Express app.

3. Configure dotenv to provision environment variables

Before we continue, let’s learn about process.env, what it represents, and how to make the most of it with the dotenv package we installed.

Every Node project has a process.env global variable that represents the state of the system environment your application is in. We need it because we need to deploy an app to a server if we actually want to use it, and different servers represent different environments. We might test an app out locally on127.0.0.1, and then deploy it to a server at 51.16.0.9, but then have to switch for some reason to a server at 172.0.7.0. With process.env, we can connect properly in any environment without having to write code for each one.

We can provision (define & use)process.env variables several ways — the simplest is at the application level, with code like below:

const process.env.TEST = "Test";
console.log( process.env.TEST );

You can also use the export terminal command: run export TEST="Test" in your terminal, and using process.env.TEST anywhere in your project will yield the string "Test”.

Both these ways work fine, but provisioning on-the-fly can get out of hand really quickly. Enter dotenv stage right, which allows us to define and load all our environment variables in one convenient place. Not only does this spare us brain cells and therapy bills, it also makes our app faster and much more usable — so using dotenv is strongly recommended even though, as I said above, it isn’t strictly necessary.

Since we already installed dotenv, all we have to do is create a .env file in the root of our project and add code like this to it:

PORT = 3000
TEST = "dotenv test"

.env is just a raw text file, not JavaScript, so don’t use semicolons.

Next, in package.json, let’s tell our Node script all about the environment variables we just defined:

...
"scripts": {
   "dev": "nodemon -r dotenv/config index.js"
},
...

Finally, at the top of index.js, let’s require( "dotenv" ), delete our port variable and use those environment variables instead:

require( "dotenv" ).config();
const express = require( "express" );
const cors = require( "cors" );
const app = express();
app.use( express.json() );
app.use( express.urlencoded( { extended: true } ) );
app.use( cors( { origin: `http://localhost:${ process.env.PORT }` } ) );
app.get( "/", ( request, response ) => response.send( process.env.TEST ) );
app.listen( process.env.PORT, () => console.log( `Listening: port ${ process.env.PORT }` ) );

Save your changes so nodemon will restart your app, and when you curl localhost:3000 in another terminal, you’ll get dotenv test as a response — confirming that dotenv is now successfully configured and provisioning!

If you’re using Github, now is a good time to add, commit and push. You’ll remember that we added .env to our .gitignore file; that’s because we’ll be adding sensitive information about our database and authorization tokens to our .env file as we continue building our app. We definitely don’t want that stuff on a public Github repository.

At this point, the file structure of your app should look something like this:

├── node_modules
│   └── ...a LOT of packages in folders
├── .env
├── .gitignore
├── index.js
├── package.json
└── yarn.lock

4. Use sequelize-cli to set up Sequelize

We’re now at a point where we can start setting up our app to interact with a PostgreSQL database. We’ll do that with help from sequelize-cli, a command-line interface that lets us generate projects, models and migrations with easy commands.

First, configure sequelize by making a new file in your project’s root directory called .sequelizerc and filling it with the code below:

const path = require( "path" );
module.exports = {
   "config": path.resolve( "./app/config", "database.config.js" ),
   "models-path": path.resolve( "./app/models" ),
   "seeders-path": path.resolve( "./app/seeders" ),
   "migrations-path": path.resolve( "./app/migrations" )
};

All this does is export a a JSON object that directs Sequelize to some files it needs. We haven’t made them yet, but these files have names that should be self-explanatory: config will hold database configuration, models will hold our models, seeders holds our project’s seed (sample/test) data and migrations holds migrations.

Now, make a folder in the root directory called app, cd into it in your terminal, hold onto your hat and run sequelize-cli init. Voilá! Your Sequelize project now has its config, models, seeders and migrations files. Your app’s file structure should now look like this:

├── node_modules
│   └── ...package folders
├── app
│   └── config
│   │   └── config.json
│   └── models
│   │   └── index.js
│   └── migrations
│   └── seeders
├── .env
├── .sequelizerc
├── index.js
├── package.json
└── yarn.lock

Before we continue, let’s take a detour to our .env file and add some important variables Sequelize needs to communicate with PostgreSQL:

PORT = 3000
DEV_DATABASE_HOST = "127.0.0.1"
DEV_DATABASE_USERNAME = "postgres"
DEV_DATABASE_PASSWORD = "postgres"

A quick note:

Configuring PostgreSQL can be very frustrating, and it’s a topic that’s unfortunately beyond this tutorial’s scope. If you’re testing your app on your local machine with localhost, HOST will be 127.0.0.1, and your USERNAME and PASSWORD will be the username/password of your preferred local PostgreSQL user; if you’ve never changed them, they’ll both be the default values "postgres”. If you’re using an external host, your hosting company almost certainly provides a set of instructions for configuring a PostgreSQL database on their server, and a “Settings” or “Config” panel listing the host address/username/password. Helpful instructions abound online; comment and I might write some.

The next tasks on our to-do list will concern the files we auto-generated in our app folder. We’ll start with config/config.json because we need it to match our .sequelizerc paths and use our environment variables. Adjust it like so:

1. Rename the file to database.config.js;
2. Add module.exports = { ... } before the config object and refactor it from a JSON object to a JavaScript object by removing the quotes surrounding all the keys;
3. Add the two lines of code below at the top of the file, to require( "dotenv" ) and destructure those important database variables we just created from our .environment; and
4. Change the database: name to the name of your project, and the SQL dialect: to "postgres".

When you’re done, database.config.js should look like this:

require( "dotenv" ).config();
const { DEV_DATABASE_HOST, DEV_DATABASE_USERNAME, DEV_DATABASE_PASSWORD } = process.env;
module.exports = {
   development: {
      username: "root",
      password: null,
      database: "my_express_app_development",
      host: "127.0.0.1",
      dialect: "postgres"
   },
   test: {
      username: "root",
      password: null,
      database: "my_express_app_test",
      host: "127.0.0.1",
      dialect: "postgres"
   },
   production: {
      username: "root",
      password: null,
      database: "my_express_app_production",
      host: "127.0.0.1",
      dialect: "postgres"
   }
}

Finally, use those .environment variables in our development: object. test: and production: won’t come into play until we’re ready to deploy, so feel free to comment them out and ignore them for now.

...
module.exports = {
   development: {
      username: DEV_DATABASE_USERNAME,
      password: DEV_DATABASE_PASSWORD,
      database: "my_express_app_development",
      host: DEV_DATABASE_HOST,
      dialect: "postgres"
   },
   ...
}

Next let’s take a peek at our other auto-generated file, models/index.js. Change line 8 so our config variable points to the right place—database.config.js and not config.json anymore. Look lower down and you’ll see the logic that actually uses our config to start a new Sequelize():

'use strict';
const fs = require('fs');
const path = require('path');
const Sequelize = require('sequelize');
const basename = path.basename(__filename);
const env = process.env.NODE_ENV || 'development';
const config = require( __dirname + "/../config/database.config.js" )[ env ];
const db = {};
let sequelize;
if (config.use_env_variable) {
sequelize = new Sequelize(process.env[config.use_env_variable], config);
} else {
sequelize = new Sequelize(config.database, config.username, config.password, config);
}

Run yarn dev in the terminal. If you see errors, go back through this tutorial and make sure all the changes to boilerplate sequelize code were made correctly with no typos. If all has gone according to plan and the gods are smiling, you should see a familiar Listening: port 3000 message, and no errors. add, commit and push to commit your progress.

On the next episode

Our Express app is created, our environment is provisioned, and our Sequelize configuration is in place. We’ve covered a lot of ground and we’re ready for part 2 of this tutorial, where we’ll create models, associations and migrations.

Later, we’ll briefly cover signing up users, hashing their passwords with bcryptjs, and authenticating their connections with jsonwebtokens. All this will give you a basic but important base of knowledge for building a robust and professional JavaScript backend with Express!