// Your node server can interact with a PostgreSQL server via
    // the node-postgres libraries.

    // We assume you'll be running this code on a platform running a
    // PostgreSQL server that you can access.

    // Visit PostgreSQLTutorial.com for more help.

    
// The Client and Pool objects both allow you to connect to the server.
// A Client connection can execute transactions while Pools will reuse
// connections and speed things up.
const {Client} = require('pg');
const {Pool} = require('pg');

// We'll use a Pool object and connect via a UNIX socket.
const pg = new Pool({host:'/var/run/postgresql'});

// get the software version
pg.query('select version()')
  .then(result => console.log(result.rows[0].now))
  .catch(error => console.error(error.hint || error.stack))

// get the time
pg.query('select now()')
  .then(result => console.log(result.rows[0].now))
  .catch(error => console.error(error.hint || error.stack))

// an error
pg.query('select error')
  .then(result => console.log(result.rows[0].now))
  .catch(error => console.error(error.hint || error.stack))

// show databases
pg.query('SELECT datname FROM pg_database')
  .then(dbs => console.log(dbs.rows))
  .catch(error => console.error(error.hint || error.stack))

// create database
pg.query('create database dummy')
  .then(result => console.log(result))
  .catch(error => console.error(error.hint || error.stack))

// connect to database
const dummy = new Pool({host:'/var/run/postgresql', database:'dummy'});

// show tables
dummy.query("SELECT tablename FROM pg_catalog.pg_tables WHERE schemaname not in ('pg_catalog', 'information_schema') ")
  .then(tables => console.log(tables.rows))
  .catch(error => console.error(error.hint || error.stack))

// create table (data types)
dummy.query(`create table if not exists dummy (
               id serial primary key,
               name text,
               age smallint) `)
  .catch(error => console.error(error.hint || error.stack))

// insert into table
dummy.query("insert into dummy (name,age) values ('jon', 10)")
  .catch(error => console.error(error.hint || error.stack))

dummy.query("insert into dummy (name,age) values ('jan', 20) returning id")
  .then(result => console.log(result.rows[0].id))
  .catch(error => console.error(error.hint || error.stack))

// insert with parameters
dummy.query("insert into dummy (name,age) values ($1, $2)", ['jim', 30])
  .catch(error => console.error(error.hint || error.stack))

// select from table
dummy.query('select * from dummy')
  .then(result => console.log(result.rows))
  .catch(error => console.error(error.hint || error.stack))

// update table
dummy.query("update dummy set age=100 where name='jim'")
  .catch(error => console.error(error.hint || error.stack))

// delete from table
dummy.query("delete from dummy where name='jim'")
  .catch(error => console.error(error.hint || error.stack))

dummy.query("delete from dummy") // delete all records
  .catch(error => console.error(error.hint || error.stack))


// alter table

// delete table
dummy.query('drop table if exists dummy2')
  .catch(error => console.error(error.hint || error.stack))

// release all connections
pg.end(() => console.log('pg pool has ended'))
dummy.end(() => console.log('dummy pool has ended'))


// https://sean.brunnock.com  1/2025