Node.js Connect PostgreSQL and perform data operations _node.js

Objective

PostgreSQL is an object-oriented relational database, PostGIS is a spatial database plug-in based on PostgreSQL, which is mainly used to manage geo-spatial data. Therefore, in the field of GIS, PostgreSQL is widely used as spatial database.

First use NPM to install the database connection module:

Connection Pool Creation

Then introduce the PG module into the code and write the database configuration:

var pg = require (' pg ');

Database configuration
var config = { 
 User: "Postgres", db
 : "Ghost",
 Password: "123456",
 port:5432,

 //Extended attribute
 max:20,//connection pool maximum connection number
 idletimeoutmillis:3000,//Connect maximum idle time 3s
}

There are two types of database connections in the PG module, first the connection pool mode, and the following is the Create connection pool:

Create a connection pool

The connection pool is created after the incoming configuration.

Querying data

The query first creates a connection and then invokes the API for querying:

Query
pool.connect (function (err, client, done) { 
 if (err) {return
 console.error (' Database connection error ', err);
 }
 Simply output a Hello World
 client.query (' SELECT $1::varchar as Out ', ["Hello World"], function (err, result) {done
 ();/ Releases the connection (returns it to the connection pool)
 if (err) {return
 console.error (' query error ', err);
 }
 Console.log (result.rows[0].out); Output:hello World
 });

Output:

The argument done is a function that calls this function to turn off the connection (the connection will be back to the connection pool).

The above is an asynchronous query that requires write callbacks, which can be await and async in Es 7 (but need to install the latest version of PG, plus more than 7.2 of the nodejs, preferably with the latest Nodejs) to optimize the code as follows:

Async & Await Mode (Requires node ^7.2.1, Runtime uses node--harmony-async-await index.js)
var query = Async () => { 
 //Same Step Create connection
 var connect = await Pool.connect ()
 try {
 //sync wait result
 var res = await connect.query (' SELECT $1::varcha "R as Out", [' Hello World by Async&await '])
 console.log (res.rows[0].out)//can traverse data through rows
 } finally {
 Connect.release ()
 }

///Asynchronous Database Processing

After the Nodejs has been upgraded, you need to add parameters when executing the code--harmony-async-await

Of course, all support to ES7, ES6 's Promise method is certainly supported, as follows:

Pool.connect (). Then (client=>{ 
 client.query (' SELECT $1::varchar as Out ", [' Hello World by Promise ']). Then (res= >{
 client.release ()
 console.log (res.rows[0].out)
 }). catch (E => {
 client.release ()
 Console.error (' query error ', E.message, E.stack)}
})

Inserting, modifying, and deleting data

Inserts, modifies, deletes the data and the query the similar

Inserts, modifies, deletes data in table test, a total of two fields (name, age) Pool.connect (). Then (client=>{//Insert Data client.query ("INSERT INTO Test" (NA Me, age) VALUES ($1::varchar, $2::int) ", [" Xiaoming "," M "]). Then (res=>{console.log (" Insert Success ")//If the ID is self added,
 Having a return value, returning res in res;
 }). Then (res=>{//Query xiaoming return client.query ("SELECT * FROM Test WHERE name = $", ["xiaoming"]); }). Then (res=>{//output results to see if insert successful Console.log (Res.rows[0])}). Then (res=>{//Update data, change the age to return Client.quer Y ("UPDATE test SET age=$1 WHERE name=$2", [N, "Xiaoming"])}. Then (res=>{//Requery once again xiaoming return Client.query ("Sel
 ECT * FROM Test WHERE name = $ ", [" xiaoming "]);  ). Then (res=>{//again output the result, see if changed to Console.log (Res.rows[0])}). Then (res=>{//delete data client.query ("Delete from test  WHERE name=$1 ", [" Xiaoming "]}). Then (res=>{//Last requery xiaoming res = client.query (" SELECT * FROM test WHERE name =
 $ ", [" xiaoming "]); Free connection client.release () return res}). Then (res=>{//again output results,No data undefined Console.log (Res.rows[0])}) 

The above inserts, the update the code all does not carry on the error processing, according to the reason is to add, but if wants to add Try...catch ... , it would be too much trouble (just an example, after all).

Event Monitoring

You can add an Error event method to listen for connection pooling

Pool.on ("Error", function (err, client) { 
 Console.log ("Error-->", err)
})

The maximum idle time for the connection pool is now 3s, that is, 3s has not used the connection, the release of the connection, you can set this time longer, such as 30s, which let us have enough time to shut down the database for testing (with the database connection a disconnect, this event was triggered, production environment, can be used to write logs, e-mail SMS notice what ... ）。

In addition, you can listen for acquire and connect events that are triggered when the connection is fetched by the client, which is triggered when the connection is generated and when the client interacts with the database.

Pool.on (' Acquire ', function (client) { 
 Console.log ("Acquire Event")
})

Pool.on (' Connect ', function () { 
 Console.log ("Connect Event")
})

Clients that do not use connection pooling

When you are not using a connection pool, you can create the client directly:

Connection pooling is only used to manage (caching) connections (i.e., clients), and queries like this do not matter.

Summarize

The above is the entire content of this article, I hope the content of this article for everyone's study or work can have some help, if there is doubt you can message exchange.