DEV Community

miku86
miku86

Posted on

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

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
Enter fullscreen mode Exit fullscreen mode

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}/`);
});
Enter fullscreen mode Exit fullscreen mode

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');
Enter fullscreen mode Exit fullscreen mode

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;
Enter fullscreen mode Exit fullscreen mode

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;
Enter fullscreen mode Exit fullscreen mode

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}/`);
});

Enter fullscreen mode Exit fullscreen mode

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

Top comments (3)

Collapse
 
oddnavee profile image
OddNavee

I love this

Collapse
 
monfernape profile image
Usman Khalil

You saved a drowning man here with your series. Kudos

Collapse
 
miku86 profile image
miku86

@usman ,

I'm very happy about that!
What challenge did you face?