Setup PostgreSQL with Sequelize in Express Tutorial

This month, all articles are sponsored by TRUMPF Laser GmbH. The german company I worked with is using a sophisticated React with GraphQL and .NET tech stack. They are hiring eager developers for their sites in Berlin and Schramberg. If you are interested to bring highly configurable laser device user interfaces to their clients all over the world, write them a striking application with a cover letter.

The article is a checklist for myself, but for anyone else setting up PostgreSQL on MacOS for having a SQL database in a Express.js application. You can leave out the Express part to just set up Postgres on MacOS. If you attempt to run Postgres on Windows, I believe you can substitute most of the tools here for your machine.

You may wonder: Why is there a need for another setup guide for Postgres in the first place? I found many tutorials and guides on how to set it up, but most of it was kinda blurry, outdated (old Postgres versions) and not to the point. So I wanted to have this little checklist for setting up Postgres 10 on MacOS for myself. If it is useful for you, it is an additional plus. The checklist is not comprehensive at all, maybe needs to be improved by using the correct terms (looking forward to your comments), but I will fill out the gaps along the way of using Postgres in Express myself.

Postgres Setup on MacOS

My personal recommendation is using Homebrew for installing and managing your applications on MacOS. Over here you can find some help on how to setup Homebrew. Afterward, you can update all your Homebrew dependencies and install PostgreSQL on the command line:

brew update
brew install postgresql

Then you can check your PostgreSQL version on the command line:

postgres --version
postgres (PostgreSQL) 10.3

As you can see, it is all about PostgreSQL 10 in the following checklist. But I am looking forward to update this checklist once Postgres receives another update.

Setting up PostgreSQL as physical Database

Now you can initialize the physical space on your hard-disk to allocate databases. Therefore, create the default postgres database on the command line in case it didn’t happen automatically for you:

initdb /usr/local/var/postgres

When you connect to this physical database later on, you will see an actual database which is called “postgres” as well. The postgres database is meant to be the default database for any third-party tools that you are using in combination with PostgreSQL. These tools attempt to make the default connection to this default database, so you shouldn’t delete it.

You can manually start and stop your Postgres database server with the following commands on the command line:

pg_ctl -D /usr/local/var/postgres start
pg_ctl -D /usr/local/var/postgres stop

In case you want to have the database server running once your machine booted, there are commands which you can look up to make it happen. As for me, I want to keep control over when to start and stop the database server.

Creating your PostgreSQL Database

Now, you will set up your actual database which can be used in your actual applications. On the command line, you have got the shell scripts to create and drop a database:

createdb mydatabasename
dropdb mydatabasename

However, you can also connect to your databases in order to execute SQL statements. Either the psql command works for you or you have to specify a database such as the default postgres database in order to connect to it.

psql postgres

Afterward you should be in the psql shell, which you can exit again by typing CTRL + d. Being in the psql shell, you can create and drop databases too:

CREATE DATABASE mydatabasename;
DROP DATABASE mydatabasename;

In order to list all your databases, you can type \list. Your new database should show up there, but also your default postgres database and two more default template0 and template1 databases. You shouldn’t delete them. Apart from \list, you can find more Meta-Commands for psql in their documentation for Postgres 10.

  • \list - List all of your actual databases.
  • \c mydatabasename - Connect to another database.
  • \d - List the relations of your currently connected database.
  • \d mytablename - Shows information for a specific table.

A minimal Postgres with Sequelize in Express Setup

In order to connect PostgreSQL to your Express application, you can use a ORM to convert your information from the database to your JavaScript application without performing SQL statements yourself. Sequelize is such an ORM which supports multiple dialects whereas PostgreSQL is one of those dialects. In general, Sequelize gives you a comfortable API to work with your actual PostgreSQL database from setup to execution.

Before you can implement the database usage in your Node.js application, you have to install sequelize and pg (postgres client for Node.js) on the command line by using npm:

npm install pg sequelize --save

The following use case implements a database for a Twitter clone application where you have the two models Author and Tweet. Therefore, you usually have a folder in your Node.js application called src/models/. Its content are files for each model in your database (e.g. src/models/author.js and src/models/tweet.js). Moreover, there is one file (e.g. src/models/index.js) which combines all models and exports all the necessary information to the Express server.

Let’s start with the two models in the src/models/[modelname].js files. The Author model could look like the following:

const author = (sequelize, DataTypes) => {
  const Author = sequelize.define('author', {
    username: {
      type: DataTypes.STRING,
      unique: true,
    },
  });

  Author.associate = models => {
    Author.hasMany(models.Tweet);
  };

  return Author;
};

export default author;

And the Tweet model looks quite similar to it:

const tweet = (sequelize, DataTypes) => {
  const Tweet = sequelize.define('tweet', {
    text: DataTypes.STRING,
  });

  Tweet.associate = models => {
    Tweet.belongsTo(models.Author);
  };

  return Tweet;
};

export default tweet;

Sequelize is used to define the model with its content (composed of DataTypes and optional configuration such as the unique flag for the username). Furthermore, the associate property is used to create relations between models. Whereas an Author can have multiple Tweets, a Tweet belongs to only one Author. You can dive deeper into these things by reading up the Sequelize documentation. Next, in your src/models/index.js file, you import and combine those models and resolve their associations by using the Sequelize API:

import Sequelize from 'sequelize';

const sequelize = new Sequelize('twitter', 'postgres', 'postgres', {
  dialect: 'postgres',
});

const models = {
  Author: sequelize.import('./author'),
  Tweet: sequelize.import('./tweet'),
};

Object.keys(models).forEach(key => {
  if ('associate' in models[key]) {
    models[key].associate(models);
  }
});

models.sequelize = sequelize;
models.Sequelize = Sequelize;

export default models;

At the top of the file, you have to create a Sequelize instance by passing mandatory arguments (database name, username of database superuser, password of database superuser, configuration) to the constructor. For instance, you have to tell Sequelize the dialect of your database which is postgres and not mysql or sqlite. You may have to create the superuser with its credentials on the command line while being in the psql shell too. Otherwise, you can also try to keep the credentials null.

CREATE USER postgres WITH SUPERUSER PASSWORD 'postgres';

If you haven’t the named twitter database yet, you can create it on the command line as well. Or you can use any other database which you have created before.

Last but not least, you can use the created Sequelize instance in your Express application:

import express from 'express';
...
// here you have your express related imports
...

import models from './models';

const app = express();

...
// here you do your express server setup
...

models.sequelize.sync().then(() => {
  app.listen(3000, () => {
    console.log('Your Server is up and running');
  });
});

Once you start your application again, you should see on the command line that the tables in your database got created. You can tap into those tables by using the psql shell and the previously mentioned Meta-Commands. If you want to re-initialize your database on every Express server start, you can add a confition to your sync method: sequelize.sync({ force: true }).then(...).


That’s it for the short checklist. If you have any further tips or improvements for it, you can leave a comment below. Especially if you have improvements for the terminology or additional Meta-Commands which you happen to use on a regular basis. Otherwise I hope the walkthrough helped you to set up PostgreSQL with Sequelize (or any other ORM) in your Express application on MacOS.

Build a Hacker News App along the way. No setup configuration. No tooling. No Redux. Plain React in 190+ pages of learning material. Learn React like 33.000+ readers.

Get the Book
comments powered by Disqus

Never miss an article about web development and self-growth.

Take Part

Join 15.000+ Developers

Learn Web Development with JavaScript

Tips and Tricks

Access Tutorials, eBooks and Courses

Personal Development as a Software Engineer