DEV Community

miku86
miku86

Posted on

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

#beginners #tutorial #postgres #javascript

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

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-pg psql -d node-postgres-pg \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 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 };
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. 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;
Enter fullscreen mode Exit fullscreen mode

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

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

Express Docs
PostgreSQL Docs
SQL Syntax
pg at npm
pg docs

Top comments (0)