miku86

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

2019-09-12

Intro

We learned:

Now we want to learn how to connect our PostgreSQL database with our express server with pg and no ORM.

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

mkdir node-postgres-pg
cd node-postgres-pg
npm init
npm i express 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-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

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

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.

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 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.

Further Reading