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:
- Go through my course on writing a basic NodeJs API with Express and Es6, you can find it below 👇
- Have a basic knowledge of SQL query syntax as this tutorial won't cover those
- Basic Knowledge of NodeJs and Express
- Have NodeJs installed, here is the download link
- Have PostgreSQL installed, here is the download link
- After installation search for the application
pgAdminand 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:
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:
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.Initialize your project as a node project by running
npm initfrom 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.
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;
- Express, this one we already encountered in the previous article
- To communicate with our postgres Database, we would need a package called node-postgres.
- 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
--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');
});
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();
});
};
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');
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"
Go to your command line while still in your project directory and run;
npm run create
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.
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}`);
});
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",
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.
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.
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
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}));
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],
});
});
});
});
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.
First you set your header information on postman:
After that, we would set the body values, follow the screenshot below:
Then you click Send, and holla you have your result
Now to run a get request, change request type to GET and then click send.
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`);
});
when we make a request to the new endpoint we would get;
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.
ogwurujohnson
/
AwesomeNodePostgres
A project created for the purpose of teaching how to persist data using PostgreSQL while creating APIs with NodeJs
Codebase for a Node API tutorial on dev.to
Storing Record in Database
Retrieving Record from Database
Updating Records in Database, etc
Project
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
Top comments (15)
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) => {...
You can, but using template literals to interpolate query parameters like you're suggesting opens you up to SQL injection attacks. If the
idvalue is passed from somewhere the user can modify it, someone could supply a value like3; 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
$nplaceholders 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.
Thanks dian
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
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
is using an orm like sequalize a good idea as we can also write raw sql queries in it?
Okay
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.jsinto the index.js fileWould do, thanks Prosper, for your kind words
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:
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.
Thanks for the suggestion,i woudl definitely try them out. Thanks again
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]
});
});
});
});
Hi Thomson, sorry for the late reply. Can you explain a little bit further what the problem is? 😉
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
Sorry for the late reply Samuel, but were you able to try that out?