DEV Community

loading...
Cover image for  Persisting a Node API with PostgreSQL, without the help of ORM's like sequelize.

Persisting a Node API with PostgreSQL, without the help of ORM's like sequelize.

ogwurujohnson profile image Johnson Ogwuru ・7 min read

What do we mean when we use the term 'persist'? Persistence in our everyday English is "the continuance of an effect after its cause is removed", in the context of storing data in the a database, persistence refers to the ability of a record to survive after the process with which it was created has ended.

Unlike other NodeJS tutorials where data persistence is done with MongoDB, we would be using PostgreSQL instead. And also in order to make sure we get the basics of developing Restful API'S with NodeJs and PostgreSQL, we would be avoiding the use of ORM(Object-relational mapping), read more about it 👉 here.

Prerequisites:

  1. Go through my course on writing a basic NodeJs API with Express and Es6, you can find it below 👇
  2. Have a basic knowledge of SQL query syntax as this tutorial won't cover those
  3. Basic Knowledge of NodeJs and Express
  4. Have NodeJs installed, here is the download link
  5. Have PostgreSQL installed, here is the download link
  6. After installation search for the application pgAdmin and get it started, comes installed with PostgreSQL. pgAdmin is a GUI administration and development platform for PostgreSQL(meaning it helps make our DB management much easier and friendly)

Getting Started:
In this tutorial we would be creating an API for a class registry, we would get started with creating and setting up our Database.

Follow the direction below to creating a Database with pgAdmin:
1. Double click on the PostgreSQL server.

2. Right-lick on Login Roles, assuming you have none and select new login role.

3. Type your preferred Role name, mine is school_reg.

4. Move to the next tab *Definition* and set your password for this role, mine is school_reg.

5. Click on *OK* to save.

6. Right-click on Databases and select New Database.

7. Type Database name in the space for Name, mine is school_register.

8. Select owner of Database, here we would select the login role or user we created, remember mine is school_reg.

9. Click *OK* to save.

The Image above illustrates the process above, zoom in to see clearly:

Image illustrating creation of Database

After creating our Database, we would need to create tables and table relationships, in geeky terms, let's create our schema. For now we would have just one table, our student table, that would have basic information about the student, as the application grows if we choose to expand, we would have to create further tables.

Project Setup:

  1. We would start by creating our project folder, i named mine AwesomeNodepostgres, yeah i know that's long, you are free to choose a shorter name.

  2. Initialize your project as a node project by running npm init from your command line when you have found your way to your project folder from the command line. Refer to the last tutorial to figure out how to do this if you haven't already.

Having gone through the steps above, your project directory should appear like this, assuming you are making use of my favorite code editor vscode.
vscode shot

Now that's out of the way, lets start by installing some packages we would need to get writing our application. We would be needing the following node packages in this tutorial;

  1. Express, this one we already encountered in the previous article
  2. To communicate with our postgres Database, we would need a package called node-postgres.
  3. To make sure our server is always listening for changes and restart our application, we would also install another package nodemon.

To install the first two packages, make sure you are inside the project folder from the command line. Then run the following commands;

            npm install express pg --save
            npm install nodemon --save-dev
Enter fullscreen mode Exit fullscreen mode

--save-dev here means save and only use nodemon during development as a development dependency, so during production nodemon won't be used.

Now to establish database connection, we would create a folder services, and inside the folder we would create a file named db.js.
So inside services/db.js, type the following code;

const pg = require('pg');

const config = {
  user: 'school_reg', //this is the db user credential
  database: 'school_register',
  password: 'school_reg',
  port: 5432,
  max: 10, // max number of clients in the pool
  idleTimeoutMillis: 30000,
};

const pool = new pg.Pool(config);

pool.on('connect', () => {
  console.log('connected to the Database');
});
Enter fullscreen mode Exit fullscreen mode

Now to create our tables, type the following code under the previous block of code;

const createTables = () => {
  const schoolTable = `CREATE TABLE IF NOT EXISTS
      students(
        id SERIAL PRIMARY KEY,
        student_name VARCHAR(128) NOT NULL,
        student_age INT NOT NULL,
        student_class VARCHAR(128) NOT NULL,
        parent_contact VARCHAR(128) NOT NULL,
        admission_date VARCHAR(128) NOT NULL
      )`;
  pool.query(schoolTable)
    .then((res) => {
      console.log(res);
      pool.end();
    })
    .catch((err) => {
      console.log(err);
      pool.end();
    });
};
Enter fullscreen mode Exit fullscreen mode

To create our table with this script we need to find a way to run this code on the command line. we can use another package for this called make-runnable, install the package; npm install make-runnable --save, after installation, go back to your codebase at the bottom of the page, add the following;

pool.on('remove', () => {
  console.log('client removed');
  process.exit(0);
});


//export pool and createTables to be accessible  from an where within the application
module.exports = {
  createTables,
  pool,
};

require('make-runnable');
Enter fullscreen mode Exit fullscreen mode

Now to create our app, lets write a script on our package.json file, which when run from the command line would create a table for us. On your package.json file locate the object scripts, replace what is inside the object with the code below;

"create": "node ./services/db createTables"
Enter fullscreen mode Exit fullscreen mode

Go to your command line while still in your project directory and run;

npm run create
Enter fullscreen mode Exit fullscreen mode

The image below shows what happens when you do, and also look closely at where you would locate your newly created table on the pgAdmin application.

runing npm run create

viewing newly created table

With our Database and Table creation complete, its time for us to start storing data in our database and retrieving this stored data too.

Before we do, lets create our index.js file, which would contain our server and express codes, on the project folder create a file named index.js. Type the following code into our index.js.

   const express = require('express');
   const app = express();


   const port = process.env.PORT || 3000;


   // Add route code Here
   app.get('/', (req, res) => {
      res.send('Welcome to Our SCHOOL API');
   });


   app.listen(port, () => {
      console.log(`We are live at 127.0.0.1:${port}`);
   });
Enter fullscreen mode Exit fullscreen mode

To run our app, lets add two extra codes on our scripts object found on the package.json file, before create, drop this two;

    "dev": "nodemon index.js",
    "start": "node index.js",
Enter fullscreen mode Exit fullscreen mode

scripts for running

Now to run our project, we would be using the nodemon option, so whatever changes we make, we would be able to notice. Try observing how nodemon restarts the server, when you make a change and save it.

nodemon starting server

With our server up, visit the link your server is listening on, 127.0.0.1:3000 as is specified on mine. Open your postman application and make a GET request using the link.

postman

Now lets, create routes that would handle creation of students, and fetching of all students. After the first route on your index.js file, add the following codes, these codes would handle, inserting a student, fetching all students. To be able to insert into our Database we would need to be able to pick the value supplied to the body of any request, and to do this we would use a node package called body-parser.

npm install body-parser
Enter fullscreen mode Exit fullscreen mode

After installing the package above, add the following code to our index.js before continuing with our routes.Add the following below const app = express();

const bodyParser = require('body-parser');
app.use(bodyParser.json());
app.use(bodyparser.urlencoded({extended:true}));
Enter fullscreen mode Exit fullscreen mode

Now lets continue with our routes;

app.get('/student', (req, res) => {
pool.connect((err, client, done) => {
    const query = 'SELECT * FROM students';
    client.query(query, (error, result) => {
      done();
      if (error) {
        res.status(400).json({error})
      } 
      if(result.rows < '1') {
        res.status(404).send({
        status: 'Failed',
        message: 'No student information found',
        });
      } else {
        res.status(200).send({
        status: 'Successful',
        message: 'Students Information retrieved',
        students: result.rows,
        });
      }
    });
  });
});


app.post('/student', (req, res) => {
  const data = {
    name : req.body.studentName,
    age : req.body.studentAge,
    classroom : req.body.studentClass,
    parents : req.body.parentContact,
    admission : req.body.admissionDate,
  }

  pool.connect((err, client, done) => {
    const query = 'INSERT INTO students(student_name,student_age, student_class, parent_contact, admission_date) VALUES($1,$2,$3,$4,$5) RETURNING *';
    const values = [data.name, data.age, data.classroom, data.parents, data.admission];

    client.query(query, values, (error, result) => {
      done();
      if (error) {
        res.status(400).json({error});
      }
      res.status(202).send({
        status: 'SUccessful',
        result: result.rows[0],
      });
    });
  });
});
Enter fullscreen mode Exit fullscreen mode

Run your app, and visit the link on postman, this time visit the link 127.0.0.1/student alternating between GET and POST for each of the request. Take a look at the screenshots below and do exactly same thing if you don't know how to test with postman.

  1. First you set your header information on postman:
    header information

  2. After that, we would set the body values, follow the screenshot below:
    body config

  3. Then you click Send, and holla you have your result
    result

Now to run a get request, change request type to GET and then click send.

get

Wolla!!! Now we have our API fetching and sending to the Database.

Note: Having our route, server and express application on one file is bad practice, star this projects Repository on GitHub, fork and submit PR's, to learn how to, as i continue working on it, or you could try working on it yourself and submitting a push request.

Let's add one more route to our application, say we want to fetch a particular students information, we would write the route, but i expect you to write the queries and return a response, based on what you've learnt.

app.get('/student/:id', (req,res) => {
  const id = req.params.id;
  res.send(`Student ${id} profile`);
});
Enter fullscreen mode Exit fullscreen mode

when we make a request to the new endpoint we would get;

individual student

SO try completing the code to pick the student whose id your are referencing from the Database.

Attached Below is the GitHub Repo as promised., don't forget to star. Thanks.

In our next tutorial, we would be looking at how we could authenticate our routes, so without proper authentication, users cant access the routes.

GitHub logo ogwurujohnson / AwesomeNodePostgres

A project created for the purpose of teaching how to persist data using PostgreSQL while creating APIs with NodeJs

Node Express Ppostgres
 

Codebase for a Node API tutorial on dev.to

Storing Record in Database

Retrieving Record from Database

Updating Records in Database, etc


image

Project

GitHub issues GitHub forks GitHub stars GitHub license price

Twitter

AwesomeNodePostgres

A project created for the purpose of teaching how to persist data using PostgreSQL while creating APIs with NodeJs we would be working on creating a classroom application, an API for now to handle viewing all students, adding a student, editing a student details, deleting a student, etc. Feel free to add other functionalities as well by contributing to this project and leaving direction as how to use it on the README.md file




Discussion (15)

Collapse
lionardo profile image
lionardo • Edited

Awesome post, meanwhile I have a question. Can you use template literals for queries? Example:
const query = SELECT sometable FROM students WHERE id=${id};
client.query(query, (error, result) => {...

Collapse
dmfay profile image
Dian Fay

You can, but using template literals to interpolate query parameters like you're suggesting opens you up to SQL injection attacks. If the id value is passed from somewhere the user can modify it, someone could supply a value like 3; DROP TABLE users CASCADE; and the driver would happily execute it.

Interpolation like that is also more difficult with strings because of the quoting rules, so it's really never worth it. Prepared statements with $n placeholders are easier and safer.

For Postgres+Node specifically, pg-promise lets you use named parameters and dynamic SQL with prepared statements, which might be worth checking out if you want to avoid having to count params.

Collapse
ogwurujohnson profile image
Collapse
ogwurujohnson profile image
Johnson Ogwuru Author

Sorry for the late reply liornado, figured you have gotten the reply to your question, to re echo, yes you can but you might be opening up your app to lots of security issues

Collapse
slidenerd profile image
slidenerd

Many standard practices have not been followed
First of all you dont need to call pool.end Check this Github Issue on pg github.com/brianc/node-postgres/is...
Secondly, you havent structured the repo as per the standards mentioned by the repo owner, Check this section of the documentation node-postgres.com/guides/async-exp...
Thirdly you are not using migrations to create the tables, that is how you would do it in production, you should use a package like github.com/salsita/node-pg-migrate to create migration files, then run the DML queries only via pg, I dont have anything against you but an incomplete article such as this sways the newbies into NOT following the standard practices which is bad in the long run, if you dont have the time for rewriting this post, let me know, I ll be happy to correct everything and link back to your post

Collapse
vikrantsingh47 profile image
vikrant singh • Edited

is using an orm like sequalize a good idea as we can also write raw sql queries in it?

Collapse
ogwurujohnson profile image
Collapse
kodekage profile image
Prosper Opara

Thanks for the awesome piece johnson, it's one of the few tutorials that don't focus on using sequalize.

Update the article to include the part where you import { pool } from services/db.js into the index.js file

Collapse
ogwurujohnson profile image
Johnson Ogwuru Author

Would do, thanks Prosper, for your kind words

Collapse
dmfay profile image
Dian Fay

Good on you for avoiding O/RMs, but you don't have to resort to writing SQL for everything, especially if you want to stick to the JavaScript part! There are a couple of options that give you a more "JavaScripty" way to work with your database without the O/RM overhead:

  • query builders like Knex
  • data mappers like (my project) Massive

They have slightly different strengths & so the appropriate choice depends on where you're planning to take this. Query builders do well if you have a lot of one-offs, different joins depending on context, customizable aggregation, and so on. Data mappers are more organized and provide a consistent framework for retrieving and manipulating information.

Collapse
ogwurujohnson profile image
Johnson Ogwuru Author

Thanks for the suggestion,i woudl definitely try them out. Thanks again

Collapse
tvmthomson profile image
Thomson Varkey

I am using PostgreSQL functions instead of query the problem facing is the errror. The errors are driven as result here the code follows

router.post('/insert_update_employee', (req, res) => {
debugger;
data = req.body;
pool.connect((err, client, done) => {
const query = 'SELECT insert_update_employee($1,$2,$3,$4,$5,$6,$7,$8,$9,$10,$11,$12) ';
const values = [data.ID, data.Name, data.DOB, data.ContactNo, data.EmailAddress, data.SupervisorID, data.PositionID, data.HireDate, data.IsSupervisor, data.ImageUrl, data.UserName, data.IsUpdate];
client.query(query, values, (err, result) => {
done();
if (err) {
debugger;
res.status(400).json({ err });
}
debugger;
res.status(202).send({
status: 'SUccessful',
result: result.rows[0]
});
});
});
});

Collapse
ogwurujohnson profile image
Johnson Ogwuru Author

Hi Thomson, sorry for the late reply. Can you explain a little bit further what the problem is? 😉

Collapse
wekesamuel profile image
wekesamuel

Hi, can we add multiple files inside here "create": "node ./services/db createTables" , so that instead of it running only the db file, it can also run other files inside the services folder

Collapse
ogwurujohnson profile image
Johnson Ogwuru Author

Sorry for the late reply Samuel, but were you able to try that out?

Forem Open with the Forem app