接続情報
.env
user='****'
password='*****'
data_base='city'
必要なライブラリーのインストール
sudo npm install -g pg
sudo npm install -g dotenv
環境の設定
export NODE_PATH=/usr/local/lib/node_modules
- PostgreSQL のバージョンを調べる方法です。
- Create
- Read
- Update
- Delete
version_check.js
#! /usr/local/bin/node
// ---------------------------------------------------------------
// version_check.js
//
// May/19/2019
//
// ---------------------------------------------------------------
console.error ("*** 開始 ***");
const { Client } = require('pg')
require('dotenv').config()
const client = new Client({
user: process.env.user,
host: 'localhost',
database: process.env.data_base,
password: process.env.password,
port: 5432,
})
client.connect()
const query = {
text: 'SELECT VERSION()',
}
client.query(query)
.then(res => {
row = res.rows[0]
console.log (row)
client.end()
console.error ("*** 終了 ***")
})
.catch(e => console.error(e.stack))
// ---------------------------------------------------------------
postgre_create.js
#! /usr/local/bin/node
// ---------------------------------------------------------------
// postgre_create.js
//
// Sep/12/2018
//
// ---------------------------------------------------------------
var pg = require('pg')
// ---------------------------------------------------------------
function dict_append_proc (dict_aa,id_in,name_in,population_in,date_mod_in)
{
unit_aa = {}
unit_aa['name'] = name_in
unit_aa['population'] = population_in
unit_aa['date_mod'] = date_mod_in
dict_aa[id_in] = unit_aa
return dict_aa
}
// ---------------------------------------------------------------
function data_prepare_proc ()
{
var dict_aa = new Object ()
dict_aa = dict_append_proc (dict_aa,'t3461','広島',79425,'1950-8-22')
dict_aa = dict_append_proc (dict_aa,'t3462','福山',82197,'1950-2-17')
dict_aa = dict_append_proc (dict_aa,'t3463','東広島',65241,'1950-10-2')
dict_aa = dict_append_proc (dict_aa,'t3464','呉',37864,'1950-6-22')
dict_aa = dict_append_proc (dict_aa,'t3465','尾道',61358,'1950-8-14')
dict_aa = dict_append_proc (dict_aa,'t3466','竹原',65281,'1950-9-12')
dict_aa = dict_append_proc (dict_aa,'t3467','三次',31256,'1950-3-21')
dict_aa = dict_append_proc (dict_aa,'t3468','大竹',52981,'1950-7-26')
dict_aa = dict_append_proc (dict_aa,'t3469','府中',84736,'1950-10-2')
return dict_aa
}
// ---------------------------------------------------------------
function data_insert_proc(dict_aa,client)
{
const length = Object.keys(dict_aa).length
console.error ("length = " + length)
var count = 0
for (var key in dict_aa)
{
var sql_str
= "insert into cities (id,name,population,date_mod) values ("
const str_data = "'" + key + "','" + dict_aa[key].name + "',"
+ dict_aa[key].population + ",'" + dict_aa[key].date_mod + "')"
sql_str += str_data
const query = { text: sql_str,}
client.query(query)
.then(res => {
count += 1
if (count == length)
{
client.end()
console.error ("*** 終了 ***")
}
})
.catch(e => console.error(e.stack))
}
}
// ---------------------------------------------------------------
console.error ("*** 開始 ***")
const dict_aa = data_prepare_proc ()
const { Client } = require('pg')
require('dotenv').config()
const client = new Client({
user: process.env.user,
host: 'localhost',
database: process.env.data_base,
password: process.env.password,
port: 5432,
})
client.connect()
const query = {
text: 'drop table cities',
}
client.query(query)
.then(res => {
console.error ("*** table is dropped ***")
var command = 'create table cities (id varchar(10), name varchar(20),'
command += ' population int, date_mod date)'
const query_b = {text: command,}
client.query(query_b)
.then(res => {
console.error ("*** table is created ***")
data_insert_proc(dict_aa,client)
})
.catch(e => console.error(e.stack))
})
.catch(e => console.error(e.stack))
// ---------------------------------------------------------------
postgre_read.js
#! /usr/local/bin/node
// ---------------------------------------------------------------
// postgre_read.js
//
// Sep/12/2018
//
// ---------------------------------------------------------------
console.error ("*** 開始 ***");
const { Client } = require('pg')
require('dotenv').config()
const client = new Client({
user: process.env.user,
host: 'localhost',
database: process.env.data_base,
password: process.env.password,
port: 5432,
})
client.connect()
const query = {
text: 'SELECT * FROM cities',
}
client.query(query)
.then(res => {
for (var it in res.rows)
{
row = res.rows[it]
var str_out = row.id + '\t'
str_out += row.name + '\t'
str_out += row.population + '\t'
str_out += row.date_mod
console.log (str_out)
}
client.end()
console.error ("*** 終了 ***")
})
.catch(e => console.error(e.stack))
// ---------------------------------------------------------------
postgre_update.js
#! /usr/local/bin/node
// ---------------------------------------------------------------
// postgre_update.js
//
// Sep/12/2018
//
// ---------------------------------------------------------------
var pg = require('pg')
// ---------------------------------------------------------------
function update_command_gen (id_in,population_in)
{
const today = get_current_date_proc()
var command = "update cities set population = " + population_in
command += " , date_mod = '" + today + "'"
command += " where id = '" + id_in + "'"
console.log (command)
return command
}
// ---------------------------------------------------------------
function get_current_date_proc ()
{
const today = new Date ()
var ddx = (1900 + today.getYear ()) + "-" + (today.getMonth () +1)
ddx += "-" + today.getDate ()
return ddx
}
// ---------------------------------------------------------------
console.error ("*** 開始 ***")
const id_in = process.argv[2]
const population_in = process.argv[3]
console.log (id_in + "\t" + population_in)
const { Client } = require('pg')
require('dotenv').config()
const client = new Client({
user: process.env.user,
host: 'localhost',
database: process.env.data_base,
password: process.env.password,
port: 5432,
})
client.connect()
const command = update_command_gen (id_in,population_in)
console.error (command)
const query = {
text: command,
}
client.query(query)
.then(res => {
client.end()
console.error ("*** 終了 ***")
})
.catch(e => console.error(e.stack))
// ---------------------------------------------------------------
postgre_delete.js
#! /usr/local/bin/node
// ---------------------------------------------------------------
// postgre_delete.js
//
// Sep/12/2018
//
// ---------------------------------------------------------------
var pg = require('pg');
// ---------------------------------------------------------------
function delete_command_gen (id_in)
{
const command = "delete from cities where id = '" + id_in + "'"
return command
}
// ---------------------------------------------------------------
console.error ("*** 開始 ***");
var id_in = process.argv[2];
console.error (id_in);
const { Client } = require('pg')
require('dotenv').config()
const client = new Client({
user: process.env.user,
host: 'localhost',
database: process.env.data_base,
password: process.env.password,
port: 5432,
})
client.connect()
const command = delete_command_gen (id_in);
console.error (command);
const query = {
text: command,
}
client.query(query)
.then(res => {
client.end()
console.error ("*** 終了 ***")
})
.catch(e => console.error(e.stack))
// ---------------------------------------------------------------
確認したバージョン
$ node --version
v20.1.0
参考情報
Ruby のサンプルです。
Ruby で PostgreSQL の CRUD
Python3 のサンプルです。
Python3 で PostgreSQL の CRUD