［Node.js + PostgreSQL］使用 Node.js 应用连接到 PostgreSQL 数据库

2 年 ago

清, 宇

2 minutes

写作

使用Node.js创建的应用程序如何连接到PostgreSQL数据库。

前提条件 tí

使用下述文章中提到的方法连接到数据库。
将[DB / SQL]要求转化为表格的记事方法的备忘录（以复式簿记表格设计为例）。

文件夹结构

NodeJSSampleApp
┣ app.js
┣ node_modules
┣ package-lock.json
┣ package.json
┣ public
┗ views
  ┗ journal.ejs

代码 (Mandarin Chinese)

Pinyin:

const express = require('express');
const app = express();

app.use(express.static('public'));

var {Client} = require('pg');

var client = new Client({
    user: 'DB USER NAME', // DB のユーザー名を指定
    host: 'localhost',
    database: 'SampleApp',
    password: 'DB PASSWORD', // DB のパスワードを指定
    post: 5432
})

client.connect();

var query = 'select journal.id as 仕訳ID,journal.date as 日付, (select accounts_title.name from accounts_title where accounts_title.id = journal_details.debit_accounts_id) as 借方科目,journal_details.credit_amount as 借方金額,(select accounts_title.name from accounts_title where accounts_title.id = journal_details.credit_accounts_id) as 貸方科目,journal_details.credit_amount as 貸方金額,journal.memo as 摘要 from journal_details join journal on journal_details.journal_id = journal.id;';

app.get('/',(req,res)=>{

    client.query(query,(error,result)=>{
        console.log(result);
        res.render('journal.ejs',{results: result}); // results に格納した取得結果を journal.ejs で表示
        client.end();
    });

});

app.listen(3000);

<html>
<head>
<title>複式簿記</title>
<link rel = "stylesheet" type="text/css" href = "/css/style.css">
<head>
<body>
<h1>複式簿記</h1>
<table>
<thead>
<tr>
<th>仕分ID</th>
<th>日付</th>
<th>借方科目</th>
<th>借方金額</th>
<th>貸方科目</th>
<th>貸方金額</th>
<th>摘要</th>
</tr>
</thead>
<% for(var i=0;i< results.rowCount;i++) {%>
<% var year = results.rows[i].日付.getFullYear(); %>
<% var month = results.rows[i].日付.getMonth() + 1; %>
<% var day = results.rows[i].日付.getDate(); %>
<tr>
<td><%= results.rows[i].仕訳id %></td>
<td><%= year + '/' + month + '/' + day %></td>
<td><%= results.rows[i].借方科目 %></td>
<td><%= results.rows[i].借方金額 %></td>
<td><%= results.rows[i].貸方科目 %></td>
<td><%= results.rows[i].貸方金額 %></td>
<td><%= results.rows[i].摘要 %></td>
</tr>
<% } %>
</table>

</body>
</html>

打开应用程序，从浏览器中访问 localhost:3000

$ cd NodeJSSampleApp
$ node app.js

表明效果