Makes As Many Connection As you wish to database(Lazey Connection) and provides good methods for select,create database, insert,…

project on github:

https://github.com/mehdi-sadighian/cpp-postgresql-Connection-Pool-And-Query-Suite

PostgreSQL_ConnectionPool_V2.h

#pragma once

#ifndef POSTGRESQL_CONNECTION_POOL_V2
#define POSTGRESQL_CONNECTION_POOL_V2 1

#define VERSION 2.1.12

#include <cstdio>
#include <unistd.h>
#include <string>
#include <iostream>
#include <pqxx/pqxx>
#include <condition_variable>
#include <queue>
#include <mutex>
#include <map>

using namespace std;
using namespace pqxx;


namespace PostgresqlConnectionPool
{
	class PGSQL_Settings
	{
	public:
		string db_name;
		string db_user;
		string db_password;
		string db_host;
		string db_port = "5432";

		//max connections
		int max_connections = 20;

		//column types
		bool GetColumnTypes = false;
	};


	class PGSQL_ConnectionPool
	{
	public:

		explicit PGSQL_ConnectionPool(const PGSQL_Settings& Settings) :
			Settings(Settings)
		{

		}//PGSQL_ConnectionPool

		~PGSQL_ConnectionPool()
		{
		}//~PGSQL_ConnectionPool

		bool Connect(string& Error);
		bool CreateDatabase(string dbname, string& Error);
		bool CustomQuery(string& query, string& Error);
		string InsertReturnID(string& query, string& Error);

	private:
		string ConnectionString;
		const PGSQL_Settings& Settings;

		//queue to store pgsql connections
		queue<std::shared_ptr<pqxx::lazyconnection>> conList;
		mutex conList_mutex;

		//condition variable to notify threads waiting that we have one connection free
		condition_variable pgsql_pool_condition;

		map <int, string> TypeNames;

	protected:

		bool SetupConnectionPool(string& Error);
		shared_ptr<pqxx::lazyconnection> ConnectionPickup();
		void ConnectionHandBack(std::shared_ptr<pqxx::lazyconnection> conn_);
		bool GetTypeOid();

	public:
		class Select
		{
			friend class PGSQL_ConnectionPool;

		public:
			bool Success = false;
                        string Error;
			struct ColumnInfo
			{
				string name;
				int type;
				string typeName;
			};

			typedef vector<string> Row;
			vector<ColumnInfo*> SelectedColumnsInfo;

		public:

			explicit Select(PGSQL_ConnectionPool* Parent, string& query) :
				Parent(Parent), query(query)
			{
				Success = SelectFromDB();
				if (Success && Parent->Settings.GetColumnTypes)
					ConvertSelectResultTypes();
			}

			~Select() { this->ClearResult(); }

			void ReSelect(string& query)
			{
				this->Clear();
				this->NumberOfSelectedRows = 0;
				this->NumberOfSelectedColumns = 0;
				this->Success = false;

				Success = SelectFromDB();
				if (Success && Parent->Settings.GetColumnTypes)
					ConvertSelectResultTypes();
			}

			void Clear() { this->ClearResult(); }

			inline int GetNumberOfSelectedRows() const { return this->NumberOfSelectedRows; }
			inline int GetNumberOfSelectedColumns() const { return this->NumberOfSelectedColumns; }

			inline Row& GetRow(int index) {
				if (index < NumberOfSelectedRows)
					return *SelectResult[index];
				else
				{
					Error = "Error, Request Index Is Out Of Range";
					cout << Error << endl;
				}
			};

		private:
			PGSQL_ConnectionPool* Parent;
			vector<Row*> SelectResult;

			string& query;
			bool SetColumnTypes;
			int NumberOfSelectedRows = 0;
			int NumberOfSelectedColumns = 0;

		private:
			bool SelectFromDB();
			void ClearResult();
			void ConvertSelectResultTypes();

		};//class Select
	};//class PGSQL_ConnectionPool


}//namespace PostgresqlConnectionPoolV2

#endif // !POSTGRESQL_CONNECTION_POOL_V2 1

PostgreSQL_ConnectionPool_V2.cpp

#include "PostgreSQL_ConnectionPool_V2.h"

namespace PostgresqlConnectionPool
{
	bool PGSQL_ConnectionPool::Connect(string& Error)
	{
		this->ConnectionString = "dbname= " + Settings.db_name;
		this->ConnectionString += " user=" + Settings.db_user;
		this->ConnectionString += " password=" + Settings.db_password;
		this->ConnectionString += " hostaddr=" + Settings.db_host;
		this->ConnectionString += " port=" + Settings.db_port;
		this->ConnectionString += " connect_timeout=6";

		return this->SetupConnectionPool(Error);
	}//connect


	bool PGSQL_ConnectionPool::SetupConnectionPool(string& Error)
	{
		//check if we can connect to database
		try
		{
			connection C(ConnectionString);

			if (C.is_open())
			{
				//close database connection
				C.disconnect();
			}
			else {
				Error = "Can't open database";
				return false;
			}//if
		}
		catch (const exception& e)
		{
			Error = string(e.what());
			return false;
		}//catch

		for (int i = 0; i < Settings.max_connections; i++)
		{
			//conList.emplace(std::make_shared<pqxx::lazyconnection>());
			try
			{
				conList.emplace(std::make_shared<pqxx::lazyconnection>(this->ConnectionString));
			}
			catch (const exception& e)
			{
				Error = string(e.what());
				return false;
			}

		}//for

		if (Settings.GetColumnTypes)
		{
			if (!GetTypeOid())
			{
				Error = "Cannot Get Type Oid";
				return false;
			}
		}

		return true;

	}//SetupConnectionPool

	bool PGSQL_ConnectionPool::GetTypeOid()
	{
		string query = "select oid, typname from pg_type;";
		PostgresqlConnectionPool::PGSQL_ConnectionPool::Select Select(this, query);

		if (!Select.Success)
			return false;

		for (auto& row : Select.SelectResult)
		{
			try
			{
				TypeNames.emplace(stoi((*row)[0]), (*row)[1]);
			}
			catch (exception& e)
			{
				cout << "GetTypeOid Exception:" << e.what() << endl;
				return false;
			}
		}

		return true;
	}//GetTypeOid

	shared_ptr<pqxx::lazyconnection> PGSQL_ConnectionPool::ConnectionPickup()
	{
		std::unique_lock<std::mutex> lock_(conList_mutex);

		//if pool is empty, we have no connection available, so we wait until a connection become available
		while (conList.empty()) {

			pgsql_pool_condition.wait(lock_);//wait for notfiy

		}//while

		//when program reachs this point, we have an available connection in pool

		//get first element (connection) in the queue
		auto conn_ = conList.front();

		//remove first element from queue
		conList.pop();
		return conn_;

	}//ConnectionPickup

	void PGSQL_ConnectionPool::ConnectionHandBack(std::shared_ptr<pqxx::lazyconnection> conn_)
	{
		std::unique_lock<std::mutex> lock_(conList_mutex);

		//hand back connection into pool
		conList.push(conn_);

		// unlock mutex
		lock_.unlock();

		// notify one of thread that is waiting
		pgsql_pool_condition.notify_one();

		return;
	}//ConnectionHandBack

	bool PGSQL_ConnectionPool::CreateDatabase(string dbname,string& Error)
	{
		const char* sql;
		
		string query = "CREATE DATABASE " + dbname + ";";
		sql = query.c_str();

		auto conn = ConnectionPickup();

		//if we have nullptr in conn
		if (!conn)
		{
			Error = "Can't Get a Connection To Database";
			return false;
		}

		try
		{
			// create a nontransaction from a connection
			pqxx::nontransaction W(reinterpret_cast<pqxx::lazyconnection&>(*conn.get()));
				
			try {
				/* Execute SQL query */
				W.exec(sql);
				W.commit();
			}//try
			catch (const exception& e) {
				Error = "CREATE DATABASE:" + string(e.what());
				Error += "Query Was:" + query;
				
				W.abort();
				ConnectionHandBack(conn);
				return false;
			}//catch

		}//try
		catch (const exception& e) {
			Error = "Work Create:" + string(e.what());
			ConnectionHandBack(conn);
			return false;
		}//catch
	
		ConnectionHandBack(conn);
		return true;

	}//CreateDatabase


	bool PGSQL_ConnectionPool::CustomQuery(string& query,string& Error)
	{
		const char* sql;
		bool success = false;
		sql = query.c_str();

		auto conn = ConnectionPickup();

		//if we have nullptr in conn
		if (!conn)
		{
			Error = "Can't Get a Connection To Database";
			return false;
		}

		try
		{
			// create a transaction from a connection
			work W(reinterpret_cast<pqxx::lazyconnection&>(*conn.get()));

			try {

				/* Execute SQL query */
				W.exec(sql);
				W.commit();
			}//try
			catch (const std::exception& e) {
				Error =  "Custom Query ERROR:" + string(e.what());
				W.abort();
				ConnectionHandBack(conn);
				return false;
			}//catch

		}//try
		catch (const exception& e) {
			Error = "Work Create:" + string(e.what());
			ConnectionHandBack(conn);
			return false;
		}//catch

		//hand back pgsql_connection
		ConnectionHandBack(conn);

		return true;

	}//CustomQuery

	string PGSQL_ConnectionPool::InsertReturnID(string& query, string& Error)
	{
		string id = "-1";

		const char* sql;
		
		sql = query.c_str();

		auto conn = ConnectionPickup();

		//if we have nullptr in conn
		if (!conn)
		{
			Error = "Can't Get a Connection To Database";
			return id;
		}

		try
		{
			// create a transaction from a connection
			work W(reinterpret_cast<pqxx::lazyconnection&>(*conn.get()));

			try {

				/* Execute SQL query */
				result R(W.exec(sql));
				result::const_iterator c = R.begin();
				
				if (c[0].is_null())
				{
					ConnectionHandBack(conn);
					return id;
				}
				else
				{
					id = c[0].as<string>();
					W.commit();
				}
			}//try
			catch (const std::exception& e) {
				Error = "InsertReturnID ERROR:" + string(e.what());
				W.abort();
				ConnectionHandBack(conn);
				return "-1";
			}//catch

		}//try
		catch (const std::exception& e) {
			Error = "Work Create:" + string(e.what());
			ConnectionHandBack(conn);
			return "-1";
		}//catch

		//hand back pgsql_connection
		ConnectionHandBack(conn);

		return id;
	}//InsertReturnID



	//select
	
	void PGSQL_ConnectionPool::Select::ClearResult()
	{
		if (!SelectResult.empty())
		{
			for (int i = 0; i < SelectResult.size(); i++)
				delete (SelectResult[i]);

			SelectResult.clear();
		}
		

		if (!SelectedColumnsInfo.empty())
		{
			for (int i = 0; i < SelectedColumnsInfo.size(); i++)
				delete (SelectedColumnsInfo[i]);

			SelectedColumnsInfo.clear();
		}
			

	}//ClearResult

	void PGSQL_ConnectionPool::Select::ConvertSelectResultTypes()
	{
		map<int, string>::iterator IT;

		for (int i = 0; i < SelectedColumnsInfo.size(); i++)
		{
			auto& node = SelectedColumnsInfo[i];

			IT = Parent->TypeNames.find(node->type);

			if (IT != Parent->TypeNames.end())
				node->typeName = IT->second;
		}

	}//ConvertSelectResultTypes

	bool PGSQL_ConnectionPool::Select::SelectFromDB()
	{
		ClearResult();

		const char* sql;
		sql = query.c_str();

		auto conn = Parent->ConnectionPickup();

		//if we have nullptr in conn
		if (!conn)
		{
			Error = "Can't Get a Connection To Database";
			return false;
		}

		try {

			/* Create a non-transactional object. */
			work N(reinterpret_cast<pqxx::lazyconnection&>(*conn.get()));

			/* Execute SQL query */
			result R(N.exec(sql));

			NumberOfSelectedRows = R.affected_rows();
			
			if (NumberOfSelectedRows <= 0)
			{
				N.abort();
				R.clear();
				Parent->ConnectionHandBack(conn);
				return true;
			}

			SelectResult.resize(NumberOfSelectedRows);

			int j = 0;

			for (result::const_iterator c = R.begin(); c != R.end(); ++c)
			{
				Row* RW = new Row(c.size());

				for (int i = 0; i < c.size(); i++)
				{
					if (!c[i].is_null())
					{
						(*RW)[i] = move(c[i].as<string>());
						
					/*	string temp = c[i].as<string>();
						std::cout << R.column_name(i) << '\n';
						std::cout << R.column_type(i) << '\n';
						cout << temp << endl;*/

					}
				}//for

				SelectResult[j] = RW;
				j++;
			}

			NumberOfSelectedColumns = R.columns();

			SelectedColumnsInfo.resize(NumberOfSelectedColumns);

			for (int col = 0; col < NumberOfSelectedColumns; ++col)
			{
				ColumnInfo* CI = new ColumnInfo();
				CI->name = R.column_name(col);
				CI->type = R.column_type(col);

				SelectedColumnsInfo[col] = CI;
				/*std::cout << R.column_name(col) << '\n';
				std::cout << R.column_type(col) << '\n';*/
			}

			N.abort();
			R.clear();
		}
		catch (const std::exception& e) {
			Error = "Select Error :" + string(e.what());
			Parent->ConnectionHandBack(conn);
			return false;
		}
		
		Parent->ConnectionHandBack(conn);
		return true;

	}//SelectFromDB


}//namespace PostgresqlConnectionPool

examples:

main.cpp

#include <cstdio>
#include <unistd.h>
#include <string>
#include <iostream>

#include "PostgreSQL_ConnectionPool_V2/PostgreSQL_ConnectionPool_V2.h"

using namespace std;
using PGSQL_Settings = PostgresqlConnectionPool::PGSQL_Settings;
using PGSQL_ConnectionPool = PostgresqlConnectionPool::PGSQL_ConnectionPool;
using Select = PostgresqlConnectionPool::PGSQL_ConnectionPool::Select;

int main()
{
    PGSQL_Settings PGSettings;
    PGSettings.db_host = "127.0.0.1";
    PGSettings.db_port = "5432";
    PGSettings.db_name = "test";
    PGSettings.db_user = "root";
    PGSettings.db_password = "testpassword";
    PGSettings.max_connections = 20;
    PGSettings.GetColumnTypes = true;


    PGSQL_ConnectionPool PgsqlConnection(PGSettings);

    string Error;
    if (!PgsqlConnection.Connect(Error))
    {
        cout << Error << endl;
        cout << "Cannot Connect To Database...Exiting" << endl;
        exit(1);
    }

    //Create DataBase
    if (!PgsqlConnection.CreateDatabase("test11", Error))
        cout << Error << endl;
    else
        cout << "Database Creation Success" << endl;

    //custom query can be used for create table, insert, delete,update,.... BUT NOT SELECT
    //for select use select object

    string query;

    //create table example
    query = "create table t7 (id serial PRIMARY KEY NOT NULL UNIQUE,familyname varchar(256) );";
    if (!PgsqlConnection.CustomQuery(query, Error))
        cout << Error << endl;
    else
        cout << "CustomQuery Success" << endl;


    //insert example
    query = "insert into t7 (familyname) VALUES (\'mehdi.sadighian@hotmail.com\');";
    if (!PgsqlConnection.CustomQuery(query, Error))
        cout << Error << endl;
    else
        cout << "CustomQuery Success" << endl;

    //insert retunring id example:
    query = "insert into t7 (familyname) VALUES (\'mehdi.sadighian@hotmail.com\') RETURNING id;";
    string id = PgsqlConnection.InsertReturnID(query, Error);

    if (id == "-1")
        cout << Error << endl;
    else
        cout << "insert retunring id Success, id=" << id << endl;


    //Select Example:

    query = "select id,familyname from t7;";

    //Creare Object, This Will Dispose And Clear itself when goes out of scope
    Select PGSelect(&PgsqlConnection, query);

    if (PGSelect.Success)
    {
        cout << "####\nprint select result example 1:" << endl;
        for (int i = 0; i < PGSelect.GetNumberOfSelectedRows(); i++)
        {
            Select::Row& row = PGSelect.GetRow(i);
            string& column1 = row[0];
            string& column2 = row[1];
            cout << column1 << endl;
            cout << column2 << endl;
        }

        cout << "####\nprint select result example 2:" << endl;

        for (int i = 0; i < PGSelect.GetNumberOfSelectedRows(); i++)
        {
            for (auto& column : PGSelect.GetRow(i))
            {
                cout << "column value=" << column << endl;
            }
        }

        cout << "####\nprint only selected column DataType:" << endl;
        for (int i = 0; i < PGSelect.SelectedColumnsInfo.size(); i++)
        {
            auto& node = PGSelect.SelectedColumnsInfo[i];
            cout << node->name << endl;
            cout << node->type << endl;
            cout << node->typeName << endl;
        }

        cout << "####\nprint select result and column DataTypes example:" << endl;

        for (int i = 0; i < PGSelect.GetNumberOfSelectedRows(); i++)
        {
            Select::Row& row = PGSelect.GetRow(i);

            auto& node1 = PGSelect.SelectedColumnsInfo[0];
            string& column1 = row[0];

            auto& node2 = PGSelect.SelectedColumnsInfo[1];
            string& column2 = row[1];

            cout << "####" << endl;
            cout << "column1 name:" << node1->name << endl;
            cout << "column1 value:" << column1 << endl;
            cout << "column1 type:" << node1->type << endl;
            cout << "column1 typeName:" << node1->typeName << endl;

            cout << "####" << endl;
            cout << "column2 name:" << node2->name << endl;
            cout << "column2 value:" << column2 << endl;
            cout << "column2 type:" << node2->type << endl;
            cout << "column2 typeName:" << node2->typeName << endl;

        }
       
    }

    //Reselect Using Same Object
    //this Will Clear Select Result in Object And Puts New Select Results Into it

    query = "select familyname from t7;";

    PGSelect.ReSelect(query);

    if (PGSelect.Success)
    {
        cout << "####\nprint ReSelect result example:" << endl;

        for (int i = 0; i < PGSelect.GetNumberOfSelectedRows(); i++)
        {
            for (auto& column : PGSelect.GetRow(i))
            {
                cout << "column value=" << column << endl;
            }
        }
    }

}//main

compile with:

g++ main.cpp -lpthread -lpq -lpqxx