miku86

NodeJS & PostgreSQL: How To Connect A Database To An Express Server (with ORM)

2019-09-13

Intro

We learned:

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