Web Front-end
JS Tutorial
Introduction to Node.js connecting to postgreSQL and performing data operations
Introduction to Node.js connecting to postgreSQL and performing data operations
这篇文章就给大家介绍了关于Node.js如何连接postgreSQL数据库，并进行数据操作的方法，有需要的朋友们可以参考借鉴，下面来一起看看吧。
自从MySQL被Oracle收购以后，PostgreSQL逐渐成为开源关系型数据库的首选
前言
PostgreSql是一个面向对象的关系数据库，postgis是一个基于PostgreSql的空间数据库插件，主要用于管理地理空间数据。因此在GIS领域，广泛使用PostgreSql作为空间数据库。
首先使用npm安装数据库连接模块：
npm install --save pg
连接池创建
然后代码中引入pg模块，并编写数据库配置：
var pg = require('pg');
// 数据库配置
var config = {
user:"postgres",
database:"ghost",
password:"123456",
port:5432,
// 扩展属性
max:20, // 连接池最大连接数
idleTimeoutMillis:3000, // 连接最大空闲时间 3s
}pg模块中有两种数据库连接方式，先讲连接池模式，下面是创建连接池：
// 创建连接池 var pool = new pg.Pool(config);
传入配置后就创建好了连接池。
查询数据
查询首先创建好连接，然后调用api进行查询:
// 查询
pool.connect(function(err, client, done) {
if(err) {
return console.error('数据库连接出错', err);
}
// 简单输出个 Hello World
client.query('SELECT $1::varchar AS OUT', ["Hello World"], function(err, result) {
done();// 释放连接（将其返回给连接池）
if(err) {
return console.error('查询出错', err);
}
console.log(result.rows[0].out); //output: Hello World
});
});输出：
Hello World
参数done是一个函数，调用这个函数可以将关闭连接（即将连接还给连接池）。
上面的是需要写回调的异步查询，可以使用ES 7中await和async（但需安装最新版本的pg，另外，需要使用7.2以上的nodejs，最好就是用最新的nodejs）优化代码，如下：
// Async & Await 方式（需 node ^7.2.1，运行时使用 node --harmony-async-await index.js）
var query = async () => {
// 同步创建连接
var connect = await pool.connect()
try {
// 同步等待结果
var res = await connect.query('SELECT $1::varchar AS OUT', ['Hello World By Async&Await'])
console.log(res.rows[0].out) // 可以通过rows遍历数据
} finally {
connect.release()
}
}
// 异步进行数据库处理
query().catch(e => console.error(e.message, e.stack));在升级了nodejs之后，执行代码的时候，需要加参数--harmony-async-await
npm --harmony-async-await index.js
当然，都支持到ES7了，ES6的Promise方法肯定是支持的，如下：
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)
})
})插入、修改、删除数据
插入、修改、删除数据和查询的差不多
// 在表test中插入、修改、删除数据，共两个字段 (name, age)
pool.connect().then(client=>{
// insert 数据
client.query("INSERT INTO test(name, age) VALUES($1::varchar, $2::int)", ["xiaoming","20"]).then(res=>{
console.log("Insert Success")
// 如果是自增ID，有返回值的，在res里
return res;
}).then(res=>{
// 查询xiaoming
return client.query("Select * FROM test WHERE name = $1", ["xiaoming"]);
}).then(res=>{
// 输出结果，看是否插入成功
console.log(res.rows[0])
}).then(res=>{
// update 数据，将age改为21
return client.query("UPDATE test SET age=$1 WHERE name=$2", [21, "xiaoming"])
}).then(res=>{
// 再查询一次xiaoming
return client.query("Select * FROM test WHERE name = $1", ["xiaoming"]);
}).then(res=>{
// 再输出结果，看是否改为了21
console.log(res.rows[0])
}).then(res=>{
// 删除数据
client.query("DELETE FROM test WHERE name=$1", ["xiaoming"])
}).then(res=>{
// 最后再查询一次xiaoming
res = client.query("Select * FROM test WHERE name = $1", ["xiaoming"]);
// 释放连接
client.release()
return res
}).then(res=>{
// 再输出结果，没数据 undefined
console.log(res.rows[0])
})
})上面插入、更新里代码都没有进行错误处理，按道理是要加的，但如果要加try...catch...的话，就太麻烦了（毕竟只是示例）.
事件监听
可以添加error事件方法监听连接池情况
pool.on("error", function(err, client){
console.log("error --> ", err)
})现在连接池的最大空闲时间是3s，也就是3s还没使用连接，就释放连接，可将这个时间设置得长一些，比如30s，这就让我们有足够的时间关掉数据库进行测试（与数据库连接一断开，这个事件就被触发了，生产环境中，可以用来写日志啊、发邮件短信通知什么的。。。）。
另外，还可以监听acquire和connect事件，前者在连接被客户端获取时触发，后者在连接生成以及客户端与数据库交互时触发。
pool.on('acquire', function (client) {
console.log("acquire Event")
})
pool.on('connect', function () {
console.log("connect Event")
})不使用连接池的客户端
不使用连接池时，直接创建客户端即可：
var client = new pg.Client();
连接池只是用来管理（缓存）连接（即客户端）的，查询之类的方法跟它没关系。
以上就是本文的全部内容，希望对大家的学习有所帮助，更多相关内容请关注PHP中文网！
相关推荐：
The above is the detailed content of Introduction to Node.js connecting to postgreSQL and performing data operations. For more information, please follow other related articles on the PHP Chinese website!
Hot AI Tools
Undresser.AI Undress
AI-powered app for creating realistic nude photos
AI Clothes Remover
Online AI tool for removing clothes from photos.
Undress AI Tool
Undress images for free
Clothoff.io
AI clothes remover
Video Face Swap
Swap faces in any video effortlessly with our completely free AI face swap tool!
Hot Article
Hot Tools
Notepad++7.3.1
Easy-to-use and free code editor
SublimeText3 Chinese version
Chinese version, very easy to use
Zend Studio 13.0.1
Powerful PHP integrated development environment
Dreamweaver CS6
Visual web development tools
SublimeText3 Mac version
God-level code editing software (SublimeText3)
Hot Topics
Is nodejs a backend framework?
Apr 21, 2024 am 05:09 AM
Node.js can be used as a backend framework as it offers features such as high performance, scalability, cross-platform support, rich ecosystem, and ease of development.
How to connect nodejs to mysql database
Apr 21, 2024 am 06:13 AM
To connect to a MySQL database, you need to follow these steps: Install the mysql2 driver. Use mysql2.createConnection() to create a connection object that contains the host address, port, username, password, and database name. Use connection.query() to perform queries. Finally use connection.end() to end the connection.
What are the global variables in nodejs
Apr 21, 2024 am 04:54 AM
The following global variables exist in Node.js: Global object: global Core module: process, console, require Runtime environment variables: __dirname, __filename, __line, __column Constants: undefined, null, NaN, Infinity, -Infinity
What is the difference between npm and npm.cmd files in the nodejs installation directory?
Apr 21, 2024 am 05:18 AM
There are two npm-related files in the Node.js installation directory: npm and npm.cmd. The differences are as follows: different extensions: npm is an executable file, and npm.cmd is a command window shortcut. Windows users: npm.cmd can be used from the command prompt, npm can only be run from the command line. Compatibility: npm.cmd is specific to Windows systems, npm is available cross-platform. Usage recommendations: Windows users use npm.cmd, other operating systems use npm.
Pi Node Teaching: What is a Pi Node? How to install and set up Pi Node?
Mar 05, 2025 pm 05:57 PM
Detailed explanation and installation guide for PiNetwork nodes This article will introduce the PiNetwork ecosystem in detail - Pi nodes, a key role in the PiNetwork ecosystem, and provide complete steps for installation and configuration. After the launch of the PiNetwork blockchain test network, Pi nodes have become an important part of many pioneers actively participating in the testing, preparing for the upcoming main network release. If you don’t know PiNetwork yet, please refer to what is Picoin? What is the price for listing? Pi usage, mining and security analysis. What is PiNetwork? The PiNetwork project started in 2019 and owns its exclusive cryptocurrency Pi Coin. The project aims to create a one that everyone can participate
Is there a big difference between nodejs and java?
Apr 21, 2024 am 06:12 AM
The main differences between Node.js and Java are design and features: Event-driven vs. thread-driven: Node.js is event-driven and Java is thread-driven. Single-threaded vs. multi-threaded: Node.js uses a single-threaded event loop, and Java uses a multi-threaded architecture. Runtime environment: Node.js runs on the V8 JavaScript engine, while Java runs on the JVM. Syntax: Node.js uses JavaScript syntax, while Java uses Java syntax. Purpose: Node.js is suitable for I/O-intensive tasks, while Java is suitable for large enterprise applications.
Is nodejs a back-end development language?
Apr 21, 2024 am 05:09 AM
Yes, Node.js is a backend development language. It is used for back-end development, including handling server-side business logic, managing database connections, and providing APIs.
Which one to choose between nodejs and java?
Apr 21, 2024 am 04:40 AM
Node.js and Java each have their pros and cons in web development, and the choice depends on project requirements. Node.js excels in real-time applications, rapid development, and microservices architecture, while Java excels in enterprise-grade support, performance, and security.
