Manipulate PostgreSQL (Connect/Create Table/Insert/Select/Drop/Transaction)

Prerequisites

Assuming you prepared .env file like

DBHOST=0.0.0.0
DBUSER=exampleuser
DBPASSWORD=example20141024

for connection to PostgreSQL server

Then prepare database and a user using following command.

CREATE USER exampleuser PASSWORD 'example20141024';
CREATE DATABASE example;
GRANT ALL ON DATABASE example TO exampleuser;
CREATE TYPE sex_example AS ENUM ('male', 'female');

Expected result

expected result is following one(regex expression)

r"1\tname1\tmale\t\d+/\d+/\d+ \d+:\d+:\d+\t\d+/\d+/\d+ \d+:\d+:\d+\n2\tname2\tfemale\t\d+/\d+/\d+ \d+:\d+:\d+\t\d+/\d+/\d+ \d+:\d+:\d+$"

example

1	name1	male	2019/10/29 18:04:45	2019/10/29 18:04:45
2	name2	female	2019/10/29 18:04:45	2019/10/29 18:04:45

JavaScript

class PsqlManipulator {
	constructor(opt={}) {
    const { Pool, Client } = require('pg');
    require('dotenv').config();
    this.rdbh = new Client({
      host: process.env.DBHOST,
      user: process.env.DBUSER,
      password: process.env.DBPASSWORD,
      database: 'example'
    })
  }

  async beginTransaction() {
    const sql = "BEGIN"
    await this.executeSQL(sql)
  }

  async createExampleData() {
    const datas = [
      ['name1', 'male'],
      ['name2', 'female']
    ];
    for (const data of datas) {
      const query = {
        text: 'INSERT INTO example (name, sex) VALUES ($1, $2)',
        values: [data[0], data[1]]
      }
      await this.executeSQL(query);
    }
  }

  async createExampleTable() {
    const sql = `
    CREATE TABLE example (
      id SERIAL NOT NULL,
      name VARCHAR(255) NOT NULL,
      sex example_sex DEFAULT NULL,
      created_at timestamp with time zone DEFAULT NOW(),
      updated_at timestamp with time zone DEFAULT NOW(),
      PRIMARY KEY (id)
    );
    CREATE INDEX created_at_idx ON example(created_at);
    CREATE INDEX name_idx ON example(name);
    CREATE INDEX updated_at_idx ON example(updated_at);
    `;
    await this.executeSQL(sql);
  }

  async createExampleType() {
    const sql = "CREATE TYPE example_sex AS ENUM ('male', 'female')";
    await this.executeSQL(sql);
  }

	async dbclose() {
    if (typeof(this.rdbh) !== "undefined") {
      await this.rdbh.end();
    }
  }

	async dbconnect() {
    await this.rdbh.connect();
    const timezone = "Asia/Tokyo";
    const sql = "SET TIME ZONE '"+ timezone +"'"
    await this.executeSQL(sql)
  }

  async dropExampleTable() {
    const sql = "DROP TABLE IF EXISTS example";
    await this.executeSQL(sql);
  }

  async dropExampleType() {
    const sql = "DROP TYPE IF EXISTS example_sex";
    await this.executeSQL(sql);
  }

  async endTransaction() {
    const sql = "END"
    await this.executeSQL(sql)
  }

  async executeSQL(sql) {
    try {
      const res = await this.rdbh.query(sql);
    }
    catch(err){
      const sql = "ROLLBACK"
      await this.executeSQL(sql)
      console.log(err.name + ': ' + err.message);
    }
  }

  async selectFromExampleTable() {
    const sql = "SELECT id, name, sex, to_char(created_at, 'YYYY/MM/DD HH24:MI:SS') AS created_at, to_char(updated_at, 'YYYY/MM/DD HH24:MI:SS') AS updated_at FROM example";
    try {
      const res = await this.rdbh.query(sql)
      for(const row of res.rows){
        console.log([row.id, row.name, row.sex, row.created_at, row.updated_at].join("\t"))
      }
    }
    catch(err) {
      const sql = "ROLLBACK"
      await this.executeSQL(sql)
      console.log(err.name + ': ' + err.message)
      process.exit(-1)
    }
  }
};
module.exports = PsqlManipulator;

if(!module.parent) {
  (async () => {
    const rdb = new PsqlManipulator()
    await rdb.dbconnect()
    await rdb.beginTransaction()
    await rdb.dropExampleTable()
    await rdb.dropExampleType()
    await rdb.createExampleType()
    await rdb.createExampleTable()
    await rdb.createExampleData()
    await rdb.selectFromExampleTable()
    await rdb.dropExampleTable()
    await rdb.dropExampleType()
    await rdb.endTransaction()
    await rdb.dbclose()
  })()
}

Perl

package PsqlManipulator;
use strict;
use DBI;
use Dotenv;

sub new() {
  my $self = {};
  Dotenv->load();
  return bless $self;
}

sub beginTransaction(){
  my $self = shift;
  my $sql = "BEGIN";
  $self->executeSQL($sql);
}

sub createExampleData(){
  my $self = shift;
  my @datas = (
    ['name1', 'male'],
    ['name2', 'female']
  );
  my $sth = $self->{'rdbh'}->prepare("INSERT INTO example (name, sex) VALUES (?, ?)");
  foreach my $data_ref (@datas) {
    $sth->execute($data_ref->[0], $data_ref->[1]);
  }
  $sth->finish();
}

sub createExampleTable() {
  my $self = shift;
  my $sql = <<EOF;
CREATE TABLE example (
  id SERIAL NOT NULL,
  name VARCHAR(255) NOT NULL,
  sex example_sex DEFAULT NULL,
  created_at timestamp with time zone DEFAULT NOW(),
  updated_at timestamp with time zone DEFAULT NOW(),
  PRIMARY KEY (id)
);
CREATE INDEX created_at_idx ON example(created_at);
CREATE INDEX name_idx ON example(name);
CREATE INDEX updated_at_idx ON example(updated_at);
EOF
  $self->executeSQL($sql);
}

sub createExampleType() {
  my $self = shift;
  my $sql = "CREATE TYPE example_sex AS ENUM ('male', 'female')";
  $self->executeSQL($sql);
}

sub dbclose(){
  my $self = shift;
  $self->{'rdbh'}->disconnect();
}

sub dbconnect(){
  my $self = shift;
  my $database = "example";
  my $timezone = "Asia/Tokyo";
  $self->{'rdbh'} = DBI->connect("DBI:Pg:database=".$database.";host=".$ENV{'DBHOST'}, $ENV{'DBUSER'}, $ENV{'DBPASSWORD'}, {
   AutoCommit => 0,
   PrintError => 1,
   PrintWarn => 0
   }) or die "Failed to connect to db.";
  my $sql = "SET TIME ZONE '".$timezone."'";
  $self->{'rdbh'}->do($sql);
}

sub dropExampleTable(){
  my $self = shift;
  my $sql = "DROP TABLE IF EXISTS example";
  $self->executeSQL($sql);
}

sub dropExampleType(){
  my $self = shift;
  my $sql = "DROP TYPE IF EXISTS example_sex";
  $self->executeSQL($sql);
}

sub endTransaction(){
  my $self = shift;
  $self->{'rdbh'}->commit();
}

sub executeSQL(){
  my $self = shift;
  my $sql = shift;
  $self->{'rdbh'}->do($sql) or die("Failed to execute ".$sql);
}

sub selectFromExampleTable(){
  my $self = shift;
  my $sql = "SELECT id, name, sex, to_char(created_at, 'YYYY/MM/DD HH24:MI:SS') AS created_at, to_char(updated_at, 'YYYY/MM/DD HH24:MI:SS') AS updated_at FROM example";
  my $sth = $self->{'rdbh'}->prepare($sql) or die("Failed to prepare ".$sql);
  $sth->execute() or die("Failed to execute ".$sql);
  while(my $row_ref = $sth->fetchrow_hashref) {
    print(join("\t", $row_ref->{'id'}, $row_ref->{'name'}, $row_ref->{'sex'}, $row_ref->{'created_at'}, $row_ref->{'updated_at'})."\n");
  }
  $sth->finish();
}

if ($0 eq __FILE__) {
  my $rdb = new PsqlManipulator();
  $rdb->dbconnect();
  $rdb->beginTransaction();
  $rdb->dropExampleTable();
  $rdb->dropExampleType();
  $rdb->createExampleType();
  $rdb->createExampleTable();
  $rdb->createExampleData();
  $rdb->selectFromExampleTable();
  $rdb->dropExampleTable();
  $rdb->dropExampleType();
  $rdb->endTransaction();
  $rdb->dbclose();
}
else{
  1;
}

PHP

<?php
require_once __DIR__ . '/vendor/autoload.php';

class PsqlManipulator {
  private $rdbh;
  public function __construct($opt=[]) {
    $dotenv = Dotenv\Dotenv::create(__DIR__);
    $dotenv->load();
  }

  function beginTransaction() {
    $this->rdbh->beginTransaction();
  }

  function createExampleData(){
    $datas = [
      ['name1', 'male'],
      ['name2', 'female']
    ];
    $sql = "INSERT INTO example (name, sex) VALUES (?, ?)";
    $sth = $this->rdbh->prepare($sql);
    foreach($datas as $data) {
      $sth->execute([$data[0], $data[1]]);
    }
  }

  function createExampleTable(){
    $sql =  <<< EOF
CREATE TABLE example (
  id SERIAL NOT NULL,
  name VARCHAR(255) NOT NULL,
  sex example_sex DEFAULT NULL,
  created_at timestamp with time zone DEFAULT NOW(),
  updated_at timestamp with time zone DEFAULT NOW(),
  PRIMARY KEY (id)
);
CREATE INDEX created_at_idx ON example(created_at);
CREATE INDEX name_idx ON example(name);
CREATE INDEX updated_at_idx ON example(updated_at);
EOF;
    $this->executeSQL($sql);
  }

  function createExampleType(){
    $sql = "CREATE TYPE example_sex AS ENUM ('male', 'female')";
    $this->executeSQL($sql);
  }

  function dbclose() {
    $this->rdbh = null;
  }
  
  function dbconnect(){
    $dbname = "example";
    $timezone = "Asia/Tokyo";
    try {
      $this->rdbh = new PDO('pgsql:dbname=example;host='.getenv('DBHOST'), getenv('DBUSER'), getenv('DBPASSWORD'));
      $this->rdbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    }
    catch (PDOException $e) {
      die("Failed to connect: ".$e->getMessage());
    }
    $sql = "SET TIME ZONE '".$timezone."'";
    $this->executeSQL($sql);
  }

  function dropExampleTable() {
    $sql = "DROP TABLE IF EXISTS example";
    $this->executeSQL($sql);
  }

  function dropExampleType(){
    $sql = "DROP TYPE IF EXISTS example_sex";
    $this->executeSQL($sql);
  }

  function endTransaction(){
    $this->rdbh->commit();
  }

  function executeSQL($sql) {
    $sqls = explode(';', $sql);
    foreach ($sqls as $sql) {
      if($sql) {
        $sth = $this->rdbh->prepare($sql);
        $sth->execute();
      }
    }
  }
  
  function selectFromExampleTable(){
    $sql = "SELECT id, name, sex, to_char(created_at, 'YYYY/MM/DD HH24:MI:SS') AS created_at, to_char(updated_at, 'YYYY/MM/DD HH24:MI:SS') AS updated_at FROM example";
    $sth = $this->rdbh->prepare($sql);
    $sth->execute();
    while($row = $sth->fetch(PDO::FETCH_ASSOC)){
      print(implode("\t", [$row['id'], $row['name'], $row['sex'], $row['created_at'], $row['updated_at']])."\n");
    }
  }
}

if ( !isset(debug_backtrace()[0]) ) {
  $rdb = new PsqlManipulator();
  try{
    $rdb->dbconnect();
  } catch(PDOException $e){
    echo $e->getMessage();
    exit(1);
  }

  try{
    $rdb->beginTransaction();
  } catch(PDOException $e){
    echo $e->getMessage();
    $rdb->dbclose();
    exit(1);
  }

  try {
    $rdb->dropExampleTable();
    $rdb->dropExampleType();
    $rdb->createExampleType();
    $rdb->createExampleTable();
    $rdb->createExampleData();
    $rdb->selectFromExampleTable();
    $rdb->dropExampleTable();
    $rdb->dropExampleType();
    $rdb->endTransaction();
    $rdb->dbclose();
  } catch(PDOException $e){
    $rdb->rollback();
    $rdb->dbclose();
    throw $e;
  }
}

Python

from firstclass_dotenv import Dotenv
import psycopg2
import os

class PsqlManipulator():
  def __init__(self, opt=[]):
    dotenv = Dotenv()
    dotenv.load()

  def beginTransaction(self):
    sql = "BEGIN"
    self.executeSQL(sql)

  def createExampleType(self):
    sql = "CREATE TYPE example_sex AS ENUM ('male', 'female')"
    self.executeSQL(sql)

  def createExampleTable(self):
    sql = '''\
CREATE TABLE example (
  id SERIAL NOT NULL,
  name VARCHAR(255) NOT NULL,
  sex example_sex DEFAULT NULL,
  created_at timestamp with time zone DEFAULT NOW(),
  updated_at timestamp with time zone DEFAULT NOW(),
  PRIMARY KEY (id)
);
CREATE INDEX created_at_idx ON example(created_at);
CREATE INDEX name_idx ON example(name);
CREATE INDEX updated_at_idx ON example(updated_at);
'''
    self.executeSQL(sql)

  def createExampleData(self):
    datas = [
      ['name1', 'male'],
      ['name2', 'female']
    ]
    with self.rdbh.cursor() as cur:
      for data in datas:
        cur.execute("INSERT INTO example (name, sex) VALUES (%s, %s)", [data[0], data[1]])

  def dbclose(self):
    self.rdbh.close()

  def dbconnect(self):
    dbname = "example"
    timezone = "Asia/Tokyo"
    self.rdbh = psycopg2.connect("host=" + os.environ['DBHOST'] +" dbname=" + dbname + " user="+ os.environ['DBUSER'] + " password=" + os.environ['DBPASSWORD'])
    self.executeSQL("SET TIME ZONE '"+ timezone + "'")

  def dropExampleTable(self):
    sql = "DROP TABLE IF EXISTS example"
    self.executeSQL(sql)

  def dropExampleType(self):
    sql = "DROP TYPE IF EXISTS example_sex"
    self.executeSQL(sql)

  def endTransaction(self):
    self.rdbh.commit()

  def executeSQL(self, sql):
    with self.rdbh.cursor() as cur:
      cur.execute(sql)

  def rollback(self):
    self.rdbh.rollback()

  def selectFromExampleTable(self):
    sql = "SELECT id, name, sex, to_char(created_at, 'YYYY/MM/DD HH24:MI:SS') AS created_at, to_char(updated_at, 'YYYY/MM/DD HH24:MI:SS') AS updated_at FROM example"
    with self.rdbh.cursor() as cur:
      cur.execute(sql)
      for [id, name, sex, created_at, updated_at] in cur:
        print("\t".join([str(id), name, sex, str(created_at), str(updated_at)]))

if __name__ == "__main__":
  psql = PsqlManipulator()
  psql.dbconnect()
  try:
    psql.beginTransaction()
    try:
      psql.dropExampleTable()
      psql.dropExampleType()
      psql.createExampleType()
      psql.createExampleTable()
      psql.createExampleData()
      psql.selectFromExampleTable()
      psql.dropExampleTable()
      psql.dropExampleType()
      psql.endTransaction()
    except Exception as e:
      psql.rollback()
      print(e)
    finally:
      psql.dbclose()
  except Exception as e:
    print(e)
  finally:
    psql.dbclose()

Ruby

require 'dotenv'
require 'pg'

class PsqlManipulator
  def initialize(opt=[])
    Dotenv.load(
      File.join(File.dirname(File.expand_path(__FILE__)), '.env')
    )
  end

  def dbconnect
    dbname = "example"
    timezone = "Asia/Tokyo"
    @rdbh = PG::connect(:host => ENV["DBHOST"], :user => ENV["DBUSER"], :password => ENV["DBPASSWORD"], :dbname => dbname)
    sql = "SET client_min_messages = 'error'"
    @rdbh.query(sql)
    sql = "SET TIME ZONE '"+ timezone + "'"
    @rdbh.query(sql)
  end

  def beginTransaction
    sql = "BEGIN"
    executeSQL(sql)
  end

  def createExampleType
    sql = "CREATE TYPE example_sex AS ENUM ('male', 'female')"
    executeSQL(sql)
  end

  def createExampleTable
    sql = '''
CREATE TABLE example (
  id SERIAL NOT NULL,
  name VARCHAR(255) NOT NULL,
  sex example_sex DEFAULT NULL,
  created_at timestamp with time zone DEFAULT NOW(),
  updated_at timestamp with time zone DEFAULT NOW(),
  PRIMARY KEY (id)
);
CREATE INDEX created_at_idx ON example(created_at);
CREATE INDEX name_idx ON example(name);
CREATE INDEX updated_at_idx ON example(updated_at);
'''
    executeSQL(sql)
  end

  def createExampleData
    datas = [
      ['name1', 'male'],
      ['name2', 'female']
    ]
    datas.each do |data|
      @rdbh.exec_params("INSERT INTO example (name, sex) VALUES ($1, $2)", [data[0], data[1]])
    end
  end

  def dbclose
    @rdbh.close()
  end

  def dropExampleTable
    sql = "DROP TABLE IF EXISTS example"
    executeSQL(sql)
  end

  def dropExampleType
    sql = "DROP TYPE IF EXISTS example_sex"
    executeSQL(sql)
  end

  def endTransaction
    sql = "END"
    @rdbh.query(sql)
  end

  def executeSQL(sql)
    @rdbh.query(sql)
  end

  def selectFromExampleTable
    sql = "SELECT id, name, sex, to_char(created_at, 'YYYY/MM/DD HH24:MI:SS') AS created_at, to_char(updated_at, 'YYYY/MM/DD HH24:MI:SS') AS updated_at FROM example"
    results = @rdbh.query(sql)
    results.each do |row|
      print([row["id"], row['name'], row['sex'], row['created_at'], row['updated_at']].join("\t") + "\n")
    end
  end
end

if $0 == __FILE__
  rdb = PsqlManipulator.new()
  begin
    rdb.dbconnect()
  rescue => err
    print(err)
    exit(1)
  end

  begin
    rdb.beginTransaction()
    begin
      rdb.dropExampleTable()
      rdb.dropExampleType()
      rdb.createExampleType()
      rdb.createExampleTable()
      rdb.createExampleData()
      rdb.selectFromExampleTable()
      rdb.dropExampleTable()
      rdb.dropExampleType()
      rdb.endTransaction()
    rescue => err
      puts("Automatically roll backed.")
      print(err)
      rdb.dbclose()
      exit(1)
    end
  rescue => err
    rdb.dbclose()
    print(err)
    exit(1)
  end
end