Nodejs and PostgreSQL Tutorial with Example
PostgreSQL is one of the popular open source [Object]- relational database, PostgreSQL supports json datatypes as well as you can define your own datatypes so its a good choice to use in your project. If you are using Nodejs as backend then you need PostgreSQL database driver to connect with nodejs. There are various driver available but most popular and well documented is node-postgres [pg] so today we are going to learn how to use PostgreSQL with nodejs using stored procedure and connection pooling. Let’s start.
Prerequisite
- You need to download postgresql and install on machine plus pgAdmin (postgresql management tool)
- Nodejs and npm (node package manager)
Folder Structure
1
2
3
|
|-- node_modules
|-- package.json
|-- server.js
|
my package.json file.
1
2
3
4
5
6
7
8
|
{
"name": "node-pg",
"author": "Javascript Point",
"dependencies": {
"express": "^4.14.0",
"pg": "^6.0.1"
}
}
|
Create table in Postgresql
Now go to pgAdmin and use postgresql database and create a dummy table
Student.
1
2
3
4
5
|
CREATE TABLE Student(
id int not null,
name text not null,
rollnumber int not null
);
|
Insert some dummy record into the table so later we will fetch through nodejs application.
1
2
3
4
5
6
|
INSERT INTO Student values(1,'Billy John',10001);
INSERT INTO Student values(2,'Smith George',10002);
INSERT INTO Student values(3,'Ernest Cook',10003);
INSERT INTO Student values(4,'Marshall Ballard',10004);
INSERT INTO Student values(5,'Joann Riley',10005);
INSERT INTO Student values(6,'Pearl Pearson',10006);
|
so till now we have created a table and inserted some records . Now we are going to create our nodejs app using command
1
|
npm init
|
Installing nodejs postgresql driver
Install expressjs and postgresql nodejs driver [pg] in our nodejs application using command
1
|
npm install express pg --save
|
this will install modules and write into our package.json file. Now we are going to use pg module to connect with database before that we have to create pg connection string. A pg database connection string made of with this format.
1
|
postgres://dbusername:passwrod@server:port/database
|
you can change your pg connection string as per your configuration.
here is my server.js file using pg module.
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
|
var express = require('express');
var pg = require("pg");
var app = express();
var connectionString = "postgres://postgres:123@localhost:5432/postgres";
app.get('/', function (req, res, next) {
pg.connect(connectionString,function(err,client,done) {
if(err){
console.log("not able to get connection "+ err);
res.status(400).send(err);
}
client.query('SELECT * FROM student where id = $1', [1],function(err,result) {
done(); // closing the connection;
if(err){
console.log(err);
res.status(400).send(err);
}
res.status(200).send(result.rows);
});
});
});
app.listen(4000, function () {
console.log('Server is running.. on Port 4000');
});
|
If you see i am using
pg.connect method and passing connection string to get a connection, in callback. catch any error if there otherwise call postgresql query using client.querymethod, in this method you can also pass parameter to query. run server.js file and go to url http://localhost:4000/ , you will see student with id = 1
Stored Procedure in PostgreSQL
Create a simple stored procedure name
GetAllStudent to fetch all student record and execute in pgAdmin
1
2
3
4
5
6
7
8
9
10
|
-- Get All Record from Student Table
CREATE OR REPLACE FUNCTION GetAllStudent()
RETURNS setof student AS
$BODY$
BEGIN
RETURN QUERY
select * from student;
END;
$BODY$
LANGUAGE plpgsql;
|
Now I will add one new endpoint /sp and call stored procedure with pg module in our server.js file
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
|
app.get('/sp', function (req, res, next) {
pg.connect(connectionString,function(err,client,done) {
if(err){
console.log("not able to get connection "+ err);
res.status(400).send(err);
}
client.query('SELECT * from GetAllStudent()' ,function(err,result) {
done(); // closing the connection;
if(err){
console.log(err);
res.status(400).send(err);
}
res.status(200).send(result.rows);
});
});
});
|
add this in
server.js, run node application and check url http://localhost:4000/sp , you will see all student records in browser.
Till here, you are able to use postgresql query and stored procedure through nodejs application but if you are running a web server where you are getting 100 request at a time then this code will open 100 connection to your postgresql database and slowly it will start throw no memory error because of so many connections open at a time, to overcome this situation use connection pool, this will create connection but will not close till some threshold and reuse for further requests. I will show how to use connection pooling in postgresql with nodejs.
Connection Pooling in PostgreSQL
create a new config object for connection pool like this
1
2
3
4
5
6
7
8
|
var config = {
user: 'postgres',
database: 'postgres',
password: '123',
port: 5432,
max: 10, // max number of connection can be open to database
idleTimeoutMillis: 30000, // how long a client is allowed to remain idle before being closed
};
|
now we will call
pg.pool method to create connection pool using
1
|
var pool = new pg.Pool(config);
|
now add new endpoint /pool , and use pool variable to connect with database
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
|
app.get('/pool', function (req, res) {
pool.connect(function(err,client,done) {
if(err){
console.log("not able to get connection "+ err);
res.status(400).send(err);
}
client.query('SELECT * from GetAllStudent()' ,function(err,result) {
//call `done()` to release the client back to the pool
done();
if(err){
console.log(err);
res.status(400).send(err);
}
res.status(200).send(result.rows);
});
});
});
|
now hit http://localost:4000/pool , you get all students records.
If you want to learn more about postgresql module you can refer documentation here.
If you want to learn more about postgresql module you can refer documentation here.
Arquitecto especialista en gestion de proyectos si necesitas desarrollar algun proyecto en Bogota contactame en el 3006825874 o visita mi pagina en www.arquitectobogota.tk
0 comentarios:
Publicar un comentario