Postgresのプール

ここを参考にpool.jsを作成してみた

pool.js

const { Pool } = require("pg");

const pool = new Pool({
    user: 'postgres',
    host: 'localhost',
    database: 'postgres',
    password: 'password',
    port: 5432,
    max: 2
});

pool.tx = async (cb) => {
    const connection = await pool.connect();
    let res;
    try {
        await connection.query('BEGIN');
        try{
            res = await cb(connection);
            await connection.query('COMMIT');
        } catch(err) {
            await connection.query('ROLLBACK');
            throw err;
        }
    } catch(err) {
        throw err;
    } finally {
        connection.release();
    }
    return res;
}

module.exports = pool;

これを利用してexpressを使って実行

const express = require('express');
const app = express();
const bodyParser = require('body-parser');
const dbpool = require('./pool.js');
const port = 3000;

app.use(bodyParser.urlencoded({ extended: true }));
app.use(bodyParser.json());

var pgSelect = {
    text: 'SELECT id,name from test where id=$1',
    values: [0],
}

var pgInsert = {
    text: 'INSERT INTO test(id,name) VALUES ($1,$2)',
    values: [0,'sample'],
}

app.get('/', async function(req, res, next){
		let result;
		
		const reqid = req.query.id;
		pgSelect.values = [reqid];
		
		try{
			result = await dbpool.tx(async client => {
				const res1 = await client.query(pgSelect);
				return res1;
			});
		} catch(err) {
			console.error(err);
			res.sendStatus(500);
			return;
		}
		
		res.status(200).json(result.rows);
		
	}
);

app.post('/', async function(req, res, next) {
		let result;
		const reqid = req.body.id;
		const reqname = req.body.name;

		
		pgInsert.values = [reqid,reqname];
		
		try{
			result = await dbpool.tx(async client => {
				const res1 = await client.query(pgInsert);
				return res1;
			});
		} catch(err) {
			console.error(err);
			res.sendStatus(500);
			return;
		}
		
		res.status(200).json(result.rowCount);
	}
);

app.listen(port, () => console.log(`Example app listening on port ${port}!`));

Basic認証

form認証等でpassportを使う場合は以下をインストールする。
npm install passport
npm install passport-local
セッション機能を利用する場合は追加
npm install express-session

basic認証で良い場合はpassport（のpassport-http）でも認証可能だが、
npm install express-basic-auth
を使ってもよい。
ただ、認証方式の差し替えを考えるとpassportにしておく方良さそう。

とりあえずbasic認証の方が準備が楽なので、軽く作ってみる。
npm install passport
npm install passport-http
でモジュールはインストール

書き換えて認証を追加。rest想定なのでセッションはfalse
ハッシュ化はnodeの標準モジュールを利用

const express = require('express');
const app = express();
const bodyParser = require('body-parser');
const dbpool = require('./pool.js');
const passport = require('passport');
const passportHttp = require('passport-http');
const crypto = require("crypto");
const port = 3000;

app.use(bodyParser.urlencoded({ extended: true }));
app.use(bodyParser.json());

app.use(passport.initialize());

// せっかくなのでusrテーブルで認証させる(SHA256でハッシュ化)
passport.use(new passportHttp.BasicStrategy(
    async function (username, password, done) {
    	let result;
    	const hashpass = crypto.createHash('sha256').update(password).digest('hex');
		try{
			var usrSelect = {
			    text: 'SELECT pass from usr where usr=$1',
			    values: [username],
			}
			result = await dbpool.tx(async client => {
				const res1 = await client.query(usrSelect);
				return res1;
			});
		} catch(err) {
			console.error(err);
			return done(null, false);
		}
    	
        if(hashpass===result.rows[0].pass){
            // 認証成功
            return done(null,true);
        }else{
            // 認証失敗
            return done(null, false);
        }
    }
));

var pgSelect = {
    text: 'SELECT id,name from test where id=$1',
    values: [0],
}

var pgInsert = {
    text: 'INSERT INTO test(id,name) VALUES ($1,$2)',
    values: [0,'sample'],
}

app.get('/', passport.authenticate('basic', { session: false }), async function(req, res, next){
		let result;
		
		const reqid = req.query.id;
		pgSelect.values = [reqid];
		
		try{
			result = await dbpool.tx(async client => {
				const res1 = await client.query(pgSelect);
				return res1;
			});
		} catch(err) {
			console.error(err);
			res.sendStatus(500);
			return;
		}
		
		res.status(200).json(result.rows);
		
	}
);

app.post('/', passport.authenticate('basic', { session: false }), async function(req, res, next) {
		let result;
		const reqid = req.body.id;
		const reqname = req.body.name;

		
		pgInsert.values = [reqid,reqname];
		
		try{
			result = await dbpool.tx(async client => {
				const res1 = await client.query(pgInsert);
				return res1;
			});
		} catch(err) {
			console.error(err);
			res.sendStatus(500);
			return;
		}
		
		res.status(200).json(result.rowCount);
	}
);

app.listen(port, () => console.log(`Example app listening on port ${port}!`));

TypeError: req.logIn is not a function
が出て時間をかなり使ったが、
app.use(passport.initialize());
を入れたら大丈夫になった。。