安装 npm i pg ,如果慢的话,记得爬梯子
连接池的方式:
var pg = require('pg'); // 数据库配置 var config = { user:"postgres", database:"test", password:"postgres", port:5432, // 扩展属性 max:20, // 连接池最大连接数 idleTimeoutMillis:3000, // 连接最大空闲时间 3s } // 创建连接池 var pool = new pg.Pool(config); // 查询 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 }); });
客户端直连方式:
const pg=require('pg') var conString = "postgres://username:password@localhost/databaseName"; var client = new pg.Client(conString); client.connect(function(err) { if(err) { return console.error('连接postgreSQL数据库失败', err); } client.query('SELECT * FROM tableName', function(err, data) { if(err) { return console.error('查询失败', err); }else{ // console.log('成功',data.rows); console.log('成功',JSON.stringify(data.rows)); } client.end(); }); });
自己摸索的一种方式:
// 连接pg var pg = require('pg'); var pool = new pg.Pool({ host: 'XXXXXX', user: 'XXXX', password: 'XXXXX', database: 'XXX', port:5432, max:20, // 连接池最大连接数 idleTimeoutMillis:3000, // 连接最大空闲时间 3s }); function query(sql, callback) { pool.connect(function (err, connection,done) { if (err) { console.log('connect query:' + err.message); return; } // Use the connection connection.query(sql,[],function (err, rows) { done(); callback(err, rows); //connection.release();//释放链接 }); }); } exports.query = query;
/** * 获取survey *localhost:5000/getAllSurveyList */ app.get('/getAllSurveyList', function (req, res) { console.log("getAllSurveyList"); db.query('select id,trim(latitude) as latitude,trim(lontitude) as lontitude,isshow as '+'"'+'isShow'+'"'+' from survey', function (err,rows) { if (err) { console.log(err); console.log('--getAllSurveyList: 查询失败'); res.json({result:'false',datas: null}); } else { console.log('--getAllSurveyList: 查询成功'); //console.log(rows); res.json({result: 'true', datas: rows.rows}); } }) });
CRUD
var pg = require('./pg'); //加载模块node-postgres,该模块要与本文件放于同一个目录下 var conString = "postgres://postgres:postgres@localhost:5432/node-test";//此时数据库必须已经创建 //anything://user:password@host:port/database var client = new pg.Client(conString); client.connect(function(err) { if(err) { return console.error('could not connect to postgres', err); } //删除存在表 console.log("Dropping table 'person'") var query = client.query("drop table if exists person"); query.on('end', function() { console.log("Dropped!"); }); //创建表 console.log("Creating table 'person'"); query = client.query("create table person(id serial, username varchar(10), password varchar(10), age integer)"); query.on('end', function(){ console.log("Created!"); }); //添加 client.query('INSERT INTO person(username, password, age) VALUES($1, $2, $3)', ["zhangsan", "123456", 20], function(err, result) { console.log( "====================add========================"); if(err) { console.log(err); return rollback(client); } console.log( result); }); //查询 client.query('select username, password, age from person where id = $1', [3], function(err, result) { console.log( "===================query========================="); if(err) { console.log(err); } console.log(result.rows[0]); }); //更新 client.query('update person set password=$1 where id = $2', ["11a",1], function(err, result) { console.log( "=====================update======================="); if(err) { console.log(err); } console.log(result); }); //删除 client.query('delete from person where id = $1', [1], function(err, result) { console.log( "====================remove======================="); if(err) { console.log(err); } console.log(result); client.end(); }); }); var rollback = function(client) { client.query('ROLLBACK', function() { client.end(); }); };