PHP Data Services with PostgreSQL

LAMP (Linux + Apache + Mysql + PHP) stack powers many servers in the Internet today. For a LAMP  server, PostgreSQL could be the first alternative to Mysql. Similar to PHP + MySQL,  PHP + PostgreSQL too can be easily used in to host data services. Here are the steps to do it.

  1. If you already don’t have Apache + PHP + PostgreSQL download them from the following locations. Apache –, PHP – and PostgreSQL –
  2. You have to enable the PHP pdo_pgsql, pdo and pgsql plugins. Read here for the instructions to setup these libraries. (For an example: if you are windows you have to set the system ‘PATH’ variable to the <postgresql_installed_dir>/bin directory.
  3. If you already don’t have WSF/PHP, download and install it according to the guidelines provided in wsf/php installation guide.NOTE: You can check pdo_pgsql and wsf/php has properly installed with the help of phpinfo() function.
  4. Now lets start with creating a sample Database table. For this example I created a database called ‘workshop’, schema called ‘workshop’ and inside there the table ‘Employee’ with the following schema.
    Column Name Column Type
    employId integer
    name character varying
    email character varying
    jobTitle character varying
    project character varying

    Note: You can use phpPgAdmin (web based) or pgAdmin III to create tables from GUI

  5. Then you can write a small php script to expose the data in the above table as a web service.
    //Including the Data Services library
    // Including the connection information (i.e. PGSQUL USERNAME
    // and PGSQL_PASSWORD) for my PGSQL Connection
    // database configurations
    $config = array(
    		"db" => "pgsql",
    		"username" => PGSQL_USERNAME,
    		"password" => PGSQL_PASSWORD,
    		"dbname" => "workshop",
    		"dbhost" => "localhost"
    $output_format = array(
                        "resultElement" => "employees",
                        "rowElement" => "employee",
                        "elements" => array(
    			    "id" => "employeeId",
                                "name" => "name",
                                "email" => "email",
                                "jobTitle" => "jobTitle",
                                "project" => "project"));
    $sql = "SELECT * FROM workshop.Employees";
    $get_employees_op = array("outputFormat" => $output_format, "sql" => $sql);
    $get_employees_url = array("HTTPMethod" => "GET", "RESTLocation" => "employees");
    // list of operations
    $operations = array(
                    "getEmployees" => $get_employees_op,
    // list of rest url mappping (operation => url)
    $restmap = array(
                    "getEmployees" => $get_employees_url,
    // creating DSService and reply
    $service = new DataService(array(
             "config" => $config,
             "operations" => $operations, "RESTMapping"=>$restmap));
  6. We just wrote a PostgreSQL Data Services that provides its service as both REST and SOAP form. To deploy this service, We just need to copy this in to the web root directory. And the web URL for the script will be the endpoint to the web service.
  7. We can test the service either by calling its SOAP interface, which we may need to write a small SOAP client or by calling its REST interface, which only need a GET request from the browser. Say my script name is “my_dataservice.php” and I’ve put it in the web root directory, then the URL to call the REST interface of the service is

WSDL Generation for PostgreSQL Data Service
You can get the WSDL for the service from the URL formed adding the suffix “?wsdl” (or “?wsdl2” to wsdl v2.0) to the service URL,


Here all the schema data types are shown as xsd:anyType, which may not be the behavior that you want. In fact you can provide the schema data types to the fields from the code itself. Lets change the $outputFormat variable to provide the schema information as well using the following code snip.

$output_format = array(
                    "resultElement" => "employees",
                    "rowElement" => "employee",
                    "elements" => array(
			    "id" => array("column" => "employeeId",
		    			  "xsdType" => "xsd:int"),
			    "name" => array("column" => "name",
		    			  "xsdType" => "xsd:string"),
			    "email" => array("column" => "email",
		    			  "xsdType" => "xsd:string"),
			    "jobTitle" => array("column" => "jobTitle",
		    			  "xsdType" => "xsd:string"),
			    "project" => array("column" => "project",
		    			  "xsdType" => "xsd:string")));

Note that you provide the xsd type for each field explicitly. In fact this change is not needed for mysql pdo extension since it allows identifying field types programatically. Since this feature is not available in all the other pdo drivers, we have to explicitly give xsd type information for them.

If you wan to provide data services with SQLite or MSSQL, You can check my other posts on MSSQL(Microsoft SQL) Data Services In PHP and Data Services with SQLite in PHP.

This entry was posted in 2 minutes guide, DataServices, php, REST, SOA, SQL, Tutorial/Guide, web services, WSDL, wsf/php, wso2 and tagged , , , , , , . Bookmark the permalink.

2 Responses to PHP Data Services with PostgreSQL

  1. Mounir Bechchi says:

    Thanks for releasing this document which helped me to get start with WSF/PHP data services.

    However, I have a problem with my first service.
    The service works well but the apache memory usage rise, as the soap client is called, until it crashes both apache and postgresql servers.

    Do you have any idea about this issue.

    My server :
    “username” => “postgres”,
    “password” => “postgres”,
    “dbname” => “DBTICKETS”,
    “dbhost” => “localhost”

    // security configuration
    $pub_key = ws_get_cert_from_file(“./clientsoapovh9.crt”);
    $pvt_key = ws_get_key_from_file(“./servsoapovh9.key”);
    $pub_key_server = ws_get_cert_from_file(“./servsoapovh9.crt”);
    $sec_array = array(“sign”=>TRUE,”encrypt” => TRUE, “algorithmSuite” => “Basic256Rsa15”,
    “securityTokenReference” => “IssuerSerial”);
    $policy = new WSPolicy(array(“security”=>$sec_array));
    $sec_token = new WSSecurityToken(array(“privateKey” => $pvt_key,
    “receiverCertificate” =>$pub_key,
    “certificate” => $rec_cert_client));

    $inputFormatticket = array(“nim” => “STRING”,”datej” => “STRING”,”cb” => “STRING”,”fam” => “STRING”);
    $outputFormatticket = array(“resultElement” => “listeticketjourResponse”,
    “rowElement” => “ticket”,

    “elements” => array(
    “id” => “id”,
    “nul” => “nul”,
    “heure” => “heure”,
    “caisse” => “caisse”,
    “emp” => “emp”,
    “montant” => “montant”,
    “mode” => “mode”,
    “article” => “article”,
    “z” => “z”));
    $sqlticket = “SELECT id,nul,heure,caisse,emp,montant,mode,article,z FROM ticket_liste_jour(?,?,’HEURE, MONTANT, NB_ARTICLE, PAIEMENT, CAISSE, EMPLOYE’,’ASC’,?,?) “.
    “as (id int8, nul int8, heure timestamp, caisse int8, emp varchar, montant float8, mode varchar, article int4, z int8 )”;
    $operationsticket = array(“inputFormat” => $inputFormatticket,
    “sql” => $sqlticket,
    “outputFormat” => $outputFormatticket);

    $operations = array(“listeticketjour” => $operationsticket);

    $actions = array(“http://xxx/listeticketjour” => “listeticketjour”);

    $my_data_service = new DataService(array(“config” => $db_config,
    “operations” => $operations,
    “actions” => $actions,
    “policy” => $policy,
    “securityToken” => $sec_token));


    My client :
    $requestPayloadString = <<<XML


    $rec_cert = ws_get_cert_from_file(“./keys/servsoapovh9.crt”);
    $pvt_key = ws_get_key_from_file(“./keys/clientsoapovh9.key”);
    $rec_cert_client = ws_get_cert_from_file(“./keys/clientsoapovh9.crt”);

    $reqMessage = new WSMessage($requestPayloadString,
    array(“to” => “http://xxx/server.php”,
    “action” => “http://xxx/listeticketjour”));

    $sec_array = array(“sign”=>TRUE,”encrypt”=>TRUE, “algorithmSuite” => “Basic256Rsa15”,
    “securityTokenReference” => “IssuerSerial”);
    $policy = new WSPolicy(array(“security”=>$sec_array));
    $sec_token = new WSSecurityToken(array(“privateKey” => $pvt_key,
    “receiverCertificate” => $rec_cert,
    “certificate” => $rec_cert_client));
    $client = new WSClient(array(“useWSA” => TRUE, “policy” => $policy,
    “securityToken” => $sec_token));

    $resMessage = $client->request($reqMessage);
    $response = $resMessage->str;

  2. dimuthu says:

    Did you able to run non-pgsql services on your installation. If so I think it can be an issue in the installation?. Anyway there is a new wsf/php release (2.1), Can you try that and check whether the problem still there?

Leave a Reply

Your email address will not be published. Required fields are marked *