More than 1 year has passed since last update.

Node.js で PostgreSQL の CRUD

Last updated at 2023-05-06Posted at 2018-09-12

接続情報

.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　のバージョンを調べる方法です。

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))

// ---------------------------------------------------------------

Create

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))

// ---------------------------------------------------------------

Read

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))

// ---------------------------------------------------------------

Update

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))

// ---------------------------------------------------------------

Delete

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

You get articles that match your needs
You can efficiently read back useful information
You can use dark theme

What you can do with signing up