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

---
title: NodeJS & PostgreSQL: How To Connect Our Database To Our Simple Express Server (without an ORM)
published: true
description: NodeJS & PostgreSQL: How To Connect Our Database To Our Simple Express Server (without 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)

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

---

## Create A Project Folder, do `npm init`, install express & pg

```bash
mkdir node-postgres-pg
cd node-postgres-pg
npm init
npm i express 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-pg
psql -d node-postgres-pg
\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 a generic query

```js
const { Pool } = require('pg');
// your credentials
DATABASE_URL = 'postgres://[db-user]:[password]@127.0.0.1:5432/node-postgres-pg';

const pool = new Pool({
  connectionString: DATABASE_URL
});

// a generic query, that executes all queries you send to it
function query(text) {
  return new Promise((resolve, reject) => {
    pool
      .query(text)
      .then((res) => {
        resolve(res);
      })
      .catch((err) => {
        reject(err);
      });
  });
}

module.exports = {
  query
};
```

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`. You should also move the database config into a separate file. 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 database = require('./database');

const User = {
  async readAll(req, res) {
    try {
      const readAllQuery = 'SELECT * FROM users';
      const { rows } = await database.query(readAllQuery);
      return res.send({ rows });
    } catch (error) {
      return res.send(error);
    }
  }
};

module.exports = User;
```

A User object, that has one method `readAll()`.
This method sends a PostgreSQL query (`SELECT * FROM users`) to the generic query in `database.js` (we imported it at the top) and awaits the response, where it destructures the `rows` and returns them.

---

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

```

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 query (`INSERT`, `DELETE` etc.)
- add a new route in `index.js` that runs the new method

---

## Next Part

We will learn how to use an [ORM](https://en.wikipedia.org/wiki/Object-relational_mapping).

---

## 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)
[pg at npm](https://www.npmjs.com/package/pg)
[pg docs](https://node-postgres.com/)