NodeJS & PostgreSQL: How To Connect Our Database To Our Simple Express Server (with an ORM)
Tags: beginners, tutorial, postgres, javascript
Published: 2019-09-13 10:12:58

---
title: NodeJS & PostgreSQL: How To Connect Our Database To Our Simple Express Server (with an ORM)
published: true
description: NodeJS & PostgreSQL: How To Connect Our Database To Our Simple Express Server (with an ORM)
tags: beginners, tutorial, postgres, javascript
series: Intro to PostgreSQL (for NodeJS & Express)
---

## Intro

We learned:
- [how to do stuff with PostgreSQL](https://dev.to/miku86/postgresql-what-is-it-how-to-install-it-16hk)
- [how to create a server with express](https://dev.to/miku86/nodejs-how-to-create-a-simple-server-using-express-1n9d)
- [how to connect express and PostgreSQL with pg](https://dev.to/miku86/nodejs-postgresql-how-to-connect-our-database-to-our-simple-express-server-without-an-orm-10o0)

Now we want to learn how to connect our PostgreSQL database with our express server with `sequelize` as [ORM] (https://stackoverflow.com/q/1279613).

---

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

```bash
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

```js
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](http://localhost:8080)

---

## Create a local database, table & entry

```js
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

```js
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`](https://www.npmjs.com/package/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

```js
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()`](https://sequelize.org/master/manual/querying.html). 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`.

```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`](https://dev.to/miku86/nodejs-postgresql-how-to-connect-our-database-to-our-simple-express-server-without-an-orm-10o0).

Test your server with `node index.js`,
it should run at [localhost:8080/users](http://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](https://sequelize.readthedocs.io/en/latest/api/model/) (`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`)](https://dev.to/miku86/nodejs-postgresql-how-to-connect-our-database-to-our-simple-express-server-without-an-orm-10o0) 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](https://expressjs.com/)
[PostgreSQL Docs](https://www.postgresql.org/docs/current/index.html)
[SQL Syntax](https://en.wikipedia.org/wiki/SQL_syntax)
[sequelize at npm](https://www.npmjs.com/package/sequelize)
[sequelize docs](https://sequelize.org/master/)