NodeJS & PostgreSQL: How To Connect Our Database To Our Simple Express Server (with an ORM)

Intro

We learned:

• how to do stuff with PostgreSQL
• how to create a server with express
• how to connect express and PostgreSQL with pg

Now we want to learn how to connect our PostgreSQL database with our express server with sequelize as ORM.

Create A Project Folder, do npm init, install all needed packages

mkdir node-postgres-sequelize
cd node-postgres-sequelize
npm init
npm i express sequelize pg pg-hstore

We can see that sequelize needs pg.

Create index.js and setup a small server

const express = require('express');
const app = express();
const PORT = 8080;

app.get('/', (req, res) => {
  res.send({ message: 'endpoint working' });
});

app.listen(PORT, () => {
  console.log(`Server running at: http://localhost:${PORT}/`);
});

Test your server with node index.js,
it should run at localhost:8080

Create a local database, table & entry

sudo -iu postgres
createdb -O postgres node-postgres-sequelize;
psql -d node-postgres-sequelize
\conninfo
CREATE TABLE users(id SERIAL PRIMARY KEY, nickname TEXT NOT NULL);
INSERT INTO users(nickname) VALUES ('miku86');

Test your database with SELECT * FROM users;,
there should be 1 row.

Create database.js with only the database stuff

const Sequelize = require('sequelize');
// your credentials
DATABASE_URL = 'postgres://[db-user]:[password]@127.0.0.1:5432/node-postgres-sequelize';

const database = new Sequelize(DATABASE_URL);

module.exports = database;

Note: This implementation is very simple and not for production. You should never move your credentials into this file, you should use something like dotenv. But for the sake of simplicity, I make this example as simple as possible.

Create User.js with one method to read all database rows

const Sequelize = require('sequelize');
const database = require('./database');

const User = database.define(
  'users',
  {
    nickname: {
      type: Sequelize.TEXT
    }
  },
  { timestamps: false }
);

User.readAll = async (req, res) => {
  try {
    const users = await User.findAll();
    return res.send({ users });
  } catch (error) {
    return res.send(error);
  }
};

module.exports = User;

We separate the User model and its readAll() function to make things more obvious. We don't have to write the SQL query (e.g. SELECT * FROM users), we can use the sequelize's abstraction of it, findAll(). We don't care how sequelize is doing this.

Update index.js with a new route

We now have to add the users endpoint to index.js.

const express = require('express');
// new: import User
const User = require('./User');  
const app = express();
const PORT = 8080;

app.get('/', (req, res) => {
  res.send({ message: 'endpoint working' });
});

// new: route to users, that runs readAll()
app.get('/users', User.readAll);

app.listen(PORT, () => {
  console.log(`Server running at: http://localhost:${PORT}/`);
});

The exact some file like in the tutorial without sequelize.

Test your server with node index.js,
it should run at localhost:8080/users and show all (= 1) rows from the PostgreSQL database.

Summary

In then end, we did these steps:

• created an express server
• created a database with one table and content
• created a method to read the content
• added a route that runs this method

To setup additional database queries, we only have to:

• add a new method in User.js with a new sequelize method (create(), destroy() etc.)
• add a new route in index.js that runs the new method

Next Part

We created a connection between server and database without ORM (pg) and with ORM(sequelize).

In the next part, we will take a closer look at the pros and cons of both methods.

Further Reading

Express Docs
PostgreSQL Docs
SQL Syntax
sequelize at npm
sequelize docs